# 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);
}
}
```
