整合JDBC · JAVA

[TOC] # 简介直接在 Java 程序中使⽤用 JDBC ⽐比较复杂，需要 7 步才能完成数据库的操作: * 加载数据库驱动 * 建⽴立数据库连接 * 创建数据库操作对象 * 定义操作的 SQL 语句 * 执行数据库操作 * 获取并操作结果集 * 关闭对象 ~~~ try { // 1、加载数据库驱动 Class.forName(driver); // 2、获取数据库连接 conn = DriverManager.getConnection(url, username, password); // 3、获取数据库操作对象 stmt = conn.createStatement(); // 4、定义操作的 SQL 语句句 String sql = "select * from user where id = 6"; // 5、执⾏行行数据库操作 rs = stmt.executeQuery(sql); // 6、获取并操作结果集 while (rs.next()) { // 解析结果集 } } catch (Exception e) { // ⽇日志信息 } finally { // 7、关闭资源 } ~~~ # 配置 ~~~ <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.18</version> </dependency> ~~~ ~~~ spring.datasource.url=jdbc:mysql://localhost:3306/app?serverTimezone=PRC&useUnicode=true&characterEncoding=utf-8 spring.datasource.username=root spring.datasource.password=root spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver ~~~ # 案例 ## 创建表 ~~~ CREATE TABLE `users` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id', `name` varchar(32) DEFAULT NULL COMMENT '用户名', `password` varchar(32) DEFAULT NULL COMMENT '密码', `age` int DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; ~~~ ## 创建实体实体类的数据类型要和数据库字段⼀一对应: * Long 对应 bigint * String 对应 varchar * int 对应 int ~~~ @Data public class User { private Long id; private String name; private String password; private int age; public User(String name, String password, int age) { this.name = name; this.password = password; this.age = age; } public User() { } } ~~~ ## 封装结果集接收对象 ~~~ import org.springframework.jdbc.core.RowMapper; import java.sql.ResultSet; import java.sql.SQLException; public class UserRowMapper implements RowMapper<User> { @Override public User mapRow(ResultSet rs, int rowNum) throws SQLException { User user = new User(); user.setId(rs.getLong("id")); user.setName(rs.getString("name")); user.setPassword(rs.getString("password")); user.setAge(rs.getInt("age")); return user; } } ~~~ ## 封装Repository ~~~ public interface UserRepository { int save(User user); int update(User user); int delete(long id); List<User> findALL(); User findById(long id); } ~~~ 创建 UserRepositoryImpl实现接口 ~~~ @Repository public class UserRepositoryImpl implements UserRepository { @Autowired private JdbcTemplate jdbcTemplate; @Override public int save(User user) { return jdbcTemplate.update("Insert into users(name, password, age) values (?, ?, ?)", user.getName(), user.getPassword(), user.getAge()); } @Override public int update(User user) { return jdbcTemplate.update(" update users set name = ?, password = ?, age = ? where id = ? ", user.getName(), user.getPassword(), user.getAge(), user.getId()); } @Override public int delete(long id) { return jdbcTemplate.update("delete from users where id = ?", id); } @Override public List<User> findALL() { return jdbcTemplate.query("select * from users", new UserRowMapper()); } @Override public User findById(long id) { return jdbcTemplate.queryForObject("select * from users where id = ?", new Object[]{id}, new BeanPropertyRowMapper<User>(User.class)); } } ~~~ ## 测试 ~~~ @RunWith(SpringRunner.class) @SpringBootTest(classes = ***.class) public class UserRepositoryTests { @Autowired private UserRepository userRepository; @Test public void testSave() { User user = new User("neo", "123456", 30); userRepository.save(user); } @Test public void testUpdate() { User user = new User("neo", "123456", 18); user.setId(1L); userRepository.update(user); } @Test public void testQueryOne() { User user = userRepository.findById(1L); System.out.println("user == " + user.toString()); } @Test public void testQueryAll() { List<User> users = userRepository.findALL(); for (User user : users) { System.out.println("user == " + user.toString()); } } } ~~~ # 多数据源 ~~~ spring.datasource.primary.jdbc-url=jdbc:mysql://localhost:3306/app?serverTimezone=PRC&useUnicode=true&characterEncoding=utf-8 spring.datasource.primary.username=root spring.datasource.primary.password=root spring.datasource.primary.driver-class-name=com.mysql.cj.jdbc.Driver spring.datasource.secondary.jdbc-url=jdbc:mysql://localhost:3306/mybatis?serverTimezone=PRC&useUnicode=true&characterEncoding=utf-8 spring.datasource.secondary.username=root spring.datasource.secondary.password=root spring.datasource.secondary.driver-class-name=com.mysql.cj.jdbc.Driver ~~~ 添加了两个数据源注意，这里使用的是 `spring.datasource.*.jdbc-url`，因为默认连接池 HikariCP 读取的是 jdbc-url ## 初始化 JDBC 在项⽬目启动的时候读取配置文件中的信息，并对 JDBC 初始化 ~~~ @Configuration public class DataSourceConfig { @Primary @Bean("primaryDataSource") @Qualifier("primaryDataSource") @ConfigurationProperties(prefix = "spring.datasource.primary") public DataSource primaryDataSource() { return DataSourceBuilder.create().build(); } @Bean("secondaryDataSource") @Qualifier("secondaryDataSource") @ConfigurationProperties(prefix = "spring.datasource.secondary") public DataSource secondaryDataSource() { return DataSourceBuilder.create().build(); } @Bean(name = "primaryJdbcTemplate") public JdbcTemplate primaryJdbcTemplate(@Qualifier("primaryDataSource") DataSource dataSource) { return new JdbcTemplate(dataSource); } @Bean(name = "secondaryJdbcTemplate") public JdbcTemplate secondaryJdbcTemplate(@Qualifier("secondaryDataSource") DataSource dataSource) { return new JdbcTemplate(dataSource); } } ~~~ ## Repository改造所有方法改造,增加一个参数jdbcTemplate ~~~ @Repository public class UserRepositoryImpl implements UserRepository { @Autowired private JdbcTemplate primaryJdbcTemplate; @Override public int save(User user, JdbcTemplate jdbcTemplate) { return jdbcTemplate.update("Insert into users(name, password, age) values (?, ?, ?)", user.getName(), user.getPassword(), user.getAge()); } ~~~ ## 测试 ~~~ @RunWith(SpringRunner.class) @SpringBootTest(classes = **.class) public class UserRepositoryTests { @Autowired private UserRepository userRepository; @Autowired private JdbcTemplate primaryJdbcTemplate; @Autowired private JdbcTemplate secondaryJdbcTemplate; @Test public void testSave() { User user = new User("neo", "123456", 30); userRepository.save(user, primaryJdbcTemplate); userRepository.save(user, secondaryJdbcTemplate); } ~~~