Professional Java Development with the Spring Framework

The central class in the JDBC framework is JdbcTemplate, found in the org.springframework.jdbc.core package. This is the class that manages all the database communication and exception handling using a java.sql.Connection that is obtained from the provided javax.sql.DataSource (see below for more detail on the DataSource).

Important 

JdbcTemplate is a stateless and threadsafe class and you can safely instantiate a single instance to be used for each DAO.

Use of Callback Methods

JdbcTemplate is based on a template style of programming common to many other parts of Spring. Some method calls are handled entirely by the JdbcTemplate, while others require the calling class to provide callback methods that contain the implementation for parts of the JDBC workflow. This is another form of Inversion of Control. Your application code hands over the responsibility of managing the database access to the template class. The template class in turn calls back to your application code when it needs some detail processing filled in. These callback methods are allowed to throw a java.sql.SQLException, since the framework will be able to catch this exception and use its built-in exception translation functionality.

We'll look at an example of a callback method for a query method that takes an SQL statement, an object array with parameter values, and a callback implementation to handle the rows returned by the query. We have chosen to implement this callback implementation as an anonymous inner class. This is a common practice in code developed with Spring, and idiomatic Java. The advantages are that the code is more compact and the callback implementation has access to variables and parameters from the surrounding method. This is true as long as the variables and parameters are declared as final. A drawback is that the code can be hard to read if the callback implementation is lengthy. In that case it is better to refactor code into a separate method, or use a named, rather than anonymous, inner class. A good IDE will make this type of programming much easier. (It also has the significant advantage versus raw JDBC coding with try/catch blocks that the compiler will detect errors, rather than the errors appearing at runtime in the form of connection leaks.) Here is the code for our query example:

JdbcTemplate jt = new JdbcTemplate(dataSource); String sql = "select id, name from genre where id < ?"; final List myResults = new ArrayList(); List l = jt.query(sql, new Object[] {new Integer(4)},     new RowCallbackHandler() {       public void processRow(ResultSet rs) throws SQLException {         // do something with the rowdata - like create a new         // object and add it to the List in the enclosing code         Genre g = new Genre();         g.setId(rs.getLong("id");         g.setName(rs.getString("name");         myResults.add(g);       }     } );

The callback implementation is of type RowCallbackHandler, which is an interface requiring the implementation of a single method: processRow. This method gets called once for each row. Most of the time you would implement a method that stores the data in a final variable declared in the enclosing method. This final variable is usually a List or sometimes a Map. We will see examples of this and of other callback methods later in the "Advanced Use of JdbcTemplate" section. For more details on howto use anonymous inner classes, see Chapter 5 of The Java Programming Language, Third Edition, by Ken Arnold, James Gosling, and David Holmes (Addison-Wesley, 2000).

JdbcTemplate Convenience Methods

There are several convenience methods on JdbcTemplate that provide an easy interface for accessing and manipulating data in the database. These convenience methods lend themselves to direct use without the need to create or use additional framework objects. They provide basic options for executing queries and updating the database.

We have query methods with four different return types in two variations: one where you pass in static SQL, and one where you pass in SQL with placeholders for your parameters along with an object array containing the values for your parameters.

The return type is indicated in the name of the query method. The ones that return an Object take an additional parameter that indicates the required type that should be returned. If there is no match between the requested data type and the type returned by the database, then the appropriate conversion is attempted. In the case where this conversion fails, a TypeMismatchDataAccessException is thrown. Any type can be converted to a String and any numeric type should be able to be converted to any class that extends java.lang.Number. There might be some rounding issues or truncation, so you should select a type that matches the database value as closely as possible. The appropriate type mappings are defined in the JDBC specification.

The List that is returned by queryForList contains a Map for each row returned by the query. The Map's entries are keyed by the column name and the value is the column value returned from the database. The value's type depends on the database type and it follows the regular JDBC mapping rules.

In addition to the query methods there is an execute method that takes a static SQL statement as the only parameter:

void execute(String sql)

We used this method in the first example in this chapter where we executed a DDL statement to create a table.

The next group of convenience methods is the update methods. There is one where you pass in static SQL, one where you pass in SQL with placeholders and an object array with the parameter values, and finally one like the previous one except that you also pass in an array of integers containing the SQL types of your parameters. This can be helpful for making sure that the prepared statement is populated with the correct data type rather than relying on the default mappings.

All these update methods return an int indicating the number of rows that were affected, as reported by the JDBC driver.

JdbcTemplate also has a number of methods for more advanced processing requirements where the caller provides callback interfaces for preparing statements and for processing returned results. We will take a closer look at these methods later in this chapter.

Basic Queries Using the JdbcTemplate

The easiest way to query the database is to use some of the previously mentioned convenience methods of JdbcTemplate. We already talked about these earlier and now we will see additional examples of how to use them. One very useful method is queryForInt, which returns a single integer returned by the SQL statement passed in. The following example shows you how to do this:

JdbcTemplate jt = new JdbcTemplate(ds); int count = jt.queryForInt("select count(*) from Genre");

The SQL statement must, of course, return a single row with a single numeric column for this to work properly. If your query uses placeholders for parameters, then you would use a second parameter consisting of an object array with one element for each placeholder. We should note that the use of parameter placeholders will allow the framework to always use a prepared statement. This will allow the application server and the database server to optimize caching and reuse of your query statement. This can provide a significant boost in performance. The mapping between the object passed in and the SQL data type is the same as for regular JDBC usage:

JdbcTemplate jt = new JdbcTemplate(ds); Object[] parameters = new Object[] {"M"}; int count = jt.queryForInt("select count(*) from Genre where name > ?", parameters);

If your query returns a very large number that exceeds the capacity of an int, then you can use queryForLong in the same way as queryForInt. Any other return type is covered by queryForObject. Here you pass in the Class for the object you expect to get returned in addition to the SQL and the optional object array for parameters:

JdbcTemplate jt = new JdbcTemplate(ds); Object[] parameters = new Object[] {new Integer(2)}; Object o = jt.queryForObject("select name from Genre where id = ?", parameters, String.class);

So far the queries we have looked at have been limited to returning a single value. We also need a method for returning several column values for multiple rows. The method that provides this is queryForList. It returns a List of Map objects, one Map object for each row returned by the query. The Map holds the column values with the column name as the key for the entry:

JdbcTemplate jt = new JdbcTemplate(ds); Object[] parameters = new Object[] {new Integer(1)}; List l = jt.queryForList("select id, name from Genre where id > ?", parameters);

This would return something like the following based on the data in the previous example for our unit test:

[{NAME=Circus, ID=2}, {NAME=Rock Concert, ID=3}]

These query methods work well for simple queries, but if you have more complex ones or you need to map the data returned to actual business objects rather than a list, then you would be better off using a MappingSqlQuery. This query is described later in this chapter.

Basic Updating with JdbcTemplate

It's not enough to just be able to read the data. We also want to be able to modify it. One way of running simple update statements is the update() method of the JdbcTemplate. We can just pass in an SQL statement, or if we use parameter placeholders in the SQL, then we also pass in an object array with the actual parameters. Here is a more complete example using all the methods we have discussed thus far:

JdbcTemplate jt = new JdbcTemplate(ds); jt.execute("truncate table Genre"); int x = jt.update("insert into Genre (id, name) values(1, 'Opera’)"); x += jt.update("insert into Genre (id, name) values(2, 'Circus’)"); x += jt.update("insert into Genre (id, name) values(3, 'Rock Concert’)"); x += jt.update("insert into Genre (id, name) values(4, 'Symphony’)"); System.out.println(x + " row(s) inserted."); x = jt.update("update Genre set name = 'Pop/Rock’ where id new Object[] {new Integer(3)}); System.out.println(x + " row(s) updated."); x = jt.update("delete from Genre where id = 2"); System.out.println(x + " row(s) deleted."); List l = jt.queryForList("select id, name from Genre"); System.out.println(l);

You might wonder what the difference between the execute and update methods is. The update method returns a count of the number of rows affected and it will use a java.sql.PreparedStatement if you pass in parameters. The execute method always uses a java.sql.Statement, does not accept parameters, and it will not return a count of rows affected. The execute method is more appropriate for statements where you create and drop tables while the update method is more appropriate for insert, update, and delete operations.

Advanced Use of JdbcTemplate

We saw previously some examples of queries where the return type was of a type defined by the type of query you used. What if you wanted to create one of your own domain objects as the output from a query? There are several JdbcTemplate methods that will allow you to do that and we will show you some examples of this. We will also show you some examples of how you can create prepared statements and set the parameter values through some framework callback methods.

In addition to the query methods of the JdbcTemplate there is a set of RDBMS Operation classes that has similar functionality and at the same time allows you to write JDBC code in a more object-oriented manner. We will cover these classes later in this chapter.

Back to the JdbcTemplate. First we'll look at query(PreparedStatementCreator psc, RowCallbackHandler rch) which takes a callback interface of type PreparedStatementCreator as the first parameter and a RowCallbackHandler as the second parameter. The PreparedStatementCreator interface allows you to create a prepared statement given a connection that the JdbcTemplate provides. You must implement the createPreparedStatement method and the framework will handle all the exception and connection management as usual. The RowCallbackHandler interface is designed to handle the data return for each row of the ResultSet. The method you must implement is processRow and it does not have a return value. If you need to return data, then you should choose the ResultReader interface, which is what we did for this example. The ResultReader extends the RowCallbackHandler interface and adds a method called getResults for retrieving the locally maintained data. Here is an example of a method that uses both callback methods for a query. Note the use of the final keyword in the method signature to allow the callback method to reference this parameter:

public List aPreparedStatementCreatorQuery(final int id) {   JdbcTemplate jt = new JdbcTemplate(dataSource); final String sql = "select id, name from genre where id < ?"; List results = jt.query(       new PreparedStatementCreator() {         public PreparedStatement createPreparedStatement(Connection con)             throws SQLException {           PreparedStatement ps = con.prepareStatement(sql);           ps.setInt(1, id);           return ps;         } },       new ResultReader() {         List names = new ArrayList();         public void processRow(ResultSet rs) throws SQLException {           names.add(rs.getString("name"));         }         public List getResults() {           return names;         } } ); return results; }

If the callback methods are long, code may become hard to read. To avoid this, delegate to a method in the enclosing class for the additional logic, or use a top-level, rather than anonymous, inner class.

An alternative to the PreparedStatementCreator is the PreparedStatementSetter. With this interface, you need to set only the parameter values, so it is easier to use. The framework will create the actual prepared statement for you. This is appropriate if you don't need to control the creation of the prepared statement. Here is the same query using a PreparedStatementSetter:

public List aPreparedStatementSetterQuery(final int id) { JdbcTemplate jt = new JdbcTemplate(dataSource); final String sql = "select id, name from genre where id < ?"; List results = jt.query(sql,       new PreparedStatementSetter() {         public void setValues(PreparedStatement ps)             throws SQLException {           ps.setInt(1, id);         }       }, new ResultReader() { List names = new ArrayList(); public void processRow(ResultSet rs) throws SQLException { names.add(rs.getString("name")); } public List getResults() { return names; } } ); return results; }

The only difference in this example, except for the callback method change, is that we pass in the SQL statement along with the PreparedStatementSetter as the first two parameters to the query method.

Support for RowSet

The RowSet interface was introduced as part of the JDBC Optional Package features for JDBC 2.0. It provides a JavaBeans-compliant way to interact with tabular data retrieved from the database. It gives you a more flexible interface than the ResultSet and it also provides the ability to detach the data and disconnect from the active database connection while still being able to access the data.

The RowSet interface has not been in widespread use until recently because it was part of the JDBC Optional Package. This meant that it was not part of standard J2SE distributions as recent as JDK 1.4. JSR-114 was started to try to popularize the RowSet interface and to provide some reference implementations. These implementations are now part of J2SE 5.0.

The RowSet implementation that Spring provides support for is the CachedRowSet, which is a disconnected rowset. Even though this RowSet implementation is disconnected, any method call can still result in an SQLException being thrown. To work around this limitation, Spring provides an SqlRowSet class that wraps a CachedRowSet and provides exception translation from any SQLException thrown toSpring's own informative, portable data access exception hierarchy.

The javax.sql.RowSet interface allows you to update the data and apply the changes back to the database. This is a feature that we consider to be of limited value and there isn't currently any support for this in Spring. You write queries for Spring's RowSet support in the same way that you write queries that retrieve a List. Just use the queryForRowSet methods the same way as you would use the queryForList methods. Here is the queryForList example presented previously rewritten to return an SqlRowSet:

JdbcTemplate jt = new JdbcTemplate(ds); Object[] parameters = new Object[] {new Integer(1)}; SqlRowSet srs = jdbcTemplate.queryForRowSet( "select id, name from Genre where id > ?", parameters);

To access the data returned in the SqlRowSet, you would just navigate and retrieve the column values the same way you would with a regular java.sql.ResultSet:

while (srs.next()) { System.out.println(srs.getString("id") + " - " + srs.getString("name")); }

You should check that the JDBC driver you are using provides support for the latest RowSet implementations. We have tested successfully with the most recent MySQL, PostgreSQL, and Oracle drivers.

If you are not using J2SE 5.0, you will need to download the JDBC RowSet Implementations available as a separate download from Sun's Java website. To read more about the JDBC RowSet Implementations, refer to the tutorial available at http://today.java.net/pub/a/today/2004/10/15/jdbcRowsets.html.

Related

Категории