Spring-Boot-Mybatis
github地址:springboot-learn
先搭建Spring boot项目,然后引入mybatis-spring-boot-starter
和数据库连接驱动(这里使用关系型数据库mysql8.0)。
添加pom:
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.1</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
<version>1.18.4</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.16</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
Druid数据源
配置依赖
请看上述pom文件
数据源配置和Druid的监控功能
spring:
datasource:
druid:
# 数据库访问配置, 使用druid数据源
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/lianxi?useUnicode=true&characterEncoding=utf-8&useSSL=false
username: root
password: password
# 连接池配置
initial-size: 5
min-idle: 5
max-active: 20
# 连接等待超时时间
max-wait: 30000
# 配置检测可以关闭的空闲连接间隔时间
time-between-eviction-runs-millis: 60000
# 配置连接在池中的最小生存时间
min-evictable-idle-time-millis: 300000
validation-query: select '1' from dual
test-while-idle: true
test-on-borrow: false
test-on-return: false
# 打开PSCache,并且指定每个连接上PSCache的大小
pool-prepared-statements: true
max-open-prepared-statements: 20
max-pool-prepared-statement-per-connection-size: 20
# 配置监控统计拦截的filters, 去掉后监控界面sql无法统计, 'wall'用于防火墙
filters: stat,wall
# Spring监控AOP切入点,如x.y.z.service.*,配置多个英文逗号分隔
aop-patterns: com.spbt.services.*
# WebStatFilter配置
web-stat-filter:
enabled: true
# 添加过滤规则
url-pattern: /*
# 忽略过滤的格式
exclusions: '*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*'
# StatViewServlet配置
stat-view-servlet:
enabled: true
# 访问路径为/druid时,跳转到StatViewServlet
url-pattern: /druid/*
# 是否能够重置数据
reset-enable: false
# 需要账号密码才能访问控制台
login-username: druid
login-password: druid123
# IP白名单
allow: 127.0.0.1
# IP黑名单(共同存在时,deny优先于allow)
# deny: 192.168.1.218
# 配置StatFilter
filter:
stat:
log-slow-sql: true
监视功能其他配置可参考官方文档
此时,运行项目,访问http://localhost:8080/web/druid:
登录成功可以看到具体信息
数据库
表的建立
CREATE TABLE student (
sid VARCHAR(10) NOT NULL,
sname VARCHAR(20) NOT NULL,
sex CHAR (2) NOT NULL
);
插入数据:
INSERT INTO student VALUES ('001', 'KangKang', 'M ');
INSERT INTO student VALUES ('002', 'Mike', 'M ');
INSERT INTO student VALUES ('003', 'Jane', 'F ');
MyBatis使用
创建实体类
@Data
@Component
public class Student implements Serializable {
private String sid;
private String sname;
private String sex;
@Override
public String toString() {
return "Student{" +
"sid='" + sid + '\'' +
", sname='" + sname + '\'' +
", sex='" + sex + '\'' +
'}';
}
}
创建StudentDao
@Component
@Mapper
public interface StudentDao {
Student selectStudentById(String sid);
}
注解方式
在上述StudentDao中继续编辑
@Component
@Mapper
public interface StudentDao {
@Select("select * from student where sid = #{sid}")
@Results( id = "student", value = {
@Result(property = "sid", column = "sid", javaType = String.class),
@Result(property = "sname", column = "sname", javaType = String.class),
@Result(property = "sex", column = "sex", javaType = String.class)
})
Student selectStudentById(String sid);
}
基本增删改查使用@Insert、@Update、@Delete、@Select这4个注解即可,动态SQL语句需要使用@InsertProvider、@UpdateProvider、@DeleteProvider、@SelectProvider等注解。具体可参考MyBatis官方文档
使用xml方式
使用xml方式需要在application.yml中进行一些额外的配置:
mybatis:
# type-aliases扫描路径
# type-aliases-package:
# mapper xml实现扫描路径
mapper-locations: classpath:mapper/*.xml
property:
order: BEFORE
创建StudentMapper.xml
<?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">
<mapper namespace="com.spbt.dao.StudentDao">
<resultMap type="com.spbt.pojo.Student" id="BaseMap">
<id column="sid" property="sid" javaType="java.lang.String" jdbcType="VARCHAR"/>
<id column="sname" property="sname" javaType="java.lang.String" jdbcType="VARCHAR"/>
<id column="sex" property="sex" javaType="java.lang.String" jdbcType="VARCHAR"/>
</resultMap>
<select id="selectStudentById" resultMap="BaseMap">
select * from student where sid = #{sid}
</select>
</mapper>
测试
编写service
@Service
public interface StudentService {
Student selectBySid(String sid);
}
service实现类
@Service
public class StudentServiceImpl implements StudentService {
@Autowired
private StudentDao studentDao;
@Override
public Student selectBySid(String sid) {
return studentDao.selectStudentById(sid);
}
}
编写controller
@RestController
@Slf4j
public class StudentController {
@Autowired
private StudentService studentService;
@GetMapping("getStudent/{sid}")
public Student getOneStu(@PathVariable(value = "sid") String sid){
Student student = studentService.selectBySid(sid);
log.info("学生信息" + student);
return student;
}
}
访问http://localhost:8080/getStudent/001
查看 Druid 监控的sql
分页
如果用mybatis写分页查询,比较麻烦,需要先写获取count的select语句,然后写分页查询语句。这里使用一个强大的插件 pagehelper ,可以帮助开发者快速实现分页。
优点:
- 和sqlmapper.xml文件解耦,以插件形式实现,避免直接写分页查询sql
- 方便、快速
引入pagehelper依赖
pom.xml中引入相关依赖
<!-- 添加分页插件 pagehelper -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.13</version>
</dependency>
复制代码
application.yml中配置
# 分页插件
pagehelper:
helperDialect: mysql
reasonable: false
params: count=countSql
supportMethodsArguments: true
复制代码
说明:
helperDialect:指定数据库,不指定会自动检测数据库类型
reasonable:合理化参数,默认false,
true:页码小于1,查询第一页数据,页码大于总数时,返回最后一页数据;
false:页码小于等于1都返回第一页数据,页码大于总数时返回空
复制代码
supportMethodsArguments:默认false,true:分页插件根据params中取值,找到合适值,就自动分页
增加接口
dao
List<UserMoudel> findAllByPage();
复制代码
service
PageInfo<UserMoudel> findAllByPage(int page, int offset);
复制代码
serviceImpl,重点是这里的实现,也是使用 pagehelper的核心代码
@Override
public PageInfo<UserMoudel> findAllByPage(int page, int offset) {
// 这一句是核心
PageHelper.startPage(page,offset);
List<UserMoudel> all = dao.findAllByPage();
return new PageInfo<UserMoudel>(all);
}
复制代码
controller
@ApiOperation(value = "分页查询用户信息")
@GetMapping("/findAllByPage")
@ResponseBody
public PageInfo<UserMoudel> findAllByPage(@RequestParam(value = "当前页码",required = true) int page,@RequestParam(value = "每页数量",required = true) int offset){
return userSvc.findAllByPage(page,offset);
}
复制代码
测试分页查询接口
测试通过
问题总结
application.yml中pagehelper配置问题
1 指定数据库一定要使用 helperDialect,不要使用 dialect,否则,spring boot程序启动失败,出现以下异常:
Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.
2020-06-14 13:56:42.839 ERROR 2104 --- [ restartedMain] o.s.boot.SpringApplication : Application run failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'com.github.pagehelper.autoconfigure.PageHelperAutoConfiguration': Invocation of init method failed; nested exception is com.github.pagehelper.PageException: java.lang.ClassNotFoundException: mysql
复制代码
从异常中看出,找不到mysql,然后debug,发现是配置中指定数据库的问题