Hibernate动态查询

来源：互联网发布：部落冲突英雄升级数据编辑：程序博客网时间：2024/06/06 01:46

使用hibernate作为持久层框架时，如果查询需要根据查询条件的不同而构造不同的查询sql，这就需要在hibernate 之上做一层分装。下面的总体思路就是结合freemarker模板引擎来实现参数的不同而生成不同的动态sql。

<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配置

<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="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>

<map>

</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)*

<!ELEMENT hql-query (#PCDATA)>

<!ATTLIST hql-query name CDATA #REQUIRED>

<!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);

}

0 0