一个处理SQL语句多条件添加的工具类

我们写SQL语句的时候，有时候查询条件非常多，并且可能有可能没有，如果手动拼写，可能由于疏忽少写空格，关键字等，并且添加条件的时候可能需要写大量的判断语句，非常不好。我写了一个工具类，很适合处理这种情况。源码如下。

package cn.zytx.common.db;

import java.util.*;
import java.util.regex.Pattern;


/**
 * SQL的模式
 *   SELECT .. FROM .. (LEFT|RIGHT|INNER) JOIN .. ON .. WHERE .... GROUP BY .. HAVING .. ORDER BY .. LIMIT ..
 * @author xiongshiyan
 */
public class QueryHelper{

    public static final boolean ASC = true;
    public static final boolean DESC = false;
    /**
     *  替换掉SQL注入的那些字符 ['|;|--| and | or ]
     */
    private static final String SQL_INJECT_CHARS = "([';]+|(--)+|(\\s+([aA][nN][dD])\\s+)+|(\\s+([oO][rR])\\s+)+)";
    /**
     *  开头是否包含关键字SELECT[不算空格],没有就加上
     */
    private static final Pattern INCLUDE_SELECT = Pattern.compile("^(\\s*[sS][eE][lL][eE][cC][tT])+.*");

    /**
     *  开头是否包含FROM关键字[不算空格],没有就加上
     */
    private static final Pattern INCLUDE_FROM   = Pattern.compile("^(\\s*[fF][rR][oO][mM])+.*");
    /**
     *  SQL语句的关键字
     */
    private static final String KW_SELECT       = "SELECT ";
    private static final String KW_FROM         = " FROM ";
    private static final String KW_ON           = " ON ";
    private static final String KW_LEFT_JOIN    = " LEFT JOIN ";
    private static final String KW_RIGHT_JOIN   = " RIGHT JOIN ";
    private static final String KW_INNER_JOIN   = " INNER JOIN ";
    private static final String KW_WHERE        = " WHERE ";
    private static final String KW_AND          = " AND ";
    private static final String KW_GROUP_BY     = " GROUP BY ";
    private static final String KW_HAVING       = " HAVING ";
    private static final String KW_ORDER_BY     = " ORDER BY ";
    private static final String KW_ASC          = " ASC ";
    private static final String KW_DESC         = " DESC ";
    private static final String KW_LIMIT        = " LIMIT ";
    private static final String COMMA           = " , ";
    private static final String BLANK           = " ";
    private static final String QUOTE           = "'";
    private static final String LEFT_BRAKET     = " ( ";
    private static final String RIGHT_BRAKET    = " ) ";
    private static final String KW_IN           = " IN ";

    /**
     * select语句
     */
    private String       select;
    /**
     * from子句
     */
    private StringBuilder fromClause    = new StringBuilder();
    /**
     * where子句
     */
    private StringBuilder whereClause   = new StringBuilder();
    /**
     * group by子句
     */
    private StringBuilder groupByClause = new StringBuilder();
    /**
     * having子句
     */
    private StringBuilder havingClause  = new StringBuilder();
    /**
     * order by子句
     */
    private StringBuilder orderByClause = new StringBuilder();
    /**
     * limit子句
     */
    private String       limitClause    = "";
    /**
     * 参数列表
     */
    private List<Object> parameters;
    /**
     * map类型的参数
     */
    private Map<String , Object> mapParameters;

    /**
     * 用于一张表的情况，生成From子句
     * from topic t
     */
    public QueryHelper(String select, String tableName, String alias){
        this.select = addSelectIfNecessary(select);
        fromClause.append(KW_FROM).append(tableName).append(BLANK).append(alias);
    }

    /**
     * 用于两张表联合的情况，生成From子句，类似from table1 a,table2 b 然后添加where条件
     * 另外像left join 这种可以写在一个from字句中或者使用 leftJoin rightJoin innerJoin方法
     */
    public QueryHelper(String select, String... froms){
        this.select = addSelectIfNecessary(select);
        String prefix = KW_FROM ;
        if(INCLUDE_FROM.matcher(froms[0]).matches()){
            prefix = "" ;
        }
        fromClause.append(join(COMMA, prefix, froms));
    }

    /**
     * 添加left join子句
     * @param joinClause LEFT JOIN 子句
     * @param on on条件 有一个添加在后面 , 不要带 ON 了 , 没有必须使用on方法添加
     */
    public QueryHelper leftJoin(String joinClause , String on){
        fromClause.append(KW_LEFT_JOIN).append(joinClause);
        fromClause.append(join(BLANK, KW_ON, on));
        return this;
    }
    public QueryHelper leftJoin(String joinClause){
        fromClause.append(KW_LEFT_JOIN).append(joinClause);
        return this;
    }
    /**
     * 添加right join子句
     * @param joinClause RIGHT JOIN 子句
     */
    public QueryHelper rightJoin(String joinClause , String on){
        fromClause.append(KW_RIGHT_JOIN).append(joinClause);
        fromClause.append(join(BLANK, KW_ON , on));
        return this;
    }
    public QueryHelper rightJoin(String joinClause){
        fromClause.append(KW_RIGHT_JOIN).append(joinClause);
        return this;
    }
    /**
     * 添加inner join子句
     * @param joinClause INNER JOIN 子句
     */
    public QueryHelper innerJoin(String joinClause , String on){
        fromClause.append(KW_INNER_JOIN).append(joinClause);
        fromClause.append(join(BLANK, KW_ON , on));
        return this;
    }
    public QueryHelper innerJoin(String joinClause){
        fromClause.append(KW_INNER_JOIN).append(joinClause);
        return this;
    }

    /**
     * 添加on子句 , 不要带ON 了 , 可以被 left、right、inner join子句使用  , 但是必须紧跟在JOIN 子句后面
     * @param onClause ON 子句
     */
    public QueryHelper on(String onClause){
        fromClause.append(KW_ON).append(onClause);
        return this;
    }

    private String addSelectIfNecessary(String select) {
        if(INCLUDE_SELECT.matcher(select).matches()){
            //包含了select
            return select;
        }else {
            //没有包含select
            return KW_SELECT + select;
        }
    }

    private static String join(String separator , String prefix , String... parts){
        if(null == parts || 0 == parts.length){ throw new IllegalArgumentException("parts must more than one");}
        StringBuilder buffer = new StringBuilder(prefix).append(BLANK);
        for(String part : parts){
            buffer.append(part).append(separator);
        }
        int i = buffer.lastIndexOf(separator);
        //去掉最后的separator
        return buffer.substring(0 , i);
    }

    public QueryHelper addCondition(String condition){
        // 拼接条件
        addWhere(condition);
        return this;
    }

    /**
     * 拼接where子句 d.id between ? and ? d.parent=? d.parent is null
     *
     * @param condition 具体条件
     * @param params 参数,QueryHelper只支持？参数，如果你想用Query的具名参数，就不要设置参数，产生{Query}后再调用setParameter设置
     */
    public QueryHelper addCondition(String condition, Object... params){
        // 拼接条件
        addWhere(condition);

        // 添加参数
        addParams(params);

        return this;
    }

    /**
     * @param append 是否拼装此条件
     * @param condition 具体条件
     * @param params 参数
     */
    public QueryHelper addCondition(boolean append, String condition, Object... params){
        if(append){
            addCondition(condition, params);
        }
        return this;
    }

    /**
     * @param append 是否拼装此条件
     * @param condition 具体条件
     * @param keyValue 参数
     */
    public QueryHelper addMapCondition(boolean append, String condition, Object... keyValue){
        if(append){
            addMapCondition(condition, keyValue);
        }
        return this;
    }

    /**
     * 主要是为了支持某些框架中的具名参数
     * @param condition 具体条件
     * @param keyValue 模式k1,v1,k2,v2...(k1,k2必须是String)
     */
    public QueryHelper addMapCondition(String condition, Object... keyValue){
        // 拼接参数
        addWhere(condition);

        //添加map类型参数k1,v1,k2,v2...
        addMapParams(keyValue);

        return this;
    }

    private void addWhere(String condition) {
        // 拼接
        if(whereClause.length() == 0){
            whereClause = new StringBuilder(KW_WHERE).append(condition);
        } else{
            whereClause.append(KW_AND).append(condition);
        }
    }

    /**
     * addIn("d.id" , 1,2,3) - > d.id IN (1,2,3)
     * addIn("d.phone" , "1","2","3") - > d.id IN ('1','2','3')
     * @param what 添加 IN 语句
     * @param ins In条件
     */
    public <T> QueryHelper addIn(String what , T... ins){
        if(null == ins || ins.length == 0){
            throw new IllegalArgumentException("必须至少包含一个in条件");
        }
        // 拼接
        if(whereClause.length() == 0){
            whereClause = new StringBuilder(KW_WHERE);
        } else{
            whereClause.append(KW_AND);
        }
        // 添加左括号
        whereClause.append(what).append(KW_IN).append(LEFT_BRAKET);
        for(Object part : ins){
            //数字不需要'' , 其他就转化为字符串并加上''
            String x = part instanceof Number ? part.toString() : QUOTE + part + QUOTE;
            whereClause.append(x).append(COMMA);
        }
        // 去掉最后的 ,
        whereClause = new StringBuilder(whereClause.substring(0 , whereClause.lastIndexOf(COMMA)));
        //添加右括号
        whereClause.append(RIGHT_BRAKET);

        return this;
    }

    /**
     * 拼接order by子句
     * @param propertyName 参与排序的属性名
     * @param asc true表示升序，false表示降序
     */
    public QueryHelper addOrderProperty(String propertyName, boolean asc){
        if(orderByClause.length() == 0){
            orderByClause = new StringBuilder(KW_ORDER_BY).append(propertyName + (asc ? KW_ASC : KW_DESC));
        } else{
            orderByClause.append(COMMA).append(propertyName + (asc ? KW_ASC : KW_DESC));
        }
        return this;
    }
    public QueryHelper addAscOrderProperty(String propertyName){
        return addOrderProperty(propertyName , ASC);
    }
    public QueryHelper addDescOrderProperty(String propertyName){
        return addOrderProperty(propertyName , DESC);
    }

    /**
     * @param append 是否拼装这个排序
     * @param propertyName 排序属性
     * @param asc true表示升序，false表示降序
     */
    public QueryHelper addOrderProperty(boolean append, String propertyName, boolean asc){
        if(append){
            addOrderProperty(propertyName, asc);
        }
        return this;
    }
    public QueryHelper addAscOrderProperty(boolean append, String propertyName){
        if(append){
            addOrderProperty(propertyName, ASC);
        }
        return this;
    }
    public QueryHelper addDescOrderProperty(boolean append, String propertyName){
        if(append){
            addOrderProperty(propertyName, DESC);
        }
        return this;
    }

    public QueryHelper addGroupProperty(String groupByName){
        if(groupByClause.length() == 0){
            groupByClause = new StringBuilder(KW_GROUP_BY).append(groupByName);
        } else{
            groupByClause.append(COMMA).append(groupByName);
        }
        return this;
    }


    /**
     * 是否添加此having子句
     * @see QueryHelper#addHaving(String, Object...)
     */
    public QueryHelper addHaving(boolean append , String having , Object... params){
        if(!append){
            return this;
        }
        return addHaving(having , params);
    }

    /**
     * @param having having子句
     * @param params 参数
     */
    public QueryHelper addHaving(String having , Object... params){
        if(havingClause.length() == 0){
            havingClause = new StringBuilder(KW_HAVING).append(having);
        } else{
            havingClause.append(KW_AND).append(having);
        }

        addParams(params);

        return this;
    }

    public QueryHelper addMapHaving(boolean append , String having, Object... keyValue){
        if(!append){return this;}

        return addMapHaving(having , keyValue);
    }

    /**
     * 主要是为了支持某些框架中的具名参数
     * @param having having子句
     * @param keyValue 模式k1,v1,k2,v2...
     */
    public QueryHelper addMapHaving(String having, Object... keyValue){
        // 拼接having
        addHaving(having);

        //增加map参数
        addMapParams(keyValue);

        return this;
    }

    private void addMapParams(Object... keyValue) {
        if(0 != (keyValue.length % 2)){
            throw new IllegalArgumentException("参数必须符合模式k1,v1,k2,v2...");
        }
        if(null == mapParameters){
            mapParameters = new LinkedHashMap<>();
        }
        int kvLen = keyValue.length / 2;
        for (int i = 0; i < kvLen; i++) {
            mapParameters.put(keyValue[i].toString() , keyValue[i+1]);
        }
    }


    private void addParams(Object... params) {
        // 参数
        if(params != null){
            if(null == parameters){
                parameters = new LinkedList<>();
            }
            for(Object p : params){
                if(p != null){
                    parameters.add(p);
                }
            }
        }
    }

    public QueryHelper addLimit(int pageNumber , int pageSize){
        int thisPage = (pageNumber - 1) * pageSize;
        limitClause = KW_LIMIT + thisPage + COMMA + pageSize;
        return this;
    }

    public String getSelect(){
        return this.select;
    }

    /**
     * From后面的所有语句 , 处理了 ? 参数的
     * @see QueryHelper#getSqlExceptSelectWithoutPadding()
     */
    public String getSqlExceptSelect(){
        return paddingParam(getSqlExceptSelectWithoutPadding() ,parameters);
    }

    /**
     * From后面的所有语句 , 没有处理 ?
     * @see QueryHelper#getSqlExceptSelect()
     */
    public String getSqlExceptSelectWithoutPadding(){
        return fromClause.append(whereClause).append(groupByClause).append(havingClause)
                .append(orderByClause).append(limitClause).toString();
    }

    /**
     * 获取最终拼装的SQL , 并且处理了 ? 参数的
     * @see QueryHelper#getSqlWithoutPadding
     */
    public String getSql(){
        return paddingParam(getSqlWithoutPadding() , parameters);
    }

    /**
     * 获取最终拼装的SQL , 没有处理 ?
     * @see QueryHelper#getSql()
     */
    public String getSqlWithoutPadding(){
        return new StringBuilder(select).append(fromClause).append(whereClause).append(groupByClause).append(havingClause)
                .append(orderByClause).append(limitClause).toString();
    }

    private String paddingParam(String sql , List<Object> params) {
        // 填充参数
        if(params != null){
            for(int i = 0 , size = params.size(); i < size; i++){
                // 1.巧妙利用替换一次之后，后面的?就自动往前移动一位，那么replaceFirst每次替换的就是下一个?
                // 2.去掉某些特殊符号，防注入
                String param = (parameters.get(i) instanceof Number) ? parameters.get(i) + "" :
                        QUOTE + parameters.get(i).toString().replaceAll(SQL_INJECT_CHARS, "")
                        + QUOTE;
                sql = sql.replaceFirst("\\?", param);
            }
        }
        return sql;
    }


    /**
     * 获取生成的用于查询总记录数的SQL语句 , 并且处理了 ? 参数的
     * @see QueryHelper#getCountQuerySqlWithoutPadding()
     */
    public String getCountQuerySql(){
        return paddingParam(getCountQuerySqlWithoutPadding(), parameters);
    }
    /**
     * 获取生成的用于查询总记录数的SQL语句 , 没有处理 ?
     * @see QueryHelper#getCountQuerySql()
     * @see QueryHelper#getSqlWithoutPadding()
     */
    public String getCountQuerySqlWithoutPadding(){
        return  KW_SELECT + " COUNT(*) AS totalRow " + fromClause + whereClause + groupByClause + havingClause;
    }

    /**
     * 获取SQL中的参数值列表，List返回
     * @see QueryHelper#addCondition(String)
     * @see QueryHelper#addMapHaving(String, Object...)
     */
    public List<Object> getListParameters(){
        if(null == parameters){
            return new LinkedList<>();
        }
        return parameters;
    }

    /**
     * 获取SQL中的参数值列表，Array返回
     * @see QueryHelper#addCondition(String)
     */
    public Object[] getArrayParameters(){
        if(null == parameters){
            return new Object[0];
        }
        return parameters.toArray();
    }

    /**
     * 获取SQL中的参数值列表，Map返回
     * @see QueryHelper#addMapCondition(String, Object...)
     * @see QueryHelper#addMapHaving(String, Object...)
     */
    public Map<String, Object> getMapParameters() {
        if(null == mapParameters){
            return new LinkedHashMap<>();
        }
        return mapParameters;
    }

    @Override
    public String toString() {
        return "QueryHelper{" +
                "sql=" + getSql() +
                ", parameters=" + getListParameters() +
                ", mapParameters=" + getMapParameters() +
                '}';
    }
}

测试及使用方式

package cn.zytx.common.db;

import org.junit.Test;

/**
 * @author xiongshiyan at 2018/5/10
 * QueryHelper的简单测试，也是介绍其用法
 */
public class QueryHelperTest {
    /**
     * addCondition、addGroupProperty、addOrderProperty、addLimit
     */
    @Test
    public void testNormal(){
        QueryHelper helper = new QueryHelper("  tsu.name AS moshaoName,tsu.user_name AS moshaoUserName,COUNT(*) AS useCard ,tsu.remain_blank_card AS sellingCard,(COUNT(*)+tsu.remain_blank_card) AS cardCount , p.name AS managerName,p.user_name AS managerUserName,pp.name AS agentName,pp.user_name AS agentUserName",
                "tcm_cmcc_order_extend tcoe LEFT JOIN tcm_spreader_user tsu ON tsu.id=tcoe.user_id LEFT JOIN tcm_spreader_user p ON p.id=tsu.parent_user_id LEFT JOIN tcm_spreader_user pp ON pp.id=p.parent_user_id");
        //helper.addCondition("tcoe.tcm_state=0");
        helper.addCondition("tsu.name like ?" , "sdas and ' d And Or");
        helper.addGroupProperty("tcoe.user_id");
        helper.addOrderProperty("tcoe.user_id" ,true);
        helper.addLimit(1,10);
        System.out.println(helper.getSql());
    }

    /**
     * QueryHelper帮助拼装参数，能处理?
     */
    @Test
    public void testPositionParam(){
        QueryHelper helper = new QueryHelper("SELECT tsu.name AS moshaoName,tsu.user_name AS moshaoUserName,COUNT(*) AS useCard ,tsu.remain_blank_card AS sellingCard,(COUNT(*)+tsu.remain_blank_card) AS cardCount , p.name AS managerName,p.user_name AS managerUserName,pp.name AS agentName,pp.user_name AS agentUserName",
                "tcm_cmcc_order_extend tcoe LEFT JOIN tcm_spreader_user tsu ON tsu.id=tcoe.user_id LEFT JOIN tcm_spreader_user p ON p.id=tsu.parent_user_id LEFT JOIN tcm_spreader_user pp ON pp.id=p.parent_user_id");
        helper.addCondition("tcoe.tcm_state=?" , 0);
        helper.addCondition(3>2 ,"tcoe.user_id=?" , 12445);
        System.out.println(helper.getSql());
        System.out.println(helper.getListParameters());

        printArray(helper.getArrayParameters());
    }

    private void printArray(Object... objects){
        for (Object object : objects) {
            System.out.println(object);
        }
    }

    /**
     * QueryHelper简单拼装 :参数名
     */
    @Test
    public void testNamedParamMap(){
        QueryHelper helper = new QueryHelper("SELECT *", "tcm_cmcc_order_extend" , "tcoe");
        helper.addMapCondition("tcoe.tcm_state=:state" , "state" , 0);
        helper.addMapCondition("tcoe.user_id=:userId" , "userId" , "123");
        System.out.println(helper);
        System.out.println(helper.getMapParameters());
    }

    @Test
    public void testHaving(){
        QueryHelper helper = new QueryHelper("SELECT count(*)", "tcm_cmcc_order_extend" , "tcoe");
        helper.addGroupProperty("tcoe.id");
        helper.addMapHaving("count(*)>:ss" , "ss" , 1);
        System.out.println(helper.getSql());
        System.out.println(helper.getMapParameters());
    }

    @Test
    public void testLeftJoin(){
        QueryHelper helper = new QueryHelper("SELECT *", "tcm_cmcc_order_extend" , "tcoe");
        helper.leftJoin("cmcc co", "co.id=tcoe.cmcc_id");
        helper.leftJoin("member_org o").on("o.id=tcoe.user_id");
        System.out.println(helper.getSql());
    }
    @Test
    public void testAddIn(){
        QueryHelper helper = new QueryHelper("SELECT *", "tcm_cmcc_order_extend" , "tcoe");
        helper.addIn("tcoe.id" , 1,2,3,4);
        helper.addIn("tcoe.phone" , "15208384257");
        System.out.println(helper.getSql());
    }
    @Test
    public void testAddParams(){
        QueryHelper helper = new QueryHelper("SELECT *", "tcm_cmcc_order_extend tcoe" , "cmcc co" , "member_org mo");
        helper.addCondition("tcoe.cmcc_id=co.id");
        helper.addCondition("tcoe.user_id=mo.id");
        System.out.println(helper.getSql());
    }
    @Test
    public void testAll(){
        QueryHelper helper = new QueryHelper("SELECT tcoe.user_id", "tcm_cmcc_order_extend tcoe" , "cmcc co" , "member_org mo");
        helper.leftJoin("organization o" , "o.id=mo.org_id");
        helper.leftJoin("organization_class oc").on("oc.id=o.WebsiteId");
        helper.addCondition("tcoe.cmcc_id=co.id");
        helper.addCondition("tcoe.user_id=mo.id");
        helper.addCondition(1>0 , "tcoe.id>?" , 12);
        helper.addGroupProperty("tcoe.user_id");
        helper.addHaving("SUM(co.order_id) > 10");
        helper.addDescOrderProperty("SUM(co.order_id)");
        helper.addLimit(1,10);
        System.out.println(helper);
    }
}