How to use Spring JdbcTemplate

Photo by Christopher Gower on Unsplash

Jdbc is heavily used in Spring boot to CRUD data. Let’s go through how to insert, retrieve, update and delete data using JdbcTemplate. JdbcTemplate is inside the org.springframework.jdbc.core.JdbcTemplate.

Insert, Update and Delete data

In JdbcTemplate insert, update, delete are done using query function as follows and it will return 1 if successfully done the job or else it will return 0.

query = "INSERT INTO Student Values("Yashod", 25)";
int updated = this.jdbcTemplate.update(query);

Wait what if we need to inject data to the query? Okay following is some example.

query = "INSERT INTO Student Values(?, ?)";
this.jdbcTemplate.update(query, "Yashod", 25);

Retrieve data

This is bit tricky. There are several cases where we need to get objects, one object, one column data or multiple column data. Let’s go one by one.

Retrieve an object

If we getting a specific type object we need to have a mapper for that class as follows.

• Employee class

class Employee {
  private String name;
  private int age;
  private String address;
  public void setName(String name) {
    this.name = name;
  }
  public String getName() {
    return this.name;
  }
  public void setAge(int age) {
    this.age = age;
  }
  public int getAge() {
    return this.age;
  }
  public void setAddress(String address) {
    this.address = address;
  }
}

• Employee Mapper Class

import org.springframework.jdbc.core.RowMapper;
import cogi.metrics.app.dao.Employee;
import java.sql.ResultSet;
import java.sql.SQLException;
public class EmployeeMapper implements RowMapper<Employee> {

  @Override
  public Employee mapRow(ResultSet rs, int i) throws SQLException {
        Employee employee = new Employee();
        employee.setName(rs.getString("name"));
        employee.setAge(rs.getInt("age"));
        employee.setAddress(rs.getString("address"));
        return employee;
    }
}

Note — Highlighted fields should be same as database column name.

Let’s go to retrieve object.

String query = "Select * from employees Where name = 'Yashod'";
Employee emp = this.jdbcTemplate
                   .queryForObject(query, new EmployeeMapper());

Ohh then how to inject data.

String query = "Select * from employees Where name = ?";
Employee emp = this.jdbcTemplate
                   .queryForObject(query, 
                                   new EmployeeMapper(),      
                                   "yashod");

• Common mistake done.

There are two run time exception which will pop up when there is no row is returning or more than one rows are returning. In most of the case developers forget to handle those. There is a example for the exception handling.

try {
  String query = "Select * from employees Where age = ?";
  Employee emp = this.jdbcTemplate
                   .queryForObject(query, 
                                   new EmployeeMapper(),      
                                   21);
} catch (EmptyResultDataAccessException e) {
  // throw error or return value;
} catch (IncorrectResultSizeDataAccessException e) {
  // throw error or return value; 
}

Retrieve multiple objects

It is simple as follows. It gets the list of objects and thats it.

String query = "Select * from employees"
List<Employee> employees = this.jdbcTemplate
                               .query(query, new EmployeeMapper());

Injecting data is simple as above.

String query = "Select * from employees where age = ?"
List<Employee> employees = this.jdbcTemplate
                               .query(query, 
                                      new EmployeeMapper(),
                                      21);

Retrieve one column

In some cases we have to get one column list or exactly one column. The syntax is as follows.

this.jdbcTemplate
    .queryForObject(sqlQuery, new Object[] {}, returnTypeClass);

sqlQuery is the query and returnTypeClass should be the return type class as an example it can be Long.class , Integer.class , String.class .

Let’s move for an example.

String query = "Select name from employees age = 23";
List<String> names = this.jdbcTemplate
                      .query(query, new Object[] {}, String.class);

This will return the list of names of the employees whose age is 23. If we need exact one item then we have to use queryForObject .

Wait wait how can we pass parameters.

String query = "Select name from employees age = ?";
List<String> names = this.jdbcTemplate.query(query, 
                                             new Object[] {23}, 
                                             String.class);

Retrieve more columns

This is bit tricky but not hard. There is a function called queryForList which is used to get multiple columns using a query and it will come as a map<String, Object> then the keys would be the column names. Let’s go for an example.

String query = "Select name, address from employees age = ?";
List<Map<String, Object>> employees 
            = this.jdbcTemplate.queryForList(query,
                                             new Object[] {21});
for (Map<String, Object> employee: employees) {
  System.out.println(employee.get("name));
}

To learn more.

Spring JDBC Tutorial | Baeldung

Hopefully this is helpful.

If you have found this helpful please hit that 👏 and share it on social media :).