使用hibernate作为持久层框架时,如果查询需要根据查询条件的不同而构造不同的查询sql,这就需要在hibernate 之上做一层分装。下面的总体思路就是结合freemarker模板引擎来实现参数的不同而生成不同的动态sql。
强力推荐-架构师的成长之路
nodejs 视频
react 视频
java 架构视频
java 视频
请使用Chrome 或者IE10以上浏览器
<!-- 根据年份分组查询培训班信息,全部 --> <hql-query name="cadreInfo.findEgjb04ByCode"> <![CDATA[ FROM EGJB04 as egjb04 WHERE 1=1 <#if code??> AND egjb04.code = :code </#if> ]]> </hql-query>
|
采用freeMarker模板引擎实现。
Hibernate动态Sql
1. 流程图
2.Freemarker配置
<bean id="freeMarkerConfigurer" class="org.springframework.web.servlet.view.freemarker.FreeMarkerConfigurer"> <property name="templateLoaderPaths" value="${template.loader_path}" /> <property name="freemarkerSettings"> <props> <prop key="defaultEncoding">${template.encoding}</prop> <prop key="url_escaping_charset">${url_escaping_charset}</prop> <prop key="locale">${locale}</prop> <prop key="template_update_delay">${template.update_delay}</prop> <prop key="tag_syntax">auto_detect</prop> <prop key="whitespace_stripping">true</prop> <prop key="classic_compatible">true</prop> <prop key="number_format">${template.number_format}</prop> <prop key="boolean_format">${template.boolean_format}</prop> <prop key="datetime_format">${template.datetime_format}</prop> <prop key="date_format">${template.date_format}</prop> <prop key="time_format">${template.time_format}</prop> <prop key="object_wrapper">freemarker.ext.beans.BeansWrapper</prop> </props> </property> <property name="freemarkerVariables"> <map> <entry key="systemName" value="${system.name}" /> <entry key="systemVersion" value="${system.version}" /> <entry key="systemDescription" value="${system.description}" /> <entry key="systemShowPowered" value="${system.show_powered}" /> <entry key="base" value="#{servletContext.contextPath}" /> <entry key="locale" value="${locale}" /> <entry key="setting" value="#{T(rongji.framework.util.SettingUtils).get()}" /> </map> </property> </bean> |
#------------ Template ------------ template.encoding=UTF-8 template.update_delay=3600 template.number_format=0.###### template.boolean_format=true,false template.datetime_format=yyyy-MM-dd template.date_format=yyyy-MM-dd template.time_format=HH:mm:ss template.loader_path=classpath:/template/ template.suffix=.ftl |
3. DTD定义
dynamic-hibernate-statement-1.0.dtd
<!-- HOP Hibernate Dynamic Statement Mapping DTD.
<!DOCTYPE dynamic-hibernate-statement PUBLIC "-//Haier/HOP Hibernate Dynamic Statement DTD 1.0//EN" "http://www.cmis.com/dtd/dynamic-hibernate-statement-1.0.dtd">
这个文件时用来定义动态参数语句
-->
<!-- The document root. -->
<!ELEMENT dynamic-hibernate-statement ( (hql-query|sql-query)* )> <!-- default: none -->
<!-- The query element declares a named Hibernate query string -->
<!ELEMENT hql-query (#PCDATA)> <!ATTLIST hql-query name CDATA #REQUIRED>
<!-- The sql-query element declares a named SQL query string -->
<!ELEMENT sql-query (#PCDATA)> <!ATTLIST sql-query name CDATA #REQUIRED> |
4. 动态sql配置文件
文件已-dynamic.xml结尾
<?xml version="1.0" encoding="utf-8"?> <!DOCTYPE dynamic-hibernate-statement PUBLIC "-//cmis/HOP Hibernate Dynamic Statement DTD 1.0//EN" "http://www.cmis.com/dtd/dynamic-hibernate-statement-1.0.dtd"> <dynamic-hibernate-statement>
<!-- --> <sql-query name="unitInfo.getCountUnitByInfrq"> <![CDATA[ SELECT count(1) FROM B01_UNIT_LIBRARY_RELA AS b01Hiber INNER JOIN CMIS_UNITGROUP unitGroup ON b01Hiber.DMCOD = unitGroup.DMCOD WHERE unitGroup.INPFRQ <:infrq AND unitGroup.UNIT_LIBRARY_ID =:libraryId ]]> </sql-query>
</dynamic-hibernate-statement> |
5. DTD解析器
package rongji.framework.base.dao.support;
import java.io.InputStream; import java.io.Serializable;
import org.hibernate.internal.util.ConfigHelper; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.xml.sax.EntityResolver; import org.xml.sax.InputSource;
/** * hibernate动态sql dtd解析器 * * */ public class DynamicStatementDTDEntityResolver implements EntityResolver, Serializable { private static final long serialVersionUID = 8123799007554762965L; private static final Logger LOGGER = LoggerFactory.getLogger(DynamicStatementDTDEntityResolver.class); private static final String HOP_DYNAMIC_STATEMENT = "http://www.cmis.com/dtd/";
public InputSource resolveEntity(String publicId, String systemId) { InputSource source = null; // returning null triggers default behavior if (systemId != null) { LOGGER.debug("trying to resolve system-id [" + systemId + "]"); if (systemId.startsWith(HOP_DYNAMIC_STATEMENT)) { LOGGER.debug("recognized hop dyanmic statement namespace; attempting to resolve on classpath under rongji/framework/base/dao/support/"); source = resolveOnClassPath(publicId, systemId, HOP_DYNAMIC_STATEMENT); } } return source; }
private InputSource resolveOnClassPath(String publicId, String systemId, String namespace) { InputSource source = null; String path = "rongji/framework/base/dao/support/" + systemId.substring(namespace.length()); InputStream dtdStream = resolveInHibernateNamespace(path); if (dtdStream == null) { LOGGER.debug("unable to locate [" + systemId + "] on classpath"); if (systemId.substring(namespace.length()).indexOf("2.0") > -1) { LOGGER.error("Don't use old DTDs, read the Hibernate 3.x Migration Guide!"); } } else { LOGGER.debug("located [" + systemId + "] in classpath"); source = new InputSource(dtdStream); source.setPublicId(publicId); source.setSystemId(systemId); } return source; }
protected InputStream resolveInHibernateNamespace(String path) { return this.getClass().getClassLoader().getResourceAsStream(path); }
protected InputStream resolveInLocalNamespace(String path) { try { return ConfigHelper.getUserResourceAsStream(path); } catch (Throwable t) { return null; } } } |
6. 加载动态sql文件
存储动态sql的实体
package rongji.framework.base.dao.support;
import freemarker.template.Template;
public class StatementTemplate {
public enum TYPE {
/** hql 查询 */ HQL,
/** sql 查询 */ SQL }
public StatementTemplate() { }
public StatementTemplate(TYPE type, Template template) { this.type = type; this.template = template; }
private TYPE type;
private Template template;
public TYPE getType() { return type; }
public void setType(TYPE type) { this.type = type; }
public Template getTemplate() { return template; }
public void setTemplate(Template template) { this.template = template; }
} |
动态加载sql接口
package rongji.framework.base.dao.support;
import java.io.IOException; import java.util.Map;
/** * 动态sql/hql语句组装器 * */ public interface DynamicHibernateStatementBuilder { /** * hql语句map * * @return */ public Map<String, String> getNamedHQLQueries();
/** * sql语句map * * @return */ public Map<String, String> getNamedSQLQueries();
/** * 初始化 * * @throws IOException */ public void init() throws IOException; } |
动态加载Sql的实现
package rongji.framework.base.dao.support;
import java.io.IOException; import java.util.HashMap; import java.util.HashSet; import java.util.Iterator; import java.util.Map; import java.util.Set;
import org.apache.commons.lang3.Validate; import org.dom4j.Document; import org.dom4j.Element; import org.hibernate.internal.util.xml.MappingReader; import org.hibernate.internal.util.xml.OriginImpl; import org.hibernate.internal.util.xml.XmlDocument; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.context.ResourceLoaderAware; import org.springframework.core.io.Resource; import org.springframework.core.io.ResourceLoader; import org.springframework.core.io.support.ResourcePatternResolver; import org.xml.sax.EntityResolver; import org.xml.sax.InputSource;
import rongji.framework.base.exception.ApplicationException;
public class DefaultDynamicHibernateStatementBuilder implements DynamicHibernateStatementBuilder, ResourceLoaderAware { private static final Logger LOGGER = LoggerFactory.getLogger(DefaultDynamicHibernateStatementBuilder.class); private Map<String, String> namedHQLQueries; private Map<String, String> namedSQLQueries; private String[] fileNames = new String[0]; private ResourceLoader resourceLoader; private EntityResolver entityResolver = new DynamicStatementDTDEntityResolver(); /** * 查询语句名称缓存,不允许重复 */ private Set<String> nameCache = new HashSet<String>();
public void setFileNames(String[] fileNames) { this.fileNames = fileNames; }
@Override public Map<String, String> getNamedHQLQueries() { return namedHQLQueries; }
@Override public Map<String, String> getNamedSQLQueries() { return namedSQLQueries; }
@Override public void init() throws IOException { namedHQLQueries = new HashMap<String, String>(); namedSQLQueries = new HashMap<String, String>(); boolean flag = this.resourceLoader instanceof ResourcePatternResolver; for (String file : fileNames) { if (flag) { Resource[] resources = ((ResourcePatternResolver) this.resourceLoader).getResources(file); buildMap(resources); } else { Resource resource = resourceLoader.getResource(file); buildMap(resource); } } // clear name cache nameCache.clear(); }
@Override public void setResourceLoader(ResourceLoader resourceLoader) { this.resourceLoader = resourceLoader; }
private void buildMap(Resource[] resources) throws IOException { if (resources == null) { return; } for (Resource resource : resources) { buildMap(resource); } }
@SuppressWarnings({ "rawtypes" }) private void buildMap(Resource resource) { InputSource inputSource = null; try { inputSource = new InputSource(resource.getInputStream()); XmlDocument metadataXml = MappingReader.INSTANCE.readMappingDocument(entityResolver, inputSource, new OriginImpl("file", resource.getFilename())); if (isDynamicStatementXml(metadataXml)) { final Document doc = metadataXml.getDocumentTree(); final Element dynamicHibernateStatement = doc.getRootElement(); Iterator rootChildren = dynamicHibernateStatement.elementIterator(); while (rootChildren.hasNext()) { final Element element = (Element) rootChildren.next(); final String elementName = element.getName(); if ("sql-query".equals(elementName)) { putStatementToCacheMap(resource, element, namedSQLQueries); } else if ("hql-query".equals(elementName)) { putStatementToCacheMap(resource, element, namedHQLQueries); } } } } catch (Exception e) { LOGGER.error(e.toString()); throw new ApplicationException(e); } finally { if (inputSource != null && inputSource.getByteStream() != null) { try { inputSource.getByteStream().close(); } catch (IOException e) { LOGGER.error(e.toString()); throw new ApplicationException(e); } } }
}
private void putStatementToCacheMap(Resource resource, final Element element, Map<String, String> statementMap) throws IOException { String sqlQueryName = element.attribute("name").getText(); Validate.notEmpty(sqlQueryName); if (nameCache.contains(sqlQueryName)) { throw new ApplicationException("重复的sql-query/hql-query语句定义在文件:" + resource.getURI() + "中,必须保证name的唯一."); } nameCache.add(sqlQueryName); String queryText = element.getText(); statementMap.put(sqlQueryName, queryText); }
private static boolean isDynamicStatementXml(XmlDocument xmlDocument) { return "dynamic-hibernate-statement".equals(xmlDocument.getDocumentTree().getRootElement().getName()); } } |
7.hibernateDao方法分装
/** * 模板缓存 */ protected Map<String, StatementTemplate> templateCache;
@Resource(name = "dynamicStatementBuilder") protected DynamicHibernateStatementBuilder dynamicStatementBuilder;
@Resource(name = "freeMarkerConfigurer") private FreeMarkerConfigurer freeMarkerConfigurer; |
/** * 查询在xxx-dynamic.xml中配置的查询语句 * * @param queryName * 查询的名称 * @param parameters * 参数 * @return */ public List<T> findByNamedQuery(final String queryName, final Map<String, ?> parameters) { StatementTemplate statementTemplate = templateCache.get(queryName); String statement = processTemplate(statementTemplate, parameters); if (statementTemplate.getType() == StatementTemplate.TYPE.HQL) { return this.findByHQL(statement, parameters); } else { return this.findBySQL(statement, parameters); } }
public String findQueryByNamed(final String queryName, final Map<String, ?> parameters) { StatementTemplate statementTemplate = templateCache.get(queryName); String statement = processTemplate(statementTemplate, parameters); return statement; }
/** * 查询在xxx-dynamic.xml中配置的查询语句 * * @param rowMapper * @param queryName * 查询的名称 * @param parameters * 参数 * @return */ @Override public <E> List<E> findByNamedQuery(RowMapper<E> rowMapper, final String queryName, final Map<String, ?> parameters) { StatementTemplate statementTemplate = templateCache.get(queryName); String statement = processTemplate(statementTemplate, parameters); if (statementTemplate.getType() == StatementTemplate.TYPE.HQL) { return this.findByHQLRowMapper(rowMapper, statement); } else { return this.findBySQLRowMapper(rowMapper, statement); } }
/** * 按HQL查询对象列表,并将对象封装成指定的对象 * * @param values * 数量可变的参数,按顺序绑定. */ @SuppressWarnings("unchecked") public <E> List<E> findByHQLRowMapper(RowMapper<E> rowMapper, final String hql, final Object... values) { Validate.notNull(rowMapper, "rowMapper不能为空!"); List<Object[]> result = createHQLQuery(hql, values).list(); return buildListResultFromRowMapper(rowMapper, result); }
/** * 按HQL查询对象列表,并将结果集封装成对象列表 * * @param values * 命名参数,按名称绑定. */ @SuppressWarnings("unchecked") public <E> List<E> findByHQLRowMapper(RowMapper<E> rowMapper, final String hql, final Map<String, ?> values) { Validate.notNull(rowMapper, "rowMapper不能为空!"); List<Object[]> result = createHQLQuery(hql, values).list(); return buildListResultFromRowMapper(rowMapper, result); }
/** * 按SQL查询对象列表. * * @param values * 数量可变的参数,按顺序绑定. */ @SuppressWarnings("unchecked") public <E> List<E> findBySQLRowMapper(RowMapper<E> rowMapper, final String sql, final Object... values) { Validate.notNull(rowMapper, "rowMapper不能为空!"); List<Object[]> result = createSQLQuery(sql, values).list(); return buildListResultFromRowMapper(rowMapper, result); }
/** * 按SQL查询对象列表,并将结果集封装成对象列表 * * @param sql * SQL查询语句 * @param values * 命名参数,按名称绑定. */ @SuppressWarnings("unchecked") public <X> List<X> findBySQLRowMapper(RowMapper<X> rowMapper, final String sql, final Map<String, ?> values) { Validate.notNull(rowMapper, "rowMapper不能为空!"); List<Object[]> result = createSQLQuery(sql, values).list(); return buildListResultFromRowMapper(rowMapper, result); }
protected <E> List<E> buildListResultFromRowMapper(RowMapper<E> rowMapper, List<Object[]> result) { List<E> rs = new ArrayList<E>(result.size()); for (Object[] obj : result) { rs.add(rowMapper.fromColumn(obj)); } return rs; }
@Override public void afterPropertiesSet() throws Exception { templateCache = new HashMap<String, StatementTemplate>(); dynamicStatementBuilder.init(); Map<String, String> namedHQLQueries = dynamicStatementBuilder.getNamedHQLQueries(); Map<String, String> namedSQLQueries = dynamicStatementBuilder.getNamedSQLQueries();
StringTemplateLoader stringLoader = new StringTemplateLoader(); for (Entry<String, String> entry : namedHQLQueries.entrySet()) { stringLoader.putTemplate(entry.getKey(), entry.getValue()); templateCache.put(entry.getKey(), new StatementTemplate(StatementTemplate.TYPE.HQL, new Template(entry.getKey(), new StringReader(entry.getValue()), freeMarkerConfigurer.getConfiguration()))); }
for (Entry<String, String> entry : namedSQLQueries.entrySet()) { stringLoader.putTemplate(entry.getKey(), entry.getValue()); templateCache.put(entry.getKey(), new StatementTemplate(StatementTemplate.TYPE.SQL, new Template(entry.getKey(), new StringReader(entry.getValue()), freeMarkerConfigurer.getConfiguration()))); } }
protected String processTemplate(StatementTemplate statementTemplate, Map<String, ?> parameters) { StringWriter stringWriter = new StringWriter(); try { statementTemplate.getTemplate().process(parameters, stringWriter); } catch (Exception e) { logger.error("处理DAO查询参数模板时发生错误:{}", e.toString()); throw new ApplicationException(e); } return stringWriter.toString(); } |
8.使用例子
@Override public void addColumn(SysColumnShow column) { try { Map<String, Object> parameters = new LinkedHashMap<String, Object>(); parameters.put("infoSet", column.getInfoSet()); if (Type.dataList.equals(column.getType())) { parameters.put("columnName", column.getPropertyCode()); } else { parameters.put("columnName", column.getPropertyName()); } String alterSql = infoCadreBasicAttributeDao.findQueryByNamed("infoSet.addColumn", parameters); dynamicInfoSetDao.addColumn(alterSql); } catch (ApplicationException e) { throw e; } catch (Exception e) { throw new ApplicationException("add column error!", e); } } |