The JDBC abstraction framework provided by Spring consists of four different packages core, dataSource, object, and support.

The org.springframework.jdbc.core package contains the JdbcTemplate class and its various callback interfaces, plus a variety of related classes.

The org.springframework.jdbc.datasource package contains a utility class for easy DataSource access, and various simple DataSource implementations that can be used for testing and running unmodified JDBC code outside of a J2EE container. The utility class provides static methods to obtain connections from JNDI and to close connections if necessary. It has support for thread-bound connections, e.g. for use with DataSourceTransactionManager.

Next, the org.springframework.jdbc.object package contains classes that represent RDBMS queries, updates, and stored procedures as thread safe, reusable objects. This approach is modeled by JDO, although of course objects returned by queries are “disconnected” from the database. This higher level of JDBC abstraction depends on the lower-level abstraction in the org.springframework.jdbc.core package.

Finally the org.springframework.jdbc.support package is where you find the SQLException translation functionality and some utility classes.

Exceptions thrown during JDBC processing are translated to exceptions defined in the org.springframework.dao package. This means that code using the Spring JDBC abstraction layer does not need to implement JDBC or RDBMS-specific error handling. All translated exceptions are unchecked giving you the option of catching the exceptions that you can recover from while allowing other exceptions to be propagated to the caller.

The JdbcTemplate class is the central class in the JDBC core package. It simplifies the use of JDBC since it handles the creation and release of resources. This helps to avoid common errors such as forgetting to always close the connection. It executes the core JDBC workflow like statement creation and execution, leaving application code to provide SQL and extract results. This class executes SQL queries, update statements or stored procedure calls, imitating iteration over ResultSets and extraction of returned parameter values. It also catches JDBC exceptions and translates them to the generic, more informative, exception hierarchy defined in the org.springframework.dao package.

Code using this class only need to implement callback interfaces, giving them a clearly defined contract. The PreparedStatementCreator callback interface creates a prepared statement given a Connection provided by this class, providing SQL and any necessary parameters. The same is true for the CallableStatementCreator interface which creates callable statement. The RowCallbackHandler interface extracts values from each row of a ResultSet.

This class can be used within a service implementation via direct instantiation with a DataSource reference, or get prepared in an application context and given to services as a bean reference. Note: the DataSource should always be configured as a bean in the application context, in the first case given to the service directly, in the second case to the prepared template. Because this class is parameterizable by the callback interfaces and the SQLExceptionTranslator interface, it isn't necessary to subclass it.

Finally, please be aware that all of the SQL issued by this class is logged at the 'DEBUG' level under the category corresponding to the fully qualified class name of the template instance (typically JdbcTemplate, but it may be different if a custom subclass of the JdbcTemplate class is being used).

The NamedParameterJdbcTemplate class adds support for programming JDBC statements using named parameters (as opposed to programming JDBC statements using only classic placeholder ('?') arguments. The NamedParameterJdbcTemplate class wraps a vanilla JdbcTemplate, and delegates to the wrapped JdbcTemplate to do much of its work. This section will describe only those areas of the NamedParameterJdbcTemplate class that differ from the JdbcTemplate itself; namely, programming JDBC statements using named parameters.

The expected usage pattern for the NamedParameterJdbcTemplate class is perhaps best illustrated by looking at an example (the finer points will be addressed immediately thereafter).

// some JDBC-backed DAO class...
public int countOfActorsByFirstName(String firstName) {

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

    NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(this.getDataSource());
    SqlParameterSource namedParameters = new MapSqlParameterSource("first_name", firstName);

    return template.queryForInt(sql, namedParameters);
}

Notice the use of the named parameter notation in the value assigned to the 'sql' variable, and the corresponding value that is plugged into the 'namedParameters' variable (of type MapSqlParameterSource).

If you like, you can also pass along named parameters (and their corresponding values) to a NamedParameterJdbcTemplate instance using the (perhaps more familiar) Map-based style. (The rest of the methods exposed by the NamedParameterJdbcOperations - and implemented by the NamedParameterJdbcTemplate class) follow a similar pattern and will not be covered here.)

// some JDBC-backed DAO class...
public int countOfActorsByFirstName(String firstName) {

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

    NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(this.getDataSource());
    Map namedParameters = new HashMap();
    namedParameters.put("first_name", firstName);

    return template.queryForInt(sql, namedParameters);
}

Another nice feature related to the NamedParameterJdbcTemplate (and existing in the same Java package) is the SqlParameterSource interface. You have already seen an example of an implementation of this interface in one of the preceding code snippets (the MapSqlParameterSource class). The entire point of the SqlParameterSource is to serve as a source of named parameter values to a NamedParameterJdbcTemplate. The MapSqlParameterSource class is (as the name implies) a very simple implementation, that is simply an adapter around a java.util.Map, and its use (should) be self-evident (if it is not then do raise a JIRA issue asking for more documentation in this regard).

Another, more interesting, implementation of the SqlParameterSource interface is the BeanPropertySqlParameterSource class. This class wraps an arbitrary JavaBean-like object, and uses the properties of the wrapped object as the source of named parameter values. An example will perhaps make its use clear.

// some JavaBean-like class...
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...
public int countOfActors(Actor exampleActor) {

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

    NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(this.getDataSource());
    SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(exampleActor);

    return template.queryForInt(sql, namedParameters);
}

Remember that the NamedParameterJdbcTemplate class wraps a classic JdbcTemplate template... if you need access to the wrapped JdbcTemplate instance (to access some of the functionality only present in the JdbcTemplate class), then you can use the getJdbcOperations() method to access the wrapped JdbcTemplate via the JdbcOperations interface.

The NamedParameterJdbcTemplate class is threadsafe, and the expected usage pattern is not to instantiate a new NamedParameterJdbcTemplate instance per operation, but rather to simply configure a single NamedParameterJdbcTemplate instance per DataSource (typically via a Spring IoC container if you are using Spring's IoC technology) and then share that same instance amongst those DAOs (typically) that need it.

	Note
	Please be aware that the functionality offered by this class is only available to you if you are using Java 5 (Tiger).

The SimpleJdbcTemplate class is a wrapper around the classic Spring JdbcTemplate, that takes advantage of Java 5 language features such as varargs and autoboxing. The SimpleJdbcTemplate class is somewhat of a sop to the syntactic-sugar-like features of Java 5, but as anyone who has developed on Java 5 and then had to move back to developing on a previous version of the JDK will know, those syntactic-sugar-like features sure are nice.

The value-add of the SimpleJdbcTemplate class in the area of syntactic-sugar is definitely best illustrated with a 'before and after' example. The following code snippet shows first some data access code using the classic Spring JdbcTemplate, followed immediately thereafter by a code snippet that does the same job, only this time using the SimpleJdbcTemplate.

// classic JdbcTemplate-style...
public Actor findActor(long id) {
    String sql = "select id, first_name, last_name from T_ACTOR where id = ?";
    
    RowMapper mapper = new RowMapper() {
    
        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            Actor actor = new Actor();
            actor.setId(rs.getLong(Long.valueOf(rs.getLong("id"))));
            actor.setFirstName(rs.getString("first_name"));
            actor.setLastName(rs.getString("last_name"));
            return actor;
        }
    };
    
    // normally this would be dependency injected of course...
    JdbcTemplate jdbcTemplate = new JdbcTemplate(this.getDataSource());
    
    // notice the cast, and the wrapping up of the 'id' argument
    // in an array, and the boxing of the 'id' argument as a reference type
    return (Actor) jdbcTemplate.queryForObject(sql, mapper, new Object[] {Long.valueOf(id)});
}

Here is the same method, only this time using the SimpleJdbcTemplate; notice how much 'cleaner'the code is.

// SimpleJdbcTemplate-style...
public Actor findActor(long id) {
    String sql = "select id, first_name, last_name from T_ACTOR where id = ?";

    ParameterizedRowMapper<Actor> mapper = new ParameterizedRowMapper<Actor>() {
    
        // notice the return type with respect to Java 5 covariant return types
        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;
        }
    };

    // again, normally this would be dependency injected of course...
    SimpleJdbcTemplate simpleJdbcTemplate = new SimpleJdbcTemplate(this.getDataSource());

    return simpleJdbcTemplate.queryForObject(sql, mapper, id);
}

In order to work with data from a database, one needs to obtain a connection to the database. The way Spring does this is through a DataSource. A DataSource is part of the JDBC specification and can be seen as a generalized connection factory. It allows a container or a framework to hide connection pooling and transaction management issues from the application code. As a developer, you don't need to know any details about how to connect to the database, that is the responsibility for the administrator that sets up the datasource. You will most likely have to fulfill both roles while you are developing and testing you code though, but you will not necessarily have to know how the production data source is configured.

When using Spring's JDBC layer, you can either obtain a data source from JNDI or you can configure your own, using an implementation that is provided in the Spring distribution. The latter comes in handy for unit testing outside of a web container. We will use the DriverManagerDataSource implementation for this section but there are several additional implementations that will be covered later on. The DriverManagerDataSource works the same way that you probably are used to work when you obtain a JDBC connection. You have to specify the fully qualified class name of the JDBC driver that you are using so that the DriverManager can load the driver class. Then you have to provide a url that varies between JDBC drivers. You have to consult the documentation for your driver for the correct value to use here. Finally you must provide a username and a password that will be used to connect to the database. Here is an example of how to configure a DriverManagerDataSource:

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

SQLExceptionTranslator is an interface to be implemented by classes that can translate between SQLExceptions and Spring's own data-access-strategy-agnostic org.springframework.dao.DataAccessException.

Implementations can be generic (for example, using SQLState codes for JDBC) or proprietary (for example, using Oracle error codes) for greater precision.

SQLErrorCodeSQLExceptionTranslator is the implementation of SQLExceptionTranslator that is used by default. This implementation uses specific vendor codes. More precise than SQLState implementation, but vendor specific. The error code translations are based on codes held in a JavaBean type class named SQLErrorCodes. This class is created and populated by an SQLErrorCodesFactory which as the name suggests is a factory for creating SQLErrorCodes based on the contents of a configuration file named 'sql-error-codes.xml'. This file is populated with vendor codes and based on the DatabaseProductName taken from the DatabaseMetaData, the codes for the current database are used.

The SQLErrorCodeSQLExceptionTranslator applies the following matching rules:

SQLErrorCodeSQLExceptionTranslator can be extended the following way:

public class MySQLErrorCodesTranslator 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 '-12345' is translated and any other errors are simply left to be translated by the default translator implementation. To use this custom translator, it is necessary to pass it to the JdbcTemplate using the method setExceptionTranslator and to use this JdbcTemplate for all of the data access processing where this translator is needed. Here is an example of how this custom translator can be used:

// create a JdbcTemplate and set data source 
JdbcTemplate jt = new JdbcTemplate(); 
jt.setDataSource(dataSource); 
// create a custom translator and set the DataSource for the default translation lookup 
MySQLErrorCodesTransalator tr = new MySQLErrorCodesTransalator(); 
tr.setDataSource(dataSource); 
jt.setExceptionTranslator(tr); 
// use the JdbcTemplate for this SqlUpdate 
SqlUpdate su = new SqlUpdate(); 
su.setJdbcTemplate(jt); 
su.setSql("update orders set shipping_charge = shipping_charge * 1.05"); 
su.compile(); 
su.update();

The custom translator is passed a data source because we still want the default translation to look up the error codes in sql-error-codes.xml.

To execute an SQL statement, there is very little code needed. All you need is a DataSource and a JdbcTemplate. Once you have that, you can use a number of convenience methods that are provided with the JdbcTemplate. Here is a short example showing what you need to include for a minimal but fully functional class that creates a new table.

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

public class ExecuteAStatement {

    private JdbcTemplate jt;
    private DataSource dataSource;

    public void doExecute() {
        jt = new JdbcTemplate(dataSource);
        jt.execute("create table mytable (id integer, name varchar(100))"); 
    }

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

In addition to the execute methods, there is a large number of query methods. Some of these methods are intended to be used for queries that return a single value. Maybe you want to retrieve a count or a specific value from one row. If that is the case then you can use queryForInt(..),queryForLong or queryForObject(..). The latter will convert the returned JDBC Type to the Java class that is passed in as an argument. If the type conversion is invalid, then an InvalidDataAccessApiUsageException will be thrown. Here is an example that contains two query methods, one for an int and one that queries for a String.

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

public class RunAQuery {

    private JdbcTemplate jt;
    private DataSource dataSource;
  
    public int getCount() {
        jt = new JdbcTemplate(dataSource);
        int count = jt.queryForInt("select count(*) from mytable");
        return count;
    }

    public String getName() {
        jt = new JdbcTemplate(dataSource);
        String name = (String) jt.queryForObject("select name from mytable", String.class);
        return name;
    }

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

In addition to the single results query methods there are several methods that return a List with an entry for each row that the query returned. The most generic one is queryForList which returns a List where each entry is a Map with each entry in the map representing the column value for that row. If we add a method to the above example to retrieve a list of all the rows, it would look like this:

public List getList() {
    jt = new JdbcTemplate(dataSource);
    List rows = jt.queryForList("select * from mytable");
    return rows;
}

The list returned would look something like this:

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

There are also a number of update methods that you can use. Find below an example where a column is updated for a certain primary key. In this example an SQL statement is used that has place holders for row parameters (most of the query and update methods have this functionality). Note that the parameter values are passed in as an array of objects (and this primitives have to be wrapped in the attendant wrapper reference classes).

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;

public class ExecuteAnUpdate {

    private JdbcTemplate jt;
    private DataSource dataSource;

    public void setName(int id, String name) {
        jt = new JdbcTemplate(dataSource);
        jt.update("update mytable set name = ? where id = ?", new Object[] {name, new Integer(id)});
    }

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

The DataSourceUtils class is a convenient and powerful helper class that provides static methods to obtain connections from JNDI and close connections if necessary. It has support for thread-bound connections, e.g. for use with DataSourceTransactionManager.

Note: the getDataSourceFromJndi(..) methods are targeted at applications that do not use a bean factory or application context. With the latter, it is preferable to preconfigure ones beans or even JdbcTemplate instances in the factory: JndiObjectFactoryBean can be used to fetch a DataSource from JNDI and give the DataSource bean reference to other beans. Switching to another DataSource is just a matter of configuration then: one can even replace the definition of the FactoryBean with a non-JNDI DataSource!

The SmartDataSource interface is to be implemented by classes that can provide a connection to a relational database. Extends the DataSource interface to allow classes using it to query whether or not the connection should be closed after a given operation. This can sometimes be useful for efficiency, in the cases where one knows that one wants to reuse a connection.

This is an abstract base class for Spring's DataSource implementations, that takes care of the "uninteresting" glue. This is the class one would extend if one was writing one's own DataSource implementation.

The SingleConnectionDataSource class is an implementation of SmartDataSource that wraps a single connection which is not closed after use. Obviously, this is not multi-threading capable.

If client code will call close in the assumption of a pooled connection, like when using persistence tools, set suppressClose to true. This will return a close-suppressing proxy instead of the physical connection. Be aware that you will not be able to cast this to a native Oracle Connection or the like anymore.

This is primarily a test class. For example, it enables easy testing of code outside an application server, in conjunction with a simple JNDI environment. In contrast to DriverManagerDataSource, it reuses the same connection all the time, avoiding excessive creation of physical connections.

The DriverManagerDataSource class is an implementation of the SmartDataSource interface that configures a plain old JDBC Driver via bean properties, and returns a new connection every time.

This is potentially useful for test or standalone environments outside of a J2EE container, either as a DataSource bean in a respective ApplicationContext, or in conjunction with a simple JNDI environment. Pool-assuming Connection.close() calls will simply close the connection, so any DataSource-aware persistence code should work. However, using JavaBean style connection pools such as commons-dbcp is so easy, even in a test environment, that it is almost always preferable to use such a connection pool over DriverManagerDataSource.

TransactionAwareDataSourceProxy is a proxy for a target DataSource, which wraps that target DataSource to add awareness of Spring-managed transactions. In this respect it is similar to a transactional JNDI DataSource as provided by a J2EE server.

Note

It should almost never be necessary or desireable to use this class, except when existing code exists which must be called and passed a standard JDBC DataSource interface implementation. In this case, it's possible to still have this code be usable, but participating in Spring managed transactions. It is generally preferable to write your own new code using the higher level abstractions for resource management, such as JdbcTemplate or DataSourceUtils.

(See the TransactionAwareDataSourceProxy Javadocs for more details.)

The DataSourceTransactionManager class is a PlatformTransactionManager implementation for single JDBC datasources. It binds a JDBC connection from the specified data source to the currently executing thread, potentially allowing for one thread connection per data source.

Application code is required to retrieve the JDBC connection via DataSourceUtils.getConnection(DataSource) instead of J2EE's standard DataSource.getConnection. This is recommended anyway, as it throws unchecked org.springframework.dao exceptions instead of checked SQLExceptions. All framework classes like JdbcTemplate use this strategy implicitly. If not used with this transaction manager, the lookup strategy behaves exactly like the common one - it can thus be used in any case.

The DataSourceTransactionManager class supports custom isolation levels, and timeouts that get applied as appropriate JDBC statement query timeouts. To support the latter, application code must either use JdbcTemplate or call DataSourceUtils.applyTransactionTimeout method for each created statement.

This implementation can be used instead of JtaTransactionManager in the single resource case, as it does not require the container to support JTA. Switching between both is just a matter of configuration, if you stick to the required connection lookup pattern. Note that JTA does not support custom isolation levels!

SqlQuery is a reusable, threadsafe class that encapsulates an SQL query. Subclasses must implement the newResultReader() method to provide an object that can save the results while iterating over a supplied ResultSet. The SqlQuery class is rarely used directly since the MappingSqlQuery subclass provides a much more convenient implementation for mapping rows to Java classes. Other implementations that extend SqlQuery are MappingSqlQueryWithParameters and UpdatableSqlQuery.

MappingSqlQuery is a reusable query in which concrete subclasses must implement the abstract mapRow(ResultSet, int) method to convert each row of the supplied ResultSet into an object.

Of all the various SqlQuery implementations, the MappingSqlQuery is the one used most often, and it is also the one that is the easiest to use.

Find below a (brief) example of a custom query that maps the data from the customer table to an instance of the Customer class.

private class CustomerMappingQuery extends MappingSqlQuery {

    public CustomerMappingQuery(DataSource ds) {
        super(ds, "SELECT id, name FROM customer WHERE id = ?");
        super.declareParameter(new SqlParameter("id", Types.INTEGER));
        compile();
    }

    public Object mapRow(ResultSet rs, int rowNumber) throws SQLException {
        Customer cust = new Customer();
        cust.setId((Integer) rs.getObject("id"));
        cust.setName(rs.getString("name"));
        return cust;
    } 
}

We provide a constructor for this customer query that takes the DataSource as the only parameter. In this constructor we call the constructor on the superclass with the DataSource and the SQL that should be executed to retrieve the rows for this query. This SQL will be used to create a PreparedStatement so it may contain place holders for any parameters to be passed in during execution. Each parameter must be declared using the declareParameter method passing in an SqlParameter. The SqlParameter takes a name and the JDBC type as defined in java.sql.Types. After all parameters have been defined we call the compile() method so the statement can be prepared and later be executed.

Let's take a look at the code where this custom query is instantiated and executed:

public Customer getCustomer(Integer id) {
    CustomerMappingQuery custQry = new CustomerMappingQuery(dataSource); 
    Object[] parms = new Object[1];
    parms[0] = id;
    List customers = custQry.execute(parms);
    if (customers.size() > 0) {
        return (Customer) customers.get(0);
    }
    else {
        return null;
    }
}

The method in this example retrieves the customer with the id that is passed in as the only parameter. After creating an instance of the CustomerMappingQuery class we create an array of objects that will contain all parameters that are passed in. In this case there is only one parameter and it is passed in as an Integer. Now we are ready to execute the query using this array of parameters and we get a List that contains a Customer object for each row that was returned for our query. In this case it will only be one entry if there was a match.

The SqlUpdate class encapsulates an SQL update. Like a query, an update object is reusable. Like all RdbmsOperation classes, an update can have parameters and is defined in SQL.

This class provides a number of update() methods analogous to the execute() methods of query objects.

This class is concrete. Although it can be subclassed (for example to add a custom update method) it can easily be parameterized by setting SQL and declaring parameters.

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(Types.NUMERIC));
        declareParameter(new SqlParameter(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 run(int id, int rating) {
        Object[] params =
            new Object[] {
                new Integer(rating),
                new Integer(id)};
        return update(params);
    }
}

The StoredProcedure class is a superclass for object abstractions of RDBMS stored procedures. This class is abstract, and its various execute(..) methods have protected access, preventing use other than through a subclass that offers tighter typing.

The inherited sql property will be the name of the stored procedure in the RDBMS. Note that JDBC 3.0 introduces named parameters, although the other features provided by this class are still necessary in JDBC 3.0.

Here is an example of a program that calls a function, sysdate(), that comes with any Oracle database. To use the stored procedure functionality one has to create a class that extends StoredProcedure. There are no input parameters, but there is an output parameter that is declared as a date type using the class SqlOutParameter. The execute() method returns a map with an entry for each declared output parameter using the parameter name as the key.

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

import javax.sql.DataSource;

import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.datasource.*;
import org.springframework.jdbc.object.StoredProcedure;

public class TestStoredProcedure {

    public static void main(String[] args)  {
        TestStoredProcedure t = new TestStoredProcedure();
        t.test();
        System.out.println("Done!");
    }
    
    void test() {
        DriverManagerDataSource ds = new DriverManagerDataSource();
        ds.setDriverClassName("oracle.jdbc.OracleDriver");
        ds.setUrl("jdbc:oracle:thin:@localhost:1521:mydb");
        ds.setUsername("scott");
        ds.setPassword("tiger");

        MyStoredProcedure sproc = new MyStoredProcedure(ds);
        Map results = sproc.execute();
        printMap(results);
    }

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

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

        public Map execute() {
            // the 'sysdate' sproc has no input parameters, so an empty Map is supplied...
            return execute(new HashMap());
        }
    }

    private static void printMap(Map results) {
        for (Iterator it = results.entrySet().iterator(); it.hasNext(); ) {
            System.out.println(it.next());  
        }
    }
}

Find below an example of a StoredProcedure that has two output parameters (in this case Oracle cursors).

import oracle.jdbc.driver.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 execute() {
        // again, this sproc has no input parameters, so an empty Map is supplied...
        return super.execute(new HashMap());
    }
}

Notice how the overloaded variants of the declareParameter(..) method that have been used in the TitlesAndGenresStoredProcedure constructor are passed RowMapper implementation instances; this is a very convenient and powerful way to reuse existing functionality. (The code for the two RowMapper implementations is provided below in the interest of completeness.)

Firstly the TitleMapper class, which simply maps a ResultSet to a Title domain object for each row in the supplied ResultSet.

import com.foo.sprocs.domain.Title;
import org.springframework.jdbc.core.RowMapper;

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

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

Secondly, the GenreMapper class, which again simply maps a ResultSet to a Genre domain object for each row in the supplied ResultSet.

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 {
    
    public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
        return new Genre(rs.getString("name"));
    }
}

If one needs to pass parameters to a stored procedure (i.e. the stored procedure has been declared as having one or more input parameters in its definition in the RDBMS), one would code a strongly typed execute(..) method which would delegate to the superclass' (untyped) execute(Map parameters) (which has protected access); for example:

import oracle.jdbc.driver.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 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);
        declaraParameter(new SqlParameter(CUTOFF_DATE_PARAM, Types.DATE);
        declareParameter(new SqlOutParameter("titles", OracleTypes.CURSOR, new TitleMapper()));
        compile();
    }

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

The SqlFunction RDBMS operation class encapsulates an SQL "function" wrapper for a query that returns a single row of results. The default behavior is to return an int, but that can be overridden by using the methods with an extra return type parameter. This is similar to using the queryForXxx methods of the JdbcTemplate. The advantage with SqlFunction is that you don't have to create the JdbcTemplate, it is done behind the scenes.

This class is intended to use to call SQL functions that return a single result using a query like "select user()" or "select sysdate from dual". It is not intended for calling more complex stored functions or for using a CallableStatement to invoke a stored procedure or stored function. (Use the StoredProcedure or SqlCall classes for this type of processing).

SqlFunction is a concrete class, and there is typically no need to subclass it. Code using this package can create an object of this type, declaring SQL and parameters, and then invoke the appropriate run method repeatedly to execute the function. Here is an example of retrieving the count of rows from a table:

public int countRows() {
    SqlFunction sf = new SqlFunction(dataSource, "select count(*) from mytable");
    sf.compile();
    return sf.run();
}