How to Replace Deprecated jdbcTemplate.queryForObject and jdbcTemplate.query in Spring Boot 2.4.X and above

1. Overview

In this tutorial, we’ll discuss the deprecated queryForObject() and query() methods of JdbcTemplate and their preferred counterparts.

The deprecated methods accept parameters passed within an array of objects, whereas the new methods utilize Varargs for passing parameters. This approach is more intuitive because Varargs are designed specifically for efficiently passing a variable number of arguments to a method.

Throughout our tutorial, we’ll explain all the methods by running queries on a student table in the in-memory H2 database.

2. Database Setup

Before we look at the methods in question, let’s first examine the student table, which we’ll use in all our examples:

CREATE TABLE student (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(255) NOT NULL,
    age INT,
    grade INT NOT NULL,
    gender VARCHAR(10) NOT NULL,
    state VARCHAR(100) NOT NULL
);
-- Student 1
INSERT INTO student (student_name, age, grade, gender, state) VALUES ('John Smith', 18, 3, 'Male', 'California');

-- Student 2
INSERT INTO student (student_name, age, grade, gender, state) VALUES ('Emily Johnson', 17, 2, 'Female', 'New York');

--More insert statements...

3. Deprecated Method query()

In JdbcTemplate, there are three query() method variations, each using a different functional interface to collect or process the rows from the query result. These interfaces are ResultSetExtractor, RowCallbackHandler, and RowMapper.

Let’s see each of them and their suggested replacements.

3.1. query() Method with RowMapper

The query() method in the JdbcTemplate class returns a list representing rows returned by the DB query. Let’s begin with an example where we’ll query the student table to get students of a certain age and gender:

public List<Student> getStudentsOfAgeAndGender(Integer age, String gender) {
    String sql = "select student_id, student_name, age, gender, grade from student where age= ? and gender = ?";
    Object[] args = {age, gender};
    return jdbcTemplate.query(sql, args, new StudentRowMapper());
}

In the above method, the variable args of type Object[] stores the query parameters. Even if it is a single query parameter, it needs to be added to an array, which is inconvenient.

Suppose we need additional filter criteria to get students of a certain grade. Then, we have to write a new method for it:

public List<Student> getStudentsOfAgeGenderAndGrade(Integer age, String gender, Integer grade) {
    String sql = "select student_id, student_name, age, gender, grade from student where age= ? and gender = ? and grade = ?";
    Object[] args = {age, gender, grade};
    return jdbcTemplate.query(sql, args, new StudentRowMapper());
}

So, to implement this, we modified sql and args variables. But, can we eliminate the boiler-plate code involving the args variable? Let’s explore this further in the following method:

public List<Student> getStudentsOfAgeGenderAndGrade(Integer age, String gender, String grade) {
    String sql = "select student_id, student_name, age, gender, grade from student where age= ? and gender = ? and grade = ?";
    return jdbcTemplate.query(sql, new StudentRowMapper(), age, gender, grade);
}

The varargs variation of the jdbcTemplate.query did exactly what we wanted.

Let’s see the deprecated and the replacement methods in action:

@Test
public void givenDeprecatedMethodQuery_whenArgsAgeAndGender_thenReturnStudents() {
    List<Student> students = studentDaoWithDeprecatedJdbcTemplateMethods.getStudentsOfAgeGenderAndGrade(4, "Female", 2);
    for (Student student: students) {
        logger.info("Student Name: " + student.getStudentName() + " Student gender: " + student.getStudentGender());
    }
    assertEquals(5, students.size());
}

@Test
public void givenPreferredMethodQuery_whenArgsAgeAndGender_thenReturnStudents() {
    List<Student> students = studentDaoWithPreferredJdbcTemplateMethods.getStudentsOfAgeGenderAndGrade(4, "Female", 2);

    for (Student student: students) {
        logger.info("Student Name: " + student.getStudentName() + " Student gender: " + student.getStudentGender());
    }
    assertEquals(5, students.size());
}

As we can see above, the two test methods invoke getStudentsOfAgeAndGender() of the Dao classes, StudentDaoWithDeprecatedJdbcTemplateMethods and StudentDaoWithPreferredJdbcTemplateMethods. Following is the output of both methods:

Student Name: Olivia Garcia Student gender: Female Student grade: 2
Student Name: Ava Davis Student gender: Female Student grade: 2
Student Name: Olivia Johnson Student gender: Female Student grade: 2
Student Name: Isabella Davis Student gender: Female Student grade: 2
Student Name: Sophia Hernandez Student gender: Female Student grade: 2

No doubt, the varargs version serves its intended purpose.

In the upcoming sections, we’ll mostly discuss the usage of the deprecated and preferred methods. The benefits of the varargs version would be the same as discussed in this section. Hence, we won’t be repeating that.

3.2. query() Method with ResultSetExtractor

Now, let’s move on to the next deprecated version that uses the ResultSetExtractor interface. The method extractData() in the interface gets invoked once so that all the rows can be returned in a data structure after processing. For this example, we have created a StudentResultExtractor:

public class StudentResultExtractor implements ResultSetExtractor<List<Student>> {
    @Override
    public List<Student> extractData(ResultSet rs) throws SQLException {
        List<Student> students = new ArrayList<Student>();
        while(rs.next()) {
            Student student = new Student();
            student.setStudentId(rs.getInt("student_id"));
            student.setStudentName(rs.getString("student_name"));
            student.setAge(rs.getInt("age"));
            student.setStudentGender(rs.getString("gender"));
            student.setGrade(rs.getInt("grade"));
            student.setState(rs.getString("state"));
            students.add(student);
        }
        return students;
    }
}

It returns a List of Student.

Let’s take a look at the query method using the array of objects to pass query parameters, and StudentResultExtractor mentioned above:

public List<Student> getStudentsOfGradeAndState(Integer grade, String state) {
    String sql = "select student_id, student_name, age, gender, grade, state from student where grade = ? and state = ?";
    Object[] args = {grade, state};
    return jdbcTemplate.query(sql, args, new StudentResultExtractor());
}

The above method queries the student table to retrieve students studying in a specific grade and belonging to a particular state.

Similar to the earlier section, we’ll implement the same with the varargs version of the query method:

public List<Student> getStudentsOfGradeAndState(Integer grade, String state) {
    String sql = "select student_id, student_name, age, gender, grade, state from student where grade = ? and state = ?";
    return jdbcTemplate.query(sql, new StudentResultExtractor(), grade, state);
}

Finally, we invoke the above two methods in separate test functions:

@Test
public void givenDeprecatedMethodQuery_whenArgsGradeAndState_thenReturnStudents() {
    List<Student> students = studentDaoWithDeprecatedJdbcTemplateMethods.getStudentsOfGradeAndState(1, "New York");
    for (Student student: students) {
        logger.info("Student Name: " + student.getStudentName()
          + " Student grade: " + student.getStudentGender()
          + " Student State: " + student.getState());
    }
    assertEquals(6, students.size());
}

@Test
public void givenPreferredMethodQuery_whenArgsGradeAndState_thenReturnStudents() {
    List<Student> students = studentDaoWithPreferredJdbcTemplateMethods.getStudentsOfGradeAndState(1, "New York");
    for (Student student: students) {
        logger.info("Student Name: " + student.getStudentName()
          + " Student grade: " + student.getStudentGender()
          + " Student State: " + student.getState());
    }
    assertEquals(6, students.size());
}

As expected, both the tests give the same output:

Student Name: Ethan Rodriguez Student grade: Male Student State: New York
Student Name: Benjamin Brown Student grade: Male Student State: New York
Student Name: Matthew Martinez Student grade: Male Student State: New York
Student Name: Christopher Lee Student grade: Male Student State: New York
Student Name: Liam Johnson Student grade: Male Student State: New York
Student Name: Mason Smith Student grade: Male Student State: New York

3.3. query() Method with RowCallbackHandler

Lastly, in this section, we’ll discuss the version using the interface RowCallbackHandler. For this example, we would use RowCountCallbackHandler, which is a subclass of RowCallbackHandler.

Let’s start by taking a look at the deprecated version:

public Integer getCountOfStudentsInAGradeFromAState(String grade, String state) {
    String sql = "select student_id, student_name, age, gender, grade, state from student where grade = ? and state = ?";
    Object[] args = {grade, state};
    RowCountCallbackHandler countCallbackHandler = new RowCountCallbackHandler();
    jdbcTemplate.query(sql, args, countCallbackHandler);
    return countCallbackHandler.getRowCount();
}

The method, true to its name, gets the total number of students studying in a given grade and belonging to a particular state.

Similarly, here is the method using the preferred version of the query method:

public Integer getCountOfStudentsInAGradeFromAState(String grade, String state) {
    String sql = "select student_id, student_name, age, gender, grade, state from student where grade = ? and state = ?";

    RowCountCallbackHandler countCallbackHandler = new RowCountCallbackHandler();
    jdbcTemplate.query(sql, countCallbackHandler, grade, state);
    return countCallbackHandler.getRowCount();
}

Let’s see how these methods can be invoked:

@Test
public void givenDeprecatedMethodQuery_whenArgsGradeAndState_thenReturnCount() {
    Integer count = studentDaoWithDeprecatedJdbcTemplateMethods.getCountOfStudentsInAGradeFromAState(1, "New York");
    logger.info("Total students of grade 1 from New York:" + count);
    assertEquals(6, count);
}

@Test
public void givenPreferredMethodQuery_whenArgsGradeAndState_thenReturnCount() {
    Integer count = studentDaoWithPreferredJdbcTemplateMethods.getCountOfStudentsInAGradeFromAState(1, "New York");
    logger.info("Total students of grade 1 from New York:" + count);
    assertEquals(6, count);
}

As shown below, unsurprisingly, they yield identical results:

Total students of grade 1 from New York: 6

4. Deprecated Method queryForObject()

The method queryForObject() also has two deprecated variants that use the Object[] type for passing the query parameters. These are queryForObject(String sql, Object[] args, Class<T> requiredType) and queryForObject(String sql, Object[] args, RowMapper<T> rowMapper).

Like before, we’ll explore their suggested replacements using Varargs in the upcoming sections.

4.1. queryForObject() Method with RowMapper

Unlike the query() method, queryForObject() is meant for handling a DB query result with a single row. Along the same lines, let’s first check out the following method that uses the deprecated version of queryForObject():

public Student getStudentOfStudentIDAndGrade(Integer studentID, Integer grade) {
    String sql = "select student_id, student_name, age, gender, grade from student where student_id = ? and grade = ?";
    Object[] args = {studentID, grade};

    return jdbcTemplate.queryForObject(sql, args, new StudentRowMapper());
}

The above method returns the student from a grade having a specific student ID. But just like the deprecated version of the query() method, it also needs to declare a variable of type Object[] for the query parameters.

Since the above promotes boiler-plate code, let’s see a cleaner approach:

public Student getStudentOfStudentIDAndGrade(Integer studentID, Integer grade) {
    String sql = "select student_id, student_name, age, gender, grade from student where student_id = ? and grade = ?";

    return jdbcTemplate.queryForObject(sql, new StudentRowMapper(), studentID, grade);
}

However, here, there is no need to declare a variable of type Object[]. The varargs variant of queryForObject() directly takes the studentID and grade arguments as the last parameters.

Now, let’s look at how the methods are used:https://www.baeldung.com/wp-admin/post.php?post=165771&action=edit

@Test
public void givenDeprecatedMethodQueryForObject_whenArgsStudentIDAndGrade_thenReturnStudent() {
    Student student = studentDaoWithDeprecatedJdbcTemplateMethods.getStudentOfStudentIDAndGrade(4, 1);
    assertEquals(1, student.getGrade());
    assertEquals(4, student.getStudentId());
    logger.info("Student ID: " + student.getStudentId()
      + " Student Name: " + student.getStudentName() + " Student grade: " + student.getGrade());
}

@Test
public void givenPreferredMethodQueryForObject_whenArgsStudentIDAndGrade_thenReturnStudent() {
    Student student = studentDaoWithPreferredJdbcTemplateMethods.getStudentOfStudentIDAndGrade(4, 1);
    assertEquals(1, student.getGrade());
    assertEquals(4, student.getStudentId());
    logger.info("Student ID: " + student.getStudentId()
      + " Student Name: " + student.getStudentName() + " Student grade: " + student.getGrade());
}

As expected, both methods give the same output:

Student ID: 4 Student Name: Sophia Martinez Student grade: 1

4.2. queryForObject() With Class<T>

Let’s begin by taking a look at the example using the deprecated method queryForObject(String sql, Object[] args, Class<T> requiredType):

public Integer getCountOfGenderInAGrade(String gender, Integer grade) {
    String sql = "select count(1) as total from student where gender = ? and grade = ?";
    Object[] args = {gender, grade};

    return jdbcTemplate.queryForObject(sql, args, Integer.class);
}

The above method returns the total number of students of a certain gender studying in a given grade.

Let’s see the approach with the preferred method queryForObject(String sql, Class<T> requiredType, Object… args):

public Integer getCountOfGenderInAGrade(String gender, Integer grade) {
    String sql = "select count(1) as total from student where gender = ? and grade = ?";

    return jdbcTemplate.queryForObject(sql, Integer.class, gender, grade);
}

As usual, in the above method, we’re able to get rid of the variable args of type Object[].

Now, let’s go through the following methods showing getCountOfGenderInAGrade() in action:

@Test
public void givenPreferredMethodQueryForObject_whenArgsGenderAndGrade_thenReturnCount() {
    Integer count = studentDaoWithPreferredJdbcTemplateMethods.getCountOfGenderInAGrade("Female", 2);
    assertEquals(6, count);
    logger.info("Total number of Female Students: " + count);
}

@Test
public void givenDeprecatedMethodQueryForObject_whenArgsGenderAndGrade_thenReturnCount() {
    Integer count = studentDaoWithPreferredJdbcTemplateMethods.getCountOfGenderInAGrade("Female", 2);
    assertEquals(6, count);
    logger.info("Total number of Female Students: " + count);
}

Finally, as shown below, the replacement method successfully manages to get a similar result:

Total number of Female Students: 6

5. Conclusion

In this tutorial, we explored the preferred replacements of the deprecated variants of query() and queryForObject() methods of the JdbcTemplate class. With examples, we explained the new methods that use Varargs to take the query parameters as arguments. We also saw how it helps eliminate the boilerplate code for getting the parameters in an array of objects.