JDBC improvements and convenience method suggestions

[Lukas Eder]

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