一、简介
Mybatis-Plus自带一套增删改查系统,但是都是针对于单表来实现,如果遇到多表联查分页就不能用它的方法来查了,只能自己写sql语句来实现。
二、实现
我们需要建立Dao、Service、ServiceImpl、Mapper、实体类等,下面以User实体类为例:
User实体类:
@Data
@EqualsAndHashCode(callSuper = true)
@Accessors(chain = true)
@TableName("SYS_USER")
public class User extends BaseModel {
private static final long serialVersionUID = -3089138238598051744L;
/**
* 账号
*/
@TableField("NAME")
private String name;
/**
* 密码
*/
@TableField("PASSWORD")
private String password;
/**
* 昵称
*/
@TableField("NICK_NAME")
private String nickName;
/**
* 备注
*/
@TableField("REMARK")
private String remark;
/**
* 最后登陆时间
*/
@TableField("LOGIN_TIME")
private String loginTime;
/**
* 工号
*/
@TableField("WORK_NUM")
private Integer workNum;
/**
* 登陆标识0是1否
*/
@TableField("LOGIN_TAG")
private Integer loginTag;
/**
* 用户拥有的角色
*/
@TableField(exist = false)
private List<Role> roles;
/**
* 用户拥有的职位
*/
@TableField(exist = false)
private List<Position> positions;
/**
* 用户拥有的菜单
*/
@TableField(exist = false)
private List<Menu> menus;
/**
* 用户拥有的资源
*/
@TableField(exist = false)
private List<Resource> resources;
/**
* 用户等级
*/
@TableField("LEVEL_ID")
private String levelId;
/**
* 子类
*/
@TableField(exist = false)
private List<User> children;
}
Dao层:
public interface UserMapper extends BaseMapper<User> {
/**
* 分页等级用户
* @param page
* @param uid
* @return
*/
IPage<User> selectPageUserByUid(Page page, @Param("uid") String uid, @Param("user") User user);
}
Service层:
public interface UserService extends BaseService<User> {
/**
* 分页等级用户
* @param page
* @param uid
* @return
*/
IPage<User> selectPageUserByUid(Page page, String uid,User user);
}
ServiceImpl 层:
@Service
public class UserServiceImpl extends BaseServiceImpl<UserMapper, User> implements UserService {
private final UserMapper userMapper;
public UserServiceImpl(UserMapper userMapper) {
this.userMapper = userMapper;
}
@Override
public IPage<User> selectPageUserByUid(Page page, String uid,User user) {
return userMapper.selectPageUserByUid(page,uid,user);
}
}
1、大家可以看到从Dao、Service、ServiceImpl都很简单,只用到了IPage、Page这两个类都是MyBatis-Plus 自带的,只要导入就行了:
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
下面是Mapper层:
<select id="selectPageUserByUid" resultType="com.test.entity.User">
SELECT
T.*
FROM
sys_user T,
(
SELECT
@DATAK := ( SELECT group_concat( T.ID ) FROM sys_user T WHERE id = #{uid} ) unId
) z,
( SELECT @DATAS := getChildId_bas_basic_tree_more ( @DATAK ) temppId ) x
WHERE
find_in_set ( T.ID, @DATAS )
AND T.STATUS = 0 AND T.dirty = 0
<if test="user.name != null and user.name!= ''">
AND T.`NAME` LIKE concat('%',#{user.name},'%')
</if>
<if test="user.nickName != null and user.nickName!= ''">
AND T.NICK_NAME LIKE concat('%',#{user.nickName},'%')
</if>
<if test="user.status != null and user.status!= ''">
AND T.STATUS LIKE concat('%',#{user.status},'%')
</if>
</select>
2、我这个查询用到了函数,是根据当前用户id 查询其下面所有子类用户,sql根据自身需求编写。
3、下面就是Controller层的实现了,调用其实也很简单这里只贴出实现方法:
/**
* <p>
* 账户信息 前端控制器
* </p>
*
* @author YC
* @since 2019-05-06
*/
@RestController
@RequestMapping("/user")
@Slf4j
public class UserController extends BaseController {
private final UserService userService;
public UserController(UserService userService) {
this.userService = userService;
}
@GetMapping
public HttpResponses<IPage<User>> page(String name,
String nickname,
StatusEnum status,String levelId,Integer current, Integer limit) {
//获取当前登录用户id
String uid = ApiUtils.currentUid();
User user = new User();
if(StringUtils.isNotEmpty(name)){
user.setName(name);
}
if(StringUtils.isNotEmpty(nickname)){
user.setNickName(nickname);
}
IPage<User> page = this.userService.selectPageUserByUid(PageUtil.getPage(current, limit, "id", false),uid,user);
return success(page);
}
}
到这里整个分页查询就结束了,我这个不是多表查询,多表查询只是sql不一样其他的都一样,只要前台传入current(当前页) 和limit(查询数量)就可以了,在PageUtil这个类里我们对current和limit进行了处理,包括排序:
PageUtil类:
import com.baomidou.mybatisplus.core.metadata.OrderItem;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.test.framework.cons.ApiCons;
import com.test.framework.model.convert.OrderItemDTO;
import org.apache.commons.lang3.StringUtils;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* 分页工具类
*/
public class PageUtil {
/**
* 默认第几页
*/
private final static Long DEF_CURRENT = 1L;
/**
* 每页默认显示多少条
*/
private final static Long DEF_SIZE = 10L;
/**
* 获取分页对象
*
* @param orderField 要排序的列
* @return 分页对象
*/
public static Page getPage(String orderField) {
return getPage(null, null, orderField, false);
}
/**
* 获取分页对象
*
* @param currentStr 当前页
* @param sizeStr 大小
* @param orderField 要排序的列
* @return 分页对象
*/
public static Page getPage(String currentStr, String sizeStr, String orderField) {
int current;
int size;
if (StringUtils.isBlank(currentStr)) {
current = DEF_CURRENT.intValue();
} else {
current = Integer.parseInt(currentStr);
}
if (StringUtils.isBlank(sizeStr)) {
size = DEF_SIZE.intValue();
} else {
size = Integer.parseInt(sizeStr);
}
return getPage(current, size, orderField, false);
}
/**
* 获取分页对象
*
* @param current 当前页
* @param size 大小
* @param orderField 要排序的列
* @return 分页对象
*/
public static Page getPage(Integer current, Integer size, String orderField) {
return getPage(current, size, orderField, false);
}
/**
* 获取分页对象
*
* @param current 当前页
* @param size 大小
* @param orderField 要排序的列
* @param isAsc 是否未正序
* @return 分页对象
*/
public static Page getPage(Integer current, Integer size, String orderField, boolean isAsc) {
if (current == null) {
current = DEF_CURRENT.intValue();
}
if (size == null) {
size = DEF_SIZE.intValue();
}
Page page = new Page(current, size);
String id = "id";
if (isAsc) {
page.addOrder(OrderItem.asc(id));
} else {
page.addOrder(OrderItem.desc(id));
}
return page;
}
public static OrderItemDTO getOrder(Page p){
List<OrderItemDTO> ois = p.getOrders();
OrderItemDTO oi = ois.size() > 0 ? ois.get(0) : null;
return oi;
}
public static Map<String, Object> getPage(Page p){
Map<String, Object> res = getPage(p.getCurrent(), p.getSize());
List<OrderItemDTO> ois = p.getOrders();
OrderItemDTO oi = ois.size() > 0 ? ois.get(0) : null;
res.put("order", oi);
return res;
}
public static Map<String, Object> getPage(Long current, Long size) {
Map<String, Object> res = new HashMap<>(ApiCons.LIST_DEF_SIZE);
if (current == null) {
current = DEF_CURRENT;
}
if (size == null) {
size = DEF_SIZE;
}
long start = (current - 1) * size+1;
long end = current * size;
res.put("start", start);
res.put("end", end);
return res;
}
}
欢迎大家批评指正。