JdbcTemplate in Spring Framework: Best Practices for Java Querying

1. Query for Multiple Row

For multiple rows, we use jdbcTemplate.query()

 public List findAll() {
    
        String sql = "SELECT * FROM CUSTOMER";

        List customers = jdbcTemplate.query(
                sql,
                new CustomerRowMapper());

        return customers;
        
    }

Use BeanPropertyRowMapper — It will reduce your manual row mapping efforts [Recommended]

public List findAll() {

        String sql = "SELECT * FROM CUSTOMER";

        List customers = jdbcTemplate.query(
                sql,
                new BeanPropertyRowMapper(Customer.class));

        return customers;
    }

Take benefits of java 8 for row mapping with resultset data.

public List findAll() {

        String sql = "SELECT * FROM CUSTOMER";

        return jdbcTemplate.query(
                sql,
                (rs, rowNum) ->
                        new Customer(
                                rs.getLong("id"),
                                rs.getString("name"),
                                rs.getInt("age"),
                                rs.getTimestamp("created_date").toLocalDateTime()
                        )
        );
    }

jdbcTemplate.queryForList, it works, but not recommend, the mapping in Map may not same as the object, need casting.

public List findAll() {

        String sql = "SELECT * FROM CUSTOMER";

        List customers = new ArrayList<>();

        List> rows = jdbcTemplate.queryForList(sql);

        for (Map row : rows) {
            Customer obj = new Customer();

            obj.setID(((Integer) row.get("ID")).longValue());
            obj.setName((String) row.get("NAME"));
            // Spring returns BigDecimal, need convert
            obj.setAge(((BigDecimal) row.get("AGE")).intValue()); 
            obj.setCreatedDate(((Timestamp) row.get("CREATED_DATE")).toLocalDateTime());
            customers.add(obj);
        }

        return customers;
    }

In Spring, we can use jdbcTemplate.queryForObject() to query a single row record from the database, and convert the row into an object via row mapper.

import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;

public class CustomerRowMapper implements RowMapper {

    @Override
    public Customer mapRow(ResultSet rs, int rowNum) throws SQLException {

        Customer customer = new Customer();
        customer.setID(rs.getLong("ID"));
        customer.setName(rs.getString("NAME"));
        customer.setAge(rs.getInt("AGE"));
        customer.setCreatedDate(rs.getTimestamp("created_date").toLocalDateTime());

        return customer;

    }
}

Now, Bind resultset value with Custom Row Mapper (*DaoImpl.java)

import org.springframework.jdbc.core.JdbcTemplate;

    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    public Customer findByCustomerId(Long id) {

        String sql = "SELECT * FROM CUSTOMER WHERE ID = ?";

        return jdbcTemplate.queryForObject(sql, new Object[]{id}, new CustomerRowMapper());

    }

Spring BeanPropertyRowMapper, this class saves you a lot of time for the mapping. [Recommended]

This class saves you a lot of time for mapping.

import org.springframework.jdbc.core.BeanPropertyRowMapper;
    
    public Customer findByCustomerId2(Long id) {

        String sql = "SELECT * FROM CUSTOMER WHERE ID = ?";

        return (Customer) jdbcTemplate.queryForObject(
            sql, 
            new Object[]{id}, 
            new BeanPropertyRowMapper(Customer.class));

    }

public Customer findByCustomerId3(Long id) {

        String sql = "SELECT * FROM CUSTOMER WHERE ID = ?";

        return jdbcTemplate.queryForObject(sql, new Object[]{id}, (rs, rowNum) ->
                new Customer(
                        rs.getLong("id"),
                        rs.getString("name"),
                        rs.getInt("age"),
                        rs.getTimestamp("created_date").toLocalDateTime()
                ));

    }

It’s the same like query a single row from the database, uses jdbcTemplate.queryForObject()

Count Query

public int count() {
 String sql = "SELECT COUNT(*) FROM CUSTOMER";
 return jdbcTemplate.queryForObject(sql, Integer.class);      
}

Single column name

 public String findCustomerNameById(Long id) {

        String sql = "SELECT NAME FROM CUSTOMER WHERE ID = ?";

        return jdbcTemplate.queryForObject(
                sql, new Object[]{id}, String.class);

    }

How easy it is! Comment what you learned with the above examples. Your support will be appreciated.