google
`mug-safesql` - `PreparedStatement.addBatch()` support?
is there support (or a suggestion for how to implement) JDBC batching with SafeSql?
That's an intruiguing question!
For the moment I can't think of a good API to directly integrate batching. But for now, at least you can still use the SQL template to create dynamic SQL for batching, and then use the JDBC API directly to send batches:
// Create SafeSql, with the right identifier and subqueries, but with dummy jdbc parameters
SafeSql sql = SafeSql.of("select * from `{table}` where id = {id}", table, 0);
try (PreparedStatement stmt = sql.prepareStatement(conn)) {
for (id : ids) {
stmt.setInt(1, id);
stmt.addBatch();
}
stmt.executeBatch();
}
It's not as compile-time safe as single statements though.
you already have:
public static Template<Integer> prepareToUpdate(
Connection connection, @CompileTimeConstant String template) {
return prepare(connection, template, PreparedStatement::executeUpdate);
}
It could use PreparedStatement::addBatch instead and then perhaps return a sub-interface of Template that would allow 'executing' the batch at some point?
Good call!
I've added prepareToBatch(). It returns a Template<PreparedStatement> upon each call. Usage pattern will be like:
Template<PreparedStatement> insertUser = sql.prepareToBatch(connection, ...);
Set<PreparedStatement> batches = new HashSet<>();
for (...) {
batches.add(insertUser.with(...));
}
for (var batch : batches) {
batch.executeBatch();
}
You need a Set in case there are unequal identifier or subquery parameters passed to with(). If you only have JDBC parameters, the same PreparedStatement instance will be returned from with(). But using a Set is probably safest and easy.
cheers. btw poking around in the source, should the Template be closing the cached PreparedStatements?
The Template interface isn't an AutoCloseable.
The hope is that the caller just calls Connection.close() in a try-with-resources and all the statements will be closed.
Will that pattern work for your use case?
For batching, you could still perform a tidy batch with:
void insertUsers(Connection connection, List<User> users) throws SQLException {
checkArgument(users.size() > 0);
var insertUser = SafeSql.prepareToBatch(connection, ...);
try (PreparedStatement batch =
users.stream().map(u -> insertUser.with(...)).distinct().collect(toOnlyElement())) {
batch.executeBatch();
}
}
toOnlyElement() is from Guava if you know it's only a single batch, as is usually the case.
This way SafeSql remains least intrusive, dealing only with the SQL templating stuff; and you still interact with the JDBC API for executing batch, and for closing the JDBC resources. No need to learn yet another third-party API that merely delegates to JDBC. Every API should pull its weight.
yeah, that's what i'm doing at the moment. i guess the only other thing i'd point out is that with:
PreparedStatement stmt = cached.computeIfAbsent(sql.toString(), s -> {
<...>
});
try {
return action.apply(sql.setParameters(stmt));
} catch (SQLException e) {
throw new UncheckedSqlException(e);
}
if something gets thrown from the try block then the PreparedStatement ends up being 'orphaned'. though i guess that's a problem for the existing prepareToUupdate etc too.
This isn't using SafeSql, right?
Imho, if you will be closing the connection soon-ish (like for the current request), there is no need to worry about unclosed temporary statements upon exceptions. They don't happen often and statements are closed along with the connection anyways.
Speculatively, if closing a statement could involve interacting with the DB server, it might even be more efficient to close them in connection.close() because it'd be a batch close operation.
no, that's code from within prepare in SafeSql: https://github.com/google/mug/blob/79f2f9d22bcde5f27e513348a7128d05ce22e328/mug-safesql/src/main/java/com/google/mu/safesql/SafeSql.java#L1875
Oh I see.
Yeah, as suggested in the javadoc, it's expected that the caller manages these jdbc resources through the connection.
This is in compliance with JDBC spec:
Releases this Connection object's database and JDBC resources immediately... All Statement objects created by this Connection object are also closed.
It's for use cases where you open a connection -> do a bunch of things -> close it.
Note that the pattern works even with connection pools, as all spec-compliant connection pools must close the open statements and result sets when Connection.close() is called before returning the connection to the pool.