Spring JDBCTemplate

JDBCTemplate is the central class in the JDBC core package. It handles the creation and release of resources, which helps you avoid common errors like forgetting to close a resource etc. Along with this it supports the basic jdbc workflow like creating a statement, executing a statement and iterating over the resultset etc. here you need to write the business logic for using the resultset values. In this way the core functionally required to execute your sql query will be facilitated by Jdbc Template.

Apart from this if you want to work with your own logic in executing the statements and iterating the results values etc, spring JdbcTemplate allows you to work with PreparedStatements and CallableStatements by providing classes like PreparedStatementCreator, PreparedStatementCallback and CallableStatementCreator, CallableStatementCallback etc. When working with those classes, these classes has two phases of execution.

1) Prepare or create phase
    2) Execute and Callback to handle the result phase.

In the prepare phase you need to create the prepared statement by using the connection object passed to you by JdbcTemplate and in the CallBack phase you need to execute the statement and need to iterator over the resultset values. Here it seems like you are not getting most out spring jdbc, but the creation and closing of the resources has been taken care by JdbcTemplateitself.

Working with JdbcTemplate involves two ways,

One is straight away using the JdbcTemplate methods to get the resultset values.

Another is creating your own statements and call back handlers.

Working with PreparedStatements using JdbcTemplate

Spring JdbcTemplate provides lot of methods which supports executing an SQL statement and returning the results in pojp object.if you want to write your own logic of handling the resultset values rather than wrapping into a pojp object, then you need to create a statement and iterator over the resultset to perform business logic.
JdbcTemplate exposes convenient methods which allow you to execute a PreparedStatement.

Executing a PreparedStatement involves two phases, creating and preparing the PreparedStatement using the PreparedStatementCreator class and executing and wrapping the resultset values in callback phase using PreparedStatementCallback.

Before creating the PreparedStatementCreator or callback class to execute the statement, we need to setup the JdbcTemplate with a Datasource object. In order to execute any Sql Query we needs connection object and this would be provided by the DataSource bean which we discussed in the previous section. So, while JdbcTemplate performs any operation it will try to get the connection from the datasource with which we created it.

persistence-beans.xml

<bean id=”jdbcTemplate” class=”org.springframework.jdbc.core.JdbcTemplate”>
         <constructor-arg ref=”dataSource”/>
   </bean>

EmployeeDao.java

import java.sql.Connection;
import java.sql.PreparedStatement; 
import java.sql.ResuleSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.sql.DataSource;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import com.emp.business.Employee;

public class EmployeeDao {
       private JdbcTemplate jdbcTemplate;

       //insert datasource and create jdbcTemplate
       public EmployeeDao(DataSource dataSource) {
              jdbcTemplate = new JdbcTemplate(datasource);
       }

       public List<Employee> getEmployeesByName(final String empName) {
              PreparedStatementCreator creator = new PreparedStatementCreator() {

                    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                           PreparedStatement ps = connection.preparedStatement(“select * from tblemp where emp_name like ?”);
                           ps.setString(1, empName);
                           return ps;
                    }
          };

In contd…..

                    PreparedStatementCallback<List<Employee>> callback = new PreparedStatementCallback<List<Employee>>() {
                    public List<Employee> doInPreparedStatement(PreparedStatement preparedStatement) throws SQLException, DataAccessException {
                           List<Employee> employees = new ArrayList<Employee> ();
                           ResultSet rs = preparedStatement.executeQuery();
                           while (rs.next()) {
                                  Employee e = new Employee(rs.getInt(“emp_id”), rs.getString(“emp_name”), rs.getFloat(“salary”));
                                  employees.add(e);
                               }
                                 return employees;
                             }
                         };
                           return jdbcTemplate.execute(creator, callback);
                    }
       }

persistence-beans.xml

<bean id=”employeeDao” class=”com.emp.dao.EmployeeDao”>
      <constructor-arg ref=”dataSource”/>
</bean>

Using JdbcTemplate Operations

As we discuss earlier let us try to explore how to perform various DML and DQL operations using the methods of JdbcTemplate class in the following sections.

Aggregate Operations

The aggregate operations that could be performed on a database are SUM, AVG, COUNT etc. All these functions will result in single columned results. So in order to execute an aggregate query using JdbcTemplate class, there is a method queryForInt(), to this method you need to pass the SQL where the result of execution of that query should yield an integer single columned value. Let us try to understand with an example how to work with it.

StudentDao.java

import org.springframework.jdbc.core.JdbcTemplate;
import com.jdbctemplate.business.StudentBO;

public class StudentDao {
       private final String SQL_COUNT_OF_STUDENT = “SELECT COUNT(*) FROM STUDENT”;
       private JdbcTemplate jdbcTemplate;
       public StudentDao(JdbcTemplate jdbcTemplate) {
              this.jdbcTemplate = jdbcTemplate;
      }
       public int getCountOfStudents() {
              return jdbcTemplate.queryForInt(SQL_COUNT_OF_STUDENT);
      }
}

In the above code we called the method queryForInt to execute our sql query and give the result of execution as Integer rather than ResultSet. In further detail to it, we haven’t created the statement or ResultSet objects or even the connection rather we just configure the JdbcTemplate class as a bean in the configuration and inject into StudentDao as constructor argument, and JdbcTemplate is going to borrow the connection object and going to execute the sql query you passed to it.

Note: In the above code the SQL Query has been declared as an constant String variable SQL_COUNT_OF_STUDENT.

Query Single column value

You may query one value from a column for example finding a Student Name by Student Id; here the Student Id is a primary key so that always the result of query execution against a primary key column would yield only one value. As we are trying to find student name by primary key student id the result is one student name.

In order to query a single column value as Object, we need to use queryForObject() method. It has lot of variants, out of which one of it takes parameter as the SQL query and the type of value of value returned out of execution and Object array, as we are selecting only NAME by STUDENT_ID, the result of execution is a String value. In the WHERE clause of the query we need to specify the condition on STUDENT_ID =?. In this (?) is this (?) is the place holder or substitution positional parameter for which we are going to provide the value while executing the query, which is shown below.

StudentDao.java

import org.springframework.jdbc.core.JdbcTemplate;
import com.jdbctemplate.business,StudentBO;

public class StudentDao {

       private final String SQL_FIND_NAME_BY_ID = “SELECT NAME FROM STUDENT WHERE STUDENT_ID = ?”;
       private JdbcTemplate jdbcTemplate;

       public StudentDao(JdbcTemplate jdbcTemplate) {
              this.jdbcTemplate = jdbcTemplate;
       }

       public String getStudentNameById(int studentID) {
              return jdbcTemplate.queryForObject(SQL_FIND_NAME_BY_ID, String.class. new Object[] { studentID } );
       }
}

In the above example the query contains a (?) which is a place holder going to be replaced while executing it. If you observe while calling queryForObject method the first parameter is SQL query, second one is the type of outcome we are expecting after executing the query and third argument is Object array containing studentID value, this value will be replaced with the (?)’s, then you need to pass two values as part of your Object array, so that the first (?) will be replaced with first value of the Object array and the second one with second value of the array respectively.

Query Single Row as Object

In order to query a record from a table, we need to use queryForObject() method, as we said there are multiple signatueres of the queryForObject() method, the one we use here take parameters as queryForObject(SQL, Object[], RoeMapper); If you observe the method signature, the third argument to the method is RowMapper.

The JdbcTemplate can execute the query which you have provided to it by replacing the (?) with Object[] values. But after executing the query, it cannot wrap the result values into your pojo. So the RowMapper is an Interface which contains a method mapRow(ResultSet, Row Index), you need to pass the object of RowMapper implementation class to the JdbcTemplate’s, queryForObject method.

Once the method has finished executing the query it will passes the result set object to the mapRow method of the RowMapper object to convert the resultset values into Object.

So, you need to write the code for mapping the resultset values to object in the mapRow method and should provide it to JdbcTemplate, using which the JdbcTemplate will executes the query and build the object for you. This is shown in the below example.

StudentDao.java

import java.sql.ResultSet;
   
   import org.springframework.jdbc.core.JdbcTemplate;
   import org.springframework.jdbc.core.RowMapper;

   import com.jdbctemplate.business.StudentBO;

   public class StudentDao {

          private final String SQL_FIND_STUDENT_BY_ID = “SELECT STUDENT_ID, NAME FROM STUDENT WHERE STUDENT_ID = ?”;
          private JdbcTemplate jdbcTemplate;

          public StudentDao(JdbcTemplate jdbcTemplate) {
                 this.jdbcTemplate = jdbcTemplate;
         }

         public StudentBO findStudent(int id) {
                return jdbcTemplate.queryForObject(SQL_FIND_STUDENT_BY_ID, new StudentRowMapper(), new Object[] { id } );
         }
         private static final class StudentRowMapper implements RowMapper<StudentBO> {
                 @Override
                 public StudentBO mapRow(ResultSet rs, int rowIndex) throws SQLException {
                        return new StudentBO(rs.getInt(“STUDENT_ID”), rs.getString(“NAME”));
                 }
         }
   }

StudentBO.java

public class StudentBO {
          private int student_id;
          private String name;

          public StudentBO(int student_id, String name) {
                 this.student_id = student_id;
                 this.name = name;
          }

          // setters and getters on the attributes

           @Override
            public String toString() {
                   return (“Student ID : “ + this.getStudent_id() + “ Name : “ + this.getName());
            }
   }

Query Multiple Rows as List of Objects

This is similar to working with querying one row as object but the only difference is instead of calling queryForObject(), you need to call the simple query() method. Same is shown below

StudentDao.java

import java.sql.ResultSet;
   
   import org.springframework.jdbc.core.JdbcTemplate;
   import org.springframework.jdbc.core.RowMapper;
   
   import com.jdbctemplate.business.StudentBO;

   public class StudentDao {

          private final String SQL_SELECT_ALL_STUDENTS = “SELECT STUDENT_ID, NAME FROM STUDENT”;
          private JdbcTemplate jdbcTemplate;          

          public StudentDao(JdbcTemplate jdbcTemplate) {
                 this.jdbcTemplate = jdbcTemplate;
          }

          public List<StudentBO> getAllStudents() {
                 return jdbcTemplate.query(SQL_SELECT_ALL_STUDENTS, new StudentRowMapper());
          }

          private static final class StudentRowMapper implements RowMapper<StudentBO> {
          @Override
          public StudentBO mapRow(ResultSet rs, int rowIndex) throws SQLException {
                 return new StudentBO(rs.getInt(“STUDENT_ID”), rs.getString(“NAME”));
          }
      }
  }

Insert/Update/Deleting a record

In order to perform Insert or Update or Delete operations on a table, we need to use the update method provided in JdbcTemplate class. This method will perform the Insert/Update/Delete based on the query you passed to it. The result of executing this method is an integer indicating the number of rows affected due to the execution.

The update method takes arguments as update(SQL, Object[]), where the values in the Object[] would be replaced with the (?) place holders.

StudentDao.java

import java.sql.ResultSet;
 
   import org.springframework.jdbc.core.JdbcTemplate;

   public class StudentDao {

          private final String SQL_INSERT_STUDENT = “INSERT INTO STUDENT(STUDENT_ID, NAME) VALUES(?, ?)”;
          private JdbcTemplate jdbcTemplate;

          public StudentDao(JdbcTemplate jdbcTemplate) {
                 this.jdbcTemplate = jdbcTemplate;
          }

          public int insert(int student_id, String name) {
                 return jdbcTemplate.update(SQL_INSERT_STUDENT, new Object[] { student_id, name } );
          }
   } 

In the above example instead of passing INSERT query to the update method if you pass UPDATE SQL query it will performs update operation the same applies to DELETE as well.

Batch Operations

If we want to perform bulk insert or update operations on a database, we need to use batchUpdate method, this method takes parameter as BatchPreparedStatementSetter Interface implementation object.

The BatchPreparedStatementSetter interface has methods setValues() and getBatchSize(). The getBatchSize() method should return a Integer value indicating number of records you want to insert as part of operation. The setValues() method takes argument as PreparedStatement, the batchUpdate method while inserting the records it would call the setValues() method for BatchSize no of times by passing PreparedStatement object as parameter to you, in this method you need to set the values which you want to insert as part of this batch. The same is shown below

StudentDao.java

import java.sql.ResultSet;

   import org.springframework.jdbc.core.JdbcTemplate; 
   import org.springframework.jdbc.core.RowMapper;

   import com.jdbctemplate.business.StudentBO;
  
   public class StudentDao {

          private final String SQL_INSERT_STUDENT = “INSERT INTO STUDENT(STUDENT_ID, NAME) VALUES(?, ?)”;
          private JdbcTemplate jdbcTemplate;

          public StudentDao(JdbcTemplate jdbcTemplate) {
                 this.jdbcTemplate = jdbcTemplate;
          }

          public int[] insert(final List<StudentBO> students) {
                 return jdbcTemplate.batchUpdate(SQL_INSERT_STUDENT, new StudentBatchSetter());
          } 

          private static final class StudentBatchSetter implements BatchPreparedStatementSetter {
                 @Override
                 public void setValues(PreparedStatement preparedStatement, int index) throws SQLException {
                        preparedStatement.setInt(1, students.get(index).getStudent_id());
                        preparedStatement.setString(2,students.get(index).getName());
                 } 

                 @Override
                 public int getBatchSize() {
                        return students.size();
                 }
          }
    }

Pagination

public class StudentDao {
          //pagination query
          private final String SQL_GET_ALL_STUDENTS_ORDER_BY_NAME = “SELECT STUDENT_ID, NAME, MOBILE, EMAIL FROM STUDENT ORDER BY NAME”;

          private JdbcTemplate jdbcTemplate;

          public StudentDao(JdbcTemplate jdbcTemplate) {
                 this.jdbcTemplate = jdbcTemplate;
          }

          public  List<StudentBO> getStudents(final int pageSize, final int pageNo) {
                  List<StudentBO> students = null;
                  int totalRecords = 0;
                  int startIndex = 0;
                  int endIndex = 0;

                  totalRecords = getNoOfStudents();
                  startIndex = (pageSize * (pageNo – 1)) + 1;
                  endIndex = ((startIndex + pageSize) – 1) > totalRecords ? totalRecords : (startIndex + pageSize) – 1;
                  students = jdbcTemplate.query(SQL_GET_ALL_STUDENTS_ORDER_BY_NAME, new StudentPaginationResultSetExtractor(startIndex, endIndex));
                  return students;
          }
          Private final static class StudentPaginationResultSetExtractor implements ResultSetExtractor<List<StudentBO>> {
                  private int startIndex;
                  private int endIndex;

                  public StudentPaginationResultSetExtractor(int startIndex, int endIndex) {
                         this.startIndex = startIndex;
                         this.endIndex = endIndex;
                  }
                  
                  @Override
                  public List<StudentBO> extractData(ResultSet rs) throws SQLException, DataAccessException {
                         int row = 1;
                         StudentBO bo = null;
                         List<StudentBO> students = null;

                         students = new ArrayList<StudentBO>();

                         while (row <= endIndex && rs.next()) {
                               if (row >= startIndex)  {
                                       bo = new StudentBO(rs.getInt(“STUDENT_ID”), rs.getSytring(“NAME”), rs.getString(“MOBILE”), rs.getString(“EMAIL”));
                                       students.add(bo);
                               }
                               row++;
                         }
                          return students;
                }
         }
    }

Thanks for reading. If you like this post please follow us for more updates about technology related updates.