JDBC improvements and convenience method suggestions

[Mark Rotteveel]

On 12-7-2018 17:43, Lukas Eder wrote:
> 3. Add Connection.executeXYZ() methods
> https://github.com/lukaseder/openjdk/commit/e77db3e28b24d24e220a215c396c10bf8adf2d30
> 
> 
> In many cases, there is not really a need for keeping around intermediary
> Statement or PreparedStatement references. In a recent non-representative
> poll I've done on twitter (182 answers), I've had about equal numbers of
> answers among people:
> 
> - caching and reusing prepared statements
> - creating new statements all the time (this includes people who don't use
> JDBC directly, most ORMs don't keep around open statements)
> 
> Poll here:
> https://twitter.com/lukaseder/status/974229231076077568
> 
> For those people who do not cache prepared statements, I think being able
> to execute SQL directly on the JDBC connection would be very helpful. E.g.

As Michael Rasmussen pointed out on Twitter in 
https://twitter.com/jmichaelras/status/1017438847309926401, the 
`executeQuery` variants will not work because the result set will be 
closed by closing the statement. Instead I think that it should be 
implemented as

     /**
      * Create a statement and execute some SQL on it.
      */
     default ResultSet executeQuery(String sql) throws SQLException {
         Statement s = createStatement();
         try {
             s.closeOnCompletion()
             return s.executeQuery(sql);
         } catch (SQLException e) {
             // Safeguard against lack of closeOnCompletion support
             try {
                 s.close();
             } catch (SQLException suppressed) {
                 e.addSuppressed(suppressed);
             }
             throw e;
         }
     }

(+ similar for` executeQuery(String sql, Object... binds)`)

However, this could still leak a statement resource if 
`closeOnCompletion` is ignored without actual support. Maybe this is an 
indication that instead it should be a more functional construct like:

default void executeQuery(String sql, SQLConsumer<ResultSet> rsConsumer) {
     try (Statement s = createStatement();
         ResultSet rs = s.executeQuery(sql)) {
         rsConsumer.consume(rs);
     }
}

But that immediately raises the question whether or not we should 
introduce similar constructs for the executeXXX methods on Statements, etc.

Mark
-- 
Mark Rotteveel