HasorDB 批量操作SQL

HasorDB 提供提供了 5 個基于 SQL 批量操作接口，下面重點說其中 4 個。另外一個會在介紹 SqlParameterSource 接口時提到

批量執(zhí)行 SQL 命令?

批量執(zhí)行 SQL 命令適用于不需要參數的語句，它們將會通過 ?Statement ?接口來執(zhí)行。比如下面這個批量執(zhí)行 ?insert?。

int[] result = jdbcTemplate.executeBatch(new String[] {
    "insert into `test_user` values (11, 'david', 26, now())",
    "insert into `test_user` values (12, 'kevin', 26, now())"
});

執(zhí)行結果

[1, 1]

參數化 SQL 批量執(zhí)行?

批量執(zhí)行帶參的 SQL

String querySql = "insert into `test_user` values (?,?,?,?)";
Object[][] queryArg = new Object[][] {
    new Object[] { 20, "david", 26, new Date() },
    new Object[] { 22, "kevin", 26, new Date() }
};

int[] result = jdbcTemplate.executeBatch(querySql, queryArg);

執(zhí)行結果

[1, 1]

Map數組作為批量參數?

批量執(zhí)行帶參的 SQL，使用 Map 作為入參

String querySql = "update test_user set name = :name where id = :id";
Map<String, Object> record1 = new HashMap<>();
record1.put("name", "jack");
record1.put("id", 1);

Map<String, Object> record2 = new HashMap<>();
record2.put("name", "steve");
record2.put("id", 2);

Map<String, Object>[] queryArg = new Map[] { record1, record2 };
int[] result = jdbcTemplate.executeBatch(querySql, queryArg);

執(zhí)行結果

[1, 1]

BatchPreparedStatementSetter?

使用 BatchPreparedStatementSetter 接口進行參數批量設置

String querySql = "delete from test_user where id = ?";
Object[][] queryArg = new Object[][] { new Object[] { 1 }, new Object[] { 2 } };

BatchPreparedStatementSetter batchSetter = new BatchPreparedStatementSetter() {
    public void setValues(PreparedStatement ps, int i) throws SQLException {
        ps.setObject(1, queryArg[i][0]);
    }

    public int getBatchSize() {
        return queryArg.length;
    }
};

int[] result = jdbcTemplate.executeBatch(querySql, batchSetter);

執(zhí)行結果

[1, 1]