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

Example 21 with FieldMeta

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

the class DialectExtUtils method generateInsertSql.

/**
 * @todo 产生对象对应的insert sql语句
 * @param dbType
 * @param entityMeta
 * @param pkStrategy
 * @param isNullFunction
 * @param sequence
 * @param isAssignPK
 * @param tableName
 * @return
 */
public static String generateInsertSql(Integer dbType, EntityMeta entityMeta, PKStrategy pkStrategy, String isNullFunction, String sequence, boolean isAssignPK, String tableName) {
    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;
    boolean isSupportNULL = StringUtil.isBlank(isNullFunction) ? false : true;
    String columnName;
    boolean isString = false;
    for (int i = 0; i < columnSize; i++) {
        field = entityMeta.getFieldsArray()[i];
        fieldMeta = entityMeta.getFieldMeta(field);
        isString = false;
        if (fieldMeta.getFieldType().equals("java.lang.string")) {
            isString = true;
        }
        columnName = ReservedWordsUtil.convertWord(fieldMeta.getColumnName(), dbType);
        if (fieldMeta.isPK()) {
            // identity主键策略，且支持主键手工赋值
            if (pkStrategy.equals(PKStrategy.IDENTITY)) {
                // 目前只有mysql支持
                if (isAssignPK) {
                    if (!isStart) {
                        sql.append(",");
                        values.append(",");
                    }
                    sql.append(columnName);
                    values.append("?");
                    isStart = false;
                }
            } else // sequence 策略，oracle12c之后的identity机制统一转化为sequence模式
            if (pkStrategy.equals(PKStrategy.SEQUENCE)) {
                if (!isStart) {
                    sql.append(",");
                    values.append(",");
                }
                sql.append(columnName);
                if (isAssignPK && isSupportNULL) {
                    values.append(isNullFunction);
                    values.append("(?,").append(sequence).append(")");
                } else {
                    values.append(sequence);
                }
                isStart = false;
            } else {
                if (!isStart) {
                    sql.append(",");
                    values.append(",");
                }
                sql.append(columnName);
                values.append("?");
                isStart = false;
            }
        } else {
            if (!isStart) {
                sql.append(",");
                values.append(",");
            }
            sql.append(columnName);
            // 默认值处理
            if (isSupportNULL && null != fieldMeta.getDefaultValue()) {
                values.append(isNullFunction);
                values.append("(?,");
                processDefaultValue(values, dbType, fieldMeta.getType(), fieldMeta.getDefaultValue());
                values.append(")");
            } else {
                // kudu 中文会产生乱码
                if (dbType == DBType.IMPALA && isString) {
                    values.append("cast(? as string)");
                } else {
                    values.append("?");
                }
            }
            isStart = false;
        }
    }
    // OVERRIDING SYSTEM VALUE
    sql.append(") ");
    // if ((dbType == DBType.POSTGRESQL || dbType == DBType.GAUSSDB) && isAssignPK)
    // { sql.append(" OVERRIDING SYSTEM VALUE "); }
    sql.append(" values (");
    sql.append(values);
    sql.append(")");
    return sql.toString();
}

Example 22 with FieldMeta

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

the class DialectExtUtils method insertIgnore.

/**
 * @TODO 针对postgresql\sqlite\guassdb等数据库
 * @param dbType
 * @param entityMeta
 * @param pkStrategy
 * @param isNullFunction
 * @param sequence
 * @param isAssignPK
 * @param tableName
 * @return
 */
public static String insertIgnore(Integer dbType, EntityMeta entityMeta, PKStrategy pkStrategy, String isNullFunction, String sequence, boolean isAssignPK, String tableName) {
    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;
    String columnName;
    boolean isStart = true;
    for (int i = 0; i < columnSize; i++) {
        field = entityMeta.getFieldsArray()[i];
        fieldMeta = entityMeta.getFieldMeta(field);
        columnName = ReservedWordsUtil.convertWord(fieldMeta.getColumnName(), dbType);
        if (!isStart) {
            sql.append(",");
            values.append(",");
        }
        if (fieldMeta.isPK()) {
            // identity主键策略，且支持主键手工赋值
            if (pkStrategy.equals(PKStrategy.IDENTITY)) {
                if (isAssignPK) {
                    sql.append(columnName);
                    values.append("?");
                    isStart = false;
                }
            } else if (pkStrategy.equals(PKStrategy.SEQUENCE)) {
                sql.append(columnName);
                values.append(isNullFunction).append("(?,").append(sequence).append(")");
                isStart = false;
            } else {
                sql.append(columnName);
                values.append("?");
                isStart = false;
            }
        } else {
            sql.append(columnName);
            if (null != fieldMeta.getDefaultValue()) {
                values.append(isNullFunction).append("(?,");
                processDefaultValue(values, dbType, fieldMeta.getType(), fieldMeta.getDefaultValue());
                values.append(")");
            } else {
                values.append("?");
            }
            isStart = false;
        }
    }
    sql.append(") values ( ");
    sql.append(values);
    sql.append(")");
    // 增加do noting
    if (entityMeta.getIdArray() != null) {
        sql.append(" ON CONFLICT (");
        for (int i = 0, n = entityMeta.getIdArray().length; i < n; i++) {
            if (i > 0) {
                sql.append(",");
            }
            columnName = entityMeta.getColumnName(entityMeta.getIdArray()[i]);
            sql.append(ReservedWordsUtil.convertWord(columnName, dbType));
        }
        sql.append(" ) DO NOTHING ");
    }
    return sql.toString();
}

Example 23 with FieldMeta

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

the class DialectExtUtils method mergeIgnore.

/**
 * @todo 处理加工对象基于db2、oracle、informix、sybase数据库的saveIgnoreExist
 * @param dbType
 * @param entityMeta
 * @param pkStrategy
 * @param fromTable
 * @param isNullFunction
 * @param sequence
 * @param isAssignPK
 * @param tableName
 * @return
 */
public static String mergeIgnore(Integer dbType, EntityMeta entityMeta, PKStrategy pkStrategy, String fromTable, String isNullFunction, String sequence, boolean isAssignPK, String tableName) {
    // 在无主键的情况下产生insert sql语句
    String realTable = entityMeta.getSchemaTable(tableName, dbType);
    if (entityMeta.getIdArray() == null) {
        return DialectExtUtils.generateInsertSql(dbType, entityMeta, pkStrategy, isNullFunction, sequence, isAssignPK, realTable);
    }
    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) {
        int rejectIdColumnSize = entityMeta.getRejectIdFieldArray().length;
        FieldMeta fieldMeta;
        // update 只针对非主键字段进行修改
        for (int i = 0; i < rejectIdColumnSize; i++) {
            fieldMeta = entityMeta.getFieldMeta(entityMeta.getRejectIdFieldArray()[i]);
            columnName = ReservedWordsUtil.convertWord(fieldMeta.getColumnName(), dbType);
            if (i > 0) {
                insertRejIdCols.append(",");
                insertRejIdColValues.append(",");
            }
            insertRejIdCols.append(columnName);
            // 存在默认值
            if (isSupportNUL && null != 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]);
            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 24 with FieldMeta

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

the class DB2DialectUtils method getSaveIgnoreExistSql.

/**
 * @todo 处理加工对象基于db2 merge into (only insert)
 * @param dbType
 * @param entityMeta
 * @param pkStrategy
 * @param fromTable
 * @param isNullFunction
 * @param sequence
 * @param isAssignPK
 * @param tableName
 * @return
 */
public static String getSaveIgnoreExistSql(Integer dbType, EntityMeta entityMeta, PKStrategy pkStrategy, 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);
    }
    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) {
        int rejectIdColumnSize = entityMeta.getRejectIdFieldArray().length;
        // update 只针对非主键字段进行修改
        for (int i = 0; i < rejectIdColumnSize; i++) {
            fieldMeta = entityMeta.getFieldMeta(entityMeta.getRejectIdFieldArray()[i]);
            // 增加处理保留字
            columnName = ReservedWordsUtil.convertWord(fieldMeta.getColumnName(), dbType);
            if (i > 0) {
                insertRejIdCols.append(",");
                insertRejIdColValues.append(",");
            }
            insertRejIdCols.append(columnName);
            // 存在默认值
            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 25 with FieldMeta

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

the class SqlUtilTest method testConvertFieldsToCols2.

@Test
public void testConvertFieldsToCols2() {
    String sql = " detail_id = :detailId and res_type = : resType";
    EntityMeta entityMeta = new EntityMeta();
    entityMeta.setTableName("staff_info");
    HashMap<String, FieldMeta> fieldsMeta = new HashMap<String, FieldMeta>();
    FieldMeta sexMeta = new FieldMeta();
    sexMeta.setFieldName("detailId");
    sexMeta.setColumnName("detail_id");
    fieldsMeta.put("detailid", sexMeta);
    FieldMeta resType = new FieldMeta();
    resType.setFieldName("resType");
    resType.setColumnName("res_type");
    fieldsMeta.put("restype", resType);
    entityMeta.setFieldsMeta(fieldsMeta);
    entityMeta.setFieldsArray(new String[] { "detailId", "resType" });
    String[] paramNames = SqlConfigParseUtils.getSqlParamsName(sql, false);
    sql = SqlUtil.convertFieldsToColumns(entityMeta, sql);
    System.err.println(JSON.toJSONString(paramNames));
    System.err.println(sql);
}