Examples with PreparedStatementResultHandler org.sagacity.sqltoy.callback.PreparedStatementResultHandler used on opensource projects

Example 11 with PreparedStatementResultHandler

use of org.sagacity.sqltoy.callback.PreparedStatementResultHandler 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);
    }
}

Example 12 with PreparedStatementResultHandler

use of org.sagacity.sqltoy.callback.PreparedStatementResultHandler in project sagacity-sqltoy by chenrenfei.

the class ClickHouseDialectUtils method getTableColumns.

@SuppressWarnings("unchecked")
public static List<ColumnMeta> getTableColumns(String catalog, String schema, String tableName, Connection conn, Integer dbType, String dialect) throws Exception {
    List<ColumnMeta> tableColumns = DefaultDialectUtils.getTableColumns(catalog, schema, tableName, conn, dbType, dialect);
    String sql = "SELECT name COLUMN_NAME,comment COMMENTS,is_in_primary_key PRIMARY_KEY,is_in_partition_key PARTITION_KEY from system.columns t where t.table=?";
    PreparedStatement pst = conn.prepareStatement(sql);
    ResultSet rs = null;
    // 通过preparedStatementProcess反调，第二个参数是pst
    Map<String, ColumnMeta> colMap = (Map<String, ColumnMeta>) SqlUtil.preparedStatementProcess(null, pst, rs, new PreparedStatementResultHandler() {

        @Override
        public void execute(Object rowData, PreparedStatement pst, ResultSet rs) throws Exception {
            pst.setString(1, tableName);
            rs = pst.executeQuery();
            Map<String, ColumnMeta> colComments = new HashMap<String, ColumnMeta>();
            while (rs.next()) {
                ColumnMeta colMeta = new ColumnMeta();
                colMeta.setColName(rs.getString("COLUMN_NAME"));
                colMeta.setComments(rs.getString("COMMENTS"));
                colMeta.setPK(rs.getString("PRIMARY_KEY").equals("1") ? true : false);
                colMeta.setPartitionKey(rs.getString("PARTITION_KEY").equals("1") ? true : false);
                colComments.put(colMeta.getColName(), colMeta);
            }
            this.setResult(colComments);
        }
    });
    ColumnMeta mapColMeta;
    for (ColumnMeta col : tableColumns) {
        mapColMeta = colMap.get(col.getColName());
        if (mapColMeta != null) {
            col.setComments(mapColMeta.getComments());
            col.setPK(mapColMeta.isPK());
            col.setPartitionKey(mapColMeta.isPartitionKey());
        }
    }
    return tableColumns;
}

Example 13 with PreparedStatementResultHandler

use of org.sagacity.sqltoy.callback.PreparedStatementResultHandler in project sagacity-sqltoy by chenrenfei.

the class ClickHouseDialectUtils method save.

/**
 * @todo 保存对象
 * @param sqlToyContext
 * @param entityMeta
 * @param insertSql
 * @param entity
 * @param conn
 * @param dbType
 * @return
 * @throws Exception
 */
public static Object save(SqlToyContext sqlToyContext, final EntityMeta entityMeta, final String insertSql, Serializable entity, final Connection conn, final Integer dbType) throws Exception {
    PKStrategy pkStrategy = entityMeta.getIdStrategy();
    ReturnPkType returnPkType = (pkStrategy != null && pkStrategy.equals(PKStrategy.SEQUENCE)) ? ReturnPkType.GENERATED_KEYS : ReturnPkType.PREPARD_ID;
    final boolean isIdentity = (pkStrategy != null && pkStrategy.equals(PKStrategy.IDENTITY));
    final boolean isSequence = (pkStrategy != null && pkStrategy.equals(PKStrategy.SEQUENCE));
    String[] reflectColumns;
    boolean isAssignPK = isAssignPKValue(pkStrategy);
    if ((isIdentity && !isAssignPK) || (isSequence && !isAssignPK)) {
        reflectColumns = entityMeta.getRejectIdFieldArray();
    } else {
        reflectColumns = entityMeta.getFieldsArray();
    }
    // 构造全新的新增记录参数赋值反射(覆盖之前的)
    ReflectPropsHandler handler = DialectUtils.getAddReflectHandler(null, sqlToyContext.getUnifyFieldsHandler());
    handler = DialectUtils.getSecureReflectHandler(handler, sqlToyContext.getFieldsSecureProvider(), sqlToyContext.getDesensitizeProvider(), entityMeta.getSecureFields());
    Object[] fullParamValues = BeanUtil.reflectBeanToAry(entity, reflectColumns, null, handler);
    boolean needUpdatePk = false;
    int pkIndex = entityMeta.getIdIndex();
    // 是否存在业务ID
    boolean hasBizId = (entityMeta.getBusinessIdGenerator() == null) ? false : true;
    int bizIdColIndex = hasBizId ? entityMeta.getFieldIndex(entityMeta.getBusinessIdField()) : 0;
    // 标识符
    String signature = entityMeta.getBizIdSignature();
    Integer[] relatedColumn = entityMeta.getBizIdRelatedColIndex();
    String[] relatedColumnNames = entityMeta.getBizIdRelatedColumns();
    int relatedColumnSize = (relatedColumn == null) ? 0 : relatedColumn.length;
    // 主键采用assign方式赋予，则调用generator产生id并赋予其值
    if (entityMeta.getIdStrategy() != null && null != entityMeta.getIdGenerator()) {
        int bizIdLength = entityMeta.getBizIdLength();
        int idLength = entityMeta.getIdLength();
        Object[] relatedColValue = null;
        String businessIdType = hasBizId ? entityMeta.getColumnJavaType(entityMeta.getBusinessIdField()) : "";
        if (relatedColumn != null) {
            relatedColValue = new Object[relatedColumnSize];
            for (int meter = 0; meter < relatedColumnSize; meter++) {
                relatedColValue[meter] = fullParamValues[relatedColumn[meter]];
                if (StringUtil.isBlank(relatedColValue[meter])) {
                    throw new IllegalArgumentException("对象:" + entityMeta.getEntityClass().getName() + " 生成业务主键依赖的关联字段:" + relatedColumnNames[meter] + " 值为null!");
                }
            }
        }
        if (StringUtil.isBlank(fullParamValues[pkIndex])) {
            // id通过generator机制产生，设置generator产生的值
            fullParamValues[pkIndex] = entityMeta.getIdGenerator().getId(entityMeta.getTableName(), signature, entityMeta.getBizIdRelatedColumns(), relatedColValue, null, entityMeta.getIdType(), idLength, entityMeta.getBizIdSequenceSize());
            needUpdatePk = true;
        }
        if (hasBizId && StringUtil.isBlank(fullParamValues[bizIdColIndex])) {
            fullParamValues[bizIdColIndex] = entityMeta.getBusinessIdGenerator().getId(entityMeta.getTableName(), signature, entityMeta.getBizIdRelatedColumns(), relatedColValue, null, businessIdType, bizIdLength, entityMeta.getBizIdSequenceSize());
            // 回写业务主键值
            BeanUtil.setProperty(entity, entityMeta.getBusinessIdField(), fullParamValues[bizIdColIndex]);
        }
    }
    SqlExecuteStat.showSql("执行单记录插入", insertSql, null);
    final Object[] paramValues = fullParamValues;
    final Integer[] paramsType = entityMeta.getFieldsTypeArray();
    PreparedStatement pst = null;
    Object result = SqlUtil.preparedStatementProcess(null, pst, null, new PreparedStatementResultHandler() {

        @Override
        public void execute(Object obj, PreparedStatement pst, ResultSet rs) throws SQLException, IOException {
            if (isIdentity || isSequence) {
                if (returnPkType.equals(ReturnPkType.GENERATED_KEYS)) {
                    pst = conn.prepareStatement(insertSql, PreparedStatement.RETURN_GENERATED_KEYS);
                } else if (returnPkType.equals(ReturnPkType.PREPARD_ID)) {
                    pst = conn.prepareStatement(insertSql, new String[] { entityMeta.getColumnName(entityMeta.getIdArray()[0]) });
                } else {
                    pst = conn.prepareStatement(insertSql);
                }
            } else {
                pst = conn.prepareStatement(insertSql);
            }
            SqlUtil.setParamsValue(sqlToyContext.getTypeHandler(), conn, dbType, pst, paramValues, paramsType, 0);
            ResultSet keyResult = null;
            if ((isIdentity || isSequence) && returnPkType.equals(ReturnPkType.RESULT_GET)) {
                keyResult = pst.executeQuery();
            } else {
                pst.execute();
            }
            if (isIdentity || isSequence) {
                if (!returnPkType.equals(ReturnPkType.RESULT_GET)) {
                    keyResult = pst.getGeneratedKeys();
                }
                if (keyResult != null) {
                    List result = new ArrayList();
                    while (keyResult.next()) {
                        result.add(keyResult.getObject(1));
                    }
                    if (result.size() == 1) {
                        this.setResult(result.get(0));
                    } else {
                        this.setResult(result.toArray());
                    }
                }
            }
        }
    });
    // 无主键直接返回null
    if (entityMeta.getIdArray() == null) {
        return null;
    }
    if (result == null) {
        result = fullParamValues[pkIndex];
    }
    // 回置到entity 主键值
    if (needUpdatePk || isIdentity || isSequence) {
        BeanUtil.setProperty(entity, entityMeta.getIdArray()[0], result);
    }
    return result;
}

Example 14 with PreparedStatementResultHandler

use of org.sagacity.sqltoy.callback.PreparedStatementResultHandler in project sagacity-sqltoy by chenrenfei.

the class OracleDialectUtils method getTableColumns.

@SuppressWarnings("unchecked")
public static List<ColumnMeta> getTableColumns(String catalog, String schema, String tableName, Connection conn, Integer dbType, String dialect) throws Exception {
    List<ColumnMeta> tableColumns = DefaultDialectUtils.getTableColumns(catalog, schema, tableName, conn, dbType, dialect);
    String sql = "SELECT COLUMN_NAME,COMMENTS FROM USER_COL_COMMENTS WHERE TABLE_NAME=?";
    PreparedStatement pst = conn.prepareStatement(sql);
    ResultSet rs = null;
    // 通过preparedStatementProcess反调，第二个参数是pst
    Map<String, String> colMap = (Map<String, String>) SqlUtil.preparedStatementProcess(null, pst, rs, new PreparedStatementResultHandler() {

        @Override
        public void execute(Object rowData, PreparedStatement pst, ResultSet rs) throws Exception {
            pst.setString(1, tableName);
            rs = pst.executeQuery();
            Map<String, String> colComments = new HashMap<String, String>();
            String comment;
            while (rs.next()) {
                comment = rs.getString("COMMENTS");
                if (comment != null) {
                    colComments.put(rs.getString("COLUMN_NAME").toUpperCase(), comment);
                }
            }
            this.setResult(colComments);
        }
    });
    for (ColumnMeta col : tableColumns) {
        col.setComments(colMap.get(col.getColName().toUpperCase()));
    }
    return tableColumns;
}

Example 15 with PreparedStatementResultHandler

use of org.sagacity.sqltoy.callback.PreparedStatementResultHandler in project sagacity-sqltoy by chenrenfei.

the class ImpalaDialect method getTableColumns.

@SuppressWarnings("unchecked")
@Override
public List<ColumnMeta> getTableColumns(String catalog, String schema, String tableName, Connection conn, Integer dbType, String dialect) throws Exception {
    List<ColumnMeta> tableCols = DefaultDialectUtils.getTableColumns(catalog, schema, tableName, conn, dbType, dialect);
    // 获取主键信息
    ResultSet rs = conn.createStatement().executeQuery("DESCRIBE " + tableName);
    Map<String, ColumnMeta> colMap = (Map<String, ColumnMeta>) SqlUtil.preparedStatementProcess(null, null, rs, new PreparedStatementResultHandler() {

        @Override
        public void execute(Object rowData, PreparedStatement pst, ResultSet rs) throws Exception {
            Map<String, ColumnMeta> colComments = new HashMap<String, ColumnMeta>();
            while (rs.next()) {
                ColumnMeta colMeta = new ColumnMeta();
                colMeta.setColName(rs.getString("NAME"));
                colMeta.setPK(rs.getBoolean("PRIMARY_KEY"));
                colComments.put(colMeta.getColName(), colMeta);
            }
            this.setResult(colComments);
        }
    });
    ColumnMeta mapColMeta;
    for (ColumnMeta col : tableCols) {
        mapColMeta = colMap.get(col.getColName());
        if (mapColMeta != null) {
            col.setPK(mapColMeta.isPK());
        }
    }
    return tableCols;
}