整合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); } ~~~