JDBC Tips and Tricks

Tip #1

A ResultSet is ALWAYS returned from an executeQuery() method. This often causes issues since developers often try to check to see if the result was null, and it will always return false.

For example:
...
Statement stmt = connection.createStatement();

ResultSet rs = stmt.exeuteQuery("SELECT * FROM X.Y");

// Check for null will be false
If(rs == null) {
//Do something
}
...

To check for results use next(). This will allow you to determine if there are results.

if(rs.next()) {
//Do something
}

Tip #2

Please note that generally a cursor returned by a query only moves forward through the ResultSet unless it is set explicitly. Some databases do not support anything other than forward only cursors. You need to set the cursor explicitly before executing a query.

To set the cursor to a read-only concurrency and scroll insensitive use the following:
...
Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
...

Tip #3

To see native database errors try using Connection.nativeSQL("your query here") in the error messages. This will show you what the database thinks you are asking for.

try {
...
} catch (SQLException e ) {
info("An SQLException occurred. This is the native query sent to the database\n: "
+ con.nativeSQL("SELECT TRUNC(SYSDATE) DATE FROM DUAL"));
}
...

Tip #4

If you use a counter inside your next() method loop you can see how many results were processed, or report if nothing was processed.

...
int counter = 0;
while(rs.next()) {
//Do something
counter++;
}

if (counter == 0) {
System.out.println("No records processed.");
} else {
System.out.println(counter + " records processed.");
}
...