使用freemarker生成sql实例

1.使用freemarket 生成sql语句

 

Index.xml:

 

<?xml version="1.0" encoding="UTF-8"?>
<sqls>
	<sql id="queryAccountTransfers"><![CDATA[
	<#assign where>
	   <#if date??>
	      and transferTime>=:date
	    </#if>
	    <#if date_end??>
	      and transferTime<=:date_end
	    </#if>
	    <#if money??>
	      and srcMoney>=:money
	    </#if>
	    <#if money_end??>
	      and srcMoney<=:money_end
	    </#if>
	    <#if scrAccountId??>
	      and srcAccountId=:scrAccountId
	    </#if>
	    <#if tgtAccountId??>
	      and tgtAccountId=:tgtAccountId
	    </#if>
	  </#assign>

    <#assign where=where?trim>
    select *
    from ${tbl_main}
    where isdelete=0 and userid=${userid}
    ${where}

	]]></sql>
</sqls>

 

2.调用，与Index.xml在同一级目录

 

	public static void main(String[] arg) {
		StringTemplateLoader STL = new StringTemplateLoader();
		Configuration CONFIG = new Configuration();
		CONFIG.setTemplateLoader(STL);

		InputStream is = null;
		try {
			is = Index.class.getResourceAsStream("Index.xml");
			Document doc = DocumentHelper.parseText(IOUtils.toString(is));
			List<Element> es = doc.selectNodes("sqls/sql");
			for (Element e : es)
				STL.putTemplate(e.attributeValue("id"), e.getTextTrim());
		} catch (Exception e) {
			throw new IllegalStateException(e);
		} finally {
			IOUtils.closeQuietly(is);
		}
		Map cond = new HashMap<String, Object>();
		cond.put("userid", "'1'");
		cond.put("tbl_main", "t_accountTransfer");
		StringWriter sw = new StringWriter();
		try {
			//生成sql
			CONFIG.getTemplate("queryAccountTransfers").process(cond, sw);
			//打印
			System.out.println(sw);
		} catch (Exception e) {
			throw new RuntimeException(e);
		}

	}

 

3. 

 

select * from t_accountTransfer where isdelete=0 and userid= '1'