Spring Boot使用JdbcTemplate访问数据库

JDBC（Java Database Connectivity）是 Java 提供用于编写应用程序作为客户端访问数据库的 API，JdbcTemplate 则是 Spring 对 JDBC 的封装。它简化了JDBC 的使用并有助于避免常见错误。JdbcTemplate 是一种轻量的持久化解决方案，仅需要非常简单的配置就可以实现对数据库的访问。因为是轻量的解决方案，当请求数据库的需求并不复杂时，JdbcTemplate 将会是一个非常好的选择。

引入依赖

为了启用 JdbcTemplate，需要引入依赖 spring-boot-starter-jdbc。另外本文以 MySQL 为例，因此还需要引入 mysql-connector-java 的依赖：

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupld>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

准备数据

Spring Boot 提供了一种自动初始化数据库的方式：从 classpath 中加载 schema.sql 与 data.sql，两个 sql 脚本分别用于创建表结构和初始化表数据。如果初始化数据需要分别支持不同的数据库，可以使用配置项 spring.datasource.platform 区分当前环境所连接的平台，并且通过“schema-${platform}.sql”与“data-${platform}.sql”这种命名方式创建对应平台的脚本文件。

这一初始化行为通过配置项 spring.datasource.initialization-mode 来控制，可选的参数有：

//总是执行初始化操作
DataSourceInitializationMode.ALWAYS
//仅当数据源为嵌入式数据库的时候
DataSourceInitializationMode.EMBEDDED
//从不执行初始化操作
 DataSourceInitializationMode.NEVER

在默认情况下，Spring Boot 启用 Spring JDBC 初始化程序的快速失败功能。这意味着如果这两个脚本的执行出现异常，就会导致程序无法启动。可以通过配置项 spring.datasource.continue-on-error 做出调整。

application.yml 示例如下：

     #application.yml模板
     spring:
       datasource:
         url: jdbc:mysql://127.0.0.1:3306/dev?useSSL=false
         username: root
         password: dbpass
         #当执行schema.sql的用户不同时，可以配置以下两项
         schema-username: root
         schema-password: dbpass
         #当执行data.sql的用户不同时，可以配置以下两项
         data-username: root
         data-password: dbpass
         platform: mysql
         schema: classpath:schema.sql
         data: classpath:data.sql
         continue-on-error: true
     initialization-mode: always

schema.sql：

     -- ----------------------------
     -- Table structure for vehicle
     -- ----------------------------
     DROP TABLE IF EXISTS `vehicle`;
     CREATE TABLE `vehicle` (
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `name` varchar(255) DEFAULT NULL,
       `price` decimal(10,2) DEFAULT NULL,
       PRIMARY KEY (`id`)
     )
     ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

data.sql：

     -- ----------------------------
     -- Records of vehicle
     -- ----------------------------
     INSERT INTO `vehicle` VALUES (1, 'Bentley', 2750000.00);
     INSERT INTO `vehicle` VALUES (2, 'Land Rover', 1468000.00);
     INSERT INTO `vehicle` VALUES (3, 'Porsche', 860000.00);
     INSERT INTO `vehicle` VALUES (4, 'Mercedes', 1980000.00);
     INSERT INTO `vehicle` VALUES (5, 'BMW', 561800.00);
     INSERT INTO `vehicle` VALUES (6, 'InfinitI', 798000.00);
     INSERT INTO `vehicle` VALUES (7, 'Cadillac', 1380000.00);
     INSERT INTO `vehicle` VALUES (8, 'Lincoln', 1580000.00);

queryForObject()方法

首先介绍基础的 queryForObject() 方法。该方法会执行一条 SQL 语句并得到一个结果对象，其中结果对象的类型需要在参数中声明。示例代码如下：

@Transactional(rollbackFor = Exception.class)
@SpringBootTest
class JdbcApplicationTests {

    @Autowired

    private JdbcTemplate jdbcTemplate;

    @Test
    void queryForObject() {
        //查询vehicle表记录数
        String sql = "SELECT count (*) FROM vehicle";
        //获得SQL语句的执行结果
        Integer numOfVehicle = jdbcTemplate.queryForObject(sql, Integer.class);
        assert numOfVehicle != null;
        System.out.format("There are %d vehicles in the table", numOfVehicle);
    }
}

使用RowMapper映射实体

当以上示例中的返回类型由包装类修改为自定义的类型时，或许会碰到 IncorrectResult SetColumnCountException 这一异常。这是因为该方法并不支持自动化映射操作化映射操作，如果需要将结果与自定义类型进行映射，将需要 RowMapper 的帮助。通过编写一个 RowMapper 的具体实现以完成结果到自定义类型的映射。

首先创建与数据库结构所对应的实体类 Vehicle.java：

@NoArgsConstructor
@Accessors(chain = true)
@Setter
@Getter
@ToString
public class Vehicle {
    private Integer id;
    private String name;
    private BigDecimal price;
}

RowMapper 相关示例代码如下：
@Transactional(rollbackFor = Exception.class)
@SpringBootTest
class JdbcApplicationTests {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    void queryForObject_WithRowMApper() {
        RowMapper<Vehicle> rm = (ResultSet result, int rowNum) -> new Vehicle().setld(result.getlnt("id")) .setName(result.getString("name")).setPrice(result.getBigDecimal("price"));
        //使用"?"作为占位符.执行SQL时将会将其替换成对应的参数
        String sql = "SELECT id,name,price FROM vehicle WHERE id = ?";
        int id = 1;
        Vehicle vehicle = jdbcTemplate.queryForObject(sql, new Object[]{id}, rm);
        assert vehicle != null;
        System.out.printIn(vehicle.toString());
    }
}

使用BeanPropertyRowMapper映射

RowMapper 固然很不错，但如果每查询出一组新结果需要进行映射操作，就需要实现一个 RowMapper，那么这个开发体验就不够友好。好在 BeanPropertyRowMapper 打消了这种顾虑。当查询结果的字段名与映射类的属性名一致时，可以使用 BeanPropertyRowMapper 代替手工实现RowMapper。

注意: 映射类需要有默认或者无参构造器。

示例代码如下：

@Transactional(rollbackFor = Exception.class)
@SpringBootTest
class JdbcApplicationTests {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    void queryForObject_WithBeanPropertyRowMapper() {
        String sql = "SELECT id, name,price FROM vehicle WHERE id = ?";
        int id = 1;
        //使用 BeanPropertyRowMapper 代替手动实现 RowMapper 接口
        Vehicle vehicle = jdbcTemplate.queryForObject(sql, new Object[]{id}, BeanPropertyRowMapper.newInstance(Vehicle.class));
        assert vehicle != null;
        System.out.println(vehicle.toString());
    }
}

queryForList()方法

以上示例的查询结果都是单个对象，当查询结果类型为列表时，则需要用到 queryForList() 方法。示例代码如下：

@Transactional(rollbackFor = Exception.class)
@SpringBootTest
class JdbcApplicationTests {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    void queryForList() {
        String sql = "SELECT id,name,price FROM vehicle";
        List<Map<String, Object>> ret = jdbcTemplate.queryForList(sql);
        assert !ret.isEmpty();
        ret.forEach(System.out::println);
    }
}

不同的JDBCTemplate实现NamedParameterJdbcTemplate

使用基础的 JdbcTemplate 执行 SQL 语句时，所编写的 SQL 语句的参数占位符默认为“?”。当需要传入的参数增加到一定数量，参照这样编写的 SQL 语句将会变得难以理解。此时将需要用到 NamedParameterJdbcTemplate。使用 NamedParameterJdbcTemplate 可以将有含义的占位符来代替“?”。示例代码如下：

@Transactional(rollbackFor = Exception.class)
@SpringBootTest
class JdbcApplicationTests {

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Test
    void queryForObject_WithNamedParameterJdbcTemplate() {
        String sql = "SELECT id,name,price FROM vehicle WHERE name like :name AND price> :price LIMIT 1";

        //参数名与SQL语句中的占位符需要保持一致
        MapSqlParameterSource mapSqlParameterSource = new MapSqlParameterSource().addValue("name", "B%").addValue("price", "60000");
        Vehicle vehicle = namedParameterJdbcTemplate.queryForObject(sql,mapSqlParameterSource, BeanPropertyRowMapper.newInstance(Vehicle.class));
        assert vehicle != null;
        System.out.println(vehicle.toString());
    }
}

update()方法

以上示例都在介绍如何获取一个或一组数据，当需要对记录进行更新时，则需要调用 update() 方法。该更新操作包含对记录的增加、修改以及删除。示例代码如下：

@Transactional(rollbackFor = Exception.class)
@SpringBootTest
class JdbcApplicationTests {

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Test
    void update_SaveVehicle() {
        //新增一条记录
        String sql = "INSERT INTO vehicle(name,price) VALUES(:name,:price)";
        MapSqlParameterSource mapSqlParameterSource = new MapSqlParameterSource().addValue("name", "Tesla").addValue("price", "850000");
        int ret = namedParameterJdbcTemplate.update(sql, mapSqlParameterSource);
        assert ret > 0;
    }

    @Test
    void updateUpdateVehicle() {
        //更新数条记录
        String sql = "UPDATE vehicle SET price = price*0.9 WHERE price > : price**;
        MapSqlParameterSource mapSqlParameterSource = new MapSqlParameterSource().addValue("price", "1000000");
        int ret = namedParameterJdbcTemplate.update(sql, mapSqlParameterSource);
        assert ret > 0;
    }

    @Test
    void update_DeleteVehicle() {
        //删除数条记录
        String sql = "DELETE FROM vehicle WHERE price < :price";
        MapSqlParameterSource mapSqlParameterSource = new MapSqlParameterSource().addValue("price", "1000000");
        int ret = namedParameterJdbcTemplate.update(sql, mapSqlParameterSource);
        assert ret > 0;
    }
}