Examples with PreparedStatement java.sql.PreparedStatement used on opensource projects

Example 96 with PreparedStatement

use of java.sql.PreparedStatement in project sharding-jdbc by dangdangdotcom.

the class ShardingPreparedStatementTest method assertExecuteUpdateWithoutParameter.

@Test
public void assertExecuteUpdateWithoutParameter() throws SQLException {
    String sql = "DELETE FROM `t_order` WHERE `status` = 'init'";
    try (Connection connection = shardingDataSource.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
        assertThat(preparedStatement.executeUpdate(), is(40));
        assertThat(preparedStatement.executeUpdate(), is(0));
        assertThat(preparedStatement.executeUpdate(), is(0));
    }
}

Example 97 with PreparedStatement

use of java.sql.PreparedStatement in project sharding-jdbc by dangdangdotcom.

the class ShardingPreparedStatementTest method assertUpdateBatch.

@Test
public void assertUpdateBatch() throws SQLException {
    String sql = "UPDATE `t_order` SET `status` = ? WHERE  `status` = ?";
    try (Connection connection = shardingDataSource.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
        preparedStatement.setString(1, "batch");
        preparedStatement.setString(2, "init");
        preparedStatement.addBatch();
        preparedStatement.setString(1, "batch");
        preparedStatement.setString(2, "init");
        preparedStatement.addBatch();
        preparedStatement.setString(1, "init");
        preparedStatement.setString(2, "batch");
        preparedStatement.addBatch();
        int[] result = preparedStatement.executeBatch();
        assertThat(result.length, is(3));
        assertThat(result[0], is(40));
        assertThat(result[1], is(0));
        assertThat(result[2], is(40));
    } finally {
        DMLExecutionEventBus.clearListener();
    }
}

Example 98 with PreparedStatement

use of java.sql.PreparedStatement in project sharding-jdbc by dangdangdotcom.

the class AbstractShardingBothDataBasesAndTablesSpringDBUnitTest method selectData.

private void selectData() throws SQLException {
    String sql = "SELECT i.order_id, i.order_item_id  FROM `t_order` o JOIN `t_order_item` i ON o.user_id = i.user_id AND o.order_id = i.order_id" + " WHERE o.`user_id` = ? AND o.`order_id` = ? AND i.`order_id` = ? ORDER BY i.order_item_id DESC";
    try (Connection connection = getShardingDataSource().getConnection()) {
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, 1);
        preparedStatement.setInt(2, 1);
        preparedStatement.setInt(3, 1);
        ResultSet resultSet = preparedStatement.executeQuery();
        int count = 0;
        while (resultSet.next()) {
            if (0 == count) {
                assertThat(resultSet.getInt(1), is(1));
                assertThat(resultSet.getInt(2), is(5));
            } else if (1 == count) {
                assertThat(resultSet.getInt(1), is(1));
                assertThat(resultSet.getInt(2), is(1));
            }
            count++;
        }
        preparedStatement.close();
    }
}

Example 99 with PreparedStatement

use of java.sql.PreparedStatement in project sharding-jdbc by dangdangdotcom.

the class RdbTransactionLogStorage method add.

@Override
public void add(final TransactionLog transactionLog) {
    String sql = "INSERT INTO `transaction_log` (`id`, `transaction_type`, `data_source`, `sql`, `parameters`, `creation_time`) VALUES (?, ?, ?, ?, ?, ?);";
    try (Connection conn = dataSource.getConnection();
        PreparedStatement preparedStatement = conn.prepareStatement(sql)) {
        preparedStatement.setString(1, transactionLog.getId());
        preparedStatement.setString(2, SoftTransactionType.BestEffortsDelivery.name());
        preparedStatement.setString(3, transactionLog.getDataSource());
        preparedStatement.setString(4, transactionLog.getSql());
        preparedStatement.setString(5, new Gson().toJson(transactionLog.getParameters()));
        preparedStatement.setLong(6, transactionLog.getCreationTime());
        preparedStatement.executeUpdate();
    } catch (final SQLException ex) {
        throw new TransactionLogStorageException(ex);
    }
}

Example 100 with PreparedStatement

use of java.sql.PreparedStatement in project sharding-jdbc by dangdangdotcom.

the class RdbTransactionLogStorage method findEligibleTransactionLogs.

@Override
public List<TransactionLog> findEligibleTransactionLogs(final int size, final int maxDeliveryTryTimes, final long maxDeliveryTryDelayMillis) {
    List<TransactionLog> result = new ArrayList<>(size);
    String sql = "SELECT `id`, `transaction_type`, `data_source`, `sql`, `parameters`, `creation_time`, `async_delivery_try_times` " + "FROM `transaction_log` WHERE `async_delivery_try_times`<? AND `transaction_type`=? AND `creation_time`<? LIMIT ?;";
    try (Connection conn = dataSource.getConnection()) {
        try (PreparedStatement preparedStatement = conn.prepareStatement(sql)) {
            preparedStatement.setInt(1, maxDeliveryTryTimes);
            preparedStatement.setString(2, SoftTransactionType.BestEffortsDelivery.name());
            preparedStatement.setLong(3, System.currentTimeMillis() - maxDeliveryTryDelayMillis);
            preparedStatement.setInt(4, size);
            try (ResultSet rs = preparedStatement.executeQuery()) {
                while (rs.next()) {
                    Gson gson = new Gson();
                    //TODO 对于批量执行的参数需要解析成两层列表
                    List<Object> parameters = gson.fromJson(rs.getString(5), new TypeToken<List<Object>>() {
                    }.getType());
                    result.add(new TransactionLog(rs.getString(1), "", SoftTransactionType.valueOf(rs.getString(2)), rs.getString(3), rs.getString(4), parameters, rs.getLong(6), rs.getInt(7)));
                }
            }
        }
    } catch (final SQLException ex) {
        throw new TransactionLogStorageException(ex);
    }
    return result;
}