No common interface to java.sql.ResultSet, CallableStatement, SQLInput

Question

This is the situation

In jOOQ, there is a lot of need for abstraction over JDBC. I want the jOOQ client code to be unaware of the fact, that some data is retrieved from a simple ResultSet, some data is retrieved from SQLInput (for UDTs), or from CallableStatements (for stored procedures/functions). Hence, I want to add abstraction over these JDBC types:

java.sql.ResultSet
java.sql.CallableStatement
java.sql.SQLInput
java.sql.SQLOutput

Now they all work pretty much the same way. They usually have a get and set method for every data type in java.sql.Types. For example, they ship with methods like

BigDecimal getBigDecimal(int index);
int getInt(int index);

And they all have methods like

boolean wasNull();

The problem

Unfortunately, these JDBC interfaces don't extend a single common interface making lives easier for those who want to write generic JDBC code like this snippet here (just an example to support my question):

// As JDBC has no support for BigInteger types directly,
// read a BigDecimal and transform it to a BigInteger
BigDecimal result = null;

if (source instanceof ResultSet) {
    result = ((ResultSet) source).getBigDecimal(index);
}
else if (source instanceof CallableStatement) {
    result = ((CallableStatement) source).getBigDecimal(index);
}
else if (source instanceof SQLInput) {
    result = ((SQLInput) source).readBigDecimal();
}

return result == null ? null : result.toBigInteger();

The above code needs to be written for all three of them, ResultSet, CallableStatement, SQLInput. And there are lots of similar examples

My question is

• Does anyone know a JDBC extension library that solves this problem elegantly?
• Or should I write a simple wrapper class (or adapter) for all of these types myself?
• Or would you just accept that fact and keep duplicating internal library code?

Which solution do you prefer and why? Thanks for any feedback

Answer 1

• Does anyone know a JDBC extension library that solves this problem elegantly?

No, someone has to invent it yet.

---

• Or should I write a simple wrapper class (or adapter) for all of these types myself?

I'd definitely go for this. Start with a common wrapper interface.

public interface DataProvider {
    public BigInteger getBigInteger(int columnIndex);
    // ...
}

Let all concrete wrappers implement it.

public class ResultSetDataProvider implements DataProvider {
    private ResultSet resultSet;

    public ResultSetDataProvider(ResultSet resultSet) {
        this.resultSet = resultSet;
    }

    public BigInteger getBigInteger(int columnIndex) {
        BigDecimal bigDecimal = resultSet.getBigDecimal(columnIndex);
        return bigDecimal != null ? bigDecimal.toBigInteger() : null;
    }

    // ...
}

And use it instead.

try {
    // Acquire ResultSet.
    DataProvider dataProvider = new ResultSetDataProvider(resultSet);
    // Process DataProvider.
} finally {
    // Close ResultSet.
}

---

• Or would you just accept that fact and keep duplicating internal library code?

No, I wouldn't. Keep your code DRY.

Answer 2

Personally, I wouldn't use such a thing. I don't see that you're making my life much easier with this abstraction.

I don't think there's a reason for SQL abstractions to leak out from a persistence tier. I make the call, map into objects, and close the SQL abstractions. You sound like you want them to be sticking around, which is a bad idea.

I think the Spring folks have made using JDBC about as easy as it can be. I could be wrong, but I don't see a reason to go down the path you're suggesting.

If I look at the javadocs for SQLInput, I see this:

This interface, used only for custom mapping, is used by the driver behind the scenes, and a programmer never directly invokes SQLInput methods.

I'm not sure why you see it necessary to expose this interface.

As for ResultSet and CallableStatement (or any Statement, for that matter), those can ultimately return one or more ResultSets to return query results. I'd rather see an abstraction around that. I believe you're muddying the waters by exposing the others. I wouldn't recommend it.

Perhaps the fact that it's never been done is another indication that it shouldn't be done. But you're welcome to do it and see if the marketplace votes you a winner.

Answer 3

1. Not sure of an extension for JDBC.
2. A wrapper class or the visitor pattern might be useful depending on the rest of your design.
3. What about extending each one and making them implement the same interface that has the same method signatures to get the data you want.