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

Example 1 with PreparedStatementResultHandler

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

the class DialectUtils method save.

/**
 * @todo 保存对象
 * @param sqlToyContext
 * @param entityMeta
 * @param pkStrategy
 * @param isAssignPK
 * @param returnPkType
 * @param insertSql
 * @param entity
 * @param generateSqlHandler
 * @param generateSavePKStrategy
 * @param conn
 * @return
 * @throws Exception
 */
public static Object save(SqlToyContext sqlToyContext, final EntityMeta entityMeta, final PKStrategy pkStrategy, final boolean isAssignPK, final ReturnPkType returnPkType, final String insertSql, Serializable entity, final GenerateSqlHandler generateSqlHandler, final GenerateSavePKStrategy generateSavePKStrategy, final Connection conn) throws Exception {
    final boolean isIdentity = (pkStrategy != null && pkStrategy.equals(PKStrategy.IDENTITY));
    final boolean isSequence = (pkStrategy != null && pkStrategy.equals(PKStrategy.SEQUENCE));
    String[] reflectColumns;
    if ((isIdentity && !isAssignPK) || (isSequence && !isAssignPK)) {
        reflectColumns = entityMeta.getRejectIdFieldArray();
    } else
        reflectColumns = entityMeta.getFieldsArray();
    // 构造全新的新增记录参数赋值反射(覆盖之前的)
    ReflectPropertyHandler handler = getAddReflectHandler(sqlToyContext, null);
    Object[] fullParamValues = BeanUtil.reflectBeanToAry(entity, reflectColumns, null, handler);
    boolean needUpdatePk = false;
    // 无主键,或多主键且非identity、sequence模式
    boolean noPK = (entityMeta.getIdArray() == null);
    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();
    // 主键采用assign方式赋予，则调用generator产生id并赋予其值
    if (entityMeta.getIdStrategy() != null && null != entityMeta.getIdGenerator()) {
        int bizIdLength = entityMeta.getBizIdLength();
        int idLength = entityMeta.getIdLength();
        Object relatedColValue = null;
        int businessIdType = hasBizId ? entityMeta.getColumnType(entityMeta.getBusinessIdField()) : 0;
        if (relatedColumn != null) {
            relatedColValue = fullParamValues[relatedColumn];
        }
        if (StringUtil.isBlank(fullParamValues[pkIndex])) {
            // id通过generator机制产生，设置generator产生的值
            fullParamValues[pkIndex] = entityMeta.getIdGenerator().getId(entityMeta.getSchemaTable(), signature, relatedColValue, null, entityMeta.getIdType(), idLength);
            needUpdatePk = true;
        }
        if (hasBizId && StringUtil.isBlank(fullParamValues[bizIdColIndex])) {
            fullParamValues[bizIdColIndex] = entityMeta.getBusinessIdGenerator().getId(entityMeta.getTableName(), signature, relatedColValue, null, businessIdType, bizIdLength);
            // 回写业务主键值
            BeanUtils.setProperty(entity, entityMeta.getBusinessIdField(), fullParamValues[bizIdColIndex]);
        }
    }
    if (sqlToyContext.isDebug())
        out.println(insertSql);
    final Object[] paramValues = fullParamValues;
    final Integer[] paramsType = entityMeta.getFieldsTypeArray();
    PreparedStatement pst = null;
    Object result = SqlUtil.preparedStatementProcess(null, pst, null, new PreparedStatementResultHandler() {

        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(conn, 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 (noPK)
        return null;
    else {
        if (result == null)
            result = fullParamValues[pkIndex];
        // 回置到entity 主键值
        if (needUpdatePk || isIdentity || isSequence) {
            BeanUtils.setProperty(entity, entityMeta.getIdArray()[0], result);
        }
        // 判定是否有级联子表数据保存
        if (!entityMeta.getOneToManys().isEmpty()) {
            List subTableData;
            final Object[] idValues = BeanUtil.reflectBeanToAry(entity, entityMeta.getIdArray(), null, null);
            EntityMeta subTableEntityMeta;
            String insertSubTableSql;
            SavePKStrategy savePkStrategy;
            for (OneToManyModel oneToMany : entityMeta.getOneToManys()) {
                final String[] mappedFields = oneToMany.getMappedFields();
                subTableEntityMeta = sqlToyContext.getEntityMeta(oneToMany.getMappedType());
                subTableData = (List) PropertyUtils.getProperty(entity, oneToMany.getProperty());
                if (subTableData != null && !subTableData.isEmpty()) {
                    insertSubTableSql = generateSqlHandler.generateSql(subTableEntityMeta, null);
                    savePkStrategy = generateSavePKStrategy.generate(subTableEntityMeta);
                    saveAll(sqlToyContext, subTableEntityMeta, savePkStrategy.getPkStrategy(), savePkStrategy.isAssginValue(), insertSubTableSql, subTableData, sqlToyContext.getBatchSize(), new ReflectPropertyHandler() {

                        public void process() {
                            for (int i = 0; i < mappedFields.length; i++) {
                                this.setValue(mappedFields[i], idValues[i]);
                            }
                        }
                    }, conn, null);
                }
            }
        }
        return result;
    }
}

Example 2 with PreparedStatementResultHandler

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

the class DialectUtils method updateFetchBySql.

/**
 * @todo 实现普通的sql语句查询
 * @param sqlToyContext
 * @param sqlToyConfig
 * @param sql
 * @param paramsValue
 * @param updateRowHandler
 * @param conn
 * @param startIndex
 * @return
 * @throws Exception
 */
public static QueryResult updateFetchBySql(final SqlToyContext sqlToyContext, final SqlToyConfig sqlToyConfig, final String sql, final Object[] paramsValue, final UpdateRowHandler updateRowHandler, final Connection conn, final int startIndex) throws Exception {
    // 打印sql
    SqlExecuteStat.showSql(sql, paramsValue);
    PreparedStatement pst = null;
    if (updateRowHandler == null)
        pst = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    else
        pst = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
    ResultSet rs = null;
    return (QueryResult) SqlUtil.preparedStatementProcess(null, pst, rs, new PreparedStatementResultHandler() {

        public void execute(Object obj, PreparedStatement pst, ResultSet rs) throws Exception {
            SqlUtil.setParamsValue(conn, pst, paramsValue, null, 0);
            rs = pst.executeQuery();
            this.setResult(ResultUtils.processResultSet(sqlToyContext, sqlToyConfig, conn, rs, null, updateRowHandler, startIndex));
        }
    });
}

Example 3 with PreparedStatementResultHandler

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

the class DialectUtils method getCountBySql.

/**
 * @todo 通用的查询记录总数
 * @param sqlToyContext
 * @param sql
 * @param paramsValue
 * @param isLastSql
 * @param conn
 * @return
 * @throws Exception
 */
public static Long getCountBySql(final SqlToyContext sqlToyContext, final String sql, final Object[] paramsValue, final boolean isLastSql, final Connection conn) throws Exception {
    String lastCountSql;
    int paramCnt = 0;
    int withParamCnt = 0;
    // 通过配置直接给定的最优化count 语句
    if (isLastSql) {
        lastCountSql = sql;
    } else {
        // with as分析器
        SqlWithAnalysis sqlWith = new SqlWithAnalysis(sql);
        String query_tmp = sqlWith.getRejectWithSql();
        int lastBracketIndex = query_tmp.lastIndexOf(")");
        // 剔除order提高运行效率
        int orderByIndex = StringUtil.matchLastIndex(query_tmp, ORDER_BY_PATTERN);
        // 剔除order by 语句
        if (orderByIndex > lastBracketIndex)
            query_tmp = query_tmp.substring(0, orderByIndex + 1);
        int groupIndex = StringUtil.matchLastIndex(query_tmp, GROUP_BY_PATTERN);
        // 判断group by 是否是内层，如select * from (select * from table group by)
        // 外层group by 必须要进行包裹(update by chenrenfei 2016-4-21)
        boolean isInnerGroup = false;
        if (groupIndex != -1)
            isInnerGroup = clearDisturbSql(query_tmp.substring(groupIndex + 1)).lastIndexOf(")") != -1;
        final StringBuilder countQueryStr = new StringBuilder();
        // 是否包含union,update 2012-11-21
        boolean hasUnion = StringUtil.matches(query_tmp, UNION_PATTERN);
        // 性能最优
        if (!StringUtil.matches(query_tmp.trim(), DISTINCT_PATTERN) && !hasUnion && (groupIndex == -1 || (groupIndex < lastBracketIndex && isInnerGroup))) {
            int sql_from_index = 0;
            // sql不以from开头，截取from 后的部分语句
            if (StringUtil.indexOfIgnoreCase(query_tmp, "from") != 0)
                sql_from_index = StringUtil.getSymMarkIndexIgnoreCase("select ", " from", query_tmp, 0);
            String selectFields = (sql_from_index < 1) ? "" : query_tmp.substring(0, sql_from_index).toLowerCase();
            // 存在统计函数 update by chenrenfei ,date: 2017-2-24
            if (StringUtil.matches(selectFields, STAT_PATTERN))
                countQueryStr.append("select count(1) from (").append(query_tmp).append(") sag_count_tmpTable ");
            else
                // 截取from后的部分
                countQueryStr.append("select count(1) ").append((sql_from_index != -1 ? query_tmp.substring(sql_from_index) : query_tmp));
        } else // 包含distinct 或包含union则直接将查询作为子表(普通做法)
        {
            countQueryStr.append("select count(1) from (").append(query_tmp).append(") sag_count_tmpTable ");
        }
        paramCnt = SqlUtil.getParamsCount(countQueryStr.toString(), false);
        withParamCnt = SqlUtil.getParamsCount(sqlWith.getWithSql(), false);
        countQueryStr.insert(0, sqlWith.getWithSql() + " ");
        lastCountSql = countQueryStr.toString();
    }
    final int paramCntFin = paramCnt;
    final int withParamCntFin = withParamCnt;
    Object[] realParamsTemp = null;
    if (paramsValue != null) {
        // 将from前的参数剔除
        realParamsTemp = isLastSql ? paramsValue : CollectionUtil.subtractArray(paramsValue, withParamCntFin, paramsValue.length - paramCntFin - withParamCntFin);
    }
    final Object[] realParams = realParamsTemp;
    // 打印sql
    SqlExecuteStat.showSql(lastCountSql, realParams);
    PreparedStatement pst = conn.prepareStatement(lastCountSql);
    ResultSet rs = null;
    return (Long) SqlUtil.preparedStatementProcess(null, pst, rs, new PreparedStatementResultHandler() {

        public void execute(Object obj, PreparedStatement pst, ResultSet rs) throws SQLException, IOException {
            long resultCount = 0;
            if (realParams != null) {
                SqlUtil.setParamsValue(conn, pst, realParams, null, 0);
            }
            rs = pst.executeQuery();
            if (rs.next()) {
                resultCount = rs.getLong(1);
            }
            this.setResult(resultCount);
        }
    });
}

Example 4 with PreparedStatementResultHandler

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

the class SqlUtil method executeSql.

/**
 * @todo 执行Sql语句完成修改操作
 * @param executeSql
 * @param params
 * @param paramsType
 * @param conn
 * @param autoCommit
 * @throws Exception
 */
public static Long executeSql(final String executeSql, final Object[] params, final Integer[] paramsType, final Connection conn, final Boolean autoCommit) throws Exception {
    if (logger.isDebugEnabled())
        logger.debug("executeJdbcSql=" + executeSql);
    boolean hasSetAutoCommit = false;
    Long updateCounts = null;
    if (autoCommit != null) {
        if (!autoCommit == conn.getAutoCommit()) {
            conn.setAutoCommit(autoCommit);
            hasSetAutoCommit = true;
        }
    }
    PreparedStatement pst = conn.prepareStatement(executeSql);
    Object result = SqlUtil.preparedStatementProcess(null, pst, null, new PreparedStatementResultHandler() {

        public void execute(Object obj, PreparedStatement pst, ResultSet rs) throws SQLException, IOException {
            SqlUtil.setParamsValue(conn, pst, params, paramsType, 0);
            pst.executeUpdate();
            // 返回update的记录数量
            this.setResult(new Long(pst.getUpdateCount()));
        }
    });
    if (result != null)
        updateCounts = (Long) result;
    if (hasSetAutoCommit && autoCommit != null)
        conn.setAutoCommit(!autoCommit);
    return updateCounts;
}

Example 5 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 queryStr
 * @param params
 * @param voClass
 * @param rowCallbackHandler
 * @param conn
 * @return
 * @throws Exception
 */
public static List findByJdbcQuery(final String queryStr, final Object[] params, final Class voClass, final RowCallbackHandler rowCallbackHandler, final Connection conn) throws Exception {
    ResultSet rs = null;
    PreparedStatement pst = conn.prepareStatement(queryStr, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    List result = (List) preparedStatementProcess(null, pst, rs, new PreparedStatementResultHandler() {

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