import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.zzxypm.base.BaseEntity;
import lombok.Data;
import lombok.NoArgsConstructor;
@NoArgsConstructor
@Data
@TableName("sys_user")
public class User extends BaseEntity {
private static final long serialVersionUID = 1L;
@TableId(value = "id", type = IdType.AUTO)
private Long id;
/**
* 头像
*/
private String avatar;
/**
* 账号
*/
private String account;
/**
* 密码
*/
private String password;
/**
* 首页url
*/
private String customSetting;
/**
* 状态(1:启用 2:删除 3:冻结)
*/
private Integer status;
/**
* 对应的公司人员ID
*/
private Long memberId;
/**
* 保留字段
*/
private Integer version;
private String ext1;
private String ext2;
private String ext3;
}
import lombok.Data;
import java.util.List;
@Data
public class Page<T> {
//当前页
private int pageNum;
// 每页的数量
private int pageSize = 2;
// 当前页的数量
private int size;
// 总记录数
private long total;
// 总页数
private int pages;
// 结果集
private List<T> list;
}
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.zzxypm.entity.User;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.List;
import java.util.Map;
public interface UserMapper extends BaseMapper<User> {
@Select("select * from sys_user")
List<User> findByPage(com.zzxypm.base.Page<User> page);
}
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.zzxypm.base.Page;
import com.zzxypm.entity.User;
import com.zzxypm.mapper.UserMapper;
import org.apache.shiro.authc.LockedAccountException;
import org.apache.shiro.authc.UnknownAccountException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class UserService extends ServiceImpl<UserMapper,User> {
@Autowired
private UserMapper userMapper;
public Page<User> findUserByPage(Page<User> page){
List<User> userList = userMapper.findByPage(page);
page.setList(userList);
return page;
}
}
@RequestMapping("/user")
@RestController
public class UserController {
@Autowired
private UserService userService;
@GetMapping("/getUserByPage/{pageNum}")
public Result getUserByPage(@PathVariable int pageNum){
Page<User> page = new Page<>();
page.setPageNum(pageNum);
page = userService.findUserByPage(page);
Result result = new Result(1,"查询成功",page);
return result;
}
}
package com.zzxypm.plugin;
import com.zzxypm.base.Page;
import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.springframework.stereotype.Component;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;
/**
* Created by wanghong
* Date 2019-08-01 11:17
* Description:
* <p>
* 自定义封装Mybatis分页插件
*/
@Component
@Intercepts(@Signature(
type = StatementHandler.class,
method = "prepare",
args = {Connection.class, Integer.class}))
public class PagePlugin implements Interceptor {
// 数据库类型
private static String dialect = "MySql";
// 分页关键字
private static String pageSqlId = "";
/**
* 根据数据库类型生成分页sql
*
* @param sql
* @param page
* @return
*/
public String generatePageSql(String sql, Page page) {
StringBuilder sb = new StringBuilder();
if ("MySql".equals(dialect)) {
int pageNum = page.getPageNum();
// 注意分页区间 [ )
int pageSize = page.getPageSize();
int startIndex = (pageNum - 1) * pageSize;
int endIndex = pageNum * pageSize;
sb.append(sql)
.append(" limit ")
.append(startIndex)
.append(",")
.append(endIndex);
} else if ("Oracle".equals(dialect)) {
}
return sb.toString();
}
@Override
public Object intercept(Invocation invocation) throws Throwable {
// 约定哪些方法要分页 ByPage结尾的方法
StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
// 获取原始的sql
BoundSql boundSql = statementHandler.getBoundSql();
String sql = boundSql.getSql();
System.out.println("原始sql:" + sql);
MetaObject metaObject = MetaObject.forObject(statementHandler,
SystemMetaObject.DEFAULT_OBJECT_FACTORY,
SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY,
new DefaultReflectorFactory());
MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
// 获取mapper接口中的方法名称
String mapperMethodName = mappedStatement.getId();
if (mapperMethodName.matches(".*ByPage$")){
String countSql = "select count(0) from (" + sql + ") a";
System.out.println("查询总数的sql:" + countSql);
//分页参数
Page page = (Page) boundSql.getParameterObject();
// Mybatis执行JDBC操作
Connection connection = (Connection) invocation.getArgs()[0];
PreparedStatement preparedStatement = connection.prepareStatement(countSql);
ParameterHandler parameterHandler = (ParameterHandler) metaObject.getValue("delegate.parameterHandler");
parameterHandler.setParameters(preparedStatement);
ResultSet rs = preparedStatement.executeQuery();
if (rs.next()){
page.setTotal(rs.getInt(1));
}
rs.close();
preparedStatement.close();
// 改造sql,limit, count
String pageSql = generatePageSql(sql,page);
System.out.println("分页sql:" + pageSql);
// 把分页sql放回
metaObject.setValue("delegate.boundSql.sql",pageSql);
}
// 把执行流程交给Mybatis
Object result = invocation.proceed();
return result;
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target,this);
}
@Override
public void setProperties(Properties properties) {
dialect = properties.getProperty("dialect");
pageSqlId = properties.getProperty("pageSqlId");
}
}