car-wash-java/car-wash-jdbc/src/main/java/com/kym/jdbc/template/JdbcHelper.java

package com.kym.jdbc.template;

import com.kym.DbUtil;
import com.kym.JacksonUtil;
import com.kym.jdbc.BasicQuery;
import com.kym.jdbc.IHandler;
import com.kym.jdbc.OBuilder;
import com.kym.jdbc.SQLEntity;
import com.kym.jdbc.annotations.DBF;
import com.kym.jdbc.annotations.Entity;
import com.kym.jdbc.annotations.FK;
import com.kym.jdbc.annotations.JoinType;
import com.kym.jdbc.annotations.Many;
import com.kym.jdbc.annotations.OP;
import com.kym.jdbc.annotations.One;
import com.kym.jdbc.annotations.QE;
import com.kym.jdbc.annotations.QF;
import com.kym.jdbc.annotations.QueryType;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.lang.reflect.Array;
import java.lang.reflect.Field;
import java.lang.reflect.Type;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.function.Function;
import java.util.stream.Collectors;


/**
 * SQL动态解析器
 * <p>
 * jdbcTemplate模式
 *
 * @author yaop
 */
public class JdbcHelper {
    private final Logger logger = LoggerFactory.getLogger(JdbcHelper.class);

    public static final int DIALECT_MYSQL = 1;
    public static final int DIALECT_MSSQL = 2;
    public static final int DIALECT_ORACLE = 3;
    public static final int DIALECT_PGSQL = 4;

    public static final String[] BASE_IGNORE = {"createAt", "updateAt", "createBy", "updateBy"};

    private static final String DEFAULT_MKF = "id";

    private String comma = "`";

    private int dialect = 1;
    /***
     * 表名前缀
     */
    public static String tbPrefix = "t";

    private static Function<String, String> func;


    /**
     * 设置解析器的SQL方言类型，默认MYSQL
     */
    public void setDialect(int dialectType) {
        this.dialect = dialectType;
        OBuilder.dialect = dialectType;
        switch (dialectType) {
            case IHandler.DIALECT_MYSQL -> this.comma = "`";
            case IHandler.DIALECT_MSSQL -> this.comma = "'";
            case IHandler.DIALECT_ORACLE -> this.comma = "\"";
            case IHandler.DIALECT_PGSQL -> this.comma = "\"";
            default -> {
            }
        }
    }

    public int getDialect() {
        return dialect;
    }

    /**
     * 设置表名前缀
     */
    public static void setFunc(Function<String, String> func) {
        JdbcHelper.func = func;
    }

    public static void setTablePrefix(String prefix) {
        JdbcHelper.tbPrefix = prefix;
    }


    public static String getTbPrefix() {
        return JdbcHelper.tbPrefix;
    }


    //region json处理


    //endregion

    //region select
    public SQLEntity selectList(String sql, Object... params) {
        SQLEntity entity = new SQLEntity();
      /*  if (!DbUtil.isEmptyOrNull(params)) {
            for (Object param : params) {
                if (param.getClass() == String.class) {
                    sql = sql.replaceFirst("\\?", "'" + DbUtil.injectDefend(param.toString()) + "'");
                } else if (param.getClass() == Date.class) {
                    sql = sql.replaceFirst("\\?", "'" + new Timestamp(((Date) param).getTime()) + "'");
                } else {
                    sql = sql.replaceFirst("\\?", DbUtil.injectDefend(param.toString()));
                }
            }
        }*/
        entity.sql = sql;
        entity.parameters = params;
        return entity;
    }


    /**
     * 聚合函数查询<p>
     * 注意：聚合函数的查询需要使用子查询</p>
     *
     * @param clz
     * @param func    聚合查询字段 count(1) sum(amt)
     * @param builder
     * @return
     */
    public SQLEntity selectFunc(Class<?> clz, String func, OBuilder builder) {
        SQLEntity entity = new SQLEntity();
        List<Object> parameters = new ArrayList<>(16);
        boolean distinct = distinctAggerate(func);
        StringBuilder sbr = new StringBuilder();
        boolean originEntity = !clz.getSimpleName().endsWith("Info");
        if (originEntity) {
            String funcField = func;
            if (!distinct) {
                funcField = func.contains("COUNT(") ? "1" : func.trim().split("\\(")[1].split("\\)")[0].trim();
            }
            sbr.append("SELECT ").append(funcField).append(" FROM ").append(getTableName(clz, clz.getSimpleName()));
            if (null != builder) {
                if (!DbUtil.isEmptyOrNull(builder.getWheres())) {
                    List<OBuilder.Where> wheres = builder.getWheres();
                    if (!DbUtil.isEmptyOrNull(wheres)) {
                        sbr.append(" \r\n WHERE 1=1");
                        wheres.forEach(wh -> {
                            sbr.append(" AND ");
                            appendBuilderWhere(sbr, wh, "", parameters);
                        });
                    }
                }
                //GROUP BY
                if (!DbUtil.isEmptyOrNull(builder.getGroupBys())) {
                    sbr.append("  \r\n GROUP BY ");
                    builder.getGroupBys().forEach(group -> {
                        sbr.append(group).append(" ,");
                    });
                    sbr.deleteCharAt(sbr.length() - 1);
                }
                //ORDER BY
                if (!DbUtil.isEmptyOrNull(builder.getOrderBys())) {
                    sbr.append(" ORDER BY ");
                    builder.getOrderBys().forEach(order -> sbr.append(order).append(" ,"));
                    sbr.deleteCharAt(sbr.length() - 1);
                }

                //count不分页
                if (!func.contains("COUNT(")) {
                    //分页
                    String result = appendWhereLimit(builder, sbr);
                    if (null != result) {
                        if (!distinct) {
                            entity.sql = "SELECT " + func + " FROM (" + result + ") temp";
                        } else {
                            entity.sql = result;
                        }
                    }
                }
            }
            if (!distinct) {
                entity.sql = "SELECT " + func + " FROM (" + sbr.toString() + ") temp";
            } else {
                entity.sql = sbr.toString();
            }
        } else {
            //支持聚合查询的关联
            String queryDomain = clz.getSuperclass().getCanonicalName() + "$" + clz.getSuperclass().getSimpleName() + "BasicQuery";
            try {
                Object obj = Class.forName(queryDomain).newInstance();
                if (!DbUtil.isEmptyOrNull(func)) {
                    DbUtil.setFieldValue(obj, obj.getClass().getField("pageSize"), -1);
                }
                return select((BasicQuery) obj, builder, null, null, func, true);
            } catch (InstantiationException | IllegalAccessException | ClassNotFoundException | NoSuchFieldException e) {
                logger.error("SQL2Helper ERR# initialize class,class:{}", queryDomain);
                throw new IllegalStateException(e);
            }

        }
        entity.parameters = parameters.toArray();
        return entity;
    }

    /**
     * 聚合函数查询
     */
    public SQLEntity selectDistinct(Class<?> clz, String func, OBuilder OBuilder) {
        return selectFunc(clz, func, OBuilder);
    }

    public SQLEntity selectListByWhere(Class<?> clz, OBuilder OBuilder, List<String> include, List<String> exclude, boolean isList) {
        return selectListByWhereV2(clz, OBuilder, include, exclude, null, isList);
    }


    public SQLEntity selectListByWhereV2(Class<?> clz, OBuilder OBuilder, List<String> include, List<String> exclude, String aggregate, boolean isList) {
        SQLEntity sqlEntity = new SQLEntity();
        List<Object> parameters = new ArrayList<>();
        Set<Field> fields = DbUtil.getEntityFields(clz);
        boolean isSimple = !clz.getSimpleName().endsWith("Info");
        if (isSimple) {
            Entity entity = clz.getAnnotation(Entity.class);
            StringBuilder sbr = new StringBuilder();
            if (!DbUtil.isEmptyOrNull(aggregate)) {
                sbr.append("SELECT ").append(aggregate).append(" FROM ").append(getTableName(entity));
            } else {
                if (DbUtil.isEmptyOrNull(include)) {
                    if (DbUtil.isEmptyOrNull(exclude)) {
                        sbr.append("SELECT * FROM ").append(getTableName(entity));
                    } else {
                        sbr.append("SELECT ");
                        for (Field field : fields) {
                            if (DbUtil.isUsageField(field)) {
                                //richId  rich2Id 不关联查
                                String fileName = field.getName();
                                if (!exclude.contains(fileName)) {
                                    sbr.append(this.comma).append(DbUtil.getColumnName(fileName)).append(this.comma).append(" ,");
                                }
                            }
                        }
                        sbr.deleteCharAt(sbr.length() - 1);
                        sbr.append("  \n FROM ").append(getTableName(entity));
                    }
                } else {
                    sbr.append("SELECT ");
                    for (Field field : fields) {
                        if (DbUtil.isUsageField(field)) {
                            String fileName = field.getName();
                            if (include.contains(fileName)) {
                                sbr.append(this.comma).append(DbUtil.getColumnName(fileName)).append(this.comma).append(" ,");
                            }
                        }
                    }
                    sbr.deleteCharAt(sbr.length() - 1);
                    sbr.append("  \n FROM ").append(getTableName(entity));
                }
            }
            if (null != OBuilder && !DbUtil.isEmptyOrNull(OBuilder.getWheres())) {
                List<OBuilder.Where> wheres = OBuilder.getWheres();
                if (!DbUtil.isEmptyOrNull(wheres)) {
                    sbr.append(" \r\n WHERE 1=1");
                    wheres.forEach(wh -> {
                        sbr.append(" AND ");
                        appendBuilderWhere(sbr, wh, "", parameters);
                    });
                }
            }


            if (isList) {
                //ORDER BY
                if (null != OBuilder && !DbUtil.isEmptyOrNull(OBuilder.getOrderBys())) {
                    sbr.append(" ORDER BY ");
                    OBuilder.getOrderBys().forEach(order -> sbr.append(order).append(" ,"));
                    sbr.deleteCharAt(sbr.length() - 1);
                } else {
                    sbr.append(" ORDER BY update_at DESC ");
                }

                //limit
                String result = appendWhereLimit(OBuilder, sbr);
                if (null != result) {
                    sqlEntity.sql = result;
                }
            }

            sbr.append(";");
            sqlEntity.sql = sbr.toString();
        } else {
            String queryDomain = clz.getSuperclass().getCanonicalName() + "$" + clz.getSuperclass().getSimpleName() + "BasicQuery";
            Object obj;
            try {
                obj = Class.forName(queryDomain).newInstance();
                if (!DbUtil.isEmptyOrNull(aggregate)) {
                    DbUtil.setFieldValue(obj, obj.getClass().getField("pageSize"), -1);
                }
            } catch (InstantiationException | IllegalAccessException | ClassNotFoundException | NoSuchFieldException e) {
                logger.error("SQL2Helper ERR# initialize class,class:{}", queryDomain);
                throw new IllegalStateException(e);
            }
            return select((BasicQuery) obj, OBuilder, include, exclude, aggregate, isList);
        }
        sqlEntity.parameters = parameters.toArray();
        return sqlEntity;
    }


    public SQLEntity selectListByQuery(BasicQuery query, List<String> include, List<String> exclude) {
        return select(query, null, include, exclude, null, true);
    }


    /**
     * 查询SQL解析生成
     *
     * @param query     query条件对象
     * @param OBuilder  where条件对象
     * @param include   指定查询字段集合
     * @param exclude   指定过滤不查询字段集合
     * @param aggregate 聚合查询条件
     */
    private SQLEntity select(BasicQuery query, OBuilder OBuilder, List<String> include, List<String> exclude, String aggregate, boolean isList) {
        SQLEntity sqlEntity = new SQLEntity();
        List<Object> parameters = new ArrayList<>(16);
        if (!DbUtil.isEmptyOrNull(query.includeFields)) {
            if (DbUtil.isEmptyOrNull(include)) {
                include = new ArrayList<>();
            }
            for (String includeField : query.includeFields) {
                if (!include.contains(includeField)) {
                    include.add(includeField);
                }
            }
        }
        if (!DbUtil.isEmptyOrNull(query.excludeFields)) {
            if (DbUtil.isEmptyOrNull(exclude)) {
                exclude = new ArrayList<>();
            }
            for (String excludeField : query.excludeFields) {
                if (!exclude.contains(excludeField)) {
                    exclude.add(excludeField);
                }
            }
        }
        StringBuilder sbr = new StringBuilder();
        int aliasIdx = 0;
        //表别名 @QF->inner @FK、@One->left
        Map<String, String> alias = new HashMap<>(4);
        //关联表名
        Map<String, String> tbs = new HashMap<>(4);
        QE qEAnnotation = query.getClass().getAnnotation(QE.class);
        String queryClassName = query.getClass().getSimpleName();
        if (null == qEAnnotation) {
            throw new IllegalStateException("query entity of " + queryClassName + " @Query Entity is not defined!");
        }
        Class<?> infoClass = qEAnnotation.clz();
        Entity entity = infoClass.getSuperclass().getAnnotation(Entity.class);
//        Field[] fields = infoClass.getFields();
        Set<Field> fields = DbUtil.getEntityFields(infoClass);
        Set<Field> qfields = DbUtil.getQueryFields(query.getClass());
//        Field[] qfields = query.getClass().getFields();
        //关联表别名
        //------1.查询字段
        for (Field field : fields) {
            String fieldName = field.getName();
            //常量字段过滤
            if (!DbUtil.isUsageField(field)) {
                continue;
            }
            One oneAnnotation = field.getAnnotation(One.class);
            Many manyAnnotation = field.getAnnotation(Many.class);
            boolean isExtenstionField = null != oneAnnotation || null != manyAnnotation;
            //忽略字段过滤
            if (!DbUtil.isEmptyOrNull(exclude) && exclude.contains(fieldName) && !isExtenstionField) {
                continue;
            }
            //指定查询
            if (!DbUtil.isEmptyOrNull(include) && !include.contains(fieldName) && !isExtenstionField) {
                continue;
            }
            DBF DBFAnnotation = field.getAnnotation(DBF.class);
            if (null != DBFAnnotation) {
                FK foreignKey = field.getAnnotation(FK.class);
                if (null != foreignKey) {
                    Class<?> entityClass = foreignKey.clz();
                    Entity ey = entityClass.getAnnotation(Entity.class);
                    for (Field qfield : qfields) {
                        if (qfield.isAnnotationPresent(QF.class)) {
                            QF qf = qfield.getAnnotation(QF.class);
                            String mkf = qf.pkf();
                            if (!DbUtil.isEmptyOrNull(mkf) && mkf.equals(fieldName)) {
                                if (null != DbUtil.getFieldValue(query, qfield)) {
                                    alias.putIfAbsent(fieldName, "i" + aliasIdx++);
                                    tbs.putIfAbsent(fieldName, getTableName(ey));
                                }
                            }
                        }
                    }
                    alias.putIfAbsent(fieldName, "l" + aliasIdx++);
                    tbs.putIfAbsent(fieldName, getTableName(ey));
                }
            } else {
                if (null != oneAnnotation) {
                    String mkf = oneAnnotation.mkf();
                    if (!DbUtil.isGenericType(field.getType()) && DEFAULT_MKF.equals(mkf)) {
                        mkf = fieldName;
                    }
                    JoinType joinType = oneAnnotation.join();
                    if (JoinType.LEFT == joinType) {
                        alias.put(mkf, "l" + aliasIdx++);
                    } else if (JoinType.INNER == joinType) {
                        alias.put(mkf, "i" + aliasIdx++);
                    } else if (JoinType.RIGHT == joinType) {
                        alias.put(mkf, "r" + aliasIdx++);
                    }
                }
            }
        }
        //非聚合查询
        if (DbUtil.isEmptyOrNull(aggregate)) {
            sbr.append("SELECT");
            //------1.查询主表字段
            boolean appendFields = false;
            for (Field field : fields) {
                String fieldName = field.getName();
                //常量字段过滤
                if (!DbUtil.isUsageField(field)) {
                    continue;
                }
                boolean isExtenstionField = field.isAnnotationPresent(One.class) || field.isAnnotationPresent(Many.class);
                //忽略字段过滤
                if (!DbUtil.isEmptyOrNull(exclude) && exclude.contains(fieldName) && !isExtenstionField) {
                    continue;
                }
                //指定查询
                if (!DbUtil.isEmptyOrNull(include) && !include.contains(fieldName) && !isExtenstionField) {
                    continue;
                }
                DBF DBFAnnotation = field.getAnnotation(DBF.class);
                if (null != DBFAnnotation) {
                    if (appendFields) {
                        sbr.append(",");
                    }
                    sbr.append(" l.").append(this.comma).append(DbUtil.getColumnName(fieldName)).append(this.comma);
                    appendFields = true;
                }
            }
            //-------2.查询关联字段
            for (Field field : fields) {
                Class<?> type = field.getType();
                String fieldName = field.getName();
                //常量字段过滤
                if (!DbUtil.isUsageField(field)) {
                    continue;
                }
                boolean isExtenstionField = field.isAnnotationPresent(One.class) || field.isAnnotationPresent(Many.class);
                //忽略字段过滤
                if (!DbUtil.isEmptyOrNull(exclude) && exclude.contains(fieldName)) {
                    continue;
                }
                //指定查询
                if (!DbUtil.isEmptyOrNull(include) && !include.contains(fieldName)) {
                    continue;
                }
                Class<?> fieldClass = field.getType();
                One oneAnnotation = field.getAnnotation(One.class);
                if (null != oneAnnotation) {
                    //忽略查询
                    if (oneAnnotation.noQuery()) {
                        continue;
                    }
                    String fkField = oneAnnotation.mkf();
                    if (!DbUtil.isGenericType(field.getType()) && DEFAULT_MKF.equals(fkField)) {
                        fkField = fieldName;
                    }
                    // join字段
                    if (!DbUtil.isEmptyOrNull(fkField)) {
                        //1.join查询对象
                        if (!DbUtil.isGenericType(type) && type != String.class && !type.isArray() && !type.isAssignableFrom(List.class)) {
//                            alias.putIfAbsent(fkField, "l" + aliasIdx++);
                            Field[] joinFields = fieldClass.getFields();
                            for (Field joinField : joinFields) {
                                if (DbUtil.isUsageField(joinField)) {
                                    if (!"SELECT".contentEquals(sbr)) {
                                        sbr.append(",");
                                    }
                                    sbr.append(alias.get(fkField)).append(".").append(this.comma).append(DbUtil.getColumnName(joinField.getName())).append(this.comma).append(" AS ").append(this.comma).append(fieldName).append("_").append(DbUtil.getColumnName(joinField.getName())).append(this.comma);
                                }
                            }

                            //2.join查询变量
                        } else {
                            if (!"SELECT".equals(sbr.toString().trim())) {
                                sbr.append(",");
                            }
                            sbr.append(alias.get(fkField)).append(".").append(this.comma).append(DbUtil.getColumnName(oneAnnotation.tf())).append(this.comma).append(" AS ").append(this.comma).append(DbUtil.getColumnName(fieldName)).append(this.comma);
                        }
                    }
                }
            }
        } else {
           /* //聚合查询需要获取关联表别名
            for (Field field : fields) {
                if (field.isAnnotationPresent(Fk.class)) {
                    alias.putIfAbsent(field.getName(), "l" + aliasIdx++);
                }
            }*/

            String aggregateField = aggregate;
            boolean distinct = distinctAggerate(aggregate);
            if (!distinct) {
                aggregateField = aggregate.contains("COUNT(") ? "l.*" : aggregate.trim().split("\\(")[1].split("\\)")[0].trim();
            }
            sbr.append("SELECT ").append(aggregateField);
        }

        sbr.append("   \n FROM ").append(this.comma).append(getTableName(entity)).append(this.comma).append(" AS l");
        List<String> joined = new ArrayList<>();
        //-------3.关联对象  JOINS
        //BUG:涉及到关联查询的对象字段需要inner join @QF
        Field[] qfs = query.getClass().getDeclaredFields();
        for (Field qf : qfields) {
            //qf 内联查询
            if (qf.isAnnotationPresent(QF.class)) {
                Object val = DbUtil.getFieldValue(query, qf);
                if (null != val) {
                    String amkf = qf.getAnnotation(QF.class).pkf();
                    if (!DbUtil.isEmptyOrNull(amkf)) {
//                        if (!joinFields.contains(amkf)) {
//                            joinFields.add(amkf);
//                            alias.putIfAbsent(amkf, "i" + aliasIdx++);
                        if (!joined.contains(alias.get(amkf))) {
                            joined.add(alias.get(amkf));
                        } else {
                            continue;
                        }
                        Class<?> aliasClass = getFkDomainClass(qf, infoClass);
                        sbr.append(" INNER JOIN ").append(getTableName(aliasClass, aliasClass.getSimpleName())).append(" AS ").append(alias.get(amkf)).append(" ON ").append("l.").append(this.comma).append(DbUtil.getColumnName(amkf)).append(this.comma).append("=").append(alias.get(amkf)).append(".").append(this.comma).append(DbUtil.getColumnName(qf.getAnnotation(QF.class).tkf())).append(this.comma);
                    }
                }
//                }
            }
        }
        for (Field field : fields) {
            String fieldName = field.getName();
            //忽略查询
            if (!DbUtil.isEmptyOrNull(exclude) && exclude.contains(fieldName)) {
                continue;
            }
            One oneAnnotation = field.getAnnotation(One.class);
            if (null != oneAnnotation) {
                //忽略查询
                if (oneAnnotation.noQuery()) {
                    continue;
                }
                if (!DbUtil.isUsageField(field)) {
                    continue;
                }
                String mkf = oneAnnotation.mkf();
                if (!DbUtil.isGenericType(field.getType()) && DEFAULT_MKF.equals(mkf)) {
                    mkf = fieldName;
                }
                //joins
                if (!DbUtil.isEmptyOrNull(mkf)) {
                    if (!joined.contains(alias.get(mkf))) {
                        joined.add(alias.get(mkf));
                    } else {
                        continue;
                    }
//                    joinFields.add(fkField);
                    Class<?> aliasClass = getFkDomainClass(field, infoClass);
//                    alias.putIfAbsent(fkField, "l" + aliasIdx++);
                    if (oneAnnotation.join() == JoinType.LEFT) {
                        sbr.append(" \n LEFT JOIN ");
                    } else if (oneAnnotation.join() == JoinType.INNER) {
                        sbr.append("  \n  INNER JOIN  ");
                    } else {
                        sbr.append("  \n RIGHT JOIN ");
                    }
                    sbr.append(getTableName(aliasClass, aliasClass.getSimpleName())).append(" AS ").append(alias.get(mkf)).append(" ON ").append("l.").append(this.comma).append(DbUtil.getColumnName(oneAnnotation.mkf())).append(this.comma).append("=").append(alias.get(mkf)).append(".").append(this.comma).append(DbUtil.getColumnName(oneAnnotation.tkf())).append(this.comma);
                }
            }
        }
        //-------4.查询条件
        sbr.append(" \n WHERE 1=1 ");
        Field[] queryFields = query.getClass().getDeclaredFields();
        //query条件和Builder条件仅支持一个（查询时只提供一个条件构造实体）
        if (null != OBuilder && !DbUtil.isEmptyOrNull(OBuilder.getWheres())) {
            List<OBuilder.Where> wheres = OBuilder.getWheres();
            wheres.forEach(wh -> {
                //联表查询字段
                String aliasName = alias.getOrDefault(getMkf(query, wh), "l");
                sbr.append(" AND ");
                appendBuilderWhere(sbr, wh, aliasName, parameters);
            });
        } else {
            for (Field qf : queryFields) {
                Object val = DbUtil.getFieldValue(query, qf);
                String fieldName = qf.getName();
                if (DbUtil.isEmptyOrNull(val)) {
                    continue;
                }
                QF qfAnnotation = qf.getAnnotation(QF.class);
                if (null == qfAnnotation) {
                    if (!fieldName.endsWith("Sort")) {
                        sbr.append(" AND l.").append(this.comma).append(DbUtil.getColumnName(fieldName)).append(this.comma);
                        if (String.class.equals(qf.getType())) {
                            if (DIALECT_MYSQL == dialect) {
                                sbr.append(" LIKE concat('%',?,'%')");
                            } else if (DIALECT_PGSQL == dialect) {
                                sbr.append(" like concat('%',?::text,'%')");
                            } else if (DIALECT_MSSQL == dialect) {
                                sbr.append(" LIKE '%'+?+'%')");
                            } else if (DIALECT_ORACLE == dialect) {
                                sbr.append(" LIKE '%'||?||'%')");
                            }
                            parameters.add(val);
                        } else {
                            sbr.append(" = ?");
                            parameters.add(val);
//                            .append(val);
                        }
                    }
                } else {
                    //foreignKeys
                    String mkf = qfAnnotation.pkf();
                    if (qfAnnotation.qt() == QueryType.IGNORE) {
                        continue;
                    }
                    if (!DbUtil.isEmptyOrNull(mkf)) {
                        String tf = qfAnnotation.tf();
                        if (DbUtil.isEmptyOrNull(tf)) {
                            logger.error("SQL2Helper ERR# [" + fieldName + "] @QF with mkf define,but without tkf exist!!!");
                        } else {
                            if (qfAnnotation.qt() == QueryType.MAIN) {
                                sbr.append(" AND ").append(" l").append(".").append(this.comma).append(DbUtil.getColumnName(mkf)).append(this.comma);
                            } else {
                                sbr.append(" AND ").append(alias.get(mkf)).append(".").append(this.comma).append(DbUtil.getColumnName(tf)).append(this.comma);
                            }
                            //字符串使用模糊查询
                            if (String.class.equals(qf.getType())) {
                                if (DIALECT_MYSQL == dialect) {
                                    sbr.append(" like concat('%',?,'%')");
                                } else if (DIALECT_PGSQL == dialect) {
                                    sbr.append(" like concat('%',?::text,'%')");
                                } else if (DIALECT_MSSQL == dialect) {
                                    sbr.append(" like '%'+'?'+'%')");
                                } else if (DIALECT_ORACLE == dialect) {
                                    sbr.append(" like '%'||'?'||'%')");
                                }
                                parameters.add(val);
                            } else {
                                sbr.append(" =");
//                                parameters.add(val);
                                getValue(sbr, val, parameters);
                            }
                        }

                    }
                    //sql
                    String whereSql = qfAnnotation.sql();
                    String tbAlias = alias.getOrDefault(qfAnnotation.pkf(), "l");
                    whereSql = appendWhereSql(whereSql, Collections.singletonList(val), tbAlias, parameters);
                    if (!DbUtil.isEmptyOrNull(whereSql)) {
                        sbr.append(" AND ").append(whereSql);
                    }
                    //op
                    String operator = qfAnnotation.op();
                    if (!DbUtil.isEmptyOrNull(operator)) {
                        String tf = qfAnnotation.tf();
                        if (DbUtil.isEmptyOrNull(tf)) {
                            logger.error("SQL2Helper ERR# " + fieldName + "@QF with operator defined,but without tkf exist!!!");
                        } else {
                            //JIN  JINT
                            if (operator.equals(OP.JIN)) {
                                int loop = 1;
                                String each = qfAnnotation.each();
                                if (val instanceof Collection<?>) {
                                    List<Object> list = JacksonUtil.toList(val);
                                    loop = list.size();

                                    parameters.addAll((Collection<?>) val);
                                } else if (val.getClass().isArray()) {
                                    loop = Array.getLength(val);
                                    for (int i = 0; i < loop; i++) {
                                        parameters.add(Array.get(val, i));
                                    }
                                } else {
                                    loop = 1;
                                    parameters.add(val);
                                }
                                // and ( json_contains(l.id_list,'1'))
                                if (dialect == IHandler.DIALECT_MYSQL) {
                                    sbr.append(" AND ").append("( ");
                                    for (int i = 0; i < loop; i++) {
                                        sbr.append(" (JSON_CONTAINS(").append(tbAlias).append(".").append(this.comma).append(DbUtil.getColumnName(tf)).append(this.comma).append(",").append("JSON_ARRAY(?)) ");
                                        if (i != loop - 1) {
                                            sbr.append(each);
                                        }
                                    }
                                    sbr.append(") ");
                                    //  owner_id_list::jsonb @> jsonb_build_array(2)
                                    //  owner_id_list::jsonb @> '[2]'
                                } else if (dialect == IHandler.DIALECT_PGSQL) {
                                    sbr.append(" AND ").append("( ");
                                    for (int i = 0; i < loop; i++) {
                                        sbr.append(" (").append(tbAlias).append(".").append(this.comma).append(DbUtil.getColumnName(tf)).append(this.comma).append("::jsonb @>  ").append("jsonb_build_array(?)) ");
                                        if (i != loop - 1) {
                                            sbr.append(each);
                                        }
                                    }
                                    sbr.append(") ");
                                }

                            } else if (operator.equals(OP.JINT)) {

                                //and (JSON_OVERLAPS(role,'["a","d"]')=1)
                                if (dialect == IHandler.DIALECT_MYSQL) {
                                    sbr.append(" AND ").append("(").append(operator).append("JSON_OVERLAPS(").append(tbAlias).append(".").append(this.comma).append(DbUtil.getColumnName(tf)).append(this.comma).append(",").append("'?')=1").append(")");
                                    parameters.add(JacksonUtil.toJSONString(val));
                                } else if (dialect == IHandler.DIALECT_PGSQL) {
                                    //    //ARRAY[1,4,3] && ARRAY[2,1]
                                    sbr.append(" AND ").append("(").append(tbAlias).append(".").append(this.comma).append(DbUtil.getColumnName(tf)).append(this.comma).append("::jsonb && ").append("jsonb_build_array(?))");
                                    parameters.add(JacksonUtil.toJSONString(val));
                                }
                            } else if (operator.equals(OP.IN) || operator.equals(OP.NIN)) {
                                sbr.append(" AND ").append("(").append(tbAlias).append(".").append(this.comma).append(DbUtil.getColumnName(tf)).append(this.comma).append(" ").append(operator).append("  (");
                                if (val instanceof Collection<?>) {
                                    List<Object> list = JacksonUtil.toList(val);
                                    String collect = list.stream().map(k -> "?").collect(Collectors.joining(","));
                                    sbr.append(collect).append(") ) ");
                                    parameters.addAll((Collection<?>) val);
                                } else if (val.getClass().isArray()) {
                                    int length = Array.getLength(val);
                                    for (int i = 0; i < length; i++) {
                                        sbr.append("?");
                                        if (i != length - 1) {
                                            sbr.append(",");
                                        }
                                        parameters.add(Array.get(val, i));
                                    }
                                    sbr.append("))");
                                } else {
                                    throw new IllegalArgumentException("不支持的参数格式");
                                }

                            } else if (operator.equals(OP.RLK) || operator.equals(OP.LK) || operator.equals(OP.LLK)) {
                                sbr.append(" AND ").append(tbAlias).append(".").append(this.comma).append(DbUtil.getColumnName(tf)).append(this.comma).append(" ").append(operator);
                                parameters.add(val);
                            } else {
                                sbr.append(" AND ").append(tbAlias).append(".").append(this.comma).append(DbUtil.getColumnName(tf)).append(this.comma).append(" ").append(operator);
                                if (qf.getType().isArray()) {
                                    sbr.append(" (");
                                    int length = Array.getLength(val);
                                    for (int i = 0; i < length; i++) {
                                        Object value = Array.get(val, i);
                                        getValue(sbr, value, parameters);
                                        if (i != length - 1) {
                                            sbr.append(",");
                                        }
                                    }
                                    sbr.append(")");
                                } else if (val instanceof Collection<?>) {
                                    List<Object> list = JacksonUtil.toList(val);
                                    String collect = list.stream().map(k -> "?").collect(Collectors.joining(","));
                                    sbr.append(collect).append(")  ");
                                    parameters.addAll((Collection<?>) val);
                                } else {
                                    getValue(sbr, val, parameters);
                                }
                            }
                        }
                    }
                }
            }
        }


        //-------6.GROUP BY
        if (null != OBuilder && !DbUtil.isEmptyOrNull(OBuilder.getGroupBys())) {
            sbr.append(" GROUP BY ");
            OBuilder.getGroupBys().forEach(group -> {
                sbr.append(group).append(" ,");
            });
            sbr.deleteCharAt(sbr.length() - 1);
        }

        //-------5.having
        //todo

        //-------7.ORDER BY(query)
        boolean ordered = false;
        //聚合查询不需要排序
        if (DbUtil.isEmptyOrNull(aggregate) && isList) {
            //多个排序字段需要确定先后顺序
            Map<Integer, String> sortMap = new HashMap<>(16);
            if (DbUtil.isEmptyOrNull(query.sortFields)) {
                for (Field qf : queryFields) {
                    Object val = DbUtil.getFieldValue(query, qf);
                    if (null == val) {
                        continue;
                    }
                    int sortValue = 0;
                    if (val instanceof Integer) {
                        sortValue = (int) val;
                    }
                    if (sortValue == 0) {
                        continue;
                    }
                    String fieldName = qf.getName();

                    List<Field> sorted = new ArrayList<>();
                    QF qfAnnotation = qf.getAnnotation(QF.class);
                    if (null == qfAnnotation) {
                        if (fieldName.endsWith("Sort")) {
                            if (!ordered) {
                                ordered = true;
                                sbr.append(" ORDER BY ");
                            }

//                            sbr.append(" l.")
//                                    .append(this.comma)
//                                    .append(DbUtil.getColumnName(fieldName.substring(0, fieldName.length() - 4)))
//                                    .append(this.comma);
                            if (sortValue % 2 == BasicQuery.SORT_ASC) {
                                sortMap.put(sortValue, String.format("%s ASC ", DbUtil.getColumnName(fieldName.substring(0, fieldName.length() - 4))));
//                                sbr.append(" ASC ,");
                            } else {
//                                sbr.append(" DESC ,");
                                sortMap.put(sortValue, String.format("%s DESC ", DbUtil.getColumnName(fieldName.substring(0, fieldName.length() - 4))));
                            }
                        }
                    }

                    if (!DbUtil.isEmptyOrNull(sortMap)) {
                        Set<Integer> integers = sortMap.keySet();
                        List<Integer> sorts = new ArrayList<>(integers);
                        sorts.sort(Comparator.comparingInt(o -> o));
                        sbr.append(sorts.stream().map(k -> "l." + sortMap.get(k)).collect(Collectors.joining(",")));
                    }
               /*     if (null == qfAnnotation) {
                        if (fieldName.endsWith("Sort")) {
                            if (!ordered) {
                                ordered = true;
                                sbr.append(" ORDER BY ");
                            }
                            sbr.append(" l.")
                                    .append(this.comma)
                                    .append(DbUtil.getColumnName(fieldName.substring(0, fieldName.length() - 4)))
                                    .append(this.comma);
                            if (sortType == BasicQuery.SORT_ASC) {
                                sbr.append(" ASC ,");
                            } else if (sortType == BasicQuery.SORT_DESC) {
                                sbr.append(" desc ,");
                            }
                        }
                    }*/
                }
            } else {
                String[] sorts = query.sortFields;
                for (String sortField : sorts) {
                    try {
                        Field sf = query.getClass().getDeclaredField(sortField + "Sort");
                        Object val = DbUtil.getFieldValue(query, sf);
                        if (null == val) {
                            continue;
                        }
                        int sortType = (int) val;
                        if (!ordered) {
                            ordered = true;
                            sbr.append(" ORDER BY ");
                        }

                        sbr.append(" l.").append(this.comma).append(DbUtil.getColumnName(sortField)).append(this.comma);
                        if (sortType == BasicQuery.SORT_ASC) {
                            sbr.append(" ASC ,");
                        } else if (sortType == BasicQuery.SORT_DESC) {
                            sbr.append(" DESC ,");
                        }
                    } catch (NoSuchFieldException e) {
                        logger.warn("SQL2Helper warn# sort field is not exist,{}", sortField);
                    }
                }
            }
        }
        //-------7.ORDER BY(builder)  聚合查询不需要排序
        if (DbUtil.isEmptyOrNull(aggregate) && isList) {
            if (null != OBuilder && !DbUtil.isEmptyOrNull(OBuilder.getOrderBys())) {
                if (!ordered) {
                    ordered = true;
                    sbr.append(" ORDER BY ");
                }
                OBuilder.getOrderBys().forEach(order -> sbr.append(" l.").append(order).append(" ,"));
            }
            if (ordered && sbr.toString().endsWith(",")) {
                sbr.deleteCharAt(sbr.length() - 1);
            }
            //------7.ORDER BY default update_at DESC
            if (!ordered) {
                sbr.append(" ORDER BY l.update_at DESC");
            }
        }

        //-------8.limit(query)
        if (isList) {
            if (query.pageSize > 0) {
                if (DIALECT_MYSQL == dialect) {
                    sbr.append(" LIMIT ");
                    if (query.pageIndex > 0) {
                        sbr.append((query.pageIndex - 1) * query.pageSize).append(",");
                    }
                    if (query.pageSize > 0) {
                        sbr.append(query.pageSize);
                    }
                } else if (DIALECT_MSSQL == dialect) {
                    sbr.append(" OFFSET ");
                    if (query.pageIndex > 0) {
                        sbr.append((query.pageIndex - 1) * query.pageSize).append(" ROWS ");
                    }
                    if (query.pageSize > 0) {
                        sbr.append(" FETCH NEXT ").append(query.pageSize).append(" ROWS ONLY  ");
                    }
                } else if (DIALECT_PGSQL == dialect) {
                    sbr.append(" LIMIT ").append(query.pageSize);
                    if (query.pageIndex > 0) {
                        sbr.append(" OFFSET ").append((query.pageIndex - 1) * query.pageSize);
                    }
                } else if (DIALECT_ORACLE == dialect) {
                    StringBuilder sr = new StringBuilder();
                    sr.append("SELECT * FROM (SELECT o.*,ROWNUM as rowno FROM (").append(sbr).append(") o ");
                    if (query.pageSize > 0) {
                        sr.append(" WHERE ROWNUM<=").append(query.pageSize).append(")  o1");
                    }
                    if (query.pageIndex > 0) {
                        sr.append(" WHERE o1.rowno>").append((query.pageIndex - 1) * query.pageSize).append(";");
                    }
                    //聚合类使用子查询
                    if (!DbUtil.isEmptyOrNull(aggregate)) {
                        sqlEntity.sql = "SELECT " + aggregate + " FROM (" + sr.toString() + ") temp";
                    }
                    sqlEntity.sql = sr.toString();
                }
                //-------8.limit(builder)
            } else {
                String result = appendWhereLimit(OBuilder, sbr);
                if (null != result) {
                    //聚合类使用子查询
                    if (!DbUtil.isEmptyOrNull(aggregate) && !distinctAggerate(aggregate)) {
                        sbr.setLength(0);
                        sbr.append("SELECT ").append(aggregate).append(" FROM (").append(result).append(") temp");
                    }
                }
            }

        }

        sqlEntity.sql = sbr.toString();
        //聚合类使用子查询
        if (!DbUtil.isEmptyOrNull(aggregate) && !distinctAggerate(aggregate)) {
            String tmpSql = sbr.toString();
            sbr.setLength(0);
            sbr.append("SELECT ").append(aggregate).append(" FROM (").append(tmpSql).append(") temp");
        }
        sbr.append(";");

        sqlEntity.sql = sbr.toString();
        sqlEntity.parameters = parameters.toArray();
        return sqlEntity;
    }


    private boolean distinctAggerate(String aggregate) {
        return aggregate.toLowerCase().contains("distinct(");
    }

    private String getMkf(BasicQuery query, OBuilder.Where where) {
        String fieldName = where.key;
        if (DbUtil.isEmptyOrNull(fieldName)) {
            if (!DbUtil.isEmptyOrNull(where.sql)) {
                logger.warn("SQL#builder with sql foreign key is not support yet! {}", where.sql);
            }
            return null;
        }
        try {
            //info
            Class<?> infoClz = query.getClass().getAnnotation(QE.class).clz();
            Field field = infoClz.getField(DbUtil.getCamelName(fieldName));
            if (field.isAnnotationPresent(One.class)) {
                One one = field.getAnnotation(One.class);
                where.key = one.tf();
                return one.mkf();
            }
        } catch (NoSuchFieldException e) {
            try {
                //query
                Field field = query.getClass().getField(DbUtil.getCamelName(fieldName));
                if (field.isAnnotationPresent(QF.class)) {
                    QF qf = field.getAnnotation(QF.class);
                    where.key = qf.tf();
                    return qf.pkf();
                }
            } catch (NoSuchFieldException e1) {
                return null;
            }
        }
        return null;
    }


    public SQLEntity selectOne(String sql, Object... params) {
        return selectList(sql, params);
    }

    public SQLEntity selectOneByQuery(BasicQuery query, List<String> include, List<String> exclude) {
        return select(query, null, include, exclude, null, false);
    }

    public SQLEntity selectById(Class<?> clz, Object id, List<String> include, List<String> exclude) {
        return selectListByWhere(clz, OBuilder.build().eq("id", id), include, exclude, false);
    }

    public SQLEntity selectOneByWhere(Class<?> clz, OBuilder OBuilder, List<String> include, List<String> exclude) {
        return selectListByWhere(clz, OBuilder, include, exclude, false);
    }

    public SQLEntity selectCount(Class<?> clz, OBuilder OBuilder) {
        return selectFunc(clz, "COUNT(1)", OBuilder);
    }

    public SQLEntity selectCountByQuery(BasicQuery query) {
        return select(query, null, null, null, "COUNT(1)", false);
    }
//endregion select


    //region insert

    /**
     * 存储过程
     *
     * @param sql
     * @param params
     * @return
     */
    public SQLEntity call(String sql, Object... params) {
        SQLEntity sqlEntity = new SQLEntity();
        sqlEntity.sql = sql;
        sqlEntity.parameters = params;
//        if (!DbUtil.isEmptyOrNull(params)) {
//            sql = String.format(sql, params);
//        }
//    "CALL updateRole(#{id,jdbcType=VARCHAR,mode=IN},#{result,jdbcType=INTEGER,mode=OUT});";
        return sqlEntity;
    }

    /**
     * --------------insert method begin ---------------
     */
    public SQLEntity insertSelective(Object o, List<String> exclude) {
        return insertWithCheck(o, true, false, exclude);
    }

    public SQLEntity insert(Object o, List<String> exclude) {
        return insertWithCheck(o, false, false, exclude);
    }

    public SQLEntity insertWithGenKey(Object o, List<String> exclude) {
        return insertWithCheck(o, false, true, exclude);
    }

    /***
     * 插入方法
     * @param o 插入实体对象
     * @param ignoreNull 忽略空字段
     * @param withAutoIncreamentKey 对象是否已赋值自增主键
     * @param exclude 忽略字段
     * @return
     */
    private SQLEntity insertWithCheck(Object o, boolean ignoreNull, boolean withAutoIncreamentKey, List<String> exclude) {
        SQLEntity sqlEntity = new SQLEntity();
        List<Object> parameters = new ArrayList<>();
        StringBuilder sbr = new StringBuilder("INSERT INTO ");
        sbr.append(getTableName(o.getClass(), o.getClass().getSimpleName()));
        sbr.append("(");

        StringBuilder vb = new StringBuilder("(");
        //自带主键
        if (withAutoIncreamentKey) {
            sbr.append("id,");
            try {
                parameters.add(DbUtil.getFieldValue(o, DbUtil.getField(o, "id")));
                vb.append("?").append(",");
            } catch (NoSuchFieldException e) {
                logger.error("SQL2Helper ERR# insert with primary key,but without a value!!");
                throw new IllegalStateException("no primary key value exist.");
            }
        }
        exclude.add("id");
        Set<Field> fields = DbUtil.getEntityFields(o.getClass());
    /*    List<Field> fields1 = new ArrayList<>();
        Entity declaredAnnotation = o.getClass().getDeclaredAnnotation(Entity.class);
        if (null != declaredAnnotation) {
            fields = o.getClass().getFields();
            fields1.addAll(Arrays.asList( o.getClass().getFields()));
        } else {
            declaredAnnotation = o.getClass().getSuperclass().getDeclaredAnnotation(Entity.class);
            if(null==declaredAnnotation){
                throw new IllegalArgumentException("no @Entity define in class");
            }
            fields = o.getClass().getSuperclass().getDeclaredFields();
            fields1.addAll(Arrays.asList( o.getClass().getFields()));
            Field[] declaredFields = o.getClass().getSuperclass().getDeclaredFields();
            fields1.addAll(Arrays.asList( o.getClass().getSuperclass().getDeclaredFields()));
        }*/
        for (Field field : fields) {
            if (DbUtil.isUsageField(field)) {
                String fieldName = field.getName();
                if (!DbUtil.isEmptyOrNull(exclude)) {
                    //忽略属性
                    if (exclude.contains(fieldName)) {
                        continue;
                    }
                }
                Type type = field.getGenericType();
                DBF DBF = field.getAnnotation(DBF.class);
                if (null != DBF) {
                    Object value = DbUtil.getFieldValue(o, field);
                    if (ignoreNull) {
                        if (null != value) {
                            //TODO 校验值
                            checkFieldValue(field, value, true);
                            sbr.append(this.comma).append(DbUtil.getColumnName(fieldName)).append(this.comma).append(",");
                            appendValue(vb, type, value, parameters);
                        }
                    } else {
                        if (type == Date.class && null == value) {
                            continue;
                        }
                        sbr.append(this.comma).append(DbUtil.getColumnName(fieldName)).append(this.comma).append(",");
                        appendValue(vb, type, value, parameters);
                    }
                }
            }
        }
        sbr.deleteCharAt(sbr.length() - 1);
        vb.deleteCharAt(vb.length() - 1);
        sbr.append(")").append(" VALUES ");
        sbr.append(vb.toString()).append(")");
        sbr.append(";");
        sqlEntity.sql = sbr.toString();
        sqlEntity.parameters = parameters.toArray();
        return sqlEntity;
    }

    //endregion insert

    //region update
    private SQLEntity updateNative(Object o, OBuilder OBuilder, boolean ignoreNull, List<String> includes, List<String> excludes) {
        SQLEntity sqlEntity = new SQLEntity();
        List<Object> parameters = new ArrayList<>();

        StringBuilder sbr = new StringBuilder("UPDATE ");
        sbr.append(getTableName(o.getClass(), o.getClass().getSimpleName())).append(" SET ");
        Set<Field> fields = DbUtil.getEntityFields(o.getClass());
        boolean hasUpdateField = false;
        for (Field field : fields) {
            if (DbUtil.isUsageField(field)) {
                DBF dbf = field.getAnnotation(DBF.class);
                if (null == dbf) {
                    continue;
                }
                String fieldName = field.getName();
                if (!DbUtil.isEmptyOrNull(excludes)) {
                    if (excludes.contains(DbUtil.getColumnName(fieldName)) || excludes.contains(fieldName)) {
                        continue;
                    }
                }
                if (!DbUtil.isEmptyOrNull(includes)) {
                    if (!includes.contains(fieldName) && !includes.contains(DbUtil.getColumnName(fieldName))) {
                        continue;
                    }
                }
                if (dbf.canUpdate() && !isImmutableField(fieldName)) {
                    Type type = field.getGenericType();
                    Object value = DbUtil.getFieldValue(o, field);
                    if (ignoreNull) {
                        if (!DbUtil.isEmptyOrNull(value) && !"0".equals(value.toString())) {
                            checkFieldValue(field, value, false);
                            sbr.append(this.comma).append(DbUtil.getColumnName(fieldName)).append(this.comma).append("=");
                            appendValue(sbr, type, value, parameters);
                            hasUpdateField = true;
                        }
                    } else {
                        if (null == value && type == Date.class) {
                            continue;
                        }
                        sbr.append(this.comma).append(DbUtil.getColumnName(fieldName)).append(this.comma).append("=");
                        appendValue(sbr, type, value, parameters);
                        hasUpdateField = true;
                    }

                }
            }
        }
        if (!hasUpdateField) {
            throw new RuntimeException("no field to UPDATE ,#SQL:{}" + sbr.toString());
        }
        if (sbr.toString().endsWith(",")) {
            sbr.deleteCharAt(sbr.length() - 1);
        }
        //where
        if (null != OBuilder && !DbUtil.isEmptyOrNull(OBuilder.getWheres())) {
            List<OBuilder.Where> wheres = OBuilder.getWheres();
            if (!DbUtil.isEmptyOrNull(wheres)) {
                sbr.append(" WHERE ");
                wheres.forEach(wh -> {
                    if (ignoreNull) {
                        if (null != wh.value) {
                            sbr.append(this.comma).append(DbUtil.getColumnName(wh.key)).append(this.comma).append(" ").append(wh.op).append(" ");
                            appendValue(sbr, wh.value, parameters);
                            sbr.append(" AND ");
                        }
                    } else {
                        sbr.append(this.comma).append(DbUtil.getColumnName(wh.key)).append(this.comma).append(" ").append(wh.op).append(" ");
                        appendValue(sbr, wh.value, parameters);
                        sbr.append(" AND ");
                    }
                });
                sbr.delete(sbr.length() - 5, sbr.length());
            }
            if (DIALECT_MYSQL == dialect) {
                //mysql innodb 锁
                if (OBuilder.forUpdate) {
                    sbr.append(" FOR UPDATE ");
                }
                if (OBuilder.forShare) {
                    sbr.append(" LOCK IN SHARE MODE");
                }
            }
        } else {
            try {
                Field f = DbUtil.getField(o, "id");
                sbr.append(" WHERE id=?");
                parameters.add(DbUtil.getFieldValue(o, f));
            } catch (Exception e) {
                throw new IllegalArgumentException("tkf id is not exist!!!");
            }
        }
        sbr.append(";");
        sqlEntity.sql = sbr.toString();
        sqlEntity.parameters = parameters.toArray();
        return sqlEntity;
    }

    public SQLEntity updateByWhere(Object o, OBuilder builder) {
        return updateNative(o, builder, false, null, null);
    }


    public SQLEntity updateByWhere(Object o, OBuilder builder, List<String> includes) {
        return updateNative(o, builder, false, includes, null);
    }

    public SQLEntity updateByWhere(Object o, OBuilder builder, List<String> includes, List<String> excludes) {
        return updateNative(o, builder, false, includes, excludes);
    }

    public SQLEntity update(String sql, Object... params) {
        SQLEntity sqlEntity = new SQLEntity();
        sqlEntity.sql = sql;
        sqlEntity.parameters = params;
        return sqlEntity;
    }

    public SQLEntity updateSelective(Object o, List<String> includes, List<String> excludes) {
        return updateNative(o, null, true, includes, excludes);
    }

    public SQLEntity updateSelectiveByWhere(Object o, OBuilder OBuilder) {
        return updateNative(o, OBuilder, true, null, null);
    }
//endregion update


    //region delete
    public SQLEntity deleteById(Class<?> clz, Object id) {
        SQLEntity sqlEntity = new SQLEntity();
        List<Object> parameters = new ArrayList<>();

        if (null != id) {
            parameters.add(id);
        } else {
            throw new IllegalArgumentException("null value with id!!!");
        }
        sqlEntity.sql = "DELETE FROM " + getTableName(clz, clz.getSimpleName()) + " WHERE id= ?";
        sqlEntity.parameters = parameters.toArray();
        return sqlEntity;
    }


    public SQLEntity delete(Class<?> clz, OBuilder OBuilder) {
        SQLEntity sqlEntity = new SQLEntity();
        List<Object> parameters = new ArrayList<>();
        StringBuilder sbr = new StringBuilder("DELETE FROM ");
        sbr.append(getTableName(clz, clz.getSimpleName()));
        if (null != OBuilder) {
            List<OBuilder.Where> wheres = OBuilder.getWheres();
            if (!DbUtil.isEmptyOrNull(wheres)) {
                sbr.append(" WHERE 1=1");
                wheres.forEach(wh -> {
                    sbr.append(" AND ");
                    appendBuilderWhere(sbr, wh, "", parameters);
                });
            }
        } else {
            throw new IllegalArgumentException(" delete with no where condition exist!!!");
        }
        sqlEntity.sql = sbr.toString();
        sqlEntity.parameters = parameters.toArray();
        return sqlEntity;
    }

    //endregion delete

    private String appendWhereLimit(OBuilder OBuilder, StringBuilder sbr) {
        if (null != OBuilder) {
            if (OBuilder.limitDelta() > 0) {
                if (DIALECT_MYSQL == dialect) {
                    sbr.append(" LIMIT ");
                    if (OBuilder.limitStart() > 0) {
                        sbr.append(OBuilder.limitStart()).append(",");
                    }
                    if (OBuilder.limitDelta() > 0) {
                        sbr.append(OBuilder.limitDelta());
                    }
                    return null;
                } else if (DIALECT_MSSQL == dialect) {
                    sbr.append(" OFFSET ");
                    if (OBuilder.limitStart() >= 0) {
                        sbr.append(OBuilder.limitStart()).append(" ROWS ");
                    }
                    if (OBuilder.limitDelta() > 0) {
                        sbr.append(" FETCH NEXT  ").append(OBuilder.limitDelta()).append(" ROWS ONLY  ");
                    }
                    return null;
                } else if (DIALECT_PGSQL == dialect) {
                    sbr.append(" LIMIT ");

                    if (OBuilder.limitDelta() > 0) {
                        sbr.append(OBuilder.limitDelta());
                    }
                    if (OBuilder.limitStart() >= 0) {
                        sbr.append(" OFFSET ").append(OBuilder.limitStart());
                    }
                    return null;
                } else if (DIALECT_ORACLE == dialect) {
                    StringBuilder sr = new StringBuilder();
                    sr.append("SELECT * FROM (SELECT o.*,ROWNUM as rowno FROM (").append(sbr.toString()).append(") o ");
                    if (OBuilder.limitDelta() > 0) {
                        sr.append(" WHERE ROWNUM<=").append(OBuilder.limitDelta()).append(")  o1");
                    }
                    if (OBuilder.limitStart() >= 0) {
                        sr.append(" WHERE o1.rowno>").append(OBuilder.limitStart());
                    }
                    return sr.toString();
                }
            }
        }
        return null;
    }

    /**
     * 拼接Builder 条件
     */
    private void appendBuilderWhere(StringBuilder sbr, OBuilder.Where wh, String tbAlias, List<Object> parameters) {
        if (DbUtil.isEmptyOrNull(wh.sql)) {
            if (!DbUtil.isEmptyOrNull(tbAlias)) {
                sbr.append(tbAlias).append(".");
            }
            sbr.append(this.comma).append(DbUtil.getColumnName(wh.key)).append(this.comma).append(" ").append(wh.op);
            String op = wh.op;
            Object value = wh.value;
            if ("in".equals(op.trim()) || "not in".equals(op.trim())) {
                sbr.append("(");
                for (int i = 0; i < Array.getLength(value); i++) {
                    Object v = Array.get(value, i);
                    sbr.append("?");
                    parameters.add(v);
                   /* if (v.getClass() == String.class) {
                        sbr.append("'").append(DbUtil.injectDefend(v.toString())).append("'");
                    } else {
                        sbr.append(v);
                    }*/
                    if (i != Array.getLength(value) - 1) {
                        sbr.append(",");
                    }
                }
                sbr.append(")");
            } else {
                //转换取值方式
                sbr.append("?");
                parameters.add(value);
           /*     if (value.getClass() == String.class) {
                    sbr.append("'").append(value).append("'");
                } else if (wh.value.getClass() == Date.class) {
                    sbr.append("'").append(new Timestamp(((Date) value).getTime())).append("'");
                } else {

//                    sbr.append(value);
                }*/
            }
        } else {
            //别名判断
            sbr.append(" (");
            sbr.append(appendWhereSql(wh.sql, wh.sqlValues, tbAlias, parameters));
            sbr.append(") ");
        }
    }

    /**
     * whereSql拼接，需要注意json类型处理
     *
     * @param whereSql
     * @param val
     * @param tbAlias
     * @param parameters
     * @return
     */
    private String appendWhereSql(String whereSql, List<Object> val, String tbAlias, List<Object> parameters) {
//        parameters.addAll(val);
        if (!DbUtil.isEmptyOrNull(whereSql)) {
            if (!DbUtil.isEmptyOrNull(val)) {
                if (whereSql.contains("?")) {
                    String regex = "\\?";
                    for (Object o : val) {
                        if (o.getClass() == String.class) {
                            whereSql = whereSql.replaceFirst(regex, "'" + DbUtil.injectDefend(o.toString()) + "'");
                        } else if (o.getClass() == Date.class) {
                            whereSql = whereSql.replaceFirst(regex, "'" + (new Timestamp(((Date) o).getTime())) + "'");
                        } else {
                            whereSql = whereSql.replaceFirst(regex, DbUtil.injectDefend(o.toString()));
                        }
                    }
                    while (whereSql.contains("?")) {
                        for (Object o : val) {
                            if (o.getClass() == String.class) {
                                whereSql = whereSql.replaceFirst(regex, "'" + DbUtil.injectDefend(o.toString()) + "'");
                            } else if (o.getClass() == Date.class) {
                                whereSql = whereSql.replaceFirst(regex, "'" + (new Timestamp(((Date) o).getTime())) + "'");
                            } else {
                                whereSql = whereSql.replaceFirst(regex, DbUtil.injectDefend(o.toString()));
                            }
                        }
                    }
                } else {
                    if (val.size() == 1 && val.get(0) instanceof Boolean) {
                        if (!(Boolean) val.get(0)) {
                            return null;
                        }
                    }
                }
            }
            String[] splits = whereSql.split(" AND ");
            boolean containsComma = false;
            if (splits.length > 1) {
                StringBuilder sbd = new StringBuilder();
                for (int i = 0; i < splits.length; i++) {
                    if (splits[i].startsWith("(")) {
                        containsComma = true;
                        sbd.append("(");
                        if (!DbUtil.isEmptyOrNull(tbAlias)) {
                            sbd.append(tbAlias).append(".");
                        }
                        sbd.append(splits[i].trim().substring(1));
                    } else {
                        if (!DbUtil.isEmptyOrNull(tbAlias)) {
                            sbd.append(tbAlias).append(".");
                        }
                        sbd.append(splits[i].trim());
                    }
                    if (i != splits.length - 1) {
                        sbd.append(" AND ");
                    }
                }
                whereSql = sbd.toString();
            }
            String[] splits1 = whereSql.split(" OR ");
            if (splits1.length > 1) {
                StringBuilder sbd = new StringBuilder();
                for (int i = 0; i < splits1.length; i++) {
                    if (splits1[i].startsWith("(")) {
                        if (!containsComma) {
                            sbd.append("(");
                            if (!DbUtil.isEmptyOrNull(tbAlias)) {
                                sbd.append(tbAlias).append(".");
                            }
                            sbd.append(splits1[i].trim().substring(1));
                        }
                    } else {
                        if (!DbUtil.isEmptyOrNull(tbAlias)) {
                            sbd.append(tbAlias).append(".");
                        }
                        sbd.append(splits1[i].trim());
                    }
                    if (i != splits1.length - 1) {
                        sbd.append(" OR ");
                    }
                }
                whereSql = sbd.toString();
            }
        }
        return whereSql;
    }


    private void appendValue(StringBuilder sbr, Type type, Object value, List<Object> parameters) {
        sbr.append("?").append(",");
        parameters.add(value);
//        if (DbUtil.isGenericType(type)) {
//            parameters.add(value);
//        } else {
//            parameters.add(JacksonUtil.toJSONString(value));
//        }
     /*   if (type == String.class) {
            if (null == value) {
                sbr.append("?").append(", ");
                parameters.add("");
            } else {
                sbr.append("?").append(", ");
                parameters.add(value);
//                sbr.append("'");
             *//*   String v = (String) value;
                if (v.contains("'")) {
                    sbr.append(DbUtil.injectDefend(v.replaceAll("'", "\\\\'")));
                } else {
                    sbr.append(DbUtil.injectDefend(v));
                }
                sbr.append("'").append(",");*//*
            }
        } else if (DbUtil.isGenericType(type)) {
            sbr.append("?").append(", ");
            parameters.add(value);
         *//*   if (null == value) {
                sbr.append(0).append(",");
            } else {
                sbr.append(value).append(",");
            }*//*
        } else if (type == Date.class) {
            sbr.append("?").append(", ");
            parameters.add(value);
        } else {

        }*/
    }

    private void appendValue(StringBuilder sbr, Object value, List<Object> parameters) {
        sbr.append(" ? ");
        parameters.add(value);
      /*  if (null != value) {
            if (value instanceof String) {
                sbr.append("'");
                String v = (String) value;
                if (v.contains("'")) {
                    sbr.append(DbUtil.injectDefend(v.replaceAll("'", "\\\\'")));
                } else {
                    sbr.append(DbUtil.injectDefend(v));
                }
                sbr.append("'").append(",");
            } else if (DbUtil.isGenericType(value)) {
                sbr.append(value).append(",");
            } else if (value instanceof Date) {
                sbr.append("'").append(new Timestamp(((Date) value).getTime())).append("'").append(",");
            } else {
                sbr.append("'").append(JacksonUtil.toJSONString(value)).append("'").append(",");
            }
        } else {
            //类型默认值
            sbr.append(" ").append(",");
        }*/
    }


    private void getValue(StringBuilder sbr, Object value, List<Object> parameters) {
        if (null != value) {
//            if (value instanceof String) {
//                sbr.append("'").append(value).append("'");
//            } else if (DbUtil.isGenericType(value)) {
//                sbr.append(value);
//            } else if (value instanceof Date) {
//                sbr.append("'").append(new Timestamp(((Date) value).getTime())).append("'");
//            } else {
//                sbr.append("'").append(JacksonUtil.toJSONString(value)).append("'");
//            }
            sbr.append("?");
            parameters.add(value);
        } else {
            throw new IllegalArgumentException("append value with NULL");
        }
    }


    private String getTableName(Entity anno) {
        String tbName = anno.tbName();
        if (!DbUtil.isEmptyOrNull(tbName)) {
            return DbUtil.getColumnName(tbName);
        }
        Class<?> clz = anno.clz();
       /* if (tbName.endsWith("Info")) {
            tbName = tbName.substring(0, tbName.length() - 4);
        }*/
        return JdbcHelper.tbPrefix + DbUtil.getColumnName(clz.getSimpleName());
    }

    public static String getTableName(Class<?> clz, String tbName) {
        if (clz.isAnnotationPresent(Entity.class)) {
            Entity entity = clz.getAnnotation(Entity.class);
            if (!DbUtil.isEmptyOrNull(entity.tbName())) {
                return DbUtil.getColumnName(entity.tbName());
            }
        } else if (clz.getSuperclass().isAnnotationPresent(Entity.class)) {
            Class<?> superClz = clz.getSuperclass();
            Entity entity = superClz.getAnnotation(Entity.class);
            if (!DbUtil.isEmptyOrNull(entity.tbName())) {
                return DbUtil.getColumnName(entity.tbName());
            }
        }
        if (tbName.endsWith("Info")) {
            tbName = tbName.substring(0, tbName.length() - 4);
        }
        return JdbcHelper.tbPrefix + DbUtil.getColumnName(tbName);
    }


    /**
     * 获取外键关联实体类
     */
    private Class<?> getFkDomainClass(Field f, Class<?> domainClass) {
        try {
            if (f.isAnnotationPresent(FK.class)) {
                return f.getAnnotation(FK.class).clz();
            }
            if (f.isAnnotationPresent(One.class)) {
                One oneAnnotation = f.getAnnotation(One.class);
                if (null != oneAnnotation) {
                    if (oneAnnotation.te() != void.class) {
                        return oneAnnotation.te();
                    }
                    if (!DbUtil.isEmptyOrNull(oneAnnotation.mkf())) {
                        if (DbUtil.isGenericType(f.getType())) {
                            Field field = domainClass.getDeclaringClass().getDeclaredField(oneAnnotation.mkf());
                            if (field.isAnnotationPresent(FK.class)) {
                                return field.getAnnotation(FK.class).clz();
                            }
                        } else {
                            return f.getDeclaringClass();
                        }
                    }
                }
            }
            if (f.isAnnotationPresent(QF.class)) {
                QF qf = f.getAnnotation(QF.class);
                if (!DbUtil.isEmptyOrNull(qf.pkf())) {
                    Field field = domainClass.getDeclaringClass().getDeclaredField(qf.pkf());
                    return field.getAnnotation(FK.class).clz();
                }
            }
        } catch (NoSuchFieldException e) {
            logger.error("SQL2Helper ERR# getFkDomainClass ,fileName:{},class:{}", f.getName(), domainClass.getSimpleName());
        }
        return Void.class;
    }

    private boolean isImmutableField(String fieldName) {
        List<String> immutableFields = new ArrayList<>();
        immutableFields.add("id");
        immutableFields.add("createat");
        immutableFields.add("updateat");
        return immutableFields.contains(fieldName.toLowerCase());
    }

    /**
     * 校验字段的值
     */
    private void checkFieldValue(Field field, Object value, boolean insertCheck) {
        if (!field.isAnnotationPresent(DBF.class)) {
            return;
        }
        DBF dbf = field.getAnnotation(DBF.class);

        //插入SQL校验必填
        if (insertCheck) {
            if (dbf.required() && DbUtil.isEmptyOrNull(value)) {
                throw new IllegalArgumentException("【" + dbf.comment() + "】取值不能为空");
            }
        }
        int min = dbf.min();
        int max = dbf.max();
        if (dbf.min() > 0) {
            if (value instanceof Integer || value instanceof Short || value instanceof Double || value instanceof Long) {
                if (BigDecimal.valueOf(min).compareTo(new BigDecimal(String.valueOf(value))) > 0) {
                    throw new IllegalArgumentException("【" + dbf.comment() + "】最小值为" + min);
                }
                if (max > 0 && BigDecimal.valueOf(max).compareTo(new BigDecimal(value.toString())) < 0) {
                    throw new IllegalArgumentException("【" + dbf.comment() + "】最大值为" + max);
                }
            } else if (value instanceof String || value instanceof Character) {
                if (!DbUtil.isEmptyOrNull(value)) {
                    int length = value.toString().length();
                    if (min > length) {
                        throw new IllegalArgumentException("【" + dbf.comment() + "】最小长度为" + min);
                    }
                    if (max > 0 && max < length) {
                        throw new IllegalArgumentException("【" + dbf.comment() + "】最大长度为" + max);
                    }
                }
            }
        }


    }
}