SpringBoot+JPA+Freemarker 生成动态SQL

「爱情、让人受尽委屈。」 2022-05-11 08:24 485阅读 0赞

在实际开发中，有时候会遇到复杂的统计SQL，这时候Hibernate并不能很好的满足我们的需求，一方面SQL语句写在代码中不美观，可读性比较差，另一方面不方便修改SQL语句。
如果可以像mybaits一样写在XML中的话，就方便许多了。
采用Freemarker模板就可以达到将SQL写在XML文件中的功能。

一、pom引用

  1. <dependency>
  2.             <groupId>org.springframework.boot</groupId>
  3.             <artifactId>spring-boot-starter-data-jpa</artifactId>
  4.             <exclusions><!-- 去掉默认配置 -->
  5.                 <exclusion>
  6.                     <groupId>org.springframework.boot</groupId>
  7.                     <artifactId>spring-boot-starter-logging</artifactId>
  8.                 </exclusion>
  9.             </exclusions>
  10.         </dependency>
  11.         <dependency>
  12.             <groupId>org.springframework.boot</groupId>
  13.             <artifactId>spring-boot-starter-freemarker</artifactId>
  14.         </dependency>
  15.         <dependency>
  16.             <groupId>org.springframework.boot</groupId>
  17.             <artifactId>spring-boot-starter-jdbc</artifactId>
  18.         </dependency>
  19.         <dependency>
  20.             <groupId>org.springframework.boot</groupId>
  21.             <artifactId>spring-boot-starter-log4j2</artifactId>
  22.         </dependency>

二、写个DTD文件，定义XML文件格式

  1. <!-- HOP Hibernate Dynamic Statement Mapping DTD.  
  3. <!DOCTYPE dynamic-hibernate-statement PUBLIC   
  4.     "-//Haier/HOP Hibernate Dynamic Statement DTD 1.0//EN"  
  5.     "http://www.wongws.com/dtd/dynamic-hibernate-statement-1.0.dtd">  
  7. 这个文件时用来定义动态参数语句，类似itabis  
  9. -->  
  11. <!-- 
  12.     The document root. 
  13.  -->  
  15. <!ELEMENT dynamic-hibernate-statement (  
  16.     (hql-query|sql-query)*  
  17. )>  
  18.                             <!-- default: none -->  
  20. <!-- The query element declares a named Hibernate query string -->  
  22. <!ELEMENT hql-query (#PCDATA)>  
  23.     <!ATTLIST hql-query name CDATA #REQUIRED>  
  25. <!-- The sql-query element declares a named SQL query string -->  
  27. <!ELEMENT sql-query (#PCDATA)>  
  28.     <!ATTLIST sql-query name CDATA #REQUIRED>  
  29.     <!ATTLIST sql-query mode (normal|jdbc|namedJdbc) "normal">

三、DTD解析器

  1. public class DynamicStatementDTDEntityResolver implements EntityResolver {
  3.     private static final Logger LOGGER = LoggerFactory.getLogger(DynamicStatementDTDEntityResolver.class);
  4.     private static final String HOP_DYNAMIC_STATEMENT = "http://www.wongws.com/dtd/";
  6.     public InputSource resolveEntity(String publicId, String systemId) {
  7.         InputSource source = null; // returning null triggers default behavior
  8.         if (systemId != null) {
  9.             LOGGER.debug("trying to resolve system-id [" + systemId + "]");
  10.             if (systemId.startsWith(HOP_DYNAMIC_STATEMENT)) {
  11.                 LOGGER.debug(
  12.                         "recognized hop dyanmic statement namespace; attempting to resolve on classpath under com/haier/openplatform/dao/hibernate/");
  13.                 source = resolveOnClassPath(publicId, systemId, HOP_DYNAMIC_STATEMENT);
  14.             }
  15.         }
  16.         return source;
  17.     }
  19.     private InputSource resolveOnClassPath(String publicId, String systemId, String namespace) {
  20.         InputSource source = null;
  21.         String path = "dtd/" + systemId.substring(namespace.length());
  22.         InputStream dtdStream = resolveInHibernateNamespace(path);
  23.         if (dtdStream == null) {
  24.             LOGGER.debug("unable to locate [" + systemId + "] on classpath");
  25.             if (systemId.substring(namespace.length()).indexOf("2.0") > -1) {
  26.                 LOGGER.error("Don't use old DTDs, read the Hibernate 3.x Migration Guide!");
  27.             }
  28.         } else {
  29.             LOGGER.debug("located [" + systemId + "] in classpath");
  30.             source = new InputSource(dtdStream);
  31.             source.setPublicId(publicId);
  32.             source.setSystemId(systemId);
  33.         }
  34.         return source;
  35.     }
  37.     protected InputStream resolveInHibernateNamespace(String path) {
  38.         return this.getClass().getClassLoader().getResourceAsStream(path);
  39.     }
  41. }

四、构造存储动态SQL的对象

  1. public class StatementTemplate {
  2.     public enum TYPE {
  4.         /** hql 查询 */
  6.         HQL,
  8.         /** sql 查询 */
  10.         SQL
  12.     }
  14.     public StatementTemplate() {
  16.     }
  18.     public StatementTemplate(TYPE type, Template template) {
  20.         this.type = type;
  22.         this.template = template;
  24.     }
  26.     private TYPE type;
  28.     private Template template;
  30.     public TYPE getType() {
  32.         return type;
  34.     }
  36.     public void setType(TYPE type) {
  38.         this.type = type;
  40.     }
  42.     public Template getTemplate() {
  44.         return template;
  46.     }
  48.     public void setTemplate(Template template) {
  50.         this.template = template;
  52.     }
  53. }

五、构造动态SQL加载器

  1. public class DefaultDynamicHibernateStatementBuilder implements DynamicHibernateStatementBuilder, ResourceLoaderAware {
  2.     private static final Logger LOGGER = LoggerFactory.getLogger(DefaultDynamicHibernateStatementBuilder.class);
  3.     private Map<String, String> namedHQLQueries;
  4.     private Map<String, String> namedSQLQueries;
  5.     private String[] fileNames = new String[0];
  6.     private ResourceLoader resourceLoader;
  7.     private EntityResolver entityResolver = new DynamicStatementDTDEntityResolver();
  8.     /**
  9.      * 查询语句名称缓存，不允许重复
  10.      */
  11.     private Set<String> nameCache = new HashSet<String>();
  13.     public void setFileNames(String[] fileNames) {
  14.         this.fileNames = fileNames;
  15.     }
  17.     @Override
  18.     public Map<String, String> getNamedHQLQueries() {
  19.         return namedHQLQueries;
  20.     }
  22.     @Override
  23.     public Map<String, String> getNamedSQLQueries() {
  24.         return namedSQLQueries;
  25.     }
  27.     @Override
  28.     public void init() throws IOException {
  29.         namedHQLQueries = new HashMap<String, String>();
  30.         namedSQLQueries = new HashMap<String, String>();
  31.         boolean flag = this.resourceLoader instanceof ResourcePatternResolver;
  32.         for (String file : fileNames) {
  33.             if (flag) {
  34.                 Resource[] resources = ((ResourcePatternResolver) this.resourceLoader).getResources(file);
  35.                 buildMap(resources);
  36.             } else {
  37.                 Resource resource = resourceLoader.getResource(file);
  38.                 buildMap(resource);
  39.             }
  40.         }
  41.         // clear name cache
  42.         nameCache.clear();
  43.     }
  45.     @Override
  46.     public void setResourceLoader(ResourceLoader resourceLoader) {
  47.         this.resourceLoader = resourceLoader;
  48.     }
  50.     private void buildMap(Resource[] resources)  {
  51.         if (resources == null) {
  52.             return;
  53.         }
  54.         for (Resource resource : resources) {
  55.             buildMap(resource);
  56.         }
  57.     }
  59.     @SuppressWarnings({ "rawtypes" })
  60.     private void buildMap(Resource resource) {
  61.         InputSource inputSource = null;
  62.         try {
  63.             inputSource = new InputSource(resource.getInputStream());
  64.             XmlDocument metadataXml = readMappingDocument(entityResolver, inputSource,
  65.                     new OriginImpl("file", resource.getFilename()));
  66.             if (isDynamicStatementXml(metadataXml)) {
  67.                 final Document doc = metadataXml.getDocumentTree();
  68.                 final Element dynamicHibernateStatement = doc.getRootElement();
  69.                 Iterator rootChildren = dynamicHibernateStatement.elementIterator();
  70.                 while (rootChildren.hasNext()) {
  71.                     final Element element = (Element) rootChildren.next();
  72.                     final String elementName = element.getName();
  73.                     if ("sql-query".equals(elementName)) {
  74.                         putStatementToCacheMap(resource, element, namedSQLQueries);
  75.                     } else if ("hql-query".equals(elementName)) {
  76.                         putStatementToCacheMap(resource, element, namedHQLQueries);
  77.                     }
  78.                 }
  79.             }
  80.         } catch (Exception e) {
  81.             LOGGER.error(e.toString());
  82.             throw new RuntimeException(e);
  83.         } finally {
  84.             if (inputSource != null && inputSource.getByteStream() != null) {
  85.                 try {
  86.                     inputSource.getByteStream().close();
  87.                 } catch (IOException e) {
  88.                     LOGGER.error(e.toString());
  89.                     throw new RuntimeException(e);
  90.                 }
  91.             }
  92.         }
  94.     }
  96.     private void putStatementToCacheMap(Resource resource, final Element element, Map<String, String> statementMap)
  97.             throws Exception {
  98.         String sqlQueryName = element.attribute("name").getText();
  99.          Validate.notEmpty(sqlQueryName);
  101.         if (nameCache.contains(sqlQueryName)) {
  102.             throw new RuntimeException("重复的sql-query/hql-query语句定义在文件:" + resource.getURI() + "中，必须保证name的唯一.");
  103.         }
  104.         nameCache.add(sqlQueryName);
  105.         String queryText = element.getText();
  106.         statementMap.put(sqlQueryName, queryText);
  107.     }
  109.     private static boolean isDynamicStatementXml(XmlDocument xmlDocument) {
  110.         return "dynamic-hibernate-statement".equals(xmlDocument.getDocumentTree().getRootElement().getName());
  111.     }
  113.     public XmlDocument readMappingDocument(EntityResolver entityResolver, InputSource source, Origin origin) {
  114.         return legacyReadMappingDocument(entityResolver, source, origin);
  115. //        return readMappingDocument( source, origin );
  116.     }
  118.     private XmlDocument legacyReadMappingDocument(EntityResolver entityResolver, InputSource source, Origin origin) {
  119.         // IMPL NOTE : this is the legacy logic as pulled from the old
  120.         // AnnotationConfiguration code
  122.         Exception failure;
  124.         ErrorLogger errorHandler = new ErrorLogger();
  126.         SAXReader saxReader = new SAXReader();
  127.         saxReader.setEntityResolver(entityResolver);
  128.         saxReader.setErrorHandler(errorHandler);
  129.         saxReader.setMergeAdjacentText(true);
  130.         saxReader.setValidation(true);
  132.         Document document = null;
  133.         try {
  134.             // first try with orm 2.1 xsd validation
  135.             setValidationFor(saxReader, "orm_2_1.xsd");
  136.             document = saxReader.read(source);
  137.             if (errorHandler.hasErrors()) {
  138.                 throw errorHandler.getErrors().get(0);
  139.             }
  140.             return new XmlDocumentImpl(document, origin.getType(), origin.getName());
  141.         } catch (Exception e) {
  143.             LOGGER.debug("Problem parsing XML using orm 2.1 xsd, trying 2.0 xsd : {}", e.getMessage());
  145.             failure = e;
  146.             errorHandler.reset();
  148.             if (document != null) {
  149.                 // next try with orm 2.0 xsd validation
  150.                 try {
  151.                     setValidationFor(saxReader, "orm_2_0.xsd");
  152.                     document = saxReader.read(new StringReader(document.asXML()));
  153.                     if (errorHandler.hasErrors()) {
  154.                         errorHandler.logErrors();
  155.                         throw errorHandler.getErrors().get(0);
  156.                     }
  157.                     return new XmlDocumentImpl(document, origin.getType(), origin.getName());
  158.                 } catch (Exception e2) {
  160.                     LOGGER.debug("Problem parsing XML using orm 2.0 xsd, trying 1.0 xsd : {}", e2.getMessage());
  162.                     errorHandler.reset();
  164.                     if (document != null) {
  165.                         // next try with orm 1.0 xsd validation
  166.                         try {
  167.                             setValidationFor(saxReader, "orm_1_0.xsd");
  168.                             document = saxReader.read(new StringReader(document.asXML()));
  169.                             if (errorHandler.hasErrors()) {
  170.                                 errorHandler.logErrors();
  171.                                 throw errorHandler.getErrors().get(0);
  172.                             }
  173.                             return new XmlDocumentImpl(document, origin.getType(), origin.getName());
  174.                         } catch (Exception e3) {
  176.                             LOGGER.debug("Problem parsing XML using orm 1.0 xsd : {}", e3.getMessage());
  178.                         }
  179.                     }
  180.                 }
  181.             }
  182.         }
  183.         throw new InvalidMappingException("Unable to read XML", origin.getType(), origin.getName(), failure);
  184.     }
  186.     private void setValidationFor(SAXReader saxReader, String xsd) {
  187.         try {
  188.             saxReader.setFeature("http://apache.org/xml/features/validation/schema", true);
  189.             // saxReader.setFeature( "http://apache.org/xml/features/validation/dynamic",
  190.             // true );
  191.             if ("orm_2_1.xsd".equals(xsd)) {
  192.                 saxReader.setProperty("http://apache.org/xml/properties/schema/external-schemaLocation",
  193.                         "http://xmlns.jcp.org/xml/ns/persistence/orm " + xsd);
  194.             } else {
  195.                 saxReader.setProperty("http://apache.org/xml/properties/schema/external-schemaLocation",
  196.                         "http://java.sun.com/xml/ns/persistence/orm " + xsd);
  197.             }
  198.         } catch (SAXException e) {
  199.             saxReader.setValidation(false);
  200.         }
  201.     }
  203. }
  205. public interface DynamicHibernateStatementBuilder {
  206.     /**
  207.      * hql语句map
  208.      * 
  209.      * @return
  210.      */
  211.     Map<String, String> getNamedHQLQueries();
  213.     /**
  214.      * sql语句map
  215.      * 
  216.      * @return
  217.      */
  218.     Map<String, String> getNamedSQLQueries();
  220.     /**
  221.      * 初始化
  222.      * 
  223.      * @throws IOException
  224.      */
  225.     void init() throws IOException;
  227.     void setFileNames(String[] fileNames);
  229.     void setResourceLoader(ResourceLoader resourceLoader);
  230. }

六、利用Freemarker模板书写动态SQL

  1. <?xml version="1.0" encoding="utf-8"?>  
  2. <!DOCTYPE dynamic-hibernate-statement PUBLIC "-//Haier/HOP Hibernate Dynamic Statement DTD 1.0//EN"  
  3. "http://www.wongws.com/dtd/dynamic-hibernate-statement-1.0.dtd">
  5. <dynamic-hibernate-statement>
  7.     <sql-query name="resource.getUser" mode="normal">  
  8.     <![CDATA[ 
  10.        select * from t_user t where t.user_id=${userid}
  11.          <#if password??> 
  12.             and t.password=${password} 
  13.          </#if> 
  14.     ]]>
  15.     </sql-query>
  16. </dynamic-hibernate-statement>

以上就是功能实现的关键代码，但是利用Freemarker有个问题，就是没法解决SQL注入的问题，这里我的操作就是可以使用Freemarker生成动态SQL，然后再利用JdbcTemplate或NamedParameterJdbcTemplate来执行SQL。如将上面的SQL改写成这样：

  1. <sql-query name="resource.getUser" mode="normal">  
  2.     <![CDATA[ 
  4.        select * from t_user t where t.user_id=:userid
  5.          <#if password??> 
  6.             and t.password=:password
  7.          </#if> 
  8.     ]]>
  9.     </sql-query>

这样的话就可以绕过Freemarker的占位符使用，然后再通过NamedParameterJdbcTemplate执行SQL。
当然实际SQL不可能这么简单，肯定会遇到Map或者List等问题，这些只要网上查询Freemarker的语法，就能迎刃而解。
下面奉上源码，由于这只是个Demo，代码实在是粗制滥造了点，但是功能还是能够实现的，在com.example.demo下的DemoApplicationTests类上，运行Junit Test。当然，要是实现mysql访问，还得自己配数据库连接。

https://download.csdn.net/download/dashuaigege642/10681049

参考：

https://blog.csdn.net/crazycoder2010/article/details/7414152

发表评论

表情：
评论列表 （有 0 条评论，485人围观）

还没有评论，来说两句吧...

相关阅读