In this article, you will learn how to use JdbcTemplate
to execute a query.jdbctemplate select 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.jdbctemplate select query
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 “jdbctemplate select query”
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