Spring JdbcTemplate查询示例

旧城等待， 2023-02-15 05:28 14阅读 0赞

以下是一些示例，向您展示如何使用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示例][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>
    
    	
    	<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][https_github.com_mkyong_spring-boot.git]  
> $ cd spring-jdbc  
> $查找com.mkyong.customer

## 参考文献 ##

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

标签： [h2][] [jdbc][] [查询][Link 1] [spring][] [spring jdbc][]

> 翻译自: [https://mkyong.com/spring/spring-jdbctemplate-querying-examples/][https_mkyong.com_spring_spring-jdbctemplate-querying-examples]

[Spring Boot JDBC]: https://www.mkyong.com/spring-boot/spring-boot-jdbc-examples/
[https_github.com_mkyong_spring-boot.git]: https://github.com/mkyong/spring-boot.git
[H2]: https://www.h2database.com/html/main.html
[Spring JDBC]: https://docs.spring.io/spring/docs/current/spring-framework-reference/data-access.html#jdbc
[SQL]: https://docs.spring.io/spring-boot/docs/2.1.3.RELEASE/reference/htmlsingle/#boot-features-sql
[JdbcTemplate queryForInt]: https://www.mkyong.com/spring/jdbctemplate-queryforint-is-deprecated/
[h2]: https://mkyong.com/tag/h2/
[jdbc]: https://mkyong.com/tag/jdbc/
[Link 1]: https://mkyong.com/tag/query/
[spring]: https://mkyong.com/tag/spring/
[spring jdbc]: https://mkyong.com/tag/spring-jdbc/
[https_mkyong.com_spring_spring-jdbctemplate-querying-examples]: https://mkyong.com/spring/spring-jdbctemplate-querying-examples/