Examples with PreparedStatementCallback org.springframework.jdbc.core.PreparedStatementCallback used on opensource projects

Example 1 with PreparedStatementCallback

use of org.springframework.jdbc.core.PreparedStatementCallback in project otter by alibaba.

the class DbDialectTest method test_mysql.

@Test(expectedExceptions = RuntimeException.class)
public void test_mysql() {
    DbDataMedia media = getMysqlMedia();
    final DbDialect dbDialect = dbDialectFactory.getDbDialect(2L, media.getSource());
    want.object(dbDialect).clazIs(MysqlDialect.class);
    final SqlTemplate sqlTemplate = dbDialect.getSqlTemplate();
    final JdbcTemplate jdbcTemplate = dbDialect.getJdbcTemplate();
    final TransactionTemplate transactionTemplate = dbDialect.getTransactionTemplate();
    final int[] pkColumnTypes = { Types.INTEGER, Types.VARCHAR };
    final int[] columnTypes = { Types.CHAR, Types.DECIMAL, Types.BLOB, Types.CLOB, Types.DATE, Types.TIMESTAMP, Types.TIMESTAMP };
    transactionTemplate.execute(new TransactionCallback() {

        public Object doInTransaction(TransactionStatus status) {
            int affect = 0;
            String sql = null;
            // 执行insert
            sql = sqlTemplate.getInsertSql(MYSQL_SCHEMA_NAME, TABLE_NAME, pkColumns, columns);
            System.out.println(sql);
            affect = (Integer) jdbcTemplate.execute(sql, new PreparedStatementCallback() {

                public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                    doPreparedStatement(ps, dbDialect, toTypes(columnTypes, pkColumnTypes), toValues(columnValues, pkColumnValues));
                    return ps.executeUpdate();
                }
            });
            want.number(affect).isEqualTo(1);
            // 执行update
            sql = sqlTemplate.getUpdateSql(MYSQL_SCHEMA_NAME, TABLE_NAME, pkColumns, columns);
            System.out.println(sql);
            affect = (Integer) jdbcTemplate.execute(sql, new PreparedStatementCallback() {

                public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                    doPreparedStatement(ps, dbDialect, toTypes(columnTypes, pkColumnTypes), toValues(columnValues, pkColumnValues));
                    return ps.executeUpdate();
                }
            });
            want.number(affect).isEqualTo(1);
            // 执行deleate
            sql = sqlTemplate.getDeleteSql(MYSQL_SCHEMA_NAME, TABLE_NAME, pkColumns);
            System.out.println(sql);
            affect = (Integer) jdbcTemplate.execute(sql, new PreparedStatementCallback() {

                public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                    doPreparedStatement(ps, dbDialect, toTypes(pkColumnTypes), toValues(pkColumnValues));
                    return ps.executeUpdate();
                }
            });
            want.number(affect).isEqualTo(1);
            // 执行merge
            sql = sqlTemplate.getMergeSql(MYSQL_SCHEMA_NAME, TABLE_NAME, pkColumns, columns, null, true);
            System.out.println(sql);
            affect = (Integer) jdbcTemplate.execute(sql, new PreparedStatementCallback() {

                public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                    doPreparedStatement(ps, dbDialect, toTypes(columnTypes, pkColumnTypes), toValues(columnValues, pkColumnValues));
                    return ps.executeUpdate();
                }
            });
            want.number(affect).isEqualTo(1);
            throw new RuntimeException("rollback");
        }
    });
}

Example 2 with PreparedStatementCallback

use of org.springframework.jdbc.core.PreparedStatementCallback in project camel by apache.

the class SqlConsumer method poll.

@Override
protected int poll() throws Exception {
    // must reset for each poll
    shutdownRunningTask = null;
    pendingExchanges = 0;
    final String preparedQuery = sqlPrepareStatementStrategy.prepareQuery(resolvedQuery, getEndpoint().isAllowNamedParameters(), null);
    log.trace("poll: {}", preparedQuery);
    final PreparedStatementCallback<Integer> callback = new PreparedStatementCallback<Integer>() {

        @Override
        public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
            Queue<DataHolder> answer = new LinkedList<DataHolder>();
            log.debug("Executing query: {}", preparedQuery);
            ResultSet rs = ps.executeQuery();
            SqlOutputType outputType = getEndpoint().getOutputType();
            boolean closeEager = true;
            try {
                log.trace("Got result list from query: {}, outputType={}", rs, outputType);
                if (outputType == SqlOutputType.StreamList) {
                    ResultSetIterator data = getEndpoint().queryForStreamList(ps.getConnection(), ps, rs);
                    // only process if we have data
                    if (data.hasNext()) {
                        addListToQueue(data, answer);
                        closeEager = false;
                    }
                } else if (outputType == SqlOutputType.SelectList) {
                    List<?> data = getEndpoint().queryForList(rs, true);
                    addListToQueue(data, answer);
                } else if (outputType == SqlOutputType.SelectOne) {
                    Object data = getEndpoint().queryForObject(rs);
                    if (data != null) {
                        addListToQueue(data, answer);
                    }
                } else {
                    throw new IllegalArgumentException("Invalid outputType=" + outputType);
                }
            } finally {
                if (closeEager) {
                    closeResultSet(rs);
                }
            }
            // process all the exchanges in this batch
            try {
                if (answer.isEmpty()) {
                    // no data
                    return 0;
                } else {
                    int rows = processBatch(CastUtils.cast(answer));
                    return rows;
                }
            } catch (Exception e) {
                throw ObjectHelper.wrapRuntimeCamelException(e);
            } finally {
                closeResultSet(rs);
            }
        }
    };
    Integer messagePolled;
    if (namedJdbcTemplate != null) {
        messagePolled = namedJdbcTemplate.execute(preparedQuery, parameterSource, callback);
    } else {
        messagePolled = jdbcTemplate.execute(preparedQuery, callback);
    }
    return messagePolled;
}

Example 3 with PreparedStatementCallback

use of org.springframework.jdbc.core.PreparedStatementCallback in project otter by alibaba.

the class DbDialectIntegration method test_mysql.

@Test(expectedExceptions = RuntimeException.class)
public void test_mysql() {
    DbMediaSource dbMediaSource = new DbMediaSource();
    dbMediaSource.setId(10L);
    dbMediaSource.setDriver("com.mysql.jdbc.Driver");
    dbMediaSource.setUsername("xxxxx");
    dbMediaSource.setPassword("xxxxx");
    dbMediaSource.setUrl("jdbc:mysql://127.0.0.1:3306");
    dbMediaSource.setEncode("UTF-8");
    dbMediaSource.setType(DataMediaType.MYSQL);
    final DbDialect dbDialect = dbDialectFactory.getDbDialect(2L, dbMediaSource);
    want.object(dbDialect).clazIs(MysqlDialect.class);
    Table table = dbDialect.findTable("test", "ljh_demo");
    System.out.println(table);
    final SqlTemplate sqlTemplate = dbDialect.getSqlTemplate();
    final JdbcTemplate jdbcTemplate = dbDialect.getJdbcTemplate();
    final TransactionTemplate transactionTemplate = dbDialect.getTransactionTemplate();
    final int[] pkColumnTypes = { Types.INTEGER };
    final int[] columnTypes = { Types.VARCHAR, Types.INTEGER, Types.DECIMAL, Types.BIGINT };
    transactionTemplate.execute(new TransactionCallback() {

        public Object doInTransaction(TransactionStatus status) {
            int affect = 0;
            String sql = null;
            // 执行insert
            sql = sqlTemplate.getInsertSql(SCHEMA_NAME, TABLE_NAME, pkColumns, columns);
            System.out.println(sql);
            affect = (Integer) jdbcTemplate.execute(sql, new PreparedStatementCallback() {

                public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                    doPreparedStatement(ps, dbDialect, toTypes(columnTypes, pkColumnTypes), toValues(columnValues, pkColumnValues));
                    return ps.executeUpdate();
                }
            });
            want.number(affect).isEqualTo(1);
            throw new RuntimeException("rollback");
        }
    });
}

Example 4 with PreparedStatementCallback

use of org.springframework.jdbc.core.PreparedStatementCallback in project otter by alibaba.

the class DbDialectTest method test_oracle.

@Test(expectedExceptions = RuntimeException.class)
public void test_oracle() {
    DbDataMedia media = getOracleMedia();
    final DbDialect dbDialect = dbDialectFactory.getDbDialect(1L, media.getSource());
    want.object(dbDialect).clazIs(OracleDialect.class);
    final SqlTemplate sqlTemplate = dbDialect.getSqlTemplate();
    final JdbcTemplate jdbcTemplate = dbDialect.getJdbcTemplate();
    final TransactionTemplate transactionTemplate = dbDialect.getTransactionTemplate();
    final int[] pkColumnTypes = { Types.NUMERIC, Types.VARCHAR };
    final int[] columnTypes = { Types.CHAR, Types.NUMERIC, Types.BLOB, Types.CLOB, Types.DATE, Types.DATE, Types.DATE };
    transactionTemplate.execute(new TransactionCallback() {

        public Object doInTransaction(TransactionStatus status) {
            int affect = 0;
            String sql = null;
            // 执行insert
            sql = sqlTemplate.getInsertSql(ORACLE_SCHEMA_NAME, TABLE_NAME, pkColumns, columns);
            System.out.println(sql);
            affect = (Integer) jdbcTemplate.execute(sql, new PreparedStatementCallback() {

                public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                    doPreparedStatement(ps, dbDialect, toTypes(columnTypes, pkColumnTypes), toValues(columnValues, pkColumnValues));
                    return ps.executeUpdate();
                }
            });
            want.number(affect).isEqualTo(1);
            // 执行update
            sql = sqlTemplate.getUpdateSql(ORACLE_SCHEMA_NAME, TABLE_NAME, pkColumns, columns);
            System.out.println(sql);
            affect = (Integer) jdbcTemplate.execute(sql, new PreparedStatementCallback() {

                public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                    doPreparedStatement(ps, dbDialect, toTypes(columnTypes, pkColumnTypes), toValues(columnValues, pkColumnValues));
                    return ps.executeUpdate();
                }
            });
            want.number(affect).isEqualTo(1);
            // 执行deleate
            sql = sqlTemplate.getDeleteSql(ORACLE_SCHEMA_NAME, TABLE_NAME, pkColumns);
            System.out.println(sql);
            affect = (Integer) jdbcTemplate.execute(sql, new PreparedStatementCallback() {

                public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                    doPreparedStatement(ps, dbDialect, toTypes(pkColumnTypes), toValues(pkColumnValues));
                    return ps.executeUpdate();
                }
            });
            want.number(affect).isEqualTo(1);
            // 执行merge
            sql = sqlTemplate.getMergeSql(ORACLE_SCHEMA_NAME, TABLE_NAME, pkColumns, columns, null, true);
            System.out.println(sql);
            affect = (Integer) jdbcTemplate.execute(sql, new PreparedStatementCallback() {

                public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                    doPreparedStatement(ps, dbDialect, toTypes(columnTypes, pkColumnTypes), toValues(columnValues, pkColumnValues));
                    return ps.executeUpdate();
                }
            });
            want.number(affect).isEqualTo(1);
            throw new RuntimeException("rollback");
        }
    });
}

Example 5 with PreparedStatementCallback

use of org.springframework.jdbc.core.PreparedStatementCallback in project otter by alibaba.

the class TimeTableIntegration method test_mysql.

@Test
public void test_mysql() {
    DbMediaSource dbMediaSource = new DbMediaSource();
    dbMediaSource.setId(10L);
    dbMediaSource.setDriver("oracle.jdbc.OracleDriver");
    dbMediaSource.setUsername("otter1");
    dbMediaSource.setPassword("jonathan");
    dbMediaSource.setUrl("jdbc:oracle:thin:@127.0.0.1:1521:ointest");
    dbMediaSource.setEncode("UTF-8");
    dbMediaSource.setType(DataMediaType.ORACLE);
    final DbDialect dbDialect = dbDialectFactory.getDbDialect(2L, dbMediaSource);
    // want.object(dbDialect).clazIs(MysqlDialect.class);
    Table table = dbDialect.findTable("otter2", "test_time");
    System.out.println(table);
    final SqlTemplate sqlTemplate = dbDialect.getSqlTemplate();
    final JdbcTemplate jdbcTemplate = dbDialect.getJdbcTemplate();
    final TransactionTemplate transactionTemplate = dbDialect.getTransactionTemplate();
    final int[] pkColumnTypes = { Types.INTEGER };
    final int[] columnTypes = { Types.TIMESTAMP, Types.TIMESTAMP, Types.DATE, Types.TIME, Types.INTEGER, Types.INTEGER };
    transactionTemplate.execute(new TransactionCallback() {

        public Object doInTransaction(TransactionStatus status) {
            int affect = 0;
            String sql = null;
            // 执行insert
            sql = sqlTemplate.getInsertSql(SCHEMA_NAME, TABLE_NAME, pkColumns, columns);
            System.out.println(sql);
            affect = (Integer) jdbcTemplate.execute(sql, new PreparedStatementCallback() {

                public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                    doPreparedStatement(ps, dbDialect, toTypes(columnTypes, pkColumnTypes), toValues(columnValues, pkColumnValues));
                    return ps.executeUpdate();
                }
            });
            want.number(affect).isEqualTo(1);
            return null;
        // throw new RuntimeException("rollback");
        }
    });
}