MyBatisPlus的基本使用之QueryWrapper
package com.example.demo.mybatisplus.service;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.demo.zmybatisplus.entity.User;
import com.example.demo.zmybatisplus.mapper.UserMapper;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@SpringBootTest
public class UserTest {
@Autowired
private UserMapper userMapper;
//---------------------------------- 查询 ---------------------------------------------------
//简单查询
@Test
public void testSelect() {
//所有
List<User> userList = userMapper.selectList(null);
userList.forEach(System.out::println);
//多个
List<User> userList2 = userMapper.selectBatchIds(Arrays.asList(1L,2L,3L));
userList2.forEach(System.out::println);
//单个
User user3 = userMapper.selectById(1L);
System.out.println(user3);
}
//等值条件查询，-- HashMap
@Test
public void testSelectByMap() {
HashMap<String, Object> map = new HashMap<>();
//定义查询条件
map.put("name", "小垃圾"); //where k = v
map.put("age",0);
List<User> users = userMapper.selectByMap(map);
users.forEach(System.out::println);
}
//Wrapper查询---1.数字的比较 2.字符串的模糊查询
@Test
public void testSelectByWrapper() {
//链式调用构造查询条件
QueryWrapper<User> queryWrapper=new QueryWrapper<>();
queryWrapper.isNotNull("name")
.like("email","john")
.le("age",26)
.lt("version",2)
.orderByAsc("id","age");
//选择API执行query
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
// eq相等 ne不相等， gt大于， lt小于 ge大于等于 le 小于等于
// equal/ not equal/ greater than/ less than/ less than or equal/ great than or equal/
//模糊查询 like notLike likeLeft（%x） likeRight notLikeLeft notLikeRight
}
//Wrapper查询---1. 查询区间内的记录 between
// 2.集合 in （in notIn）（inSql notInSql）
@Test
void test2SelectByWrapper(){
//查询区间内的记录 between
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.isNotNull("name").between("age",20,30);
Long count = userMapper.selectCount(wrapper);
System.out.println("count = " + count);
//集合IN ： in notIn
QueryWrapper<User> queryWrapper=new QueryWrapper<>();
queryWrapper.in("id",Arrays.asList(1L,2L,3L,4L));
Long c=userMapper.selectCount(queryWrapper);
System.out.println("count="+c);
// 集合 IN ( sql语句 ) : inSql notInSql
//例: inSql("age", "1,2,3,4,5,6")--->age in (1,2,3,4,5,6)
//例: inSql("id", "select id from table where id < 3")--->id in (select id from table where id < 3)
QueryWrapper<User> queryWrapper2=new QueryWrapper<>();
//("id","1,2,3,4");//两种写法
queryWrapper2.inSql("id","select id from user where id<=4");
Long c2=userMapper.selectCount(queryWrapper);
System.out.println("count="+c2);
}
//Wrapper查询---分组查询 groupBy having
@Test
public void testGroupByByWrapper(){
QueryWrapper<User> wrapper=new QueryWrapper<>();
wrapper.select("version","sum(age) as num")
.groupBy("version")
.having("sum(age)>0")
.orderByDesc("num");
//使用Map获取返回结果
List<Map<String,Object>> list=userMapper.selectMaps(wrapper);
list.forEach(System.out::println);
}
//Wrapper更新---
@Test
public void testUpdateWrapper() {
//修改--所有的记录
// //方式1
// UpdateWrapper<User> updateWrapper=new UpdateWrapper<>();
// ("name","updateName").set("email",null);
// (updateWrapper);
//
// //方式2
// String sql = "name = 'updateName1', email = null ";
// int affectedRows = (new UpdateWrapper<User>().setSql(sql));
// ("Affected rows: " + affectedRows);
//修改 -- id=1的记录 UpdateWrapper拥有 QueryWrapper一样的条件方法
UpdateWrapper<User> updateWrapper2 = new UpdateWrapper<>();
updateWrapper2.set("name", "updateName111").set("email", "人文氛围v给xxx@").eq("id", 1);
userMapper.update(null, updateWrapper2);
}
//分页查询
@Test
public void testPage() {
Page<User> page = new Page<>(2,5); //开启拦截器后，会注册一个page对象 当前页，每页条数
//方法源码： <P extends IPage<T>> P selectPage(P page, @Param() Wrapper<T> queryWrapper);
userMapper.selectPage(page,null); //分页查询
page.getRecords().forEach(System.out::println); //获取分页后的数据 打印
System.out.println(page.getTotal()); //获取记录总数
}
//链式查询: 使用 Lambda 表达式的链式查询对象
/*
public List<User> getUserListByQueryWrapper() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
("age", 25)
.like("name", "John")
.orderByDesc("create_time");
List<User> userList = (queryWrapper);
return userList;
}
public List<User> getUserListByLambdaQueryChainWrapper() {
LambdaQueryChainWrapper<User> lambdaQueryWrapper = new LambdaQueryChainWrapper<>(userMapper);
(User::getAge, 25)
.like(User::getName, "John")
.orderByDesc(User::getCreateTime);
List<User> userList = ();
return userList;
}
public List<User> getUserListByLambdaQueryChainWrapperWithPage(int pageNum, int pageSize) {
LambdaQueryChainWrapper<User> lambdaQueryWrapper = new LambdaQueryChainWrapper<>(userMapper);
(User::getAge, 25)
.like(User::getName, "John")
.orderByDesc(User::getCreateTime)
.page(new Page<>(pageNum, pageSize));
List<User> userList = ();
return userList;
}
*/
//---------------------------增删改-查-------------------------------------------
//加入了逻辑删除之后，查询会自动增加 delfalg=0;
@Test
public void testSelectById() {
System.out.println("----- selectById method test -----");
User user = userMapper.selectById(1L);
Assertions.assertNotNull(user);
System.out.println(user);
}
@Test
public void testInsert() {
System.out.println("----- insert method test -----");
User user = new User();
user.setName("John 雪花算法");
user.setAge(25);
user.setEmail("@");
int result = userMapper.insert(user);
Assertions.assertEquals(1, result);
System.out.println("Inserted user ID: " + user.getId());
}
@Test
public void testUpdateById() {
System.out.println("----- updateById method test -----");
User user = new User();
user.setId(1L);
user.setAge(0);//update注解优先自定义设值
int result = userMapper.updateById(user);
Assertions.assertEquals(1, result);
}
//配置了全局逻辑删除 delfalg=0;
@Test
public void testDeleteById() {
System.out.println("----- deleteById method test -----");
int result = userMapper.deleteById(5L);
Assertions.assertEquals(1, result);
int r=userMapper.deleteBatchIds(Arrays.asList(1L,2L,3L));
}
//-------------------------- 乐观锁 ---------------------------------
/*
乐观锁（OptimisticLockerInnerInterceptor）机制:
当要更新一条记录的时候，希望这条记录没有被别人更新
乐观锁实现方式：
取出记录时，获取当前version
更新时，带上这个version
执行更新时， set version = newVersion where version = oldVersion
如果version不对，就更新失败
*/
@Test//乐观锁测试
public void testOptimisticLocker(){
//1、查询用户信息
User user = userMapper.selectById(1L);
//2、修改用户信息
user.setEmail("123@");
user.setName("小垃圾");
//3、更新操作
userMapper.updateById(user); //WHERE id=? AND version=?
}
@Test
public void testOptimisticLocker2(){
//模拟多线程
User user = userMapper.selectById(2L);
user.setEmail("111jdw@");
user.setName("帅小伙111");//我们在这里对线程1修改值
//线程2插队
User user2 = userMapper.selectById(2L);
user2.setEmail("222jdw@");
user2.setName("帅小伙222");
userMapper.updateById(user2); //线程2抢先提交
userMapper.updateById(user);//线程1失败，乐观锁在这种情况下防止了脏数据存在，没有乐观锁就会有覆盖掉线程2的操作
}
}