Spring JDBC abstraction

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 classes
  • RDBMS Sql* classes

Each 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

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>

Note

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:

Method

Description

execute

A set of overloaded methods for executing a SQL update (INSERT, UPDATE, and DELETE) statement, with different parameter sets including the SQL statement to be executed, bind parameters, a statement creator, and callback methods.

query

A set of overloaded methods for querying PreparedStatement for a given SQL SELECT statement with a multitude of parameter sets including bind parameters, argument types, RowMapper, ResultSetExtractor, PreparedStatementCreator, RowCallbackHandler, and so on. While methods with callbacks are void methods, the others return a list of objects of type <T> specified with the corresponding RowMapper, ResultSetExtractor, or a populated instance of type <T>.

queryForList

A set of overloaded query methods executing a SELECT query returns a list of objects of type <T> specified as an argument, Class<T> elementType. Those methods not specifying the elementType return List<Map<String, Object>>.

queryForMap

Executes a (SELECT) query and returns the result as Map<String, Object>.

queryForObject

A set of overloaded methods querying a given SQL SELECT statement with parameter sets including bind parameters, argument types, RowMapper, and the required return type <T>.

update

A set of overloaded methods issuing an update (INSERT, UPDATE, or DELETE) statement with parameter sets including bind parameters, argument types, PreparedStatementCreator, and so on. It returns an integer, which is the count of records affected.

batchUpdate

A set of overloaded methods for executing multiple SQL updates (INSERT, UPDATE, and DELETE) with different parameter sets including an array of SQL statements and many combinations of PreparedStatementSetter and other arguments.

execute

A set of overloaded methods for executing a SQL update (either INSERT, UPDATE, or DELETE) statement, with different parameter sets including the SQL statement to be executed, bind parameters, StatementCreator, and callback methods.

query

A set of overloaded methods for querying PreparedStatement for a given SQL SELECT statement with several parameter sets including bind parameters, argument types, RowMapper, ResultSetExtractor, PreparedStatementCreator, RowCallbackHandler, and so on. While those methods with callbacks are void methods, the others return a list of objects of type <T> specified with the corresponding RowMapper, ResultSetExtractor, or a populated instance of type <T>.

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

CallableStatementCreator

execute

Constructs java.sql.CallableStatement, which is used to execute stored procedures inside its createCallableStatement(Connection)method:.

PreparedStatementCreator

execute, update, query

Constructs java.sql.PreparedStatement, given a connection, inside the method, createPreparedStatement (Connection).

PreparedStatementSetter

update, query

Sets values to PreparedStatement before execution, inside JdbcTemplate.setValues (PreparedStatement).

CallableStatementCallback

execute

Prepares CallableStatement. Usually sets the IN and OUT parameters of a stored procedure or function, before the actual execution, inside JdbcTemplate.doInCallableStatement(CallableStatement).

PreparedStatementCallback

execute

Used by JdbcTemplate execute methods for preparing PreparedStatement. Usually sets the bind parameters, before the actual execution, inside the doInPreparedStatement(PreparedStatement)method:.

ResultSetExtractor

query

Extracts results from ResultSet and returns a domain object, inside the extractData(ResultSet)method:.

RowCallbackHandler

query

Processes each row of a ResultSet in a stateful manner, inside the processRow(Resultset)method, which doesn't return anything.

RowMapper

query

Maps each row of a ResultSet into a domain object, inside the mapRow(Resultset, int rowNum)method, returning the created domain object.

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

NamedParameterJdbcTemplate

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

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.

JDBC operations with Sql* classes

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

MappingSqlQuery

Concrete representation of a SQL query, supporting a RowMapper, and having a wide variety of convenient execute and find* methods. Supports named parameters too.

SqlUpdate

Executes an SQL update (INSERT, UPDATE, and DELETE) operation, with support for named parameters and keyholders (for retrieving generated keys).

SqlCall

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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset