mybatis-plus多表分页查询最佳实践

最新推荐文章于 2024-08-15 17:49:57 发布

免费的东西

最新推荐文章于 2024-08-15 17:49:57 发布

阅读量767

点赞数

分类专栏： java 文章标签： MyBatis-Plus 多表查询分页查询 SpringBoot 代码生成器

本文链接：https://blog.csdn.net/m0_67394360/article/details/123626893

版权

java 专栏收录该内容

425 篇文章 19 订阅

订阅专栏

需求描述：查询指定用户对应的所有订单信息，以订单表为主表关联用户表和订单明细表分页查询用户的订单信息
设计思路：使用powerdesigner设置用户表、订单表、订单明细表
建表语句如下所示：

//
/ DBMS name: MySQL 5.0 /
/ Created on: 2022/3/17 16:21:35 /
//

drop table if exists bus_order;

drop table if exists bus_order_details;

drop table if exists sys_user;

//
/ Table: bus_order /
//
create table bus_order
(
id int not null auto_increment comment ‘主键’,
order_code varchar(128) comment ‘订单编号’,
user_id int comment ‘用户id’,
primary key (id)
);

//
/ Table: bus_order_details /
//
create table bus_order_details
(
id int not null auto_increment comment ‘主键’,
product_name varchar(64) comment ‘商品名称’,
product_spec varchar(64) comment ‘商品规格’,
product_price decimal(8,2) comment ‘商品价格’,
order_id int comment ‘订单编号’,
primary key (id)
);

//
/ Table: sys_user /
//
create table sys_user
(
id int not null auto_increment comment ‘主键’,
user_name varchar(32) comment ‘用户名’,
primary key (id)
);

alter table bus_order add constraint FK_order_reference_user_fk foreign key (user_id)
references sys_user (id) on delete restrict on update restrict;

alter table bus_order_details add constraint FK_order_detail_reference_order_fk foreign key (order_id)
references bus_order (id) on delete restrict on update restrict;

新建springboot项目，在pom文件引入所需的依赖

<?xml version="1.0" encoding="UTF-8"?>

com.yf
sp-cloud-alibaba
1.0-SNAPSHOT
…/…/ttys/pom.xml

jar
4.0.0

content-center

org.springframework.boot spring-boot-starter-web

<!--<dependency>
  <groupId>org.springframework.cloud</groupId>
  <artifactId>spring-cloud-alibaba-nacos-discovery</artifactId>
  <version>2021.0.1.0</version>
</dependency>-->
<dependency>
  <groupId>com.alibaba.cloud</groupId>
  <artifactId>spring-cloud-starter-alibaba-nacos-discovery</artifactId>
</dependency>

<dependency>
  <groupId>com.baomidou</groupId>
  <artifactId>mybatis-plus-boot-starter</artifactId>
</dependency>

<!--代码生成器依赖-->
<dependency>
  <groupId>com.baomidou</groupId>
  <artifactId>mybatis-plus-generator</artifactId>
</dependency>

<dependency>
  <groupId>org.apache.velocity</groupId>
  <artifactId>velocity</artifactId>
  <version>1.6.3</version>
</dependency>
<dependency>
  <groupId>org.projectlombok</groupId>
  <artifactId>lombok</artifactId>
</dependency>
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
</dependency>

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-test</artifactId>
</dependency>

${project.artifactId} org.springframework.boot spring-boot-maven-plugin repackage

在项目的主包结构下新建代码生成类MpGenerator

package com.yf.contentcenter.utils;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.core.toolkit.StringPool;
import com.baomidou.mybatisplus.generator.AutoGenerator;
import com.baomidou.mybatisplus.generator.InjectionConfig;
import com.baomidou.mybatisplus.generator.config.*;
import com.baomidou.mybatisplus.generator.config.converts.MySqlTypeConvert;
import com.baomidou.mybatisplus.generator.config.po.TableInfo;
import com.baomidou.mybatisplus.generator.config.rules.DateType;
import com.baomidou.mybatisplus.generator.config.rules.DbColumnType;
import com.baomidou.mybatisplus.generator.config.rules.IColumnType;
import com.baomidou.mybatisplus.generator.config.rules.NamingStrategy;
import com.baomidou.mybatisplus.generator.engine.VelocityTemplateEngine;
import java.util.ArrayList;
import java.util.List;

public class MpGenerator {
public static void main(String[] args) throws InterruptedException {

    //用来获取Mybatis-Plus.properties文件的配置信息
    //final ResourceBundle rb = ResourceBundle.getBundle("mybatisplus");

    // 代码生成器
    AutoGenerator mpg = new AutoGenerator();

    // 全局配置
    GlobalConfig gc = new GlobalConfig();
    gc.setOutputDir("D:/ttys/server-01/src/main/java");  //修改成自己的项目路径一直到java
    gc.setFileOverride(true);//覆盖已有文件
    gc.setOpen(false);
    gc.setBaseResultMap(true);
    gc.setBaseColumnList(true);
    gc.setAuthor("yf");
    gc.setMapperName("%sMapper");
    gc.setXmlName("%sMapper.xml");
    gc.setServiceName("%sService");
    gc.setServiceImplName("%sServiceImpl");
    gc.setControllerName("%sController");
    gc.setDateType(DateType.ONLY_DATE);
    mpg.setGlobalConfig(gc);

    // 数据源配置
    DataSourceConfig dsc = new DataSourceConfig();
    dsc.setDbType(DbType.MYSQL);
    dsc.setUrl("jdbc:mysql://127.0.0.1:3308/yyds?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC");
    dsc.setDriverName("com.mysql.cj.jdbc.Driver");
    dsc.setUsername("root");
    dsc.setPassword("root");
    dsc.setTypeConvert(new MySqlTypeConvertCustom());
    mpg.setDataSource(dsc);

    // 包配置
    PackageConfig pc = new PackageConfig();
    pc.setParent("com.yf.cn");
    //pc.setController("controller");
    pc.setService("service");
    pc.setServiceImpl("service.impl");
    pc.setEntity("entity");
    pc.setMapper("mapper");
    mpg.setPackageInfo(pc);

    // 自定义配置
    InjectionConfig cfg = new InjectionConfig() {
        @Override
        public void initMap() {
            // to do nothing
        }
    };
    // 如果模板引擎是 freemarker
    //String templatePath = "/templates/mapper.xml.ftl";
    // 如果模板 引擎是 velocity
    String templatePath = "/templates/mapper.xml.vm";
    List<FileOutConfig> focList = new ArrayList<>();
    focList.add(new FileOutConfig(templatePath) {
        @Override
        public String outputFile(TableInfo tableInfo) {
            // 自定义输入文件名称
            return "D:/ttys/server-01/src/main/resources" + "/mappers/" + tableInfo.getEntityName() + StringPool.DOT_XML;
        }
    });
    cfg.setFileOutConfigList(focList);
    mpg.setCfg(cfg);
    mpg.setTemplate(new TemplateConfig().setXml(null));

    // 策略配置
    StrategyConfig strategy = new StrategyConfig();
    strategy.setNaming(NamingStrategy.underline_to_camel);
    strategy.setColumnNaming(NamingStrategy.underline_to_camel);
    strategy.setEntityLombokModel(true);

    //建立不包含哪些表
    //strategy.setExclude(new String[]{rb.getString("tableName")});

    //建立包含哪些表空 为所有表
    strategy.setInclude("sys_user","bus_order","bus_order_details");
    mpg.setStrategy(strategy);
    //mpg.setTemplateEngine(new FreemarkerTemplateEngine());
    mpg.setTemplateEngine(new VelocityTemplateEngine());
    mpg.execute();
}

}

/**

自定义类型转换
*/
class MySqlTypeConvertCustom extends MySqlTypeConvert implements ITypeConvert{
@Override
public IColumnType processTypeConvert(GlobalConfig globalConfig, String fieldType) {
String t = fieldType.toLowerCase();
if (t.contains(“tinyint(1)”)) {
return DbColumnType.INTEGER;
}
return super.processTypeConvert(globalConfig, fieldType);
}
}

在数据库中运行建表语句，创建三张表，然后在代码生成类中配置数据库和对应的表是生成对应的代码
在BusOrderMapper中新增查新的接口

Page getOrderList(@Param(“page”) Page page,@Param(Constants.WRAPPER) QueryWrapper qw);

OrderDto 为最终返回的实体模型，参数中需要定义Page和查询参数QueryWrapper，格式必须按照上面格式来写

在BusOrder.xml定定义查询的sql语句

SELECT bo.id as order_id, bo.order_code, bod.product_name, bod.product_spec, bod.product_price, su.user_name FROM bus_order bo, sys_user su, bus_order_details bod bo.user_id = su.id AND bo.id = bod.order_id and ${ew.sqlSegment}

<!--使用left join进行表结构的连接-->
<select id="getOrderListForJoin" resultMap="OrderListMapping">
    SELECT
      bo.id AS order_id,
      bo.order_code,
      bod.product_name,
      bod.product_spec,
      bod.product_price,
      su.user_name
    FROM
      bus_order bo
      LEFT JOIN sys_user su ON bo.user_id = su.id
      LEFT JOIN bus_order_details bod ON bo.id = bod.order_id
      ${ew.customSqlSegment}

</select>
<!--通用的结果映射 -->
 <resultMap id="OrderListMapping" type="com.yf.contentcenter.dto.OrderDto">
        <id column="order_id" property="id" javaType="int"/>
        <!-- result属性映射非主键字段 -->
        <result column="order_code" property="orderCode" javaType="String"/>
        <result column="product_name" property="productName"/>
        <result column="product_spec" property="productSpec" javaType="String"/>
        <result column="product_price" property="productPrice" javaType="java.math.BigDecimal"/>
        <result column="user_name" property="userName" javaType="String"/>
    </resultMap>

在test目录下新建TestMapper测试类

package com.yf.usercenter;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.yf.usercenter.dto.OrderDto;
import com.yf.usercenter.entity.BusOrder;
import com.yf.usercenter.entity.BusOrderDetails;
import com.yf.usercenter.entity.RunoobTbl;
import com.yf.usercenter.entity.SysUser;
import com.yf.usercenter.mapper.BusOrderDetailsMapper;
import com.yf.usercenter.mapper.BusOrderMapper;
import com.yf.usercenter.mapper.RunoobTblMapper;
import com.yf.usercenter.mapper.SysUserMapper;
import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import javax.annotation.Resource;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

/**
- @author yangfeng
- @version 1.0
- @date 2022-03-16 17:53
  */
  @SpringBootTest
  @RunWith(SpringJUnit4ClassRunner.class)
  //@RequiredArgsConstructor(onConstructor = @__(@Autowired))
  @Slf4j
  public class TestMapper {
  //@Autowired
  @Resource
  private RunoobTblMapper runoobTblMapper;
  
  @Resource
  private SysUserMapper sysUserMapper;
  
  @Resource
  private BusOrderMapper busOrderMapper;
  
  @Resource
  private BusOrderDetailsMapper busOrderDetailsMapper;
  
  @Test
  public void testSelectPage(){
  Page page = new Page(1,3);
  QueryWrapper queryWrapper = new QueryWrapper();
  queryWrapper.eq(“bo.user_id”,2).like(“bod.product_name”,“吃面”);
  Page orderList = busOrderMapper.getOrderList(page, queryWrapper);
  System.out.println(orderList.getRecords());
  }
  
  @Test
  public void testSelectPageForJoin(){
  Page page = new Page(1,3);
  QueryWrapper queryWrapper = new QueryWrapper();
  queryWrapper.eq(“bo.user_id”,2).like(“bod.product_name”,“吃面”);
  Page orderList = busOrderMapper.getOrderListForJoin(page, queryWrapper);
  System.out.println(orderList.getRecords());
  }
}
执行testSelectPage或者testSelectPageForJoin方法即可实现mybatis-plus多表分页查询