Spring Boot JdbcTemplate使用教程

作者 | 2020年5月4日

本文我们将学习如何在Spring Boot中使用JdbcTemplate进行数据查询。

下面给出我们的实验环境:

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

简单的来说:

  • jdbcTemplate.queryForObject()用于单行记录的查询
  • jdbcTemplate.query()用于多行记录的查询

1. 查询单行记录

在Spring Boot中,jdbcTemplate.queryForObject()用于查询单行记录,并通过RowMapper将该行记录转为一个对象。

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;

    }
}

下面看看如何在jdbcTemplate.queryForObject()方法中使用这个自定义的RowMapper:

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 在Java8中,我们可以直接通过Lambda表达式指定映射关系。

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 Java8

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<String, Object>

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 直接返回String

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.mkyong.com/spring/jdbctemplate-queryforint-is-deprecated/
    //int total = jdbcTemplate.queryForInt(sql);

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

}

发表评论

电子邮件地址不会被公开。 必填项已用*标注