Spring JdbcTemplate Querying Examples

 

Spring JdbcTemplate Querying Examples

参考地址： https://www.mkyong.com/spring/spring-jdbctemplate-querying-examples/

 

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.queryForObject for single row or value
• jdbcTemplate.query for 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;

    }
}

Copy

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());

    }

Copy

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));

    }

Copy

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;
		
    }

Copy

2.2 BeanPropertyRowMapper

    public List<Customer> findAll() {

        String sql = "SELECT * FROM CUSTOMER";

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

        return customers;
    }

Copy

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()
                        )
        );
    }

Copy

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;
    }

Copy

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);

    }

Copy

3.2 Count

    public int count() {

        String sql = "SELECT COUNT(*) FROM CUSTOMER";

        // queryForInt() is Deprecated
        // https://www.mkyong.com/spring/jdbctemplate-queryforint-is-deprecated/
        //int total = jdbcTemplate.queryForInt(sql);

        return jdbcTemplate.queryForObject(sql, Integer.class);

    }

Copy

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>

Copy

StartApplication.java

package com.mkyong;

import com.mkyong.customer.Customer;
import com.mkyong.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());

    }

}

Copy

Output

INFO  com.mkyong.StartApplication - Saving...Customer A
INFO  com.mkyong.StartApplication - Saving...Customer B
INFO  com.mkyong.StartApplication - Saving...Customer C
INFO  com.mkyong.StartApplication - Saving...Customer D
INFO  com.mkyong.StartApplication - [FIND_BY_ID]
INFO  com.mkyong.StartApplication - Customer{ID=1, name='Customer A', age=19, createdDate=2019-08-01T15:48:45.950848}
INFO  com.mkyong.StartApplication - Customer{ID=2, name='Customer B', age=20, createdDate=2019-08-01T15:48:45.961819}
INFO  com.mkyong.StartApplication - Customer{ID=3, name='Customer C', age=21, createdDate=2019-08-01T15:48:45.961819}
INFO  com.mkyong.StartApplication - [FIND_ALL]
INFO  com.mkyong.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.mkyong.StartApplication - [FIND_NAME_BY_ID]
INFO  com.mkyong.StartApplication - Customer D
INFO  com.mkyong.StartApplication - [COUNT]
INFO  com.mkyong.StartApplication - 4

Copy

Download Source Code

$ git clone https://github.com/mkyong/spring-boot.git
$ cd spring-jdbc
$ find com.mkyong.customer

References

• Spring Boot JDBC Examples_
• H2 Database Engine
• Spring JDBC docs
• Working with SQL Databases
• JdbcTemplate queryForInt() is Deprecated
• Java JDBC Tutorial