Here are a few examples to show you how to use Spring JdbcTemplate to query or extract data from database.
Technologies used :
- Spring Boot 2.1.2.RELEASE
- Spring JDBC 5.1.4.RELEASE
- Maven 3
- Java 8
In Short:
jdbcTemplate.queryForObjectfor single row or valuejdbcTemplate.queryfor multiple rows or list
Note
The article is updated from Spring core 2.5.x to Spring Boot 2.1.x
P.S You may also interested in this Spring Boot JDBC Examples
1. Query for Single Row
In Spring, we can use jdbcTemplate.queryForObject() to query a single row record from database, and convert the row into an object via row mapper.
1.1 Custom RowMapper
CustomerRowMapper.java
import org.springframework.jdbc.core.RowMapper;
import java.sql.ResultSet;
import java.sql.SQLException;
public class CustomerRowMapper implements RowMapper<Customer> {
@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;
}
}
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());
}
1.2 Spring BeanPropertyRowMapper, this class saves you a lot of time for the 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));
}
1.3 In Java 8, we can map it directly:
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()
));
}
2. Query for Multiple Rows
For multiple rows, we use jdbcTemplate.query()
2.1 Custom RowMapper
public List<Customer> findAll() {
String sql = "SELECT * FROM CUSTOMER";
List<Customer> customers = jdbcTemplate.query(
sql,
new CustomerRowMapper());
return customers;
}
2.2 BeanPropertyRowMapper
public List<Customer> findAll() {
String sql = "SELECT * FROM CUSTOMER";
List<Customer> customers = jdbcTemplate.query(
sql,
new BeanPropertyRowMapper(Customer.class));
return customers;
}
2.3 Java 8
public List<Customer> 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()
)
);
}
2.4 jdbcTemplate.queryForList, it works, but not recommend, the mapping in Map may not same as the object, need casting.
public List<Customer> findAll() {
String sql = "SELECT * FROM CUSTOMER";
List<Customer> customers = new ArrayList<>();
List<Map<String, Object>> 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;
}
3. Query for a Single Value
It’s same like query a single row from database, uses jdbcTemplate.queryForObject()
3.1 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);
}
3.2 Count
public int count() {
String sql = "SELECT COUNT(*) FROM CUSTOMER";
// queryForInt() is Deprecated
// https://www.tuts.heomi.net/jdbctemplate-queryforint-is-deprecated/
//int total = jdbcTemplate.queryForInt(sql);
return jdbcTemplate.queryForObject(sql, Integer.class);
}
4. Test
Run a Spring Boot CommandLineRunner application, create tables and test the APIs.
pom.xml
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <!-- in-memory database --> <dependency> <groupId>com.h2database</groupId> <artifactId>h2</artifactId> </dependency>
StartApplication.java
package com.favtuts;
import com.favtuts.customer.Customer;
import com.favtuts.customer.CustomerRepository;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.jdbc.core.JdbcTemplate;
import java.math.BigDecimal;
import java.util.Arrays;
import java.util.List;
@SpringBootApplication
public class StartApplication implements CommandLineRunner {
private static final Logger log = LoggerFactory.getLogger(StartApplication.class);
@Autowired
JdbcTemplate jdbcTemplate;
@Autowired
CustomerRepository customerRepository;
public static void main(String[] args) {
SpringApplication.run(StartApplication.class, args);
}
@Override
public void run(String... args) {
log.info("StartApplication...");
startCustomerApp();
}
// Tested with H2 database
void startCustomerApp() {
jdbcTemplate.execute("DROP TABLE customer IF EXISTS");
jdbcTemplate.execute("CREATE TABLE customer(" +
"id SERIAL, name VARCHAR(255), age NUMERIC(2), created_date timestamp)");
List<Customer> list = Arrays.asList(
new Customer("Customer A", 19),
new Customer("Customer B", 20),
new Customer("Customer C", 21),
new Customer("Customer D", 22)
);
list.forEach(x -> {
log.info("Saving...{}", x.getName());
customerRepository.save(x);
});
log.info("[FIND_BY_ID]");
log.info("{}", customerRepository.findByCustomerId(1L));
log.info("{}", customerRepository.findByCustomerId2(2L));
log.info("{}", customerRepository.findByCustomerId3(3L));
log.info("[FIND_ALL]");
log.info("{}", customerRepository.findAll());
log.info("{}", customerRepository.findAll2());
log.info("{}", customerRepository.findAll3());
log.info("{}", customerRepository.findAll4());
log.info("[FIND_NAME_BY_ID]");
log.info("{}", customerRepository.findCustomerNameById(4L));
log.info("[COUNT]");
log.info("{}", customerRepository.count());
}
}
Output
INFO com.favtuts.StartApplication - Saving...Customer A
INFO com.favtuts.StartApplication - Saving...Customer B
INFO com.favtuts.StartApplication - Saving...Customer C
INFO com.favtuts.StartApplication - Saving...Customer D
INFO com.favtuts.StartApplication - [FIND_BY_ID]
INFO com.favtuts.StartApplication - Customer{ID=1, name='Customer A', age=19, createdDate=2019-08-01T15:48:45.950848}
INFO com.favtuts.StartApplication - Customer{ID=2, name='Customer B', age=20, createdDate=2019-08-01T15:48:45.961819}
INFO com.favtuts.StartApplication - Customer{ID=3, name='Customer C', age=21, createdDate=2019-08-01T15:48:45.961819}
INFO com.favtuts.StartApplication - [FIND_ALL]
INFO com.favtuts.StartApplication - [
Customer{ID=1, name='Customer A', age=19, createdDate=2019-08-01T15:48:45.950848},
Customer{ID=2, name='Customer B', age=20, createdDate=2019-08-01T15:48:45.961819},
Customer{ID=3, name='Customer C', age=21, createdDate=2019-08-01T15:48:45.961819},
Customer{ID=4, name='Customer D', age=22, createdDate=2019-08-01T15:48:45.961819}
]
//...omitted, duplicate code
INFO com.favtuts.StartApplication - [FIND_NAME_BY_ID]
INFO com.favtuts.StartApplication - Customer D
INFO com.favtuts.StartApplication - [COUNT]
INFO com.favtuts.StartApplication - 4Download Source Code
$ git clone https://github.com/favtuts/java-spring-boot-tutorials.git
$ cd spring-jdbc-queries$ find com.favtuts.customer
$ mvn spring-boot:run