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

Example 6 with FieldMeta

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

the class DialectUtils method getSaveOrUpdateSql.

/**
 * @todo 处理加工对象基于db2、oracle、sqlserver数据库的saveOrUpdateSql
 * @param unifyFieldsHandler
 * @param dbType
 * @param entityMeta
 * @param pkStrategy
 * @param forceUpdateFields
 * @param fromTable
 * @param isNullFunction
 * @param sequence
 * @param isAssignPK
 * @param tableName
 * @return
 */
public static String getSaveOrUpdateSql(IUnifyFieldsHandler unifyFieldsHandler, Integer dbType, EntityMeta entityMeta, PKStrategy pkStrategy, String[] forceUpdateFields, String fromTable, String isNullFunction, String sequence, boolean isAssignPK, String tableName) {
    String realTable = entityMeta.getSchemaTable(tableName, dbType);
    // 在无主键的情况下产生insert sql语句
    if (entityMeta.getIdArray() == null) {
        return DialectExtUtils.generateInsertSql(dbType, entityMeta, pkStrategy, isNullFunction, sequence, isAssignPK, realTable);
    }
    // 将新增记录统一赋值属性模拟成默认值模式
    IgnoreKeyCaseMap<String, Object> createUnifyFields = null;
    if (unifyFieldsHandler != null && unifyFieldsHandler.createUnifyFields() != null && !unifyFieldsHandler.createUnifyFields().isEmpty()) {
        createUnifyFields = new IgnoreKeyCaseMap<String, Object>();
        createUnifyFields.putAll(unifyFieldsHandler.createUnifyFields());
    }
    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(realTable);
    sql.append(" ta ");
    sql.append(" using (select ");
    for (int i = 0; i < columnSize; i++) {
        columnName = entityMeta.getColumnName(entityMeta.getFieldsArray()[i]);
        columnName = ReservedWordsUtil.convertWord(columnName, dbType);
        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]);
        columnName = ReservedWordsUtil.convertWord(columnName, dbType);
        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;
        // 需要被强制修改的字段
        HashSet<String> fupc = new HashSet<String>();
        if (forceUpdateFields != null) {
            for (String field : forceUpdateFields) {
                fupc.add(ReservedWordsUtil.convertWord(entityMeta.getColumnName(field), dbType));
            }
        }
        FieldMeta fieldMeta;
        String defaultValue;
        // update 只针对非主键字段进行修改
        for (int i = 0; i < rejectIdColumnSize; i++) {
            fieldMeta = entityMeta.getFieldMeta(entityMeta.getRejectIdFieldArray()[i]);
            columnName = ReservedWordsUtil.convertWord(fieldMeta.getColumnName(), dbType);
            if (i > 0) {
                sql.append(",");
                insertRejIdCols.append(",");
                insertRejIdColValues.append(",");
            }
            sql.append(" ta.").append(columnName).append("=");
            // 强制修改
            if (fupc.contains(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);
            // 将创建人、创建时间等模拟成默认值
            defaultValue = DialectExtUtils.getInsertDefaultValue(createUnifyFields, dbType, fieldMeta);
            // 存在默认值
            if (isSupportNUL && null != defaultValue) {
                insertRejIdColValues.append(isNullFunction);
                insertRejIdColValues.append("(tv.").append(columnName).append(",");
                DialectExtUtils.processDefaultValue(insertRejIdColValues, dbType, fieldMeta.getType(), defaultValue);
                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]);
            columnName = ReservedWordsUtil.convertWord(columnName, dbType);
            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]);
            columnName = ReservedWordsUtil.convertWord(columnName, dbType);
            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 7 with FieldMeta

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

the class SqlServerDialectUtils method getSaveOrUpdateSql.

/**
 * @todo sqlserver 相对特殊不支持timestamp类型的插入，所以单独提供sql生成功能
 * @param unifyFieldsHandler
 * @param dbType
 * @param entityMeta
 * @param pkStrategy
 * @param forceUpdateFields
 * @param tableName
 * @param isNullFunction
 * @param sequence
 * @param isAssignPK
 * @return
 */
public static String getSaveOrUpdateSql(IUnifyFieldsHandler unifyFieldsHandler, Integer dbType, EntityMeta entityMeta, PKStrategy pkStrategy, String[] forceUpdateFields, String tableName, String isNullFunction, String sequence, boolean isAssignPK) {
    // 在无主键的情况下产生insert sql语句
    if (entityMeta.getIdArray() == null) {
        return generateInsertSql(dbType, entityMeta, tableName, pkStrategy, isNullFunction, sequence, isAssignPK);
    }
    // 将新增记录统一赋值属性模拟成默认值模式
    IgnoreKeyCaseMap<String, Object> createUnifyFields = null;
    if (unifyFieldsHandler != null && unifyFieldsHandler.createUnifyFields() != null && !unifyFieldsHandler.createUnifyFields().isEmpty()) {
        createUnifyFields = new IgnoreKeyCaseMap<String, Object>();
        createUnifyFields.putAll(unifyFieldsHandler.createUnifyFields());
    }
    String realTable = entityMeta.getSchemaTable(tableName, dbType);
    int columnSize = entityMeta.getFieldsArray().length;
    StringBuilder sql = new StringBuilder(columnSize * 30 + 100);
    String columnName;
    sql.append("merge into ");
    sql.append(realTable);
    sql.append(" ta ");
    sql.append(" using (select ");
    for (int i = 0; i < columnSize; i++) {
        columnName = entityMeta.getColumnName(entityMeta.getFieldsArray()[i]);
        columnName = ReservedWordsUtil.convertWord(columnName, dbType);
        if (i > 0) {
            sql.append(",");
        }
        sql.append("? as ");
        sql.append(columnName);
    }
    // sqlserver 不需要填具体表名,oracle 对应dual
    // sql.append(" from ").append(realTable);
    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]);
        columnName = ReservedWordsUtil.convertWord(columnName, dbType);
        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;
        // 需要被强制修改的字段
        HashSet<String> fupc = new HashSet<String>();
        if (forceUpdateFields != null) {
            for (String field : forceUpdateFields) {
                fupc.add(ReservedWordsUtil.convertWord(entityMeta.getColumnName(field), dbType));
            }
        }
        FieldMeta fieldMeta;
        // update 只针对非主键字段进行修改
        boolean isStart = true;
        int meter = 0;
        String defaultValue;
        for (int i = 0; i < rejectIdColumnSize; i++) {
            fieldMeta = entityMeta.getFieldMeta(entityMeta.getRejectIdFieldArray()[i]);
            // sqlserver不支持timestamp类型的数据进行插入赋值和变更
            if (fieldMeta.getType() != java.sql.Types.TIMESTAMP) {
                columnName = fieldMeta.getColumnName();
                columnName = ReservedWordsUtil.convertWord(columnName, dbType);
                if (meter > 0) {
                    sql.append(",");
                }
                sql.append(" ta.").append(columnName).append("=");
                // 强制修改
                if (fupc.contains(columnName)) {
                    sql.append("tv.").append(columnName);
                } else {
                    sql.append(isNullFunction);
                    sql.append("(tv.").append(columnName);
                    sql.append(",ta.").append(columnName);
                    sql.append(")");
                }
                if (!isStart) {
                    insertRejIdCols.append(",");
                    insertRejIdColValues.append(",");
                }
                insertRejIdCols.append(columnName);
                isStart = false;
                // 将创建人、创建时间等模拟成默认值
                defaultValue = DialectExtUtils.getInsertDefaultValue(createUnifyFields, dbType, fieldMeta);
                // 存在默认值
                if (null != defaultValue) {
                    insertRejIdColValues.append(isNullFunction);
                    insertRejIdColValues.append("(tv.").append(columnName).append(",");
                    DialectExtUtils.processDefaultValue(insertRejIdColValues, dbType, fieldMeta.getType(), defaultValue);
                    insertRejIdColValues.append(")");
                } else {
                    insertRejIdColValues.append("tv.").append(columnName);
                }
                meter++;
            }
        }
    }
    // 主键未匹配上则进行插入操作
    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]);
            columnName = ReservedWordsUtil.convertWord(columnName, dbType);
            sql.append(",");
            sql.append(columnName);
            sql.append(") values (");
            sql.append(insertRejIdColValues).append(",");
            if (isAssignPK) {
                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]);
            columnName = ReservedWordsUtil.convertWord(columnName, dbType);
            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 8 with FieldMeta

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

the class SqlServerDialectUtils method getSaveIgnoreExistSql.

/**
 * @todo sqlserver 相对特殊不支持timestamp类型的插入，所以单独提供sql生成功能
 * @param dbType
 * @param entityMeta
 * @param pkStrategy
 * @param tableName
 * @param isNullFunction
 * @param sequence
 * @param isAssignPK
 * @return
 */
public static String getSaveIgnoreExistSql(Integer dbType, EntityMeta entityMeta, PKStrategy pkStrategy, String tableName, String isNullFunction, String sequence, boolean isAssignPK) {
    // 在无主键的情况下产生insert sql语句
    if (entityMeta.getIdArray() == null) {
        return generateInsertSql(dbType, entityMeta, tableName, pkStrategy, isNullFunction, sequence, isAssignPK);
    }
    int columnSize = entityMeta.getFieldsArray().length;
    StringBuilder sql = new StringBuilder(columnSize * 30 + 100);
    String realTable = entityMeta.getSchemaTable(tableName, dbType);
    String columnName;
    sql.append("merge into ");
    sql.append(realTable);
    sql.append(" ta ");
    sql.append(" using (select ");
    for (int i = 0; i < columnSize; i++) {
        columnName = entityMeta.getColumnName(entityMeta.getFieldsArray()[i]);
        columnName = ReservedWordsUtil.convertWord(columnName, dbType);
        if (i > 0) {
            sql.append(",");
        }
        sql.append("? as ");
        sql.append(columnName);
    }
    // sql.append(" from ").append(realTable);
    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]);
        columnName = ReservedWordsUtil.convertWord(columnName, dbType);
        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) {
        int rejectIdColumnSize = entityMeta.getRejectIdFieldArray().length;
        FieldMeta fieldMeta;
        // update 只针对非主键字段进行修改
        boolean isStart = true;
        for (int i = 0; i < rejectIdColumnSize; i++) {
            fieldMeta = entityMeta.getFieldMeta(entityMeta.getRejectIdFieldArray()[i]);
            columnName = ReservedWordsUtil.convertWord(fieldMeta.getColumnName(), dbType);
            // sqlserver不支持timestamp类型的数据进行插入赋值
            if (fieldMeta.getType() != java.sql.Types.TIMESTAMP) {
                if (!isStart) {
                    insertRejIdCols.append(",");
                    insertRejIdColValues.append(",");
                }
                insertRejIdCols.append(columnName);
                isStart = false;
                // 存在默认值
                if (null != fieldMeta.getDefaultValue()) {
                    insertRejIdColValues.append(isNullFunction);
                    insertRejIdColValues.append("(tv.").append(columnName).append(",");
                    DialectExtUtils.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]);
            columnName = ReservedWordsUtil.convertWord(columnName, dbType);
            sql.append(",");
            sql.append(columnName);
            sql.append(") values (");
            sql.append(insertRejIdColValues).append(",");
            if (isAssignPK) {
                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]);
            columnName = ReservedWordsUtil.convertWord(columnName, dbType);
            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 9 with FieldMeta

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

the class DB2DialectUtils method getSaveOrUpdateSql.

/**
 * @todo 处理加工对象基于db2 的merge into 语句
 * @param unifyFieldsHandler
 * @param dbType
 * @param entityMeta
 * @param pkStrategy
 * @param forceUpdateFields
 * @param fromTable
 * @param isNullFunction
 * @param sequence
 * @param isAssignPK
 * @param tableName
 * @return
 */
public static String getSaveOrUpdateSql(IUnifyFieldsHandler unifyFieldsHandler, Integer dbType, EntityMeta entityMeta, PKStrategy pkStrategy, String[] forceUpdateFields, String fromTable, String isNullFunction, String sequence, boolean isAssignPK, String tableName) {
    String realTable = entityMeta.getSchemaTable(tableName, dbType);
    // 在无主键的情况下产生insert sql语句
    if (entityMeta.getIdArray() == null) {
        return DialectExtUtils.generateInsertSql(dbType, entityMeta, pkStrategy, isNullFunction, sequence, isAssignPK, realTable);
    }
    // 将新增记录统一赋值属性模拟成默认值模式
    IgnoreKeyCaseMap<String, Object> createUnifyFields = null;
    if (unifyFieldsHandler != null && unifyFieldsHandler.createUnifyFields() != null && !unifyFieldsHandler.createUnifyFields().isEmpty()) {
        createUnifyFields = new IgnoreKeyCaseMap<String, Object>();
        createUnifyFields.putAll(unifyFieldsHandler.createUnifyFields());
    }
    int columnSize = entityMeta.getFieldsArray().length;
    FieldMeta fieldMeta;
    StringBuilder sql = new StringBuilder(columnSize * 30 + 100);
    String columnName;
    sql.append("merge into ");
    sql.append(realTable);
    sql.append(" ta ");
    sql.append(" using (select ");
    for (int i = 0; i < columnSize; i++) {
        fieldMeta = entityMeta.getFieldMeta(entityMeta.getFieldsArray()[i]);
        // 处理保留字
        columnName = ReservedWordsUtil.convertWord(fieldMeta.getColumnName(), dbType);
        // 这里是db2跟oracle、sqlserver不同的地方
        wrapSelectFields(sql, i, columnName, fieldMeta.getType(), fieldMeta.getLength());
    }
    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]);
        // 处理保留字
        columnName = ReservedWordsUtil.convertWord(columnName, dbType);
        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;
        // 需要被强制修改的字段
        HashSet<String> fupc = new HashSet<String>();
        if (forceUpdateFields != null) {
            for (String field : forceUpdateFields) {
                // 增加处理保留字
                fupc.add(ReservedWordsUtil.convertWord(entityMeta.getColumnName(field), dbType));
            }
        }
        String defaultValue;
        // update 只针对非主键字段进行修改
        for (int i = 0; i < rejectIdColumnSize; i++) {
            fieldMeta = entityMeta.getFieldMeta(entityMeta.getRejectIdFieldArray()[i]);
            // 增加处理保留字
            columnName = ReservedWordsUtil.convertWord(fieldMeta.getColumnName(), dbType);
            if (i > 0) {
                sql.append(",");
                insertRejIdCols.append(",");
                insertRejIdColValues.append(",");
            }
            sql.append(" ta.").append(columnName).append("=");
            // 强制修改
            if (fupc.contains(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);
            // 将创建人、创建时间等模拟成默认值
            defaultValue = DialectExtUtils.getInsertDefaultValue(createUnifyFields, dbType, fieldMeta);
            // 存在默认值
            if (null != defaultValue) {
                insertRejIdColValues.append(isNullFunction);
                insertRejIdColValues.append("(tv.").append(columnName).append(",");
                DialectExtUtils.processDefaultValue(insertRejIdColValues, dbType, fieldMeta.getType(), defaultValue);
                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]);
            // 增加处理保留字
            columnName = ReservedWordsUtil.convertWord(columnName, dbType);
            sql.append(",");
            sql.append(columnName);
            sql.append(") values (");
            sql.append(insertRejIdColValues).append(",");
            if (isAssignPK) {
                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]);
            // 增加处理保留字
            columnName = ReservedWordsUtil.convertWord(columnName, dbType);
            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 10 with FieldMeta

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

the class DefaultDialectUtils method updateSaveFetch.

/**
 * @TODO 实现：1、锁查询；2、记录存在则修改；3、记录不存在则执行insert；4、返回修改或插入的记录信息，尽量不要使用identity、sequence主键
 * @param sqlToyContext
 * @param entity
 * @param updateRowHandler
 * @param uniqueProps
 * @param conn
 * @param dbType
 * @param dialect
 * @param tableName
 * @return
 * @throws Exception
 */
public static Serializable updateSaveFetch(final SqlToyContext sqlToyContext, final Serializable entity, final UpdateRowHandler updateRowHandler, String[] uniqueProps, final Connection conn, final Integer dbType, String dialect, String tableName) throws Exception {
    final EntityMeta entityMeta = sqlToyContext.getEntityMeta(entity.getClass());
    // 条件字段
    String[] whereFields = uniqueProps;
    if (whereFields == null || whereFields.length == 0) {
        whereFields = entityMeta.getIdArray();
    }
    if (whereFields == null || whereFields.length == 0) {
        throw new DataAccessException("updateSaveFetch操作的表:" + tableName + " 没有唯一获得一条记录的条件字段,请检查!");
    }
    // 全部字段的值
    Object[] tempFieldValues = null;
    // 条件字段值
    Object[] whereParamValues = BeanUtil.reflectBeanToAry(entity, whereFields);
    for (int i = 0; i < whereParamValues.length; i++) {
        // 唯一性属性值存在空，则表示首次插入
        if (StringUtil.isBlank(whereParamValues[i])) {
            tempFieldValues = processFieldValues(sqlToyContext, entityMeta, entity);
            whereParamValues = BeanUtil.reflectBeanToAry(entity, whereFields);
            break;
        }
    }
    final Object[] fieldValues = tempFieldValues;
    final boolean hasUpdateRow = (updateRowHandler == null) ? false : true;
    // 组织select * from table for update 语句
    String sql = wrapFetchSql(entityMeta, dbType, whereFields, tableName);
    SqlExecuteStat.showSql("执行锁记录查询", sql, whereParamValues);
    // 可编辑结果集
    PreparedStatement pst = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
    List updateResult = (List) SqlUtil.preparedStatementProcess(whereParamValues, pst, null, new PreparedStatementResultHandler() {

        @Override
        public void execute(Object rowData, PreparedStatement pst, ResultSet rs) throws Exception {
            SqlUtil.setParamsValue(sqlToyContext.getTypeHandler(), conn, dbType, pst, (Object[]) rowData, null, 0);
            // 执行类似 select xxx from table for update(sqlserver语法有差异)
            rs = pst.executeQuery();
            int rowCnt = rs.getMetaData().getColumnCount();
            int index = 0;
            List result = new ArrayList();
            while (rs.next()) {
                if (index > 0) {
                    throw new DataAccessException("updateSaveFetch操作只能针对单条记录进行操作,请检查uniqueProps参数设置!");
                }
                // 存在修改记录
                if (hasUpdateRow) {
                    SqlExecuteStat.debug("执行updateRow", "记录存在调用updateRowHandler.updateRow!");
                    // 执行update反调，实现锁定行记录值的修改
                    updateRowHandler.updateRow(rs, index);
                    // 执行update
                    rs.updateRow();
                }
                index++;
                // 重新获得修改后的值
                result.add(ResultUtils.processResultRow(rs, 0, rowCnt, false));
            }
            // 没有查询到记录，表示是需要首次插入
            if (index == 0) {
                SqlExecuteStat.debug("执行insertRow", "查询未匹配到结果则进行首次插入!");
                // 移到插入行
                rs.moveToInsertRow();
                FieldMeta fieldMeta;
                Object[] fullFieldvalues = (fieldValues == null) ? processFieldValues(sqlToyContext, entityMeta, entity) : fieldValues;
                Object fieldValue;
                for (int i = 0; i < entityMeta.getFieldsArray().length; i++) {
                    fieldMeta = entityMeta.getFieldMeta(entityMeta.getFieldsArray()[i]);
                    if (fieldMeta.isPK()) {
                        fieldValue = fullFieldvalues[i];
                    } else {
                        fieldValue = SqlUtilsExt.getDefaultValue(fullFieldvalues[i], fieldMeta.getDefaultValue(), fieldMeta.getType(), fieldMeta.isNullable());
                    }
                    // 插入设置具体列的值
                    if (fieldValue != null) {
                        rs.updateObject(fieldMeta.getColumnName(), fieldValue);
                    }
                }
                // 执行插入
                rs.insertRow();
            }
            this.setResult(result);
        }
    });
    // 记录不存在首次保存，返回entity自身
    if (updateResult == null || updateResult.isEmpty()) {
        return entity;
    } else {
        List entities = BeanUtil.reflectListToBean(sqlToyContext.getTypeHandler(), updateResult, entityMeta.getFieldsArray(), entity.getClass());
        return (Serializable) entities.get(0);
    }
}