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;
   try
   {
      //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);
      rs.next();
      String nextIdString = rs.getString(1);

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

      tStmt.close();

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