# Spring Boot JDBC
## 添加相關的依賴
```
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
</dependency>
```
- `spring-boot-starter-jdbc`:Spring Boot提供的jdbc，默認使用Tomcat的jdbc
- `mysql-connector-java`:Mysql的java驅動包
- `spring-boot-devtools`:這個是Spring Boot提供給開發者在開發項目是的功能，對項目的控制更靈活，其中一個作用是我們修改代碼和配置文件后不用手動重啟（修改包依賴還是要重啟）
## 配置文件
```
#myslq數據庫連接
spring.datasource.url=jdbc:mysql://192.168.87.131:3306/wukong?useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.datasource.username=root
spring.datasource.password=root123
```
> 1. 這里`spring.datasource.driver-class-name=com.mysql.jdbc.Driver`不是必須的，spring可以根據url自動判斷。當然，根據需要有時候也會顯示設置。
> 2. 關于連接池的設置，都在spring.datasource.tomcat.*下面，具體可以參照這里[：https://tomcat.apache.org/tomcat-8.0-doc/jdbc-pool.html#Common_Attributes](https://tomcat.apache.org/tomcat-8.0-doc/jdbc-pool.html#Common_Attributes)
## 表的DDL
```
Create Table
CREATE TABLE `user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL DEFAULT '',
`birthday` date NOT NULL,
`createtime` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8
```
## DAO層引入（先省去接口定義）
```java
/**
* 數據庫持久層
* @author LiuYin
*/
@Repository
public class UserDao {
// 注入spring的JdbcTemplate
@Autowired
private JdbcTemplate jdbcTemplate;
/**
* 根據id獲取單個用戶
* @param id 注解id
* @return 用戶對象
*/
public User getUserById(long id){
String sql = "select id, name, birthday, createtime as registerDate from user where id = ?";
User user = jdbcTemplate.queryForObject(sql, new Object[]{id}, new BeanPropertyRowMapper<>(User.class));
return user;
}
/**
* 獲取所有用戶
* @return 返回用戶列表
*/
public List<User> queryAll(){
String sql = "select id, name, birthday, createtime as registerDate from user ";
List<User> list = jdbcTemplate.query(sql, new Object[]{}, new BeanPropertyRowMapper<>(User.class));
return list;
}
/**
* 更新用戶
* @param user 用戶對象
* @return 受影響行數
*/
public int update(User user){
String sql = "update user set name = ? , birthday = ? where id = ? ";
return jdbcTemplate.update(sql, user.getName(),user.getBirthDay(),user.getId());
}
/**
* 保存用戶
* @param user 用戶對象
* @return 新增后的主鍵
*/
public long save(User user){
KeyHolder keyHolder = new GeneratedKeyHolder();
String sql = "insert into user (name,birthDay,createtime) value (?,?,?)";
jdbcTemplate.update(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
PreparedStatement ps = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
ps.setObject(1, user.getName());
ps.setObject(2, user.getBirthDay());
ps.setObject(3, user.getRegisterDate());
return ps;
}
},keyHolder);
long generatedId = keyHolder.getKey().longValue();
return generatedId;
}
/**
* 根據id刪除用戶
* @param id 用戶主鍵id
* @return 受影響行數
*/
public int deleteById(long id){
String sql = "delete from user where id = ?";
return jdbcTemplate.update(sql, id);
}
}
```
