SpringBoot 整合 MyBatisPlus
·
一、整合 MyBatisPlus
1. 导入依赖
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.26</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
<version>2.6.3</version>
</dependency>
2. 配置文件
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/demo?useUnicode=true&characterEncoding=UTF-8
username: root
password: root
mybatis-plus:
# 指定 Mapper XML 文件的位置,使用 classpath 通配符指定路径
mapper-locations: classpath:/mapper/**/*.xml
# 指定实体类的包路径,用于自动扫描并注册类型别名
type-aliases-package: com.cnbai.bean
global-config:
db-config:
id-type: input
# 驼峰下划线转换(将数据库字段的下划线命名规则转换为 Java 实体类属性的驼峰命名规则)
db-column-underline: true
# 刷新 mapper
refresh-mapper: true
configuration:
# 将 Java 实体类属性的驼峰命名规则转换为数据库字段的下划线命名规则
map-underscore-to-camel-case: true
# 查询结果中包含空值的列,在映射的时候,不会映射这个字段
call-setters-on-nulls: true
# 开启 sql 日志
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
# 关闭 sql 日志
# log-impl: org.apache.ibatis.logging.nologging.NoLoggingImpl
3. 配置类
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.autoconfigure.MybatisPlusProperties;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
@Configuration
public class MybatisPlusConfig {
/**
* 加载分页插件
*/
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
// 添加分页插件(如果配置多个插件,切记分页最后添加)
PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor(DbType.MYSQL);
// 分页合理化,超出总页数默认获取第一页数据
paginationInnerInterceptor.setOverflow(true);
interceptor.addInnerInterceptor(paginationInnerInterceptor);
return interceptor;
}
@Primary
@Bean
@ConfigurationProperties("mybatis-plus")
public MybatisPlusProperties mybatisPlusProperties() {
return new MybatisPlusProperties();
}
}
4. 业务类
public interface UserService extends IService<User> {}
@Service
public class UserServiceImpl extends ServiceImpl<UserDao, User> implements UserService {}
@Mapper
public interface UserDao extends BaseMapper<User> {}
5. 实体类
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableField;
import java.io.Serializable;
@TableName(value = "t_user")
public class User implements Serializable {
@TableId(value = "id", type = IdType.INPUT)
private Integer id;
@TableField(value = "name")
private String name;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
6. 启动类
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan("com.cnbai.dao")
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
}
二、查询
1. 普通查询
public List<User> queryUserList(String name) {
LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
wrapper.eq(User::getName, name);
return list(wrapper);
}
public User queryUserById(String id) {
return getBaseMapper().selectById(id);
}
2. 拼接查询
LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
queryWrapper
.eq(User::getName, "张三")
.eq(User::getAge, 26);
List<User> list = list(queryWrapper);
3. 分页查询
// offset 默认从 1 开始,展示第一页
public Page<User> queryPage(Integer offset, Integer limit) {
return userDao.selectPage(PageDTO.of(offset, limit), new QueryWrapper<>());
}
4. 查询部分字段
public List<User> queryUserList() {
LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
wrapper.select(User::getName, User::getAge);
return list(wrapper);
}
5. 多表联查
// UserService
LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
queryWrapper.eq(StringUtils.isNotBlank(user.getName()), "u.name", user.getName());
List<UserVO> list = userDao.queryList(queryWrapper);
// UserDao
List<UserVO> queryList(@Param(Constants.WRAPPER) Wrapper<User> queryWrapper);
<resultMap type="com.cnbai.entity.vo.UserVO" id="UserResult">
<id property="userId" column="user_id"/>
</resultMap>
<select id="queryList" resultMap="UserResult">
SELECT
u.user_id, u.name, d.name as deptName
FROM
t_user u
LEFT JOIN t_dept d ON u.dept_id = d.dept_id
${ew.getCustomSqlSegment}
</select>
三、常用条件构造器
1. 单条件
# name = 'bai'
eq("name", "bai")
2. 拼接 AND
# name = ? AND age = ?
.eq("name", "张三").eq("age", 26);
3. 拼接 OR
# id = 1 or name = 'bai'
.eq("id",1).or().eq("name","bai")
4. 嵌套
# or ( name = '张三' and age != 12 )
.or ( x -> x.eq("name", "张三").ne("age", 12) )
# and ( name like '%bai%' or address like '%xian%' )
.and( x -> x.like("name", "bai").or().like("address", "xian") )
# and ( name = 'bai' or ( name = '张三' and age = 12 ) )
.and( x -> x.eq("name", "bai").or( y -> y.eq("name", "张三").eq("age", 12) ) )
四、注解查询
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.ResultType;
import org.apache.ibatis.annotations.Select;
import java.util.List;
@Mapper
public interface UserDao extends BaseMapper<User> {
@Select("select * from t_user where name = #{name}")
@ResultType(User.class)
List<User> getUserListByName(@Param("name") String name);
@Select("select * from t_user where id = #{id}")
@ResultType(User.class)
User getUserById(@Param("id") String id);
@Select("select name from t_user where id = #{id}")
String getUserNameById(@Param("id") String id);
}
五、xml 查询
1. 直接返回查询结果
List<Map<String,Object>> queryUser();
<?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.bai.dao.UserDao">
<select id="queryUser" resultType="java.util.Map">
select * from user
</select>
</mapper>
2. 返回对象集合
List<User> queryUser();
<?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.bai.dao.UserDao">
<select id="queryUser" resultType="com.cnbai.entity.User">
select * from user
</select>
</mapper>
3. 返回单个对象
User queryUserById();
<?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.bai.dao.UserDao">
<!-- 已配置实体包路径可不用配置全类名 -->
<select id="queryUserById" resultType="User">
select * from user where id = '1'
</select>
</mapper>
4. 返回整型
int queryUserCount();
<?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.bai.dao.UserDao">
<select id="queryUserCount" resultType="java.lang.Integer">
select count(*) from user
</select>
</mapper>
5. 返回字符串集合
List<String> queryUserName();
<?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.bai.dao.UserDao">
<resultMap id="userNameMap" type="java.lang.String">
<result column="name" typeHandler="org.apache.ibatis.type.StringTypeHandler" />
</resultMap>
<select id="queryUserName" resultMap="userNameMap">
select name from user
</select>
</mapper>
6. 字符串比较
MyBatis 单引号内有一个字符的情况下,会将其以 java 中的 char 类型进行解析,那么此时 char 类型与参数 String 类型用等号进行比较的时候结果都是 false
<?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.bai.dao.UserDao">
<select id="queryUser" resultType="com.cnbai.entity.User">
select * from user where 1=1
<choose>
<when test="status == '5'.toString()">
and status = #{status}
</when>
<when test="name == 'cnbai'">
and name = #{name}
</when>
<otherwise>
and id = #{id}
</otherwise>
</choose>
</select>
</mapper>
注:xml 中使用某些符号( 如:< )会报错
1. 使用 <![CDATA[]]
SELECT * FROM t_user WHERE
<![CDATA[ age < 12 ]]>
AND name = 'zhangsan';
2. 转义
符号 | 转义字符 | 描述 |
---|---|---|
< | < | 小于,需转义 |
<= | <= | 小于等于,需转义 |
> | > | 大于,无需转义 |
>= | >= | 大于等于,无需转义 |
六、处理 Json 数据
1. 存储
实体类中某个字段属性是 List,Map 之类的可以转为 Json 格式,其在 MySQL 中存储字段类型可以设置为 Json 类型,添加注解将此类型映射为 Json 存入数据库中
注:插入时可以不定义
autoResultMap = true
,查询时必须定义
@TableName(value = "t_user")
public class User {
@TableId
private int id;
@TableField(value = "user_info", typeHandler = JacksonTypeHandler.class)
private JSONArray userInfo;
@TableField(value = "info", typeHandler = JacksonTypeHandler.class)
private JSONObject info;
}
2. 取出
当没有使用到 xml 时
@TableName(value = "t_user", autoResultMap = true)
public class User {
@TableId
private int id;
@TableField(value = "user_info", typeHandler = JacksonTypeHandler.class)
private JSONArray userInfo;
@TableField(value = "info", typeHandler = JacksonTypeHandler.class)
private JSONObject info;
}
当使用了 xml 时
<result property="userInfo" column="user_info" typeHandler="com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler"/>
<result property="info" column="info" typeHandler="com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler"/>
七、循环遍历集合
/**
* List : User(id=1, name=zhangsan, age=21)
* List : User(id=2, name=lisi, age=22)
*
* Map : User(id=2, name=lisi, age=22)
* Map : User(id=3, name=wangwu, age=23)
*/
public void queryTest() {
userService.queryByList();
userService.queryByMap();
userService.updateByMap();
userService.updateByList();
}
1. UserService
public class UserService {
@Resource
UserDao userDao;
public void queryByList() {
List<Integer> list = new ArrayList<>();
list.add(1);
list.add(2);
userDao.queryByList(list);
}
public void queryByMap() {
Map<String, Object> map = new HashMap<>();
map.put("name", username);
userDao.queryByMap(map);
}
public void updateByMap() {
Map<Integer, Integer> map = new HashMap<>();
map.put(2, 21);
map.put(3, 31);
userDao.updateByMap(map);
}
public void updateByList() {
List<User> list = new ArrayList<>();
list.add(user1);
list.add(user2);
userDao.updateByList(list);
}
}
2. UserDao
public interface UserDao extends BaseMapper<User> {
// UserMapper.xml 中 collection 的值对应 @Param 里的值
List<User> queryByList(@Param("list") List<Integer> userList);
// 此处不使用 @Param , 或者不用 Map 直接传参 -> queryByMap(@Param("name") String username);
List<Map<String, Object>> queryByMap(Map<String, Object> map);
void updateByMap(@Param("map") Map<Integer, Integer> map);
void updateByList(@Param("list") List<User> userList);
}
3. UserMapper
<?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.bai.dao.UserDao">
<resultMap id="userMap" type="User">
<id column="id" jdbcType="INTEGER" property="id" />
<result column="name" jdbcType="VARCHAR" property="name" />
<result column="age" jdbcType="INTEGER" property="age" />
</resultMap>
<select id="queryByList" parameterType="java.util.List" resultMap="userMap">
select * from user where
<choose>
<when test="list != null and list.size() > 0">
id in
<foreach collection="list" item="id" open="(" separator="," close=")">
#{id}
</foreach>
</when>
<otherwise>
1 = 2
</otherwise>
</choose>
</select>
<select id="queryByMap" parameterType="java.util.Map" resultType="java.util.Map">
select id, name as username, age from user where
<if test="name != null and name != ''">
name = #{name}
</if>
</select>
<update id="updateByMap" parameterType="java.util.Map">
<foreach collection="map" index="id" item="age" separator=";">
update user set age = #{age} where id = #{id}
</foreach>
</update>
<update id="updateByList" parameterType="java.util.List">
<foreach collection="list" index="index" item="user">
update user set age = #{user.age} where id = #{user.id}
</foreach>
</update>
</mapper>
4. 增加配置参数
Mybatis 批量更新时需要在 url
后加上 &allowMultiQueries=true
application.yml
spring:
datasource:
username: root
password: root
url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true
driver-class-name: com.mysql.jdbc.Driver
否则会报错
org.springframework.jdbc.BadSqlGrammarException:
### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update user set age = 1 where id = 3' at line 3
### The error may involve com.bai.faker.mapper.UserMapper.updateByMapThree-Inline
### The error occurred while setting parameters
### SQL: update user set age = ? where id = ? ; update user set age = ? where id = ?
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update user set age = 1 where id = 3' at line 3
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update user set age = 1 where id = 3' at line 3
... ...
八、执行 Sql 文件
1. UserService
import com.baomidou.mybatisplus.extension.service.IService;
import javax.sql.DataSource;
import java.io.File;
public interface UserService extends IService<User> {
void executeSqlFile(DataSource dataSource, File sqlFile);
}
2. UserServiceImpl
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.stereotype.Service;
import javax.sql.DataSource;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
@Service
public class UserServiceImpl extends ServiceImpl<UserDao, User> implements UserService {
@Override
public void executeSqlFile(DataSource dataSource, File sqlFile) {
Connection connection = null;
FileReader reader = null;
Statement statement = null;
try {
connection = dataSource.getConnection();
connection.setAutoCommit(false);
statement = connection.createStatement();
reader = new FileReader(sqlFile);
String line;
int size = 0;
BufferedReader bufferedReader = new BufferedReader(reader);
while ((line = bufferedReader.readLine()) != null) {
if (size % 1000 == 0) {
statement.executeBatch();
connection.commit();
statement.clearBatch();
}
statement.addBatch(line);
size++;
}
statement.executeBatch();
connection.commit();
bufferedReader.close();
} catch (Exception e) {
if (connection != null) {
try {
connection.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
} finally {
try {
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
if (reader != null) {
reader.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
九、手动实现分页查询
1. pom.xml
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.26</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<version>2.6.3</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
<version>2.6.3</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.8.27</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.34</version>
</dependency>
2. 业务类
TestController
import com.cnbai.entity.PageQuery;
import com.cnbai.entity.TableDataInfo;
import com.cnbai.entity.User;
import lombok.RequiredArgsConstructor;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
@RequiredArgsConstructor
@RestController
@RequestMapping("/test")
public class TestController {
private final TestService testService;
@PostMapping("/list")
public TableDataInfo<User> getList(User user, PageQuery pageQuery) {
return testService.getList(user, pageQuery);
}
}
TestService
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.cnbai.entity.PageQuery;
import com.cnbai.entity.TableDataInfo;
import com.cnbai.entity.User;
import com.cnbai.mapper.TestMapper;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
@RequiredArgsConstructor
public class TestService {
private final TestMapper testMapper;
public TableDataInfo<User> getList(User user, PageQuery pageQuery) {
pageQuery.buildDefaultParam(pageQuery);
LambdaQueryWrapper<User> wrapper = Wrappers.lambdaQuery();
wrapper.eq(User::getUsername, user.getUsername());
List<User> userList = testMapper.getList(pageQuery.getPageNum(), pageQuery.getPageSize(), wrapper);
return TableDataInfo.build(userList, testMapper.queryListCount(wrapper));
}
}
3. 实体类
User
import lombok.Data;
@Data
public class User {
private int id;
private String username;
private String address;
}
PageQuery
import cn.hutool.core.util.ObjectUtil;
import lombok.Data;
import java.io.Serial;
import java.io.Serializable;
/**
* 分页查询实体类
*/
@Data
public class PageQuery implements Serializable {
@Serial
private static final long serialVersionUID = 1L;
/**
* 分页大小
*/
private Integer pageSize;
/**
* 当前页数
*/
private Integer pageNum;
/**
* 当前记录起始索引 默认值
*/
public static final int DEFAULT_PAGE_NUM = 1;
/**
* 每页显示记录数 默认值 默认查全部
*/
public static final int DEFAULT_PAGE_SIZE = Integer.MAX_VALUE;
/**
* 自定义分页,构建分页参数
*/
public void buildDefaultParam(PageQuery pageQuery) {
Integer num = ObjectUtil.defaultIfNull(getPageNum(), DEFAULT_PAGE_NUM);
Integer pageSize = ObjectUtil.defaultIfNull(getPageSize(), DEFAULT_PAGE_SIZE);
if (num <= 0) {
num = DEFAULT_PAGE_NUM;
}
pageQuery.setPageNum(pageSize);
pageQuery.setPageSize((num - 1) * pageSize);
}
}
TableDataInfo
import cn.hutool.http.HttpStatus;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serial;
import java.io.Serializable;
import java.util.List;
/**
* 表格分页数据对象
*/
@Data
@NoArgsConstructor
public class TableDataInfo<T> implements Serializable {
@Serial
private static final long serialVersionUID = 1L;
/**
* 总记录数
*/
private long total;
/**
* 列表数据
*/
private List<T> rows;
/**
* 消息状态码
*/
private int code;
/**
* 消息内容
*/
private String msg;
public static <T> TableDataInfo<T> build(List<T> list, int total) {
TableDataInfo<T> rspData = new TableDataInfo<>();
rspData.setCode(HttpStatus.HTTP_OK);
rspData.setMsg("查询成功");
rspData.setRows(list);
rspData.setTotal(total);
return rspData;
}
}
4. Mapper
TestMapper.java
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.cnbai.entity.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import java.util.List;
@Mapper
public interface TestMapper extends BaseMapper<User> {
List<User> getList(@Param("limit") Integer limit,
@Param("offset") Integer offset,
@Param(Constants.WRAPPER) Wrapper<User> queryWrapper);
int queryListCount(@Param(Constants.WRAPPER) Wrapper<User> queryWrapper);
}
TestMapper.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.cnbai.mapper.TestMapper">
<select id="getList" resultType="com.cnbai.entity.User">
SELECT
*
FROM
t_user
${ew.getCustomSqlSegment}
LIMIT #{limit} OFFSET #{offset}
</select>
<select id="queryListCount" resultType="java.lang.Integer">
SELECT
count(1)
FROM
t_user
${ew.getCustomSqlSegment}
</select>
</mapper>
更多推荐
已为社区贡献2条内容
所有评论(0)