cn.net.vidyo.yd.common.data.dao.SqlHelper Maven / Gradle / Ivy

package cn.net.vidyo.yd.common.data.dao;

import java.util.ArrayList;
import java.util.List;

public class SqlHelper {

    public static SqlHelper instance(){
        return new SqlHelper();
    }

    public enum LOGIC{
        AND,
        OR,
        NOT,
        EMPT
    }
    SqlItem selectItem =new SqlItem();
    SqlItem whereItem=new SqlItem();

    public SqlItem getSelectItem() {
        return selectItem;
    }

    public void setSelectItem(SqlItem selectItem) {
        this.selectItem = selectItem;
    }

    public SqlItem getWhereItem() {
        return whereItem;
    }

    public void setWhereItem(SqlItem whereItem) {
        this.whereItem = whereItem;
    }

    public SqlHelper setWhereItem(String where, Object... params){
        whereItem.setSqlText(where);
        whereItem.setParams(params);
        return this;
    }
    public SqlHelper setSelectItem(String where,Object... params){
        selectItem.setSqlText(where);
        selectItem.setParams(params);
        return this;
    }

    public SqlHelper addSelect(String columnName, String aliasName){
        StringBuilder sql= new StringBuilder();
        sql.append(selectItem.getSqlText());

        if(sql.length()>0){
            sql.append(",");
        }
        sql.append(columnName);
        if(aliasName!=null && aliasName.length()>0){
            sql.append(" ");
            sql.append(aliasName);
        }
        selectItem.setSqlText(sql.toString());
        return  this;
    }
    public SqlHelper addSelect(String columnName){
        addSelect(columnName,null);
        return  this;
    }
    public SqlHelper addWhere(LOGIC logic,String where, Object... params){
        List list = new ArrayList<>();
        StringBuilder sql= new StringBuilder();
        sql.append(whereItem.getSqlText());
        for (Object param : whereItem.getParams()) {
            list.add(param);
        }

        if(sql.length()>0){
            switch (logic){
                case AND:
                    sql.append(" AND ");
                    break;
                case OR:
                    sql.append(" OR ");
                    break;
                case NOT:
                    sql.append(" NOT ");
                    break;
            }
        }
        sql.append(where);
        for (Object param : params) {
            list.add(param);
        }
        whereItem.setSqlText(sql.toString());
        whereItem.setParams(list.toArray());
        return  this;
    }

//    public SqlHelper whereLike(LOGIC logic, String columnName, String keyword) {
//        return addWhere(logic,);
//    }
//
//    public SqlHelper whereLike(String columnName, String keyword) {
//        LikeClause clause = new LikeClause(columnName, keyword);
//        return where(clause);
//    }
//
//    public SqlHelper whereLike(LOGIC logic, String columnName, String keyword, boolean after) {
//        LikeClause clause = new LikeClause(logic, columnName, keyword, after);
//        return where(clause);
//    }
//
//    public SqlHelper whereLike(String columnName, String keyword, boolean after) {
//        LikeClause clause = new LikeClause(columnName, keyword, after);
//        return where(clause);
//    }
//
    public SqlHelper whereLike(LOGIC logic, String columnName, String keyword, boolean before, boolean after) {
        keyword=keyword.trim();
        if(keyword!=null && keyword.length()>0){
            StringBuilder sql =new StringBuilder();
            if(before){
                keyword="%"+keyword;
            }
            if(after){
                keyword=keyword+"%";
            }
            sql.append(columnName);
            sql.append(" like ");
            sql.append("?");
            addWhere(logic,sql.toString(),keyword);
        }
        return this;
    }

    public SqlHelper whereLike(String columnName, String keyword, boolean before, boolean after) {
        return whereLike(LOGIC.AND,columnName,keyword,before,after);
    }

    public SqlHelper whereGreaterAndLessThan(LOGIC logic, String columnName, Object checkDefaultValue, Object beginValue, boolean beginEqual, Object endValue, boolean endEqual) {
        StringBuilder sql =new StringBuilder();
        List list = new ArrayList();
        if(beginValue!=null && beginValue!=checkDefaultValue) {
            sql.append(columnName);
            if (beginEqual) {
                sql.append(">=?");
            } else {
                sql.append(">?");
            }
            list.add(beginValue);
        }
        if(endValue!=null && endValue!=checkDefaultValue){
            if(sql.length()>0){
                sql.append(" and ");
            }
            sql.append(columnName);
            if(endEqual){
                sql.append("<=?");
            }else{
                sql.append("0){
            addWhere(logic,sql.toString(),list.toArray());
        }
        return this;
    }

    public SqlHelper whereGreaterAndLessThan(String columnName, Object checkDefaultValue, Object beginValue, boolean beginEqual, Object endValue, boolean endEqual) {
        return whereGreaterAndLessThan(LOGIC.AND,columnName,checkDefaultValue,beginValue,beginEqual,endValue,endEqual);
    }

    public SqlHelper whereLessThan(LOGIC logic, String columnName, Object value,Object checkDefaultValue, boolean equal) {
        if(value!=null && value!=checkDefaultValue){
            StringBuilder sql =new StringBuilder();
            sql.append(columnName);
            if(equal){
                sql.append("<=?");
            }else{
                sql.append("=?");
            }else{
                sql.append(">?");
            }
            addWhere(logic,sql.toString(),value);
        }
        return this;
    }

    public SqlHelper whereGreaterThan(String columnName, Object value, Object checkDefaultValue, boolean equal) {
        return whereGreaterThan(LOGIC.AND,columnName,value,checkDefaultValue,equal);
    }

    public SqlHelper whereIn(LOGIC logic, String columnName, List values) {
        if(values!=null && values.size()>0){
            StringBuilder sql =new StringBuilder();
            sql.append(columnName);
            sql.append(" IN (");
            int len = sql.length();
            for (Object value : values) {
                if(sql.length()>len){
                    sql.append(",");
                }
                sql.append("?");
            }
            sql.append(")");
            addWhere(logic,sql.toString(),values.toArray());
        }
        return this;
    }
    public SqlHelper whereIn(String columnName, List values){
        return whereIn(LOGIC.AND,columnName,values);
    }
    public SqlHelper whereNotIn(LOGIC logic, String columnName, List values) {
        if(values!=null && values.size()>0){
            StringBuilder sql =new StringBuilder();
            sql.append(columnName);
            sql.append(" NOT IN (");
            int len = sql.length();
            for (Object value : values) {
                if(sql.length()>len){
                    sql.append(",");
                }
                sql.append("?");
            }
            addWhere(logic,sql.toString(),values.toArray());
        }
        return this;
    }
    public SqlHelper whereNotIn(String columnName, List values){
        return whereNotIn(LOGIC.AND,columnName,values);
    }

    public SqlHelper whereNotEqual(LOGIC logic, String columnName, Object value,Object checkDefaultValue) {
        if(value!=null && value!=checkDefaultValue){
            StringBuilder sql =new StringBuilder();
            sql.append(columnName);
            sql.append("!=?");
            addWhere(logic,sql.toString(),value);
        }
        return this;
    }
    public SqlHelper whereNotEqual(String columnName, Object value,Object checkDefaultValue) {
        return whereNotEqual(LOGIC.AND,columnName,value,checkDefaultValue);
    }

    public SqlHelper whereEqual(LOGIC logic, String columnName, Object value,Object checkDefaultValue) {
        if(value!=null && value!=checkDefaultValue){
            StringBuilder sql =new StringBuilder();
            sql.append(columnName);
            sql.append("=?");
            addWhere(logic,sql.toString(),value);
        }
        return this;
    }
    public SqlHelper whereGroupOrder( String sql){
        StringBuilder sql1 =new StringBuilder();
        sql1.append(" ");
        sql1.append(sql);
        addWhere(LOGIC.EMPT,sql1.toString());
        return this;
    }

    public SqlHelper whereEqual(String columnName, Object value,Object checkDefaultValue) {
        return whereEqual(LOGIC.AND,columnName,value,checkDefaultValue);
    }

//
//    public SqlHelper whereBetweenThan(LOGIC logic, String columnName, Object beginValue, Object endValue) {
//        BetweenThanClause clause = new BetweenThanClause(logic, columnName, beginValue, endValue);
//        return where(clause);
//    }
//
//    public SqlHelper whereBetweenThan(String columnName, Object beginValue, boolean beginEqual, Object endValue, boolean endEqual) {
//        BetweenThanClause clause = new BetweenThanClause(columnName, beginValue, beginEqual, endValue, endEqual);
//        return where(clause);
//    }
//
//    public SqlHelper whereBetweenThan(LOGIC logic, String columnName, Object beginValue, boolean beginEqual, Object endValue, boolean endEqual) {
//        BetweenThanClause clause = new BetweenThanClause(logic, columnName, beginValue, beginEqual, endValue, endEqual);
//        return where(clause);
//    }
}