JDBC: empty/null result set?

Question

I'm trying to use JDBC to update a table. The table consists of two attributes, A and B. The primary key is a composite key of A and B. It'll look something like this:

A | B
------
1 | 0
2 | 0
3 | 0
3 | 1

Now I'd want to be able to add a new entry to this table depending on the A attribute I choose. It should add another one, incrementing it by 1. For example, if I want to "update" 3, the table would look something like this:

A | B
------
1 | 0
2 | 0
3 | 0
3 | 1
3 | 2

To do this, I'm using a prepared statement to get the maximum B value for any A.

PreparedStatement max = connection.prepareStatement("SELECT max(B) as B FROM Table WHERE A = ?");
            max.setInt(1, x);
            ResultSet rsMax = max.executeQuery();
            if(rsMax.next()) {
                /* do stuff */
            }

Which works perfectly fine if there's an A value with the same value as x. However, I'm getting anomalies when x is a value which is not contained in the table, for example 10.

I'm using H2 and when I try to enter the same query as the one in the prepared statement (wich the right numbers of course) in the web interface, I get null as a result, which is what I am supposed to get if there's no A attribute with this value in the table. However, my program still goes into the if block and I don't know why. I thought this isn't supposed to happen if the result set is empty?

I could just try getting the value from the result set, but if I want to get an integer I understandably get a 0. Now I could work around this by not having any A entry using 0, but I doubt this is the right way to do this.

Any help would be appreciated.

Answer 1

First a word of warning do not use this approach if there could be more concurrent connection to the database.

The reason is simple, more session will will read the max value increase it and insert duplicates. On the other side if there is only one connection per time this is an valid approach.

The problem is that the MAX function returns always one record, so the test is not meaninfull.

The check must examine if the returned value is NULL or NOT NULL. For this purpose the wasNull method is defined - see snipped below:

if(rs.next()) {
   ResultSet idx = rs.getInt(1)
   if  (rs.wasNull()) { idx = 0;}  // no record found - use init value
   else {idx =+ 1;}  // record found - increase the value
}