Examples with FieldMeta org.sagacity.sqltoy.config.model.FieldMeta used on opensource projects

Example 16 with FieldMeta

use of org.sagacity.sqltoy.config.model.FieldMeta in project sagacity-sqltoy by chenrenfei.

the class DialectUtils method getSaveOrUpdateSql.

/**
 * @todo 处理加工对象基于db2、oracle、informix、sybase数据库的saveOrUpdateSql
 * @param dbType
 * @param entityMeta
 * @param pkStrategy
 * @param forceUpdateFields
 * @param fromTable
 * @param isNullFunction
 * @param sequence
 * @param isAssignPK
 * @param tableName
 * @return
 */
public static String getSaveOrUpdateSql(Integer dbType, EntityMeta entityMeta, PKStrategy pkStrategy, String[] forceUpdateFields, String fromTable, String isNullFunction, String sequence, boolean isAssignPK, String tableName) {
    String realTable = (tableName == null) ? entityMeta.getSchemaTable() : tableName;
    // 在无主键的情况下产生insert sql语句
    if (entityMeta.getIdArray() == null) {
        return generateInsertSql(dbType, entityMeta, pkStrategy, isNullFunction, sequence, isAssignPK, realTable);
    } else {
        boolean isSupportNUL = StringUtil.isBlank(isNullFunction) ? false : true;
        int columnSize = entityMeta.getFieldsArray().length;
        StringBuilder sql = new StringBuilder(columnSize * 30 + 100);
        String columnName;
        sql.append("merge into ");
        sql.append(entityMeta.getSchemaTable());
        sql.append(" ta ");
        sql.append(" using (select ");
        for (int i = 0; i < columnSize; i++) {
            columnName = entityMeta.getColumnName(entityMeta.getFieldsArray()[i]);
            if (i > 0)
                sql.append(",");
            sql.append("? as ");
            sql.append(columnName);
        }
        if (StringUtil.isNotBlank(fromTable))
            sql.append(" from ").append(fromTable);
        sql.append(") tv on (");
        StringBuilder idColumns = new StringBuilder();
        // 组织on部分的主键条件判断
        for (int i = 0, n = entityMeta.getIdArray().length; i < n; i++) {
            columnName = entityMeta.getColumnName(entityMeta.getIdArray()[i]);
            if (i > 0) {
                sql.append(" and ");
                idColumns.append(",");
            }
            sql.append(" ta.").append(columnName).append("=tv.").append(columnName);
            idColumns.append("ta.").append(columnName);
        }
        sql.append(" ) ");
        // 排除id的其他字段信息
        StringBuilder insertRejIdCols = new StringBuilder();
        StringBuilder insertRejIdColValues = new StringBuilder();
        // 是否全部是ID,匹配上则无需进行更新，只需将未匹配上的插入即可
        boolean allIds = (entityMeta.getRejectIdFieldArray() == null);
        if (!allIds) {
            // update 操作
            sql.append(" when matched then update set ");
            int rejectIdColumnSize = entityMeta.getRejectIdFieldArray().length;
            // 需要被强制修改的字段
            HashMap<String, String> fupc = new HashMap<String, String>();
            if (forceUpdateFields != null) {
                for (String field : forceUpdateFields) fupc.put(entityMeta.getColumnName(field), "1");
            }
            FieldMeta fieldMeta;
            // update 只针对非主键字段进行修改
            for (int i = 0; i < rejectIdColumnSize; i++) {
                fieldMeta = entityMeta.getFieldMeta(entityMeta.getRejectIdFieldArray()[i]);
                columnName = fieldMeta.getColumnName();
                if (i > 0) {
                    sql.append(",");
                    insertRejIdCols.append(",");
                    insertRejIdColValues.append(",");
                }
                sql.append(" ta.").append(columnName).append("=");
                // 强制修改
                if (fupc.containsKey(columnName)) {
                    sql.append("tv.").append(columnName);
                } else {
                    sql.append(isNullFunction);
                    sql.append("(tv.").append(columnName);
                    sql.append(",ta.").append(columnName);
                    sql.append(")");
                }
                insertRejIdCols.append(columnName);
                // 存在默认值
                if (isSupportNUL && StringUtil.isNotBlank(fieldMeta.getDefaultValue())) {
                    insertRejIdColValues.append(isNullFunction);
                    insertRejIdColValues.append("(tv.").append(columnName).append(",");
                    processDefaultValue(insertRejIdColValues, dbType, fieldMeta.getType(), fieldMeta.getDefaultValue());
                    insertRejIdColValues.append(")");
                } else
                    insertRejIdColValues.append("tv.").append(columnName);
            }
        }
        // 主键未匹配上则进行插入操作
        sql.append(" when not matched then insert (");
        String idsColumnStr = idColumns.toString();
        // 不考虑只有一个字段且还是主键的情况
        if (allIds) {
            sql.append(idsColumnStr.replaceAll("ta.", ""));
            sql.append(") values (");
            sql.append(idsColumnStr.replaceAll("ta.", "tv."));
        } else {
            sql.append(insertRejIdCols.toString());
            // sequence方式主键
            if (pkStrategy.equals(PKStrategy.SEQUENCE)) {
                columnName = entityMeta.getColumnName(entityMeta.getIdArray()[0]);
                sql.append(",");
                sql.append(columnName);
                sql.append(") values (");
                sql.append(insertRejIdColValues).append(",");
                if (isAssignPK && isSupportNUL) {
                    sql.append(isNullFunction);
                    sql.append("(tv.").append(columnName).append(",");
                    sql.append(sequence).append(") ");
                } else {
                    sql.append(sequence);
                }
            } else if (pkStrategy.equals(PKStrategy.IDENTITY)) {
                columnName = entityMeta.getColumnName(entityMeta.getIdArray()[0]);
                if (isAssignPK) {
                    sql.append(",");
                    sql.append(columnName);
                }
                sql.append(") values (");
                // identity 模式insert无需写插入该字段语句
                sql.append(insertRejIdColValues);
                if (isAssignPK) {
                    sql.append(",").append("tv.").append(columnName);
                }
            } else {
                sql.append(",");
                sql.append(idsColumnStr.replaceAll("ta.", ""));
                sql.append(") values (");
                sql.append(insertRejIdColValues).append(",");
                sql.append(idsColumnStr.replaceAll("ta.", "tv."));
            }
        }
        sql.append(")");
        return sql.toString();
    }
}

Example 17 with FieldMeta

use of org.sagacity.sqltoy.config.model.FieldMeta in project sagacity-sqltoy by chenrenfei.

the class DialectUtils method generateUpdateSql.

/**
 * @todo 产生对象update的语句
 * @param dbType
 * @param entityMeta
 * @param nullFunction
 * @param forceUpdateFields
 * @param tableName
 * @return
 */
private static String generateUpdateSql(Integer dbType, EntityMeta entityMeta, String nullFunction, String[] forceUpdateFields, String tableName) {
    if (entityMeta.getIdArray() == null) {
        return null;
    }
    StringBuilder sql = new StringBuilder(entityMeta.getFieldsArray().length * 30 + 30);
    sql.append(" update  ");
    sql.append(tableName);
    sql.append(" set ");
    String columnName;
    // 需要被强制修改的字段
    HashSet<String> fupc = new HashSet<String>();
    if (forceUpdateFields != null) {
        for (String field : forceUpdateFields) {
            fupc.add(ReservedWordsUtil.convertWord(entityMeta.getColumnName(field), dbType));
        }
    }
    FieldMeta fieldMeta;
    boolean convertBlob = (dbType == DBType.POSTGRESQL || dbType == DBType.GAUSSDB || dbType == DBType.KINGBASE);
    boolean isMSsql = (dbType == DBType.SQLSERVER);
    int meter = 0;
    for (int i = 0, n = entityMeta.getRejectIdFieldArray().length; i < n; i++) {
        fieldMeta = entityMeta.getFieldMeta(entityMeta.getRejectIdFieldArray()[i]);
        // 排除sqlserver timestamp类型
        if (!(isMSsql && fieldMeta.getType() == java.sql.Types.TIMESTAMP)) {
            columnName = ReservedWordsUtil.convertWord(fieldMeta.getColumnName(), dbType);
            if (meter > 0) {
                sql.append(",");
            }
            sql.append(columnName);
            sql.append("=");
            if (fupc.contains(columnName)) {
                sql.append("?");
            } else {
                // 2020-6-13 修复postgresql\kingbase\guassdb bytea类型处理错误
                if (convertBlob && fieldMeta.getFieldType().equals("byte[]")) {
                    sql.append(nullFunction);
                    sql.append("(cast(? as bytea),").append(columnName).append(" )");
                // sql.append(" cast(");
                // sql.append(nullFunction);
                // sql.append("(cast(? as
                // varchar),").append("cast(").append(columnName).append(" as varchar))");
                // sql.append(" as bytea)");
                } else {
                    sql.append(nullFunction);
                    sql.append("(?,").append(columnName).append(")");
                }
            }
            meter++;
        }
    }
    sql.append(" where ");
    for (int i = 0, n = entityMeta.getIdArray().length; i < n; i++) {
        columnName = entityMeta.getColumnName(entityMeta.getIdArray()[i]);
        columnName = ReservedWordsUtil.convertWord(columnName, dbType);
        if (i > 0) {
            sql.append(" and ");
        }
        sql.append(columnName);
        sql.append("=?");
    }
    return sql.toString();
}

Example 18 with FieldMeta

use of org.sagacity.sqltoy.config.model.FieldMeta in project sagacity-sqltoy by chenrenfei.

the class MySqlDialectUtils method getSaveOrUpdateSql.

/**
 * @todo 产生mysql数据库的saveOrUpdate操作sql语句
 * @param dbType
 * @param entityMeta
 * @param forceUpdateFields
 * @param tableName
 * @return
 */
public static String getSaveOrUpdateSql(Integer dbType, EntityMeta entityMeta, String[] forceUpdateFields, String tableName) {
    String realTable = entityMeta.getSchemaTable(tableName, dbType);
    if (entityMeta.getIdArray() == null) {
        return DialectExtUtils.generateInsertSql(dbType, entityMeta, entityMeta.getIdStrategy(), "ifnull", null, false, realTable);
    }
    StringBuilder sql;
    // 是否全部是ID
    boolean allIds = (entityMeta.getRejectIdFieldArray() == null);
    // 全部是主键采用replace into 策略进行保存或修改,不考虑只有一个字段且是主键的表情况
    if (allIds) {
        sql = new StringBuilder("replace into ");
    } else {
        sql = new StringBuilder("insert into ");
    }
    StringBuilder values = new StringBuilder();
    sql.append(realTable);
    sql.append(" (");
    FieldMeta fieldMeta;
    String fieldName;
    for (int i = 0, n = entityMeta.getFieldsArray().length; i < n; i++) {
        if (i > 0) {
            sql.append(",");
            values.append(",");
        }
        fieldName = entityMeta.getFieldsArray()[i];
        fieldMeta = entityMeta.getFieldMeta(fieldName);
        // sql中的关键字处理
        sql.append(ReservedWordsUtil.convertWord(fieldMeta.getColumnName(), dbType));
        // 默认值处理
        if (null != fieldMeta.getDefaultValue()) {
            values.append("ifnull(?,");
            DialectExtUtils.processDefaultValue(values, dbType, fieldMeta.getType(), fieldMeta.getDefaultValue());
            values.append(")");
        } else {
            values.append("?");
        }
    }
    sql.append(") values (");
    sql.append(values);
    sql.append(") ");
    // 非全部是主键
    if (!allIds) {
        // 当主键存在则进行修改操作
        sql.append(" ON DUPLICATE KEY UPDATE ");
        // 需要被强制修改的字段
        HashSet<String> fupc = new HashSet<String>();
        if (forceUpdateFields != null) {
            for (String field : forceUpdateFields) {
                fupc.add(entityMeta.getColumnName(field));
            }
        }
        String columnName;
        for (int i = 0, n = entityMeta.getRejectIdFieldArray().length; i < n; i++) {
            columnName = entityMeta.getColumnName(entityMeta.getRejectIdFieldArray()[i]);
            if (i > 0) {
                sql.append(",");
            }
            sql.append(columnName).append("=");
            // 强制修改
            if (fupc.contains(columnName)) {
                sql.append("values(").append(columnName).append(")");
            } else {
                sql.append("ifnull(values(");
                sql.append(columnName).append("),");
                sql.append(columnName).append(")");
            }
        }
    }
    return sql.toString();
}

Example 19 with FieldMeta

use of org.sagacity.sqltoy.config.model.FieldMeta in project sagacity-sqltoy by chenrenfei.

the class SqlServerDialectUtils method generateInsertSql.

/**
 * @todo 产生对象对应的insert sql语句
 * @param dbType
 * @param entityMeta
 * @param tableName
 * @param pkStrategy
 * @param isNullFunction
 * @param sequence
 * @param isAssignPK
 * @return
 */
public static String generateInsertSql(Integer dbType, EntityMeta entityMeta, String tableName, PKStrategy pkStrategy, String isNullFunction, String sequence, boolean isAssignPK) {
    int columnSize = entityMeta.getFieldsArray().length;
    StringBuilder sql = new StringBuilder(columnSize * 20 + 30);
    StringBuilder values = new StringBuilder(columnSize * 2 - 1);
    sql.append(" insert into ");
    sql.append(entityMeta.getSchemaTable(tableName, dbType));
    sql.append(" (");
    FieldMeta fieldMeta;
    String field;
    boolean isStart = true;
    String columnName;
    for (int i = 0; i < columnSize; i++) {
        field = entityMeta.getFieldsArray()[i];
        fieldMeta = entityMeta.getFieldMeta(field);
        columnName = ReservedWordsUtil.convertWord(fieldMeta.getColumnName(), dbType);
        if (fieldMeta.isPK()) {
            // identity主键策略，且支持主键手工赋值
            if (pkStrategy.equals(PKStrategy.IDENTITY)) {
                if (isAssignPK) {
                    if (!isStart) {
                        sql.append(",");
                        values.append(",");
                    }
                    sql.append(columnName);
                    values.append("?");
                    isStart = false;
                }
            } else if (pkStrategy.equals(PKStrategy.SEQUENCE)) {
                if (!isStart) {
                    sql.append(",");
                    values.append(",");
                }
                sql.append(columnName);
                if (isAssignPK) {
                    values.append(isNullFunction);
                    values.append("(?,").append(sequence).append(")");
                } else {
                    values.append(sequence);
                }
                isStart = false;
            } else if (fieldMeta.getType() != java.sql.Types.TIMESTAMP) {
                if (!isStart) {
                    sql.append(",");
                    values.append(",");
                }
                sql.append(columnName);
                values.append("?");
                isStart = false;
            }
        } else if (fieldMeta.getType() != java.sql.Types.TIMESTAMP) {
            if (!isStart) {
                sql.append(",");
                values.append(",");
            }
            sql.append(fieldMeta.getColumnName());
            if (null != fieldMeta.getDefaultValue()) {
                values.append(isNullFunction);
                values.append("(?,");
                DialectExtUtils.processDefaultValue(values, dbType, fieldMeta.getType(), fieldMeta.getDefaultValue());
                values.append(")");
            } else {
                values.append("?");
            }
            isStart = false;
        }
    }
    sql.append(") values (");
    sql.append(values);
    sql.append(")");
    return sql.toString();
}

Example 20 with FieldMeta

use of org.sagacity.sqltoy.config.model.FieldMeta in project sagacity-sqltoy by chenrenfei.

the class ClickHouseDialectUtils method generateUpdateSql.

private static String generateUpdateSql(Integer dbType, EntityMeta entityMeta, String nullFunction, String[] forceUpdateFields, String tableName) {
    if (entityMeta.getIdArray() == null) {
        return null;
    }
    StringBuilder sql = new StringBuilder(entityMeta.getFieldsArray().length * 30 + 30);
    sql.append(" alter table  ");
    sql.append(tableName);
    sql.append(" update ");
    String columnName;
    // 需要被强制修改的字段
    HashSet<String> fupc = new HashSet<String>();
    if (forceUpdateFields != null) {
        for (String field : forceUpdateFields) {
            fupc.add(ReservedWordsUtil.convertWord(entityMeta.getColumnName(field), dbType));
        }
    }
    FieldMeta fieldMeta;
    int meter = 0;
    for (int i = 0, n = entityMeta.getRejectIdFieldArray().length; i < n; i++) {
        fieldMeta = entityMeta.getFieldMeta(entityMeta.getRejectIdFieldArray()[i]);
        // 排除分区字段
        if (!fieldMeta.isPartitionKey()) {
            columnName = ReservedWordsUtil.convertWord(fieldMeta.getColumnName(), dbType);
            if (meter > 0) {
                sql.append(",");
            }
            sql.append(columnName);
            sql.append("=");
            if (fupc.contains(columnName)) {
                sql.append("?");
            } else {
                sql.append(nullFunction);
                sql.append("(?,").append(columnName).append(")");
            }
            meter++;
        }
    }
    sql.append(" where ");
    for (int i = 0, n = entityMeta.getIdArray().length; i < n; i++) {
        columnName = entityMeta.getColumnName(entityMeta.getIdArray()[i]);
        columnName = ReservedWordsUtil.convertWord(columnName, dbType);
        if (i > 0) {
            sql.append(" and ");
        }
        sql.append(columnName);
        sql.append("=?");
    }
    return sql.toString();
}