在 controller层
@RestController @RequestMapping("/tenant") public class TenantController { @Autowired public ITenantService tenantService; /** * 分页查询数据 * * @param query 查询对象 * @return PageList 分页对象 */ @PostMapping("/list") public PageList<Tenant> json(@RequestBody TenantQuery query) { PageList<Tenant> tenantPageList = tenantService.selectPageList(query); return tenantPageList; }
service层
@Service public class TenantServiceImpl extends ServiceImpl<TenantMapper, Tenant> implements ITenantService { @Override public PageList<Tenant> selectPageList(TenantQuery query) { //分页对象 并非查询 Page是mybatispusl提供的 Page<Tenant> page = new Page<>(query.getPage(),query.getRows()); //跨表查询出来结构 List<Tenant> datas = tenantMapper.loadPageList(page,query); //page.getTotal() 总数量。 datas 一系列查询后的总条数 只需要总条数和查询到了数据集合就可以分页。 return new PageList<>(page.getTotal(),datas); }
mapper层
public interface TenantMapper extends BaseMapper<Tenant> { /** * * @param * @param query 查询的对象 * @return */ List<Tenant> loadPageList(Page<Tenant> page ,TenantQuery query); }
domain层
Tenant 机构实体类。
package com.wanglu.hrm.domain;
import com.baomidou.mybatisplus.activerecord.Model;
import com.baomidou.mybatisplus.annotations.TableField;
import com.baomidou.mybatisplus.annotations.TableId;
import com.baomidou.mybatisplus.annotations.TableName;
import com.baomidou.mybatisplus.enums.IdType;
import java.io.Serializable;
import java.util.Date;
/**
* <p>
*
* </p>
*
* @author wang-zong
* @since 2021-10-24
*/
@TableName("t_tenant")
public class Tenant extends Model<Tenant> {
private static final long serialVersionUID = 1L;
@TableField(exist = false)//表示此字段在数据库不存在
private TenantType tenanType;
//管理员名字
@TableField(exist = false)
private Employee employee;
public TenantType getTenanType() {
return tenanType;
}
public void setTenanType(TenantType tenanType) {
this.tenanType = tenanType;
}
public Employee getEmployee() {
return employee;
}
public void setEmployee(Employee employee) {
this.employee = employee;
}
@TableId(value = "id", type = IdType.AUTO)
private Long id;
@TableField("tenant_type_id")
private Long tenantTypeId;
@TableField("company_name")
private String companyName;
@TableField("company_num")
private String companyNum;
@TableField("register_time")
private Date registerTime;
/**
* 0待审核,1 审核通过 , 2审核失败
*/
private Integer state;
private String address;
private String logo;
@TableField("admin_id")
private Long adminId;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public Long getTenantTypeId() {
return tenantTypeId;
}
public void setTenantTypeId(Long tenantTypeId) {
this.tenantTypeId = tenantTypeId;
}
public String getCompanyName() {
return companyName;
}
public void setCompanyName(String companyName) {
this.companyName = companyName;
}
public String getCompanyNum() {
return companyNum;
}
public void setCompanyNum(String companyNum) {
this.companyNum = companyNum;
}
public Date getRegisterTime() {
return registerTime;
}
public void setRegisterTime(Date registerTime) {
this.registerTime = registerTime;
}
public Integer getState() {
return state;
}
public void setState(Integer state) {
this.state = state;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getLogo() {
return logo;
}
public void setLogo(String logo) {
this.logo = logo;
}
public Long getAdminId() {
return adminId;
}
public void setAdminId(Long adminId) {
this.adminId = adminId;
}
@Override
protected Serializable pkVal() {
return this.id;
}
@Override
public String toString() {
return "Tenant{" +
", id=" + id +
", tenantTypeId=" + tenantTypeId +
", companyName=" + companyName +
", companyNum=" + companyNum +
", registerTime=" + registerTime +
", state=" + state +
", address=" + address +
", logo=" + logo +
", adminId=" + adminId +
"}";
}
}
TenantType 机构类型实体类
package com.wanglu.hrm.domain;
import com.baomidou.mybatisplus.activerecord.Model;
import com.baomidou.mybatisplus.annotations.TableId;
import com.baomidou.mybatisplus.annotations.TableName;
import com.baomidou.mybatisplus.enums.IdType;
import java.io.Serializable;
/**
* <p>
* 租户(机构)类型表
* </p>
*
* @author wang-zong
* @since 2021-10-24
*/
@TableName("t_tenant_type")
public class TenantType extends Model<TenantType> {
private static final long serialVersionUID = 1L;
@TableId(value = "id", type = IdType.AUTO)
private Long id;
private String name;
private String description;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
@Override
protected Serializable pkVal() {
return this.id;
}
@Override
public String toString() {
return "TenantType{" +
", id=" + id +
", name=" + name +
", description=" + description +
"}";
}
}
Employee 员工 实体类
package com.wanglu.hrm.domain;
import com.baomidou.mybatisplus.enums.IdType;
import java.util.Date;
import com.baomidou.mybatisplus.annotations.TableId;
import com.baomidou.mybatisplus.annotations.TableField;
import com.baomidou.mybatisplus.activerecord.Model;
import com.baomidou.mybatisplus.annotations.TableName;
import java.io.Serializable;
/**
* <p>
*
* </p>
*
* @author wang-zong
* @since 2021-10-24
*/
@TableName("t_employee")
public class Employee extends Model<Employee> {
private static final long serialVersionUID = 1L;
@TableId(value = "id", type = IdType.AUTO)
private Long id;
/**
* 员工用户名
*/
private String username;
/**
* 密码
*/
private String password;
/**
* 姓名
*/
@TableField("real_name")
private String realName;
/**
* 电话
*/
private String tel;
/**
* 邮箱
*/
private String email;
/**
* 创建时间
*/
@TableField("input_time")
private Date inputTime;
/**
* 状态:0正常,1锁定,2注销
*/
private Integer state;
/**
* 部门id
*/
@TableField("dept_id")
private Long deptId;
/**
* 所属租户
*/
// @TableField("tenant_id")
// private Long tenantId;
/**
* 员工类型 , 1平台普通员工 ,2平台客服人员,3平台管理员,4机构员工,5,机构管理员或其他
*/
private Integer type;
private String salt;
@TableField("login_id")
private Long loginId;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getRealName() {
return realName;
}
public void setRealName(String realName) {
this.realName = realName;
}
public String getTel() {
return tel;
}
public void setTel(String tel) {
this.tel = tel;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getInputTime() {
return inputTime;
}
public void setInputTime(Date inputTime) {
this.inputTime = inputTime;
}
public Integer getState() {
return state;
}
public void setState(Integer state) {
this.state = state;
}
public Long getDeptId() {
return deptId;
}
public void setDeptId(Long deptId) {
this.deptId = deptId;
}
public Integer getType() {
return type;
}
public void setType(Integer type) {
this.type = type;
}
public String getSalt() {
return salt;
}
public void setSalt(String salt) {
this.salt = salt;
}
public Long getLoginId() {
return loginId;
}
public void setLoginId(Long loginId) {
this.loginId = loginId;
}
@Override
protected Serializable pkVal() {
return this.id;
}
@Override
public String toString() {
return "Employee{" +
", id=" + id +
", username=" + username +
", password=" + password +
", realName=" + realName +
", tel=" + tel +
", email=" + email +
", inputTime=" + inputTime +
", state=" + state +
", deptId=" + deptId +
", type=" + type +
", salt=" + salt +
", loginId=" + loginId +
"}";
}
}
TenantMapper.xml 自定义配置
<select id="loadPageList" resultMap="map">
SELECT
t1.*,
t2.*,
t3.*
FROM
t_tenant t1
join t_tenant_type t2 on t1.tenant_type_id = t2.id JOIN t_employee t3 on t1.admin_id=t3.id
<include refid="whereSql"/>
</select>
<resultMap id="map" type="com.wanglu.hrm.domain.Tenant">
<id column="id" property="id"></id>
<result column="company_name" property="companyName" />
<result column="company_num" property="companyNum" />
<result column="register_time" property="registerTime" />
<result column="state" property="state" />
<result column="address" property="address" />
<result column="logo" property="logo" />
<result column="admin_id" property="adminId" />
<result column="tenant_type_id" property="tenantTypeId" />
<association property="tenanType" javaType="TenantType">
<id column="id" property="id"></id>
<result column="name" property="name" />
<result column="description" property="description" />
</association>
<association property="employee" javaType="Employee">
<id column="id" property="id" />
<result column="t3name" property="username" />
<result column="password" property="password" />
<result column="real_name" property="realName" />
<result column="tel" property="tel" />
<result column="email" property="email" />
<result column="input_time" property="inputTime" />
<result column="state" property="state" />
<result column="dept_id" property="deptId" />
<result column="tenant_id" property="tenantId" />
<result column="type" property="type" />
<result column="salt" property="salt" />
<result column="login_id" property="loginId" />
</association>
</resultMap>
<sql id="whereSql">
<where >
<if test="keyword != null and keyword != ''" >
t1.company_name LIKE concat("%",#{keyword},"%")
</if>
</where>
</sql>
query层
/**
*
* @author wang-zong
* @since 2021-10-24
*/
public class TenantQuery extends BaseQuery{
}
package com.wanglu.hrm.query;
/**
* 基础查询对象
*/
public class BaseQuery {
//关键字
private String keyword;
//有公共属性-分页
private Integer page = 1; //当前页
private Integer rows = 10; //每页显示多少条
public String getKeyword() {
return keyword;
}
public void setKeyword(String keyword) {
this.keyword = keyword;
}
public Integer getPage() {
return page;
}
public void setPage(Integer page) {
this.page = page;
}
public Integer getRows() {
return rows;
}
public void setRows(Integer rows) {
this.rows = rows;
}
}
slq信息 员工信息
CREATE TABLE `t_employee` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL COMMENT '员工用户名',
`password` varchar(255) DEFAULT NULL COMMENT '密码',
`real_name` varchar(255) DEFAULT NULL COMMENT '姓名',
`tel` varchar(255) DEFAULT NULL COMMENT '电话',
`email` varchar(255) DEFAULT NULL COMMENT '邮箱',
`input_time` date DEFAULT NULL COMMENT '创建时间',
`state` int(11) DEFAULT NULL COMMENT '状态:0正常,1锁定,2注销',
`dept_id` bigint(20) DEFAULT NULL COMMENT '部门id',
`type` tinyint(1) DEFAULT NULL COMMENT '员工类型 , 1平台普通员工 ,2平台客服人员,3平台管理员,4机构员工,5,机构管理员或其他',
`salt` varchar(255) DEFAULT NULL,
`login_id` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `dept_id` (`dept_id`),
KEY `state` (`state`),
KEY `state_2` (`state`),
CONSTRAINT `t_employee_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `t_department` (`id`),
CONSTRAINT `t_employee_ibfk_3` FOREIGN KEY (`state`) REFERENCES `t_employee` (`state`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=84 DEFAULT CHARSET=utf8;
机构类型
CREATE TABLE `t_tenant_type` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`description` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='租户(机构)类型表';
机构
CREATE TABLE `t_tenant` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`tenant_type_id` bigint(20) DEFAULT NULL,
`company_name` varchar(255) DEFAULT NULL,
`company_num` varchar(255) DEFAULT NULL,
`register_time` date DEFAULT NULL,
`state` tinyint(4) DEFAULT '0' COMMENT '0待审核,1 审核通过 , 2审核失败',
`address` varchar(255) DEFAULT NULL,
`logo` varchar(255) DEFAULT NULL,
`admin_id` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `tenant_type` (`tenant_type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=68 DEFAULT CHARSET=utf8;
注意使用mybatis pusl分页需要在spring容器里注入分页插件
package com.wanglu.hrm.config;
import com.baomidou.mybatisplus.plugins.PaginationInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.transaction.annotation.EnableTransactionManagement;
//Spring boot方式
@EnableTransactionManagement
@Configuration
public class MybatisPlusConfig {
/**
* 分页插件
*/
@Bean
public PaginationInterceptor paginationInterceptor() {
return new PaginationInterceptor();
}
}
以上是利用mybatispusl实现了一个分页加高级查询,如果有其他需要可以在此基础上进行修改。谢谢。