SpringBoot2.x入门教程：引入jdbc模块与JdbcTemplate简单使用 (3)

由于spring.datasource.initialization-mode指定为ALWAYS，每次数据源初始化都会执行spring.datasource.schema中配置的脚本，会删表重建。接着类路径的resources文件夹下添加dml/data.sql：

INSERT INTO customer(customer_name) VALUES ('throwable');

添加一个CommandLineRunner实现验证一下：

@Slf4j @SpringBootApplication public class Ch7Application implements CommandLineRunner { @Autowired private DataSource dataSource; public static void main(String[] args) { SpringApplication.run(Ch7Application.class, args); } @Override public void run(String... args) throws Exception { Connection connection = dataSource.getConnection(); ResultSet resultSet = connection.createStatement().executeQuery("SELECT * FROM customer WHERE id = 1"); while (resultSet.next()) { log.info("id:{},name:{}", resultSet.getLong("id"), resultSet.getString("customer_name")); } resultSet.close(); connection.close(); } }

启动后执行结果如下：

这里务必注意一点，spring.datasource.schema指定的脚本执行成功之后才会执行spring.datasource.data指定的脚本，如果想仅仅执行spring.datasource.data指定的脚本，那么需要至少把spring.datasource.schema指向一个空的文件，确保spring.datasource.schema指定路径的文件初始化成功。

使用JdbcTemplate

spring-boot-starter-jdbc中自带的JdbcTemplate是对JDBC的轻度封装。这里只简单介绍一下它的使用方式，构建一个面向前面提到的customer表的具备CURD功能的DAO。这里先在前文提到的DruidAutoConfiguration中添加一个JdbcTemplate实例到IOC容器中：

@Bean public JdbcTemplate jdbcTemplate(DataSource dataSource){ return new JdbcTemplate(dataSource); }

添加一个Customer实体类：

// 实体类 @Data public class Customer { private Long id; private String customerName; private LocalDateTime createTime; private LocalDateTime editTime; }

接着添加一个CustoemrDao类，实现增删改查：

// CustoemrDao @RequiredArgsConstructor @Repository public class CustomerDao { private final JdbcTemplate jdbcTemplate; /** * 增 */ public int insertSelective(Customer customer) { StringJoiner p = new StringJoiner(",", "(", ")"); StringJoiner v = new StringJoiner(",", "(", ")"); Optional.ofNullable(customer.getCustomerName()).ifPresent(x -> { p.add("customer_name"); v.add("?"); }); Optional.ofNullable(customer.getCreateTime()).ifPresent(x -> { p.add("create_time"); v.add("?"); }); Optional.ofNullable(customer.getEditTime()).ifPresent(x -> { p.add("edit_time"); v.add("?"); }); String sql = "INSERT INTO customer" + p.toString() + " VALUES " + v.toString(); KeyHolder keyHolder = new GeneratedKeyHolder(); int updateCount = jdbcTemplate.update(con -> { PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); int index = 1; if (null != customer.getCustomerName()) { ps.setString(index++, customer.getCustomerName()); } if (null != customer.getCreateTime()) { ps.setTimestamp(index++, Timestamp.valueOf(customer.getCreateTime())); } if (null != customer.getEditTime()) { ps.setTimestamp(index, Timestamp.valueOf(customer.getEditTime())); } return ps; }, keyHolder); customer.setId(Objects.requireNonNull(keyHolder.getKey()).longValue()); return updateCount; } /** * 删 */ public int delete(long id) { return jdbcTemplate.update("DELETE FROM customer WHERE id = ?", id); } /** * 查 */ public Customer queryByCustomerName(String customerName) { return jdbcTemplate.query("SELECT * FROM customer WHERE customer_name = ?", ps -> ps.setString(1, customerName), SINGLE); } public List<Customer> queryAll() { return jdbcTemplate.query("SELECT * FROM customer", MULTI); } public int updateByPrimaryKeySelective(Customer customer) { final long id = Objects.requireNonNull(Objects.requireNonNull(customer).getId()); StringBuilder sql = new StringBuilder("UPDATE customer SET "); Optional.ofNullable(customer.getCustomerName()).ifPresent(x -> sql.append("customer_name = ?,")); Optional.ofNullable(customer.getCreateTime()).ifPresent(x -> sql.append("create_time = ?,")); Optional.ofNullable(customer.getEditTime()).ifPresent(x -> sql.append("edit_time = ?,")); StringBuilder q = new StringBuilder(sql.substring(0, sql.lastIndexOf(","))).append(" WHERE id = ?"); return jdbcTemplate.update(q.toString(), ps -> { int index = 1; if (null != customer.getCustomerName()) { ps.setString(index++, customer.getCustomerName()); } if (null != customer.getCreateTime()) { ps.setTimestamp(index++, Timestamp.valueOf(customer.getCreateTime())); } if (null != customer.getEditTime()) { ps.setTimestamp(index++, Timestamp.valueOf(customer.getEditTime())); } ps.setLong(index, id); }); } private static Customer convert(ResultSet rs) throws SQLException { Customer customer = new Customer(); customer.setId(rs.getLong("id")); customer.setCustomerName(rs.getString("customer_name")); customer.setCreateTime(rs.getTimestamp("create_time").toLocalDateTime()); customer.setEditTime(rs.getTimestamp("edit_time").toLocalDateTime()); return customer; } private static ResultSetExtractor<List<Customer>> MULTI = rs -> { List<Customer> result = new ArrayList<>(); while (rs.next()) { result.add(convert(rs)); } return result; }; private static ResultSetExtractor<Customer> SINGLE = rs -> rs.next() ? convert(rs) : null; }

测试结果如下：