上一章我们集成了mybatis-plus，并用generator生成了代码，但对mybatis-plus该怎么使用还不清楚，本文就来说下mybatis-plus的常用操作，包括简单的增删改查，条件查询，单表分页查询，多表分页查询等。另外对于非常复杂的查询还是推荐在xml中手写。
1、简单的增删改查
简单的增删改查，即是单表的操作。所有的操作都在Test中实现，需要使用注解@Resource引入Service或Mapper。
另外由于我配置了逻辑删除字段，所以在update和select时会默认在sql后面加上status=0，delete时也会变成更新status=1.
-
insert
// INSERT INTO t_sys_user ( id, username, mobile ) VALUES ( ?, ?, ? )
@Test
public void insert(){
SysUser user = new SysUser();
user.setUsername("testusername");
user.setMobile("11111111111");
boolean save = sysUserService.save(user);
// int insert = sysUserMapper.insert(user);
System.out.println(save);
}
-
update
// UPDATE t_sys_user SET username=? WHERE id=? AND status='0'
@Test
public void update(){
SysUser user = new SysUser();
user.setId("1342357296612716546");
user.setUsername("TestUserName");
boolean b = sysUserService.updateById(user);
// int i = sysUserMapper.updateById(user);
System.out.println(b);
}
-
delete
// UPDATE t_sys_user SET status='1' WHERE id=? AND status='0'
@Test
public void delete(){
boolean b = sysUserService.removeById("1342357296612716546");
// int i = sysUserMapper.deleteById("1342357296612716546");
System.out.println(b);
}
-
select
// WHERE id=? AND status='0'
@Test
public void selectById(){
SysUser user = sysUserService.getById("153B42-1095");
// SysUser sysUser = sysUserMapper.selectById("153B42-1095");
System.out.println(user);
}
// WHERE id IN ( ? , ? ) AND status='0'
@Test
public void selectByIds(){
List<String> ids = new ArrayList<>();
ids.add("153B42-1095");
ids.add("153B42-1098");
List<SysUser> list = sysUserService.listByIds(ids);
// List<SysUser> users = sysUserMapper.selectBatchIds(ids);
list.forEach(System.out::println);
}
2、条件查询QueryWrapper
-
username like ‘陈%’ and gender = ‘1’
// username like '陈%' and gender = '1'
@Test
public void testWrapper(){
QueryWrapper<SysUser> queryWrapper = new QueryWrapper<>();
queryWrapper.likeRight("username","陈").eq("gender","1");
List<SysUser> list = sysUserService.list(queryWrapper);
// List<SysUser> users = sysUserMapper.selectList(queryWrapper);
list.forEach(System.out::println);
}
-
username like ‘陈%’ and (gender=‘1’ or mobile is not null)
// username like '陈%' and (gender='1' or mobile is not null)
@Test
public void testWrapper1(){
QueryWrapper<SysUser> queryWrapper = new QueryWrapper<>();
queryWrapper.likeRight("username","陈")
.and(qw->qw.eq("gender","1").or().isNotNull("mobile"));
List<SysUser> list = sysUserService.list(queryWrapper);
list.forEach(System.out::println);
}
-
username like ‘陈%’ or (gender=‘1’ and mobile is not null)
// username like '陈%' or (gender='1' and mobile is not null)
@Test
public void testWrapper2(){
QueryWrapper<SysUser> queryWrapper = new QueryWrapper<>();
queryWrapper.likeRight("username","陈")
.or(qw->qw.eq("gender","1").isNotNull("mobile"));
List<SysUser> list = sysUserService.list(queryWrapper);
list.forEach(System.out::println);
}
-
(gender=‘1’ or mobile is not null) and username like ‘陈%’
// (gender='1' or mobile is not null) and username like '陈%'
@Test
public void testWrapper3(){
QueryWrapper<SysUser> queryWrapper = new QueryWrapper<>();
queryWrapper.nested(qw->qw.eq("gender","1").or().isNotNull("mobile"))
.likeRight("username","陈");
List<SysUser> list = sysUserService.list(queryWrapper);
list.forEach(System.out::println);
}
3、单表分页查询IPage
// WHERE (username LIKE ?) LIMIT ?
@Test
public void testPage(){
QueryWrapper<SysUser> queryWrapper = new QueryWrapper<>();
queryWrapper.likeRight("username","陈");
// 第一页，每页限制5条数据
IPage<SysUser> page = new Page<>(1,5);
IPage<SysUser> iPage = sysUserService.page(page,queryWrapper);
// IPage<SysUser> userIPage = sysUserMapper.selectPage(page, queryWrapper);
System.out.println("总页数"+iPage.getPages());
System.out.println("总记录数"+iPage.getTotal());
List<SysUser> list = iPage.getRecords();
list.forEach(System.out::println);
}
4、多表分页查询
查询姓陈的用户信息和角色信息
mapper
IPage<Map<String,Object>> selectUserPage(Page<Map<String,Object>> page,
@Param(Constants.WRAPPER) QueryWrapper<Map<String,Object>> queryWrapper);
mapper.xml
<select id="selectUserPage" resultType="java.util.HashMap">
select u.id,u.username,r.id,r.role_name
from t_sys_user u
left join t_sys_user_role ur on u.id = ur.user_id
left join t_sys_role r on ur.role_id = r.id
${ew.customSqlSegment}
</select>
@Test
public void morePage(){
QueryWrapper<Map<String,Object>> queryWrapper = new QueryWrapper<>();
queryWrapper.likeRight("username","陈");
Page<Map<String,Object>> page = new Page<>(1,5);
IPage<Map<String,Object>> iPage = sysUserMapper.selectUserPage(page,queryWrapper);
System.out.println("总页数"+iPage.getPages());
System.out.println("总记录数"+iPage.getTotal());
List<Map<String,Object>> list = iPage.getRecords();
list.forEach(System.out::println);
}
5、结合PageHelper进行分页
引入pagehelper依赖，
<!-- pagehelper-springboot 1.3.0 -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>${pagehelper-starter.version}</version>
</dependency>
<!-- pagehelper 5.2.0 -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>${pagehelper.version}</version>
</dependency>
不再使用mybatis-plus自带的分页，而是使用pagehelper进行分页。使用pagehelper时还可以使用querywrapper条件构造器进行条件筛选，也可以自己传参自己写xml，这个就跟普通的mybatis没什么区别了。
@Test
public void morePage1(){
QueryWrapper<SysUser> queryWrapper = new QueryWrapper<>();
queryWrapper.likeRight("username","陈");
// PageHelper分页
PageHelper.startPage(1, 5);
List<SysUser> list = sysUserMapper.selectList(queryWrapper);
// 交给pageInfo
PageInfo<SysUser> pageInfo = new PageInfo<>(list);
System.out.println("pageNum"+pageInfo.getPageNum());
System.out.println("pageSize"+pageInfo.getPageSize());
System.out.println("totalCount"+pageInfo.getTotal());
System.out.println("totalPages"+pageInfo.getPages());
list.forEach(System.out::println);
}
版权声明：本文为lp1791803611原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。