SpringBoot+JPA+Freemarker 生成动态SQL

「爱情、让人受尽委屈。」 2022-05-11 08:24 419阅读 0赞

在实际开发中，有时候会遇到复杂的统计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文件格式**

<!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>  
        <!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_download.csdn.net_download_dashuaigege642_10681049]

参考：

> [https://blog.csdn.net/crazycoder2010/article/details/7414152][https_blog.csdn.net_crazycoder2010_article_details_7414152]

[https_download.csdn.net_download_dashuaigege642_10681049]: https://download.csdn.net/download/dashuaigege642/10681049
[https_blog.csdn.net_crazycoder2010_article_details_7414152]: https://blog.csdn.net/crazycoder2010/article/details/7414152