原文:通过velocity模板和Hibernate sql-query的实现动态hql
源代码下载地址:http://www.zuidaima.com/share/1723627800005632.htm
在开发的时候,很多时候都遇到过需要动态拼写SQL,有的是在配置文件中写SQL,有的是在Java代码中拼写SQL,以配置文件拼SQL的可以拿IBatis为代表,但是很多时候是使用Hibernate的,这个时候就想要是Hibernate能像IBatis那样写就好了。
这个时候就想到了模板语言和配置文件的结合。模板引擎可以选择Velocity,简单而不失强大,配置文件可以模仿Hibernate的sql-query 的XML文件。
Sql-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 && $name.length() > 1)
AND name =:name
#end
]]>
</query>
<query name="selectUserHQL">
<![CDATA[
FROM users
Where 1=1
#if($name && $name.length() > 1)
AND name =:name
#end
]]>
</query>
在系统加载时,需要把配置文件加载到系统中。加载代码关键部分如下:
public class DynamicHibernateImpl implements InitializingBean, ResourceLoaderAware, ApplicationContextAware{
public void afterPropertiesSet() throws Exception {
for (int i = 0; i < fileNames.size(); i++) {
String fileName = ((String) fileNames.get(i)).trim();
if (resourceLoader instanceof ResourcePatternResolver) {
try {
Resource[] resources= ((ResourcePatternResolver) resourceLoader).getResources(fileName);
buildHQLMap(resources);
} catch (IOException ex) {
throw new Exception("Could not resolve sql definition resource pattern [" + fileName + "]", ex);
}
} else {
Resource resource = resourceLoader.getResource(fileName);
buildHQLMap(new Resource[] { resource });
}
}
}
protected void buildHQLMap(Resource[] resources) throws Exception {
for (int i = 0; i < resources.length; i++) {
buildHQLMap(resources[i]);
}
}
private void buildHQLMap(Resource resource) throws Exception {
try {
InputSource inputSource = new InputSource(resource.getInputStream());
org.w3c.dom.Document doc = this.documentLoader.loadDocument(inputSource, null, null, org.springframework.util.xml.XmlValidationModeDetector.VALIDATION_NONE, false);
Element root = doc.getDocumentElement();
List<Element> querys = DomUtils.getChildElements(root);
for(Element query:querys){
String queryName = query.getAttribute("name");
if (StringUtils.isEmpty(queryName)) {
throw new Exception("DynamicHibernate Service : name is essential attribute in a <query>.");
}
if(statements.containsKey(queryName)){
throw new Exception("DynamicHibernate Service : duplicated query in a <query>."+queryName);
}
statements.put(queryName, DomUtils.getTextValue(query));
}
} catch (SAXParseException se) {
throw se;
} catch (IOException ioe) {
throw ioe;
}
}
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这个类
public List findList(String queryName, Map params, int pageIndex, int pageSize) throws Exception {
Context context = generateVelocityContext(params);
Query query = findInternal(queryName, context);
if (pageIndex > 0 && pageSize > 0) {
query.setFirstResult((pageIndex - 1) * pageSize);
query.setMaxResults(pageSize);
}
return query.list();
};
private Context generateVelocityContext(Map<String, Object> params) {
VelocityContext context = new VelocityContext();
if (null == params) {
return null;
}
Iterator<String> iterator = params.keySet().iterator();
while (iterator.hasNext()) {
String key = iterator.next();
Object value = params.get(key);
if (null == value) {
continue;
}
context.put(key, value);
}
return context;
};
private Query findInternal(String queryName, Context context) throws Exception {
String sql = findSQLByVelocity(queryName, context);
Query query = sessionFactory.getCurrentSession().createQuery(sql);
String[] namedParams = query.getNamedParameters();
setProperties(query, context, namedParams);
return query;
};
private String findSQLByVelocity(String queryName, Context context) throws Exception {
if (context == null)
context = new VelocityContext();
String sql = getSqlByName(queryName);
StringWriter writer = new StringWriter();
Velocity.evaluate(context, writer, "Hibernate", sql);
sql = writer.toString();
return sql;
};
protected String getSqlByName(String queryKey) {
return statements.get(queryKey);
}