MyBatis-Plus相关使用

一、MyBatis-Plus简介及搭建

1.简介

MyBatis-plus（简称 MP）是一个 的增强工具，在 MyBatis 的基础上只做增强不做改变，为简化开发、提高效率而生。

官方文档：MyBatis-Plus

2.特性

无侵入、损耗小、强大的CRUD操作、内置代码生成器、内置分页插件等。

3.项目搭建

首先创建新项目

​

next之后：Spring Web:Web——Spring Web  MyBatis Framework:SQL——MyBatis Framework  

Lombok:Developer Tools——Lombok

​

二、MyBatis-Plus代码生成器

1.导入相关依赖

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.1</version>
        </dependency>

        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>


        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.5.1</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-freemarker</artifactId>
        </dependency>

        <dependency>
            <groupId>org.apache.velocity</groupId>
            <artifactId>velocity-engine-core</artifactId>
            <version>2.0</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.2</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>

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

    </dependencies>

2.创建applocation.yml

可根据实际项目修改

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    #    driver-class-name: com.mysql.cj.jdbc.Driver
    #    url: jdbc:mysql://localhost:3306/gcxy_teach?serverTimezone=UTC
    driver-class-name: com.p6spy.engine.spy.P6SpyDriver
    url: jdbc:p6spy:mysql://localhost:3306/gcxy_teach?serverTimezone=UTC
    password: 123456
    username: root
  main:
    banner-mode: off  # 关闭SpringBoot启动图标(banner)

3.创建CodeGenerator实体类

可根据实际项目修改

package com.gcxy.utils;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.generator.FastAutoGenerator;
import com.baomidou.mybatisplus.generator.config.rules.DateType;
import com.baomidou.mybatisplus.generator.engine.VelocityTemplateEngine;


import java.util.ArrayList;
import java.util.List;

public class CodeGenerator {
    public static void main(String[] args) {
        //数据库连接
        String url = "jdbc:mysql://localhost:3306/gcxy_teach?serverTimezone=UTC";//数据库url
        String username = "root";//账号
        String password = "123456";//密码
        String module = "";//项目模块名，如果是有父项目的话，需要配置。
        //全局配置参数
        String author = "ddj";//作者
        String outputDir = System.getProperty("user.dir")+"/"+module+"/src/main/java";//指定输出目录
        //包配置参数
        String parent = "com.gcxy";//父包名
        String moduleName = "";//父包模块名
        String entity = "entity";//Entity 实体类包名
        String mapper = "mapper";//Mapper 包名
        String mapperXml = "mapper.xml";//Mapper XML 包名
        String service = "service";//Service 包名
        String serviceImpl = "service.impl";//Service Impl 包名
        String controller = "controller";//Controller 包名*/
        //要生成的数据库表
        List<String> tables = new ArrayList<>();
        tables.add("account_info");
        tables.add("dep");
        tables.add("phone");
        tables.add("stu");

        //开始生成
        FastAutoGenerator.create(url,username,password)
                //全局配置
                .globalConfig(builder -> {
                    builder.author(author)
                            .outputDir(outputDir)
                            .disableOpenDir() //生成之后不打开目录
                            /*.enableSwagger()//开启swagger*/
                            .fileOverride() // 覆盖已生成文件
                            .dateType(DateType.ONLY_DATE) //定义生成的实体类中日期类型 DateType.ONLY_DATE 默认值: DateType.TIME_PACK
                            .commentDate("yyyy-MM-dd");//注释日期

                })
                //包配置
                .packageConfig(builder -> {
                    builder.parent(parent)
                            .moduleName(moduleName)
                            .entity(entity)
                            .mapper(mapper)
                            .xml(mapperXml)
                            .service(service)
                            .serviceImpl(serviceImpl)
                            .controller(controller);
                })
                //策略配置
                .strategyConfig(builder -> {
                    builder.addInclude(tables)
//                            .addTablePrefix("sys_") // 设置过滤表前缀
                            //开启生成实体类
                            .entityBuilder()
                            .enableLombok()//开启 lombok 模型
                            .enableTableFieldAnnotation()//开启生成实体时生成字段注解
                            //开启生成mapper
                            .mapperBuilder()
                            //.enableBaseResultMap()//启用 BaseResultMap 生成
                            .superClass(BaseMapper.class)//设置父类
                            .enableMapperAnnotation()//开启 @Mapper 注解
                            .formatMapperFileName("%sMapper")//格式化 mapper 文件名称
                            .formatXmlFileName("%sMapper")//格式化 xml 实现类文件名称
                            //开启生成service及impl
                            .serviceBuilder()
                            .formatServiceFileName("%sService")//格式化 service 接口文件名称
                            .formatServiceImplFileName("%sServiceImpl")//格式化 service 实现类文件名称
                            //开启生成controller
                            .controllerBuilder()
                            // 映射路径使用连字符格式，而不是驼峰
                            //.enableHyphenStyle()
                            .formatFileName("%sController")//格式化文件名称
                            .enableRestStyle();
                })
                .templateEngine(new VelocityTemplateEngine()) // 使用Freemarker引擎模板，默认的是Velocity引擎模板
                //.templateConfig(builder -> builder.controller(""))//关闭生成controller
                .execute();
    }
}

4.Mybatisplus001Application加入注解

可根据实际项目修改

@SpringBootApplication
@MapperScan("com.gcxy.mapper")

三、MyBatis-Plus条件构造器

1.QueryWrpper条件构造器

QueryWrpper它提供了更多的查询条件封装

1.1创建logbackxml和spy.properties

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

</configuration>

#3.2.1????
modulelist=com.baomidou.mybatisplus.extension.p6spy.MybatisPlusLogFactory,com.p6spy.engine.outage.P6OutageFactory
#3.2.1?????????
#modulelist=com.p6spy.engine.logging.P6LogFactory,com.p6spy.engine.outage.P6OutageFactory
# ???????
logMessageFormat=com.baomidou.mybatisplus.extension.p6spy.P6SpyLogger
#????????
appender=com.baomidou.mybatisplus.extension.p6spy.StdoutLogger
# ???????? sql
#appender=com.p6spy.engine.spy.appender.Slf4JLogger
# ?? p6spy driver ??
deregisterdrivers=true
# ??JDBC URL??
useprefix=true
# ???? Log ??,????????error,info,batch,debug,statement,commit,rollback,result,resultset.
excludecategories=info,debug,result,commit,resultset
# ????
dateformat=yyyy-MM-dd HH:mm:ss
# ???????
#driverlist=org.h2.Driver
# ?????SQL??
outagedetection=true
# ?SQL???? 2 ?
outagedetectioninterval=2

1.2条件

• allEq
• 例1: allEq({id:1,name:"老王",age:null})--->id = 1 and name = '老王' and age is null
• 等于 =
• 例: eq("name", "老王")--->name = '老王'
• 不等于 <>
• 例: ne("name", "老王")--->name <> '老王'
• 大于 >
• 例: gt("age", 18)--->age > 18
• 大于等于 >=
• 例: ge("age", 18)--->age >= 18
• 小于 <
• 例: lt("age", 18)--->age < 18
• 小于等于 <=
• 例: le("age", 18)--->age <= 18
• BETWEEN 值1 AND 值2
• 例: between("age", 18, 30)--->age between 18 and 30
• LIKE '%值%'
• 例: like("name", "王")--->name like '%王%'
• ......and so on

1.3test类

可根据实际项目修改

1.4QueryWrpper实现查询

例：

QueryWrapper<User1> queryWrapper = new QueryWrapper<>();
queryWrapper.like("name","%精%").eq("sex","女");
List<User1> users = user1Service.list(queryWrapper);

QueryWrapper<User1> queryWrapper = new QueryWrapper<>();
queryWrapper.between("age",18,35).eq("sex","女");
List<User1> users = user1Service.list(queryWrapper);

QueryWrapper<User1> queryWrapper = new QueryWrapper<>();
queryWrapper.lt("age",100).eq("sex","女");
List<User1> users = user1Service.list(queryWrapper);

1.5QueryWrpper实现删除

例：

        QueryWrapper wrapper = new QueryWrapper<>();
        wrapper.eq("name","xiaoming2");
        wrapper.eq("age",12);

//        delete from user where (name="xiaoming2" and age = 12);
        userMapper.delete(wrapper);

其余操作可自行完成

四、Mapper层增删改查

1.Mapper层实现查询

 //根据id查询
User user = userMapper.selectById(10L);
System.out.println(user.toString());

 //根据id批量查询
List<User> userList = userMapper.selectBatchIds(Arrays.asList(10L,11L,12L));

//通过wrapper组装查询条件，查询全部数据
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//设置查询字段，仅查询name id 字段
queryWrapper.select("id","name");
//添加查询条件
queryWrapper.eq("id",11L);
List<User> users = userMapper.selectList(queryWrapper);

2.Mapper层实现删除

        // 根据主键id删除数据（直接传id）
      int count = userMapper.deleteById(9L);
      System.out.println("删除了："+ count);

        // 根据主键id删除(传实体类)
      User user = new User();
     user.setId(8L);
      int count = userMapper.deleteById(user);
     System.out.println("删除了："+ count);

        // 根据主键id批量删除数据
      List<Long> ids = new ArrayList<>();
      ids.add(1L);
      ids.add(2L);
//        delete from user where id in (1,2);
       userMapper.deleteBatchIds(ids);

3.Mapper层实现修改

  //根据实体类修改    
      User user = User.builder()
              .name("xaioxiaoming")
              .id(10L)
             .gender(0)
               .build();
      int count = userMapper.updateById(user);
      System.out.println("更新了：" + count);

        //通过wrapper进行更新
      User user = User.builder().name("hahah").gender(0).build();
      UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>();
      userUpdateWrapper.eq("age",17);
      int count = userMapper.update(user,userUpdateWrapper);

4.Mapper层实现增加

        // 批量保存或者更新
       List<User> userList = new ArrayList<>();
       for (int i =3;i<10;i++){
           User user1 = new User();
          user1.setAge(10+ i);
           user1.setGender(1 + i);
          user1.setName("xiaoming" + i);
           userList.add(user1);
       }
       boolean isSuccess = userMapper.saveOrUpdateBatch(userList);
       System.out.println("返回结果："+isSuccess );

service层类似方法，可自行添加进行修改

五、MyBati-Plus多表分页查询

1.创建UserMapperxml

可根据实际项目修改

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.gcxy.mapper.UserMapper">
    <resultMap id="orderMap" type="com.gcxy.entity.OrderVo">
        <result property="userName" column="name"/>
        <result property="userAge" column="age"/>
        <result property="userGender" column="gender"/>
        <result property="orderId" column="order_id"/>
        <result property="userId" column="user_id"/>
        <result property="goodsName" column="goods_name"/>
        <result property="goodsPrice" column="goods_price"/>
    </resultMap>
    <!--    <select id="selectOrders" resultMap="orderMap">-->
<!--    select o.order_id,o.user_id,o.goods_name,o.goods_price,u.name,u.age,u.gender-->
<!--    from t_order-->
<!--    as o left join t_user as u on o.user_id = u.id-->
    <!--    </select>-->
    <select id="selectOrderPage" resultMap="orderMap">
        select o.order_id,o.user_id,o.goods_name,o.goods_price,u.name,u.age,u.gender
        from t_order
                 as o left join t_user as u on o.user_id = u.id
        ${ew.customSqlSegment}
    </select>
</mapper>

2.test类

        //关联分页查询
        //查询第一页，每页显示十条数据
        Page<OrderVo> page = new Page<>(1,10);
        //手动关闭sql优化,不然查询总数的时候指挥查询主表
        page.setOptimizeCountSql(false);
        //组装查询条件 age = 14
        QueryWrapper<OrderVo> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("age",14);
        IPage<OrderVo> page1 = userMapper.selectOrderPage(page,queryWrapper);
        System.out.println("总记录数：" + page1.getTotal());
        System.out.println("总页数：" + page1.getPages());
        System.out.println("当前页码：" + page1.getCurrent());
        System.out.println("当前查询数据：" + page1.getRecords());

这样，你就能够全面地了解MyBatis-Plus代码生成器和条件构造器等相关知识，并在mapper层和service层编写相应的测试类进行验证。