JdbcTemplate的使用

12. Data access with JDBC
Prev	Part IV. Data Access	Next

---

12. Data access with JDBC

12.1 Introduction to Spring Framework JDBC

The value-add provided by the Spring Framework JDBC abstraction isperhaps best shown by the sequence of actions outlined in the tablebelow. The table shows what actions Spring will take care of and whichactions are the responsibility of you, the application developer.

Table 12.1. Spring JDBC - who does what?

Action	Spring	You
Define connection parameters.		X
Open the connection.	X
Specify the SQL statement.		X
Declare parameters and provide parameter values		X
Prepare and execute the statement.	X
Set up the loop to iterate through the results (ifany).	X
Do the work for each iteration.		X
Process any exception.	X
Handle transactions.	X
Close the connection, statement and resultset.	X

The Spring Framework takes care of all the low-level details thatcan make JDBC such a tedious API to develop with.

12.1.1 Choosing an approach for JDBC database access

You can choose among several approaches to form the basis for yourJDBC database access. In addition to three flavors of the JdbcTemplate,a new SimpleJdbcInsert and SimplejdbcCall approach optimizes databasemetadata, and the RDBMS Object style takes a more object-orientedapproach similar to that of JDO Query design. Once you start using oneof these approaches, you can still mix and match to include a featurefrom a different approach. All approaches require a JDBC 2.0-compliantdriver, and some advanced features require a JDBC 3.0 driver.

	Note
	Spring 3.0 updates all of the following approaches with Java 5support such as generics and varargs.

• JdbcTemplate is the classicSpring JDBC approach and the most popular. This "lowest level"approach and all others use a JdbcTemplate under the covers, and allare updated with Java 5 support such as generics and varargs.

• NamedParameterJdbcTemplatewraps a JdbcTemplate to provide named parametersinstead of the traditional JDBC "?" placeholders. This approachprovides better documentation and ease of use when you have multipleparameters for an SQL statement.

• SimpleJdbcTemplate combinesthe most frequently used operations of JdbcTemplate andNamedParameterJdbcTemplate.

• SimpleJdbcInsert andSimpleJdbcCall optimize database metadata to limit theamount of necessary configuration. This approach simplifies codingso that you only need to provide the name of the table or procedureand provide a map of parameters matching the column names.This only works if the database provides adequate metadata. If thedatabase doesn't provide this metadata, you will have to provideexplicit configuration of the parameters.

• RDBMS Objects including MappingSqlQuery,SqlUpdate and StoredProcedure requires you to createreusable and thread-safe objects during initialization of your dataaccess layer. This approach is modeled after JDO Query wherein youdefine your query string, declare parameters, and compile the query.Once you do that, execute methods can be called multiple times withvarious parameter values passed in.

12.1.2 Package hierarchy

The Spring Framework's JDBC abstraction framework consists of fourdifferent packages, namely core,datasource, object, andsupport.

The org.springframework.jdbc.core packagecontains the JdbcTemplate class and its variouscallback interfaces, plus a variety of related classes. A subpackagenamed org.springframework.jdbc.core.simple containsthe SimpleJdbcTemplate class and the relatedSimpleJdbcInsert andSimpleJdbcCall classes. Another subpackage namedorg.springframework.jdbc.core.namedparam contains theNamedParameterJdbcTemplate class and the relatedsupport classes. See Section 12.2, “Using the JDBC core classes to control basic JDBC processing anderror handling”, Section 12.4, “JDBC batch operations”, and Section 12.5, “Simplifying JDBC operations with the SimpleJdbc classes”

The org.springframework.jdbc.datasource packagecontains a utility class for easyDataSource access, and various simpleDataSource implementations that can beused for testing and running unmodified JDBC code outside of a Java EEcontainer. A subpackage namedorg.springfamework.jdbc.datasource.embedded providessupport for creating in-memory database instances using Java databaseengines such as HSQL and H2. See Section 12.3, “Controlling database connections” andSection 12.8, “Embedded database support”

The org.springframework.jdbc.object packagecontains classes that represent RDBMS queries, updates, and storedprocedures as thread safe, reusable objects. See Section 12.6, “Modeling JDBC operations as Java objects”.This approach is modeled by JDO, although ofcourse objects returned by queries are “disconnected” fromthe database. This higher level of JDBC abstraction depends on thelower-level abstraction in theorg.springframework.jdbc.core package.

Theorg.springframework.jdbc.support package providesSQLException translation functionality and someutility classes. Exceptions thrown during JDBC processing are translatedto exceptions defined in the org.springframework.daopackage. This means that code using the Spring JDBC abstraction layerdoes not need to implement JDBC or RDBMS-specific error handling. Alltranslated exceptions are unchecked, which gives you the option ofcatching the exceptions from which you can recover while allowing otherexceptions to be propagated to the caller. See Section 12.2.4, “SQLExceptionTranslator”.

12.2 Using the JDBC core classes to control basic JDBC processing anderror handling

12.2.1 JdbcTemplate

The JdbcTemplate class is the central classin the JDBC core package. It handles the creation and release ofresources, which helps you avoid common errors such as forgetting toclose the connection. It performs the basic tasks of the core JDBCworkflow such as statement creation and execution, leaving applicationcode to provide SQL and extract results. TheJdbcTemplate class executes SQL queries, updatestatements and stored procedure calls, performs iteration overResultSets and extraction of returnedparameter values.It also catches JDBC exceptions and translates them to the generic, moreinformative, exception hierarchy defined in theorg.springframework.dao package.

When you use the JdbcTemplate for yourcode, you only need to implement callback interfaces, giving them aclearly defined contract. ThePreparedStatementCreator callbackinterface creates a prepared statement given aConnection provided by this class,providing SQL and any necessary parameters. The same is true for theCallableStatementCreator interface, whichcreates callable statements. TheRowCallbackHandler interface extractsvalues from each row of aResultSet.

The JdbcTemplate can be used within a DAOimplementation through direct instantiation with aDataSource reference, or be configured ina Spring IoC container and given to DAOs as a bean reference.

	Note
	The DataSource should always beconfigured as a bean in the Spring IoC container. In the first casethe bean is given to the service directly; in the second case it isgiven to the prepared template.

All SQL issued by this class is logged at theDEBUG level under the category corresponding to thefully qualified class name of the template instance (typicallyJdbcTemplate, but it may be different if you areusing a custom subclass of the JdbcTemplateclass).

12.2.1.1 Examples of JdbcTemplate class usage

This section provides some examples ofJdbcTemplate class usage. These examples arenot an exhaustive list of all of the functionality exposed by theJdbcTemplate; see the attendant Javadocs forthat.

Querying (SELECT)

Here is a simple query for getting the number of rows in arelation:

int rowCount = this.jdbcTemplate.queryForInt("select count(*) from t_actor");

A simple query using a bind variable:

int countOfActorsNamedJoe = this.jdbcTemplate.queryForInt(
        "select count(*) from t_actor where first_name = ?", "Joe");

Querying for a String:

String lastName = this.jdbcTemplate.queryForObject(
        "select last_name from t_actor where id = ?", 
        new Object[]{1212L}, String.class);

Querying and populating a single domainobject:

Actor actor = this.jdbcTemplate.queryForObject(
        "select first_name, last_name from t_actor where id = ?",
        new Object[]{1212L},
        new RowMapper<Actor>() {
            public Actor mapRow(ResultSet rs, int rowNum) throws SQLException {
                Actor actor = new Actor();
                actor.setFirstName(rs.getString("first_name"));
                actor.setLastName(rs.getString("last_name"));
                return actor;
            }
        });

Querying and populating a number of domain objects:

List<Actor> actors = this.jdbcTemplate.query(
        "select first_name, last_name from t_actor",
        new RowMapper<Actor>() {
            public Actor mapRow(ResultSet rs, int rowNum) throws SQLException {
                Actor actor = new Actor();
                actor.setFirstName(rs.getString("first_name"));
                actor.setLastName(rs.getString("last_name"));
                return actor;
            }
        });

If the last two snippets of code actually existed in the sameapplication, it would make sense to remove the duplication presentin the two RowMapper anonymous innerclasses, and extract them out into a single class (typically astatic inner class) that can then be referencedby DAO methods as needed. For example, it may be better to write thelast code snippet as follows:

public List<Actor> findAllActors() {
    return this.jdbcTemplate.query( "select first_name, last_name from t_actor", new ActorMapper());
}

private static final class ActorMapper implements RowMapper<Actor> {

    public Actor mapRow(ResultSet rs, int rowNum) throws SQLException {
        Actor actor = new Actor();
        actor.setFirstName(rs.getString("first_name"));
        actor.setLastName(rs.getString("last_name"));
        return actor;
    }        
}

Updating (INSERT/UPDATE/DELETE) with jdbcTemplate

You use the update(..) method toperform insert, update and delete operations. Parameter values areusually provided as var args or alternatively as an objectarray.

this.jdbcTemplate.update(
        "insert into t_actor (first_name, last_name) values (?, ?)", 
        "Leonor", "Watling");

this.jdbcTemplate.update(
        "update t_actor set = ? where id = ?", 
        "Banjo", 5276L);

this.jdbcTemplate.update(
        "delete from actor where id = ?",
        Long.valueOf(actorId));

Other jdbcTemplate operations

You can use the execute(..) method toexecute any arbitrary SQL, and as such the method is often used forDDL statements. It is heavily overloaded with variants takingcallback interfaces, binding variable arrays, and so on.

this.jdbcTemplate.execute("create table mytable (id integer, name varchar(100))");

The following example invokes a simple stored procedure. Moresophisticated stored procedure support is covered later.

this.jdbcTemplate.update(
        "call SUPPORT.REFRESH_ACTORS_SUMMARY(?)", 
        Long.valueOf(unionId));

12.2.1.2 JdbcTemplate best practices

Instances of the JdbcTemplate class arethreadsafe once configured. This is importantbecause it means that you can configure a single instance of aJdbcTemplate and then safely inject thisshared reference into multiple DAOs (orrepositories). The JdbcTemplate is stateful, inthat it maintains a reference to aDataSource, but this state isnot conversational state.

A common practice when using theJdbcTemplate class (and the associated SimpleJdbcTemplateand NamedParameterJdbcTemplateclasses) is to configure a DataSourcein your Spring configuration file, and then dependency-inject thatshared DataSource bean into your DAOclasses; the JdbcTemplate is created in thesetter for the DataSource. This leadsto DAOs that look in part like the following:

public class JdbcCorporateEventDao implements CorporateEventDao {

    private JdbcTemplate jdbcTemplate;

    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    // JDBC-backed implementations of the methods on the CorporateEventDao follow...
}

The corresponding configuration might look like this.

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="
        http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context-3.0.xsd">
    
    <bean id="corporateEventDao" class="com.example.JdbcCorporateEventDao">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${jdbc.driverClassName}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>

    <context:property-placeholder location="jdbc.properties"/>

</beans>

An alternative to explicit configuration is to usecomponent-scanning and annotation support for dependency injection. Inthis case you annotate the class with@Repository (which makes it a candidatefor component-scanning) and annotate theDataSource setter method with@Autowired.

@Repository
public class JdbcCorporateEventDao implements CorporateEventDao {

    private JdbcTemplate jdbcTemplate;

    @Autowired
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    // JDBC-backed implementations of the methods on the CorporateEventDao follow...
}

The corresponding XML configuration file wouldlook like the following:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="
        http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context-3.0.xsd">
    
    <!-- Scans within the base package of the application for @Components to configure as beans -->
    <context:component-scan base-package="org.springframework.docs.test" />
    
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${jdbc.driverClassName}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>

    <context:property-placeholder location="jdbc.properties"/>

</beans>

If you are using Spring'sJdbcDaoSupport class, and your variousJDBC-backed DAO classes extend from it, then your sub-class inherits asetDataSource(..) method from theJdbcDaoSupport class. Youcan choose whether to inherit from this class. TheJdbcDaoSupport class is provided as aconvenience only.

Regardless of which of the above template initialization stylesyou choose to use (or not), it is seldom necessary to create a newinstance of a JdbcTemplate class each time youwant to execute SQL. Once configured, aJdbcTemplate instance is threadsafe. You maywant multiple JdbcTemplate instances if yourapplication accesses multiple databases, which requires multipleDataSources, and subsequently multipledifferently configured JdbcTemplates.

12.2.2 NamedParameterJdbcTemplate

The NamedParameterJdbcTemplate class addssupport for programming JDBC statements using named parameters, asopposed to programming JDBC statements using only classic placeholder('?') arguments. TheNamedParameterJdbcTemplate class wraps aJdbcTemplate, and delegates to the wrappedJdbcTemplate to do much of its work. This sectiondescribes only those areas of theNamedParameterJdbcTemplate class that differ fromthe JdbcTemplate itself; namely, programming JDBCstatements using named parameters.

// some JDBC-backed DAO class...
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

public void setDataSource(DataSource dataSource) {
    this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
}

public int countOfActorsByFirstName(String firstName) {

    String sql = "select count(*) from T_ACTOR where first_name = :first_name";

    SqlParameterSource namedParameters = new MapSqlParameterSource("first_name", firstName);

    return namedParameterJdbcTemplate.queryForInt(sql, namedParameters);
}

Notice the use of the named parameter notation in the valueassigned to the sql variable, and the correspondingvalue that is plugged into the namedParametersvariable (of type MapSqlParameterSource).

Alternatively, you can pass along named parameters and theircorresponding values to aNamedParameterJdbcTemplate instance by using theMap-based style.Theremaining methods exposed by theNamedParameterJdbcOperations andimplemented by the NamedParameterJdbcTemplateclass follow a similar pattern and are not covered here.

The following example shows the use of theMap-based style.

// some JDBC-backed DAO class...
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

public void setDataSource(DataSource dataSource) {
    this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
}

public int countOfActorsByFirstName(String firstName) {

    String sql = "select count(*) from T_ACTOR where first_name = :first_name";

    Map namedParameters = Collections.singletonMap("first_name", firstName);

    return this.namedParameterJdbcTemplate.queryForInt(sql, namedParameters);
}

One nice feature related to theNamedParameterJdbcTemplate (and existing in thesame Java package) is the SqlParameterSourceinterface. You have already seen an example of an implementation of thisinterface in one of the previous code snippet (theMapSqlParameterSource class). AnSqlParameterSource is a source ofnamed parameter values to aNamedParameterJdbcTemplate. TheMapSqlParameterSource class is a very simpleimplementation that is simply an adapter around ajava.util.Map, where the keys are theparameter names and the values are the parameter values.

Another SqlParameterSourceimplementation is theBeanPropertySqlParameterSource class. This classwraps an arbitrary JavaBean (that is, an instance of a class thatadheres to the JavaBeanconventions), and uses the properties of the wrapped JavaBean asthe source of named parameter values.

public class Actor {

    private Long id;
    private String firstName;
    private String lastName;
    
    public String getFirstName() {
        return this.firstName;
    }
    
    public String getLastName() {
        return this.lastName;
    }
    
    public Long getId() {
        return this.id;
    }
    
    // setters omitted...

}

// some JDBC-backed DAO class...
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

public void setDataSource(DataSource dataSource) {
    this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
}

public int countOfActors(Actor exampleActor) {

    // notice how the named parameters match the properties of the above 'Actor' class
    String sql = 
        "select count(*) from T_ACTOR where first_name = :firstName and last_name = :lastName";

    SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(exampleActor);

    return this.namedParameterJdbcTemplate.queryForInt(sql, namedParameters);
}

Remember that theNamedParameterJdbcTemplate classwraps a classic JdbcTemplatetemplate; if you need access to the wrappedJdbcTemplate instance to access functionalityonly present in the JdbcTemplate class, you canuse the getJdbcOperations() method to accessthe wrapped JdbcTemplate through theJdbcOperations interface.

See also Section 12.2.1.2, “JdbcTemplate best practices” forguidelines on using theNamedParameterJdbcTemplate class in the contextof an application.

12.2.3 SimpleJdbcTemplate

The SimpleJdbcTemplate class wraps theclassic JdbcTemplate and leverages Java 5language features such as varargs and autoboxing.

Note

In Spring 3.0, the original JdbcTemplatealso supports Java 5-enhanced syntax with generics and varargs.However, the SimpleJdbcTemplate provides asimpler API that works best when you do not need access to all themethods that the JdbcTemplate offers. Also, because theSimpleJdbcTemplate was designed for Java 5, ithas more methods that take advantage of varargs due to differentordering of the parameters.

The value-add of the SimpleJdbcTemplateclass in the area of syntactic-sugar is best illustrated with abefore-and-after example. The next code snippet shows data access codethat uses the classic JdbcTemplate, followed by acode snippet that does the same job with theSimpleJdbcTemplate.

// classic JdbcTemplate-style...
private JdbcTemplate jdbcTemplate;

public void setDataSource(DataSource dataSource) {
    this.jdbcTemplate = new JdbcTemplate(dataSource);
}

public Actor findActor(String specialty, int age) {

    String sql = "select id, first_name, last_name from T_ACTOR" + 
            " where specialty = ? and age = ?";
    
    RowMapper<Actor> mapper = new RowMapper<Actor>() {
        public Actor mapRow(ResultSet rs, int rowNum) throws SQLException {
            Actor actor = new Actor();
            actor.setId(rs.getLong("id"));
            actor.setFirstName(rs.getString("first_name"));
            actor.setLastName(rs.getString("last_name"));
            return actor;
        }
    };

    
    // notice the wrapping up of the argumenta in an array
    return (Actor) jdbcTemplate.queryForObject(sql, new Object[] {specialty, age}, mapper);
}

Here is the same method, with theSimpleJdbcTemplate.

// SimpleJdbcTemplate-style...
private SimpleJdbcTemplate simpleJdbcTemplate;

public void setDataSource(DataSource dataSource) {
    this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
}

public Actor findActor(String specialty, int age) {

    String sql = "select id, first_name, last_name from T_ACTOR" + 
            " where specialty = ? and age = ?";
    RowMapper<Actor> mapper = new RowMapper<Actor>() {  
        public Actor mapRow(ResultSet rs, int rowNum) throws SQLException {
            Actor actor = new Actor();
            actor.setId(rs.getLong("id"));
            actor.setFirstName(rs.getString("first_name"));
            actor.setLastName(rs.getString("last_name"));
            return actor;
        }
    };

    // notice the use of varargs since the parameter values now come 
    // after the RowMapper parameter
    return this.simpleJdbcTemplate.queryForObject(sql, mapper, specialty, age);
}

See Section 12.2.1.2, “JdbcTemplate best practices” for guidelines onhow to use the SimpleJdbcTemplate class in thecontext of an application.

Note

The SimpleJdbcTemplate class only offersa subset of the methods exposed on theJdbcTemplate class. If you need to use a methodfrom the JdbcTemplate that is not defined onthe SimpleJdbcTemplate, you can always accessthe underlying JdbcTemplate by calling thegetJdbcOperations() method on theSimpleJdbcTemplate, which then allows you toinvoke the method that you want. The only downside is that the methodson the JdbcOperations interface are notgeneric, so you are back to casting and so on.

12.2.4 SQLExceptionTranslator

SQLExceptionTranslator is aninterface to be implemented by classes that can translate betweenSQLExceptions and Spring's ownorg.springframework.dao.DataAccessException,which is agnostic in regard to data access strategy. Implementations canbe generic (for example, using SQLState codes for JDBC) or proprietary(for example, using Oracle error codes) for greater precision.

SQLErrorCodeSQLExceptionTranslator is theimplementation of SQLExceptionTranslatorthat is used by default. This implementation uses specific vendor codes.It is more precise than the SQLState implementation.The error code translations are based on codes held in a JavaBean typeclass called SQLErrorCodes. This class is createdand populated by an SQLErrorCodesFactory which asthe name suggests is a factory for creatingSQLErrorCodes based on the contents of aconfiguration file named sql-error-codes.xml. This file ispopulated with vendor codes and based on theDatabaseProductName taken from theDatabaseMetaData. The codes for the acualdatabase you are using are used.

The SQLErrorCodeSQLExceptionTranslatorapplies matching rules in the following sequence:

	Note
	The SQLErrorCodesFactory is used bydefault to define Error codes and custom exception translations.They are looked up in a file namedsql-error-codes.xml from the classpath andthe matching SQLErrorCodes instance islocated based on the database name from the database metadata ofthe database in use.

1. Any custom translation implemented by a subclass. Normallythe provided concreteSQLErrorCodeSQLExceptionTranslator is usedso this rule does not apply. It only applies if you have actuallyprovided a subclass implementation.

2. Any custom implementation of theSQLExceptionTranslator interface that isprovided as thecustomSqlExceptionTranslator property ofthe SQLErrorCodes class.

3. The list of instances of theCustomSQLErrorCodesTranslation class,provided for the customTranslationsproperty of the SQLErrorCodes class, aresearched for a match.

4. Error code matching is applied.

5. Use the fallback translator.SQLExceptionSubclassTranslator is thedefault fallback translator. If this translation is not availablethen the next fallback translator is theSQLStateSQLExceptionTranslator.

You can extendSQLErrorCodeSQLExceptionTranslator:

public class CustomSQLErrorCodesTranslator extends SQLErrorCodeSQLExceptionTranslator {

    protected DataAccessException customTranslate(String task, String sql, SQLException sqlex) {
        if (sqlex.getErrorCode() == -12345) {
            return new DeadlockLoserDataAccessException(task, sqlex);
        }
        return null;
    }
}

In this example, the specific error code -12345is translated and other errors are left to be translated by the defaulttranslator implementation. To use this custom translator, it isnecessary to pass it to the JdbcTemplate throughthe method setExceptionTranslator and to use thisJdbcTemplate for all of the data accessprocessing where this translator is needed. Here is an example of howthis custom translator can be used:

private JdbcTemplate jdbcTemoplate;

public void setDataSource(DataSource dataSource) {
    // create a JdbcTemplate and set data source 
    this.jdbcTemplate = new JdbcTemplate(); 
    this.jdbcTemplate.setDataSource(dataSource); 
    // create a custom translator and set the DataSource for the default translation lookup 
    CustomSQLErrorCodesTranslator tr = new CustomSQLErrorCodesTranslator(); 
    tr.setDataSource(dataSource); 
    this.jdbcTemplate.setExceptionTranslator(tr); 
}

public void updateShippingCharge(long orderId, long pct) {
    // use the prepared JdbcTemplate for this update
    this.jdbcTemplate.update(
        "update orders" + 
            " set shipping_charge = shipping_charge * ? / 100" + 
            " where id = ?"
        pct, orderId); 
}

The custom translator is passed a data source in order to look upthe error codes in sql-error-codes.xml.

12.2.5 Executing statements

Executing an SQL statement requires very little code. You need aDataSource and aJdbcTemplate, including the conveniencemethodsthat are provided with the JdbcTemplate. Thefollowing example shows what you need to include for a minimal but fullyfunctional class that creates a new table:

import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;

public class ExecuteAStatement {

    private JdbcTemplate jdbcTemplate;

    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public void doExecute() {
        this.jdbcTemplate.execute("create table mytable (id integer, name varchar(100))");
    }
}

12.2.6 Running queries

Some query methods return a single value. To retrieve a count or aspecific value from one row, usequeryForInt(..),queryForLong(..) orqueryForObject(..). The latter converts thereturned JDBC Type to the Java class that ispassed in as an argument. If the type conversion is invalid, then anInvalidDataAccessApiUsageException isthrown. Here is an example that contains two query methods, one for anint and one that queries for aString.

import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;

public class RunAQuery {

    private JdbcTemplate jdbcTemplate;

    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }
  
    public int getCount() {
        return this.jdbcTemplate.queryForInt("select count(*) from mytable");
    }

    public String getName() {
        return (String) this.jdbcTemplate.queryForObject("select name from mytable", String.class);
    }

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }
}

In addition to the single result query methods, several methodsreturn a list with an entry for each row that the query returned. Themost generic method is queryForList(..) whichreturns a List where each entry is aMap with each entry in the maprepresenting the column value for that row. If you add a method to theabove example to retrieve a list of all the rows, it would look likethis:

private JdbcTemplate jdbcTemplate;

public void setDataSource(DataSource dataSource) {
    this.jdbcTemplate = new JdbcTemplate(dataSource);
}

public List<Map<String, Object>> getList() {
    return this.jdbcTemplate.queryForList("select * from mytable");
}

The list returned would look something like this:

[{name=Bob, id=1}, {name=Mary, id=2}]

12.2.7 Updating the database

The following example shows a column updated for a certain primarykey. In this example, an SQL statement has placeholders for rowparameters. The parameter values can be passed in as varargs oralternatively as an array of objects. Thus primitives should be wrappedin the primitive wrapper classes explicitly or using auto-boxing.

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;

public class ExecuteAnUpdate {

    private JdbcTemplate jdbcTemplate;

    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public void setName(int id, String name) {
        this.jdbcTemplate.update(
                "update mytable set name = ? where id = ?", 
                name, id);
    }
}

12.2.8 Retrieving auto-generated keys

An update() convenience method supports the retrieval of primary keys generatedby the database. This support is part of the JDBC 3.0 standard; seeChapter 13.6 of the specification for details. The method takes aPreparedStatementCreator as its first argument,and this is the way the required insert statement is specified. Theother argument is a KeyHolder, which contains thegenerated key on successful return from the update. There is not astandard single way to create an appropriatePreparedStatement (which explains why the methodsignature is the way it is). The following example works on Oracle butmay not work on other platforms:

final String INSERT_SQL = "insert into my_test (name) values(?)";
final String name = "Rob";

KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(
    new PreparedStatementCreator() {
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            PreparedStatement ps =
                connection.prepareStatement(INSERT_SQL, new String[] {"id"});
            ps.setString(1, name);
            return ps;
        }
    },
    keyHolder);

// keyHolder.getKey() now contains the generated key

12.3 Controlling database connections

12.3.1 DataSource

Spring obtains a connection to the database through aDataSource. ADataSource is part of the JDBCspecification and is a generalized connection factory. It allows acontainer or a framework to hide connection pooling and transactionmanagement issues from the application code. As a developer, you neednot know details about how to connect to the database; that is theresponsibility of the administrator that sets up the datasource. Youmost likely fill both roles as you develop and test code, but you do notnecessarily have to know how the production data source isconfigured.

When using Spring's JDBC layer, you obtain a data source from JNDIor you configure your own with a connection pool implementation providedby a third party. Popular implementations are Apache Jakarta CommonsDBCP and C3P0. Implementations in the Spring distribution are meant onlyfor testing purposes and do not provide pooling.

This section uses Spring'sDriverManagerDataSource implementation, andseveral additional implementations are covered later.

	Note
	Only use the DriverManagerDataSourceclass should only be used for testing purposes since it does notprovide pooling and will perform poorly when multiple requests for aconnection are made.

You obtain a connection withDriverManagerDataSource as you typically obtain aJDBC connection. Specify the fully qualified classname of the JDBCdriver so that the DriverManager can load thedriver class. Next, provide a URL that varies between JDBC drivers.(Consult the documentation for your driver for the correct value.) Thenprovide a username and a password to connect to the database. Here is anexample of how to configure aDriverManagerDataSource in Java code:

DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("org.hsqldb.jdbcDriver");
dataSource.setUrl("jdbc:hsqldb:hsql://localhost:");
dataSource.setUsername("sa");
dataSource.setPassword("");

Here is the corresponding XML configuration:

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="${jdbc.driverClassName}"/>
    <property name="url" value="${jdbc.url}"/>
    <property name="username" value="${jdbc.username}"/>
    <property name="password" value="${jdbc.password}"/>
</bean>

<context:property-placeholder location="jdbc.properties"/>

The following examples show the basic connectivity andconfiguration for DBCP and C3P0. To learn about more options that helpcontrol the pooling features, see the product documentation for therespective connection pooling implementations.

DBCP configuration:

<bean id="dataSource" 
        class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="${jdbc.driverClassName}"/>
    <property name="url" value="${jdbc.url}"/>
    <property name="username" value="${jdbc.username}"/>
    <property name="password" value="${jdbc.password}"/>
</bean>

<context:property-placeholder location="jdbc.properties"/>

C3P0 configuration:

<bean id="dataSource"
        class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <property name="driverClass" value="${jdbc.driverClassName}"/>
    <property name="jdbcUrl" value="${jdbc.url}"/>
    <property name="user" value="${jdbc.username}"/>
    <property name="password" value="${jdbc.password}"/>
</bean>

<context:property-placeholder location="jdbc.properties"/>

12.3.2 DataSourceUtils

The DataSourceUtils class is a convenientand powerful helper class that provides staticmethods to obtain connections from JNDI and close connections ifnecessary. It supports thread-bound connections with, for example,DataSourceTransactionManager.

12.3.3 SmartDataSource

The SmartDataSource interfaceshould be implemented by classes that can provide a connection to arelational database. It extends theDataSource interface to allow classesusing it to query whether the connection should be closed after a givenoperation. This usage is efficient when you know that you will reuse aconnection.

12.3.4 AbstractDataSource

AbstractDataSource is anabstract base class forSpring's DataSource implementations thatimplements code that is common to all DataSourceimplementations.You extend the AbstractDataSource class if youare writing your own DataSourceimplementation.

12.3.5 SingleConnectionDataSource

The SingleConnectionDataSource class is animplementation of the SmartDataSourceinterface that wraps a singleConnection that isnot closed after each use. Obviously, this is notmulti-threading capable.

If any client code calls close in theassumption of a pooled connection, as when using persistence tools, setthe suppressClose property totrue. Thissetting returns a close-suppressing proxy wrapping the physicalconnection. Be aware that you will not be able to cast thisto a native Oracle Connection or the likeanymore.

This is primarily a test class. For example, it enables easytesting of code outside an application server, in conjunction with asimple JNDI environment. In contrast toDriverManagerDataSource, it reuses the sameconnection all the time, avoiding excessive creation of physicalconnections.

12.3.6 DriverManagerDataSource

The DriverManagerDataSource class is animplementation of the standard DataSourceinterface that configures a plain JDBC driver through bean properties,and returns a new Connection everytime.

This implementation is useful for test and stand-aloneenvironments outside of a Java EE container, either as aDataSource bean in a Spring IoCcontainer, or in conjunction with a simple JNDI environment.Pool-assuming Connection.close() calls will simplyclose the connection, so anyDataSource-aware persistence code shouldwork. However, using JavaBean-style connection pools such ascommons-dbcp is so easy, even in a test environment, thatit is almost always preferable to use such a connection pool overDriverManagerDataSource.

12.3.7 TransactionAwareDataSourceProxy

TransactionAwareDataSourceProxy is a proxyfor a target DataSource, which wraps thattarget DataSource to add awareness ofSpring-managed transactions. In this respect, it is similar to atransactional JNDI DataSource as providedby a Java EE server.

Note

It is rarely desirable to use this class, except when alreadyexisting code that must be called and passed a standard JDBCDataSource interface implementation. Inthis case, it's possible to still have this code be usable, and at thesame time have this code participating in Spring managed transactions.Itis generally preferable to write your own new code using the higherlevel abstractions for resource management, such asJdbcTemplate orDataSourceUtils.

(See theTransactionAwareDataSourceProxy Javadocs for moredetails.)

12.3.8 DataSourceTransactionManager

The DataSourceTransactionManager class is aPlatformTransactionManager implementationfor single JDBC datasources. It binds a JDBC connection from thespecified data source to the currently executing thread, potentiallyallowing for one thread connection per data source.

Application code is required toretrieve the JDBC connection throughDataSourceUtils.getConnection(DataSource) instead ofJava EE's standard DataSource.getConnection. Itthrows unchecked org.springframework.dao exceptionsinstead of checked SQLExceptions. Allframework classes like JdbcTemplate use thisstrategy implicitly. If not used with this transaction manager, thelookup strategy behaves exactly like the common one - it can thus beused in any case.

The DataSourceTransactionManager classsupports custom isolation levels, and timeouts that get applied asappropriate JDBC statement query timeouts. To support the latter,application code must either use JdbcTemplate orcall the DataSourceUtils.applyTransactionTimeout(..)method for each created statement.

This implementation can be used instead ofJtaTransactionManager in the single resourcecase, as it does not require the container to support JTA. Switchingbetween both is just a matter of configuration, if you stick to therequired connection lookup pattern. JTA does not support customisolation levels!

12.3.9 NativeJdbcExtractor

Sometimes you need to access vendor specific JDBC methods thatdiffer from the standard JDBC API. This can be problematic if you arerunning in an application server or with aDataSource that wraps theConnection, Statement andResultSet objects with its own wrapper objects.To gain access to the native objects you can configure yourJdbcTemplate orOracleLobHandler with aNativeJdbcExtractor.

The NativeJdbcExtractor comes in a variety of flavorsto match your execution environment:

• SimpleNativeJdbcExtractor

• C3P0NativeJdbcExtractor

• CommonsDbcpNativeJdbcExtractor

• JBossNativeJdbcExtractor

• WebLogicNativeJdbcExtractor

• WebSphereNativeJdbcExtractor

• XAPoolNativeJdbcExtractor

Usually the SimpleNativeJdbcExtractor issufficient for unwrapping a Connection object inmost environments. See the Javadocs for more details.

12.4 JDBC batch operations

Most JDBC drivers provide improved performance if you batch multiplecalls to the same prepared statement. By grouping updates into batches youlimit the number of round trips to the database. This section covers batchprocessing using both the JdbcTemplate and theSimpleJdbcTemplate.

12.4.1 Batch operations with the JdbcTemplate

You accomplish JdbcTemplate batchprocessing by implementing two methods of a special interface,BatchPreparedStatementSetter, and passing that inas the second parameter in your batchUpdatemethod call. Use the getBatchSize method toprovide the size of the current batch. Use thesetValues method to set the values for theparameters of the prepared statement. This method will be called thenumber of times that you specified in thegetBatchSize call. The following example updatesthe actor table based on entries in a list. The entire list is used asthe batch in this example:

public class JdbcActorDao implements ActorDao {
    private JdbcTemplate jdbcTemplate;

    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public int[] batchUpdate(final List<Actor> actors) {
        int[] updateCounts = jdbcTemplate.batchUpdate(
                "update t_actor set first_name = ?, last_name = ? where id = ?",
                new BatchPreparedStatementSetter() {
                    public void setValues(PreparedStatement ps, int i) throws SQLException {
                        ps.setString(1, actors.get(i).getFirstName());
                        ps.setString(2, actors.get(i).getLastName());
                        ps.setLong(3, actors.get(i).getId().longValue());
                    }

                    public int getBatchSize() {
                        return actors.size();
                    }
                } );
        return updateCounts;
    }

    //  ... additional methods
}

If you are processing a stream of updates or reading from afile, then you might have a preferred batch size, but the last batchmight not have that number of entries. In this case you can use theInterruptibleBatchPreparedStatementSetterinterface, which allows you to interrupt a batch once the input sourceis exhausted. The isBatchExhausted method allowsyou to signal the end of the batch.

12.4.2 Batch operations with the SimpleJdbcTemplate

The SimpleJdbcTemplate provides analternate way of providing the batch update. Instead of implementing aspecial batch interface, you provide all parameter values in the call.The framework loops over these values and uses an internal preparedstatement setter. The API varies depending on whether you use namedparameters. For the named parameters you provide an array ofSqlParameterSource, one entry for each member ofthe batch. You can use theSqlParameterSource.createBatch method to createthis array, passing in either an array of JavaBeans or an array of Mapscontaining the parameter values.

This example shows a batch update using named parameters:

public class JdbcActorDao implements ActorDao {
    private SimpleJdbcTemplate simpleJdbcTemplate;

    public void setDataSource(DataSource dataSource) {
        this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
    }

    public int[] batchUpdate(final List<Actor> actors) {
        SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(actors.toArray());
        int[] updateCounts = simpleJdbcTemplate.batchUpdate(
            "update t_actor set first_name = :firstName, last_name = :lastName where id = :id",
            batch);
        return updateCounts;
    }

    //  ... additional methods
}

For an SQL statement using the classic "?" placeholders, youpass in a list containing an object array with the update values. Thisobject array must have one entry for each placeholder in the SQLstatement, and they must be in the same order as they are defined in theSQL statement.

The same example using classic JDBC "?" placeholders:

public class JdbcActorDao implements ActorDao {
    private SimpleJdbcTemplate simpleJdbcTemplate;

    public void setDataSource(DataSource dataSource) {
        this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
    }

    public int[] batchUpdate(final List<Actor> actors) {
        List<Object[]> batch = new ArrayList<Object[]>();
        for (Actor actor : actors) {
            Object[] values = new Object[] {
                    actor.getFirstName(),
                    actor.getLastName(),
                    actor.getId()};
            batch.add(values);
        }
        int[] updateCounts = simpleJdbcTemplate.batchUpdate(
                "update t_actor set first_name = ?, last_name = ? where id = ?",
                batch);
        return updateCounts;
    }

    //  ... additional methods
}

All batch update methods return an int array containing thenumber of affected rows for each batch entry. This count is reported bythe JDBC driver. If the count is not available, the JDBC driver returnsa -2 value.

12.5 Simplifying JDBC operations with the SimpleJdbc classes

The SimpleJdbcInsert andSimpleJdbcCall classes provide a simplifiedconfiguration by taking advantage of database metadata that can beretrieved through the JDBC driver. This means there is less to configureup front, although you can override or turn off the metadata processing ifyou prefer to provide all the details in your code.

12.5.1 Inserting data using SimpleJdbcInsert

Let's start by looking at theSimpleJdbcInsert class with the minimal amount ofconfiguration options. You should instantiate theSimpleJdbcInsert in the data access layer'sinitialization method. For this example, the initializing method is thesetDataSource method. You do not need to subclassthe SimpleJdbcInsert class; simply create a newinstance and set the table name using thewithTableName method. Configuration methods forthis class follow the "fluid" style that returns the instance of theSimpleJdbcInsert, which allows you to chain allconfiguration methods. This example uses only one configuration method;you will see examples of multiple ones later.

public class JdbcActorDao implements ActorDao {
    private SimpleJdbcTemplate simpleJdbcTemplate;
    private SimpleJdbcInsert insertActor;

    public void setDataSource(DataSource dataSource) {
        this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
        this.insertActor = 
                new SimpleJdbcInsert(dataSource).withTableName("t_actor");
    }

    public void add(Actor actor) {
        Map<String, Object> parameters = new HashMap<String, Object>(3);
        parameters.put("id", actor.getId());
        parameters.put("first_name", actor.getFirstName());
        parameters.put("last_name", actor.getLastName());
        insertActor.execute(parameters);
    }

    //  ... additional methods
}

The execute method used here takes a plainjava.utils.Map as its only parameter. Theimportant thing to note here is that the keys used for the Map mustmatch the column names of the table as defined in the database. This isbecause we read the metadata in order to construct the actual insertstatement.

12.5.2 Retrieving auto-generated keys using SimpleJdbcInsert

This example uses the same insert as the preceding, but instead ofpassing in the id it retrieves the auto-generated key and sets it on thenew Actor object. When you create theSimpleJdbcInsert, in addition to specifying thetable name, you specify the name of the generated key column with theusingGeneratedKeyColumns method.

public class JdbcActorDao implements ActorDao {
    private SimpleJdbcTemplate simpleJdbcTemplate;
    private SimpleJdbcInsert insertActor;

    public void setDataSource(DataSource dataSource) {
        this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
        this.insertActor =
                new SimpleJdbcInsert(dataSource)
                        .withTableName("t_actor")
                        .usingGeneratedKeyColumns("id");
    }

    public void add(Actor actor) {
        Map<String, Object> parameters = new HashMap<String, Object>(2);
        parameters.put("first_name", actor.getFirstName());
        parameters.put("last_name", actor.getLastName());
        Number newId = insertActor.executeAndReturnKey(parameters);
        actor.setId(newId.longValue());
    }

    //  ... additional methods
}

The main difference when executing the insert by this secondapproach is that you do not add the id to the Map and you call theexecuteReturningKey method. This returns ajava.lang.Number object with which you can create aninstance of the numerical type that is used in our domain class.Youcannot rely on all databases to return a specific Java class here;java.lang.Number is the base class that you can relyon. If you have multiple auto-generated columns, or the generated valuesare non-numeric, then you can use a KeyHolder that isreturned from the executeReturningKeyHoldermethod.

12.5.3 Specifying columns for a SimpleJdbcInsert

You can limit the columns for an insert by specifying a list ofcolumn names with the usingColumns method:

public class JdbcActorDao implements ActorDao {
    private SimpleJdbcTemplate simpleJdbcTemplate;
    private SimpleJdbcInsert insertActor;

    public void setDataSource(DataSource dataSource) {
        this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
        this.insertActor =
                new SimpleJdbcInsert(dataSource)
                        .withTableName("t_actor")
                        .usingColumns("first_name", "last_name")
                        .usingGeneratedKeyColumns("id");
    }

    public void add(Actor actor) {
        Map<String, Object> parameters = new HashMap<String, Object>(2);
        parameters.put("first_name", actor.getFirstName());
        parameters.put("last_name", actor.getLastName());
        Number newId = insertActor.executeAndReturnKey(parameters);
        actor.setId(newId.longValue());
    }

    //  ... additional methods
}

The execution of the insert is the same as if you had reliedon the metadata to determine which columns to use.

12.5.4 Using SqlParameterSource to provide parameter values

Using a Map to provide parameter valuesworks fine, but it's not the most convenient class to use. Springprovides a couple of implementations of theSqlParameterSource interface that can be usedinstead.Thefirst one is BeanPropertySqlParameterSource,which is a very convenient class if you have a JavaBean-compliant classthat contains your values. It will use the corresponding getter methodto extract the parameter values. Here is an example:

public class JdbcActorDao implements ActorDao {
    private SimpleJdbcTemplate simpleJdbcTemplate;
    private SimpleJdbcInsert insertActor;

    public void setDataSource(DataSource dataSource) {
        this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
        this.insertActor =
                new SimpleJdbcInsert(dataSource)
                        .withTableName("t_actor")
                        .usingGeneratedKeyColumns("id");
    }

    public void add(Actor actor) {
        SqlParameterSource parameters = new BeanPropertySqlParameterSource(actor);
        Number newId = insertActor.executeAndReturnKey(parameters);
        actor.setId(newId.longValue());
    }

    //  ... additional methods
}

Another option is theMapSqlParameterSource that resembles a Map butprovides a more convenient addValue method thatcan be chained.

public class JdbcActorDao implements ActorDao {
    private SimpleJdbcTemplate simpleJdbcTemplate;
    private SimpleJdbcInsert insertActor;

    public void setDataSource(DataSource dataSource) {
        this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
        this.insertActor =
                new SimpleJdbcInsert(dataSource)
                        .withTableName("t_actor")
                        .usingGeneratedKeyColumns("id");
    }

    public void add(Actor actor) {
        SqlParameterSource parameters = new MapSqlParameterSource()
                .addValue("first_name", actor.getFirstName())
                .addValue("last_name", actor.getLastName());
        Number newId = insertActor.executeAndReturnKey(parameters);
        actor.setId(newId.longValue());
    }

    //  ... additional methods
}

As you can see, the configuration is the same; only theexecuting code has to change to use these alternative inputclasses.

12.5.5 Calling a stored procedure with SimpleJdbcCall

The SimpleJdbcCall class leverages metadatain the database to look up names of in and outparameters, so that you do not have to declare them explicitly. You candeclare parameters if you prefer to do that, or if you have parameterssuch as ARRAY or STRUCT that do not have anautomatic mapping to a Java class. The first example shows a simpleprocedure that returns only scalar values in VARCHAR andDATE format from a MySQL database. The example procedurereads a specified actor entry and returns first_name,last_name, and birth_date columns in the formof out parameters.

CREATE PROCEDURE read_actor ( 
  IN in_id INTEGER, 
  OUT out_first_name VARCHAR(100), 
  OUT out_last_name VARCHAR(100), 
  OUT out_birth_date DATE) 
BEGIN 
  SELECT first_name, last_name, birth_date 
  INTO out_first_name, out_last_name, out_birth_date 
  FROM t_actor where id = in_id;
END;

The in_id parameter contains theid of the actor you are looking up. The outparameters return the data read from the table.

The SimpleJdbcCall is declared in a similarmanner to the SimpleJdbcInsert. You shouldinstantiate and configure the class in the initialization method of yourdata access layer. Compared to the StoredProcedure class, you don'thave to create a subclass and you don't have to declare parameters thatcan be looked up in the database metadata. Followingis an example of a SimpleJdbcCall configuration using the above storedprocedure. The only configuration option, in addition to theDataSource, is the name of the storedprocedure.

public class JdbcActorDao implements ActorDao {
    private SimpleJdbcTemplate simpleJdbcTemplate;
    private SimpleJdbcCall procReadActor;

    public void setDataSource(DataSource dataSource) {
        this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
        this.procReadActor =
                new SimpleJdbcCall(dataSource)
                        .withProcedureName("read_actor");
    }

    public Actor readActor(Long id) {
        SqlParameterSource in = new MapSqlParameterSource()
                .addValue("in_id", id); 
        Map out = procReadActor.execute(in);
        Actor actor = new Actor();
        actor.setId(id);
        actor.setFirstName((String) out.get("out_first_name"));
        actor.setLastName((String) out.get("out_last_name"));
        actor.setBirthDate((Date) out.get("out_birth_date"));
        return actor;
    }

    //  ... additional methods
}

The code you write for the execution of the call involvescreating an SqlParameterSource containing the INparameter. It'simportant to match the name provided for the input value with that ofthe parameter name declaredin the stored procedure. The case does not have to match because you usemetadata to determine how database objects should be referred to in astored procedure. What is specified in the source for the storedprocedure is not necessarily the way it is stored in the database. Somedatabases transform names to all upper case while others use lower caseor use the case as specified.

The execute method takes the IN parametersand returns a Map containing any out parameters keyed bythe name as specified in the stored procedure. In this case they areout_first_name, out_last_name andout_birth_date.

The last part of the execute method createsan Actor instance to use to return the data retrieved. Again, it isimportant to use the names of the out parameters as theyare declared in the stored procedure. Also,the case in the names of the out parameters stored in theresults map matches that of the out parameter names in thedatabase, which could vary between databases. Tomake your code more portable you should do a case-insensitive lookup orinstruct Spring to use a CaseInsensitiveMap fromthe Jakarta Commons project. To do the latter, you create your ownJdbcTemplate and set thesetResultsMapCaseInsensitive property totrue. Then you pass this customizedJdbcTemplate instance into the constructor ofyour SimpleJdbcCall. You must include thecommons-collections.jar in your classpath forthis to work. Here is an example of this configuration:

public class JdbcActorDao implements ActorDao {
    private SimpleJdbcCall procReadActor;

    public void setDataSource(DataSource dataSource) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.setResultsMapCaseInsensitive(true);
        this.procReadActor =
                new SimpleJdbcCall(jdbcTemplate)
                        .withProcedureName("read_actor");
    }


    //  ... additional methods
}

By taking this action, you avoid conflicts in the case usedfor the names of your returned out parameters.

12.5.6 Explicitly declaring parameters to use for aSimpleJdbcCall

You have seen how the parameters are deduced based on metadata,but you can declare then explicitly if you wish. You do this by creatingand configuring SimpleJdbcCall with thedeclareParameters method, which takes a variablenumber of SqlParameter objects as input. See thenext section for details on how to define anSqlParameter.

	Note
	Explicit declarations are necessary if the database you use isnot a Spring-supported database. Currently Spring supports metadatalookup of stored procedure calls for the following databases: ApacheDerby, DB2, MySQL, Microsoft SQL Server, Oracle, and Sybase. We alsosupport metadata lookup of stored functions for: MySQL, MicrosoftSQL Server, and Oracle.

You can opt to declare one, some, or all the parametersexplicitly. The parameter metadata is still used where you do notdeclare parameters explicitly. Tobypass all processing of metadata lookups for potential parameters andonly use the declared parameters, you call the methodwithoutProcedureColumnMetaDataAccess as part ofthe declaration. Suppose that you have two or more different callsignatures declared for a database function. In this case you call theuseInParameterNames to specify the list of INparameter names to include for a given signature.

The following example shows a fully declared procedure call, usingthe information from the preceding example.

public class JdbcActorDao implements ActorDao {
    private SimpleJdbcCall procReadActor;

    public void setDataSource(DataSource dataSource) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.setResultsMapCaseInsensitive(true);
        this.procReadActor =
                new SimpleJdbcCall(jdbcTemplate)
                        .withProcedureName("read_actor")
                        .withoutProcedureColumnMetaDataAccess()
                        .useInParameterNames("in_id")
                        .declareParameters(
                                new SqlParameter("in_id", Types.NUMERIC),
                                new SqlOutParameter("out_first_name", Types.VARCHAR),
                                new SqlOutParameter("out_last_name", Types.VARCHAR),
                                new SqlOutParameter("out_birth_date", Types.DATE)
                        );
    }


    //  ... additional methods
}

The execution and end results of the two examples are thesame; this one specifies all details explicitly rather than relying onmetadata.

12.5.7 How to define SqlParameters

To define a parameter for the SimpleJdbc classes and also for theRDBMS operations classes, covered in Section 12.6, “Modeling JDBC operations as Java objects”,youuse an SqlParameter or one of its subclasses. Youtypically specify the parameter name and SQL type in the constructor.The SQL type is specified using thejava.sql.Types constants. We have already seendeclarations like:

   new SqlParameter("in_id", Types.NUMERIC),
   new SqlOutParameter("out_first_name", Types.VARCHAR),

The first line with the SqlParameterdeclares an IN parameter. IN parameters can be used for both storedprocedure calls and for queries using theSqlQuery and its subclasses covered in thefollowing section.

The second line with the SqlOutParameterdeclares an out parameter to be used in a stored procedurecall. There is also an SqlInOutParameter forInOut parameters, parameters that provide anIN value to the procedure and that also return avalue.

	Note
	Only parameters declared as SqlParameterand SqlInOutParameter will be used to provideinput values. This is different from theStoredProcedure class, which for backwardscompatibility reasons allows input values to be provided forparameters declared as SqlOutParameter.

For IN parameters, in addition to the name and the SQL type, youcan specify a scale for numeric data or a type name for custom databasetypes. For out parameters, you can provide aRowMapper to handle mapping of rows returned froma REF cursor. Another option is to specify anSqlReturnType that provides an opportunity todefine customized handling of the return values.

12.5.8 Calling a stored function using SimpleJdbcCall

You call a stored function in almost the same way as you call astored procedure, except that you provide a function name rather than aprocedure name. You use the withFunctionNamemethod as part of the configuration to indicate that we want to make acall to a function, and the corresponding string for a function call isgenerated. A specialized execute call,executeFunction, is used to execute the functionand it returns the function return value as an object of a specifiedtype, which means you do not have to retrieve the return value from theresults map. Asimilar convenience method named executeObject isalso available for stored procedures that only have one outparameter. The following example is based on a stored function namedget_actor_name that returns an actor's full name.Here is the MySQL source for this function:

CREATE FUNCTION get_actor_name (in_id INTEGER)
RETURNS VARCHAR(200) READS SQL DATA 
BEGIN
  DECLARE out_name VARCHAR(200);
  SELECT concat(first_name, ' ', last_name)
    INTO out_name
    FROM t_actor where id = in_id;
  RETURN out_name;
END;

To call this function we again create aSimpleJdbcCall in the initializationmethod.

public class JdbcActorDao implements ActorDao {
    private SimpleJdbcTemplate simpleJdbcTemplate;
    private SimpleJdbcCall funcGetActorName;

    public void setDataSource(DataSource dataSource) {
        this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.setResultsMapCaseInsensitive(true);
        this.funcGetActorName =
                new SimpleJdbcCall(jdbcTemplate)
                        .withFunctionName("get_actor_name");
    }

    public String getActorName(Long id) {
        SqlParameterSource in = new MapSqlParameterSource()
                .addValue("in_id", id); 
        String name = funcGetActorName.executeFunction(String.class, in);
        return name;
    }

    //  ... additional methods
}

The execute method usedreturns a String containing the return value fromthe function call.

12.5.9 Returning ResultSet/REF Cursor from a SimpleJdbcCall

Calling a stored procedure or function that returns a result setis a bit tricky. Some databases return result sets during the JDBCresults processing while others require an explicitly registeredout parameter of a specific type. Both approaches needadditional processing to loop over the result set and process thereturned rows. With the SimpleJdbcCall you usethe returningResultSet method and declare aRowMapper implementation to be used for aspecific parameter. In the case where the result set is returned duringthe results processing, there are no names defined, so the returnedresults will have to match the order in which you declare theRowMapper implementations. The name specified isstill used to store the processed list of results in the results mapthat is returned from the execute statement.

The next example uses a stored procedure that takes no INparameters and returns all rows from the t_actor table. Here is theMySQL source for this procedure:

CREATE PROCEDURE read_all_actors()
BEGIN
 SELECT a.id, a.first_name, a.last_name, a.birth_date FROM t_actor a;
END;

To call this procedure you declare theRowMapper. Because the class you want to map tofollows the JavaBean rules, you can use aParameterizedBeanPropertyRowMapper that iscreated by passing in the required class to map to in thenewInstance method.

public class JdbcActorDao implements ActorDao {
    private SimpleJdbcTemplate simpleJdbcTemplate;
    private SimpleJdbcCall procReadAllActors;

    public void setDataSource(DataSource dataSource) {
        this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.setResultsMapCaseInsensitive(true);
        this.procReadAllActors =
                new SimpleJdbcCall(jdbcTemplate)
                        .withProcedureName("read_all_actors")
                        .returningResultSet("actors",
                                ParameterizedBeanPropertyRowMapper.newInstance(Actor.class));
    }

    public List getActorsList() {
        Map m = procReadAllActors.execute(new HashMap<String, Object>(0));
        return (List) m.get("actors");
    }

    //  ... additional methods
}

The execute call passes in an empty Map because this calldoes not take any parameters. The list of Actors is then retrieved fromthe results map and returned to the caller.

12.6 Modeling JDBC operations as Java objects

The org.springframework.jdbc.object packagecontains classes that allow you to access the database in a moreobject-oriented manner. As an example, you can execute queries and get theresults back as a list containing business objects with the relationalcolumn data mapped to the properties of the business object. You can alsoexecute stored procedures and run update, delete, and insertstatements.

Note

Many Spring developers believe that the various RDBMS operationclasses described below (with the exception of the StoredProcedureclass) can often be replaced with straightJdbcTemplate calls. Often it is simpler to writea DAO method that simply calls a method on aJdbcTemplate directly (as opposed toencapsulating a query as a full-blown class). However, if you are getting measurable value from using the RDBMSoperation classes, continue using these classes.

12.6.1 SqlQuery

SqlQuery is a reusable, threadsafe classthat encapsulates an SQL query. Subclasses must implement thenewRowMapper(..) method to provide aRowMapper instance that can create oneobject per row obtained from iterating over theResultSet that is created during theexecution of the query. The SqlQuery class israrely used directly because the MappingSqlQuerysubclass provides a much more convenient implementation for mapping rowsto Java classes. Other implementations that extendSqlQuery areMappingSqlQueryWithParameters andUpdatableSqlQuery.

12.6.2 MappingSqlQuery

MappingSqlQuery is a reusable query inwhich concrete subclasses must implement the abstractmapRow(..) method to convert each row of thesupplied ResultSet into an object of thetype specified. The following example shows a custom query that maps thedata from the t_actor relation to an instance of theActor class.

public class ActorMappingQuery extends MappingSqlQuery<Actor> {

    public ActorMappingQuery(DataSource ds) {
        super(ds, "select id, first_name, last_name from t_actor where id = ?");
        super.declareParameter(new SqlParameter("id", Types.INTEGER));
        compile();
    }

    @Override
    protected Actor mapRow(ResultSet rs, int rowNumber) throws SQLException {
        Actor actor = new Actor();
        actor.setId(rs.getLong("id"));
        actor.setFirstName(rs.getString("first_name"));
        actor.setLastName(rs.getString("last_name"));
        return actor;
    }

}

The class extends MappingSqlQueryparameterized with the Actor type. Theconstructor for this customer query takes theDataSource as the only parameter. In thisconstructor you call the constructor on the superclass with theDataSource and the SQL that should beexecuted to retrieve the rows for this query. This SQL will be used tocreate a PreparedStatement so it maycontain place holders for any parameters to be passed in duringexecution.Youmust declare each parameter using thedeclareParameter method passing in anSqlParameter. TheSqlParameter takes a name and the JDBC type asdefined in java.sql.Types. After you define allparameters, you call the compile() method so thestatement can be prepared and later executed. This class is thread-safeafter it is compiled, so as long as these instancesare created when the DAO is initialized they can be kept as instancevariables and be reused.

private ActorMappingQuery actorMappingQuery;

@Autowired
public void setDataSource(DataSource dataSource) {
    this.actorMappingQuery = new ActorMappingQuery(dataSource);
}

public Customer getCustomer(Long id) {
    return actorMappingQuery.findObject(id);
}

The method in this example retrieves the customer with the id thatis passed in as the only parameter. Since we only want one objectreturned we simply call the convenience method findObjectwith the id as parameter. If we had instead a query that returned a listof objects and took additional parameters then we would use one of theexecute methods that takes an array of parameter values passed in asvarargs.

public List<Actor> searchForActors(int age, String namePattern) {
    List<Actor> actors = actorSearchMappingQuery.execute(age, namePattern);
    return actors;
}

12.6.3 SqlUpdate

The SqlUpdate class encapsulates an SQLupdate. Like a query, an update object is reusable, and like allRdbmsOperation classes, an update can haveparameters and is defined in SQL. This class provides a number ofupdate(..) methods analogous to theexecute(..) methods of query objects. TheSQLUpdate class is concrete. It can besubclassed, for example, to add a custom update method, as in thefollowing snippet where it's simply calledexecute. However,you don't have to subclass the SqlUpdate classsince it can easily be parameterized by setting SQL and declaringparameters.

import java.sql.Types;

import javax.sql.DataSource;

import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.SqlUpdate;

public class UpdateCreditRating extends SqlUpdate {

    public UpdateCreditRating(DataSource ds) {
        setDataSource(ds);
        setSql("update customer set credit_rating = ? where id = ?");
        declareParameter(new SqlParameter("creditRating", Types.NUMERIC));
        declareParameter(new SqlParameter("id", Types.NUMERIC));
        compile();
    }

    /**
     * @param id for the Customer to be updated
     * @param rating the new value for credit rating
     * @return number of rows updated
     */
    public int execute(int id, int rating) {
        return update(rating, id);
    }
}

12.6.4 StoredProcedure

The StoredProcedure class is a superclassfor object abstractions of RDBMS stored procedures. This class isabstract, and its variousexecute(..) methods have protectedaccess, preventing use other than through a subclass that offers tightertyping.

The inherited sql property will be the name ofthe stored procedure in the RDBMS.

To define a parameter for theStoredProcedure class, you use anSqlParameter or one of its subclasses. You mustspecify the parameter name and SQL type in the constructor like in thefollowing code snippet. The SQL type is specified using thejava.sql.Types constants.

   new SqlParameter("in_id", Types.NUMERIC),
   new SqlOutParameter("out_first_name", Types.VARCHAR),

The first line with the SqlParameterdeclares an IN parameter. IN parameters can be used for both storedprocedure calls and for queries using theSqlQuery and its subclasses covered in thefollowing section.

The second line with the SqlOutParameterdeclares an out parameter to be used in the storedprocedure call. There is also anSqlInOutParameter forInOut parameters, parameters that provide anin value to the procedure and that also return avalue.

For in parameters, in addition to thename and the SQL type, you can specify a scale for numeric data or atype name for custom database types. For out parameters youcan provide a RowMapper to handle mapping of rowsreturned from a REF cursor. Another option is to specify anSqlReturnType that enables you to definecustomized handling of the return values.

Here is an example of a simple DAO that uses aStoredProcedure to call a function,sysdate(),which comes with any Oracle database. Touse the stored procedure functionality you have to create a class thatextends StoredProcedure. In this example, theStoredProcedure class is an inner class, but ifyou need to reuse the StoredProcedure you declareit as a top-level class. This example has no input parameters, but anoutput parameter is declared as a date type using the classSqlOutParameter. The execute()method executes the procedure and extracts the returned date from theresults Map. The resultsMap has an entry for each declared outputparameter, in this case only one, using the parameter name as thekey.

import java.sql.Types;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.object.StoredProcedure;

public class StoredProcedureDao {

    private GetSysdateProcedure getSysdate;
    
    @Autowired
    public void init(DataSource dataSource) {
        this.getSysdate = new GetSysdateProcedure(dataSource);
    }
    
    public Date getSysdate() {
        return getSysdate.execute();
    }

    private class GetSysdateProcedure extends StoredProcedure {
        
        private static final String SQL = "sysdate";

        public GetSysdateProcedure(DataSource dataSource) {
            setDataSource(dataSource);
            setFunction(true);
            setSql(SQL);
            declareParameter(new SqlOutParameter("date", Types.DATE));
            compile();
        }

        public Date execute() {
            // the 'sysdate' sproc has no input parameters, so an empty Map is supplied...
            Map<String, Object> results = execute(new HashMap<String, Object>());
            Date sysdate = (Date) results.get("date");
            return sysdate;    
        }
    }

}

The following example of a StoredProcedurehas two output parameters (in this case, Oracle REF cursors).

import oracle.jdbc.OracleTypes;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.object.StoredProcedure;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

public class TitlesAndGenresStoredProcedure extends StoredProcedure {

    private static final String SPROC_NAME = "AllTitlesAndGenres";

    public TitlesAndGenresStoredProcedure(DataSource dataSource) {
        super(dataSource, SPROC_NAME);
        declareParameter(new SqlOutParameter("titles", OracleTypes.CURSOR, new TitleMapper()));
        declareParameter(new SqlOutParameter("genres", OracleTypes.CURSOR, new GenreMapper()));
        compile();
    }

    public Map<String, Object> execute() {
        // again, this sproc has no input parameters, so an empty Map is supplied
        return super.execute(new HashMap<String, Object>());
    }
}

Notice how the overloaded variants of thedeclareParameter(..) method that have been used inthe TitlesAndGenresStoredProcedure constructorare passed RowMapper implementationinstances; this is a very convenient and powerful way to reuse existingfunctionality. The code for the twoRowMapper implementations is providedbelow.

The TitleMapper class maps aResultSet to aTitle domain object for each row in the suppliedResultSet:

import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;

import com.foo.domain.Title;

public final class TitleMapper implements RowMapper<Title> {
    
    public Title mapRow(ResultSet rs, int rowNum) throws SQLException {
        Title title = new Title();
        title.setId(rs.getLong("id"));
        title.setName(rs.getString("name"));
        return title;
    }
}

The GenreMapper class maps aResultSet to aGenre domain object for each row in the suppliedResultSet.

import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;

import com.foo.domain.Genre;

public final class GenreMapper implements RowMapper<Genre> {
    
    public Genre mapRow(ResultSet rs, int rowNum) throws SQLException {
        return new Genre(rs.getString("name"));
    }
}

To pass parameters to a stored procedure that has one or moreinput parameters in its definition in the RDBMS, you can code a stronglytyped execute(..) method that would delegate to thesuperclass' untyped execute(Map parameters) method(which has protected access); forexample:

import oracle.jdbc.OracleTypes;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.StoredProcedure;

import javax.sql.DataSource;

import java.sql.Types;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

public class TitlesAfterDateStoredProcedure extends StoredProcedure {

    private static final String SPROC_NAME = "TitlesAfterDate";
    private static final String CUTOFF_DATE_PARAM = "cutoffDate";

    public TitlesAfterDateStoredProcedure(DataSource dataSource) {
        super(dataSource, SPROC_NAME);
        declareParameter(new SqlParameter(CUTOFF_DATE_PARAM, Types.DATE);
        declareParameter(new SqlOutParameter("titles", OracleTypes.CURSOR, new TitleMapper()));
        compile();
    }

    public Map<String, Object> execute(Date cutoffDate) {
        Map<String, Object> inputs = new HashMap<String, Object>();
        inputs.put(CUTOFF_DATE_PARAM, cutoffDate);
        return super.execute(inputs);
    }
}

12.7 Common problems with parameter and data value handling

Common problems with parameters and data values exist in thedifferent approaches provided by the Spring Framework JDBC.

12.7.1 Providing SQL type information for parameters

Usually Spring determines the SQL type of the parameters based onthe type of parameter passed in. It is possible to explicitly providethe SQL type to be used when setting parameter values. This is sometimesnecessary to correctly set NULL values.

You can provide SQL type information in several ways:

• Many update and query methods of theJdbcTemplate take an additional parameter inthe form of an int array. This array is used toindicate the SQL type of the coresponding parameter using constantvalues from the java.sql.Types class. Provideone entry for each parameter.

• You can use the SqlParameterValue classto wrap the parameter value that needs this additional information.Createa new instance for each value and pass in the SQL type and parametervalue in the constructor. You can also provide an optional scaleparameter for numeric values.

• For methods working with named parameters, use theSqlParameterSource classesBeanPropertySqlParameterSource orMapSqlParameterSource. They both have methodsfor registering the SQL type for any of the named parametervalues.

12.7.2 Handling BLOB and CLOB objects

You can store images, other binary objects, and large chunks oftext. These large object are called BLOB for binary data and CLOB forcharacter data. In Spring you can handle these large objects by usingthe JdbcTemplate directly and also when using the higher abstractionsprovided by RDBMS Objects and the SimpleJdbc classes. Allof these approaches use an implementation of theLobHandler interface for the actual management ofthe LOB data. The LobHandler provides access to aLobCreator class, through thegetLobCreator method, used for creating new LOBobjects to be inserted.

The LobCreator/LobHandler provides thefollowing support for LOB input and output:

• BLOB

  • byte[] – getBlobAsBytes and setBlobAsBytes

  • InputStream – getBlobAsBinaryStream andsetBlobAsBinaryStream

• CLOB

  • String – getClobAsString and setClobAsString

  • InputStream – getClobAsAsciiStream andsetClobAsAsciiStream

  • Reader – getClobAsCharacterStream andsetClobAsCharacterStream

The next example shows how to create and insert a BLOB. Later youwill see how to read it back from the database.

This example uses a JdbcTemplate and animplementation of theAbstractLobCreatingPreparedStatementCallback.It implements one method, setValues. This method provides aLobCreator that you use to set the values for the LOBcolumns in your SQL insert statement.

For this example we assume that there is a variable,lobHandler, that already is set to an instanceof a DefaultLobHandler. You typically set thisvalue through dependency injection.

final File blobIn = new File("spring2004.jpg");
final InputStream blobIs = new FileInputStream(blobIn);
final File clobIn = new File("large.txt");
final InputStream clobIs = new FileInputStream(clobIn);
final InputStreamReader clobReader = new InputStreamReader(clobIs);
jdbcTemplate.execute(
  "INSERT INTO lob_table (id, a_clob, a_blob) VALUES (?, ?, ?)",
  new AbstractLobCreatingPreparedStatementCallback(lobHandler) {                                                       
      protected void setValues(PreparedStatement ps, LobCreator lobCreator) 
          throws SQLException {
        ps.setLong(1, 1L);
        lobCreator.setClobAsCharacterStream(ps, 2, clobReader, (int)clobIn.length());                                  
        lobCreator.setBlobAsBinaryStream(ps, 3, blobIs, (int)blobIn.length());                                         
      }
  }
);
blobIs.close();
clobReader.close();

	Pass in the lobHandler that in this example is a plainDefaultLobHandler
	Using the methodsetClobAsCharacterStream, pass in thecontents of the CLOB.
	Using the methodsetBlobAsBinaryStream, pass in the contentsof the BLOB.

Now it's time to read the LOB data from the database. Again, youuse a JdbcTemplate with the same instance variablelobHandler and a reference to aDefaultLobHandler.

List<Map<String, Object>> l = jdbcTemplate.query("select id, a_clob, a_blob from lob_table",
        new RowMapper<Map<String, Object>>() {
          public Map<String, Object> mapRow(ResultSet rs, int i) throws SQLException {
            Map<String, Object> results = new HashMap<String, Object>();
            String clobText = lobHandler.getClobAsString(rs, "a_clob");                                                
            results.put("CLOB", clobText);
            byte[] blobBytes = lobHandler.getBlobAsBytes(rs, "a_blob");                                                
            results.put("BLOB", blobBytes);
            return results;
          }
        });

	Using the method getClobAsString,retrieve the contents of the CLOB.
	Using the method getBlobAsBytes,retrieve the contents of the BLOB.

12.7.3 Passing in lists of values for IN clause

The SQL standard allows for selecting rows based on an expressionthat includes a variable list of values. A typical example would beselect * from T_ACTOR where id in (1, 2, 3). This variablelist is not directly supported for prepared statements by the JDBCstandard; you cannot declare a variable number of placeholders. You needa number of variations with the desired number of placeholders prepared,or you need to generate the SQL string dynamically once you know howmany placeholders are required. The named parameter support provided inthe NamedParameterJdbcTemplate andSimpleJdbcTemplate takes the latter approach.Pass in the values as a java.util.List ofprimitive objects. This list will be used to insert the requiredplaceholders and pass in the values during the statementexecution.

	Note
	Be careful when passing in many values. The JDBC standard doesnot guarantee that you can use more than 100 values for anin expression list. Various databases exceed this number,but they usually have a hard limit for how many values are allowed.Oracle's limit is 1000.

In addition to the primitive values in the value list, you cancreate a java.util.List of object arrays. Thislist would support multiple expressions defined for the inclause such as select * from T_ACTOR where (id, last_name) in((1, 'Johnson'), (2, 'Harrop')). Thisof course requires that your database supports this syntax.

12.7.4 Handling complex types for stored procedure calls

When you call stored procedures you can sometimes use complextypes specific to the database. To accommodate these types, Springprovides a SqlReturnType for handling them whenthey are returned from the stored procedure call andSqlTypeValue when they are passed in as aparameter to the stored procedure.

Here is an example of returning the value of an OracleSTRUCT object of the user declared typeITEM_TYPE. The SqlReturnTypeinterface has a single method named getTypeValuethat must be implemented. This interface is used as part of thedeclaration of an SqlOutParameter.

final TestItem - new TestItem(123L, "A test item", 
        new SimpleDateFormat("yyyy-M-d").parse("2010-12-31"););

declareParameter(new SqlOutParameter("item", OracleTypes.STRUCT, "ITEM_TYPE",
    new SqlReturnType() {
      public Object getTypeValue(CallableStatement cs, int colIndx, int sqlType, String typeName) 
          throws SQLException {
        STRUCT struct = (STRUCT)cs.getObject(colIndx);
        Object[] attr = struct.getAttributes();
        TestItem item = new TestItem();
        item.setId(((Number) attr[0]).longValue());
        item.setDescription((String)attr[1]);
        item.setExpirationDate((java.util.Date)attr[2]);
        return item;
      }
    }));

You use the SqlTypeValue topass in the value of a Java object like TestIteminto a stored procedure. TheSqlTypeValue interface has a single method namedcreateTypeValue that you must implement. Theactive connection is passed in, and you can use it to createdatabase-specific objects such asStructDescriptors, as shown in the followingexample, or ArrayDescriptors.

final TestItem - new TestItem(123L, "A test item", 
        new SimpleDateFormat("yyyy-M-d").parse("2010-12-31"););

SqlTypeValue value = new AbstractSqlTypeValue() {
  protected Object createTypeValue(Connection conn, int sqlType, String typeName) throws SQLException {
    StructDescriptor itemDescriptor = new StructDescriptor(typeName, conn);
    Struct item = new STRUCT(itemDescriptor, conn,
        new Object[] {
            testItem.getId(),
            testItem.getDescription(),
            new java.sql.Date(testItem.getExpirationDate().getTime())
        });
    return item;
  }
};

This SqlTypeValue can now be addedto the Map containing the input parameters for the execute call of thestored procedure.

Another use for the SqlTypeValue is passingin an array of values to an Oracle stored procedure. Oracle has its owninternal ARRAY class that must be used in thiscase, and you can use the SqlTypeValue to createan instance of the Oracle ARRAY and populate itwith values from the Java ARRAY.

final Long[] ids = new Long[] {1L, 2L};

SqlTypeValue value = new AbstractSqlTypeValue() {
  protected Object createTypeValue(Connection conn, int sqlType, String typeName) throws SQLException {
    ArrayDescriptor arrayDescriptor = new ArrayDescriptor(typeName, conn);
    ARRAY idArray = new ARRAY(arrayDescriptor, conn, ids);
    return idArray;
  }
};

12.8 Embedded database support

The org.springframework.jdbc.datasource.embeddedpackage provides support for embedded Java database engines. Support forHSQL, H2, and Derby is provided natively. Youcan also use an extensible API to plug in new embedded database types andDataSource implementations.

12.8.1 Why use an embedded database?

An embedded database is useful during the development phase of aproject because of its lightweight nature. Benefits include ease ofconfiguration, quick startup time, testability, and the ability torapidly evolve SQL during development.

12.8.2 Creating an embedded database instance using Spring XML

If you want to expose an embedded database instance as a bean in aSpring ApplicationContext, use the embedded-database tag in thespring-jdbc namespace:

    <jdbc:embedded-database id="dataSource">
        <jdbc:script location="classpath:schema.sql"/>
        <jdbc:script location="classpath:test-data.sql"/>
    </jdbc:embedded-database>

The preceding configuration creates an embedded HSQL databasepopulated with SQL from schema.sql and testdata.sql resources in theclasspath. The database instance is made available to the Springcontainer as a bean of type javax.sql.DataSource.This bean can then be injected into data access objects asneeded.

12.8.3 Creating an embedded database instance programmatically

The EmbeddedDatabaseBuilder class providesa fluent API for constructing an embedded database programmatically. Usethis when you need to create an embedded database instance in astandalone environment, such as a data access object unit test:

    EmbeddedDatabaseBuilder builder = new EmbeddedDatabaseBuilder();
    EmbeddedDatabase db = builder.setType(H2).addScript("my-schema.sql").addScript("my-test-data.sql").build();
    // do stuff against the db (EmbeddedDatabase extends javax.sql.DataSource)
    db.shutdown()

12.8.4 Extending the embedded database support

Spring JDBC embedded database support can be extended in two ways:

1. Implement EmbeddedDatabaseConfigurerto support a new embedded database type, such as ApacheDerby.

2. Implement DataSourceFactory tosupport a new DataSource implementation, such as a connectionpool, to manage embedded database connections.

You are encouraged to contribute back extensions to the Springcommunity at jira.springframework.org.

12.8.5 Using HSQL

Spring supports HSQL 1.8.0 and above. HSQL is the default embeddeddatabase if no type is specified explicitly. To specify HSQL explicitly,set the type attribute of theembedded-database tag to HSQL. Ifyou are using the builder API, call thesetType(EmbeddedDatabaseType) method withEmbeddedDatabaseType.HSQL.

12.8.6 Using H2

Spring supports the H2 database as well. To enable H2, set thetype attribute of theembedded-database tag to H2. Ifyou are using the builder API, call thesetType(EmbeddedDatabaseType) method withEmbeddedDatabaseType.H2.

12.8.7 Using Derby

Spring also supports Apache Derby 10.5 and above. To enable Derby,set the type attribute of theembedded-database tag to Derby. Ifusing the builder API, call thesetType(EmbeddedDatabaseType) method withEmbeddedDatabaseType.Derby.

12.8.8 Testing data access logic with an embedded database

Embedded databases provide a lightweight way to test data accesscode. The following is a data access unit test template that uses anembedded database:

public class DataAccessUnitTestTemplate {
    private EmbeddedDatabase db;
    
    @Before
    public void setUp() {
        // creates a HSQL in-memory db populated from default scripts classpath:schema.sql and classpath:test-data.sql
        db = new EmbeddedDatabaseBuilder().addDefaultScripts().build();		
    }

    @Test
    public void testDataAccess() {
        JdbcTemplate template = new JdbcTemplate(db);
        template.query(...);
    }

    @After
    public void tearDown() {
        db.shutdown();
    }
}

12.9 Initializing a DataSource

The org.springframework.jdbc.datasource.initpackage provides support for initializing an existingDataSource. The embedded database support providesone option for creating and initializing aDataSource for an application, but sometimes youneed to initialize an instance running on a server somewhere.

12.9.1 Initializing a database instance using Spring XML

If you want to initialize a database and you can provide areference to a DataSource bean, use theinitialize-database tag in thespring-jdbc namespace:

<jdbc:initialize-database data-source="dataSource">
  <jdbc:script location="classpath:com/foo/sql/db-schema.sql"/>
  <jdbc:script location="classpath:com/foo/sql/db-test-data.sql"/>
</jdbc:initialize-database>

The example above runs the two scripts specified against thedatabase: the first script is a schema creation, and the second is atest data set insert. The script locations can also be patterns withwildcards in the usual ant style used for resources in Spring (e.g.classpath*:/com/foo/**/sql/*-data.sql). If a pattern isused the scripts are executed in lexical order of their URL orfilename.

The default behaviour of the database initializer is tounconditionally execute the scripts provided. This will not always bewhat you want, for instance if running against an existing database thatalready has test data in it. The likelihood of accidentally deletingdata is reduced by the commonest pattern (as shown above) that createsthe tables first and then inserts the data - the first step will fail ifthe tables already exist.

However, to get more control over the creation and deletion ofexisting data, the XML namespace provides a couple more options. Thefirst is flag to switch the initialization on and off. This can be setaccording to the environment (e.g. to pull a boolean value from systemproperties or an environment bean), e.g.

<jdbc:initialize-database data-source="dataSource"
    enabled="#{systemProperties.INITIALIZE_DATABASE}">
  <jdbc:script location="..."/>
</jdbc:initialize-database>

The second option to control what happens with existing data is tobe more tolerant of failures. To this end you can control the ability ofthe initializer to ignore certain errors in the SQL it executes from thescripts, e.g.

<jdbc:initialize-database data-source="dataSource" ignore-failures="DROPS">
  <jdbc:script location="..."/>
</jdbc:initialize-database>

In this example we aresaying we expect that sometimes the scripts will be run against an emptydtabase and there are some DROP statements in the scripts which wouldtherefore fail. So failed SQL DROP statements will beignored, but other failures will cause an exception. This is useful ifyour SQL dialect doesn't support DROP ... IF EXISTS (orsimilar) but you want to unconditionally remove all test data beforere-creating it. In that case the first script is usually a set of drops,followed by a set of CREATE statements.

The ignore-failures option can be set toNONE (the default), DROPS (ignore faileddrops) or ALL (ignore all failures).

If you need more control than you get from the XML namespace, youcan simply use the DataSourceInitializerdirectly, and define it as a component in your application.

12.9.1.1 Initialization of Other Components that Depend on theDatabase

A large class of applications can just use the databaseinitializer with no further complications: those that do not use thedatabase until after the Spring context has started. If yourapplication is not one of those then you mightneed to read the rest of this section.

The database initializer depends on a data source instance andruns the scripts provided in its initialization callback (c.f.init-method in an XML bean definition orInitializingBean). If other beans depend on the same datasource and also use the data source in an initialization callback thenthere might be a problem because the data has not yet beeninitialized. A common example of this is a cache that initializeseagerly and loads up data from the database on applicationstartup.

To get round this issue you two options: change your cacheinitialization strategy to a later phase, or ensure that the databaseinitializer is initialized first.

The first option might be easy if the application is in yourcontrol, and not otherwise. Some suggestions for how to implement thisare

• Make the cache initialize lazily on first usage, whichimproves application startup time

• Have your cache or a separate component thatinitializes the cache implement Lifecycle orSmartLifecycle. When the application contextstarts up a SmartLifecycle can be automaticallystarted if its autoStartup flag is set,and a Lifecycle can be startedmanually by callingConfigurableApplicationContext.start() on theenclosing context.

• Use a Spring ApplicationEvent or similarcustom observer mechanism to trigger the cache initialization.ContextRefreshedEvent is always published by thecontext when it is ready for use (after all beans have beeninitialized), so that is often a useful hook (this ishow the SmartLifecycle works by default).

The second option can also be easy. Some suggestions on how toimplement this are

• Rely on Spring BeanFactory default behaviour, which isthat beans are initialized in registration order. You can easilyarrange that by adopting the common practice of a set of<import/> elements that order your application modules,and ensure that the database and database initialization arelisted first

• Separate the datasource and the business components thatuse it and control their startup order by putting them inseparate ApplicationContext instances (e.g. parent has thedatasource and child has the business components). Thisstructure is common in Spring web applications, but can be moregenerally applied.

• Use a modular runtime like SpringSource dm Server andseparate the data source and the components that depend on it.E.g. specify the bundle start up order as datasource ->initializer -> business components.

---

Prev	Up	Next
11. DAO support	Home	13. Object Relational Mapping (ORM) Data Access

 

Developing Applications with Cloud Foundry Live Online, 13 Oct 2015