The JdbcTemplate Class

This class represents the core of Spring's JDBC support. It can execute all types of SQL statements. In the most simplistic view, you can classify the data definition and data manipulation statements. Data definition statements cover creating various database objects (tables, views, stored procedures, etc). Data manipulation statements manipulate the data and can be classified as select and update statements. A select statement generally returns a set of rows; each row has the same set of columns. An update statement modifies the data in the database but does not return any results.

The JdbcTemplate class allows you to issue any type of SQL statement to the database and return any type of result.

Let's start with a simple query that returns a single value. A good example is a query that returns the last generated primary key value—for instance, select last_insert_id() in MySQL. This statement returns an int value. We can use JdbcTemplate to retrieve the value, as shown in Listing 8-12.

Listing 8-12: Using JdbcTemplate to Retreive an int Value

package com.apress.prospring.ch10.spring;      public abstract class JdbcAbstractTestDao      extends JdbcDaoSupport implements TestDao {          protected void retrieveIdentity(final Test test) {         JdbcTemplate jt = new JdbcTemplate(getDataSource());         test.setTestId(jt.queryForInt("select last_insert_id()") );     } }i

The other methods of JdbcTemplate return Long, Object, and int values and are very self- explanatory.

In Listing 8-13, we demonstrate how to return a list of two domain objects as a result of an inner join operation using the execute method of JdbcTemplate.

Listing 8-13: JdbcTemplate Example

package com.apress.prospring.ch10.spring;      public abstract class JdbcAbstractTestDao      extends JdbcDaoSupport implements TestDao {          public List getAll() {         JdbcTemplate jt = new JdbcTemplate(getDataSource());         return (List)jt.execute(             new PreparedStatementCreator() {                      public PreparedStatement createPreparedStatement(                     Connection connection)                      throws SQLException {                     return connection.prepareStatement(                          "select t.*, td.* from Tests t " +                         "inner join TestDetails td on t.TestId = td.Test");                 }                          },              new PreparedStatementCallback() {                      private int testId = -1;                                  public Object doInPreparedStatement(PreparedStatement ps)                      throws SQLException, DataAccessException {                     int testId;                     List result = new ArrayList();                     int lastTestId = -1;                     Test test = null;                     ResultSet rs = ps.executeQuery();                     while (rs.next()) {                         testId = rs.getInt("TestId");                         if (testId != lastTestId) {                             test = new Test();                             test.setTestId(testId);                             test.setName(rs.getString("Name"));                             test.setDetails(new ArrayList());                         }                         TestDetail td = new TestDetail();                         td.setData(rs.getString("Data"));                         td.setTest(rs.getInt("Test"));                         td.setTestDetailId(rs.getInt("TestDetailId"));                         test.getDetails().add(td);                     }                     return result;                 }                          });     } }

This code demonstrates how to use this fundamental class. As you can see in Listing 8-13, you have full control over how you create and use the PreparedStatement. Here, we created a JdbcTemplate instance with implementations of PreparedStatementCreator and PreparedStatementCallback. In the creator, we created a PreparedStatement that selects rows from two inner joined tables. The result is a subset of a Cartesian product of the Test and TestDetail tables. Here, we iterate through the result and create appropriate instances of Test and TestDetail objects.

This code adequately demonstrates how to use JdbcTemplate. The bottom line is that anything you can do in JDBC you can do in Spring, but you do not need to manage the JDBC checked exceptions, which makes your code less cluttered and easier to read.

It is worth noting that other Spring JDBC support classes (such as MappingSqlQuery and SqlUpdate) use JdbcTemplate internally to perform the requested database operations. We cover these high-level classes in the next section of the chapter.