开源项目Druid的提取SQL模板

在数据库审计中，常常用到SQL模板，这样提取一次模板，下一次就不用对相同的模板的SQL进行相关操作。对此Druid提供相应的工具类进行SQL模板提取：

package com.lhm;
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.visitor.ParameterizedOutputVisitorUtils;
import com.alibaba.druid.util.JdbcConstants;

/**
  * @author :haimli
  * @email : [e-mail]
  * @datetime:2017年9月12日下午7:26:27
  */
public class SQLFormat {

    public static void main(String[] args) {
        // TODO Auto-generated method stub
        //给定需要格式化的SQL语句
        //String sql = "select id,name from lhm where id=1 and name='lihaiming' and 1=3";
        //String sql = "select a,b from c where a='33' and b='ddd'";
        String sql = "select b,a from c where a='33' and b='ddd' and a in ('33','332','334')";

        //指定数据库类型
        String dbtype = JdbcConstants.MYSQL;
        String fs = ParameterizedOutputVisitorUtils.parameterize(sql, dbtype);

        System.out.println("fs:\n"+fs);
    }
}

上面是一个很简单的一个SQL：

select b,a from c where a='' and b='ddd' and a in ('','','')

对此我们使用相应的提取SQL模板的工具提取模板：

SELECT b, a
FROM c
WHERE a = ?
AND b = ?
AND a IN (?)

使用比较复杂的SQL语句：

String sql = "select rownum,organ_level4,organ_name4,user_no,user_name,pzl_num,xf_ccn from (select t.organ_level4,t.organ_name4,t.user_no,t.user_name,sum(t.pzl_num) pzl_num,sum(t.xf_ccn) xf_ccn from rt_smtj_tb t,sm_user_organ_tb a,sm_organ_tb b where 2>1 and a.organ_no=b.organ_no and b.organ_no=t.organ_level4 and b.organ_level=4 and a.user_no='#DEAL_USERNO#' group by t.organ_level4,t.organ_name4,t.user_no,t.user_name order by sum(t.pzl_num) desc,user_no)";
//String sql = "select id,name from lhm where id=1 and name='lihaiming' and 1=3";
//String sql = "select a,b from c where a='33' and b='ddd'";
//String sql = "select b,a from c where a='33' and b='ddd' and a in ('33','332','334')";

//指定数据库类型
String dbtype = JdbcConstants.MYSQL;
String fs = ParameterizedOutputVisitorUtils.parameterize(sql, dbtype);

System.out.println("fs:\n"+fs);

再使用比较复杂的SQL语句：

select rownum,
organ_level4,
organ_name4,
user_no,
user_name,
pzl_num,
xf_ccn
from (select t.organ_level4,
t.organ_name4,
t.user_no,
t.user_name,
sum(t.pzl_num) pzl_num,
sum(t.xf_ccn) xf_ccn
from rt_smtj_tb t, sm_user_organ_tb a, sm_organ_tb b
where 2 > 1
and a.organ_no = b.organ_no
and b.organ_no = t.organ_level4
and b.organ_level = 4
and a.user_no = '#DEAL_USERNO#'
group by t.organ_level4, t.organ_name4, t.user_no, t.user_name
order by sum(t.pzl_num) desc, user_no)

 

提取SQL模板:

SELECT rownum, organ_level4, organ_name4, user_no, user_name
, pzl_num, xf_ccn
FROM (
SELECT t.organ_level4, t.organ_name4, t.user_no, t.user_name
, SUM(t.pzl_num) AS pzl_num, SUM(t.xf_ccn) AS xf_ccn
FROM rt_smtj_tb t, sm_user_organ_tb a, sm_organ_tb b
WHERE ? > ?
AND a.organ_no = b.organ_no
AND b.organ_no = t.organ_level4
AND b.organ_level = ?
AND a.user_no = ?
GROUP BY t.organ_level4, t.organ_name4, t.user_no, t.user_name
ORDER BY SUM(t.pzl_num) DESC, user_no
)

 

注意事项：提取模板的时候SQL语句必须是正确的，这样才能保证提取模板的时候不会报错；