SpringBoot 之 整合JDBC使用

導入相關依賴：

# pom.xml

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

配置資料庫連接信息：

# src/main/resources/application.yml

spring:
  datasource:
    username: root
    password: 123456
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
    # driver-class-name: com.mysql.jdbc.Driver # 已過時
    driver-class-name: com.mysql.cj.jdbc.Driver

測試連接：

@SpringBootTest
public class SpringbootDataApplicationTests {
    @Autowired
    DataSource dataSource;

    @Test
    public void contextLoads() throws SQLException {
    	// 查看預設數據源
        System.out.println(dataSource.getClass());
        
    	// 查看鏈接
        Connection connection = dataSource.getConnection();
        System.out.println(connection);
        connection.close();
    }
}

簡單使用示例：

# src/main/java/com/wu/controller/JdbcController.java

@RestController
public class JdbcController {
    @Autowired
    JdbcTemplate jdbcTemplate;
 
    @GetMapping("/users")
    public List<Map<String, Object>> index() {
        String sql = "select * from user";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return list;
    }
 
    @PostMapping("/users")
    public int store() {
        String sql = "insert into user(id,name) values (1,'小明')";
        return jdbcTemplate.update(sql);
    }
 
    @GetMapping("/users/{id}")
    public Map<String, Object> show(@PathVariable("id") int id) {
        String sql = "select * from user where id=" + id;
        Map<String, Object> map = jdbcTemplate.queryForObject(sql);
        return map;
    }
 
    @PutMapping("/users/{id}")
    public int update(@PathVariable("id") int id) {
        String sql = "update user set name=? where id=" + id;
        Object[] objects = new Object[1];
        objects[0] = "小花";
        return jdbcTemplate.update(sql, objects);
    }
 
    @DeleteMapping("/users/{id}")
    public int destroy(@PathVariable("id") int id) {
        String sql = "delete from user where id=?";
        return jdbcTemplate.update(sql, id);
    }
}