About Lukas Eder

Lukas is a Java and SQL enthusiast developer. He created the Data Geekery GmbH. He is the creator of jOOQ, a comprehensive SQL library for Java, and he is blogging mostly about these three topics: Java, SQL and jOOQ.

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 interactions
  • PreparedStatement: the object that lets you execute a statement
  • ResultSet: 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:

Hacking JDBC. Image copyright information on this page

Hacking JDBC. Image copyright information on this page

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…

we have been hacking JDBC, so you don’t have to

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you two of our best selling eBooks for FREE!

JPA Mini Book

Learn how to leverage the power of JPA in order to create robust and flexible Java applications. With this Mini Book, you will get introduced to JPA and smoothly transition to more advanced concepts.

JVM Troubleshooting Guide

The Java virtual machine is really the foundation of any Java EE platform. Learn how to master it with this advanced guide!

Given email address is already subscribed, thank you!
Oops. Something went wrong. Please try again later.
Please provide a valid email address.
Thank you, your sign-up request was successful! Please check your e-mail inbox.
Please complete the CAPTCHA.
Please fill in the required fields.

2 Responses to "We’re Hacking JDBC, so You Don’t Have To"

  1. SomeUser says:

    As usual, the main (only ?) purpose of this article is to promote Jooq.

    • Lukas Eder says:

      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 great writer yourself – help making this great platform what you want it to be by writing your own blog posts! We’re looking forward to your awesome content!

      Cheers
      Lukas

Leave a Reply


− six = 1



Java Code Geeks and all content copyright © 2010-2014, Exelixis Media Ltd | Terms of Use | Privacy Policy | Contact
All trademarks and registered trademarks appearing on Java Code Geeks are the property of their respective owners.
Java is a trademark or registered trademark of Oracle Corporation in the United States and other countries.
Java Code Geeks is not connected to Oracle Corporation and is not sponsored by Oracle Corporation.
Do you want to know how to develop your skillset and become a ...
Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you two of our best selling eBooks for FREE!

Get ready to Rock!
You can download the complementary eBooks using the links below:
Close