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

Example 26 with FieldMeta

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

the class SqlUtilTest method testConvertFieldsToCols.

@Test
public void testConvertFieldsToCols() {
    String sql = "staffName,`sexType`,name,bizStaffName from table where #[t.staffName like ?] and sexType=:sexType";
    EntityMeta entityMeta = new EntityMeta();
    entityMeta.setTableName("staff_info");
    HashMap<String, FieldMeta> fieldsMeta = new HashMap<String, FieldMeta>();
    FieldMeta staffMeta = new FieldMeta();
    staffMeta.setFieldName("staffName");
    staffMeta.setColumnName("STAFF_NAME");
    fieldsMeta.put("staffname", staffMeta);
    FieldMeta bizStaffMeta = new FieldMeta();
    bizStaffMeta.setFieldName("bizStaffName");
    bizStaffMeta.setColumnName("BIZ_STAFF_NAME");
    fieldsMeta.put("bizstaffname", bizStaffMeta);
    FieldMeta nameMeta = new FieldMeta();
    nameMeta.setFieldName("name");
    nameMeta.setColumnName("NAME");
    fieldsMeta.put("name", nameMeta);
    FieldMeta sexMeta = new FieldMeta();
    sexMeta.setFieldName("sexType");
    sexMeta.setColumnName("SEX_TYPE");
    fieldsMeta.put("sextype", sexMeta);
    entityMeta.setFieldsMeta(fieldsMeta);
    entityMeta.setFieldsArray(new String[] { "name", "staffName", "bizStaffName", "sexType" });
    sql = SqlUtil.convertFieldsToColumns(entityMeta, sql);
    assertEquals(sql.trim(), "STAFF_NAME,`SEX_TYPE`,name,BIZ_STAFF_NAME from table where #[t.STAFF_NAME like ?] and SEX_TYPE=:sexType");
}

Example 27 with FieldMeta

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

the class SqlUtilTest method testConvertFieldsToCols1.

/**
 * @TODO 测试vo属性名称转表字段名称
 */
@Test
public void testConvertFieldsToCols1() {
    String sql = "sexType=:sexType";
    EntityMeta entityMeta = new EntityMeta();
    entityMeta.setTableName("staff_info");
    HashMap<String, FieldMeta> fieldsMeta = new HashMap<String, FieldMeta>();
    FieldMeta sexMeta = new FieldMeta();
    sexMeta.setFieldName("sexType");
    sexMeta.setColumnName("SEX_TYPE");
    fieldsMeta.put("sextype", sexMeta);
    entityMeta.setFieldsMeta(fieldsMeta);
    entityMeta.setFieldsArray(new String[] { "sexType" });
    sql = SqlUtil.convertFieldsToColumns(entityMeta, sql);
    System.err.println(sql);
}

Example 28 with FieldMeta

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

the class PostgreSqlDialectUtils method getSaveOrUpdateSql.

/**
 * @todo postgresql9.5以及以上版本的saveOrUpdate语句，实际不会使用(用update和saveIgnore组合替代)，因为postgresql
 *       此功能存在bug
 * @param dbType
 * @param entityMeta
 * @param pkStrategy
 * @param sequence
 * @param forceUpdateFields
 * @param tableName
 * @return
 */
@Deprecated
public static String getSaveOrUpdateSql(Integer dbType, EntityMeta entityMeta, PKStrategy pkStrategy, boolean isAssignPK, String sequence, String[] forceUpdateFields, String tableName) {
    String realTable = entityMeta.getSchemaTable(tableName, dbType);
    if (entityMeta.getIdArray() == null) {
        return DialectExtUtils.generateInsertSql(dbType, entityMeta, entityMeta.getIdStrategy(), NVL_FUNCTION, null, false, realTable);
    }
    // 是否全部是ID
    boolean allIds = (entityMeta.getRejectIdFieldArray() == null);
    // 全部是主键采用replace into 策略进行保存或修改,不考虑只有一个字段且是主键的表情况
    StringBuilder sql = new StringBuilder("insert into ");
    StringBuilder values = new StringBuilder();
    sql.append(realTable);
    sql.append(" AS t1 (");
    FieldMeta fieldMeta;
    String fieldName;
    boolean isStart = true;
    String columnName;
    for (int i = 0, n = entityMeta.getFieldsArray().length; i < n; i++) {
        fieldName = entityMeta.getFieldsArray()[i];
        fieldMeta = entityMeta.getFieldMeta(fieldName);
        columnName = ReservedWordsUtil.convertWord(fieldMeta.getColumnName(), dbType);
        // sql中的关键字处理
        if (!isStart) {
            sql.append(",");
            values.append(",");
        }
        if (fieldMeta.isPK()) {
            // identity主键策略，且支持主键手工赋值
            if (pkStrategy.equals(PKStrategy.IDENTITY)) {
                // 目前只有mysql支持
                if (isAssignPK) {
                    sql.append(columnName);
                    values.append("?");
                    isStart = false;
                }
            } else // sequence 策略，oracle12c之后的identity机制统一转化为sequence模式
            if (pkStrategy.equals(PKStrategy.SEQUENCE)) {
                sql.append(columnName);
                if (isAssignPK) {
                    values.append(NVL_FUNCTION);
                    values.append("(?,").append(sequence).append(")");
                } else {
                    values.append(sequence);
                }
                isStart = false;
            } else {
                sql.append(columnName);
                values.append("?");
                isStart = false;
            }
        } else {
            sql.append(columnName);
            if (null != fieldMeta.getDefaultValue()) {
                values.append(NVL_FUNCTION);
                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(") ");
    // 非全部是主键
    if (!allIds) {
        // String columnName;
        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 UPDATE SET ");
        // 需要被强制修改的字段
        HashSet<String> fupc = new HashSet<String>();
        if (forceUpdateFields != null) {
            for (String field : forceUpdateFields) {
                fupc.add(ReservedWordsUtil.convertWord(entityMeta.getColumnName(field), dbType));
            }
        }
        for (int i = 0, n = entityMeta.getRejectIdFieldArray().length; i < n; i++) {
            columnName = entityMeta.getColumnName(entityMeta.getRejectIdFieldArray()[i]);
            columnName = ReservedWordsUtil.convertWord(columnName, dbType);
            if (i > 0) {
                sql.append(",");
            }
            sql.append(columnName).append("=");
            // 强制修改
            if (fupc.contains(columnName)) {
                sql.append("excluded.").append(columnName);
            } else {
                sql.append("COALESCE(excluded.");
                sql.append(columnName).append(",t1.");
                sql.append(columnName).append(")");
            }
        }
    }
    return sql.toString();
}

Example 29 with FieldMeta

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

the class SqliteDialectUtils method getSaveOrUpdateSql.

/**
 * @todo 利用sqlite3 的on conflict(id) DO UPDATE SET 语法,但只能用于关联子表更新
 * @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);
    // 无主键表全部采用insert机制
    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 (").append(values).append(") ");
    // 非全部是主键
    if (!allIds) {
        String columnName;
        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 UPDATE SET ");
        // 需要被强制修改的字段
        HashSet<String> fupc = new HashSet<String>();
        if (forceUpdateFields != null) {
            for (String field : forceUpdateFields) {
                fupc.add(ReservedWordsUtil.convertWord(entityMeta.getColumnName(field), dbType));
            }
        }
        for (int i = 0, n = entityMeta.getRejectIdFieldArray().length; i < n; i++) {
            columnName = entityMeta.getColumnName(entityMeta.getRejectIdFieldArray()[i]);
            columnName = ReservedWordsUtil.convertWord(columnName, dbType);
            if (i > 0) {
                sql.append(",");
            }
            sql.append(columnName).append("=");
            // 强制修改
            if (fupc.contains(columnName)) {
                sql.append("excluded.").append(columnName);
            } else {
                sql.append("ifnull(excluded.");
                sql.append(columnName).append(",");
                sql.append(columnName).append(")");
            }
        }
    }
    return sql.toString();
}