1.新建Maven工程(使用spring Initializr)
2.pom.xml文件中添加相关依赖
<!--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>
<!--MYSQL数据库启动器坐标-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId> <version>8.0.16</version>
</dependency>
3.配置数据源
- pom.xml文件中添加Druid
<!--Druid数据源依赖-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.22</version>
</dependency>
- 新建配置文件jdbc.properties
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false
jdbc.username=root
jdbc.password=root
- 创建配置类
config包下新建JdbcConfiguration.java类文件
@Configuration
@PropertySource("classpath:/jdbc.properties") // 加载指定的Properties配置文件
public class JdbcConfiguration {
@Value("${jdbc.driverClassName}")
private String driverClassName;
@Value("${jdbc.url}")
private String url;
@Value("${jdbc.username}")
private String username;
@Value("${jdbc.password}")
private String password;
// 实例化Druid
@Bean
public DataSource getDataSourse(){
DruidDataSource source = new DruidDataSource();
source.setUsername(this.username);
source.setPassword(this.password);
source.setUrl(this.url);
source.setDriverClassName(this.driverClassName);
return source;
}
}
- 编写Controller
新建controller包,然后创建UsersController.java类文件
@Controller
public class UsersController {
@Autowired
private DataSource dataSource;
@GetMapping("/showInfo")
public String showinfo(){
return "ok";
}
}
- 通过@ConfigurationProperties注解读取配置信息
@PropertySource是属于springframework的注解
@ConfigurationProperties是属于springboot的注解
1.在config包下创建JdbcProperties.java类文件
@ConfigurationProperties(prefix = "jdbc") //是spring boot的注解,不能读取其他配置文件,prefix设置读取配置文件的前缀
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;
}
}
2.JdbcConfiguration配置文件中配置
使用@Autowired注解
@Configuration
@EnableConfigurationProperties(JdbcProperties.class) //指定加载那个配置信息属性类
public class JdbcConfiguration {
@Autowired
private JdbcProperties jdbcProperties;
// 实例化Druid
@Bean
public DataSource getDataSourse(){
DruidDataSource source = new DruidDataSource();
source.setUsername(this.jdbcProperties.getUsername());
source.setPassword(this.jdbcProperties.getPassword());
source.setUrl(this.jdbcProperties.getUrl());
source.setDriverClassName(this.jdbcProperties.getDriverClassName());
return source;
}
}
通过构造方法进行注解
@Configuration
@EnableConfigurationProperties(JdbcProperties.class) //指定加载那个配置信息属性类
public class JdbcConfiguration {
private JdbcProperties jdbcProperties;
/**
* 使用构造方法进行注解
*/
public JdbcConfiguration(JdbcProperties jdbcProperties){
this.jdbcProperties = jdbcProperties;
}
// 实例化Druid
@Bean
public DataSource getDataSourse(){
DruidDataSource source = new DruidDataSource();
source.setUsername(this.jdbcProperties.getUsername());
source.setPassword(this.jdbcProperties.getPassword());
source.setUrl(this.jdbcProperties.getUrl());
source.setDriverClassName(this.jdbcProperties.getDriverClassName());
return source;
}
}
通过形参进行注解
@Configuration
@EnableConfigurationProperties(JdbcProperties.class) //指定加载那个配置信息属性类
public class JdbcConfiguration {
@Bean
public DataSource getDataSourse(JdbcProperties jdbcProperties){
DruidDataSource source = new DruidDataSource();
source.setUsername(jdbcProperties.getUsername());
source.setPassword(jdbcProperties.getPassword());
source.setUrl(jdbcProperties.getUrl());
source.setDriverClassName(jdbcProperties.getDriverClassName());
return source;
}
}
2.1 @ConfigurationProperties注解的优雅使用方式
@Configuration
public class JdbcConfiguration {
@Bean
@ConfigurationProperties(prefix = "jdbc")
public DataSource getDataSourse(){
DruidDataSource source = new DruidDataSource();
return source;
}
}
从配置文件application.properties处直接读取信息
配置文件配置数据源
在 Spring Boot 1.x 版本中的spring-boot-starter-jdbc 启动器中默认使用的是org.apache.tomcat.jdbc.pool.DataSource 作为数据源
在 Spring Boot 2.x 版本中的 spring-boot-starter-jdbc 启动器中默认使用的是com.zaxxer.hikariDataSource 作为数据源
spring.datasource.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
MySQL8.0的配置文件
spring.datasource.url=jdbc:mysql://localhost:3306/test?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=true
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
增删查改部分
实现添加用户功能
1.数据库添加Users数据表
2.创建实体类
在包下新建pojo文件夹,新建Users类,代码如下:
public class Users {
private Integer userid;
private String username;
private String usersex;
public Integer getUserid() {
return userid;
}
public void setUserid(Integer userid) {
this.userid = userid;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getUsersex() {
return usersex;
}
public void setUsersex(String usersex) {
this.usersex = usersex;
}
}
创建页面
在templates文件夹下新建addUser.html文件,代码如下:
<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<link rel="shortcut icon" href="../resources/favicon.ico" th:href="@{/static/favicon.ico}">
<head>
<meta charset="UTF-8">
<title>添加用户</title>
</head>
<body>
<form th:action="@{/user/addUser}" method="post">
<input type="text" name="username" /><br/>
<input type="text" name="usersex" /><br/>
<input type="submit" value="提 交"/>
</form>
</body>
</html>
创建UsersController
在Controller文件夹下新建UsersController.java,代码如下:
package com.bjsxt.springbootjdbc.controller;
import com.bjsxt.springbootjdbc.pojo.Users;
import com.bjsxt.springbootjdbc.service.UsersService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import javax.sql.DataSource;
import java.util.List;
/**
* ClassName: UsersController
* Package: com.bjsxt.springbootjdbc.controller
* Description:
* Datetime: 2020/7/10 10:48
* Author: wybing(wybingcom@126.com)
*/
@Controller
@RequestMapping("/user")
public class UsersController {
@Autowired
private UsersService usersService;
/**
* 添加用户
*/
@PostMapping("addUser")
public String addUser(Users users){
try{
this.usersService.addUser(users);
}catch (Exception e){
e.printStackTrace();
return "error";
}
return "redirect:/ok";
}
@GetMapping("findUserAll")
public String findUserAll(Model model){
List<Users> list = null;
try{
list = this.usersService.findUsersAll();
model.addAttribute("list",list);
}catch (Exception e){
e.printStackTrace();
return "error";
}
return "showUsers";
}
@GetMapping("/preUpdateUser")
public String preUpdateUser(Integer id,Model model){
try{
Users user = this.usersService.findUserById(id);
model.addAttribute("user",user);
}catch (Exception e){
e.printStackTrace();
return "error";
}
return "updateUser";
}
@PostMapping("/updateUser")
public String updateUser(Users users){
try{
this.usersService.modifyUser(users);
}catch (Exception e){
e.printStackTrace();
return "error";
}
return "redirect:/ok";
}
@GetMapping("/delateUser")
public String updateUser(Integer id){
try{
this.usersService.dropUser(id);
}catch (Exception e){
e.printStackTrace();
return "error";
}
return "redirect:/ok";
}
}
创建PageController
在Controller文件夹下新建PageController.java,代码如下:
package com.bjsxt.springbootjdbc.controller;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
/**
* ClassName: PageController
* Package: com.bjsxt.springbootjdbc.controller
* Description: 页面跳转Controller
* Datetime: 2020/7/10 17:04
* Author: wybing(wybingcom@126.com)
*/
@Controller
public class PageController {
/**
* 页面跳转方法
*/
@RequestMapping("/{page}")
public String showPage(@PathVariable String page){
return page;
}
}
创建Service
新建Service文件夹,在该文件夹下新建UsersService.java文件,代码如下:
package com.bjsxt.springbootjdbc.service;
import com.bjsxt.springbootjdbc.pojo.Users;
import java.util.List;
/**
* ClassName: UsersService
* Package: com.bjsxt.springbootjdbc.service
* Description: 接口
* Datetime: 2020/7/10 17:17
* Author: wybing(wybingcom@126.com)
*/
public interface UsersService {
void addUser(Users users);
List<Users> findUsersAll();
Users findUserById(Integer id);
void modifyUser(Users users);
void dropUser(Integer id);
}
创建持久层:在Service文件夹下创建impl文件夹,在该文件夹下新建UsersServiceImpl.java文件,代码如下:
package com.bjsxt.springbootjdbc.service.impl;
import com.bjsxt.springbootjdbc.dao.UsersDao;
import com.bjsxt.springbootjdbc.pojo.Users;
import com.bjsxt.springbootjdbc.service.UsersService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
/**
* ClassName: UsersServiceImpl
* Package: com.bjsxt.springbootjdbc.service.impl
* Description: 接口实现类
* Datetime: 2020/7/10 17:20
* Author: wybing(wybingcom@126.com)
*/
@Service
public class UsersServiceImpl implements UsersService {
@Autowired
private UsersDao usersDao;
/**
* 添加用户
* @param users
*/
@Override
@Transactional
public void addUser(Users users){
this.usersDao.insertUsers(users);
}
/**
* 查询所有用户
* @return
*/
@Override
public List<Users> findUsersAll() {
return this.usersDao.selectUsersAll();
}
/**
* 预更新查询
* @param id
* @return
*/
@Override
public Users findUserById(Integer id) {
return this.usersDao.selectuserById(id);
}
/**
* 更新用户
* @param users
*/
@Override
@Transactional //CURD操作最好加上
public void modifyUser(Users users) {
this.usersDao.updateUsers(users);
}
/**
* 删除用户
* @param id
*/
@Override
@Transactional
public void dropUser(Integer id) {
this.usersDao.deleteUserById(id);
}
}
创建数据访问层
在项目包下新建dao文件夹,在该文件夹下新建UsersDao.java文件,代码如下:
package com.bjsxt.springbootjdbc.dao;
import com.bjsxt.springbootjdbc.pojo.Users;
import java.util.List;
/**
* ClassName: UsersDao
* Package: com.bjsxt.springbootjdbc.dao
* Description:
* Datetime: 2020/7/10 17:23
* Author: wybing(wybingcom@126.com)
*/
public interface UsersDao {
void insertUsers(Users users);
List<Users> selectUsersAll();
Users selectuserById(Integer id);
void updateUsers(Users users);
void deleteUserById(Integer id);
}
在dao文件夹下新建impl文件夹,在该文件夹下新建文件UsersDaolmpl.java
package com.bjsxt.springbootjdbc.dao.impl;
import com.bjsxt.springbootjdbc.dao.UsersDao;
import com.bjsxt.springbootjdbc.pojo.Users;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
/**
* ClassName: UsersDaoImpl
* Package: com.bjsxt.springbootjdbc.dao.impl
* Description: 持久层
* Datetime: 2020/7/10 17:24
* Author: wybing(wybingcom@126.com)
*/
@Repository
public class UsersDaoImpl implements UsersDao {
@Autowired
private JdbcTemplate jdbcTemplate;
/**
* 添加用户
* @param users
*/
@Override
public void insertUsers(Users users){
String sql = "insert into users(username,usersex) values (?,?)";
this.jdbcTemplate.update(sql,users.getUsername(),users.getUsersex());
}
/**
* 查询所有用户
* @return
*/
@Override
public List<Users> selectUsersAll() {
String sql = "select * from users";
/**
* 查询返回多个数据,使用RowMapper
*/
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;
}
});
}
/**
* 预更新用户查询
* @param id
* @return
*/
@Override
public Users selectuserById(Integer id) {
Users user = new Users();
/**
* 查询返回单个对象,使用RowCallbackHandler
*/
String sql = "select * from users where userid = ?";
// 第一种方法
// this.jdbcTemplate.query(sql,new RowCallbackHandler(){
// @Override
// public void processRow(ResultSet resultSet) throws SQLException{
// user.setUserid(resultSet.getInt("userid"));
// user.setUsersex(resultSet.getString("usersex"));
// user.setUsername(resultSet.getString("username"));
// }
// });
// 第二种方法
Object[] arr = new Object[]{id};
this.jdbcTemplate.query(sql, arr, new RowCallbackHandler() {
@Override
public void processRow(ResultSet resultSet) throws SQLException {
user.setUserid(resultSet.getInt("userid"));
user.setUsersex(resultSet.getString("usersex"));
user.setUsername(resultSet.getString("username"));
}
});
return user;
}
/**
* 更新用户
* @param users
*/
@Override
public void updateUsers(Users users) {
String sql = "update users set username = ?,usersex = ? where userid = ?";
this.jdbcTemplate.update(sql,users.getUsername(),users.getUsersex(),users.getUserid());
}
/**
* 删除用户
* @param id
*/
@Override
public void deleteUserById(Integer id) {
String sql = "delete from users where userid = ?";
this.jdbcTemplate.update(sql,id);
}
}
以上代码来自视频教程
https://www.bilibili.com/video/BV1sc411h7pN