MybatisPlus学习笔记
文章目录
快速开始
1、引入MybatisPlus起步依赖
<!--MybatisPlus-->
<dependency>
<groupid>com.baomidou</groupid>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.3.1</version>
</dependency>
值得注意的是无需再引入Mybatis的依赖了。
2、定义Mapper
MyBatisPlus提供了一个BaseMapper接口,其中定义了单表的CRUD功能。
因此我们自定义的Mapper只要继承BaseMapper就具备了这些功能,无需自己手动编写:
public interface UserMapper extends BaseMapper<User> {
List<User> queryUserByIds(@Param("ids") List<Long> ids);
}
3、自动装配后使用
常见注解
约定:
类名驼峰转下划线作为表
名为id的字段作为主键
变量名驼峰转下划线作为表的字段名
根据变量类型推断教据库字段类型
@TableName
描述:表名注解,标识实体类对应的表
使用位置:实体类
@Tableld
描述:主键健注解
使用位置:实体类主键字段
@TableField
描述:字段注解(非主键)
@TableFiled注解用于声明字段对应的数据库列名,使用场景如下:
成员变量名与数据库字段名不一致
成员变量是以isXXX命名,按照JavaBean的规范,MybatisPlus识别字段时会把is去除,这就导致与数据库不符
成员变量名与数据库一致,但是与数据库的关键字冲突。使用@TableField注解给字段名添加`"转义
常见配置
MyBatisPlus的配置项继承了MyBatis原生配置和一些自己特有的配置。例如:
mybatis-plus:
type-aliases-package : com.itheima.mp.domain.po #别名扫描包
mapper-locations: "classpath* : /mapper/**/*。xml" #Mapper.xml文件地址,默认值
global-config:
db-config:
id-type: auto # id为自增长
update-strategy: not_null #更新策略:只更新非空字段
configuration:
map-underscore-to-camel-case: true # 是否开启下划线和驼峰的映射
cache-enabled: false #是否开启二级缓存
条件构造器
Wrapper
Mybatis Plus 提供的几种条件构造器,关系: 抽象类AbstractWrapper下有两个实现类一个抽象类。两个实现类分别是QueryWrapper 和 UpdateWrapper。另一个抽象类AbstractLambdaWrapper下有两个实现类:LambdaQueryWrapper和LambdaUpdateWrapper
使用
具体使用可以查询资料,这里也贴一份别人的演示代码
语法详情演示
package com.ys.mybatisplusstudy;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.ys.mybatisplusstudy.entry.User;
import com.ys.mybatisplusstudy.mapper.UserMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@SpringBootTest
public class WrapperTest {
@Autowired
private UserMapper userMapper;
/**
* 新增一条记录
*/
@Test
public void testInsert(){
User user = new User();
user.setId(4L);
user.setUserName("test insert");
user.setUserAge("1");
int insert = userMapper.insert(user);
System.out.println("影响记录数:"+insert);
}
/**
* allEq 全部等于
* 实例SQL:SELECT id,user_name,user_age FROM user WHERE (user_name = ? AND id = ?)
*/
@Test
public void testAllEq(){
QueryWrapper queryWrapper = new QueryWrapper();
Map map = new HashMap<>();
map.put("id","3");
map.put("user_name","IT可乐");
queryWrapper.allEq(map);
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* eq 等于
* 实例SQL:SELECT id,user_name,user_age FROM user WHERE (id = ?)
*/
@Test
public void testEq(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.eq("id","3");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* ne 不等于
* 实例SQL:SELECT id,user_name,user_age FROM user WHERE (id <> ?)
*/
@Test
public void testNe(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.ne("id","3");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* gt 大于
* 实例SQL:SELECT id,user_name,user_age FROM user WHERE (user_age > ?)
*/
@Test
public void testGt(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.gt("user_age","18");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* ge 大于等于
* 实例SQL:SELECT id,user_name,user_age FROM user WHERE (user_age >= ?)
*/
@Test
public void testGe(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.ge("user_age","18");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* lt 小于
* 实例SQL:SELECT id,user_name,user_age FROM user WHERE (user_age < ?)
*/
@Test
public void testLt(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.lt("user_age","18");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* le 小于等于
* 实例SQL:SELECT id,user_name,user_age FROM user WHERE (user_age <= ?)
*/
@Test
public void testLe(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.le("user_age","18");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* between 值1和值2之间,两边临界值都包含
* 实例SQL:SELECT id,user_name,user_age FROM user WHERE (user_age BETWEEN ? AND ?)
*/
@Test
public void testBetween(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.between("user_age","18","25");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* notBetween 不在值1和值2之间,两边临界值都包含
* 实例SQL:SELECT id,user_name,user_age FROM user WHERE (user_age NOT BETWEEN ? AND ?)
*/
@Test
public void testNoBetween(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.notBetween("user_age","18","25");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* like 模糊查询,会在参数左右两边加上 %
* 实例SQL:SELECT id,user_name,user_age FROM user WHERE (user_name LIKE ?)
*/
@Test
public void testLike(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.like("user_name","可乐");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* notLike NOT LIKE ‘%parameter%’
* 实例SQL:SELECT id,user_name,user_age FROM user WHERE (user_name NOT LIKE ?)
*/
@Test
public void testNotLike(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.notLike("user_name","可乐");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* likeLeft LIKE ‘%parameter’
* 实例SQL:SELECT id,user_name,user_age FROM user WHERE (user_name LIKE '%parameter')
*/
@Test
public void testLikeLeft(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.likeLeft("user_name","可乐");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* likeRight LIKE ‘parameter%’
* 实例SQL:SELECT id,user_name,user_age FROM user WHERE (user_name LIKE 'parameter%')
*/
@Test
public void testLikeRight(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.likeRight("user_name","可乐");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* isNull 判断字段为null
* 实例SQL:SELECT id,user_name,user_age FROM user WHERE (user_name IS NULL)
*/
@Test
public void testIsNull(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.isNull("user_name");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* isNotNull 判断字段不为null
* 实例SQL:SELECT id,user_name,user_age FROM user WHERE (user_name IS NOT NULL)
*/
@Test
public void testIsNotNull(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.isNotNull("user_name");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* in 范围定值查询
* 实例SQL:SELECT id,user_name,user_age FROM user WHERE (user_age IN (?,?,?))
*/
@Test
public void testIn(){
QueryWrapper queryWrapper = new QueryWrapper();
List<Integer> queryList = new ArrayList<>();
queryList.add(18);
queryList.add(1);
queryList.add(25);
queryWrapper.in("user_age",queryList);
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* notIn
* 实例SQL:SELECT id,user_name,user_age FROM user WHERE (user_age IN (?,?,?))
*/
@Test
public void testNotIn(){
QueryWrapper queryWrapper = new QueryWrapper();
List<Integer> queryList = new ArrayList<>();
queryList.add(18);
queryList.add(1);
queryList.add(25);
queryWrapper.notIn("user_age",queryList);
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* inSql
* 实例SQL:SELECT id,user_name,user_age FROM user WHERE (id IN (select id from user))
*/
@Test
public void testInSql(){
QueryWrapper queryWrapper = new QueryWrapper();
//查询所有数据
queryWrapper.inSql("id","select id from user");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* notInSql
* 实例SQL:SELECT id,user_name,user_age FROM user WHERE (id NOT IN (select id from user where id > 2))
*/
@Test
public void testNotInSql(){
QueryWrapper queryWrapper = new QueryWrapper();
//查询所有数据
queryWrapper.notInSql("id","select id from user where id > 2");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* groupBy 分组
* 下面SQL有个问题,在MySQL8.0版本中,是可以执行下面SQL语句的,select user_name并没有出现在group by 语句中
* 实例SQL:SELECT id,user_name,user_age FROM user GROUP BY id,user_age
*/
@Test
public void testGroupBy(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.groupBy("id","user_age");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* orderByAsc 升序
* 实例SQL:SELECT id,user_name,user_age FROM user ORDER BY id ASC,user_age ASC
*/
@Test
public void testOrderByAsc(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.orderByAsc("id","user_age");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* orderByDesc 降序
* 实例SQL:SELECT id,user_name,user_age FROM user ORDER BY id DESC,user_age DESC
*/
@Test
public void testOrderByDesc(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.orderByDesc("id","user_age");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* orderBy 指定顺序排序
* 实例SQL:SELECT id,user_name,user_age FROM user ORDER BY id ASC,user_age ASC
*/
@Test
public void testOrderBy(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.orderBy(true,true,"id","user_age");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* having
* 实例SQL:SELECT id,user_name,user_age FROM user GROUP BY id,user_age HAVING sum(user_age)>?
*/
@Test
public void testHaving(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.groupBy("id","user_age");
queryWrapper.having("sum(user_age)>{0}","25");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* having
* 实例SQL:SELECT id,user_name,user_age FROM user WHERE (id = ? OR user_age = ?)
*/
@Test
public void testOr(){
QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.eq("id",1);
queryWrapper.or();
queryWrapper.eq("user_age",25);
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* and
* 实例SQL:SELECT id,user_name,user_age FROM user WHERE ((id = ? AND user_age <> ?))
*/
@Test
public void testAnd(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.and(i->i.eq("id",1).ne("user_age",18));
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* nested
* 实例SQL:SELECT id,user_name,user_age FROM user WHERE ((id = ? AND user_age <> ?))
*/
@Test
public void testNested(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.nested(i->i.eq("id",1).ne("user_age",18));
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* apply
* 实例SQL:SELECT id,user_name,user_age FROM user WHERE (user_age>?)
*/
@Test
public void testApplyd(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.apply("user_age>{0}","25 or 1=1");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* last
* 实例SQL:SELECT id,user_name,user_age FROM user limit 1
*/
@Test
public void testLast(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.last("limit 1 ");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* exists
* 实例SQL:SELECT id,user_name,user_age FROM user WHERE (EXISTS (select id from user where user_age = 1))
*/
@Test
public void testExists(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.exists("select id from user where user_age = 1");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
/**
* notExists
* 实例SQL:SELECT id,user_name,user_age FROM user WHERE (EXISTS (select id from user where user_age = 1))
*/
@Test
public void testNotExists(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.notExists("select id from user where user_age = 1");
List<User> list = userMapper.selectList(queryWrapper);
System.out.println(list);
}
}
LambdaQueryWrapper和LambdaUpdateWrapper(推荐)
LambdaQueryWrapper 和 LambdaUpdateWrapper 这是相对于 QueryWrapper 及 UpdateWrapper 的 Lmbda 语法实现方式。
分别通过如下两种方式获取:
//两种方式
LambdaUpdateWrapper updateLambda = new UpdateWrapper().lambda();
LambdaUpdateWrapper lambdaUpdateWrapper = new LambdaUpdateWrapper();
注意:获取LambdaQueryWrapper 和 LambdaUpdateWrapper 对象时,为了使用lambda语法,要使用泛型。
下面贴一份别人的演示实例
/**
* LambdaQueryWrapper
* SQL实例:SELECT id,user_name,user_age FROM user WHERE (id = ? AND user_age <> ?)
*/
@Test
public void testLambdaQueryWrapper(){
LambdaQueryWrapper<User> queryLambda = new LambdaQueryWrapper<>();
queryLambda.eq(User::getId,"1").ne(User::getUserAge,25);
List<User> users = userMapper.selectList(queryLambda);
System.out.println(users);
}
/**
* LambdaQueryWrapper
* SQL实例:UPDATE user SET user_name=? WHERE (user_name = ?)
*/
@Test
public void testLambdaUpdateWrapper(){
User user = new User();
user.setUserName("LambdaUpdateWrapper");
LambdaUpdateWrapper<User> userLambdaUpdateWrapper = new LambdaUpdateWrapper<>();
userLambdaUpdateWrapper.eq(User::getUserName,"IT可乐");
userMapper.update(user,userLambdaUpdateWrapper);
}
自定义sql
我们可以利用MyBatisPlus的Wrapper来构建复杂的Where条件,然后自己定义SQL语句中剩下的部分。
1、基于Wrapper构建where条件
List<Long> ids = List.of(1L,2L,4L);
int amount = 200;
1//1.构建条件
LambdaQuerywrapper<User> wrapper = new LambdaQuerywrapper<User>(). in(User::getId,ids)
//2.自定义SQL方法调用
userMapper.updateBalanceByIds(wrapper,amount);
2、在mapper方法参数中用Param注解声明wrapper变量名称,必须是ew
public interface UserMapper extends BaseMapper<User> {
void updateBalanceByIds(@Param("ew") LambdaQueryWrapper<User> wrapper,@Param("amount") int amount);
//对于简单业务亦可以如下编写,则无需第三步
@Update("UPDATE tb_user SET balance = balance - #{money} WHERE id = #{id}")
void deductBalance(@Param("id") Long id, @Param("money")Integer money);
}
3、自定义SQL,并使用Wrapper条件
<update id="updateBalanceByIds">
UPDATE tb_user SET balance = balance - #{amount} ${ew.customSqlSegment}
</update>
IService接口
基本使用
定义了常用的增删改查,使用方法是
1、定义自己的Service接口继承IService接口
public interface IUserService
extends IService<User> {}
2、定义自己的实现类继承ServiceImpl实现类并实现自己的接口。
@Service
public class UserServicermpl extends ServiceImpl<UserMapper,User>implements IUserService {
}
3、编写相关方法
实现你需要的功能的相关方法,值得注意的一点在于若是直接调用基层的这个实现类的包含 的基础的经济中方法只需要直接使用或者使用this.来进行调用即可。
lambda方法
查询示例如下:
@Override
public List<User> queryUsers(String name, Integer status, Integer minBalance, Integer maxBalance) {
return lambdaQuery()
.like(name!= null, User::getUsername , name)
.eq(status != null, User::getStatus, status)
.ge(minBalance != null, User::getBalance,minBalance)
.le(maxBalance != null, User::getBalance, maxBalance)
.list();
}
更新示例如下:
// 4.加减余额 update tb_user set balance = balance - ?
int remainBalance = user.getBalance() - money;
lambdaUpdate()
.set(User::getBalance,remainBalance)
.set(remainBalance == 0 , User::getStatus,2)//余额不足则冻结
.eq(User::getId,id)
.eq(User::getBalance , user.getBalance() ) // 乐观锁
.update();
拓展功能
代码生成
MP提供了一套代码生成器。
但是我们一般使用MybatisX、MyBatisPlus插件生成代码。
静态工具
我们在实现某些业务方法的时候看你会出现多表查询的情况。这个时候我们在其中一个Service中需要注入另一个Service,但是另一个Service同时也注入了我们当前的这个Service,这就出现了相互依赖的情况。为了避免这种情况的发生,我们开源考虑使用静态工具提供的相关方法。
静态工具提供了与正常的Service实现相同的方法,位置不同的是由于它没有指定泛型,所以在传递参数的时候往往需要多加一个相关类的字节码(***.class)来实现定位。
静态工具的使用往往是Db.****
案例
需求:
改造根据id查询用户的接口,查询用户的同时,查询出用尸对应的所有地址
改造根据id批量查询用户的接口,查询用户的同时,查询出用户对应的所有地址
实现根据用户id查询收货地址功能,需要验证用户状态,冻结用户抛出异常(练习)
@Override
public UserVo queryUserAndAddressById(Long id){
// 1.查询用户
User user = getById(id);
if (user == null || user.getStatus() == 2) {
throw new RuntimeException("用户状态异常!");
}
// 2.查询地址
List<Address> addresses = Db.lambdaQuery(Address.class)
.eq(Address::getUserId, id)
.list();
// 3.封装v0
// 3.1.转User的PO为VO
UserVO userV0 = BeanUtil.copyProperties(user,UserVO.class);
// 3.2.转地址VO
if (CollUtil.isNotEmpty(addresses))
{
userV0.setAddresses(BeanUtil.copyToList(addresses, AddressV0.class));
}
return uservo:
逻辑删除
MP提供了逻辑删除功能,无需改变方法调用的方式,在递茶自动修改CRUD语句。唯一需要做的是在配置文件中配置逻辑删除的字段名称和值
mybatis-plus:
global-config:
db-config:
logic-delete-field: flag # 全局逻辑删除的实体字名,字段类型可以是boolean、integer
logic-delete-value: 1 # 逻已除值(默认为 1)
Logic-not-delete-value: 0 # 逻未除值(默认为 0)
枚举处理器
在Mybatis中提供了一个TypeHandler它实现了移不动的类型处理。MP在此基础上增强了功能,提供了更多的方法。
对于枚举类型MP提供了@EnumValue注解用于指定当前的枚举类型在数据库当中的存储类型。
在做JSON处理的时候对于枚举类型程序的返回值默认值是枚举变量的名称,我们也可以使用@JsonValue来指定我们的Json返回值
@Getter
public enum UserStatus{
NORMAL(1,"正常"),
FREEZE(2,"冻结")
;
@EnumValue
private final int value;
@JsonValue
private final String desc;
UserStatus(int value, String desc){
this.value = value;
this.desc = desc;
}
}
使用前需要添加注解
mybatis-plus:
configuration:
default-enum-type-handler: com,baomidou.mybatisplus,core.handlers.MybatisEnumTypeHandler
Json处理器
MP提供了一种 简单可行的Json到Java对象的转换,以此来方便我们在存储实体的时候直接得到Json格式下的各个属性的值。
使用方法如下:
1、在需要转化的Json格式前添加开启类型处理器,并编写对应的复杂实体类
@TableField(typeHandler = JacksonTypeHandler.class)
private UserInfo info;
@Data
public class UserInfo{
private Integer age;
private String intro;
private String gender;
}
2、处理对象嵌套,我们需要在@TableName注解中添加开启自动结果映射。
@Data
@TableName(value= "user", autoResultMap = true)
public class User {
private Long id;
private String username;
@TableField(typeHandler = JacksonTypeHandler.class)
private UserInfo info;
}
插件功能
一些注意事项
批处理
在进行如saveBatch这样的批处理的语句的时候,我们之所以性能较高,查询较快的原因在于他将多次的sql语句的网络请求预编译成了一次包含多个sql语句的单次网络请求。但是这依旧较慢,要想更快开源考虑进一步将多个sql合并成一个sql语句。具体的做法是在配置文件中开启 rewriteBatchedStatements = true 参数。