Spring JdbcTemplate查询示例

以下是一些示例，向您展示如何使用Spring JdbcTemplate查询或从数据库提取数据。

使用的技术：

• Spring Boot 2.1.2发布
• Spring JDBC 5.1.4.RELEASE
• Maven 3
• Java 8

简而言之：

• jdbcTemplate.queryForObject或单值的jdbcTemplate.queryForObject
• jdbcTemplate.query用于多个行或列表

注意
本文已从Spring Core 2.5.x更新到Spring Boot 2.1.x

PS您可能对此Spring Boot JDBC示例也感兴趣

1.查询单行

在Spring中，我们可以使用jdbcTemplate.queryForObject()从数据库查询单行记录，然后通过行映射器将行转换为对象。

1.1自定义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 ，该类为您节省了很多映射时间。

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在Java 8中，我们可以直接将其映射：

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.查询多行

对于多行，我们使用jdbcTemplate.query()

2.1自定义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 ，它可以工作，但不建议这样做， Map的映射可能与对象不相同，需要强制转换。

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.查询单个值

就像从数据库查询单行一样，使用jdbcTemplate.queryForObject()

3.1单列名称

public String findCustomerNameById(Long id) {
        String sql = "SELECT NAME FROM CUSTOMER WHERE ID = ?";
        return jdbcTemplate.queryForObject(
                sql, new Object[]{id}, String.class);
    }

3.2计数

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

4.测试

运行一个Spring Boot CommandLineRunner应用程序，创建表并测试API。

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

输出量

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

下载源代码

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

参考文献

• Spring Boot JDBC示例 _
• H2数据库引擎
• Spring JDBC文档
• 使用SQL数据库
• JdbcTemplate queryForInt（）已弃用
• Java JDBC教程

标签： h2 jdbc 查询 spring spring jdbc

翻译自: https://mkyong.com/spring/spring-jdbctemplate-querying-examples/