top.zeimao77.product.sql.SQL Maven / Gradle / Ivy

Go to download
package top.zeimao77.product.sql;

import top.zeimao77.product.exception.BaseServiceRunException;
import top.zeimao77.product.util.AssertUtil;

import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.function.Consumer;
import java.util.function.Predicate;

/**
 * 利用它可以简单直观的创建一个SQL
 * @author zeimao77
 * @since 2.1.1
 */
public class SQL implements StatementParamResolver, IWhere {

    private StringBuilder sqlBuilder = new StringBuilder(1024);
    private ArrayList statementParams = new ArrayList<>();
    private static final int FLAG_SELECT = 0x01;
    private static final int FLAG_SET = 0x02;
    private static final int FLAG_VALUES = 0x04;
    private static final int FLAG_WHERE = 0x08;
    private static final int FLAG_ON = 0x10;
    private static final int FLAG_NNOTBIND = 0x20;  // 左括号下一TOKEN不BIND;
    private int whereOrSetFlag = 0;
    private int sqlType = 0; // 1 = SELECT ;2 = DELETE ;3 = UPDATE ;4 INSERT ;5 = UPSERT ;
    private int paramIndex = 0;

    private int dbtype;

    public SQL() {
        this(Dbtype.ALL);
    }

    public SQL(int dbtype) {
        this.dbtype = dbtype;
    }

    public SQL select() {
        sqlBuilder.append("SELECT *");
        whereOrSetFlag |= FLAG_SELECT;
        this.sqlType = 1;
        return this;
    }

    public SQL append(String text) {
        sqlBuilder.append(text);
        return this;
    }

    public SQL select(String labelName) {
        if((whereOrSetFlag & FLAG_SELECT) == 0) {
            sqlBuilder.append("SELECT ");
            whereOrSetFlag |= FLAG_SELECT;
        } else {
            sqlBuilder.append(",");
        }
        sqlBuilder.append(labelName);
        return this;
    }

    /**
     * @param columnName 列名
     * @param alias 别名
     * @return this
     */
    public SQL select(String columnName,String alias) {
        if(alias == null || columnName.equals(alias)) {
            return select(columnName);
        } else {
            String labelName = String.format("%s AS \"%s\"",columnName,alias);
            return select(labelName);
        }
    }

    public SQL selectJsonVal(String columnName,String key,String alias) {
        String labelName = String.format("JSON_EXTRACT(%s,'%s') AS %s",columnName,key,alias);
        return select(labelName);
    }

    public SQL from(String tableName) {
        sqlBuilder.append(" FROM ").append(tableName);
        return this;
    }

    public SQL from(String tableName,String alias) {
        sqlBuilder.append(" FROM ").append(tableName).append(" AS ").append(alias);
        return this;
    }

    public SQL innerJoin(String tableName,String alias) {
        sqlBuilder.append(" INNER JOIN ").append(tableName).append(" AS ").append(alias);
        whereOrSetFlag &= (~FLAG_ON);
        return this;
    }

    public SQL leftJoin(String tableName,String alias) {
        sqlBuilder.append(" LEFT JOIN ").append(tableName).append(" AS ").append(alias);
        whereOrSetFlag &= (~FLAG_ON);
        return this;
    }

    public SQL rightJoin(String tableName,String alias) {
        sqlBuilder.append(" RIGHT JOIN ").append(tableName).append(" AS ").append(alias);
        whereOrSetFlag &= (~FLAG_ON);
        return this;
    }

    public SQL on(String bind,String columnName,String columnName2) {
        if((whereOrSetFlag & FLAG_ON) == 0) {
            sqlBuilder.append(" ON ");
            whereOrSetFlag |= FLAG_ON;
        } else if(BIND_AND.equals(bind)) {
            sqlBuilder.append(" AND ");
        } else if(BIND_OR.equals(bind)) {
            sqlBuilder.append(" OR ");
        }
        sqlBuilder.append(columnName).append(" = ").append(columnName2);
        return this;
    }

    public SQL where(String bind,String columnName,String cond,Object value) {
        return where(true,bind,columnName,cond,null,null,value);
    }


    public SQL where(boolean expression,String bind,String columnName,String cond,Object value) {
        return where(expression,bind,columnName,cond,null,null,value);
    }

    /**
     * @param expression 如果为 false 将不处理该条件
     * @param bind 条件关系
     * @see SQL#BIND_AND
     * @see SQL#BIND_OR
     * @param columnName 列名
     * @param cond 运算符
     * @see SQL#COND_QNIN
     * @param valSetPre 值的函数前缀
     * @param valSetPost 值的函数后缀
     * @param value 值
     * @return this
     */
    public SQL where(boolean expression,String bind,String columnName,String cond,String valSetPre,String valSetPost,Object value) {
        if(!expression) {
            return this;
        }
        if(COND_RBRACKET.equals(cond)) {
            sqlBuilder.append(" )");
            return this;
        }
        if((whereOrSetFlag & FLAG_NNOTBIND) == 0) {
            if((whereOrSetFlag & FLAG_WHERE) == 0) {
                sqlBuilder.append(" WHERE ");
                whereOrSetFlag |= FLAG_WHERE;
            } else if(BIND_AND.equals(bind)) {
                sqlBuilder.append(" AND ");
            } else if(BIND_OR.equals(bind)) {
                sqlBuilder.append(" OR ");
            }
        }
        if(COND_LBRACKET.equals(cond)) {
            sqlBuilder.append(" (");
            whereOrSetFlag |= FLAG_NNOTBIND;
            return this;
        }
        sqlBuilder.append(columnName);
        Consumer