文章目录
- 前言
- 一、环境准备
- 二、操作步骤
- 1.建表
- 2.创建 Spring Boot 工程
- 3.修改配置文件 application.yaml
- 4.创建实体类等
- 5.创建mapper
- 6.创建service
- 7.在启动类上加上@MapperScan 扫描Mapper
- 8.创建UserMapper.xml
- 9.使用PageHelper实现分页
- 1.导入依赖
- 2.配置PageHelper(两种方式)
- 3.在UserMapper.java中加入查询方法
- 4.在service实现分页方法
- 10.封装全局返回结果类和返回状态类,方便接口测试
- 11.创建控制器UserController
- 12.使用APIPOST测试接口
- 1. 测试添加
- 2. 测试删除
- 3. 测试修改
- 4. 测试查询
- 三、总结
前言
使用 java 开发或者学习过程中,最避免不了的是连接和操作数据库,此次,学习了如何在spring boot中配置和使用Mybatis框架以及 PageHelper的使用 加快开发效率。
一、环境准备
开发工具:idea
接口测试工具:apipost7
数据库:mysql
二、操作步骤
1.建表
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
`id` int NOT NULL AUTO_INCREMENT,
`username` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`password` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`age` int NULL DEFAULT NULL,
`sex` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`email` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `t_user` VALUES (3, '张三', '123', 23, '女', '854296521@qq.com');
INSERT INTO `t_user` VALUES (4, 'admin', '123', 23, '女', '854296521@qq.com');
INSERT INTO `t_user` VALUES (5, 'weqwe', '123', 23, '女', '854296521@qq.com');
INSERT INTO `t_user` VALUES (6, '李四', '123123', 78, '男', '98984@qq.com');
INSERT INTO `t_user` VALUES (7, '四', '123', 78, '女', '4564@qq.com');
INSERT INTO `t_user` VALUES (8, '王五', '123456', 12, '男', '123@136.com');
INSERT INTO `t_user` VALUES (9, '张三', '1234567', 12, '男', '854296521@qq.com');
2.创建 Spring Boot 工程
3.修改配置文件 application.yaml
spring:
application:
name: Springboot-Mybatis
#mysql
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
name: defaultDataSource
url: jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC
username: root
password: 123456
server:
port: 8080
#mybatis配置
mybatis:
#mapper file location
mapper-locations: classpath:com/cque/springbootmybatis/mapper/*.xml
#entity package
type-aliases-package: com.cque.springbootmybatis.entity
configuration:
# 开启下划线转化为驼峰规则
map-underscore-to-camel-case: true
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
4.创建实体类等
/**
* @author zhaoyuqi start
* @create 2022-12-08 - 17:30
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class User {
private Long id;
private String username;
private String password;
private Integer age;
private String sex;
private String email;
}
5.创建mapper
/**
* @author zhaoyuqi start
* @create 2022-12-08 - 17:37
*/
@Mapper
public interface UserMapper {
//添加
void insert(User user);
//删除
void delete(Long id);
//修改
void update(User user);
//分页条件查询
List<User> getPages(User user);
}
6.创建service
/**
* @author zhaoyuqi start
* @create 2022-12-08 - 17:48
*/
@Service
public class UserService {
@Autowired
private UserMapper userMapper;
//添加
public void registerUser(User user) {
userMapper.insert(user);
}
//删除
public void deleteUser(Long id) {
userMapper.delete(id);
}
//修改
public void updateUser(User user) {
userMapper.update(user);
}
}
7.在启动类上加上@MapperScan 扫描Mapper
@SpringBootApplication
@MapperScan("com.cque.springbootmybatis.mapper")
public class SpringbootMybatisApplication {
public static void main(String[] args) {
SpringApplication.run(SpringbootMybatisApplication.class, args);
}
}
8.创建UserMapper.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.cque.springbootmybatis.mapper.UserMapper">
<!--insert(User user)-->
<insert id="insert">
insert into t_user values(null ,#{username},#{password},#{age},#{sex},#{email})
</insert>
<!--delete(Integer id)-->
<delete id="delete">
delete from t_user where id = #{id}
</delete>
<!--update(User user)-->
<update id="update">
update t_user set username=#{username},password = #{password},age=#{age},sex=#{sex},email=#{email}
where id = #{id}
</update>
</mapper>
值得注意的是:需要在application.yaml中写好Mapper文件位置
9.使用PageHelper实现分页
PageHelper 用于后端分页
1.导入依赖
<!--spring boot pageHelper starter 用于分页-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.4.5</version>
</dependency>
2.配置PageHelper(两种方式)
- 方式一:在application.yaml加入以下配置
#pagehelper plugins 用于分页
pagehelper:
# 分页的数据来源
helper-dialect: mysql
# 启用合理化,如果pageNum<1会查询第一页,如果pageNum>pages会查询最后一页
reasonable: false
# 支持通过 Mapper 接口参数来传递分页参数,默认值false
support-methods-arguments: true
#用于从对象中根据属性名取值,可以配置 pageNum,pageSize,count,pageSizeZero,reasonable,
#不配置映射的用默认值,默认值为 pageNum=pageNum;pageSize=pageSize;count=countSql;reasonable=reasonable;
params: count=countSql
- 方式二:创建配置类,交给spring管理
/**
* @author zhaoyuqi start
* @create 2022-12-09 - 12:18
*/
/*
配置方式:除了用yaml配置之外还可以配置类
pageHelper的配置方式二
*/
@Configuration
public class PagehelperConfigration {
@Bean
public PageHelper pageHelper(){
PageHelper pageHelper = new PageHelper();
Properties pro = new Properties();
pro.setProperty("offsetAsPageNum","true");
pro.setProperty("rowBoundsWithCount","true");
pro.setProperty("reasonable","true");
pageHelper.setProperties(pro);
return pageHelper;
}
}
3.在UserMapper.java中加入查询方法
//用于分页条件查询
List<User> getPages(User user);
4.在service实现分页方法
/**
* 分页条件查询
*
* @param pageNum 当前页
* @param pageSize 每一页显示条数
* @param user 封装的查询条件
* @return 返回分页后的数据
*/
public List<User> listUser(Integer pageNum, Integer pageSize, User user) {
/*
startPage方法自动为Mpper.xml中的SQL,加上 limit pageNum,pageSize,在SQL中就不用写了limit了 排序也可通过startPage设置
*/
PageHelper.startPage(pageNum, pageSize);
List<User> list = userMapper.getPages(user);
//可以进一步包装成更完整的数据 PageInfo 分页对象,方便前端展示,里面封装了数据total这些)
//PageInfo<User> pageInfo = new PageInfo<>(list);
return list;
}
}
10.封装全局返回结果类和返回状态类,方便接口测试
- Result.java
/**
* 全局统一返回结果类
*/
@Data
public class Result<T> {
private Integer code;
private String message;
private T data;
public Result(){}
protected static <T> Result<T> build(T data) {
Result<T> result = new Result<T>();
if (data != null)
result.setData(data);
return result;
}
public static <T> Result<T> build(T body, ResultCodeEnum resultCodeEnum) {
Result<T> result = build(body);
result.setCode(resultCodeEnum.getCode());
result.setMessage(resultCodeEnum.getMessage());
return result;
}
public static <T> Result<T> build(Integer code, String message) {
Result<T> result = build(null);
result.setCode(code);
result.setMessage(message);
return result;
}
public static<T> Result<T> ok(){
return Result.ok(null);
}
/**
* 操作成功
* @param data
* @param <T>
* @return
*/
public static<T> Result<T> ok(T data){
Result<T> result = build(data);
return build(data, ResultCodeEnum.SUCCESS);
}
public static<T> Result<T> fail(){
return Result.fail(null);
}
/**
* 操作失败
* @param data
* @param <T>
* @return
*/
public static<T> Result<T> fail(T data){
Result<T> result = build(data);
return build(data, ResultCodeEnum.FAIL);
}
public Result<T> message(String msg){
this.setMessage(msg);
return this;
}
public Result<T> code(Integer code){
this.setCode(code);
return this;
}
public boolean isOk() {
if(this.getCode().intValue() == ResultCodeEnum.SUCCESS.getCode().intValue()) {
return true;
}
return false;
}
}
- ResultCodeEnum.java
/**
* 统一返回结果状态信息类
*/
@Getter
public enum ResultCodeEnum {
SUCCESS(20000,"成功"),
FAIL(20001, "失败"),
;
private Integer code;
private String message;
private ResultCodeEnum(Integer code, String message) {
this.code = code;
this.message = message;
}
}
11.创建控制器UserController
/**
* @author zhaoyuqi start
* @create 2022-12-08 - 17:46
*/
@RestController
@RequestMapping("/user")
public class UserController {
@Autowired
private UserService userService;
@PostMapping
public Result registerUser(@RequestBody User user){
userService.registerUser(user);
return Result.ok();
}
@DeleteMapping("{id}")
public Result deleteUser(@PathVariable Long id){
userService.deleteUser(id);
return Result.ok();
}
@PutMapping
public Result udpateUser(@RequestBody User user){
userService.updateUser(user);
return Result.ok();
}
@PostMapping("/list/{pageNum}/{pageSize}")
public Result list(@PathVariable Integer pageNum,
@PathVariable Integer pageSize,
@RequestBody User user){
List<User> users = userService.listUser(pageNum, pageSize, user);
return Result.ok(users);
}
}
12.使用APIPOST测试接口
1. 测试添加
数据库:
2. 测试删除
数据库:
3. 测试修改
数据库:
4. 测试查询
三、总结
从数据库到实体类再到测试,一个完成的 spring boot 整合 mybatis 的demo演示完毕!