You know JDBC, right? It’s that really easy, concise API that we love to use to work with virtually any database, relational or not. It has essentially three types that you need to care about:
- Connection
- Statement (and its subtypes)
- ResultSet
All the other types some sort of utilities.
Now, with the above three, we can do really nice and lean Java/SQL coding as follows:
try (Connection c = datasource.getConnection();
Statement s = c.createStatement();
ResultSet r = s.executeQuery("SELECT 'hello'")
) {
while (r.next())
System.out.println(r.getString(1));
}
Output:
hello
OK? Super easy.
Unless…
Unless you want to write generic JDBC code, because you don’t know what the SQL string is. It could be a SELECT statement. It could be and UPDATE. It could be DDL. It could be a statement batch (several statements). It could call triggers and stored procedures, which again produce nice things like warnings, exceptions, update counts, and additional result sets.
You know, the sort of thing that might come flying in to a generic utility method like jOOQ’s ResultQuery.fetchMany().
(Don’t think this couldn’t happen to you as well. SQL Server triggers are really mean things!)
For this, let’s consider the correct way to execute the following simple statement batch that works wonderfully in SQL Server:
raiserror('e0', 0, 2, 3);
create table t(i int);
raiserror('e1', 5, 2, 3);
insert into t values (1);
raiserror('e2', 10, 2, 3);
insert into t values (2);
raiserror('e3', 15, 2, 3);
select * from t;
drop table t;
raiserror('e4', 16, 2, 3);
The result is:
And obviously
For your convenience, I have pre-formatted the above String into a Java String variable, which is already the first problem, because Java STILL doesn’t have multi-line strings (gaah):
String sql =
"\n raiserror('e0', 0, 2, 3);"
+ "\n create table t(i int);"
+ "\n raiserror('e1', 5, 2, 3);"
+ "\n insert into t values (1);"
+ "\n raiserror('e2', 10, 2, 3);"
+ "\n insert into t values (2);"
+ "\n raiserror('e3', 15, 2, 3);"
+ "\n select * from t;"
+ "\n drop table t;"
+ "\n raiserror('e4', 16, 2, 3);";
Now see, we might be inclined to just copy paste some JDBC snippet off some website (e.g. this blog, and take its first snippet) and execute it as such:
try (
Statement s = c.createStatement();
ResultSet r = s.executeQuery(sql)
) {
while (r.next())
System.out.println(r.getString(1));
}
Yeah. What’ll happen if we do this?
Rats:
com.microsoft.sqlserver.jdbc.SQLServerException: e3 at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1547) at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:857) at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:757) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2689) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:224) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:204) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:659) at SQLServer.main(SQLServer.java:80)
e3? What on earth? So what happened with my statement batch? Did it execute? Only until the middle? Or did I get to the end as well?
OK, quite obviously, we have to do this more carefully. We cannot use Statement.executeQuery() here, because we don’t know whether we’ll get a result set. In fact, we got an exception, but not the first one.
Let’s try something else. Let’s try this:
try (Statement s = c.createStatement()) {
System.out.println(s.execute(sql));
}
That just yields:
false
Okaaay, did anything execute in the database at all? No more exceptions… Let me have a look at the SQL Server Profiler…
Nope, the entire batch got executed. (Could’ve just removed the DROP TABLE statement and checked the contents of table T in SQL Server Management Studio, of course).
Huh, quite a different result, depending on what method we’re calling. Does that scare you? Does your ORM get this right? (jOOQ didn’t but this is now fixed).
OK, let’s read the Javadoc on Statement.execute()
It says:
Executes the given SQL statement, which may return multiple results. In some (uncommon) situations, a single SQL statement may return multiple result sets and/or update counts. Normally you can ignore this unless you are (1) executing a stored procedure that you know may return multiple results or (2) you are dynamically executing an unknown SQL string.
The execute method executes an SQL statement and indicates the form of the first result. You must then use the methods getResultSet or getUpdateCount to retrieve the result, and getMoreResults to move to any subsequent result(s).
Huh, OK. Statement.getResultSet() and getUpdateCount() must be used, and then getMoreResults()
The getMoreResults() method also has this interesting bit of information:
There are no more results when the following is true:
// stmt is a Statement object
((stmt.getMoreResults() == false) && (stmt.getUpdateCount() == -1))
Interesting. -1. I guess we can be very happy that at least it’s not returning null or a punch in your face.
So, let’s try this again:
- We first have to call execute()
- If it’s true, we fetch getResultSet()
- If it’s false, we check getUpdateCount()
- If that was -1, we can stop
Or, in code:
fetchLoop:
for (int i = 0, updateCount = 0; i < 256; i++) {
boolean result = (i == 0)
? s.execute(sql)
: s.getMoreResults();
if (result)
try (ResultSet rs = s.getResultSet()) {
System.out.println("Result :");
while (rs.next())
System.out.println(" " + rs.getString(1));
}
else if ((updateCount = s.getUpdateCount()) != -1)
System.out.println("Update Count: " + updateCount);
else
break fetchLoop;
}
Beautiful! Some remarks:
- Note how the loop stops after 256 iterations. Never trust these infinite streaming APIs, there’s always a bug somewhere, trust me
- The boolean value return from
Statement.execute()andStatement.getMoreResults()is the same. We can assign it to a variable inside the loop and call execute only on the first iteration - If true, fetch the result set
- If false, check the update count
- If that was -1, stop
Run time!
Update Count: 0 Update Count: 1 Update Count: 1 com.microsoft.sqlserver.jdbc.SQLServerException: e3 at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1547) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getMoreResults(SQLServerStatement.java:1270) at SQLServer.main(SQLServer.java:83)
Crap. But did it execute completely? Yes it did, but we didn’t get that sweet result set after e3, because of that exception. But at least, we now got 3 update counts. But wait a second, why didn’t we get e0, e1, and e2?
AHA, they’re warnings, not exceptions. Funky SQL Server decided that everything below some severity level is a warning. Whatever.
Anyway, let’s fetch those warnings as well!
fetchLoop:
for (int i = 0, updateCount = 0; i < 256; i++) {
boolean result = (i == 0)
? s.execute(sql)
: s.getMoreResults();
// Warnings here
SQLWarning w = s.getWarnings();
for (int j = 0; j < 255 && w != null; j++) {
System.out.println("Warning : " + w.getMessage());
w = w.getNextWarning();
}
// Don't forget this
s.clearWarnings();
if (result)
try (ResultSet rs = s.getResultSet()) {
System.out.println("Result :");
while (rs.next())
System.out.println(" " + rs.getString(1));
}
else if ((updateCount = s.getUpdateCount()) != -1)
System.out.println("Update Count: " + updateCount);
else
break fetchLoop;
}
Great, so now we get all the warnings e0, e1, e2, and the exception e3, along with the update counts:
Warning : e0 Update Count: 0 Warning : e1 Update Count: 1 Warning : e2 Update Count: 1 com.microsoft.sqlserver.jdbc.SQLServerException: e3 at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1547) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getMoreResults(SQLServerStatement.java:1270) at SQLServer.main(SQLServer.java:82)
That’s more like our batch. But we’re still aborting after e3. How can we get the result set? Easy! Just ignore the exception, right? :)
And while we’re at it, let’s use ResultSetMetaData to read the unknown result set type.
fetchLoop:
for (int i = 0, updateCount = 0; i < 256; i++) {
try {
boolean result = (i == 0)
? s.execute(sql)
: s.getMoreResults();
SQLWarning w = s.getWarnings();
for (int j = 0; j < 255 && w != null; j++) {
System.out.println("Warning : " + w.getMessage());
w = w.getNextWarning();
}
s.clearWarnings();
if (result)
try (ResultSet rs = s.getResultSet()) {
System.out.println("Result :");
ResultSetMetaData m = rs.getMetaData();
while (rs.next())
for (int c = 1; c <= m.getColumnCount(); c++)
System.out.println(
" " + m.getColumnName(c) +
": " + rs.getInt(c));
}
else if ((updateCount = s.getUpdateCount()) != -1)
System.out.println("Update Count: " + updateCount);
else
break fetchLoop;
}
catch (SQLException e) {
System.out.println("Exception : " + e.getMessage());
}
}
There, that’s more like it:
Warning : e0 Update Count: 0 Warning : e1 Update Count: 1 Warning : e2 Update Count: 1 Exception : e3 Result : i: 1 i: 2 Update Count: 0 Exception : e4
Now we’ve executed the entire batch in a super generic way with JDBC
Gah, I want this to be easier
Of course you do, which is why there is jOOQ. jOOQ has the really nice fetchMany() methods, which can execute random SQL strings to get a mixture of:
- Update counts
- Result sets
- Exceptions / Warnings (jOOQ 3.10+ only)
For example, we can write:
// Use this new setting to indicate that we don't want to throw
// exceptions, but collect them, as we've seen above
DSLContext ctx = DSL.using(c,
new Settings().withThrowExceptions(THROW_NONE));
// And then, simply:
System.out.println(ctx.fetchMany(sql));
The result is of the form:
Warning: SQL [...]; e0 Update count: 0 Warning: SQL [...]; e1 Update count: 1 Warning: SQL [...]; e2 Update count: 1 Exception: SQL [...]; e3 Result set: +----+ | i| +----+ | 1| | 2| +----+ Update count: 0 Exception: SQL [...]; e4
Excellent!
What we didn’t cover
Oh, tons of things, but I need material for future blog posts, too, right?
- We only discussed SQL Server so far
- We didn’t discuss the fact that SQLException.getNextException() doesn’t work here
- We didn’t discuss how we can combine this with OUT parameters (eegh, at what moment do we fetch those)
- We didn’t discuss the fact that some JDBC drivers don’t implement this correctly (looking at you, Oracle)
- We didn’t go into the depths of how JDBC drivers don’t implement
ResultSetMetaDatacorrectly - We didn’t cover the performance overhead of fetching warnings, e.g. in MySQL
- … and much more
So, are you still writing JDBC code yourself? :)
That’s why I really believe that jOOQ is worth its price! That’s an AWESOME framework!