目录
六、Spring Boot与数据访问
1. 简介
Spring Boot对于数据访问层,无论是SQL(关系型)还是NOSQL(非关系型),都默认采用整合Spring Data的方式进行统一处理。添加大量自动配置,屏蔽了很多设置。引入各种xxxTemplate,xxxRepository来简化我们对数据访问层的操作。对我们来说只需要进行简单的设置即可。
2. Spring Boot整合JDBC
添加如下依赖:
<!-- JDBC 依赖-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- MySQL驱动依赖-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
配置文件中配置:
spring:
datasource:
username: root
password: root
url: jdbc:mysql://192.168.1.106:3306/jdbc
driver-class-name: com.mysql.cj.jdbc.Driver
测试连接
//获取数据源
@Autowired
DataSource dataSource;
/**
* 测试获取连接是否成功
* @throws SQLException
*/
@Test
void contextLoads() throws SQLException {
System.out.println(dataSource.getClass());
Connection connection = dataSource.getConnection();
System.out.println();
System.out.println(connection);
connection.close();
}
自动配置原理:
数据源的相关配置都在DataSourceProperties中探究
依赖包的目录:
jetbrains://idea/navigate/reference?project=spring-boot-06-jdbc&fqn=org.springframework.boot.autoconfigure.jdbc
参考DataSourceAutoConfiguration,根据配置创建数据源,默认使用Hikari连接池。
可以使用:
spring.datasource.type
指定自定义的数据源类型
还可以自定义数据源类型
DataSourceInitializer:ApplicationListener;
作用:
1)、runSchemaScripts();运行建表语句;
2)、runDataScripts();运行插入数据的sql语句;
默认只需要将文件命名为:
schema-*.sql(建表)、data-*.sql(数据)
默认规则:schema.sql,schema-all.sql;
可以使用
schema:
- classpath:department.sql
指定位置
操作数据库:自动配置了JdbcTemplate操作数据库
使用IDEA执行sql脚本创建数据库表
方式一:
-
在resources下创建sql脚本
-
使用命名规范:schema-.sql(建表)、data-.sql(数据)
-
配置文件中配置:
spring: datasource: username: root password: root url: jdbc:mysql://192.168.1.106:3306/jdbc driver-class-name: com.mysql.cj.jdbc.Driver initialization-mode: always
-
运行程序会自动建表或者添加数据
方式二:
-
在resources文件夹下新建sql文件夹,放入sql脚本文件
-
配置文件配置
spring: datasource: username: root password: root url: jdbc:mysql://192.168.1.106:3306/jdbc driver-class-name: com.mysql.cj.jdbc.Driver initialization-mode: always schema: - classpath:sql/department.sql
-
运行程序建表成功
操纵数据库数据举例
package com.why.springboot.controller;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import java.util.List;
import java.util.Map;
/**
* @Description TODO 操作数据库
* @Author why
* @Date 2020/12/25 14:48
* Version 1.0
**/
@Controller
public class HelloController {
@Autowired
JdbcTemplate jdbcTemplate;
@ResponseBody
@GetMapping("/query")
public Map<String,Object> map(){
List<Map<String, Object>> list = jdbcTemplate.queryForList("SELECT * FROM `department`");
return list.get(0);
}
}
3. Spring Boot整合Druid数据源
引入Druid依赖
<!-- https://mvnrepository.com/artifact/com.alibaba/druid Druid依赖-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.4</version>
</dependency>
**Druid版本依赖地址:**https://mvnrepository.com/artifact/com.alibaba/druid
配置文件配置:
spring:
datasource:
# 数据源基本配置
username: root
password: 123456
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/ssm_crud
type: com.alibaba.druid.pool.DruidDataSource
# 数据源其他配置
initialSize: 5
minIdle: 5
maxActive: 20
maxWait: 60000
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
filters: stat,wall,log4j
maxPoolPreparedStatementPerConnectionSize: 20
useGlobalDataSourceStat: true
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
此时虽然配置了属性但并不生效需要自行配置
配置使得属性生效
package com.why.springboot.config;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;
/**
* @Description TODO Druid配置使其他druid属性生效
* @Author why
* @Date 2020/12/25 15:19
* Version 1.0
**/
@Configuration
public class DruidConfig {
@ConfigurationProperties(prefix = "spring.datasource")//将以spring.datasource为前缀的属性绑定至容器
@Bean
public DataSource druid(){
return new DruidDataSource();
}
/**
* 配置druid监控
*/
//1.配置管理后台的Servlet
@Bean
public ServletRegistrationBean statViewServlet(){
ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
//配置初始化参数
//配置的参数可在StatViewServlet()和StatViewServlet()的ResourceServlet父类中查看
Map<String,String> initParams = new HashMap<>();
//登录后台系统用户名
initParams.put("loginUsername","admin");
//登录后台系统密码
initParams.put("loginPassword","123456");
//允许谁访问
initParams.put("allow","");//第二个参数不写或者为null时默认允许所有访问
//配置拒绝谁访问
initParams.put("deny","192.168.1.106");
bean.setInitParameters(initParams);
return bean;
}
//2.配置一个监控的filter
@Bean
public FilterRegistrationBean webStatFilter(){
FilterRegistrationBean bean = new FilterRegistrationBean();
bean.setFilter(new WebStatFilter());
//设置初始化参数
//配置的参数可在WebStatFilter()中查看
Map<String,String> initParams = new HashMap<>();
//排除拦截的请求
initParams.put("exclusions","*.js,*.css,/druid/*");
bean.setInitParameters(initParams);
//设置拦截的请求
bean.setUrlPatterns(Arrays.asList("/*"));
return bean;
}
}
4. Spring Boot整合MyBatis
4.1 创建MyBatis基础环境
创建工程
选中需要的模块
![image-20201225160203149](https://img2020.cnblogs.com/blog/2211066/202012/2211066-20201225160205246-452389195.png)
引入的依赖
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.4</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot</artifactId>
<version>2.4.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/druid Druid依赖-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.4</version>
</dependency>
</dependencies>
引入Druid数据源,配置属性文件,配置DruidConfig配置类,具体参照Spring Boot整合Druid数据源
创建数据库表
在resources/sql文件夹下创建sql脚本
配置属性文件
initialization-mode: always
schema:
- classpath:sql/department.sql
- classpath:sql/employee.sql
创建Java-Bean
Employee.java
package com.why.springboot.entity;
/**
* @Description TODO 员工表
* @Author why
* @Date 2020/12/25 17:10
* Version 1.0
**/
public class Employee {
private Integer id;
private String lastName;
private Integer gender;
private String email;
private Integer d_id;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public Integer getGender() {
return gender;
}
public void setGender(Integer gender) {
this.gender = gender;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Integer getD_id() {
return d_id;
}
public void setD_id(Integer d_id) {
this.d_id = d_id;
}
@Override
public String toString() {
return "Employee{" +
"id=" + id +
", lastName='" + lastName + '\'' +
", gender=" + gender +
", email='" + email + '\'' +
", d_id=" + d_id +
'}';
}
}
Department.java
package com.why.springboot.entity;
/**
* @Description TODO 部门表
* @Author why
* @Date 2020/12/25 17:13
* Version 1.0
**/
public class Department {
private Integer id;
private String departmentName;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getDepartmentName() {
return departmentName;
}
public void setDepartmentName(String departmentName) {
this.departmentName = departmentName;
}
@Override
public String toString() {
return "Department{" +
"id=" + id +
", departmentName='" + departmentName + '\'' +
'}';
}
}
4.2 使用MyBatis操纵数据库
4.2.1 Mybatis注解版
创建mapper实现操作数据库
package com.why.springboot.mapper;
import com.why.springboot.entity.Department;
import org.apache.ibatis.annotations.*;
/**
* @Description TODO 操作Department表
* @Author why
* @Date 2020/12/25 17:18
* Version 1.0
**/
@Mapper//指定操作数据库的mapper
public interface DepartmentMapper {
/**
* 插入数据
*
* @Options(useGeneratedKeys = true,keyProperty = "id") 获取到自增id
*
* @param department
* @return
*/
@Options(useGeneratedKeys = true,keyProperty = "id")
@Insert("INSERT INTO department(departmentName) VALUES(#{departmentName})")
public int insertDept(Department department);
/**
* 删除一条数据
* @param id
* @return
*/
@Delete("DELETE FROM department WHERE id=#{id}")
public int deleteDeptById(Integer id);
/**
* 更新数据
* @param department
* @return
*/
@Update("UPDATE department SET departmentName=#{departName} WHERE id = #{id}")
public int updateDept(Department department);
/**
* 查询数据
* @param id
* @return
*/
@Select("SELECT * FROM department WHERE id=#{id}")
public Department getDeptById(Integer id);
}
测试
@RestController
public class DeptController {
@Autowired
DepartmentMapper departmentMapper;
/**
* 通过id查找
* @param id
* @return
*/
@GetMapping("/dept/{id}")
public Department getDepartment(@PathVariable("id") Integer id){
return departmentMapper.getDeptById(id);
}
/**
* 根据传入的部门名称插入部门
* @param department
* @return
*/
@GetMapping("/dept")
public Department insertDept(Department department){
departmentMapper.insertDept(department);
return department;
}
}
注意:
-
获取自增的主键用于返回数据
@Options(useGeneratedKeys = true,keyProperty = "id")
-
当Java-Bean和数据库表的名称不一致时
如Java-Bean:departmentName
数据库表:department_name
使用如下规则自定义Mybatis规则
@Configuration public class MyBatisConfig { /** * 配置当Java-bean和数据库表命名不同时,依然能够注入,自定义Mybatis配置规则 * @return */ @Bean public ConfigurationCustomizer configurationCustomizer(){ return new ConfigurationCustomizer(){ @Override public void customize(org.apache.ibatis.session.Configuration configuration) { configuration.setMapUnderscoreToCamelCase(true); } }; } }
-
mapper包特别多的时候可以使用配置添加如下注解,批量扫描
@MapperScan(value = "com.why.springboot.mapper")//自动扫描此包下的所有mapper
4.2.2 Mybatiss使用配置文件版
创建mapper文件
package com.why.springboot.mapper;
import com.why.springboot.entity.Employee;
import org.apache.ibatis.annotations.Mapper;
/**
* @Description TODO Employee表的数据库操作
* @Author why
* @Date 2020/12/29 12:05
* Version 1.0
**/
@Mapper//将接口扫描装配到容器中
public interface EmployeeMapper {
/**
* 查询
* @param id
* @return
*/
public Employee getEmpById(Integer id);
/**
* 添加
* @param employee
*/
public void insertEmp(Employee employee);
}
创建如下目录
mybatis-config.xml,Mybatis全局配置
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 设置数据表和Java-bean命名方式的不同-->
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
</configuration>
EmployeeMapper.xml,mapper映射文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- namespace绑定mapper文件的全类名-->
<mapper namespace="com.why.springboot.mapper.EmployeeMapper">
<!-- public Employee getEmpById(Integer id);-->
<select id="getEmpById" resultType="com.why.springboot.entity.Employee">
select * from employee where id = #{id}
</select>
<!-- public void insertEmp(Employee employee);-->
<insert id="insertEmp">
insert into employee(lastName,email,gender,d_id) values (#{lastNmae},#{email},#{gender},#{d_id})
</insert>
</mapper>
在yml文件中设置Mybatis的相关配置
#配置Mybatis
mybatis:
#Mybatis全局配置文件的位置
config-location: classpath:mybatis/mybatis-config.xml
mapper-locations: classpath:mybatis/mapper/*.xml
Spring Boot整合JPA
1. JPA简介
JPA:基于ORM(Object Relation Mapping)思想
2. 使用基本步骤
-
编写实体类(Java-Bean)和数据表进行映射,并配置好映射关系
package com.why.springboot.entity; import javax.persistence.*; /** * @Description TODO 用户实体类 * * 配置映射关系使用JPA注解: * @Author why * @Date 2020/12/29 14:27 * Version 1.0 **/ @Entity//告诉JPA这是一个实体类 @Table(name = "user")//指定和哪个数据表对应,如果省略默认表名是类名小写user public class User { @Id//标注这是一个主键 @GeneratedValue(strategy = GenerationType.IDENTITY)//自增逐渐 private Integer id; @Column(name = "last_name",length = 50)//表示对应的列 private String lastName; @Column(name = "email")//如果省略列名就是属性名 private String email; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getLastName() { return lastName; } public void setLastName(String lastName) { this.lastName = lastName; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } }
-
编写Dao接口操作实体类对应的数据表
package com.why.springboot.repository; import com.why.springboot.entity.User; import org.springframework.data.jpa.repository.JpaRepository; /** * @Description TODO 操作JAP数据库 * * 继承JpaRepository<User,Integer>,User数据表对应的实体类,Integer主键对应的类型 * * @Author why * @Date 2020/12/29 14:42 * Version 1.0 **/ public interface UserRepository extends JpaRepository<User,Integer> { }
-
yaml配置文件基本配置
spring: datasource: url: jdbc:mysql://localhost:3306/data?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC username: root password: root driver-class-name: com.mysql.cj.jdbc.Driver initialization-mode: always jpa: hibernate: #更新或者创建数据表 ddl-auto: update #在控制台显示SQL show-sql: true
注意:
配置nitialization-mode: always否则建表不成功
-
使用JPA操作数据库数据
/** * @Description TODO 使用增删改查 * @Author why * @Date 2020/12/29 14:56 * Version 1.0 **/ @RestController public class UserController { @Autowired UserRepository userRepository; @GetMapping("/user/{id}") public User getUser(@PathVariable("id") Integer id){ User user = userRepository.findById(id).get(); return user; } @GetMapping("/user") public User insertUser(User user){ User save = userRepository.save(user); return save; } }