JDBC improvements and convenience method suggestions

[Lukas Eder]

Dear group,

I'm trying my luck again with this, given that 1.5 years later:

1. ADBA has been stopped (so, less effort in that area)
2. Text blocks have shipped (so, more value in using JDBC directly)

I think that text blocks are a killer feature for direct JDBC usage, if
only JDBC had a bit more convenience as I had suggested in my original
mail. Is there any planned effort around such an improvement of JDBC in the
near future?

Thanks,
Lukas

On Thu, Jul 12, 2018 at 5:43 PM Lukas Eder <lukas.eder at gmail.com> wrote:

> 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
>