基于Freemarker动态SQL构造工具

一.目的

将sql语句编写的xml中，然后在java代码中，传入参数，经过freemarker处理，得到处理完的SQL. 好处:

• sql编写在xml中，便于阅读
• 可以使用freemarker语法，动态构建SQL
• 可以使用freemarker的include语句，提取公用SQL

 

 

 

三.使用指南

 

maven

 

        <groupId>com.duowan.common</groupId>
        <artifactId>duowan-common-util</artifactId>
        <version>1.0.11</version>       

 

spring配置

 

        <bean id="sqlFreeMarkerConfiguration" class="org.springframework.ui.freemarker.FreeMarkerConfigurationFactoryBean" >
        <property name="preTemplateLoaders" >
                <bean id="templateLoader" class="com.duowan.common.freemarker.loader.MapTemplateLoader">
                                <property name="templateMap" ref="sqlTemplateProperties"></property>
                </bean>
        </property>
        </bean>
        
    <bean id="sqlTemplateProperties" class="org.springframework.beans.factory.config.PropertiesFactoryBean">
        <property name="ignoreResourceNotFound" value="true" />
                <property name="locations">
                        <list>
                                <value>classpath*:/freemarker_sql/**/*.xml</value>
                        </list>
                </property>
    </bean>

 

xml配置中编写SQL

user.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>

        <entry key="user.delete">
                <![CDATA[
                delete from user where id=:id
                ]]>
        </entry>
        
        <entry key="user.select">
                <![CDATA[
                select * from user
                <#include "userWhere">
                ]]>
        </entry>
        
        <entry key="userWhere">
                <![CDATA[
                1=1
                ]]>
        </entry>
        
</properties>

 

java代码

 

 

        @Test
        public void test() throws IOException, TemplateException {
                Map model = new HashMap();
                model.put("name", "badqiu");
                model.put("sex", 1);
                model.put("age", 100);
                
                ClassPathXmlApplicationContext ac = new ClassPathXmlApplicationContext("classpath:spring/applicationContext-freemarker-sql.xml");
                Configuration conf = (Configuration)ac.getBean("sqlFreeMarkerConfiguration");
                String sql = FreeMarkerTemplateUtils.processTemplateIntoString(conf.getTemplate("user.select"), model);
                System.out.println(sql);
        }