JDBC improvements and convenience method suggestions
Lance Andersen
lance.andersen at oracle.com
Thu Nov 7 14:25:11 UTC 2019
Hi Lukas,
I will be looking at an MR update shortly. I do have your previous email and need to go back through it to refresh myself
> On Nov 7, 2019, at 8:51 AM, Lukas Eder <lukas.eder at gmail.com> wrote:
>
> 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
>>
<http://oracle.com/us/design/oracle-email-sig-198324.gif>
<http://oracle.com/us/design/oracle-email-sig-198324.gif> <http://oracle.com/us/design/oracle-email-sig-198324.gif>
<http://oracle.com/us/design/oracle-email-sig-198324.gif>Lance Andersen| Principal Member of Technical Staff | +1.781.442.2037
Oracle Java Engineering
1 Network Drive
Burlington, MA 01803
Lance.Andersen at oracle.com <mailto:Lance.Andersen at oracle.com>
More information about the jdbc-spec-discuss
mailing list