Spring Framework: Confusions of RowCallbackHandler

I have had some time not writing my blog. Mainly because my previous employer doesn't allow me to access blogspot during the work hour - also I kind of ran out of topics. I would like to write something that is advanced, not only just tutorials. You can find some many entry-level materials repeating each other, but many times, you'll struggle too long when facing a deeper issue. At least I struggled so many times.

Anyway, I tried to restart blogging. And today's topic is about RowCallbackHandler interface in Spring framework.

You can find many tutorials/samples that suggesting you write program as below:

public class RowCallbackTutorial {

    private DataSource dataSource;

    public void query(String sql) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.query(sql, new RowCallbackHandler(){
            public void processRow(ResultSet rs) throws SQLException {

                System.out.println("Inside RowCallbackHandler");
                while ( rs.next() ) {
                    System.out.println("Got value:" + rs.getObject(1));
                }
            }
        });
    }
}

This actually is not right. Mentioned in the Java doc of this interface, method processRow "should not call next() on the ResultSet" - I guess they mean "should not call the next() on the first row of the ResultSet", since the ResultSet has already opened and pointing to first row when this method is called.

Here is a test using Spring embedded database support.

First we prepare a sql script to create table and insert a few rows, "db/init.sql". I have:

    CREATE TABLE customer (
        id         INTEGER PRIMARY KEY,
        name       VARCHAR(30)
    );

    insert into customer values(1, 'cust1');
    insert into customer values(2, 'cust2');

Then we test with the data.

    public static void main(String[] args) {

        EmbeddedDatabase db = new EmbeddedDatabaseBuilder()
        .setType(EmbeddedDatabaseType.DERBY)
        .addScript("db/init.sql")
        .build();

        String sql = "select * from customer";
        RowCallbackTutorial sample = new RowCallbackTutorial();
        sample.setDataSource(db);
        sample.query(sql);
    }

We're expecting the program to output:
    Got value:1

    Got value:2

In fact, the previous program will only show second row: Got value:2. Where is the first row?

The ResultSet has already opened, and the cursor is pointing to first row. The first "next()" will move the cursor to next row. So the correct operation should be:
                do {

                    System.out.println("Got value:" + rs.getObject(1));
                }  while ( rs.next() );

And run the program again, we now get the correct result:
    Got value:1

    Got value:2

Now here is the last question. What if the ResultSet is empty? Will "do {...} while ()" encounter any error?

The answer is, no problem. In such case, the RowCallbackHandler will not be invoked at all. Change the query to "select * from customer where 1=2" for a new test, the output "Inside RowCallbackHandler" will not appear.