一、需求背景

给hibernate插上ibatis动态查询的翅膀，既保留crud的简洁性，又能收获ibatis的特性。

二、ibatis的动态查询

 1 <select id="findUser" resultClass="User"> 2  SELECT * From User 3 <dynamic prepend="WHERE"> 4 <isNull property="id"> 5  id IS NULL 6 </isNull> 7 <isNotNull property="id"> 8  id = #id# 9 </isNotNull>10 </dynamic>11 </select>

ibatis在程序内部解析sql语句中的标签，然后去解析计算

三、Freemarker 模版技术

利用freemarker把sql/hql中的动态拼接条件判断语法都交给freemarker语法去处理，既能复用freemarker框架，又保持了框架设计的简洁性

Freemarker 生成静态页面，首先需要使用自己定义的模板页面，这个模板页面可以是最最普通的html，也可以是嵌套freemarker中的 取值表达式， 标签或者自定义标签等等，然后后台读取这个模板页面，解析其中的标签完成相对应的操作， 然后采用键值对的方式传递参数替换模板中的的取值表达式，做完之后 根据配置的路径生成一个新的html页面， 以达到静态化访问的目的。

Freemarker标签都是<#标签名称>这样子命名的，${value} 表示输出变量名的内容

四、实例

User-dynamicHbSql.xml

 1 <?xml version="1.0" encoding="utf-8"?> 2 <!DOCTYPE dynamic-hibernate-statement PUBLIC "-//xxx/HOP Hibernate Dynamic Statement DTD 1.0//EN" 3 "https://www.xxx.com/dtd/dynamic-hibernate-statement-1.0.dtd"> 4 <dynamic-hibernate-statement> 5 <sql-query name="User.findUser"> 6 <![CDATA[ 7  select * from user where id=:id 8 ]]>  9 </sql-query>10 </dynamic-hibernate-statement>

将sql语句编写的xml中，然后在java代码中，传入参数，经过freemarker处理，得到处理完的SQL. 好处:

• sql编写在xml中，便于阅读
• 可以使用freemarker语法，动态构建SQL
• 可以使用freemarker的include语句，提取公用SQL

pom.xml

1 <!-- freemarker -->2 <dependency>3 <groupId>org.freemarker</groupId>4 <artifactId>freemarker</artifactId>5 <version>2.3.20</version>6 </dependency>

 web.xml

1 <servlet>2 <servlet-name>sqlCache</servlet-name>3 <servlet-class>xx.servlet.DynamicSqlInitServlet</servlet-class>4 <init-param> 5 <param-name>fileName</param-name> 6 <param-value>classpath*:hibernate/**/*-dynamicHbSql.xml</param-value> 7 </init-param> 8 <load-on-startup>2</load-on-startup>9 </servlet>

dtd定义：dynamic-hibernate-statement-1.0.dtd

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

 DTD校验器/解析器

 1 package xxx.dao; 2  3 import java.io.InputStream; 4 import java.io.Serializable; 5  6 import org.hibernate.internal.util.ConfigHelper; 7 import org.slf4j.Logger; 8 import org.slf4j.LoggerFactory; 9 import org.xml.sax.EntityResolver;10 import org.xml.sax.InputSource;11 12 public class DynamicStatementDtdEntityResolver implements EntityResolver, Serializable {13 private static final long serialVersionUID = 8123799007554762965L;14 private static final Logger LOGGER = LoggerFactory.getLogger(DynamicStatementDtdEntityResolver.class);15 private static final String HOP_DYNAMIC_STATEMENT = "https://xxx.xxx.com/dtd/";16 17  @Override18 public InputSource resolveEntity(String publicId, String systemId) {19 InputSource source = null;20 if (systemId != null) {21 LOGGER.debug("trying to resolve system-id [" + systemId + "]");22 if (systemId.startsWith(HOP_DYNAMIC_STATEMENT)) {23  LOGGER.debug(24 "recognized hop dyanmic statement namespace; attempting to resolve on classpath under xxx");25 source = resolveOnClassPath(publicId, systemId, HOP_DYNAMIC_STATEMENT);26  }27  }28 return source;29  }30 31 private InputSource resolveOnClassPath(String publicId, String systemId, String namespace) {32 InputSource source = null;33 String path = "dtd/" + systemId.substring(namespace.length());34 InputStream dtdStream = resolveInHibernateNamespace(path);35 if (dtdStream == null) {36 LOGGER.debug("unable to locate [" + systemId + "] on classpath");37 String nameFlag = "2.0";38 if (systemId.substring(namespace.length()).indexOf(nameFlag) > -1) {39 LOGGER.error("Don‘t use old DTDs, read the Hibernate 3.x Migration Guide!");40  }41 } else {42 LOGGER.debug("located [" + systemId + "] in classpath");43 source = new InputSource(dtdStream);44  source.setPublicId(publicId);45  source.setSystemId(systemId);46  }47 return source;48  }49 50 protected InputStream resolveInHibernateNamespace(String path) {51 return this.getClass().getClassLoader().getResourceAsStream(path);52  }53 54 protected InputStream resolveInLocalNamespace(String path) {55 try {56 return ConfigHelper.getUserResourceAsStream(path);57 } catch (Throwable t) {58 return null;59  }60  }61 }

加载*-dynamicHbSql.xml

 1 package xx.dao.servlet; 2  3 import java.io.IOException; 4 import java.util.HashMap; 5 import java.util.HashSet; 6 import java.util.Iterator; 7 import java.util.Map; 8 import java.util.Set; 9  10 import javax.servlet.ServletContext; 11 import javax.servlet.ServletException; 12 import javax.servlet.http.HttpServlet; 13  14 import org.apache.commons.lang3.Validate; 15 import org.dom4j.Document; 16 import org.dom4j.Element; 17 import org.hibernate.internal.util.xml.MappingReader; 18 import org.hibernate.internal.util.xml.OriginImpl; 19 import org.hibernate.internal.util.xml.XmlDocument; 20 import org.slf4j.Logger; 21 import org.slf4j.LoggerFactory; 22 import org.springframework.core.io.Resource; 23 import org.springframework.core.io.support.PathMatchingResourcePatternResolver; 24 import org.springframework.web.context.WebApplicationContext; 25 import org.springframework.web.context.support.WebApplicationContextUtils; 26 import org.xml.sax.EntityResolver; 27 import org.xml.sax.InputSource; 28  29 import xx.dao.DynamicStatementDtdEntityResolver; 30 import xx.cache.DynamicSqlInitCache; 31  32 /** 33  * 持久层sql初始化加载类 34 */ 35 public class DynamicSqlInitServlet extends HttpServlet { 36  37 private static final long serialVersionUID = 1L; 38  39 private static final Logger LOGGER = LoggerFactory.getLogger(DynamicSqlInitServlet.class); 40  41 private EntityResolver entityResolver = new DynamicStatementDtdEntityResolver(); 42  43 private static DynamicSqlInitCache dynamicSqlInitCache; 44  45 /** 46  *  47  * 将sql初始化进入缓存中  48  * @Title: init  49  * @param @throws ServletException 设定文件  50  * @author Administrator  51  * @throws 52 */ 53  @Override 54 public void init() throws ServletException { 55 super.init(); 56  57 PathMatchingResourcePatternResolver resourceLoader = new PathMatchingResourcePatternResolver(); 58 Map<String, String> namedHQLQueries = new HashMap<String, String>(10); 59 Map<String, String> namedSQLQueries = new HashMap<String, String>(10); 60 Set<String> nameCache = new HashSet<String>(); 61 String fileName = this.getInitParameter("fileName"); 62 try { 63 Resource[] resources = resourceLoader.getResources(fileName); 64  buildMap(resources, namedHQLQueries, namedSQLQueries, nameCache); 65  66 ServletContext servletContext = this.getServletContext(); 67 WebApplicationContext ctx = WebApplicationContextUtils.getWebApplicationContext(servletContext); 68 dynamicSqlInitCache = (DynamicSqlInitCache) ctx.getBean("dynamicSqlInitCache"); 69  70 for (String hqlKey : namedHQLQueries.keySet()) { 71  72 dynamicSqlInitCache.getTemplateCacheByKey(hqlKey, namedHQLQueries.get(hqlKey), true); 73  74  } 75  76 for (String sqlKey : namedSQLQueries.keySet()) { 77 dynamicSqlInitCache.getTemplateCacheByKey(sqlKey, namedSQLQueries.get(sqlKey), false); 78  } 79 } catch (IOException e) { 80 LOGGER.error("初始化sql缓存失败！", e); 81  } 82 // clear name cache 83  namedHQLQueries.clear(); 84  namedSQLQueries.clear(); 85  nameCache.clear(); 86  87  } 88  89 /** 90  * 从文件中加载sql 91  * @param resources 92  * @param namedHQLQueries 93  * @param namedSQLQueries 94  * @param nameCache 设定文件  95  * @throws ServletException  96 */ 97 private void buildMap(Resource[] resources, Map<String, String> namedHQLQueries, 98 Map<String, String> namedSQLQueries, Set<String> nameCache) throws ServletException { 99 if (resources == null) {100 return;101  }102 for (Resource resource : resources) {103 InputSource inputSource = null;104 try {105 inputSource = new InputSource(resource.getInputStream());106 XmlDocument metadataXml = MappingReader.INSTANCE.readMappingDocument(entityResolver, inputSource,107 new OriginImpl("file", resource.getFilename()));108 if (isDynamicStatementXml(metadataXml)) {109 final Document doc = metadataXml.getDocumentTree();110 final Element dynamicHibernateStatement = doc.getRootElement();111 Iterator rootChildren = dynamicHibernateStatement.elementIterator();112 while (rootChildren.hasNext()) {113 final Element element = (Element) rootChildren.next();114 final String elementName = element.getName();115 if ("sql-query".equals(elementName)) {116  putStatementToCacheMap(resource, element, namedSQLQueries, nameCache);117 } else if ("hql-query".equals(elementName)) {118  putStatementToCacheMap(resource, element, namedHQLQueries, nameCache);119  }120  }121  }122 } catch (Exception e) {123  LOGGER.error(e.toString(),e);124 throw new ServletException(e.getMessage(), e);125 } finally {126 if (inputSource != null && inputSource.getByteStream() != null) {127 try {128  inputSource.getByteStream().close();129 } catch (IOException e) {130  LOGGER.error(e.toString());131 throw new ServletException(e.getMessage(), e);132  }133  }134  }135 136  }137 138  }139 140 /**141  * 将sql放入map中142  * @param resource143  * @param element144  * @param statementMap145  * @param nameCache146  * @throws IOException 设定文件 147 */148 private void putStatementToCacheMap(Resource resource, final Element element, Map<String, String> statementMap,149 Set<String> nameCache) throws Exception {150 String sqlQueryName = element.attribute("name").getText();151  Validate.notEmpty(sqlQueryName);152 if (nameCache.contains(sqlQueryName)) {153 throw new Exception("重复的sql-query/hql-query语句定义在文件:" + resource.getURI() + "中，必须保证name的唯一.");154  }155  nameCache.add(sqlQueryName);156 String queryText = element.getText();157  statementMap.put(sqlQueryName, queryText);158  }159 160 /**161  * 判断是否是贮存sql的xml文件 162  * @param xmlDocument163  * @return boolean 返回类型164 */165 private static boolean isDynamicStatementXml(XmlDocument xmlDocument) {166 return "dynamic-hibernate-statement".equals(xmlDocument.getDocumentTree().getRootElement().getName());167  }168 169 }

 1 package xxx.cache; 2  3 import java.io.IOException; 4 import java.io.StringReader; 5  6 import org.springframework.cache.annotation.Cacheable; 7 import org.springframework.stereotype.Component; 8  9 import xxx.dao.StatementTemplate;10 11 import freemarker.cache.StringTemplateLoader;12 import freemarker.template.Configuration;13 import freemarker.template.Template;14 15 /**16  * Sql缓存类17 */18 @Component19 public class DynamicSqlInitCache {20 21 private Configuration configuration;22 23 private StringTemplateLoader stringLoader;24 25 /**26  * 根据key获取sql对应的StatementTemplate27  * @param key28  * @param sql29  * @param isHql30  * @return StatementTemplate 31  * @throws IOException32 */33 @Cacheable(value = "sqlCache", key = "#key")34 public StatementTemplate getTemplateCacheByKey(String key, String sql, boolean isHql) throws IOException {35 if (configuration == null) {36 configuration =new Configuration();37  }38 if (stringLoader == null) {39 stringLoader =new StringTemplateLoader(); 40  }41 configuration.setNumberFormat("#");42 if (isHql) {43  stringLoader.putTemplate(key, sql);44 StatementTemplate statementTemplate = new StatementTemplate(StatementTemplate.TYPE.HQL,new Template(key,new StringReader(sql),configuration));45  configuration.setTemplateLoader(stringLoader);46 return statementTemplate;47 } else {48  stringLoader.putTemplate(key, sql);49 StatementTemplate statementTemplat e= new StatementTemplate(StatementTemplate.TYPE.SQL,new Template(key,new StringReader(sql),configuration));50  configuration.setTemplateLoader(stringLoader);51 return statementTemplate;52  }53  }54 }

 1 package xxx.dao; 2  3 import java.io.Serializable; 4  5 import freemarker.template.Template; 6  7 /** 8  * @Description: sql模板类 9 */10 public class StatementTemplate implements Serializable{11 /**12  * 13 */14 private static final long serialVersionUID = 6887405509065040282L;15 16 private Template template;17 18 private TYPE type;19 20 public StatementTemplate(TYPE type, Template template) {21 this.template = template;22 this.type = type;23  }24 25 public TYPE getType() {26 return type;27  }28 29 public void setType(TYPE type) {30 this.type = type;31  }32 33 public Template getTemplate() {34 return template;35  }36 37 public void setTemplate(Template template) {38 this.template = template;39  }40 41 public static enum TYPE {42 /**43  * HQL44 */45  HQL,46  SQL47  }48 }