This 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 like 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 CallableStatementCreateor 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 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. All SQL issued by this class is logged.

In order to work with data from a database, we need 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 fulfil 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 our 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 MySQLErrorCodesTransalator 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", java.lang.String.class);
        return name;
    }

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

In addition to the singe 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. I will show an example where we update a column for a certain primary key. In this example I am using an SQL statement that has place holders for row parameters. Most of the query and update methods have this functionality. The parameter values are passed in as an array of objects.

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;
    }
}

Helper class that provides static methods to obtain connections from JNDI and close connections if necessary. 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 BeanFactory resp. an ApplicationContext. With the latter, it is preferable to preconfigure your 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: You can even replace the definition of the FactoryBean with a non-JNDI DataSource!

Interface to be implemented by classes that can provide a connection to a relational database. Extends the javax.sql.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, if we know that we want to reuse a connection.

Abstract base class for Spring's DataSource implementations, taking care of the "uninteresting" glue. This is the class you would extend if you are writing your own DataSource implementation.

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.

Implementation of SmartDataSource that configures a plain old JDBC Driver via bean properties, and returns a new connection every time.

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.

PlatformTransactionManager implementation for single JDBC data sources. Binds a JDBC connection from the specified data source to the 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 SQLException. 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.

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!

Reusable threadsafe object to represent an SQL query. Subclasses must implement the newResultReader() method to provide an object that can save the results while iterating over the ResultSet. This class is rarely used directly since the MappingSqlQuery, that extends this class, 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 JDBC ResultSet into an object.

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

Here is a brief example of a custom query that maps the data from the customer table to a Java object called Customer.

  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.

RdbmsOperation subclass representing a SQL update. Like a query, an update object is reusable. Like all RdbmsOperation objects, 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);
    }
}

Superclass for object abstractions of RDBMS stored procedures. This class is abstract and its execute methods are protected, preventing use other than through a subclass that offers tighter typing.

The inherited sql property is 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 you have to create a class that extends StoredProcedure. There are no input parameters, but there is an output parameter that is declared as a date 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.driver.OracleDriver");
        ds.setUrl("jdbc:oracle:thin:@localhost:1521:mydb");
        ds.setUsername("scott");
        ds.setPassword("tiger");

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

    private class MyStoredProcedure extends StoredProcedure {
        public 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() {
            Map out = execute(new HashMap());
            return out;
        }

    }

    private static void printMap(Map r) {
        Iterator i = r.entrySet().iterator();
        while (i.hasNext()) {
            System.out.println((String) i.next().toString());  
        }
    }
}

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 StoredProcedure or SqlCall for this type of processing.

This is a concrete class, which there is normally no need to subclass. 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();
    }