It's One More, Innit? ....oracle

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;
}