MyBatis-Plus联表查询及分页代码举例_Java

一、准备工作

mybatis-plus作为mybatis的增强工具，它的出现极大的简化了开发中的数据库操作，但是长久以来，它的联表查询能力一直被大家所诟病。一旦遇到left join或right join的左右连接，你还是得老老实实的打开xml文件，手写上一大段的sql语句。

直到前几天，偶然碰到了这么一款叫做mybatis-plus-join的工具（后面就简称mpj了），使用了一下，不得不说真香！彻底将我从xml地狱中解放了出来，终于可以以类似mybatis-plus中querywrapper的方式来进行联表查询了，话不多说，我们下面开始体验。

mapper继承mpjbasemapper (必选)
service继承mpjbaseservice (可选)
serviceimpl继承mpjbaseserviceimpl (可选)

1、数据库结构以及数据

create table `op_product` (
  `id` int(11) not null auto_increment,
  `type` varchar(255) default null,
  primary key (`id`)
) engine=innodb auto_increment=2 default charset=utf8;
insert into `test_yjdsns`.`op_product`(`id`, `type`) values (1, '苹果');
create table `op_product_info` (
  `id` int(11) not null auto_increment,
  `product_id` int(11) not null,
  `name` varchar(255) default null,
  `price` decimal(10,2) default null,
  primary key (`id`)
) engine=innodb auto_increment=3 default charset=utf8;
insert into `test_yjdsns`.`op_product_info`(`id`, `product_id`, `name`, `price`) values (1, 1, '苹果13', 8.00);
insert into `test_yjdsns`.`op_product_info`(`id`, `product_id`, `name`, `price`) values (2, 1, '苹果15', 9.00);

2、依赖

<dependency>
    <groupid>com.github.yulichang</groupid>
    <artifactid>mybatis-plus-join</artifactid>
    <version>1.2.4</version>
</dependency>
<dependency>
    <groupid>com.baomidou</groupid>
    <artifactid>mybatis-plus-boot-starter</artifactid>
    <version>3.5.1</version>
</dependency>

3、配置类让mybatis-plus-join在datascopesqlinjector中生效

/**
 * @author licy
 * @description
 * @date 2022/10/20
 */
@configuration
public class mybatisplusconfig {
    /**
     * 新增分页拦截器，并设置数据库类型为mysql
     *
     * @return
     */
    @bean
    public mybatisplusinterceptor mybatisplusinterceptor() {
        mybatisplusinterceptor interceptor = new mybatisplusinterceptor();
        //分页插件
        interceptor.addinnerinterceptor(new paginationinnerinterceptor(dbtype.mysql));
        return interceptor;
    }
    /**
     * sql注入
     */
    @bean
    @primary
    public mysqlinjector mylogicsqlinjector() {
        return new mysqlinjector();
    }
}

修改datascopesqlinjector中的继承类为:mpjsqlinjector

public class mysqlinjector extends mpjsqlinjector {
    @override
    public list<abstractmethod> getmethodlist(class<?> mapperclass) {
        //将原来的保持
        list<abstractmethod> methodlist = super.getmethodlist(mapperclass);
        //多表查询sql注入 从连表插件里移植过来的
        methodlist.add(new selectjoinone());
        methodlist.add(new selectjoinlist());
        methodlist.add(new selectjoinpage());
        methodlist.add(new selectjoinmap());
        methodlist.add(new selectjoinmaps());
        methodlist.add(new selectjoinmapspage());
        return methodlist;
    }
}

4、启动类排除mpjsqlinjector.class

@springbootapplication(exclude = {mpjsqlinjector.class})

载入自定义配置类

@configuration

@mapperscan可以选择tk下的路径

import tk.mybatis.spring.annotation.mapperscan;

二、代码

1、实体类

/**
 * @author licy
 * @description
 * @date 2022/10/25
 */
@data
@noargsconstructor
@allargsconstructor
@tablename("op_product")
public class opproduct implements serializable {
    private static final long serialversionuid = -3918932563888251866l;
    @tableid(value = "id", type = idtype.auto)
    private long id;
    @tablefield("type")
    private string type;
}

/**
 * @author licy
 * @description
 * @date 2022/10/25
 */
@data
@noargsconstructor
@allargsconstructor
@tablename("op_product_info")
public class opproductinfo implements serializable {
    private static final long serialversionuid = 4186082342917210485l;
    @tableid(value = "id", type = idtype.auto)
    private long id;
    @tablefield("product_id")
    private long productid;
    @tablefield("name")
    private string name;
    @tablefield("price")
    private double price;
}

/**
 * @author licy
 * @description
 * @date 2022/10/25
 */
@data
@noargsconstructor
@allargsconstructor
public class productdto implements serializable {
    private static final long serialversionuid = -2281333877153304329l;
    private long id;
    private string type;
    private string name;
    private double price;
}

2、mapper

/**
 * @author licy
 * @description
 * @date 2022/10/26
 */
public interface opproductinfomapper extends mpjbasemapper<opproductinfo> {
}

/**
 * @author licy
 * @description
 * @date 2022/10/25
 */
public interface opproductmapper extends mpjbasemapper<opproduct> {
}

3、service

mapper接口改造完成后，我们把它注入到service中，虽然说我们要完成3张表的联表查询，但是以opproduct作为主表的话，那么只注入这一个对应的opproductmapper就可以，非常简单。

public interface opproductservice extends mpjbaseservice<opproduct> {
    list<productdto> queryallproduct();
}

@service
@slf4j
@allargsconstructor
public class opproductserviceimpl extends mpjbaseserviceimpl<opproductmapper, opproduct> implements opproductservice {
    @resource
    private opproductmapper opproductmapper;
    @override
    public list<productdto> queryallproduct() {
        mpjlambdawrapper mpjlambdawrapper = new mpjlambdawrapper<productdto>()
                .selectall(opproduct.class)//查询表1的全部字段
                .selectall(opproductinfo.class)//查询表2的全部字段
                .leftjoin(opproductinfo.class, opproductinfo::getproductid, opproduct::getid);//左查询表2条件为表二的productid=表一的id
        list<productdto> list = opproductmapper.selectjoinlist(productdto.class, mpjlambdawrapper);
        return list;
    }
}

4、测试

@springboottest
@slf4j
public class mybatisjointests {
    @autowired
    private opproductservice opproductservice;
    @test
    void test1() {
        list<productdto> productdtos = opproductservice.queryallproduct();
        log.info(productdtos.tostring());
    }
}

5、结果

三、分页查询

1、mpjlambdawrapper几个方法

接下来的mpjlambdawrapper就是构建查询条件的核心了，看一下我们在上面用到的几个方法：

selectall()：查询指定实体类的全部字段
select()：查询指定的字段，支持可变长参数同时查询多个字段，但是在同一个select中只能查询相同表的字段，所以如果查询多张表的字段需要分开写
selectas()：字段别名查询，用于数据库字段与接收结果的dto中属性名称不一致时转换
leftjoin()：左连接，其中第一个参数是参与联表的表对应的实体类，第二个参数是这张表联表的on字段，第三个参数是参与联表的on的另一个实体类属性

除此之外，还可以正常调用mybatis-plus中的各种原生方法，文档中还提到，默认主表别名是t，其他的表别名以先后调用的顺序使用t1、t2、t3以此类推。

和mybatis-plus非常类似，除了lamdawrapper外还提供了普通querywrapper的写法，举例代码：

public void getordersimple() {
     list<xxxxxdto> list = xxxxxmapper.selectjoinlist(xxxxx.class,
     new mpjquerywrapper<xxxxx>()
      .selectall(xxxxx.class)
      .select("t2.unit_price","t2.name as product_name")
      .select("t1.name as user_name")
      .leftjoin("t_user t1 on t1.id = t.user_id")
      .leftjoin("t_product t2 on t2.id = t.product_id")
      .eq("t.status", "3")
    );
    log.info(list.tostring());
}

或者

        mpjlambdawrapper mpjlambdawrapper = new mpjlambdawrapper<productdto>()
                .selectall(opproduct.class)//查询表1的全部字段
                .selectas(opproductinfo::getid,"productinfoid")//起别名
                .selectas(opproductinfo::getname,productdto::getname)//起别名
                .selectas(opproductinfo::getprice,productdto::getprice)//起别名
                .leftjoin(opproductinfo.class, opproductinfo::getproductid, opproduct::getid);//左查询表2条件为表二的productid=表一的id
        list<productdto> list = opproductmapper.selectjoinlist(productdto.class, mpjlambdawrapper);
        return list;

2、分页代码举例

    public ipage<productdto> querypageproduct(integer pageno, integer pagecount) {
        mpjlambdawrapper mpjlambdawrapper = new mpjlambdawrapper<productdto>()
                .selectall(opproduct.class)//查询表1的全部字段
                .selectall(opproductinfo.class)//查询表2的全部字段
                .leftjoin(opproductinfo.class, opproductinfo::getproductid, opproduct::getid);//左查询表2条件为表二的productid=表一的id
        ipage<productdto> page = opproductmapper.selectjoinpage(new page<productdto>(pageno, pagecount), productdto.class, mpjlambdawrapper);
        return page;
    }