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

Example 21 with PreparedStatementResultHandler

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

the class SqlServerDialectUtils 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 a.name COLUMN_NAME," + "				 cast(isnull(g.[value],'') as varchar(1000)) as COMMENTS " + "				 FROM syscolumns a  inner join sysobjects d on a.id=d.id " + "				 and d.xtype='U' and d.name<>'dtproperties' " + "				 left join syscomments e on a.cdefault=e.id" + "				 left join sys.extended_properties g " + "				 on a.id=g.major_id AND a.colid = g.minor_id   where d.name=? " + "   order by a.id,a.colorder";
    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 22 with PreparedStatementResultHandler

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

the class SqlServerDialectUtils method save.

/**
 * @todo 保存对象
 * @param sqlToyContext
 * @param entity
 * @param conn
 * @param dbType
 * @param tableName
 * @return
 * @throws Exception
 */
public static Object save(SqlToyContext sqlToyContext, Serializable entity, final Connection conn, final Integer dbType, final String tableName) throws Exception {
    EntityMeta entityMeta = sqlToyContext.getEntityMeta(entity.getClass());
    final boolean isIdentity = entityMeta.getIdStrategy() != null && entityMeta.getIdStrategy().equals(PKStrategy.IDENTITY);
    final boolean isSequence = entityMeta.getIdStrategy() != null && entityMeta.getIdStrategy().equals(PKStrategy.SEQUENCE);
    String insertSql = generateInsertSql(dbType, entityMeta, tableName, entityMeta.getIdStrategy(), "isnull", "@mySeqVariable", isIdentity ? false : true);
    if (isSequence) {
        insertSql = "set nocount on DECLARE @mySeqVariable as numeric(20)=NEXT VALUE FOR " + entityMeta.getSequence() + " " + insertSql + " select @mySeqVariable ";
    }
    int pkIndex = entityMeta.getIdIndex();
    ReflectPropsHandler handler = DialectUtils.getAddReflectHandler(null, sqlToyContext.getUnifyFieldsHandler());
    handler = DialectUtils.getSecureReflectHandler(handler, sqlToyContext.getFieldsSecureProvider(), sqlToyContext.getDesensitizeProvider(), entityMeta.getSecureFields());
    Object[] fullParamValues = BeanUtil.reflectBeanToAry(entity, (isIdentity) ? entityMeta.getRejectIdFieldArray() : entityMeta.getFieldsArray(), null, handler);
    boolean needUpdatePk = false;
    // 是否存在业务ID
    boolean hasBizId = (entityMeta.getBusinessIdGenerator() == null) ? false : true;
    int bizIdColIndex = hasBizId ? entityMeta.getFieldIndex(entityMeta.getBusinessIdField()) : 0;
    // 标识符
    String signature = entityMeta.getBizIdSignature();
    Integer[] relatedColumn = entityMeta.getBizIdRelatedColIndex();
    // 主键采用assign方式赋予，则调用generator产生id并赋予其值
    if (entityMeta.getIdStrategy() != null && null != entityMeta.getIdGenerator()) {
        int idLength = entityMeta.getIdLength();
        int bizIdLength = entityMeta.getBizIdLength();
        Object[] relatedColValue = null;
        String businessIdType = hasBizId ? entityMeta.getColumnJavaType(entityMeta.getBusinessIdField()) : "";
        if (relatedColumn != null) {
            relatedColValue = new Object[relatedColumn.length];
            for (int meter = 0; meter < relatedColumn.length; meter++) {
                relatedColValue[meter] = fullParamValues[relatedColumn[meter]];
                if (relatedColValue[meter] == null) {
                    throw new IllegalArgumentException("对象:" + entityMeta.getEntityClass().getName() + " 生成业务主键依赖的关联字段:" + relatedColumn[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]);
        }
    }
    final Object[] paramValues = fullParamValues;
    final Integer[] paramsType = entityMeta.getFieldsTypeArray();
    SqlExecuteStat.showSql("mssql单条记录插入", insertSql, null);
    final String realInsertSql = insertSql;
    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) {
                pst = conn.prepareStatement(realInsertSql, PreparedStatement.RETURN_GENERATED_KEYS);
            } else {
                pst = conn.prepareStatement(realInsertSql);
            }
            if (null != paramValues && paramValues.length > 0) {
                int index = 0;
                for (int i = 0, n = paramValues.length; i < n; i++) {
                    // sqlserver timestamp类型的字段无需赋值
                    if (!paramsType[i].equals(java.sql.Types.TIMESTAMP)) {
                        SqlUtil.setParamValue(sqlToyContext.getTypeHandler(), conn, dbType, pst, paramValues[i], paramsType[i], index + 1);
                        index++;
                    }
                }
            }
            ResultSet keyResult = null;
            if (isSequence) {
                keyResult = pst.executeQuery();
            } else {
                pst.execute();
            }
            if (isIdentity) {
                keyResult = pst.getGeneratedKeys();
            }
            if (isSequence || isIdentity) {
                while (keyResult.next()) {
                    this.setResult(keyResult.getObject(1));
                }
            }
        }
    });
    // 无主键直接返回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);
    }
    // 是否有子表进行级联保存
    if (!entityMeta.getCascadeModels().isEmpty()) {
        List subTableData = null;
        EntityMeta subTableEntityMeta;
        for (TableCascadeModel cascadeModel : entityMeta.getCascadeModels()) {
            final Object[] mainFieldValues = BeanUtil.reflectBeanToAry(entity, cascadeModel.getFields());
            final String[] mappedFields = cascadeModel.getMappedFields();
            subTableEntityMeta = sqlToyContext.getEntityMeta(cascadeModel.getMappedType());
            if (cascadeModel.getCascadeType() == 1) {
                subTableData = (List) BeanUtil.getProperty(entity, cascadeModel.getProperty());
            } else {
                subTableData = new ArrayList();
                Object item = BeanUtil.getProperty(entity, cascadeModel.getProperty());
                if (item != null) {
                    subTableData.add(item);
                }
            }
            if (subTableData != null && !subTableData.isEmpty()) {
                logger.info("执行save操作的级联子表{}批量保存!", subTableEntityMeta.getTableName());
                SqlExecuteStat.debug("执行子表级联保存操作", null);
                saveAll(sqlToyContext, subTableData, new ReflectPropsHandler() {

                    @Override
                    public void process() {
                        for (int i = 0; i < mappedFields.length; i++) {
                            this.setValue(mappedFields[i], mainFieldValues[i]);
                        }
                    }
                }, conn, dbType, null, null);
            } else {
                logger.info("未执行save操作的级联子表{}批量保存,子表数据为空!", subTableEntityMeta.getTableName());
            }
        }
    }
    return result;
}

Example 23 with PreparedStatementResultHandler

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

the class DefaultDialectUtils method getTablePrimaryKeys.

/**
 * @TODO 获取表的主键字段
 * @param catalog
 * @param schema
 * @param tableName
 * @param conn
 * @param dbType
 * @param dialect
 * @return
 * @throws Exception
 */
@SuppressWarnings("unchecked")
public static Map<String, ColumnMeta> getTablePrimaryKeys(String catalog, String schema, String tableName, Connection conn, final Integer dbType, String dialect) throws Exception {
    ResultSet rs = null;
    try {
        rs = conn.getMetaData().getPrimaryKeys(catalog, schema, tableName);
    } catch (Exception e) {
    }
    if (rs != null) {
        return (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> pkMeta = new HashMap<String, ColumnMeta>();
                while (rs.next()) {
                    ColumnMeta colMeta = new ColumnMeta();
                    colMeta.setColName(rs.getString("COLUMN_NAME"));
                    colMeta.setPK(true);
                    pkMeta.put(colMeta.getColName(), colMeta);
                }
                this.setResult(pkMeta);
            }
        });
    } else if (dbType == DBType.MYSQL || dbType == DBType.MYSQL57) {
        rs = conn.createStatement().executeQuery("desc " + tableName);
        return (Map<String, ColumnMeta>) SqlUtil.preparedStatementProcess(null, null, rs, new PreparedStatementResultHandler() {

            public void execute(Object obj, PreparedStatement pst, ResultSet rs) throws SQLException {
                Map<String, ColumnMeta> pkMeta = new HashMap<String, ColumnMeta>();
                while (rs.next()) {
                    ColumnMeta colMeta = new ColumnMeta();
                    colMeta.setColName(rs.getString("FIELD"));
                    colMeta.setPK(rs.getBoolean("KEY"));
                    if (colMeta.isPK()) {
                        pkMeta.put(colMeta.getColName(), colMeta);
                    }
                }
                this.setResult(pkMeta);
            }
        });
    }
    return null;
}

Example 24 with PreparedStatementResultHandler

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

the class OracleDialectUtils method getTables.

@SuppressWarnings("unchecked")
public static List<TableMeta> getTables(String catalog, String schema, String tableName, Connection conn, Integer dbType, String dialect) throws Exception {
    String sql = "select * from user_tab_comments";
    if (StringUtil.isNotBlank(tableName)) {
        sql = sql.concat(" where TABLE_NAME like ?");
    }
    PreparedStatement pst = conn.prepareStatement(sql);
    ResultSet rs = null;
    // 通过preparedStatementProcess反调，第二个参数是pst
    return (List<TableMeta>) SqlUtil.preparedStatementProcess(null, pst, rs, new PreparedStatementResultHandler() {

        @Override
        public void execute(Object rowData, PreparedStatement pst, ResultSet rs) throws Exception {
            if (StringUtil.isNotBlank(tableName)) {
                if (tableName.contains("%")) {
                    pst.setString(1, tableName);
                } else {
                    pst.setString(1, "%" + tableName + "%");
                }
            }
            rs = pst.executeQuery();
            List<TableMeta> tables = new ArrayList<TableMeta>();
            while (rs.next()) {
                TableMeta tableMeta = new TableMeta();
                tableMeta.setTableName(rs.getString("TABLE_NAME"));
                tableMeta.setType(rs.getString("TABLE_TYPE"));
                tableMeta.setRemarks(rs.getString("COMMENTS"));
                tables.add(tableMeta);
            }
            this.setResult(tables);
        }
    });
}

Example 25 with PreparedStatementResultHandler

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

the class SqlUtil method findByJdbcQuery.

/**
 * @todo <b>sql 查询并返回List集合结果</b>
 * @param typeHandler
 * @param queryStr
 * @param params
 * @param voClass
 * @param rowCallbackHandler
 * @param decryptHandler
 * @param conn
 * @param dbType
 * @param ignoreAllEmptySet
 * @param colFieldMap
 * @param fetchSize
 * @param maxRows
 * @return
 * @throws Exception
 */
public static List findByJdbcQuery(TypeHandler typeHandler, final String queryStr, final Object[] params, final Class voClass, final RowCallbackHandler rowCallbackHandler, final DecryptHandler decryptHandler, final Connection conn, final Integer dbType, final boolean ignoreAllEmptySet, final HashMap<String, String> colFieldMap, final int fetchSize, final int maxRows) throws Exception {
    ResultSet rs = null;
    PreparedStatement pst = conn.prepareStatement(queryStr, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    if (fetchSize > 0) {
        pst.setFetchSize(fetchSize);
    }
    if (maxRows > 0) {
        pst.setMaxRows(maxRows);
    }
    List result = (List) preparedStatementProcess(null, pst, rs, new PreparedStatementResultHandler() {

        public void execute(Object obj, PreparedStatement pst, ResultSet rs) throws Exception {
            setParamsValue(typeHandler, conn, dbType, pst, params, null, 0);
            rs = pst.executeQuery();
            this.setResult(processResultSet(typeHandler, rs, voClass, rowCallbackHandler, decryptHandler, 0, ignoreAllEmptySet, colFieldMap));
        }
    });
    // 为null返回一个空集合
    if (result == null) {
        result = new ArrayList();
    }
    return result;
}