Add Some Spring to Your Oracle JDBC Access
by Dustin Marx

Learn how to use the Spring Framework to improve your Oracle JDBC access.

Downloads for this article: Oracle Database 10g Oracle JDBC Drivers Spring Framework

Listing 1

try
{
  // JDBC Connection/Statement/Result Set
}
catch (SQLException sqlEx)
{
  // Handle the exception
}
finally
{
  try
  { // Closing connection *should* close statement and result set
   if (stmt != null) stmt.close();
   if (conn != null) conn.close();
  }
  catch (SQLException sqlEx)
  {
   System.err.println("SQLException NOT handled");
  }
}

Listing 2

List commissions = new ArrayList();
Statement stmt = null;
ResultSet rs = null;
try
{
  stmt = this.myConnection.createStatement();
  rs = stmt.executeQuery("SELECT comm FROM emp"); 
  while ( rs.next() )
  {
   Integer commission = new Integer( rs.getInt("COMM") );
   if ( rs.wasNull() )
   {
     // By assigning the commission to null, this effectively
     // represents a null in the database as a Java null.
     System.out.println( "\tCommission seen as " + commission +
               " is really null");
     commission = null;
   }
   commissions.add( commission );
  }
}
catch (SQLException sqlEx)  // checked
{
  System.err.println( "Message: " + sqlEx.getMessage() );
  System.err.println( "Error Code: " + sqlEx.getErrorCode() );
  System.err.println( "SQL State: " + sqlEx.getSQLState() );
}
finally
{
  try
  {
   if ( rs != null ) { rs.close(); }
   if ( stmt != null ) { stmt.close(); }
  }
  catch (SQLException sqlEx) // checked
  {
   System.err.println( sqlEx.getMessage() );
  }
}

Listing 3

List commissions = new ArrayList();
try
{
  JdbcTemplate jt = new JdbcTemplate(this.myDataSource);
  List commList = jt.queryForList( "SELECT comm FROM emp");
  
  Iterator commIter = commList.iterator();
  while ( commIter.hasNext() )
  {
   Number comm = (Number) ((Map) commIter.next()).get("COMM");
   if (comm != null)
     commissions.add( new Integer(comm.intValue()) );
   else commissions.add( null );  } 
}
catch ( DataAccessException ex )  // unchecked exception
{
  System.err.println( ex.getMessage() );
}

Some of the most commonly seen errors in JDBC accessing an Oracle database are listed in Table 1 below. The examples in the "Example Code and/or Comment" column show types of SQL statements that lead to this error or provide additional comments on the specific error shown in that row of the table.

The online resources section at the end of this article includes a link to a Web site that details the various Oracle database exceptions one might encounter. The Oracle JDBC driver error codes can be found in Appendix B of the Oracle JDBC Developer's Guide and Reference and several general Oracle database-originated error codes can be found in the Oracle Database Error Messages document (does not list product-specific ORA messages).

Listing 4

<bean id="Oracle”
   class="org.springframework.jdbc.support.SQLErrorCodes">
 <property name="badSqlGrammarCodes">
  <value>900,903,904,917,936,942,17006</value>
 </property>
 <property name="invalidResultSetAccessCodes">
  <value>17003</value>
 </property>
 <property name="dataAccessResourceFailureCodes">
  <value>17002</value>
 </property>
 <property name="dataIntegrityViolationCodes">
  <value>1,1400,1722,2291</value>
 </property>
 <property name="cannotAcquireLockCodes">
  <value>54</value>
 </property>
</bean>

Listing 5

String updateStr =
  "UPDATE salgrade SET losal = ?, hisal = ? WHERE grade = ?";
PreparedStatement stmt = null;
   
try
{
  stmt = this.myConnection.prepareStatement(updateStr);
  stmt.setInt(1,aLowSal);
  stmt.setInt(2,aHighSal);
  stmt.setInt(3,aSalGrade);
     
  updateStatus = stmt.execute();
  stmt.close();
} // lots of catch and finally code typically follows here

String updateStr =
  "UPDATE salgrade SET losal = ?, hisal = ? WHERE grade = ?";

JdbcTemplate jt = new JdbcTemplate(this.myDataSource);
jt.update( updateStr,
      new Object[] { new Integer(aLowSal),
             new Integer(aHighSal),
             new Integer(aSalGrade) } );
// No handling/closing of PreparedStatement or catch/finally needed

Listing 7

String queryStr = "SELECT rowid FROM emp WHERE empno = "
         + aEmpNum;  // aEmpNum set previously
String rowId = null;
try
{
  stmt = this.myConnection.createStatement();
  rs = stmt.executeQuery(queryStr);
  while ( rs.next() )
  {
   rowId = rs.getString("ROWID");
  }
} // lots of catch-finally code needed after this

String queryStr = "SELECT rowid FROM emp WHERE empno = "
         + aEmpNum;
String rowId = null;
  
try
{
  JdbcTemplate jt = new JdbcTemplate(this.myDataSource);
  oracle.sql.ROWID oraRowId =
   (ROWID) jt.queryForObject(queryStr, ROWID.class);
  rowId = oraRowId.stringValue();
}
catch ( IncorrectResultSizeDataAccessException wrongSizeEx )
{
  // This unchecked exception is thrown in this case if more
  // than one result is returned from the query.
  // Explicitly printing out the results of this exception's
  // methods getExpectedSize() and getActualSize() is really not
  // necessary in this case because this exception's getMessage()
  // returns this same information in sentence form. 
  System.err.println( wrongSizeEx.getMessage() );
  System.err.print( "Expected " + wrongSizeEx.getExpectedSize()
          + " results, but actually got back "
          + wrongSizeEx.getActualSize() + " results.");
}

Listing 9

String queryStr = "SELECT sysdate FROM dual";
Date date = null;
  
try
{
  JdbcTemplate jt = new JdbcTemplate(this.myDataSource);
  date = (Date) jt.queryForObject(queryStr, Date.class);
}
catch ( BadSqlGrammarException badSqlEx ) // unchecked
{
  System.err.println( badSqlEx.getMessage() );
  System.err.println( "Bad SQL: " + badSqlEx.getSql() );
}

Listing 10

Statement stmt = null;

try
{
  stmt = this.myConnection.createStatement();
  stmt.executeUpdate("DROP TABLE salgrade PURGE");
}
catch ( SQLException sqlEx )
{
  System.err.println("Message: " + sqlEx.getMessage());
  System.err.println("Error Code: " + sqlEx.getErrorCode());
  System.err.println("SQL State: " + sqlEx.getSQLState());
}
finally
{
  try
  {
   if (stmt != null)
   {
     stmt.close();
   }
  }
  catch (SQLException finallySqlEx)  // checked exception
  {
   System.err.println(finallySqlEx.getMessage());
  }
}

try
{
  JdbcTemplate jt = new JdbcTemplate(this.myDataSource);
  jt.execute("DROP TABLE salgrade PURGE");
}
catch ( BadSqlGrammarException badSqlEx ) // unchecked
{
  System.err.println( badSqlEx.getMessage() );
  System.err.println( "BadSQL: " + badSqlEx.getSql() );
}

Listing 12

CREATE OR REPLACE PROCEDURE salary_percentile (
  salary IN emp.sal%TYPE,
  low IN salgrade.losal%TYPE,
  high IN salgrade.hisal%TYPE,
  percentile OUT NUMBER )
AS
BEGIN
 IF salary < 0 THEN
  percentile := null;
 ELSE
  percentile := (salary - low) / (high - low);
 END IF;
END;

String escapeString = "{call salary_percentile (?,?,?,?)}";
CallableStatement cStmt = null;
double percentile = -1.0;
final int PERCENTILE_INDEX = 4;
   
try
{
  cStmt = this.myConnection.prepareCall(escapeString);
  cStmt.setInt(1, aSalary); // aSalary passed into this method
  cStmt.setInt(2, aLow);   // aLow passed into this method
  cStmt.setInt(3, aHigh);  // aHigh passed into this method
  cStmt.registerOutParameter(PERCENTILE_INDEX, Types.DOUBLE);
  cStmt.execute();
  percentile = cStmt.getDouble(PERCENTILE_INDEX);
}
catch ( SQLException sqlEx )
{
  System.err.println("Message: " + sqlEx.getMessage());
  System.err.println("Error Code: " + sqlEx.getErrorCode());
  System.err.println("SQL State: " + sqlEx.getSQLState());
}
finally
{
  if ( cStmt != null )
  {
   try
   {
     cStmt.close();
   }
   catch (SQLException finallySqlEx)
   {
     System.err.println(finallySqlEx.getMessage());
   }
  }
}

return percentile;

Listing 14

private class SalaryCalculator extends StoredProcedure
{
  /** Name of procedure in database. */
  public static final String PROC_NAME = "salary_percentile";

  /**
  * Constructor for this StoredProcedure class.
  * @param ds Data Source.
  */
  public SalaryCalculator(DataSource ds)
  {
   setDataSource(ds);
   setSql(PROC_NAME);

   // Parameters should be declared in same order here that
   // they are declared in the stored procedure. 

   declareParameter(new SqlParameter("salary", Types.DOUBLE));
   declareParameter(new SqlParameter("low", Types.INTEGER));
   declareParameter(new SqlParameter("high", Types.INTEGER));
   declareParameter(new SqlOutParameter( "percentile",
                      Types.DOUBLE ) );
   compile();
  }
   
  /**
  * Execute stored procedure.
  * @return Results of running stored procedure.
  */
  public Map executeCalculation( double aSalary,
                 int aLow,
                 int aHigh )
  {
   Map inParameters = new HashMap();
   inParameters.put( "salary", new Double(aSalary) );
   inParameters.put( "low", new Integer(aLow) );
   inParameters.put( "high", new Integer(aHigh) );
   Map out = execute( inParameters ); // Call on parent class
   return out;
   }
  }

// . . .
// Code below is all that is needed to call your Stored Procedure
// created above. 
// . . .

  SalaryCalculator calcPercentile =
   new SalaryCalculator(this.myDataSource);
  Map calcResults =
   calcPercentile.executeCalculation(aSalary, aLow, aHigh);
   
  return ((Double)calcResults.get("percentile")).doubleValue();

// . . .
// remainder of class
// . . .

Listing 15

CREATE OR REPLACE TYPE address_type AS OBJECT
(
 STREET VARCHAR2(20),
 CITY  VARCHAR2(15),
 STATE  CHAR(2),
 ZIP   CHAR(5)
);
/

CREATE TABLE emp_address_table
(
 EMP_NUM NUMBER(4),
 ADDRESS ADDRESS_TYPE
);

Listing 16

String queryStr =  "SELECT address FROM emp_address_table WHERE "
         + "emp_num = " + aEmpNum; // aEmpNum passed in
final List addresses = new ArrayList();
  
JdbcTemplate jt = new JdbcTemplate(this.myDataSource);
jt.query( queryStr,
     new RowCallbackHandler()
     {
       public void processRow(ResultSet rs)
        throws SQLException
       {
        // The Struct and ResultSet methods throw
        // SQLException, so throws above is necessary
        java.sql.Struct address =
         (java.sql.Struct) rs.getObject(1);
        String street =
          address.getAttributes()[0].toString();
        String city =
          address.getAttributes()[1].toString();
        String state =
          address.getAttributes()[2].toString();
        String zipCode =
          address.getAttributes()[3].toString();
        String addressStr = street + ", " + city + ", "
                  + state + " " + zipCode;
        addresses.add( addressStr );
       }
     }
    );

String updateStr =  "UPDATE emp_address_table SET address = ? "
          + "WHERE emp_num = ?";

JdbcTemplate jt = new JdbcTemplate( getDataSource() );
jt.update( updateStr,
      new PreparedStatementSetter()
      {
       public void setValues(PreparedStatement ps)
         throws SQLException
       {
         Address address = new Address();
         address.setStreet(aStreet);
         address.setCity(aCity);
         address.setState(aState);
         address.setZip(aZipCode);
          
         ps.setObject(1, address);
         ps.setInt(2, aEmpNum);
       }
      }
);

• Spring's JdbcTemplate class supports several other Spring-provided interfaces that are similar to the ones discussed in this article. These include ResultSetExtractor and PreparedStatementCreator, which are similar to RowCallbackHandler and PreparedStatementSetter respectively. Another of the many interfaces that JdbcTemplate supports is the highly useful BatchPreparedStatementSetter.
• While the Spring JDBC abstraction does not need to be used in a Spring application context, the optional use of the JDBC abstraction in this context provides benefit. The Spring Framework can be used to wire data access objects to business objects via configuration files rather than with direct coupling in the code.
• The org.springframework.jdbc.object package supports relational database operations (including DML statements and stored procedures) as reusable objects. These threadsafe objects give the developer an even higher level of abstraction from the relational database than provided by Spring's JdbcTemplate described earlier in this article and used in all the code examples. I personally like the level of abstraction that JdbcTemplate provides and feel that its handling of routine issues like exceptions, result sets, and resource management provide exactly the level of support I desire. However, if an organization or its developers would like to enjoy an even higher level of abstraction away from relational database concepts without embracing a complete object-relational (O/R) mapping technology (such as TopLink or Hibernate), the org.springframework.jdbc.object package may be worth considering. The stored procedure example in Listing 12 provides a brief foray into this package.
• The Spring Framework provides a simple DataSource implementation that can be used outside of a container and provides an abstract datasource class you can override as needed (see Chapter 10 of Spring's reference documentation).
• Besides JDBC, the Spring Framework supports multiple O/R mapping technologies such as Hibernate, iBatis, and Java Data Objects (JDO). (See Chapter 11 of Spring's reference documentation.) The online references section at the bottom of this article includes a link to another article on using Spring with Oracle's TopLink object-relational mapping product.
• The Spring Framework facilitates development of technology-agnostic DAO interfaces and implementations that share same exception hierarchy. (See Chapter 9 of Spring Reference Documentation.) This topic was briefly alluded to in several places in the article.
• The Spring Framework provides support for catching SQLWarnings as an exception (SQLWarningException) when desired. This is not particularly useful with the Oracle database and JDBC drivers because they tend to throw SQLException for nearly every condition. It is a more useful feature for developers who need to know about warnings and are using a database that uses SQLWarning more extensively. In those cases, the developer who uses JDBC directly must specifically ask if a SQLWarning occurred. The Spring Framework can be used to treat these warnings like any other Spring exception for easier and more consistent handling.
• The Spring Framework provides the org.springframework.jdbc.support.nativejdbc.NativeJdbcExtractor interface and some implementations (such as SimpleNativeJdbcExtractor) of this interface. These are useful for accessing Oracle features via an Oracle connection or ResultSet when the connection is "wrapped" by another DataSource (such as that used with some application servers) or obtained through certain connection pools.
• For creating instances of oracle.sql.BLOB (binary large object) and oracle.sql.CLOB (character large object), Spring provides the class org.springframework.jdbc.support.lob.OracleLobHandler.
• The Spring-provided OracleSequenceMaxValueIncrementer class provides the next value of an Oracle sequence. It effectively provides the same information that would be provided if you used the following command directly: select someSequence.nextval from dual (where someSequence is the name of your sequence in the Oracle database). An advantage of this approach is that the DataFieldMaxValueIncrementer interface can be used in a DAO hierarchy without tight coupling of the Oracle-specific implementation.