We’re Hacking JDBC, so You Don’t Have To
We love working with JDBC
Said no one. Ever.
On a more serious note, JDBC is actually a very awesome API, if you think about it. It is probably also one of the very reasons Java has become the popular platform it is today. Before the JDK 1.1, and before ODBC (and that’s a very long time ago) it was hard to imagine any platform that would standardise database access at all. Heck, SQL itself was hardly even standardised at the time and along came Java with JDBC, a simple API with only few items that you have to know of in every day work:
Connection
: the object that models all your DB interactionsPreparedStatement
: the object that lets you execute a statementResultSet
: the object that lets you fetch data from the database
That’s it!
Back to reality
That was the theory. In practice, enterprise software operating on top of JDBC quickly evolved towards this:
JDBC is one of the last resorts for Java developers, where they can feel like real hackers, hacking this very stateful, very verbose, very arcane API in many ways. Pretty much everyone operating on JDBC will implement wrappers around the API to prevent at least:
- Common syntax errors
- Bind variable index mismatches
- Dynamic SQL construction
- Edge cases around the usage LOBs
- Resource handling and closing
- Array and UDT management
- Stored procedure abstraction
… and so much more.
So while everyone is doing the above infrastructure work, they’re not working on their business logic. And pretty much everyone does these things, when working with JDBC. Hibernate and JPA do not have most these problems, but they’re not SQL APIs any longer, either.
Here are a couple of examples that we have been solving inside of jOOQ, so you don’t have to:
How to fetch generated keys in some databases
case DERBY: case H2: case MARIADB: case MYSQL: { try { listener.executeStart(ctx); result = ctx.statement().executeUpdate(); ctx.rows(result); listener.executeEnd(ctx); } // Yes. Not all warnings may have been consumed yet finally { consumeWarnings(ctx, listener); } // Yep. Should be as simple as this. But it isn't. rs = ctx.statement().getGeneratedKeys(); try { List<Object> list = new ArrayList<Object>(); // Some JDBC drivers seem to illegally return null // from getGeneratedKeys() sometimes if (rs != null) { while (rs.next()) { list.add(rs.getObject(1)); } } // Because most JDBC drivers cannot fetch all // columns, only identity columns selectReturning(ctx.configuration(), list.toArray()); return result; } finally { JDBCUtils.safeClose(rs); } }
How to handle BigInteger and BigDecimal
else if (type == BigInteger.class) { // The SQLite JDBC driver doesn't support BigDecimals if (ctx.configuration().dialect() == SQLDialect.SQLITE) { return Convert.convert(rs.getString(index), (Class) BigInteger.class); } else { BigDecimal result = rs.getBigDecimal(index); return (T) (result == null ? null : result.toBigInteger()); } } else if (type == BigDecimal.class) { // The SQLite JDBC driver doesn't support BigDecimals if (ctx.configuration().dialect() == SQLDialect.SQLITE) { return Convert.convert(rs.getString(index), (Class) BigDecimal.class); } else { return (T) rs.getBigDecimal(index); } }
How to fetch all exceptions from SQL Server
switch (configuration.dialect().family()) { case SQLSERVER: consumeLoop: for (;;) try { if (!stmt.getMoreResults() && stmt.getUpdateCount() == -1) break consumeLoop; } catch (SQLException e) { previous.setNextException(e); previous = e; } }
Convinced?
This is nasty code. And we have more examples of nasty code here, or in our source code.
All of these examples show that when working with JDBC, you’ll write code that you don’t want to / shouldn’t have to write in your application. This is why…
Reference: | We’re Hacking JDBC, so You Don’t Have To from our JCG partner Lukas Eder at the JAVA, SQL, AND JOOQ blog. |
As usual, the main (only ?) purpose of this article is to promote Jooq.
Thank you very much for your feedback. While we might have 1-2 occasional references to jOOQ in our blog posts, I would like to point out that these articles are published on our own blog over at http://blog.jooq.org, and then syndicated over to DZone, JCG and other syndicators. This one is obviously a promotional post from our blog. I suspect the curators at JCG still found it interesting enough for it to be syndicated. We’re sorry that you perceived this as overly promotional. On the other hand, we encourage you to be not only a great reader, but also a… Read more »