@Query support for statement reuse

[JoshStark]

I've had a look at the documentation around the declarative @Query annotation but couldn't find a definitive answer, so figured I'd ask: Is it possible for the @Query annotation to support reusing the underlying prepared statement when querying for a reasonable data set with a list of complex filter criteria?

I have a use-case where a client service requests a list of rows from a table using more than one query field, meaning I can't necessarily make use of the IN clause (if I were querying by a list of IDs).

For example:

@Query("SELECT * FROM MyTable WHERE field1 = :field1 AND field2 = :field2")
MyEntity findByField1AndField2(@Param("field1") String field1, @Param("field2) String field2);

Given a list of field1 and field2 values, I would need to iterate over each element and call this query multiple times, aggregating the results.

List<MyEntity> results = new ArrayList<>();
for (var criterion : queryCriteria) {
    results.add(repository.findByField1AndField2(criterion.getField1(), criterion.getField2());
}

To improve performance in native JDBC, I could reuse the prepared statement for each query:

try(var statement = connection.prepareStatement("SELECT * FROM MyTable WHERE field1 = ? AND field2 = ?")) {

    for (var criterion : queryCriteria) {

        statement.setString(1, criterion.getField1());
       statement.setString(2, criterion.getField2());

        var resultSet = statement.executeQuery();
        while (resultSet.next()) {
            results.add(mapRow(resultSet));
        }
    }
}

I'm looking to see if @Query can support something like this by defining a list return type and an attribute or extra annotation to tell spring-data-jdbc to reuse the statement it prepares for the query. For example, something like:

@ReusableStatement
@Query("SELECT * FROM MyTable WHERE field1 = :field1 AND field2 = :field2")
List<MyEntity> findByField1AndField2(@Param("field1") String field1, @Param("field2) String field2);

Apologies for the relative vagueness here; I'm working on a project I can't share publicly so I've had to simplify it.

[mp911de]

In short: No, there's no such mechanism.

We have a couple of abstraction layers on top of the JDBC API. The most significant part is that we operate on top of JdbcTemplate that works on top of DataSource. As each statement could run on a different Connection, we do not have means to cache prepared statements.

On second thought, prepared statement caching would be a neat extension to JDBC connection pools. JdbcTemplate disposes JDBC objects such as PreparedStatement and ResultSet after its usage to free resources.

For the time being, you could hook into JdbcTemplate with a subclass where you hook into PreparedStatement creation and its disposal to cache these.

[schauder]

With yet another angle: wasn't there support in JdbcTemplate and at least some databases for queries like

select ... where (a, b) in ((:a1, :b1), ..... (:an, bn))

If so that should be usable in Spring Data JDBC as well.

[schauder]

Found it: https://stackoverflow.com/questions/23305553/how-to-bind-a-list-of-tuples-using-spring-jdbctemplate

[JoshStark]

Ooh! Thanks for digging that out! I’ll have a play tomorrow and see if that will do what I want.

[JoshStark]

In short: No, there's no such mechanism.

We have a couple of abstraction layers on top of the JDBC API. The most significant part is that we operate on top of JdbcTemplate that works on top of DataSource. As each statement could run on a different Connection, we do not have means to cache prepared statements.

On second thought, prepared statement caching would be a neat extension to JDBC connection pools. JdbcTemplate disposes JDBC objects such as PreparedStatement and ResultSet after its usage to free resources.

For the time being, you could hook into JdbcTemplate with a subclass where you hook into PreparedStatement creation and its disposal to cache these.

I figured that may be the case. What I asked for is quite niche, but I appreciate your reply! For specific use-cases like this I’m quite happy to use a concrete implementation with a JdbcTemplate instead.