欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-06-05 09:28:32
...

我们写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);
    }
}





相关标签: SQL语句拼写器