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

Example 6 with PreparedStatementResultHandler

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

the class DialectUtils method findBySql.

/**
 * @todo 实现普通的sql语句查询
 * @param sqlToyContext
 * @param sqlToyConfig
 * @param sql
 * @param paramsValue
 * @param rowCallbackHandler
 * @param decryptHandler     解密
 * @param conn
 * @param dbType
 * @param startIndex
 * @param fetchSize
 * @param maxRows
 * @return
 * @throws Exception
 */
public static QueryResult findBySql(final SqlToyContext sqlToyContext, final SqlToyConfig sqlToyConfig, final String sql, final Object[] paramsValue, final RowCallbackHandler rowCallbackHandler, final DecryptHandler decryptHandler, final Connection conn, final Integer dbType, final int startIndex, final int fetchSize, final int maxRows) throws Exception {
    // 做sql签名
    String lastSql = SqlUtilsExt.signSql(sql, dbType, sqlToyConfig);
    // 打印sql
    SqlExecuteStat.showSql("执行查询", lastSql, paramsValue);
    PreparedStatement pst = conn.prepareStatement(lastSql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    if (fetchSize > 0) {
        pst.setFetchSize(fetchSize);
    }
    if (maxRows > 0) {
        pst.setMaxRows(maxRows);
    }
    ResultSet rs = null;
    return (QueryResult) SqlUtil.preparedStatementProcess(null, pst, rs, new PreparedStatementResultHandler() {

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

Example 7 with PreparedStatementResultHandler

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

the class DialectUtils method getCountBySql.

/**
 * @todo 通用的查询记录总数(包含剔除order by和智能判断是直接select count from ()
 *       还是直接剔除from之前的语句补充select count)
 * @param sqlToyContext
 * @param sqlToyConfig
 * @param sql
 * @param paramsValue
 * @param isLastSql
 * @param conn
 * @param dbType
 * @return
 * @throws Exception
 */
public static Long getCountBySql(final SqlToyContext sqlToyContext, final SqlToyConfig sqlToyConfig, final String sql, final Object[] paramsValue, final boolean isLastSql, final Connection conn, final Integer dbType) throws Exception {
    String lastCountSql;
    int paramCnt = 0;
    int withParamCnt = 0;
    // 通过配置直接给定的最优化count 语句
    if (isLastSql) {
        lastCountSql = sql;
    } else {
        String countPart = " count(1) ";
        // es count(1) 不起作用
        if (dbType.equals(DBType.ES)) {
            countPart = " count(*) ";
        }
        String query_tmp = sql;
        String withSql = "";
        // with as分析器(避免每次做with 检测,提升效率)
        if (sqlToyConfig != null && sqlToyConfig.isHasWith()) {
            SqlWithAnalysis sqlWith = new SqlWithAnalysis(sql);
            query_tmp = sqlWith.getRejectWithSql();
            withSql = sqlWith.getWithSql();
        }
        int lastBracketIndex = query_tmp.lastIndexOf(")");
        int sql_from_index = 0;
        // sql不以from开头，截取from 后的部分语句
        if (StringUtil.indexOfIgnoreCase(query_tmp, "from") != 0) {
            sql_from_index = StringUtil.getSymMarkMatchIndex(SELECT_REGEX, FROM_REGEX, query_tmp.toLowerCase(), 0);
        }
        // 剔除order提高运行效率
        int orderByIndex = StringUtil.matchLastIndex(query_tmp, ORDER_BY_PATTERN);
        // order by 在from 之后
        if (orderByIndex > sql_from_index) {
            // 剔除order by 语句
            if (orderByIndex > lastBracketIndex) {
                query_tmp = query_tmp.substring(0, orderByIndex + 1);
            } else {
                // 剔除掉order by 后面语句对称的() 内容
                String orderJudgeSql = clearDisturbSql(query_tmp.substring(orderByIndex + 1));
                // 在order by 不在子查询内,说明可以整体切除掉order by
                if (orderJudgeSql.indexOf(")") == -1) {
                    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 selectIndex = StringUtil.matchIndex(query_tmp.toLowerCase(), SELECT_REGEX);
            // 截取出select 和from之间的语句
            String selectFields = (sql_from_index < 1) ? "" : query_tmp.substring(selectIndex + 6, sql_from_index).toLowerCase();
            // 剔除嵌套的子查询语句中select 和 from 之间的内容,便于判断统计函数的作用位置
            selectFields = clearSymSelectFromSql(selectFields);
            // 存在统计函数 update by chenrenfei ,date: 2017-2-24
            if (StringUtil.matches(selectFields, STAT_PATTERN)) {
                countQueryStr.append("select ").append(countPart).append(" from (").append(query_tmp).append(") sag_count_tmpTable ");
            } else {
                // 截取from后的部分
                countQueryStr.append("select ").append(countPart).append((sql_from_index != -1 ? query_tmp.substring(sql_from_index) : query_tmp));
            }
        } else // 包含distinct 或包含union则直接将查询作为子表(普通做法)
        {
            countQueryStr.append("select ").append(countPart).append(" from (").append(query_tmp).append(") sag_count_tmpTable ");
        }
        paramCnt = getParamsCount(countQueryStr.toString());
        withParamCnt = getParamsCount(withSql);
        countQueryStr.insert(0, withSql + " ");
        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签名
    lastCountSql = SqlUtilsExt.signSql(lastCountSql, dbType, sqlToyConfig);
    // 打印sql
    SqlExecuteStat.showSql("执行count查询", lastCountSql, realParams);
    PreparedStatement pst = conn.prepareStatement(lastCountSql);
    ResultSet rs = null;
    return (Long) SqlUtil.preparedStatementProcess(null, pst, rs, new PreparedStatementResultHandler() {

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

Example 8 with PreparedStatementResultHandler

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

the class SqlServerDialectUtils 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 d.name TABLE_NAME, cast(isnull(f.value,'') as varchar(1000)) COMMENTS,d.xtype TABLE_TYPE" + " from syscolumns a " + "		 inner join sysobjects d on a.id=d.id and d.xtype in ('U','V') and d.name<>'dtproperties' " + "		 left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0 " + "		 where a.colorder=1 ";
    if (StringUtil.isNotBlank(tableName)) {
        sql = sql.concat(" and d.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"));
                if (tableMeta.getType().equals("V")) {
                    tableMeta.setType("VIEW");
                } else {
                    tableMeta.setType("TABLE");
                }
                tableMeta.setRemarks(rs.getString("COMMENTS"));
                tables.add(tableMeta);
            }
            this.setResult(tables);
        }
    });
}

Example 9 with PreparedStatementResultHandler

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

the class DefaultDialectUtils method getTableColumns.

@SuppressWarnings("unchecked")
public static List<ColumnMeta> getTableColumns(String catalog, String schema, String tableName, Connection conn, Integer dbType, String dialect) throws Exception {
    ResultSet rs = conn.getMetaData().getColumns(catalog, schema, tableName, "%");
    // 通过preparedStatementProcess反调，第二个参数是pst
    List<ColumnMeta> tableCols = (List<ColumnMeta>) SqlUtil.preparedStatementProcess(null, null, rs, new PreparedStatementResultHandler() {

        @Override
        public void execute(Object rowData, PreparedStatement pst, ResultSet rs) throws Exception {
            List<ColumnMeta> colMetas = new ArrayList<ColumnMeta>();
            String isAutoIncrement;
            while (rs.next()) {
                ColumnMeta colMeta = new ColumnMeta();
                colMeta.setColName(rs.getString("COLUMN_NAME"));
                colMeta.setDataType(rs.getInt("DATA_TYPE"));
                colMeta.setTypeName(rs.getString("TYPE_NAME"));
                colMeta.setDefaultValue(SqlUtil.clearDefaultValue(rs.getString("COLUMN_DEF")));
                colMeta.setColumnSize(rs.getInt("COLUMN_SIZE"));
                colMeta.setDecimalDigits(rs.getInt("DECIMAL_DIGITS"));
                colMeta.setNumPrecRadix(rs.getInt("NUM_PREC_RADIX"));
                colMeta.setComments(rs.getString("REMARKS"));
                isAutoIncrement = rs.getString("IS_AUTOINCREMENT");
                if (isAutoIncrement != null && (isAutoIncrement.equalsIgnoreCase("true") || isAutoIncrement.equalsIgnoreCase("YES") || isAutoIncrement.equalsIgnoreCase("Y") || isAutoIncrement.equals("1"))) {
                    colMeta.setAutoIncrement(true);
                } else {
                    colMeta.setAutoIncrement(false);
                }
                if (rs.getInt("NULLABLE") == 1) {
                    colMeta.setNullable(true);
                } else {
                    colMeta.setNullable(false);
                }
                colMetas.add(colMeta);
            }
            this.setResult(colMetas);
        }
    });
    // 获取主键信息
    Map<String, ColumnMeta> pkMap = getTablePrimaryKeys(catalog, schema, tableName, conn, dbType, dialect);
    if (pkMap == null || pkMap.isEmpty()) {
        return tableCols;
    }
    ColumnMeta mapMeta;
    for (ColumnMeta colMeta : tableCols) {
        mapMeta = pkMap.get(colMeta.getColName());
        if (mapMeta != null) {
            colMeta.setPK(true);
        }
    }
    return tableCols;
}

Example 10 with PreparedStatementResultHandler

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

the class DefaultDialectUtils method getTables.

@SuppressWarnings("unchecked")
public static List<TableMeta> getTables(String catalog, String schema, String tableName, Connection conn, Integer dbType, String dialect) throws Exception {
    // 可自定义 PreparedStatement pst=conn.xxx;
    ResultSet rs = conn.getMetaData().getTables(catalog, schema, tableName, new String[] { "TABLE", "VIEW" });
    // 通过preparedStatementProcess反调，第二个参数是pst
    return (List<TableMeta>) SqlUtil.preparedStatementProcess(null, null, rs, new PreparedStatementResultHandler() {

        @Override
        public void execute(Object rowData, PreparedStatement pst, ResultSet rs) throws Exception {
            List<TableMeta> tables = new ArrayList<TableMeta>();
            while (rs.next()) {
                TableMeta tableMeta = new TableMeta();
                tableMeta.setTableName(rs.getString("TABLE_NAME"));
                tableMeta.setSchema(rs.getString("TABLE_SCHEM"));
                tableMeta.setType(rs.getString("TABLE_TYPE"));
                tableMeta.setRemarks(rs.getString("REMARKS"));
                tables.add(tableMeta);
            }
            this.setResult(tables);
        }
    });
}