Spring JDBC components simplify JDBC-based data access by encapsulating the boilerplate code and hiding the interaction with JDBC API components from the developer with a set of simple interfaces. These interfaces handle the opening and closing of JDBC resources (connections, statements, resultsets) as required. They prepare and execute statements, extract results from resultsets, provide callback hooks for converting, mapping and handling data, handle transactions, and translate SQL exceptions into the more sensible and meaningful hierarchy of DataAccessException
.
Spring JDBC provides three convenient approaches for accessing relational databases:
JdbcTemplate
SimpleJDBC
classesSql*
classesEach of these Spring JDBC categories has multiple flavors of components under them which you can mix-and-match based on your convenience and technical choice. You may explore them under the org.springframework.jdbc
package and its subpackages.
JdbcTemplate
is the core component under Spring JDBC abstraction. This powerful component executes almost all of the possible JDBC operations with its simple, meaningful methods, accepting parameters for an impressive set of flavors of data access. It belongs to the package, org.springframework.jdbc.core
, which contains many other supporting classes that help JdbcTemplate
to complete its JDBC operations. A DataSource
instance is the only dependency for this component. All other Spring JDBC components use JdbcTemplate
internally for their operations.
Usually, you configure JdbcTemplate
as yet another Spring bean, and inject it into your DAOs or into any other bean where you want to invoke its methods.
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <constructor-arg ref="dataSource"/> </bean> <bean id="userDAO" class="com.springessentials.chapter3.dao.impl.UserJdbcDAO"> <constructor-arg ref="jdbctemplate"/> </bean>
JdbcTemplate
is one of the implementations of Template Pattern in Spring. Template Pattern is a behavioral pattern listed in the Gang of Four design pattern catalog. It defines the skeleton of an algorithm in a method or operation called Template Method, deferring some steps into the subclasses, without changing the algorithm's structure. JdbcTemplate
is a collection of these Template Methods; the user can extend it and override some of the behaviors based on specific requirements. JMSTemplate
and JpaTemplate
are also examples of Template Pattern implementations.
JdbcTemplate
executes SQL queries (SELECT
), update statements (INSERT
, UPDATE
, and DELETE
), stored procedure and function calls, returns extracted results (for SELECT
queries), and invokes call-back methods for result-set extraction and mapping rows with domain objects. It has a comprehensive set of query and execute methods for different methods of result-set extraction. The following table introduces a few very useful JdbcTemplate
methods:
Behind the super capabilities of JdbcTemplate
is a set of callback interfaces being passed as arguments for the methods listed in the preceding table. These execution hooks help JdbcTemplate
to deal with relational data in a pure object-oriented and reusable fashion. A good understanding of these interfaces is critical for the right usage of JdbcTemplate
. See the following table for these callback interfaces:
Callback interface |
Callback method(s) |
Responsibilities |
---|---|---|
|
|
Constructs |
|
|
Constructs |
|
|
Sets values to |
|
|
Prepares |
|
|
Used by |
|
|
Extracts results from |
|
|
Processes each row of a |
|
|
Maps each row of a |
Now let's try some nice realistic usages of JdbcTemplate
. The following is a simple method executing a count query using JdbcTemplate
.
@Override public int findAllOpenTasksCount() { return jdbcTemplate.queryForObject("select count(id) from tbl_user where status = ?", new Object[]{"Open"}, Integer.class); }
Do you see how this straightforward one-liner code saves you from all the boilerplate and exception-handling code you would otherwise need to write in typical JDBC code?
The following code snippet is a bit more complex and illustrates how to query a unique row from a table and map it with a domain object (User
, in this case) using RowMapper
:
public User findByUserName(String userName) { return jdbcTemplate.queryForObject("SELECT ID, NAME, USER_NAME, PASSWORD, DOB, PROFILE_IMAGE_ID, PROFILE_IMAGE_NAME FROM TBL_USER WHERE USER_NAME = ?", new Object[] { userName }, new RowMapper<User>() { @Override public User mapRow(ResultSet rs, int rowNum) throws SQLException { return new User(rs.getLong("ID"), rs.getString("NAME"), userName, rs.getString("PASSWORD"), rs.getDate("DOB")); } }); }
It is so much easier to deal with collections of data using JdbcTemplate
. The following code snippet illustrates the query method of JdbcTemplate
with bind parameters and a RowMapper
that converts ResultSet
into a list of type: <Task>
.
@Override public List<Task> findCompletedTasksByAssignee(Long assigneeId) { String query = "SELECT * FROM TBL_TASK WHERE STATUS = ? AND ASSIGNEE_USER_ID = ? "; return this.jdbcTemplate.query(query, new Object[] {"Complete", assigneeId }, new RowMapper<Task>() { @Override public Task mapRow(ResultSet rs, int rowNum) throws SQLException{ Task task = new Task(); task.setId(rs.getLong("id")); Long assigneeId = rs.getLong("assignee_user_id"); if (assigneeId != null) task.setAssignee(userDAO.findById(assigneeId)); task.setComments(rs.getString("comments")); task.setName(rs.getString("name")); ... return task; } }); }
JdbcTemplate
takes care of all the repeating code for you and you just need to write the specific code, which is about how you map the data of a row with your domain object.
Another variation of row mapping that uses a ResultSetExtractor
interface that extracts a single row from ResultSet
is illustrated in the following code:
@Transactional(readOnly = true) public User findUserById(Long userId) { return jdbcTemplate.query("SELECT NAME, USER_NAME, PASSWORD, DOB, PROFILE_IMAGE_ID, PROFILE_IMAGE_NAME FROM TBL_USER WHERE ID = ?", new Object[] { userId }, new ResultSetExtractor<User>() { @Override public User extractData(ResultSet rs) throws SQLException, DataAccessException { if (rs.next()) { return new User(userId, rs.getString("NAME"), rs.getString("USER_NAME"), rs.getString("PASSWORD"), rs.getDate("DOB")); } else { return null; } } }); }
Now let's take a look at some update statements. The following is the execution of a simple INSERT
statement as one-liner code. The SQL UPDATE
and DELETE
statements follow the same pattern.
@Override public void createUser(User user) { jdbcTemplate.update("INSERT INTO TBL_USER(NAME, USER_NAME, PASSWORD, DOB) VALUES(?,?,?,?)", new Object[] { user.getName(), user.getUserName(), user.getPassword(), user.getDateOfBirth()}); }
The preceding method has a drawback. Although it inserts the new user record into the table, the generated ID (probably by a database sequence) is not returned back; you would need to issue another query to retrieve it separately. However, JdbcTemplate
offers a nice way to solve this problem: using a KeyHolder
class. It is another variation
of the update
method which was explained in the following code; you can retrieve the generated key (ID in this case) in a single execution, using a KeyHolder
class in combination with PreparedStatementCreator
:
public void createUser(User user) { KeyHolder keyHolder = new GeneratedKeyHolder(); jdbcTemplate.update( new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement( "INSERT INTO TBL_USER(NAME,USER_NAME,PASSWORD,DOB) VALUES(?,?,?,?)", new String[]{"ID"}); ps.setString(1, user.getName()); ps.setString(2, user.getUserName()); ps.setString(3, user.getPassword()); ps.setDate(4, new java.sql.Date(user.getDateOfBirth().getTime())); return ps; } }, keyHolder); user.setId(keyHolder.getKey().longValue()); }
JdbcTemplate
makes batch updates easy, following the same pattern as shown earlier. Take a look at the following code: it executes a single PreparedStatement
over a collection of data:
@Override public void createUsers(List<User> users) { int[] updateCounts = jdbcTemplate.batchUpdate("INSERT INTO TBL_USER(NAME, USER_NAME, PASSWORD, DOB) VALUES(?,?,?,?)", new BatchPreparedStatementSetter() { public void setValues(PreparedStatement ps, int idx) throws SQLException { ps.setString(1, users.get(idx).getName()); ps.setString(2, users.get(idx).getUserName()); ps.setString(3, users.get(idx).getPassword()); ps.setDate(4, new java.sql.Date(users.get(idx) .getDateOfBirth().getTime())); } public int getBatchSize() { return users.size(); } }); }
So far, we have used JdbcTemplate
with bind parameters using ?
placeholders. When it comes to a bigger number of parameters, a named parameter is a better choice for readability and maintainability. NamedParameterJdbcTemplate
, a specialized version of JdbcTemplate,
supports using named parameters rather than traditional ?
placeholders. Instead of extending from JdbcTemplate
, NamedParameterJdbcTemplate
uses the underlying JdbcTemplate
for its operations.
You can define NamedParameterJdbcTemplate
in the same way as the classic JdbcTemplate
, passing a DataSource
object as a mandatory dependency. Then, you can use it just like JdbcTemplate
, but using named parameters instead of bound parameters (?
). The following code snippet illustrates the use of the NamedParameterJdbcTemplate
query method that uses RowMapper
for object-relational mapping.
public User findByUserName(String userName, DataSource dataSource) { NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource); SqlParameterSource namedParameters = new MapSqlParameterSource("USER_NAME", userName); return jdbcTemplate.queryForObject("SELECT ID, NAME, USER_NAME, PASSWORD, DOB, PROFILE_IMAGE_ID, PROFILE_IMAGE_NAME FROM TBL_USER WHERE USER_NAME = :USER_NAME", namedParameters, new RowMapper<User>() { @Override public User mapRow(ResultSet rs, int rowNum) throws SQLException { return new User(rs.getLong("ID"), rs.getString("NAME"), userName, rs.getString("PASSWORD"), rs.getDate("DOB")); } }); }
SimpleJdbc
classes are another nice approach to accessing data in a more object-oriented fashion, but still using the same JdbcTemplate
internally. They belong to the org.springframework.jdbc.core.simple
package. There are two classes in it:
SimpleJdbcCall
SimpleJdbcInsert
SimpleJdbcCall
handles calls to stored procedures and functions and SimpleJdbcInsert
deals with SQL INSERT
commands to database tables. Both are DatabaseMetadata
-aware, hence they auto-detect or map similarly named fields of domain objects. Both of them act as templates for performing JDBC operations around a relational entity (a stored procedure or function and a database table respectively), accepting parameters that determine the behavior of the operation once (declared globally), and then reusing it repeatedly with a dynamic set of data at runtime.
A SimpleJdbcCall
class is declared as follows:
SimpleJdbcCall createTaskStoredProc = new SimpleJdbcCall(dataSource) .withFunctionName("CREATE_TASK") .withSchemaName("springessentials") .declareParameters(new SqlOutParameter("v_newID", Types.INTEGER), new SqlParameter("v_name", Types.VARCHAR), new SqlParameter("v_STATUS", Types.VARCHAR), new SqlParameter("v_priority", Types.INTEGER), new SqlParameter("v_createdUserId", Types.INTEGER), new SqlParameter("v_createdDate", Types.DATE), new SqlParameter("v_assignedUserId", Types.INTEGER), new SqlParameter("v_comment", Types.VARCHAR));
The preceding code declares SimpleJdbcCall
, which invokes a stored procedure (in PostgreSQL, stored procedures are also called functions) and all its parameters. Once this is declared, it can be reused any number of times at runtime. Usually, you declare it at the class level (of your DAO). The following code illustrates how we invoke it at runtime:
@Override public void createTask(Task task) { SqlParameterSource inParams = new MapSqlParameterSource().addValue("v_name", task.getName()) .addValue("v_STATUS", task.getStatus()) .addValue("v_priority", task.getPriority()) .addValue("v_createdUserId", task.getCreatedBy().getId()) .addValue("v_createdDate", task.getCreatedDate()) .addValue("v_assignedUserId", task.getAssignee() == null ? null : task.getAssignee().getId()) .addValue("v_comment", task.getComments()); Map<String, Object> out = createTaskStoredProc.execute(inParams); task.setId(Long.valueOf(out.get("v_newID").toString())); }
SimpleJdbcInsert
is typically declared as shown in the following code:
SimpleJdbcInsert simpleInsert = new SimpleJdbcInsert(dataSource) .withTableName("tbl_user") .usingGeneratedKeyColumns("id");
Note the declaration of the generated key column beside the table name in the following code snippet. Again, this is usually declared at the class level for better reuse. Now, take a look at how this is invoked at runtime.
public void createUser(User user) { Map<String, Object> parameters = new HashMap<>(4); parameters.put("name", user.getName()); parameters.put("user_name", user.getUserName()); parameters.put("password", user.getPassword()); parameters.put("dob", user.getDateOfBirth()); Number newId = simpleInsert.executeAndReturnKey(parameters); user.setId(newId.longValue()); }
You can see that the generated key is returned after the execution, which is set back to the User
object. SimpleJdbcCall
and SimpleJdbcInsert
are convenient alternatives to the vanilla JdbcTemplate
; you can use any of these solutions consistently or you can mix-and-match them in the same application.
A set of classes belonging to the org.springframework.jdbc.object
package offers another method of performing JDBC operations in a more object-oriented manner. The following table lists the most common of them:
Component |
Responsibilities |
---|---|
|
Concrete representation of a SQL query, supporting a |
|
Executes an SQL update ( |
|
Performs SQL-based calls for stored procedures and functions with support for named-parameters and keyholders (for retrieving generated keys). |
The following code illustrates the use of MappingSqlQuery
:
public Task findById(Long taskId) { MappingSqlQuery<Task> query = new MappingSqlQuery<Task>() { @Override protected Task mapRow(ResultSet rs, int rowNum) throws SQLException { return new RowMapper<Task>() { @Override public Task mapRow(ResultSet rs, int rowNum) throws SQLException { Task task = new Task(); task.setId(rs.getLong("id")); ... return task; } }.mapRow(rs, rowNum); } }; query.setJdbcTemplate(jdbcTemplate); query.setSql("select id, name, status, priority, created_user_id," + " created_date, assignee_user_id, completed_date, comments " + "from tbl_task where id = ?"); query.declareParameter(new SqlParameter("id", Types.INTEGER)); return query.findObject(taskId); }
SQL updates (INSERT
, UPDATE
, and DELETE
) can be performed using SqlUpdate
with a more descriptive code, as the example in the following code illustrates:
@Override public void deleteTask(Task task) { SqlUpdate sqlUpdate = new SqlUpdate(this.jdbcTemplate.getDataSource(), "DELETE FROM TBL_TASK WHERE ID = ?"); sqlUpdate.declareParameter(new SqlParameter("ID", Types.NUMERIC)); sqlUpdate.compile(); sqlUpdate.update(task.getId()); }
SqlUpdate
provides a variety of convenient update methods, suitable for many parameter combinations. You can mix-and-match any of the preceding listed Spring JDBC components according to your convenience and preferred programming style.