Advanced Concepts


If you have made it this far, then you should have a good understanding of the basic functionality of the Spring JDBC framework, and you should already be productive with it. It is primarily based on abstraction shielding the calling code from having to deal with the technical details of database access, and allowing you to concentrate on accessing the actual data.

However, there are some details that we have not yet covered. In the pages that follow, we will cover advanced topics such as custom exception translation, LOB support, and returning result sets from stored procedures.

Running Spring JDBC in an Application Server

Running JDBC in general inside an application server provides a few challenges. Using Spring does not change this, but Spring provides valuable help in addressing these issues.

Connection Pooling

Most application servers provide some kind of connection pooling. The reason for this is that establishing the physical connection to the database can take a long time and it is preferable to have connections that are ready to use available. If your application server does not provide connection pooling, then you should use a third-party connection pool like Apache Commons DBCP. Usually the database connection is provided via a DataSource that is bound to a specific JNDI name. If you use an application context, then you should configure the DataSource lookup with a JndiObjectFactoryBean (see Chapters 2 and 3 for more details about configuring an application context). Here is an example of how you can specify this in the configuration files:

<bean          >   <property name="jndiName">     <value>java:comp/env/jdbc/ticketdb</value>   </property> </bean> 

If you are not using a Spring application context, you could use DataSourceUtils for the lookup directly from your application code. We do recommend the use of an application context, but the utility methods are available if you need them. If the DataSource is declared in a resource-ref in web.xml, then you should use this method:

DataSource ds = DataSourceUtils.getDataSourceFromJndi("jdbc/ticketdb");

The java:comp/env part will automatically be added if you don't provide it in the JNDI name. If you don't want this to be added, then your call should look as follows:

DataSource ds = DataSourceUtils.getDataSourceFromJndi("jdbc/ticketdb", false);

After you are done with your processing, the connection is returned to the pool to be reused by a future request for a connection. This is handled by the framework using DataSourceUtils.getConnection and DataSourceUtils.closeConnectionIfNecessary behind the scenes, and you don't have to worry about these details in your application code.

Connection and Statement Wrappers

Application server vendors typically need to provide extra hooks for managing transactions and other container-related concerns. They often do that by wrapping the connection and statement objects with their own special proxy classes. This can lead to problems when the database feature that you want to use requires the use of the native object as it was provided by the database. This is the case for handling LOBs and other special Types using Oracle. Spring provides a number of implementations of NativeJdbcExtractorAdapter. The SimpleNativeJdbcExtractor works for many servers and connection pools, but there are also specific implementations for WebLogic, WebSphere, JBoss, CommonsDBCP, and XAPool.

To use these extractors, you should pass the one you need to the JdbcTemplate you are using. This can best be done in the application context configuration:

<bean         >   <property name="jndiName">     <value>java:comp/env/jdbc/ticketdb</value>   </property> </bean>     <bean       lazy-init="true"/>     <!-- JdbcTemplate --> <bean     lazy-init="true">   <property name="dataSource">     <ref local="ticketDataSource"/>   </property>   <property name="nativeJdbcExtractor">     <ref local="nativeJdbcExtractor"/>   </property> </bean> 

The programmatic way to do this is:

jdbcTemplate.setNativeJdbcExtractor(new CommonsDbcpNativeJdbcExtractor );

Using Custom Exception Translations

If you have special needs in terms of exception translation, then you have a few options. The easiest one is to supply your own version of sql-error-codes.xml accessible on the classpath. This still only allows you to use any of the six categories defined for Spring's SQLErrorCodesSQLExceptionTranslator class, but in most cases this does provide enough customization. Additional options will be discussed shortly. Here is a summary of the translation sequence provided by Spring's JDBC framework:

  1. Call to customTranslate overridden method provided by a user-supplied implementation of SQLErrorCodeSQLExceptionTranslator.

  2. Custom translations to either your own exception extending DataAccessException or to one of Spring's data access exceptions. This is specified in a customized sql-error-codes.xml.

  3. Translation of error codes to one of the standard six exception categories provided in a customized sql-error-codes.xml.

  4. Standard translation of codes in sql-error-codes.xml provided in the Spring distribution.

  5. Fallback on SQL state translation using SQLStateSQLExceptionTranslator.

    Important 

    Why might you want to perform custom exception translation? Perhaps your database is reporting an unusual error condition that is not supported by Spring's default mappings. Or perhaps you have an advanced requirement, such as an Oracle trigger that throws an application-specific PL/SQL exception, and which should be reported to an application code with an appropriate exception class, providing elegant communication between PL/SQL and Java code.

Providing a CustomSQLErrorCodesTranslation

If you need an additional exception or you have your own exception that inherits from DataAccessException, then you can add a customTranslations entry to the sql-errorcodes.xml. The format for this entry is shown in the following example:

<bean  >   <property name="badSqlGrammarCodes">     <value>11,24,33</value>   </property>   <property name="dataIntegrityViolationCodes">     <value>1,12,17,22</value>   </property>   <property name="customTranslations">     <list>       <bean          >         <property name="errorCodes">           <value>942</value></property>         <property name="exceptionClass">           <value>com.mycompany.test.MyCustomException</value>         </property>       </bean>     </list>   </property> </bean>

You need to provide the error codes that you want translated and a concrete exception class that must be a subclass of DataAccessException. An example of a custom exception might look like this:

public class MyCustomException extends DataAccessException {       public MyCustomException(String msg) {     super(msg);   }       public MyCustomException(String msg, Throwable ex) {     super(msg, ex);   } }

For the most part, exceptions have little or no custom code in them. It is the class itself that carries the information we need about what type of exception it is.

The exception class provided must have one of the following constructors accessible: (String), (String, Throwable), (String, SQLException), (String, String, Throwable), or (String, String, SQLException). If there is a problem loading or instantiating this exception, then the translation will fall back on the regular SQL error code translation and a warning message will be logged.

Implementing an SQLErrorCodeSQLExceptionTranslator

If this still is not enough, then you can implement your own error code translator that will handle only some specific cases. You might, for instance, want to throw different exceptions based on the type of statement that failed. Maybe you have one exception for DDL statements and another one for all other statements. There is no need to handle all exception cases in your custom translator. You need to translate only the ones that are truly custom and delegate all other exceptions to the regular framework implementation by returning a null.

The recommended way of implementing custom translations is to create a new translator class that extends the SQLErrorCodeSQLExceptionTranslator class. The method that you should override is customTranslate. This method is passed three parameters. The first one is called "task" and it contains a short description of the task that failed. This description is provided by the framework class, which caught the exception. The second parameter contains the SQL statement that the framework was using at the time of the exception. If this was not available, then this parameter will be null. The third and last parameter is the actual SQLException that was caught. Using these parameters as a base, you should be able to determine if your special custom translation applies to this exception. If it does,you return the specific runtime exception that you want to use. This exception must inherit from the DataAccessException to make it easier for any calling classes to catch this exception. If the exception at hand was not one that you provide custom translation for, then you should simply return null and let the default translation implementation take care of it.

The following is an example of a custom exception translator where an error code of –12 for a select statement is translated to a MyCustomException:

public class CustomErrorCodesTranslator            extends SQLErrorCodeSQLExceptionTranslator {       protected DataAccessException customTranslate(String task, String sql,       SQLException sqlex) {     if (s1l != null && sqlex.getErrorCode() == -12 &&          sql.toUpper().startsWith("SELECT")) {       return new MyCustomException(task, sql, sqlex);     }     return null;   } }

To use this custom translator we need to prepare a JdbcTemplate that has this implementation set as its translator. You do this by using the setExceptionTranslator method of JdbcTemplate. You now have a prepared JdbcTemplate, and if you usually pass in a DataSource to your data access objects, now you should instead pass in this prepared JdbcTemplate. If you set the DataSource instead of the JdbcTemplate, then each data access object will create its own instance of JdbcTemplate and this instance will not know anything about your custom translation. It is important to set the DataSource for the custom translator instance. This DataSource is used for the fallback translation of any codes not handled explicitly by your custom translation. The fallback is the standard translation that usesthe DataSource to get the database metadata and look up a set of error codes specific for the current database. Here is a simple example of this configuration:

 CustomErrorCodesTranslator customTranslator = new CustomErrorCodesTranslator(); customTranslator.setDataSource(dataSource); jdbcTemplate = new JdbcTemplate(dataSource); jdbcTemplate.setExceptionTranslator(customTranslator); SqlUpdate operation = new SqlUpdate(); operation.setJdbcTemplate(jdbcTemplate); operation.setSql("SELECT THAT IS NO GOOD"); try {   operation.update(); } catch (DataAccessException dae) {   System.out.println(dae); }

Reading and Writing LOB Data

Most databases support reading and writing large chunks of data. We commonly refer to one of these chunks as a large object or a LOB. A LOB that contains binary data is called a BLOB (Binary Large Object) and one that contains character data is called a CLOB (Character Large Object). Spring lets you handle these large objects both via the JdbcTemplate directly and also through a higher abstraction via the RDBMS Objects support. Both these approaches use an implementation of the LobHandler interface for the actual management of the LOB data. The LobHandler provides the following support for LOB input and output:

  • BLOB

    • byte[]:getBlobAsBytes and setBlobAsBytes

    • InputStream:getBlobAsBinaryStream and setBlobAsBinaryStream

  • CLOB

    • String:getClobAsString and setClobAsString

    • InputStream:getClobAsAsciiStream and setClobAsAsciiStream

    • Reader:getClobAsCharacterStream and setClobAsCharacterStream

Using the JdbcTemplate for LOB Access

We will first look at an example of writing a BLOB to the database using a JdbcTemplate directly. The table is one that will hold the image of a poster used for a specific show. The table also includes a date for the first performance date because the show might have different posters at different times. Here is the SQL to create this Show_Poster table:

CREATE TABLE Show_Poster(   id INTEGER PRIMARY KEY NOT NULL,   first_performance DATE,   poster_image BLOB,   REF_Show_id INTEGER) 

Now we will look at the Java code we need to insert a row into this table:

DataSource dataSource; LobHandler lobHandler;     ...       JdbcTemplate jt = new JdbcTemplate(dataSource);       int newId = 1;   Date firstPerformance = new Date(System.currentTimeMillis());   File in = new File("spring2004.jpg");   InputStream is = null;   try {     is = new FileInputStream(in);   } catch (FileNotFoundException e) {     e.printStackTrace();   }       insertBlob(db, newId, firstPerformance, is, (int) in.length(), showId);     ...       private void insertBlob(JdbcTemplate jt, final int newId,        final Date firstPerformance, final InputStream is,        final int blobLength, final int showId) {     jt.execute(       "INSERT INTO Show_Poster " +        "(id, first_performance, poster_image, REF_Show_id) " +       "VALUES (?, ?, ?, ?)",       new AbstractLobCreatingPreparedStatementCallback(this.lobHandler) {           protected void setValues(PreparedStatement ps, LobCreator lobCreator)               throws SQLException {             ps.setInt(1, newId);             ps.setDate(2, new java.sql.Date(firstPerformance));             lobCreator.setBlobAsBinaryStream(ps, 3, is, blobLength);             ps.setInt(4, showId);           }       }     );   }

Let's take a closer look at what this code does. In addition to the DataSource and JdbcTemplate, we need an instance of a LobHandler implementation. For this example we can use the DefaultLobHandler that works with any database and JDBC driver that supports the standard LOB methods as they are defined in the JDBC specification. This includes drivers for MySQL, MS SQL Server, and the most recent Oracle 10g driver. For the earlier Oracle 9i driver, there is a custom OracleLobHandler implementation available.

Next we take advantage of the JdbcTemplate execute(String sql, PreparedStatementCallback action) method. This method will let us pass in an implementation of the AbstractLobCreatingPreparedStatetementCallback class. The only method we need to implement is setValues where we have access to the PreparedStatement and can use regular JDBC setXxx methods to set the values. We use the setBlobAsBinaryStream method from the LobHandler interface because this gives us the flexibility to rely on a custom implementation of the LobHandler interface if we need to. The DefaultLobHandler implementation just delegates to the regular JDBC method setBinaryStream, but the OracleLobHandler implementation provides more complex code to be able to support Oracle-specific LOB handling issues.

Next we will show an example of how to read the same BLOB from the database and write it to a file:

DataSource dataSource; LobHandler lobHandler;     ...       JdbcTemplate jt = new JdbcTemplate(dataSource);       int id = 1;   File out = new File("copy-of-spring2004.jpg");   OutputStream os = null;   try {     os = new FileOutputStream(out);   }    catch (FileNotFoundException e) {     e.printStackTrace();   }       streamBlob(jt, id, os);     ...       private void streamBlob(JdbcTemplate jt, final int id, final OutputStream os)       throws DataAccessException {     jt.query(         "SELECT poster_image FROM Show_Poster WHERE id = ?",          new Object[] {new Integer(id)},         new AbstractLobStreamingResultSetExtractor() {           public void streamData(ResultSet rs) throws SQLException, IOException {             FileCopyUtils.copy(lobHandler.getBlobAsBinaryStream(rs, 1), os);           }         }     );   }

We are again making use of an anonymous inner class in the call to the query method of JdbcTemplate. This time we pass in an implementation of the AbstractLobStreamingResultSetExtractor class, which has one method we must implement. This is the streamData method, which utilizes the getBlobAsBinaryStream method of the DefaultLobHandler to get the BLOB as a stream and writeit to the OutputStream that is passed in.

The AbstractLobStreamingResultSetExtractor class has two additional methods: handleNoRowFound and handleMultipleRowsFound. These methods can be used to provide custom error messages.

Using the JdbcTemplate directly gives you low-level control over the JDBC processing, but sometimes you want to work at a higher extraction level. The RDBMS Operation classes allow just that, and in the following section we will show an example of how the preceding code would look using this higher abstraction layer.

Using the RDBMS Operation LOB Support

Let's start by inserting a BLOB value into the Show_Poster table. For this task we will use the SqlUpdate class that we are familiar with already. The new feature that we will see is how we use an SqlLobValue class to pass in the BLOB content and a LobHandler:

DataSource dataSource; LobHandler lobHandler;     ...         JdbcTemplate jt = new JdbcTemplate(dataSource);       SqlUpdate su = new SqlUpdate(dataSource,        "INSERT INTO Show_Poster  " +        "(id, first_performance, poster_image, REF_Show_id) " +       "VALUES (?, ?, ?, ?)" );   su.declareParameter(new SqlParameter("id", Types.INTEGER));   su.declareParameter(new SqlParameter("first_performance", Types.DATE));   su.declareParameter(new SqlParameter("poster_image", Types.BLOB));   su.declareParameter(new SqlParameter("REF_Show_id", Types.INTEGER));   su.compile();       Object[] parameterValues = new Object[4];   parameterValues[0] = new Integer(1);   parameterValues[1] = new Date(System.currentTimeMillis()); File in = new File("spring2004.jpg"); InputStream is = null; try {   is = new FileInputStream(in); } catch (FileNotFoundException e) {   e.printStackTrace(); } parameterValues[2] = new SqlLobValue(is, (int) in.length(), lobHandler); parameterValues[3] = new Integer(3);     su.update(parameterValues);

The SqlLobValue class will use the LobHandler to write the BLOB data to the database so the difference is that you don't provide an anonymous class to set the parameter values. Instead you declare the parameter values and rely on the SqlUpdate implementation to do the work.

Now it is time to read the BLOB data from the table. Let's first define a new class that we would like to map the data to. We can call this class Poster:

public class Poster {   int id;   Date firstPerformance;   byte[] posterImage;       public Poster(int id, Date firstPerformance, byte[] posterImage) {     this.id = id;     this.firstPerformance = firstPerformance;     this.posterImage = posterImage;         }     ...       } 

Next we need to define a MappingSqlQuery implementation and call it to retrieve a single poster from the database:

private class PosterQuery extends MappingSqlQuery {   private static final String POSTER_QUERY =      "SELECT id, first_performance, poster_image FROM Show_Poster WHERE id = ?";   private LobHandler lobHandler;       PosterQuery(DataSource dataSource) {     super(dataSource, POSTER_QUERY);     declareParameter(new SqlParameter("id", Types.INTEGER));     compile();   }       public Object mapRow(ResultSet rs, int rowNumber) throws SQLException {     Poster p = new Poster(         rs.getInt(1),          rs.getDate(2),         lobHandler.getBlobAsBytes(rs, 3));     System.out.println(p);     return p;   }     ...       }

Again, we use a LobHandler to retrieve the BLOB data from the database. This time we use the getBlobAsBytes method, which returns a byte array.

This query can be created and executed using the following code:

PosterQuery pq = new PosterQuery(dataSource);  List posterList = pq.execute(1);

Using LobHandler and JdbcExtractor Together

If you need to use a JdbcExtractor for your application, then you also need to specify it for the LobHandler. For more on the JdbcExtractor, see the earlier section introducing this support.

<bean       lazy-init="true"/>     <!-- LobHandler for Oracle JDBC drivers --> <bean       lazy-init="true">   <property name="nativeJdbcExtractor">     <ref local="nativeJdbcExtractor"/>   </property> </bean>

Or if you prefer to do it programmatically:

oracleLobHandler.setNativeJdbcExtractor(new WebLogicNativeJdbcExtractor);

Batch Updates

If you perform a large number of updates, you will benefit from JDBC batching support. This allows you to group a number of statements together and have them sent together to the database for processing. This could be very beneficial for certain types of operations and with certain databases. Let's assume that we need to insert a large number of rows of data read from a flat file or an XML document. We will look at an example where we pass in a list of names and email addresses that we want to insert to the table. The table looks like this:

create table Contact_List    (id integer,     name varchar(100),     email varchar(100),     added date) 

The data for each row, passed in to the method that will perform the insert, is in the form of a Map with the key being the name of the column. All these Map objects are then added to a List, making up the entire set of data to be inserted.

We will look at two solutions for batch updates. The first one uses the JdbcTemplate directly and the second one relies on the RDBMS Operation support provided in BatchSqlUpdate. Here is the first solution:

private void insertBatch(JdbcTemplate db, final List data) {     int[] actualRowsAffected = db.batchUpdate(         "insert into contact_list (id, name, email, added) " +         "values(?, ?, ?, ?)",         new BatchPreparedStatementSetter() {           public void setValues(PreparedStatement ps, int i)             throws SQLException {             Map entry = (Map)data.get(i);             ps.setInt(1, ((Integer)entry.get("id")).intValue());             ps.setString(2, (String)entry.get("name"));             ps.setString(3, (String)entry.get("email"));             ps.setDate(4, (Date)entry.get("added"));           }           public int getBatchSize() {             return data.size();           }         });   } }

The RDBMS Operation version is similar but instead of providing a callback implementation of the BatchPreparedStatementSetter, you create a BatchSqlUpdate object and declare all the parameters. Each call to update adds an item to the batch and the batch will be written once the batch size has been reached. It defaults to 5000 and can be overridden by a call to setBatchSize. Once you are done it is important to call flush to write the last batch because the batch size most likely has not been reached yet:

private void insertBatch(DataSource dataSource, List data) {   BatchSqlUpdate update = new BatchSqlUpdate(dataSource,        "insert into contact_list (id, name, email, added) " +       "values(?, ?, ?, ?)");   update.declareParameter(new SqlParameter("id", Types.INTEGER));   update.declareParameter(new SqlParameter("name", Types.VARCHAR));   update.declareParameter(new SqlParameter("email", Types.VARCHAR));   update.declareParameter(new SqlParameter("added", Types.DATE));       for (int i = 0; i < data.size(); i++) {     Map entry = (Map)data.get(i);     Object[] values = new Object[4];     values[0] = entry.get("id");     values[1] = entry.get("name");     values[2] = entry.get("email");     values[3] = entry.get("added");     update.update(values);   }   update.flush(); } 

Advanced Use of Stored Procedures

Things get a little bit more complicated when you start using some of the more advanced features of the stored procedure support. (Although they're a lot simpler than performing the same task with raw JDBC, especially if you have any interest in portability.) Advanced features include support for stored procedures that return a result set and the use of arrays as both input and output parameters. We will look at some examples of this in this section.

Passing in a Complex Type as a Parameter

We saw earlier an example of a stored procedure that reserves a seat. If you wanted to reserve more than one seat then you had to make multiple calls to the procedure. We would like to be able to reserve a group of seats using a single call to the stored procedure. To be able to do this, we will enhance the procedure to accept an array of seat IDs as part of the input.

We are making use of a user-defined type called NUMBERS in this example. It is defined to be an array of numbers, and the SQL to create it is CREATE TYPE numbers AS TABLE OF NUMBER.

The new procedure looks as follows:

CREATE OR REPLACE procedure SPRING.RESERVE_SEATS(in_performance_id in number,             in_seats in numbers,             in_price number,             in_reserved_until date,             out_new_booking_id out number) is begin   -- Get a new pk for the bookin g table   select booking_seq.nextval into out_new_booking_id from dual;       -- Create a new booking   insert into booking(id, date_made, price, reserved_until)      values (out_new_booking_id, sysdate, in_price, in_reserved_until);       for i in 1..in_seats.count loop   insert into seat_status (REF_seat_id, REF_performance_id)         values(in_seats(i), in_performance_id);   update seat_status set REF_Booking_id = out_new_booking_id                 where REF_Seat_id = in_seats(i)                 and REF_performance_id = in_performance_id;   end loop; end; 

To call this procedure we need to create an instance of oracle.sql.ARRAY because Oracle does not support simply using setObject with a java.sql.Array object. In order to create an oracle.sql.ARRAY object, we need to first create an oracle.sql.ArrayDescriptor and then use this descriptor to create the ARRAY class. Both these steps require the use of the active connection to the database. This is something that we normally don't have when we use Spring's abstraction layer. The connection is always managed by the framework code behind the scenes. Luckily, there is a ParameterMapper interface that provides just the functionality that we need. The interface specifies a method with createMap that takes a connection as the only argument and then returns a Map containing the values that the framework should use to set the parameters for the execute call to the callable statement. Typically, we just call execute(Map inParams), but now we would call execute(ParameterMapper myMapper) and let the ParameterMapper implementation take care of creating the Map for us:

private class CallReserveSeats extends StoredProcedure {   private static final String RESERVE_SEATS_SQL = "reserve_seats";   public CallReserveSeats(DataSource dataSource) {     super(dataSource, RESERVE_SEATS_SQL);   }       public Map execute(final Integer id, final BigDecimal price,        final java.sql.Date reservedUntil) {     return execute(new ParameterMapper() {       public Map createMap(Connection conn) throws SQLException {             HashMap inpar = new HashMap(4);         inpar.put("performance_id", id);         ArrayDescriptor desc = new ArrayDescriptor("numbers", conn);         Integer[] numarr = {new Integer(2), new Integer(3)};         //params.remove("seats");         ARRAY nums = new ARRAY(desc, conn, numarr);         inpar.put("seats", nums);         inpar.put("price", price);         inpar.put("reserved_until", reservedUntil);             System.out.println(inpar);             return inpar;       }     });   } }

Returning a ResultSet

In addition to the common data types we have seen so far, stored procedures can return one or more result sets to the calling Java class. This can be done either as an implicit return value, as is the case for Sybase and Microsoft SQL Server, or it can be done as an explicitly declared parameter that returns a reference to a database cursor object, as is the case for PostgreSQL and Oracle. We will look at an example of each in this section. We will start off looking at a Microsoft SQL Server procedure that returns a result set and also returns an out parameter containing the timestamp when the process was executed:

CREATE PROCEDURE get_genres   @rundate datetime OUTPUT AS BEGIN    select @rundate = getdate()   select id, name from Genre END 

In order to tell the StoredProcedure class to expect a result set, we provide a new type of parameter declaration. This new declaration is SqlReturnResultSet and it takes a name and an implementation of the RowMapper interface as parameters. The RowMapper is another example of Spring using a callback interface to carry out some database operations. The implementation you supply here is responsible for generating an object for each row returned, and these objects will be put in a List and returned in the output Map using the name declared in the SqlReturnResultSet as the key. The result set must be processed before any other out parameters in order to provide maximum portability between databases. In order to do this, the SqlReturnResultSet parameters must be declared before any other parameters of type SqlParameter or SqlOutParameter. Here is an example of how to call the get_genres stored procedure:

public void doTest() {       ...       GetGenresCall proc = new GetGenresCall(dataSource);   Map out = proc.executeGetGenre();       System.out.println("Run: " + out.get("rundate"));   List genres = (List) out.get("genre");   for (int i = 0; i < genres.size(); i++) {     Genre g = (Genre) genres.get(i);     System.out.println(g.getId() + " " + g.getName());   } }     class GetGenresCall extends StoredProcedure {   private static final String GET_GENRES_SQL = "get_genres";       public GetGenresCall(DataSource dataSource) {     super(dataSource, GET_GENRES_SQL);     declareParameter(new SqlReturnResultSet("genre", new MapGenre()));     declareParameter(new SqlOutParameter("rundate", java.sql.Types.TIMESTAMP));     compile();   }       Map executeGetGenre() {     Map out = execute(new HashMap());     return out;   }     }     class MapGenre implements RowMapper {       public Object mapRow(ResultSet rs, int rowNum) throws SQLException {     Genre genre = new Genre();     genre.setId(rs.getInt("id"));     genre.setName(rs.getString("name"));     return genre;   } }

As you can see, the mapping is done the same way as when MappingSqlQuery is used. Just create a new object and populate the properties with data retrieved from the result set.

The Oracle version is very similar except that Oracle passes the result set back using a regular out parameter. This parameter must be declared being of the type oracle.jdbc.OracleTypes.CURSOR and it must also get a RowMapper implementation passed in as a third parameter. Because this is a regular SqlOutParameter out parameter, it should be declared in the same order as if it were any other type of out parameter:

CREATE OR REPLACE PROCEDURE get_genres (   refcur OUT Types.refcurtype,    rundate OUT DATE)  IS     refsql VARCHAR(255);  BEGIN      refsql := 'select id, name from genre’;   OPEN refcur FOR refsql;   SELECT sysdate INTO rundate FROM DUAL;  END;

We reference a type called Types.refcurtype for the reference to the cursor that is going to be passed back to the calling code. This type is declared in a separate package called Types. Here is the content of this package:

CREATE OR REPLACE PACKAGE Types AS     TYPE refcurtype IS REF CURSOR;  END; 

The Java code for the Oracle version code looks almost exactly the same: The only difference is the declaration of the out parameter as mentioned previously:

public void doTest() {       ...         GetGenresCall proc = new GetGenresCall(dataSource);   Map out = proc.executeGetGenre();       System.out.println("Run: " + out.get("rundate"));   List genres = (List) out.get("genre");   for (int i = 0; i < genres.size(); i++) {     Genre g = (Genre) genres.get(i);     System.out.println(g.getId() + " " + g.getName());   } }     class GetGenresCall extends StoredProcedure {   private static final String GET_GENRES_SQL = "get_genres";       public GetGenresCall(DataSource dataSource) {     super(dataSource, GET_GENRES_SQL);       declareParameter(new SqlOutParameter("genre",        oracle.jdbc.OracleTypes.CURSOR, new MapGenre()));       declareParameter(new SqlOutParameter("rundate", java.sql.Types.TIMESTAMP));     compile();   }   Map executeGetGenre() {     Map out = execute(new HashMap());     return out;   }     }     class MapGenre implements RowMapper {       public Object mapRow(ResultSet rs, int rowNum) throws SQLException {     Genre genre = new Genre();     genre.setId(rs.getInt("id"));     genre.setName(rs.getString("name"));     return genre;   } }

This feature of returning a reference to a database cursor can come in handy at times. You just have to remember that you tie yourself to the database platform you are currently using and it makes your code less portable.



Professional Java Development with the Spring Framework
Professional Java Development with the Spring Framework
ISBN: 0764574833
EAN: 2147483647
Year: 2003
Pages: 188

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net