62°

springboot项目mybatisplus的快速使用

一 、MyBatisPlus 介绍  转自mybatisplus官网 下方附有mybatisplus官方网站

MyBatis-Plus(简称 MP)是一个 MyBatis 的增强工具,在 MyBatis 的基础上只做增强不做改变,为简化开发、提高效率而生。
(1)愿景
我们的愿景是成为 MyBatis 最好的搭档,就像 魂斗罗 中的 1P、2P,基友搭配,效率翻倍。
(2)特性
①无侵入:只做增强不做改变,引入它不会对现工程产生影响,如丝般顺滑
②损耗小:启动即会自动注入基本 CURD,性能基本无损耗,直接面向对象操作
③强大的 CRUD 操作:内置通用 Mapper、通用 Service,仅仅通过少量配置即可实现单表大部分 CRUD 操作,更有强大的条件构造器,满足各类使用需求
④支持 Lambda 形式调用:通过 Lambda 表达式,方便的编写各类查询条件,无需再担心字段写错
⑤支持主键自动生成:支持多达 4 种主键策略(内含分布式唯一 ID 生成器 - Sequence),可自由配置,完美解决主键问题
⑥支持 ActiveRecord 模式:支持 ActiveRecord 形式调用,实体类只需继承 Model 类即可进行强大的 CRUD 操作
⑦支持自定义全局通用操作:支持全局通用方法注入( Write once, use anywhere )
⑧内置代码生成器:采用代码或者 Maven 插件可快速生成 Mapper 、 Model 、 Service 、 Controller 层代码,支持模板引擎,更有超多自定义配置等您来使用
⑨内置分页插件:基于 MyBatis 物理分页,开发者无需关心具体操作,配置好插件之后,写分页等同于普通 List 查询
⑩分页插件支持多种数据库:支持 MySQL、MariaDB、Oracle、DB2、H2、HSQL、SQLite、Postgre、SQLServer2005、SQLServer 等多种数据库
⑪内置性能分析插件:可输出 Sql 语句以及其执行时间,建议开发测试时启用该功能,能快速揪出慢查询
⑫内置全局拦截插件:提供全表 delete 、 update 操作智能分析阻断,也可自定义拦截规则,预防误操作

代码及文档发布地址 
官方地址: 
http://mp.baomidou.com 
代码发布地址: 
Github: https://github.com/baomidou/mybatis-plus 
Gitee: https://gitee.com/baomidou/mybatis-plus 
文档发布地址: 
http://mp.baomidou.com/#/?id=%E7%AE%80%E4%BB%8B

二、 springboot 快速开始使用

快速开始参考:http://mp.baomidou.com/guide/quick-start.html
测试项目: mybatis_plus
数据库:mybatis_plus
1 创建并初始化数据库
(1)创建数据库:
mybatis_plus

(2)创建 User 表
其表结构如下:

其对应的数据库 Schema 脚本如下:
DROP TABLE IF EXISTS user;

CREATE TABLE user
(
    id BIGINT(20) NOT NULL COMMENT '主键ID',
    name VARCHAR(30) NULL DEFAULT NULL COMMENT '姓名',
    age INT(11) NULL DEFAULT NULL COMMENT '年龄',
    email VARCHAR(50) NULL DEFAULT NULL COMMENT '邮箱',
    PRIMARY KEY (id)
);

其对应的数据库 Data 脚本如下:
DELETE FROM user;

INSERT INTO user (id, name, age, email) VALUES
(1, 'Jone', 18, 'test1@baomidou.com'),
(2, 'Jack', 20, 'test2@baomidou.com'),
(3, 'Tom', 28, 'test3@baomidou.com'),
(4, 'Sandy', 21, 'test4@baomidou.com'),
(5, 'Billie', 24, 'test5@baomidou.com');

2 初始化工程
使用 Spring Initializr 快速初始化一个 Spring Boot 工程
Group:com.Daniel
Artifact:mybatis-plus
版本:2.0.7.RELEASE

<mybatis-plus.version>3.0.5</mybatis-plus.version>

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>daniel-parent</artifactId>
        <groupId>com.daniel</groupId>
        <version>0.0.1-SNAPSHOT</version>
        <relativePath>../daniel-parent/pom.xml</relativePath>
    </parent>
    <modelVersion>4.0.0</modelVersion>
&lt;groupId&gt;com.daniel&lt;/groupId&gt;
&lt;artifactId&gt;mybatis-plus&lt;/artifactId&gt;

&lt;dependencies&gt;
    &lt;dependency&gt;
        &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
        &lt;artifactId&gt;spring-boot-starter&lt;/artifactId&gt;
    &lt;/dependency&gt;
    &lt;dependency&gt;
        &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
        &lt;artifactId&gt;spring-boot-starter-test&lt;/artifactId&gt;
        &lt;scope&gt;test&lt;/scope&gt;
    &lt;/dependency&gt;
    &lt;!--mybatis-plus--&gt;
    &lt;dependency&gt;
        &lt;groupId&gt;com.baomidou&lt;/groupId&gt;
        &lt;artifactId&gt;mybatis-plus-boot-starter&lt;/artifactId&gt;
        &lt;version&gt;3.0.5&lt;/version&gt;
    &lt;/dependency&gt;
    &lt;!--mysql--&gt;
    &lt;dependency&gt;
        &lt;groupId&gt;mysql&lt;/groupId&gt;
        &lt;artifactId&gt;mysql-connector-java&lt;/artifactId&gt;
    &lt;/dependency&gt;
    &lt;!--lombok用来简化实体类--&gt;
    &lt;dependency&gt;
        &lt;groupId&gt;org.projectlombok&lt;/groupId&gt;
        &lt;artifactId&gt;lombok&lt;/artifactId&gt;
    &lt;/dependency&gt;
&lt;/dependencies&gt;

</project>

3 配置
在 application.properties 配置文件中添加 MySQL 数据库的相关配置:
注意:
(1)springboot2.0.x和springboot2.1.x使用不同
springboot2.1.x版本时候,写数据库驱动和数据库路径时候不一样的
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/mybatis_plus?serverTimezone=GMT%2B8
(2)springboot1.x和springboot2.x区别
①springboot1.x底层使用spring4.x
②springboot2.x底层使用spring5.x
本工程由于父工程为2.0.7故配置文件为:(为完整配置已经进行过完整的mybatis的基本功能测试)

spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/mybatis_plus
spring.datasource.username=******
spring.datasource.password=******
#mybatis日志
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
#全局设置主键生成策略
#global-config 全局配置对所的类起作用
#mybatis-plus.global-config.db-config.id-type=auto
#其它主键策略:分析 IdType 源码可知
#配置的是逻辑删除的删除的两个状态值,默认也是这两个配不配都可以
mybatis-plus.global-config.db-config.logic-delete-value=1
mybatis-plus.global-config.db-config.logic-not-delete-value=0
#环境设置:dev、test、prod 本工程为SQL 执行性能分析插件时设置
spring.profiles.active=dev

4 编写代码
(1)主类
在 Spring Boot 启动类中添加 @MapperScan 注解,扫描 Mapper 文件夹
注意:扫描的包名根据实际情况修改

@SpringBootApplication
@MapperScan("com.atguigu.mybatisplus.mapper")
public class MybatisPlusApplication {
public static void main(String[] args) {
    SpringApplication.run(MybatisPlusApplication.class,args);
}
}

(2)实体
创建包 entity 编写实体类 User.java(此处使用了 Lombok 简化代码)

@Data
public class User {
private Long id;
private String name;
private Integer age;
private String email;
}

Lombok使用参考:
https://blog.csdn.net/motui/article/details/79012846


(3)mapper
创建包 mapper 编写Mapper 接口: UserMapper.java

@Component
public interface UserMapper extends BaseMapper<User> {
}

5 开始使用
添加测试类,进行功能测试:
 

@RunWith(SpringRunner.class)
@SpringBootTest
public class MybatisPlusApplicationTests {
​
@Autowired
private UserMapper userMapper;
​
@Test
public void testSelectList() {
System.out.println(("----- selectAll method test ------"));
//UserMapper 中的 selectList() 方法的参数为 MP 内置的条件封装器 Wrapper
//所以不填写就是无任何条件
List<User> users = userMapper.selectList(null);
users.forEach(System.out::println);
}
}

6 基本的crud

以下为测试代码

package com.daniel.mp;

import org.mybatis.spring.annotation.MapperScan; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication;

/**

  • @author Daniel
  • @Description: */ @SpringBootApplication public class MpApplication { public static void main(String[] args) { SpringApplication.run(MpApplication.class,args); } }

package com.daniel.mp.entity;

import com.baomidou.mybatisplus.annotation.*; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import lombok.ToString;

import java.util.Date; import java.util.logging.FileHandler;

/**

  • @author Daniel
  • @Description: */ @Data @ToString @NoArgsConstructor @AllArgsConstructor public class User { @TableId(type = IdType.ID_WORKER) private Long id; private String name; private Integer age; private String email; @TableField(fill = FieldFill.INSERT) private Date createTime; @TableField(fill = FieldFill.INSERT_UPDATE) private Date updateTime; @Version @TableField(fill = FieldFill.INSERT) private Integer version; @TableLogic @TableField(fill = FieldFill.INSERT) private Integer deleted; }

package com.daniel.mp.mapper;

import com.atguigu.mp.entity.User; import com.baomidou.mybatisplus.core.mapper.BaseMapper; import org.springframework.stereotype.Component;

/**

  • @author Daniel
  • @Description: */ @Component public interface UserMapper extends BaseMapper<User> { }

package com.daniel.mp.config;

import com.baomidou.mybatisplus.core.injector.ISqlInjector; import com.baomidou.mybatisplus.extension.injector.LogicSqlInjector; import com.baomidou.mybatisplus.extension.plugins.OptimisticLockerInterceptor; import com.baomidou.mybatisplus.extension.plugins.PerformanceInterceptor; import org.mybatis.spring.annotation.MapperScan; import org.springframework.boot.SpringBootConfiguration; import org.springframework.context.annotation.Bean; import org.springframework.transaction.annotation.EnableTransactionManagement;

/**

  • @author Daniel
  • @Description: / @MapperScan("com.atguigu.mp.mapper") @SpringBootConfiguration @EnableTransactionManagement public class MybatisPlusConfig { /*
    • 乐观锁插件 / @Bean public OptimisticLockerInterceptor optimisticLockerInterceptor(){ return new OptimisticLockerInterceptor(); } /*
    • 逻辑删除插件 / @Bean public ISqlInjector sqlInjector(){ return new LogicSqlInjector(); } /*
    • SQL 执行性能分析插件
    • 开发环境使用,线上不推荐。 maxTime 指的是 sql 最大执行时长 */ @Profile({"dev","test"})// 设置 dev test 环境开启 @Bean public PerformanceInterceptor performanceInterceptor(){ PerformanceInterceptor performanceInterceptor = new PerformanceInterceptor(); performanceInterceptor.setMaxTime(100);//ms,超过此处设置的ms则sql不执行 performanceInterceptor.setFormat(true); return performanceInterceptor; } }

package com.daniel.mp.handler;

import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler; import org.apache.ibatis.reflection.MetaObject; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.stereotype.Component;

import java.util.Date;

/**

  • @author Daniel

  • @Description: */ @Component public class MyMetaObjectHandler implements MetaObjectHandler { private static final Logger LOGGER = LoggerFactory.getLogger(MetaObjectHandler.class);

    @Override public void insertFill(MetaObject metaObject) { LOGGER.debug("开始insert fill ..."); this.setFieldValByName("createTime",new Date(),metaObject); this.setFieldValByName("updateTime",new Date(),metaObject); this.setFieldValByName("version",1,metaObject); this.setFieldValByName("deleted",0,metaObject);

    }

    @Override public void updateFill(MetaObject metaObject) { LOGGER.debug("开始update fill ..."); this.setFieldValByName("updateTime",new Date(),metaObject); } }

package com.daniel.mp.test;

import com.daniel.mp.entity.User;
import com.daniel.mp.mapper.UserMapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.IdWorker;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author Daniel
 * @Description:
 */
//public final class SpringRunner extends org.springframework.test.context.junit4.SpringJUnit4ClassRunner {
@RunWith(SpringRunner.class)
@SpringBootTest
public class MybatisPlusApplicationTests {

    @Autowired
    UserMapper userMapper;

    @Test
    public void testSelect() {
//        List<User> users = userMapper.selectList(null);
//        users.forEach(System.out::println);
        /*
        * User(id=1, name=Jone, age=18, email=test1@baomidou.com)
        *User(id=2, name=Jack, age=20, email=test2@baomidou.com)
        *User(id=3, name=Tom, age=28, email=test3@baomidou.com)
        *User(id=4, name=Sandy, age=21, email=test4@baomidou.com)
        *User(id=5, name=Billie, age=24, email=test5@baomidou.com)
        * */
        //SELECT id,name,age,email FROM user WHERE id=?
//        User user = userMapper.selectById(1L);
//        System.err.println(user);//User(id=1, name=Jone, age=18, email=test1@baomidou.com)
//
//        List<Long> ids = Arrays.asList(1L, 2L, 3L);
//        //SELECT id,name,age,email,create_time,update_time,version FROM user WHERE id IN ( ? , ? , ? )
//        List<User> users = userMapper.selectBatchIds(ids);
//        users.forEach(System.err::println);
        //当配置了性能优化时,设置为1ms时
        // The SQL execution time is too large, please optimize !
        /*
        User(id=1, name=Jone, age=18, email=test1@baomidou.com)
        User(id=2, name=Jack, age=20, email=test2@baomidou.com)
        User(id=3, name=Tom, age=28, email=test3@baomidou.com)
         */
//        Map<String,Object> map = new HashMap<>();
//        map.put("name","Jack");
//        map.put("age",20);
//        List<User> users = userMapper.selectByMap(map);
//        users.forEach(System.err::println);//User(id=2, name=Jack, age=20, email=test2@baomidou.com)

        //本查询需要借助于分页插件 PaginationInterceptor 与 SSM框架整合不同
        Page<User> page = new Page<>(1,2);
        IPage<User> userIPage = userMapper.selectPage(page, null);
        List<User> users = userIPage.getRecords();
        users.forEach(System.err::println);
        System.err.println("当前页:"+page.getCurrent());
        System.err.println("每页显示记录数:"+page.getSize());
        System.err.println("总记录数:"+page.getTotal());
        System.err.println("总页数:"+page.getPages());
        System.err.println("总记录数:"+page.getTotal());
        System.err.println("是否下一页:"+page.hasNext());
        System.err.println("是否上一页:"+page.hasPrevious());
        /*
        SELECT
        id,
        name,
        age,
        email,
        create_time,
        update_time,
        version,
        deleted
        FROM
        user
        WHERE
        deleted=0 LIMIT 0,2
        User(id=1, name=Jone, age=18, email=test1@baomidou.com, createTime=Thu Aug 15 09:31:29 GMT+08:00 2019, updateTime=Thu Aug 15 09:31:29 GMT+08:00 2019, version=1, deleted=0)
        User(id=2, name=Jack, age=20, email=test2@baomidou.com, createTime=Thu Aug 15 09:31:29 GMT+08:00 2019, updateTime=Thu Aug 15 09:31:29 GMT+08:00 2019, version=1, deleted=0)
        当前页:1
        每页显示记录数:2
        总记录数:6
        总页数:3
        总记录数:6
        是否下一页:true
        是否上一页:false
         */
        //本查询需要借助于分页插件 PaginationInterceptor
//        Page<User> page = new Page<>(1,5);
//        IPage<Map<String, Object>> mapPage = userMapper.selectMapsPage(page, null);//??????
//        //注意:此行必须使用 mapIPage 获取记录列表,否则会数据类型转换错误
//        List<Map<String, Object>> users = mapPage.getRecords();
//        users.forEach(System.err::println);
//        System.err.println("当前页:"+page.getCurrent());
//        System.err.println("每页显示记录数:"+page.getSize());
//        System.err.println("总记录数:"+page.getTotal());
//        System.err.println("总页数:"+page.getPages());
//        System.err.println("总记录数:"+page.getTotal());
//        System.err.println("是否下一页:"+page.hasNext());
//        System.err.println("是否上一页:"+page.hasPrevious());
        /*
        SELECT
        id,
        name,
        age,
        email,
        create_time,
        update_time,
        version,
        deleted
        FROM
        user
        WHERE
        deleted=0 LIMIT 0,5
        {update_time=2019-08-15 09:31:29.0, deleted=0, create_time=2019-08-15 09:31:29.0, name=Jone, id=1, version=1, age=18, email=test1@baomidou.com}
        {update_time=2019-08-15 09:31:29.0, deleted=0, create_time=2019-08-15 09:31:29.0, name=Jack, id=2, version=1, age=20, email=test2@baomidou.com}
        {update_time=2019-08-15 09:31:29.0, deleted=0, create_time=2019-08-15 09:31:29.0, name=Tom, id=3, version=1, age=28, email=test3@baomidou.com}
        {update_time=2019-08-15 09:31:29.0, deleted=0, create_time=2019-08-15 09:31:29.0, name=Sandy, id=4, version=1, age=21, email=test4@baomidou.com}
        {update_time=2019-08-15 09:31:29.0, deleted=0, create_time=2019-08-15 09:31:29.0, name=Billie, id=5, version=1, age=24, email=test5@baomidou.com}
        当前页:1
        每页显示记录数:5
        总记录数:6
        总页数:2
        总记录数:6
        是否下一页:true
        是否上一页:false
         */
    }

    @Test
    public void testInsert() {
        User user = new User();
        user.setName("苏洵");
        user.setEmail("sx@daniel.com");
        user.setAge(182);
        //加了版本号后
        //INSERT INTO user ( id, name, age, email, create_time, update_time, version ) VALUES ( ?, ?, ?, ?, ?, ?, ? )
        //加了时间后
        //INSERT INTO user ( id, name, age, email, create_time, update_time ) VALUES ( ?, ?, ?, ?, ?, ? )
        //INSERT INTO user ( id, name, age, email ) VALUES ( ?, ?, ?, ? )
        int insert = userMapper.insert(user);
        System.out.println("影响了" + insert + "行");//影响了1行
//       //id自动回填 System.out.println(user);//User(id=1161598217527648258, name=苏明玉, age=30, email=smy@daniel.com)

    }


    @Test
    public void testIdWorker(){
        long id = new com.atguigu.mp.utils.IdWorker().nextId();
        System.out.println(id);//1161599849573281792

    }

    @Test
    public void testUpdate(){
        User user = new User();
        user.setId(5L);
        user.setName("苏明成");
        user.setEmail("smy@daniel.com");
        user.setAge(30);
        //UPDATE user SET name=?, age=?, email=?, update_time=? WHERE id=?
//        UPDATE user SET name=?, age=?, email=? WHERE id=?
        int updateById = userMapper.updateById(user);
        System.out.println("修改了" + updateById + "行");//修改了1行

    }
    /**
     * 测试 乐观锁插件
     */
    @Test
    public void testOptimisticLocker(){
        User user = userMapper.selectById(1L);
        user.setName("苏辙");
        user.setAge(111);
        user.setEmail("sz@daniel.com");
        //UPDATE user SET name=?, age=?, email=?, create_time=?, update_time=?, version=? WHERE id=? AND version=?
        //版本号如果对应不上修改就不会成功
        user.setVersion(user.getVersion()-1);
        int updateById = userMapper.updateById(user);
        System.out.println("修改了" + updateById + "行");//修改了1行

    }
    @Test
    public void testDelete(){
        //DELETE FROM user WHERE id=?
        int rows = userMapper.deleteById(3L);
        System.out.println(rows + "行被删除了!!!");//1行被删除了!!!
    }
    @Test
    public void testDeleteBatchIds() {
        //DELETE FROM user WHERE id IN ( ? , ? , ? )
//        int result = userMapper.deleteBatchIds(Arrays.asList(8, 9, 10));//公司基本不会用删除的命令
//        System.out.println(result);
    }

    /**
     * 测试 逻辑删除
     */
    @Test
    public void testLogicDelete(){
        //UPDATE user SET deleted=1 WHERE id=? AND deleted=0
        int rows = userMapper.deleteById(3L);
        System.out.println(rows + "行被删除了!!!");//1行被删除了!!!
    }

    /**
     * wrapper 的测试由此往下 条件构造器
     * 1、delete
     *2、selectOne
     *3、selectCount
     *4、selectList
     5、selectMaps
     6、selectObjs
     7、update
     */
    @Test
    public void testWrapperSelect(){
//        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//        queryWrapper.isNotNull("name")
//                .ge("age",28)
//                .le("age",110);
//        List<User> users = userMapper.selectList(queryWrapper);
//        users.forEach(System.err::println);
        /*
        Execute SQL:
        SELECT
        id,
        name,
        age,
        email,
        create_time,
        update_time,
        version,
        deleted
        FROM
        user
        WHERE
        deleted=0
        AND name IS NOT NULL
        AND age >= 28
        AND age <= 110
         */

        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("name","苏大强");
        User user = userMapper.selectOne(queryWrapper);
        System.out.println(user);
    }
}

7 条件构造器

 Wrapper : 条件构造抽象类,最顶端父类
|---AbstractWrapper : 用于查询条件封装,生成 sql 的 where 条件
        |---QueryWrapper : Entity 对象封装操作类,不是用lambda语法
        |---UpdateWrapper : Update 条件封装,用于Entity对象更新操作
        |---AbstractLambdaWrapper : Lambda 语法使用 Wrapper统一处理解析 lambda 获取 column。
                |---LambdaQueryWrapper :看名称也能明白就是用于Lambda语法使用的查询Wrapper
                |---LambdaUpdateWrapper : Lambda 更新封装Wrapper

/**
 * @author Daniel
 * @create 2019--08--15--11:41
 * @Description:
 */
@RunWith(SpringRunner.class)
@SpringBootTest
public class QueryWrapperTests {
@Autowired
private UserMapper userMapper;

}

注意:以下条件构造器的方法入参中的 column 均表示数据库字段
(1) ge、gt、le、lt、isNull、isNotNull

@Test
public void testDelete(){
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper
            .isNull("name")
            .ge("age", 12)
            .isNotNull("email");
    int result = userMapper.delete(queryWrapper);
    System.out.println("delete return count = " + result);

}

SQL:UPDATE user SET deleted=1 WHERE deleted=0 AND name IS NULL AND age >= ? AND email IS NOT NULL

(2) eq、ne
注意:seletOne返回的是一条实体记录,当出现多条时会报错

@Test
public void testSelectOne() {
QueryWrapper&lt;User&gt; queryWrapper = new QueryWrapper&lt;&gt;();
queryWrapper.eq("name", "Tom");

User user = userMapper.selectOne(queryWrapper);
System.out.println(user);

}

SELECT id,name,age,email,create_time,update_time,deleted,version FROM user WHERE deleted=0 AND name = ?

(3) between、notBetween
包含大小边界

@Test
public void testSelcecCount(){
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.between("age",20,30);
    Integer integer = userMapper.selectCount(queryWrapper);
    System.out.println("20<-->30之间" + integer + "人");
    //SELECT COUNT(1) FROM user WHERE deleted=0 AND age BETWEEN ? AND ? 
}

(4)allEq

@Test
public void testAllEq(){
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    Map<String, Object> map = new HashMap<>();
    map.put("id",2L);
    map.put("name","Jack");
    map.put("age",20);
    queryWrapper.allEq(map);
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.err::println);
//SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 AND name = ? AND id = ? AND age = ?
}

(5)like、notLike、likeLeft、likeRight
selectMaps返回Map集合列表

@Test
public void testSelectMaps(){
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.notLike("name","e")
                .likeRight("email","t");
    List<Map<String, Object>> mapList = userMapper.selectMaps(queryWrapper);//返回值是Map列表
    mapList.forEach(System.err::println);
    /*
    姓名中不含e且邮箱以e开头
    SELECT id,name,age,email,create_time,update_time,version,deleted FROM user  WHERE deleted=0 AND name NOT LIKE '%e%' AND email LIKE 't%'
    {update_time=2019-08-15 09:31:29.0, deleted=0, create_time=2019-08-15 09:31:29.0, name=Jack, id=2, version=1, age=20, email=test2@baomidou.com}
    {update_time=2019-08-15 09:31:29.0, deleted=0, create_time=2019-08-15 09:31:29.0, name=Tom, id=3, version=1, age=28, email=test3@baomidou.com}
    {update_time=2019-08-15 09:31:29.0, deleted=0, create_time=2019-08-15 09:31:29.0, name=Sandy, id=4, version=1, age=21, email=test4@baomidou.com}
     */
}

(6)in、notIn、inSql、notinSql、exists、notExists
in、notIn:
notIn("age",{1,2,3})--->age not in (1,2,3)
notIn("age", 1, 2, 3)--->age not in (1,2,3)
inSql、notinSql:可以实现子查询
例: inSql("age", "1,2,3,4,5,6")--->age in (1,2,3,4,5,6)
例: inSql("id", "select id from table where id < 3")--->id in (select id from table where id < 3)
exits和notExists
EXISTS用于检查子查询是否至少会返回一行数据,该子查询实际上并不返回任何数据,而是返回值True或False
EXISTS 指定一个子查询,检测 行 的存在。
EXISTS(包括 NOT EXISTS )子句的返回值是一个BOOL值。 
EXISTS内部有一个子查询语句(SELECT ... FROM...), 我将其称为EXIST的内查询语句。其内查询语句返回一个结果集。 EXISTS子句根据其内查询语句的结果集空或者非空,返回一个布尔值。
一种通俗的可以理解为:将外查询表的每一行,代入内查询作为检验,如果内查询返回的结果取非空值,则EXISTS子句返回TRUE,
这一行行可作为外查询的结果行,否则不能作为结果。

   @Test
    public void testSelectObjs(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//        queryWrapper.in("id",1,2,3,4);//SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 AND id IN (?,?,?,?)
        // SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 AND id IN (select id from user where id < 3)
//        queryWrapper.inSql("id","select id from user where id < 3");
//        List<Object> objects = userMapper.selectObjs(queryWrapper);//返回值是Object列表
//        objects.forEach(System.err::println);
//        SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 AND EXISTS (select id from user where age = 2)
        //当exists内的子查询值(非空)时返回true当无值为空(null)时返回false
        queryWrapper.exists("select id from user where age = 2");
        List<Object> objects = userMapper.selectObjs(queryWrapper);
        objects.forEach(System.err::println);
    }

(7)or、and
注意:这里使用的是 UpdateWrapper 
不调用or则默认为使用 and 连

@Test
public void testUpdateOrAnd(){
    //本例修改后其版本号为改变故此修改不合理
    //修改或其他改变数据值得操作均需要携带version值
    //修改值
    User user = new User();
    user.setVersion(1);
    user.setAge(99);
    user.setName("Andy");
    //修改条件
    UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
    updateWrapper.like("name","d")
                 .or()
                 .between("age",98,100);
    int update = userMapper.update(user, updateWrapper);
    System.out.println(update + "行被修改");
    /*
        UPDATE user SET name='Andy', age=99, update_time='2019-08-15 16:32:53', version=2  WHERE deleted=0  AND name LIKE '%d%'  OR age BETWEEN 98 AND 100  AND version = 1
     */
}

(8)嵌套or、嵌套and
这里使用了lambda表达式,or中的表达式最后翻译成sql时会被加上圆括号

@Test
public void testUpdateLambdaOrAnd(){
    //本例修改后其版本号为改变故此修改不合理
    //修改或其他改变数据值得操作均需要携带version值
    //修改值
    User user = new User();
    user.setVersion(1);
    user.setAge(99);
    user.setName("Andy");
    //修改条件
    UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
    updateWrapper.like("name","a")
                 .or(i->i.eq("name","苏洵").ne("age",20));
    int update = userMapper.update(user, updateWrapper);
    System.out.println(update + "行被修改");
    /*
    UPDATE user  SET name='Andy', age=99, update_time='2019-08-15 16:47:02',  version=2  WHERE  deleted=0   AND name LIKE '%a%'   OR (  name = '苏洵'   AND age <> 20  )  AND version = 1
     */
}

(9) orderBy、orderByDesc、orderByAsc

@Test
public void testSelectListOrderBy(){
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.orderByDesc("id","age");
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.err::println);
    /*
    SELECT id, name, age, email, create_time,update_time, version, deleted  FROM user  WHERE deleted=0  ORDER BY id DESC , age DESC
     */
}

(10) last
直接拼接到 sql 的最后
注意:只能调用一次,多次调用以最后一次为准 有sql注入的风险,请谨慎使用

@Test
public void testSelectListLast(){
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    //SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 limit 1,2 
    queryWrapper.last("limit 1,2");
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.err::println);
}

(11) 指定要查询的列

@Test
public void testSelectListColumn(){
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.select("name","age","id");
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.err::println);
    /*
         SELECT name, age, id  FROM user  WHERE deleted=0
     */
}

12、set、setSql
最终的sql会合并 user.setAge(),以及 userUpdateWrapper.set()  和 setSql() 中 的字段

@Test
public void testUpdateSet(){
    //修改值
    User user = new User();
    user.setVersion(3);
    user.setAge(1099);
//修改条件
UpdateWrapper&lt;User&gt; updateWrapper = new UpdateWrapper&lt;&gt;();
updateWrapper.like("name","大")
             .set("name","王大强")
             .setSql("email='wang@daniel.com'");
int update = userMapper.update(user, updateWrapper);
System.out.println(update + "行被修改");
/*

UPDATE user SET age=1099,update_time='2019-08-15 17:24:28', version=4,name='王大强', email='wang@daniel.com' WHERE deleted=0 AND name LIKE '%大%' AND version = 3 */ }

mybatisplus官网对条件构造器的描述也很详细可以去看看

本文由【江】发布于开源中国,原文链接:https://my.oschina.net/u/4152198/blog/3095538

全部评论: 0

    我有话说: