JdbcTemplate Example to Select Records

In this article, you will learn how to use JdbcTemplate to execute a query.

JDBC Query Process

In the below diagram, you can see the basic template of a JDBC query process.

 

 

Spring provides callback method at various points. One of them is when you want to execute a statement. You can encapsulate the actual execution code within an object StatementCallback class.

 

 

In our example, we will query employees and return them as  beans.

Employee:

package com.javarticles.jdbc.jdbctemplate;

public class Employee {
    private int id;
    private String name;
    private int age;

    public Employee(int id, String name, int age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }

    public int getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public int getAge() {
        return age;
    }
    public String toString() {
        return "Employee: [" + name + ", ID: " + id + ", AGE " + age + "]";
    }
}

 

Simplest approach to query using JdbcTemplate

The most simplest, approach is to pass the sql to queryForList and it will return a List of rows where each row is a Map of column values.

    public void queryEmployee() throws SQLException { 
        String sql = "SELECT ID, NAME, AGE FROM EMPLOYEE";
        List<Map<String, Object>> list = getJdbcTemplate().queryForList(sql);
        for (Map<String, Object> row : list) {
            System.out.println(row.get("name"));
        }   
    }

Output:

Sam
John

 

Query using ResultSetExtractor

To understand the role of ResultSetExtractor, lets go back to our query diagram to the step where we call executeQuery on the statement object. The result we obtain will is in the form of ResultSet. You may want to convert the ResultSet into a more specific type like list of Employee beans in our case. To achieve this you will have to iterate through the ResultSet, extracting each row into Employee object and add it to the list. This can be encapsulated within spring provided ResultSetExtractor callback object.

 

    public void queryEmployee() throws SQLException { 
        String sql = "SELECT ID, NAME, AGE FROM EMPLOYEE";        
        List empList = getJdbcTemplate().query(sql, new ResultSetExtractor<List>(){

            public List extractData(ResultSet rs) throws SQLException,
                    DataAccessException {
                List empList = new ArrayList();
                while(rs.next()) {
                    Employee emp = new Employee(rs.getInt("ID"), rs.getString("NAME"), rs.getInt("AGE"));                    
                    empList.add(emp);
                }
                return empList;
            }});
        
        System.out.println(empList);
    }

Output:

[Employee: [Sam, ID: 1, AGE 0], Employee: [John, ID: 2, AGE 0]]

Query using RowMapper

In the previous example, we have iterated through the ResultSet and manufactured Employee bean for each row. We can avoid this iteration by using another spring provided callback class called RowMapper.

All we have to do is implement RowMapper and pass the sql and the RowMapper object to it to query method. In the below method, you will notice we don’t iterate through the ResultSet, all we do is  just convert the ResultSet into an Employee bean and return it. JdbcTemplate takes care of iterating and adding the Employee bean to the list.

        empList = getJdbcTemplate().query(sql, new RowMapper(){

            public Employee mapRow(ResultSet rs, int rowNum)
                    throws SQLException {
                Employee emp = new Employee(rs.getInt("ID"), rs.getString("NAME"), rs.getInt("AGE"));                    
                return emp;
            }});
        
        System.out.println(empList);

Download Source Code

In this example, I have show you how to query using JdbcTemplate.query and the various versions of query methods. You can download the source code here: springtemplateQuery