Hibernate 动态 HQL

Hibernate 动态 HQL

在开发的时候，很多时候都遇到过需要动态拼写SQL，有的是在配置文件中写SQL，有的是在Java代码中拼写SQL，以配置文件拼SQL的可以拿IBatis为代表，但是很多时候是使用Hibernate的，这个时候就想要是Hibernate能像IBatis那样写就好了。这个时候就想到了模板语言和配置文件的结合。模板引擎可以选择Velocity，简单而不失强大，配置文件可以模仿Hibernate的sql-query 的XML文件。Sq-query的示例代码如下(SQL or HQL)：<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE dynamic-hibernate PUBLIC "-//ANYFRAME//DTD DYNAMIC-HIBERNATE//EN""http://www.anyframejava.org/dtd/anyframe-dynamic-hibernate-mapping-4.0.dtd"><dynamic-hibernate>    <query name="selectUserSQL">        <![CDATA[            SELECT  USER_ID,NAME            FROM users_table Where 1=1            #if($name &amp;&amp; $name.length() > 1)             AND name =:name            #end    ]]>    </query>    <query name="selectUserHQL">    <![CDATA[         FROM users        Where 1=1            #if($name &amp;&amp; $name.length() > 1)                AND name =:name            #end        ]]>    </query> 在系统加载时，需要把配置文件加载到系统中。加载代码关键部分如下： 1   public class DynamicHibernateImpl implements InitializingBean, ResourceLoaderAware, ApplicationContextAware   2       public void afterPropertiesSet() throws Exception { 3          for (int i = 0; i < fileNames.size(); i++) { 4             String fileName = ((String) fileNames.get(i)).trim(); 5             if (resourceLoader instanceof ResourcePatternResolver) {                 6                 try { 7                     Resource[] resources=((ResourcePatternResolver) resourceLoader).getResources(fileName); 8                   buildHQLMap(resources); 9                 } catch (IOException ex) {10                     throw new Exception("Could not resolve sql definition resource pattern [" + fileName + "]", ex);11                 }12             } else {               13                 Resource resource = resourceLoader.getResource(fileName);14                 buildHQLMap(new Resource[] { resource });15             }16         }17     }18  protected void buildHQLMap(Resource[] resources) throws Exception {19         for (int i = 0; i < resources.length; i++) {20             buildHQLMap(resources[i]);21         }22     }23  private void buildHQLMap(Resource resource) throws Exception {24         try {25             InputSource inputSource = new InputSource(resource.getInputStream());26             org.w3c.dom.Document doc = this.documentLoader.loadDocument(inputSource, null, null, org.springframework.util.xml.XmlValidationModeDetector.VALIDATION_NONE, false);27             Element root = doc.getDocumentElement();28             List<Element> querys = DomUtils.getChildElements(root);29             for(Element query:querys){30                 String queryName = query.getAttribute("name");31                 if (StringUtils.isEmpty(queryName)) {32                     throw new Exception("DynamicHibernate Service : name is essential attribute in a <query>.");33                 }34                 if(statements.containsKey(queryName)){35                     throw new Exception("DynamicHibernate Service : duplicated query in a <query>."+queryName);36                 }37                 statements.put(queryName, DomUtils.getTextValue(query));38             }39         } catch (SAXParseException se) {40             throw se;41         } catch (IOException ioe) {42             throw ioe;43         }44     }Spring的配置文件示例如下：<bean id="dynamicHibernate" class="com.company.DynamicHibernateImpl"><property name="sessionFactory" ref="sessionFactory" /><property name="simpleTemplate" ref="simpleTemplate" /><property name="fileNames"><list><value>classpath*:hibernate/dynamic/dynamic-hibernate-*.xml</value></list></property></bean>下一步是在使用时调用sql并调用模板方法，进行sql动态化。还是DynamicHibernateImpl这个类  1     public List findList(String queryName, Map params, int pageIndex, int pageSize) throws Exception {  2         Context context = generateVelocityContext(params); 3         Query query = findInternal(queryName, context); 4         if (pageIndex > 0 &amp;&amp; pageSize > 0) { 5             query.setFirstResult((pageIndex - 1) * pageSize); 6             query.setMaxResults(pageSize); 7         } 8       return query.list();         9     };10   private Context generateVelocityContext(Map<String, Object> params) {11         VelocityContext context = new VelocityContext();12         if (null == params) {13             return null;14         }15         Iterator<String> iterator = params.keySet().iterator();16         while (iterator.hasNext()) {17             String key = iterator.next();18             Object value = params.get(key);19             if (null == value) {20                 continue;21             }           22             context.put(key, value);23         }24         return context;25     };26  private Query findInternal(String queryName, Context context) throws Exception {27         String sql = findSQLByVelocity(queryName, context);28         Query query = sessionFactory.getCurrentSession().createQuery(sql);29         String[] namedParams = query.getNamedParameters();30         setProperties(query, context, namedParams);31         return query;32     };33  private String findSQLByVelocity(String queryName, Context context) throws Exception {34         if (context == null)35             context = new VelocityContext();36         String sql = getSqlByName(queryName);37         StringWriter writer = new StringWriter();38         Velocity.evaluate(context, writer, "Hibernate", sql);39         sql = writer.toString();40         return sql;41     };42 protected String getSqlByName(String queryKey) {43         return statements.get(queryKey);44     }就这些。大家也许有更好的方法，欢迎交流。QQ:24889356QQ:24889356

评论

4 楼 liwanfeng 2012-06-18  

从哪里粘过来的？怎么这么乱？

3 楼 wangxiao5530 2011-12-06  

同感

2 楼 bubuhui 2011-12-05  

luoyu-ds 写道

我表示你的文章排版，没几个人愿意看下去....

同感哦

1 楼 luoyu-ds 2011-12-05  

我表示你的文章排版，没几个人愿意看下去....