SpringBoot整合JDBC
1.搭建项目环境
[1]创建表
CREATE TABLE `users` (
`userid` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(30) DEFAULT NULL, `usersex` varchar(10) DEFAULT NULL, PRIMARY KEY (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
[2]创建项目
[3]修改 POM 文件,添加相关依赖
<!--添加ThyMeleaf启动器-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<!--添加JDBC启动器-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- 数据库驱动坐标 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.38</version>
</dependency>
2.配置数据源
(1)通过自定义配置文件方式配置数据源信息
[1]通过@PropertySource 注解读取配置文件
添加 Druid 数据源依赖
<!--配置数据源-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.12</version>
</dependency>
创建 Properties 文件
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/springboot?useUnicode=true&characterEncoding=utf-8&useSSL=false
jdbc.username=root
jdbc.password=1111
创建配置类
/**
* jdbc配置类
*/
@Configuration
@PropertySource("classpath:/db.properties")//加载指定的property文件
public class jdbcConfigration {
@Value("${jdbc.driverClassName}")
String driverClassName;
@Value("${jdbc.url}")
String url;
@Value("${jdbc.username}")
String username;
@Value("${jdbc.password}")
String password;
/**
* 实例化Druid
*/
@Bean
public DataSource getDataSource(){
DruidDataSource source=new DruidDataSource();
source.setDriverClassName(this.driverClassName);
source.setUrl(this.url);
source.setUsername(this.username);
source.setPassword(this.password);
return source;
}
}
[2]通过@ConfigurationProperties 注解读取配置信息
添加 Druid 数据源依赖
<!--配置数据源-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.12</version>
</dependency>
把连接数据库的配置信息存放在application.properties文件中
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/springboot?useUnicode=true&characterEncoding=utf-8&useSSL=false
jdbc.username=root
jdbc.password=1111
创建JdbcProperties类
@ConfigurationProperties(prefix = "jdbc")//读取配置文件中以jdbc.开头的属性
//是 SpringBoot 的注解不能读取其他配置文件,只能读取 SpringBoot 的 application 配置文件
public class JdbcProperties {
private String driverClassName;
private String url;
private String username;
private String password;
public String getDriverClassName() {
return driverClassName;
}
public void setDriverClassName(String driverClassName) {
this.driverClassName = driverClassName;
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
创建JdbcConfigration配置类
/**
* jdbc配置类
*/
@Configuration
//@PropertySource("classpath:/db.properties")//加载指定的property文件
@EnableConfigurationProperties(JdbcProperties.class)//指定加载哪个配置信息属性类
public class JdbcConfigration {
/* @Autowired
private JdbcProperties jdbcProperties;*/
/**
* 实例化Druid
*/
@Bean
public DataSource getDataSource(JdbcProperties jdbcProperties){
DruidDataSource source=new DruidDataSource();
source.setDriverClassName(jdbcProperties.getDriverClassName());
source.setUrl(jdbcProperties.getUrl());
source.setUsername(jdbcProperties.getUsername());
source.setPassword(jdbcProperties.getPassword());
return source;
}
}
[3]通过@ConfigurationProperties 注解读取配置信息(推荐使用)
添加 Druid 数据源依赖
<!--配置数据源-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.12</version>
</dependency>
把连接数据库的配置信息存放在application.properties文件中
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/springboot?useUnicode=true&characterEncoding=utf-8&useSSL=false
jdbc.username=root
jdbc.password=1111
创建配置类
/**
* jdbc配置类
*/
@Configuration
public class JdbcConfigration {
/**
* 实例化Druid
*/
@Bean
@ConfigurationProperties(prefix = "jdbc")
public DataSource getDataSource(){
DruidDataSource source=new DruidDataSource();
return source;
}
}
(2)通过 Spring Boot 配置文件配置数据源
在 Spring Boot1.x 版本中的spring-boot-starter-jdbc启动器中默 认 使 用 的 是org.apache.tomcat.jdbc.pool.DataSource 作为数据源(效率低)
在 Spring Boot2.x 版 本 中 的 spring-boot-starter-jdbc 启 动 器 中 默 认 使 用 的 是com.zaxxer.hikariDataSource 作为数据源(效率高)
[1]使用 Spring Boot 默认的 HikariDataSource 数据源
在application.properties文件中配置
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/springboot?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.datasource.username=root
spring.datasource.password=1111
[2]使用第三方的 Druid 数据源
在application.properties文件中配置
# 使用第三方Druid数据源
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
3.添加用户
[1]创建pojo类
public class Users {
private int userid;
private String username;
private String usersex;
//有参构造、无参构造
//getter()和setter()方法
}
[2]配置数据库连接
在application.properties文件配置数据库连接
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/springboot?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.datasource.username=root
spring.datasource.password=1111
[3]创建前端页面
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html xmlns:th="http://www.thymeleaf.org">
<head >
<title>Title</title>
</head>
<body>
<form th:action="@{/user/addUser}" method="post">
<p>
用户名:<input type="text" name="username">
</p>
<p>
性别:<input type="text" name="usersex">
</p>
<p>
<input type="submit" value="提交">
</p>
</form>
</body>
</html>
[4]编写Dao层
UserDao
public interface UserDao {
int insertUser(Users users);
}
UserDaoImpl
@Repository
public class UserDaoImpl implements UserDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
@Transactional
public int insertUser(Users users) {
String sql="insert into users values(default,?,?)";
//jdbcTemplate.update方法会根据参数位置绑定sql中的?
return jdbcTemplate.update(sql,users.getUsername(),users.getUsersex());
}
}
[5]编写Service层
UserService
public interface UserService {
//添加用户
int addUser(Users users);
}
UserServiceImpl
@Service
public class UserServiceImpl implements UserService {
@Autowired
UserDao userDao;
@Override
public int addUser(Users users) {
return this.userDao.insertUser(users);
}
}
[6]编写Controller层
PageController用于页面跳转
@Controller
public class PageController {
@RequestMapping("/{path}")
public String getPath(@PathVariable String path){
return path;
}
}
UserController
@Controller
@RequestMapping("/user")
public class UserController {
@Autowired
private UserService userService;
@PostMapping("addUser")
public String addUser(Users users){
try {
this.userService.addUser(users);
}catch (Exception e){
e.printStackTrace();
return "error";
}
return "redirect:/success";
}
}
[7]解决 favicon.ico 解析问题
<link rel="shortcut icon" href="../resources/favicon.ico" th:href="@{/static/favicon.ico}"/>
4.查询用户
[1]修改Dao层
/**
* 查询Users
* @return
*/
@Override
public List<Users> selectUsersAll() {
String sql="select * from users";
return this.jdbcTemplate.query(sql, new RowMapper<Users>() {
/**
* 结果集的映射
* @param resultSet
* @param i
* @return
* @throws SQLException
*/
@Override
public Users mapRow(ResultSet resultSet, int i) throws SQLException {
Users users=new Users();
users.setUserid(resultSet.getInt("userid"));
users.setUsername(resultSet.getString("username"));
users.setUsersex(resultSet.getString("usersex"));
return users;
}
});
}
[2]修改service层
/**
* 查询用户
* @return
*/
@Override
public List<Users> findUsersAll() {
return this.userDao.selectUsersAll();
}
[3]修改Controller层
/**
* 查询用户
* @param model
* @return
*/
@GetMapping("findUsers")
public String findUsers(Model model){
List<Users> list=null;
try {
list=this.userService.findUsersAll();
model.addAttribute("list",list);
}catch (Exception e){
e.printStackTrace();
return "error";
}
return "showUsers";
}
[4]创建页面显示查询结果
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html xmlns:th="http://www.thymeleaf.org">
<head>
<title>Title</title>
<link rel="shortcut icon" href="../resources/favicon.ico" th:href="@{/static/favicon.ico}"/>
</head>
<body>
<table border="1px" align="center">
<tr>
<th>用户id</th>
<th>用户姓名</th>
<th>用户性别</th>
<th>操作</th>
</tr>
<tr th:each="u:${list}">
<td th:text="${u.userid}"></td>
<td th:text="${u.username}"></td>
<td th:text="${u.usersex}"></td>
<td>
<a th:href="@{/user/preUpdateUser(userid=${u.userid})}">修改</a>
<a th:href="@{/user/delUser(userid=${u.userid})}">删除</a>
</td>
</tr>
</table>
</body>
5.根据id查询用户
[1]修改Dao层
/**
* 根据id查询用户
* @param userid
* @return
*/
@Override
public Users selectUserById(Integer userid) {
String sql="select * from users where userid=?";
//把参数存放在数组中,qurey方法绑定参数时会根据数组中数据顺序绑定?
Object[] arr=new Object[]{userid};
Users user=new Users();
this.jdbcTemplate.query(sql, arr, new RowCallbackHandler() {
/**
* 结果集映射
* @param resultSet
* @throws SQLException
*/
@Override
public void processRow(ResultSet resultSet) throws SQLException {
user.setUserid(resultSet.getInt("userid"));
user.setUsername(resultSet.getString("username"));
user.setUsersex(resultSet.getString("usersex"));
}
});
return user;
}
[2]修改service层
/**
* 根据id查询用户
* @param userid
* @return
*/
@Override
public Users findUser(Integer userid) {
return this.userDao.selectUserById(userid);
}
[3]修改Controller层
@RequestMapping("preUpdateUser")
public String preUpdateUser(Integer userid,Model model){
Users user=null;
try {
user = this.userService.findUser(userid);
model.addAttribute("user",user);
}catch (Exception e){
e.printStackTrace();
return "error";
}
return "updateUser";
}
[4]创建前端页面
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html xmlns:th="http://www.thymeleaf.org">
<head>
<title>Title</title>
<link rel="shortcut icon" href="../resources/favicon.ico" th:href="@{/static/favicon.ico}"/>
</head>
<body>
<form th:action="@{/user/updateUser}" method="post">
<input type="hidden" th:value="${user.userid}">
<p>
用户名:<input type="text" th:value="${user.username}">
</p>
<p>
用户性别:<input type="text" th:value="${user.usersex}">
</p>
<p>
<input type="submit" value="提交">
</p>
</form>
</body>
</html>
6.更新用户功能
[1]修改Dao层
/**
* 更新用户
* @param users
* @return
*/
@Override
public int upDateUser(Users users) {
String sql="update users set username=?,usersex=? where userid=?";
return this.jdbcTemplate.update(sql,users.getUsername(),users.getUsersex(),users.getUserid());
}
[2]修改service层
/**
* 更新用户
* @param users
* @return
*/
@Override
@Transactional
public int modifyUser(Users users) {
return this.userDao.upDateUser(users);
}
[3]修改Controller层
@PostMapping("updateUser")
public String updateUser(Users users){
try {
this.userService.modifyUser(users);
}catch (Exception e){
e.printStackTrace();
return "error";
}
return "success";
}
7.删除用户功能
[1]修改dao层
/**
* 删除用户
* @param userid
* @return
*/
@Override
public int delUsers(Integer userid) {
String sql="delete from users where userid=?";
return this.jdbcTemplate.update(sql,userid);
}
[2]修改Service层
@Override
@Transactional
public int delFromUsers(Integer userid) {
return this.userDao.delUsers(userid);
}
[3]修改Controller层
@GetMapping("delUser")
public String delUser(Integer userid){
System.out.println(userid);
try {
this.userService.delFromUsers(userid);
}catch (Exception e){
e.printStackTrace();
return "error";
}
return "success";
}