SpringBoot+JPA+Freemarker 生成动态SQL

在实际开发中，有时候会遇到复杂的统计SQL，这时候Hibernate并不能很好的满足我们的需求，一方面SQL语句写在代码中不美观，可读性比较差，另一方面不方便修改SQL语句。
如果可以像mybaits一样写在XML中的话，就方便许多了。
采用Freemarker模板就可以达到将SQL写在XML文件中的功能。

一、pom引用

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
			<exclusions><!-- 去掉默认配置 -->
				<exclusion>
					<groupId>org.springframework.boot</groupId>
					<artifactId>spring-boot-starter-logging</artifactId>
				</exclusion>
			</exclusions>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-freemarker</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-log4j2</artifactId>
		</dependency>

二、写个DTD文件，定义XML文件格式

<!-- HOP Hibernate Dynamic Statement Mapping DTD.  
  
<!DOCTYPE dynamic-hibernate-statement PUBLIC   
    "-//Haier/HOP Hibernate Dynamic Statement DTD 1.0//EN"  
    "http://www.wongws.com/dtd/dynamic-hibernate-statement-1.0.dtd">  
  
这个文件时用来定义动态参数语句，类似itabis  
  
-->  
  
<!-- 
    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>  
    <!ATTLIST sql-query mode (normal|jdbc|namedJdbc) "normal">

三、DTD解析器

public class DynamicStatementDTDEntityResolver implements EntityResolver {

	private static final Logger LOGGER = LoggerFactory.getLogger(DynamicStatementDTDEntityResolver.class);
	private static final String HOP_DYNAMIC_STATEMENT = "http://www.wongws.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 com/haier/openplatform/dao/hibernate/");
				source = resolveOnClassPath(publicId, systemId, HOP_DYNAMIC_STATEMENT);
			}
		}
		return source;
	}

	private InputSource resolveOnClassPath(String publicId, String systemId, String namespace) {
		InputSource source = null;
		String path = "dtd/" + 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);
	}

}

四、构造存储动态SQL的对象

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加载器

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)  {
		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 = 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 RuntimeException(e);
		} finally {
			if (inputSource != null && inputSource.getByteStream() != null) {
				try {
					inputSource.getByteStream().close();
				} catch (IOException e) {
					LOGGER.error(e.toString());
					throw new RuntimeException(e);
				}
			}
		}

	}

	private void putStatementToCacheMap(Resource resource, final Element element, Map<String, String> statementMap)
			throws Exception {
		String sqlQueryName = element.attribute("name").getText();
		 Validate.notEmpty(sqlQueryName);
		
		if (nameCache.contains(sqlQueryName)) {
			throw new RuntimeException("重复的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());
	}

	public XmlDocument readMappingDocument(EntityResolver entityResolver, InputSource source, Origin origin) {
		return legacyReadMappingDocument(entityResolver, source, origin);
//		return readMappingDocument( source, origin );
	}

	private XmlDocument legacyReadMappingDocument(EntityResolver entityResolver, InputSource source, Origin origin) {
		// IMPL NOTE : this is the legacy logic as pulled from the old
		// AnnotationConfiguration code

		Exception failure;

		ErrorLogger errorHandler = new ErrorLogger();

		SAXReader saxReader = new SAXReader();
		saxReader.setEntityResolver(entityResolver);
		saxReader.setErrorHandler(errorHandler);
		saxReader.setMergeAdjacentText(true);
		saxReader.setValidation(true);

		Document document = null;
		try {
			// first try with orm 2.1 xsd validation
			setValidationFor(saxReader, "orm_2_1.xsd");
			document = saxReader.read(source);
			if (errorHandler.hasErrors()) {
				throw errorHandler.getErrors().get(0);
			}
			return new XmlDocumentImpl(document, origin.getType(), origin.getName());
		} catch (Exception e) {

			LOGGER.debug("Problem parsing XML using orm 2.1 xsd, trying 2.0 xsd : {}", e.getMessage());

			failure = e;
			errorHandler.reset();

			if (document != null) {
				// next try with orm 2.0 xsd validation
				try {
					setValidationFor(saxReader, "orm_2_0.xsd");
					document = saxReader.read(new StringReader(document.asXML()));
					if (errorHandler.hasErrors()) {
						errorHandler.logErrors();
						throw errorHandler.getErrors().get(0);
					}
					return new XmlDocumentImpl(document, origin.getType(), origin.getName());
				} catch (Exception e2) {

					LOGGER.debug("Problem parsing XML using orm 2.0 xsd, trying 1.0 xsd : {}", e2.getMessage());

					errorHandler.reset();

					if (document != null) {
						// next try with orm 1.0 xsd validation
						try {
							setValidationFor(saxReader, "orm_1_0.xsd");
							document = saxReader.read(new StringReader(document.asXML()));
							if (errorHandler.hasErrors()) {
								errorHandler.logErrors();
								throw errorHandler.getErrors().get(0);
							}
							return new XmlDocumentImpl(document, origin.getType(), origin.getName());
						} catch (Exception e3) {

							LOGGER.debug("Problem parsing XML using orm 1.0 xsd : {}", e3.getMessage());

						}
					}
				}
			}
		}
		throw new InvalidMappingException("Unable to read XML", origin.getType(), origin.getName(), failure);
	}

	private void setValidationFor(SAXReader saxReader, String xsd) {
		try {
			saxReader.setFeature("http://apache.org/xml/features/validation/schema", true);
			// saxReader.setFeature( "http://apache.org/xml/features/validation/dynamic",
			// true );
			if ("orm_2_1.xsd".equals(xsd)) {
				saxReader.setProperty("http://apache.org/xml/properties/schema/external-schemaLocation",
						"http://xmlns.jcp.org/xml/ns/persistence/orm " + xsd);
			} else {
				saxReader.setProperty("http://apache.org/xml/properties/schema/external-schemaLocation",
						"http://java.sun.com/xml/ns/persistence/orm " + xsd);
			}
		} catch (SAXException e) {
			saxReader.setValidation(false);
		}
	}

}

public interface DynamicHibernateStatementBuilder {
	/**
	 * hql语句map
	 * 
	 * @return
	 */
	Map<String, String> getNamedHQLQueries();

	/**
	 * sql语句map
	 * 
	 * @return
	 */
	Map<String, String> getNamedSQLQueries();

	/**
	 * 初始化
	 * 
	 * @throws IOException
	 */
	void init() throws IOException;

	void setFileNames(String[] fileNames);

	void setResourceLoader(ResourceLoader resourceLoader);
}

六、利用Freemarker模板书写动态SQL

<?xml version="1.0" encoding="utf-8"?>  
<!DOCTYPE dynamic-hibernate-statement PUBLIC "-//Haier/HOP Hibernate Dynamic Statement DTD 1.0//EN"  
"http://www.wongws.com/dtd/dynamic-hibernate-statement-1.0.dtd">

<dynamic-hibernate-statement>

	<sql-query name="resource.getUser" mode="normal">  
    <![CDATA[ 
     
       select * from t_user t where t.user_id=${userid}
         <#if password??> 
            and t.password=${password} 
         </#if> 
    ]]>
	</sql-query>
</dynamic-hibernate-statement>  

以上就是功能实现的关键代码，但是利用Freemarker有个问题，就是没法解决SQL注入的问题，这里我的操作就是可以使用Freemarker生成动态SQL，然后再利用JdbcTemplate或NamedParameterJdbcTemplate来执行SQL。如将上面的SQL改写成这样：

	<sql-query name="resource.getUser" mode="normal">  
    <![CDATA[ 
     
       select * from t_user t where t.user_id=:userid
         <#if password??> 
            and t.password=:password
         </#if> 
    ]]>
	</sql-query>

这样的话就可以绕过Freemarker的占位符使用，然后再通过NamedParameterJdbcTemplate执行SQL。
当然实际SQL不可能这么简单，肯定会遇到Map或者List等问题，这些只要网上查询Freemarker的语法，就能迎刃而解。
下面奉上源码，由于这只是个Demo，代码实在是粗制滥造了点，但是功能还是能够实现的，在com.example.demo下的DemoApplicationTests类上，运行Junit Test。当然，要是实现mysql访问，还得自己配数据库连接。

https://download.csdn.net/download/dashuaigege642/10681049

参考：

https://blog.csdn.net/crazycoder2010/article/details/7414152