JDBC improvements and convenience method suggestions

Hello, Some time has passed since I had suggested some JDBC improvements and Lance was so kind to offer looking into concrete suggestions that I might have: http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2017-November/000243.html I have finally found some time to actually do that. I have forked an inofficial git clone of the OpenJDK repository and committed my suggestions directly there: https://github.com/lukaseder/openjdk/tree/jdk/jdk/src/java.sql/share/classes There are 8 suggestions in total: 1. Add java.sql.Freeable and let Array, Blob, Clob, and SQLXML extend it https://github.com/lukaseder/openjdk/commit/acc17baa91a05525951f27e227731ac1206ee9e0 One thing that has been bothering me a bit in the past is the fact that these 4 types Array, Blob, Clob, and SQLXML have received new free() methods in Java 6 / JDBC 4.0, which I believe should have been called close() instead, and then in Java 7, the types should have extended AutoCloseable. However, it is not too late to retrofit this by adding a new Freeable API (this is optional, prevents repeating the fix) and letting Freeable delegate to AutoCloseable. On the user side, this would be possible: try (Clob clob = connection.createClob()) { ... } I guess this doesn't need any further explanation. 2. Add PreparedStatement.set(Object...) https://github.com/lukaseder/openjdk/commit/2bf968dca742989309379c8d2550979baf07c0d5 One of the bigger pain points in JDBC is the fact that indexed parameters are 1 based and have to be incremented manually on a per bind parameter basis. It would be *much* more convenient in many many cases, if there was a method that allows for passing a vararg of arbitrary values, simply delegating to setObject(), i.e. try (PreparedStatement s = connection.prepareStatement( "INSERT INTO t VALUES (?, ?, ?)")) { s.set(1, 2, 3); s.executeUpdate(); } For most types, this works just fine and if someone needs a special data type that would require one of the other setObject() overloads, they can still revert to the status quo for that particular statement. I think this would greatly help! 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. // Static statement connection.executeUpdate("INSERT INTO t VALUES (1, 2, 3)"); Instead of try (Statement s = connection.createStatement()) { s.executeUpdate("INSERT INTO t VALUES (1, 2, 3)"); } Or this // PreparedStatement connection.executeUpdate("INSERT INTO t VALUES (?, ?, ?)", 1, 2, 3); Instead of try (Statement s = connection.prepareStatement( "INSERT INTO t VALUES (?, ?, ?)")) { // New suggested method in section 2 s.set(1, 2, 3); // Today's approach s.setInt(1, 1); s.setInt(2, 2); s.setInt(3, 3); s.executeUpdate(); } I think the value in reduced boiler plate is very obvious. A hint in the Javadoc indicating that this does not cache the statements should suffice for those who are more performance aware / who can actually profit from caching the statements. 4. Add Result.get():Object[] https://github.com/lukaseder/openjdk/commit/03a63116b935977b229b8bee4d9b526f71923eee Similar to PreparedStatement.set(Object[]), being able to fetch an entire row from a ResultSet is very useful to tools that process result sets generically. The simplest possible type is Object[], but a new java.sql.Row type would be reasonable as well (ADBA is going to offer such a type). I think this is self explanatory. 5. Let ResultSet extends Iterable<Object[]> https://github.com/lukaseder/openjdk/commit/7daefde1134099baa1923c3f120789069c5f6917 Once ResultSet exposes entire rows as types, that type could also be offered through ResultSet extending Iterable<ThatType>. In my example, it's Iterable<Object[]>, but a new java.sql.Row type would be even better. I'm suggesting Iterable here instead of Iterator for 2 reasons: - It's the more appropriate type for rewindable / scroll sensitive result sets. For instance, every time ResultSet.iterator() is called, this could go back to ResultSet.beforeFirst(). To be defined. - ResultSet.next() already returns boolean, so ResultSet cannot extend Iterator, whose next() method returns T The fact that JDBC's ResultSet is so disconnected from the java.util collection types is a big issue for many JDBC users. Making it Iterable<T> or even List<T> (because technically, it *is* a list) would greatly help working with JDBC. I'm not suggesting List<T> because that would imply that: - Wasteful operations could be done accidentally, e.g. jumping around the list randomly - JDBC drivers would have to buffer the results But Iterable is definitely useful. Imagine, with the previous improvements to do this: try (ResultSet rs = connection.executeQuery("SELECT * FROM t")) { for (Object[] row : rs) { System.out.println(row[0] + ":" + row[1]); } } Makes me wish for a for-with-resources as well that iterates over (Iterable<T> & AutoCloseable) and closes the object after (un)successful iteration :-) 6. Add SQLInput.getConnection() and SQLOutput.getConnection() https://github.com/lukaseder/openjdk/commit/39aeb8c0dbf86b1fe0a4df847bca22127047667c This is something I've been missing every time when I worked with Oracle OBJECT types. Binding them through JDBC directly is non trivial, especially when nesting OBJECT and TABLE types. Imagine writing an OBJECT type to SQLOutput and having to create java.sql.Array types from within SQLData. There is no way to access the JDBC Connection which is needed to create arrays either through standard JDBC - Connection.createArray(...) Or through ojdbc - OracleConnection.createARRAY(...) The workaround is nasty: Store the JDBC Connection in some ThreadLocal or other utility that is statically accessible from a SQLData instance, and access it from there. It would be much better if SQLInput and SQLOutput would offer these two methods. 7. Add Connection.transaction() overloads for functional transaction usage https://github.com/lukaseder/openjdk/commit/12c9591ca9066e676001fd6e67e18b7d444d0a32 This is something that people have come to appreciate a lot in jOOQ: A functional transaction API that builds on top of the existing procedural one. I think other libraries have similar utilities. The user code could look like this: connection.transaction(() -> { connection.executeUpdate("INSERT INTO .."); // Using suggested methods connection.executeUpdate("INSERT INTO .."); }); How does it work? - If the above lambda completes normally: Commit - If the above lambda throws an exception: Roll back This can go a step further: Nested transactions with implicit breakpoints // Implicit transaction start here connection.transaction(() -> { connection.executeUpdate("INSERT INTO .."); // Using suggested methods try { // Implicit savepoint here connection.transaction(() -> { connection.executeUpdate("INSERT INTO .."); }); } // In case of exception implicit rollback to savepoint catch (SQLException ignore) {} }); // In case of normal completion, the first insert will be committed, // the second one might have been rolled back My implementation suggestion on GitHub includes: - 4 new functional interfaces that allow for passing different types of lambdas to the transaction() method: o () -> {} // void compatible o (Connection c) -> {} o () -> someResult o (Connection c) -> someResult - A draft implementation that uses a hack to store the savepoints in some connection state. A more thorough solution would be needed of course, but I think it nicely illustrates the use-case. 8. Add DataSource.connect() methods https://github.com/lukaseder/openjdk/commit/9a2eebea7869a3e9e81347a965bd9e65b41bc1bd Another place where lambdas could be useful is the DataSource type. When obtaining a DataSource from somewhere, the procedural approach of getting a connection and closing it feels a bit weird, even years after using it. I think this would be more intuitive: datasource.connect(c -> { c.executeUpdate("INSERT INTO t VALUES (?, ?, ?)", 1, 2, 3); c.executeUpdate("INSERT INTO u VALUES (?, ?)", "a", "b"); }); This is much shorter than today's: try (Connection c = datasource.getConnection(); PreparedStatement s1 = c.prepareStatement( "INSERT INTO t VALUES (?, ?, ?)"); PreparedStatement s2 = c.prepareStatement( "INSERT INTO u VALUES (?, ?)")) { s1.setInt(1, 1); s1.setInt(2, 2); s1.setInt(3, 3); s1.executeUpdate(); s2.setString(1, "a"); s2.setString(2, "b"); s1.executeUpdate(); } I think that's an improvement, isn't it? This is all I can think of right now. Looking forward to your feedback, Lukas