Tuesday, January 17, 2006

It's One More, Innit?

Thanks to Scott Lynch for submitting an example of how a J2EE application developer just might not trust the database to do its job.

Over to Scott...

From a big bucks retail management system (now owned by a big bucks DBMS vendor).

1. Get NextVal from the sequence.

2. Assign the value, an integer, to a string.

3. Check to see if the string they just created exists.

4. Cast the integer that has been cast to a string, to a BigDecimal.

5. Add 1 to it (because they're obviously smarter than some silly old sequence).

I just love step 3.

Sheer brilliance on that one. And it's repeated for almost every table in this particular little slice of the application.


public long getNextId() throws java.sql.SQLException{
   if (conn == null)
      throw new java.sql.SQLException("Connection not set");
   long nextIdLong = 0;
      //Create a statement
      tStmt = conn.createStatement();

      //Create a query string to get all the fields from the table. The
      //presentation layer will decide which field to display
      String query = "SELECT some_seq.nextval FROM dual";

      //The complete query is executed
      rs = tStmt.executeQuery(query);
      String nextIdString = rs.getString(1);

      if (nextIdString != null) {
         nextIdLong = ((new BigDecimal(nextIdString)).add(new BigDecimal(1))).longValue();


   } catch (SQLException e)
      throw new java.sql.SQLException(e.toString());
   return nextIdLong;


Thomas Kyte said...

but I really like the comment:

//Create a query string to get all the fields from the table. The
//presentation layer will decide which field to display
String query = "SELECT some_seq.nextval FROM dual";

guess they define "code reuse" as "cut and paste the only example of a query we have everywhere"

And the comment bodes well for performance as well in general. "Get ALL of the fields - the heck if we know if you actually need them, we love doing unnecessary table access by index rowids!"

The rest is sheer brilliance though. I really like the "let us add one" (cannot figure out *why* but all of the string conversions is nice)...

William Robertson said...

Obviously they have to add one because otherwise the value from the stupid sequence isn't quite high enough.

The alternative would be to subtract one from all the existing values in the column.

Sergei said...

Guys are good, I am a J2EE developer and do understand what they are doing:

1. They use code generation and have created a framework, which is automatically displaying tables. This is smart!
2. You have to use BigDecimal's in calculations, otherwise precision may be lost.
3. I am not sure, how SQLException.toString is implemented, but if the intention was to hide the SQL stack trace, then it is better to catch SQLException and to throw a new SQLException with empty string or some standard message, like "SQL exception occured!", but I guess guys know that they do, while using toString()

Thai Rices said...

Sergei, exactly how much precision to you need to calculate:

[integer] + 1 ?

Why would you want to take the information specifically supplied to let you know what exceptional condition occurred and replace it with a meaningless message like "SQL exception occured[sic]!" ?

William Robertson said...

You can never have too much precision.

Well, not unless you are handling SQL exceptions.

Noons said...

No Sergei, that is not smart. That is as stupid as it comes.
Unfortunately, most J2EE folks just cannot understand how stupid...

Scott Swank said...

In particular,

1) You're probably going to be executing this repeatedly, so how about a prepared statement?
2) Why is it calling getString instead of getLong?
3) Why is it checking whether the result is null?
4) Why is it add 1 to the value?
5) Why is it using a BigDecimal for integer arithmetic?
6) Why isn't it closing the result set?
7) Why is it catching a sql exeception, only to throw a new sql exeception with the exact same content?