mybatis-plus常用使用方法(二)
1.常见查询
/**
* 最简单的根据id查询
* <p>
* ==> Preparing: SELECT id,version,age,gender,name,position,account,password,status,type,create_time,update_time FROM sys_user WHERE id=?
* ==> Parameters: 20(Integer)
*/
@Test
public void selectById() {
SysUser sysUser = sysUserMapper.selectById(20);
System.out.println("sysUser=" + sysUser);
}
-------------------------------------------------------------------------
/**
* 根据多个id批量查询
* <p>
* ==> Preparing: SELECT id,version,age,gender,name,position,account,password,status,type,create_time,update_time FROM sys_user WHERE id IN ( ? , ? , ? , ? )
* ==> Parameters: 17(Integer), 18(Integer), 19(Integer), 20(Integer)
*/
@Test
public void selectBatchIds() {
List<Integer> asList = Arrays.asList(17, 18, 19, 20);
List<SysUser> sysUsers = sysUserMapper.selectBatchIds(asList);
System.out.println("list=" + sysUsers);
}
--------------------------------------------------------------------------
/**
* 条件构造器
* <p>
* 1、名字中包含 东平 并且年龄小于等于30
* name like '%东平%' and age<=30
* ==> Preparing: SELECT id,version,age,gender,name,position,account,password,status,type,create_time,update_time FROM sys_user
* WHERE (name LIKE ? AND age <= ?)
* ==> Parameters: %东平%(String), 30(Integer)
* <p>
* 备注:
* 等于的英语单词是:equals
* 小于的英文:less than
* le表示小于等于 <=
* lt表示小于 <
* <p>
* 同样的道理
* 大于的英文为 greater than
* gt 表示大于 >
* ge 表示大于等于 >=
*/
@Test
public void testQueryWrapper1() {
QueryWrapper queryWrapper = new QueryWrapper<SysUser>();
queryWrapper.like("name", "东平");
queryWrapper.le("age", 30);
List<SysUser> sysUsers = sysUserMapper.selectList(queryWrapper);
System.out.println("list=" + sysUsers);
}
-----------------------------------------------------------------------------
/**
* map条件查询
* 注意:map条件中的key是用数据库中的字段对应,而不是与java中的实体对应
* ==> Preparing: SELECT id,version,age,gender,name,position,account,password,status,type,create_time,update_time FROM sys_user WHERE name = ? AND age = ?
* ==> Parameters: 李东平(String), 18(String)
*/
@Test
public void selectByMap() {
Map<String, Object> columnMap = new HashMap<>();
columnMap.put("name", "李东平");
columnMap.put("age", "18");
// columnMap 拼接的条件为 WHERE name = ? AND age = ?
List<SysUser> sysUsers = sysUserMapper.selectByMap(columnMap);
System.out.println("list=" + sysUsers);
}
----------------------------------------------------------------
/**
* java实体作为条件
* ==> Preparing: SELECT id,version,age,gender,name,parent_id,position,account,password,status,type,create_time,update_time FROM sys_user WHERE age=? AND name=?
* ==> Parameters: 18(Integer), 张无忌(String)
*/
@Test
public void test2() {
SysUser sysUser = new SysUser().setName("张无忌").setAge(18);
QueryWrapper queryWrapper = new QueryWrapper<>(sysUser);
//queryWrapper.setEntity(sysUser);
List<SysUser> sysUsers = sysUserMapper.selectList(queryWrapper);
System.out.println("list=" + sysUsers);
}
/**
*更加构造器查询集合数据
*/
@Test
public void test3(String name) {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.lambda().eq(User::getName,name);
List<User> userList= sysUserService.list(queryWrapper);
System.out.println("list=" + userList);
}
排序方法
/**
*更加构造器查询集合数据
*/
@Test
public void test3(String name) {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.lambda().eq(User::getName,name)
.orderByDesc(Arrays.asList(new String[] {"字段名"}));
List<User> userList= sysUserService.list(queryWrapper);
System.out.println("list=" + userList);
}```
**2.常见分页查询**
2.1需添加一个分页配置文件
```java
package com.ldp.config;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
/**
* @author 姿势帝-博客园
* @address https://www.cnblogs.com/newAndHui/
* @WeChat 851298348
* @create 12/06 4:45
* @description
*/
@Configuration
@MapperScan("com.ldp.mapper")
public class MybatisPlusConfig {
/**
* 分页插件
*
* @return
*/
@Bean
public PaginationInterceptor paginationInterceptor() {
PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
return paginationInterceptor;
}
}
2.2分页方法1QueryWrapper分页
/**
* 分页测试
* 查询一共有多少条
* ==> Preparing: SELECT COUNT(1) FROM sys_user WHERE (age < ?)
* ==> Parameters: 60(Integer)
*
* 查询列表
* ==> Preparing: SELECT id,version,age,gender,name,parent_id,position,account,password,status,type,create_time,update_time FROM sys_user WHERE (age < ?) LIMIT ?,?
* ==> Parameters: 60(Integer), 4(Long), 2(Long)
*/
@Test
public void test1() {
QueryWrapper<SysUser> wrapper = new QueryWrapper<>();
wrapper.lt("age",60);
Page<SysUser> page = new Page<>(3,2);
IPage<SysUser> pageResult = sysUserMapper.selectPage(page, wrapper);
System.out.println("list=" + pageResult.getRecords());
}
2.3普通自行编译xml分页需要三步骤:
1.定义接口
/**
* 自己写sql实现分页
* @param sysUser
* @return
*/
IPage<SysUser> queryPage(Page<SysUser> page,@Param("sysUser") SysUser sysUser);
-----------------------------------------------------------------------------------
2.编写sql
<select id="queryPage" resultType="com.ldp.entity.SysUser">
SELECT * FROM sys_user
<where>
<if test="sysUser.name!=null and sysUser.name!=''">
name = #{sysUser.name}
</if>
</where>
</select>
------------------------------------------------------------------
3.测试接口
/**
* 测试自己的sql分页
*/
@Test
public void test2() {
SysUser sysUser = new SysUser().setName("");
Page<SysUser> page = new Page<>(3,2);
IPage<SysUser> pageResult = sysUserMapper.queryPage(page,sysUser);
System.out.println("list=" + pageResult.getRecords());
}
3.常见修改
package com.ldp.demo01;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.ldp.entity.SysUser;
import com.ldp.mapper.SysUserMapper;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
/**
* @author 姿势帝-博客园
* @address https://www.cnblogs.com/newAndHui/
* @WeChat 851298348
* @create 11/06 10:27
* @description <p>
*
* </p>
*/
@RunWith(SpringRunner.class)
@SpringBootTest
public class Test06Update {
@Autowired
private SysUserMapper sysUserMapper;
/**
* 根据 ID 修改(建议使用这个方式修改)
* 需求:
* 将id为20用户的年龄改为102
* <p>
* ==> Preparing: UPDATE sys_user SET age=? WHERE id=?
* ==> Parameters: 102(Integer), 20(Integer)
* <== Updates: 1
*/
@Test
public void test1() {
int rows = sysUserMapper.updateById(new SysUser().setId(20).setAge(102));
System.out.println("受影响行数:" + rows);
}
/**
* 根据 whereEntity 条件,更新记录
* 需求:
* 将id为20并且年龄为102的 用户的年龄改为100
* <p>
* ==> Preparing: UPDATE sys_user SET age=? WHERE (id = ? AND age = ?)
* ==> Parameters: 100(Integer), 20(String), 102(Integer)
* <== Updates: 1
*/
@Test
public void test2() {
// 作为更新的条件
UpdateWrapper<SysUser> updateWrapper = new UpdateWrapper<>();
updateWrapper.eq("id", "20").eq("age", 102);
// 修改的字段
SysUser sysUser = new SysUser().setAge(100);
int rows = sysUserMapper.update(sysUser, updateWrapper);
System.out.println("受影响行数:" + rows);
}
/**
* 根据 whereEntity 条件,更新记录(简写)
* 需求:
* 将id为20并且年龄为100的 用户的年龄改为200
* <p>
* ==> Preparing: UPDATE sys_user SET age=? WHERE (id = ? AND age = ?)
* ==> Parameters: 200(Integer), 20(String), 100(Integer)
* <== Updates: 1
*/
@Test
public void test3() {
UpdateWrapper<SysUser> updateWrapper = new UpdateWrapper<>();
// 作为更新的条件
updateWrapper.eq("id", "20").eq("age", 100)
// 修改的字段
.set("age", 200);
int rows = sysUserMapper.update(null, updateWrapper);
System.out.println("受影响行数:" + rows);
}
}
4.批量插入方法
@Autowired
private UserService userService;
/**
*批量插入
*/
@Test
public void test1() {
List<User> userList=new ArrayList<>();
userList.add(111);
userService.saveBatch(userList);
}
/**
*单条插入
*/
@Test
public void test1() {
User user =new User();
user.setName(111);
userMapper.insert(user);
}
参考连接:
https://www.cnblogs.com/wfd360/p/14155172.html
mybatis-plus常用使用方法(一)讲解具体每一块代表什么
mybatis-plus常用使用方法(二)开发中具体如何使用