JDBC improvements and convenience method suggestions

Mark Rotteveel mark at lawinegevaar.nl
Thu Jul 12 18:25:41 UTC 2018


On 12-7-2018 17:43, Lukas Eder 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.

I'm slightly hesitant about introducing this new interface. Making those 
interfaces directly implement `AutoCloseable` with default methods 
calling `free()` may lead to a little bit of code duplication, but 
removes the need for an additional interface whose only real function is 
to perform as a bridge between `close()` and `free()`.

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

I think that this should require that the provided parameters should 
include **all** parameters. So if there are three parameters, then 
`set(..)` must provide 3 parameters. If you provide 2 parameters, that 
should result in an SQLException.

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

I like this.

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

Why those choice for an array and not for example a List?

> 5. Let ResultSet extends Iterable<Object[]>
> https://github.com/lukaseder/openjdk/commit/7daefde1134099baa1923c3f120789069c5f6917

I'm not sure I like that UncheckedSQLException, especially not as a 
nested class.

> 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

A `ResultSet` is not a `List`, nor a `Collection` (especially not for a 
`TYPE_FORWARD_ONLY`), we should not try to bolt on that such behavior as 
that would make use of `ResultSet` rather hard to implement correctly 
and efficiently, especially some of the other requirements for 
`Collection`, for example `equals`, `hashCode`, `contains`, etc.

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

That might indicate that instead of a `Iterator`, we should add 
`stream()`, as a `Stream` does implement `Closeable` and would achieve 
this as well.

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

Never used this, so I can't really judge if this is useful or not

> 7. Add Connection.transaction() overloads for functional transaction usage
> https://github.com/lukaseder/openjdk/commit/12c9591ca9066e676001fd6e67e18b7d444d0a32

I don't really like the idea of nested classes in the API. I don't 
really see the need for `TransactionRunnable` / `SQLRunnable` and 
`TransactionalSupplier` / `SQLSupplier`. The only advantage seems to be 
that you can use () -> .. instead of having an explicit lambda argument.

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

This default implementation assumes savepoint support, but savepoints 
are an optional JDBC feature, we should not rely on optional features in 
default implementations. I would suggest that a default implementation 
should not use savepoints, but instead commit a current transaction (if 
not in auto commit), and commit/rollback at the end.

For the (nested) savepoints, then maybe a separate variant using 
savepoints would be warranted (eg `savepoint(...)` or `inSavepoint(...)` 
and `inSavepoint(String name, ...).

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

I actually often don't directly interact with a `DataSource` directly, 
but I can see the value of such a method as a convenience feature.

-- 
Mark Rotteveel


More information about the jdbc-spec-discuss mailing list