SpringBoot整合MybatisPlus3.X之Wrapper(五)

官方文档说明:

以下出现的第一个入参boolean condition表示该条件是否加入最后生成的sql中
以下代码块内的多个方法均为从上往下补全个别boolean类型的入参,默认为true
以下出现的泛型Param均为Wrapper的子类实例(均具有AbstractWrapper的所有方法)
以下方法在入参中出现的R为泛型,在普通wrapper中是String,在LambdaWrapper中是函数(例:Entity::getId,Entity为实体类,getId为字段id的getMethod)
以下方法入参中的R column均表示数据库字段,当R具体类型为String时则为数据库字段名(字段名是数据库关键字的自己用转义符包裹!)!而不是实体类数据字段名!!!,另当R具体类型为SFunction时项目runtime不支持eclipse自家的编译器!!!
以下举例均为使用普通wrapper,入参为Map和List的均以json形式表现!
使用中如果入参的Map或者List为空,则不会加入最后生成的sql中!!!

警告:

不支持以及不赞成在 RPC 调用中把 Wrapper 进行传输

wrapper 很重
传输 wrapper 可以类比为你的 controller 用 map 接收值(开发一时爽,维护火葬场)
正确的 RPC 调用姿势是写一个 DTO 进行传输,被调用方再根据 DTO 执行相应的操作
我们拒绝接受任何关于 RPC 传输 Wrapper 报错相关的 issue 甚至 pr

AbstractWrapper

说明:

QueryWrapper(LambdaQueryWrapper) 和 UpdateWrapper(LambdaUpdateWrapper) 的父类用于生成 sql 的 where 条件, entity 属性也用于生成 sql 的 where 条件注意: entity 生成的 where 条件与使用各个 api 生成的 where 条件没有任何关联行为

allEq

allEq(Map<R, V> params)
allEq(Map<R, V> params, boolean null2IsNull)
allEq(boolean condition, Map<R, V> params, boolean null2IsNull)

全部eq(或个别isNull)

个别参数说明:

params : key为数据库字段名,value为字段值 null2IsNull : 为true则在map的value为null时调用 isNull 方法,为false时则忽略value为null的

例1: allEq({id:1,name:"老王",age:null})--->id = 1 and name = '老王' and age is null
例2: allEq({id:1,name:"老王",age:null}, false)--->id = 1 and name = '老王'

allEq(BiPredicate<R, V> filter, Map<R, V> params)
allEq(BiPredicate<R, V> filter, Map<R, V> params, boolean null2IsNull)
allEq(boolean condition, BiPredicate<R, V> filter, Map<R, V> params, boolean null2IsNull)

个别参数说明:

filter : 过滤函数,是否允许字段传入比对条件中 params 与 null2IsNull : 同上

例1: allEq((k,v) -> k.indexOf("a") >= 0, {id:1,name:"老王",age:null})--->name = '老王' and age is null
例2: allEq((k,v) -> k.indexOf("a") >= 0, {id:1,name:"老王",age:null}, false)--->name = '老王'

eq

eq(R column, Object val)
eq(boolean condition, R column, Object val)

等于 =
例: eq("name", "老王")--->name = '老王'

ne

ne(R column, Object val)
ne(boolean condition, R column, Object val)

不等于 <>
例: ne("name", "老王")--->name <> '老王'

gt

gt(R column, Object val)
gt(boolean condition, R column, Object val)

大于 >
例: gt("age", 18)--->age > 18

ge

ge(R column, Object val)
ge(boolean condition, R column, Object val)

大于等于 >=
例: ge("age", 18)--->age >= 18

lt

lt(R column, Object val)
lt(boolean condition, R column, Object val)

小于 <
例: lt("age", 18)--->age < 18

le

le(R column, Object val)
le(boolean condition, R column, Object val)

小于等于 <=
例: le("age", 18)--->age <= 18

between

between(R column, Object val1, Object val2)
between(boolean condition, R column, Object val1, Object val2)

BETWEEN 值1 AND 值2
例: between("age", 18, 30)--->age between 18 and 30

notBetween

notBetween(R column, Object val1, Object val2)
notBetween(boolean condition, R column, Object val1, Object val2)

NOT BETWEEN 值1 AND 值2
例: notBetween("age", 18, 30)--->age not between 18 and 30

like

like(R column, Object val)
like(boolean condition, R column, Object val)

LIKE '%值%'
例: like("name", "王")--->name like '%王%'

notLike

notLike(R column, Object val)
notLike(boolean condition, R column, Object val)

NOT LIKE '%值%'
例: notLike("name", "王")--->name not like '%王%'

likeLeft

likeLeft(R column, Object val)
likeLeft(boolean condition, R column, Object val)

LIKE '%值'
例: likeLeft("name", "王")--->name like '%王'

likeRight

likeRight(R column, Object val)
likeRight(boolean condition, R column, Object val)

LIKE '值%'
例: likeRight("name", "王")--->name like '王%'

isNull

isNull(R column)
isNull(boolean condition, R column)

字段 IS NULL
例: isNull("name")--->name is null

isNotNull

isNotNull(R column)
isNotNull(boolean condition, R column)

字段 IS NOT NULL
例: isNotNull("name")--->name is not null

in

in(R column, Collection<?> value)
in(boolean condition, R column, Collection<?> value)

字段 IN (value.get(0), value.get(1), ...)
例: in("age",{1,2,3})--->age in (1,2,3)

in(R column, Object... values)
in(boolean condition, R column, Object... values)

字段 IN (v0, v1, ...)
例: in("age", 1, 2, 3)--->age in (1,2,3)

notIn

notIn(R column, Collection<?> value)
notIn(boolean condition, R column, Collection<?> value)

字段 IN (value.get(0), value.get(1), ...)
例: notIn("age",{1,2,3})--->age not in (1,2,3)

notIn(R column, Object... values)
notIn(boolean condition, R column, Object... values)

字段 NOT IN (v0, v1, ...)
例: notIn("age", 1, 2, 3)--->age not in (1,2,3)

inSql

inSql(R column, String inValue)
inSql(boolean condition, R column, String inValue)

字段 IN ( sql语句 )
例: 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)

notInSql

notInSql(R column, String inValue)
notInSql(boolean condition, R column, String inValue)

字段 NOT IN ( sql语句 )
例: notInSql("age", "1,2,3,4,5,6")--->age not in (1,2,3,4,5,6)
例: notInSql("id", "select id from table where id < 3")--->age not in (select id from table where id < 3)

groupBy

groupBy(R... columns)
groupBy(boolean condition, R... columns)

分组：GROUP BY 字段, ...
例: groupBy("id", "name")--->group by id,name

orderByAsc

orderByAsc(R... columns)
orderByAsc(boolean condition, R... columns)

排序：ORDER BY 字段, ... ASC
例: orderByAsc("id", "name")--->order by id ASC,name ASC

orderByDesc

orderByDesc(R... columns)
orderByDesc(boolean condition, R... columns)

排序：ORDER BY 字段, ... DESC
例: orderByDesc("id", "name")--->order by id DESC,name DESC

orderBy

orderBy(boolean condition, boolean isAsc, R... columns)

排序：ORDER BY 字段, ...
例: orderBy(true, true, "id", "name")--->order by id ASC,name ASC

having

having(String sqlHaving, Object... params)
having(boolean condition, String sqlHaving, Object... params)

HAVING ( sql语句 )
例: having("sum(age) > 10")--->having sum(age) > 10
例: having("sum(age) > {0}", 11)--->having sum(age) > 11

or

or()
or(boolean condition)

拼接 OR

注意事项:

主动调用or表示紧接着下一个方法不是用and连接!(不调用or则默认为使用and连接)

例: eq("id",1).or().eq("name","老王")--->id = 1 or name = '老王'

or(Function<Param, Param> func)
or(boolean condition, Function<Param, Param> func)

OR 嵌套
例: or(i -> i.eq("name", "李白").ne("status", "活着"))--->or (name = '李白' and status <> '活着')

and

and(Function<Param, Param> func)
and(boolean condition, Function<Param, Param> func)

AND 嵌套
例: and(i -> i.eq("name", "李白").ne("status", "活着"))--->and (name = '李白' and status <> '活着')

nested

nested(Function<Param, Param> func)
nested(boolean condition, Function<Param, Param> func)

正常嵌套不带 AND 或者 OR
例: nested(i -> i.eq("name", "李白").ne("status", "活着"))--->(name = '李白' and status <> '活着')

apply

apply(String applySql, Object... params)
apply(boolean condition, String applySql, Object... params)

拼接 sql

注意事项:

该方法可用于数据库函数动态入参的params对应前面applySql内部的{index}部分.这样是不会有sql注入风险的,反之会有!

例: apply("id = 1")--->id = 1
例: apply("date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")--->date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")
例: apply("date_format(dateColumn,'%Y-%m-%d') = {0}", "2008-08-08")--->date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")

last

last(String lastSql)
last(boolean condition, String lastSql)

无视优化规则直接拼接到 sql 的最后

注意事项:

只能调用一次,多次调用以最后一次为准有sql注入的风险,请谨慎使用

例: last("limit 1")

exists

exists(String existsSql)
exists(boolean condition, String existsSql)

拼接 EXISTS ( sql语句 )
例: exists("select id from table where age = 1")--->exists (select id from table where age = 1)

notExists

notExists(String notExistsSql)
notExists(boolean condition, String notExistsSql)

拼接 NOT EXISTS ( sql语句 )
例: notExists("select id from table where age = 1")--->not exists (select id from table where age = 1)

QueryWrapper

说明:

继承自 AbstractWrapper ,自身的内部属性 entity 也用于生成 where 条件及 LambdaQueryWrapper, 可以通过 new QueryWrapper().lambda() 方法获取

select

select(String... sqlSelect)
select(Predicate<TableFieldInfo> predicate)
select(Class<T> entityClass, Predicate<TableFieldInfo> predicate)

设置查询字段

说明:

以上方分法为两类. 第二类方法为:过滤查询字段(主键除外),入参不包含 class 的调用前需要wrapper内的entity属性有值! 这两类方法重复调用以最后一次为准

例: select("id", "name", "age")
例: select(i -> i.getProperty().startsWith("test"))

excludeColumns

@Deprecated

排除查询字段

已从3.0.5版本上移除此方法!

UpdateWrapper

说明:

继承自 AbstractWrapper ,自身的内部属性 entity 也用于生成 where 条件及 LambdaUpdateWrapper, 可以通过 new UpdateWrapper().lambda() 方法获取!

set

set(String column, Object val)
set(boolean condition, String column, Object val)

SQL SET 字段
例: set("name", "老李头")
例: set("name", "")--->数据库字段值变为空字符串
例: set("name", null)--->数据库字段值变为null

setSql

setSql(String sql)

设置 SET 部分 SQL
例: setSql("name = '老李头'")

lambda

获取 LambdaWrapper 在QueryWrapper中是获取LambdaQueryWrapper 在UpdateWrapper中是获取LambdaUpdateWrapper

使用 Wrapper 自定义SQL

需求来源:

在使用了mybatis-plus之后, 自定义SQL的同时也想使用Wrapper的便利应该怎么办？在mybatis-plus版本3.0.7得到了完美解决版本需要大于或等于3.0.7, 以下两种方案取其一即可

Service.java

mysqlMapper.getAll(Wrappers.<MysqlData>lambdaQuery().eq(MysqlData::getGroup, 1));

方案一注解方式 Mapper.java

@Select("select * from mysql_data ${ew.customSqlSegment}")
List<MysqlData> getAll(@Param(Constants.WRAPPER) Wrapper wrapper);

方案二 XML形式 Mapper.xml

<select id="getAll" resultType="MysqlData">
    SELECT * FROM mysql_data ${ew.customSqlSegment}
</select>

代码演示

pom.xml

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>

        <!-- for testing -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

application.yml

# DataSource Config
spring:
  datasource:
    driver-class-name: org.h2.Driver
    url: jdbc:h2:tcp://192.168.180.115:19200/~/mem/test
    username: root
    password: test

# Logger Config
logging:
  level:
    com.mp.wrapper: debug

实体类

@Data
public class Role {
    private Long id;
    private String roleName;
    private String roleDescribe;
}

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

Mapper

public interface RoleMapper extends BaseMapper<Role> {
}
public interface UserMapper extends BaseMapper<User> {
}

启动类

@SpringBootApplication
@MapperScan("com.mp.wrapper.mapper")
public class WrapperApplication {

    public static void main(String[] args) {
        SpringApplication.run(WrapperApplication.class, args);
    }

}

数据库脚本

DROP TABLE IF EXISTS user;
DROP TABLE IF EXISTS role;

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 '邮箱',
  role_id BIGINT (20) NOT NULL COMMENT '角色ID',
  PRIMARY KEY (id)
);

CREATE TABLE role
(
  id            BIGINT (20) NOT NULL COMMENT '主键ID',
  role_name     VARCHAR(30) NULL DEFAULT NULL COMMENT '角色名',
  role_describe VARCHAR(30) NULL DEFAULT NULL COMMENT '角色描述',
  PRIMARY KEY (id)
);

DELETE FROM user;
DELETE FROM role;

INSERT INTO role (id, role_name, role_describe)
VALUES (1, '管理员', 'boos 级别'),
       (2, '用户', '就是个普通人'),
       (3, '程序猿', '偶尔需要用来祭天');

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

测试类

@SpringBootTest
@RunWith(SpringRunner.class)
class WrapperApplicationTests {

    @Resource
    private UserMapper userMapper;
    @Resource
    private RoleMapper roleMapper;

    @Test
    public void tests() {
        System.out.println("----- 普通查询 ------");
        //SELECT id,role_id,name,email,age FROM user WHERE (role_id = ?)
        List<User> plainUsers = userMapper.selectList(new QueryWrapper<User>().eq("role_id", 2L));
        List<User> lambdaUsers = userMapper.selectList(new QueryWrapper<User>().lambda().eq(User::getRoleId, 2L));
        Assert.assertEquals(plainUsers.size(), lambdaUsers.size());
        print(plainUsers);

        System.out.println("----- 带子查询(sql注入) ------");
        //SELECT id,role_id,name,email,age FROM user WHERE (role_id IN (select id from role where id = 2))
        List<User> plainUsers2 = userMapper.selectList(new QueryWrapper<User>().eq("1","1")
                .inSql("role_id", "select id from role where id = 2"));
        List<User> lambdaUsers2 = userMapper.selectList(new QueryWrapper<User>().lambda()
                .inSql(User::getRoleId, "select id from role where id = 2"));
        Assert.assertEquals(plainUsers2.size(), lambdaUsers2.size());
        print(plainUsers2);

        System.out.println("----- 带嵌套查询 ------");
        //SELECT id,role_id,name,email,age FROM user WHERE ( (role_id = ? OR role_id = ?) ) AND ( (age >= ?) )
        List<User> plainUsers3 = userMapper.selectList(new QueryWrapper<User>()
                .nested(i -> i.eq("role_id", 2L).or().eq("role_id", 3L))
                .and(i -> i.ge("age", 20)));
        List<User> lambdaUsers3 = userMapper.selectList(new QueryWrapper<User>().lambda()
                .nested(i -> i.eq(User::getRoleId, 2L).or().eq(User::getRoleId, 3L))
                .and(i -> i.ge(User::getAge, 20)));
        Assert.assertEquals(plainUsers3.size(), lambdaUsers3.size());
        print(plainUsers3);

        System.out.println("----- 自定义(sql注入) ------");
        // SELECT id,role_id,name,email,age FROM user WHERE (role_id = 2)
        List<User> plainUsers4 = userMapper.selectList(new QueryWrapper<User>()
                .apply("role_id = 2"));
        print(plainUsers4);

        //UPDATE user SET email=? WHERE (id = ?)
        UpdateWrapper<User> uw = new UpdateWrapper<>();
        uw.set("email", null);
        uw.eq("id", 4);
        userMapper.update(new User(), uw);
        User u4 = userMapper.selectById(4);
        Assert.assertNull(u4.getEmail());


    }

    @Test
    public void lambdaQueryWrapper() {
        System.out.println("----- 普通查询 ------");
        List<User> plainUsers = userMapper.selectList(new LambdaQueryWrapper<User>().eq(User::getRoleId, 2L));
        List<User> lambdaUsers = userMapper.selectList(new QueryWrapper<User>().lambda().eq(User::getRoleId, 2L));
        Assert.assertEquals(plainUsers.size(), lambdaUsers.size());
        print(plainUsers);

        System.out.println("----- 带子查询(sql注入) ------");
        List<User> plainUsers2 = userMapper.selectList(new LambdaQueryWrapper<User>()
                .inSql(User::getRoleId, "select id from role where id = 2"));
        List<User> lambdaUsers2 = userMapper.selectList(new QueryWrapper<User>().lambda()
                .inSql(User::getRoleId, "select id from role where id = 2"));
        Assert.assertEquals(plainUsers2.size(), lambdaUsers2.size());
        print(plainUsers2);

        System.out.println("----- 带嵌套查询 ------");
        List<User> plainUsers3 = userMapper.selectList(new LambdaQueryWrapper<User>()
                .nested(i -> i.eq(User::getRoleId, 2L).or().eq(User::getRoleId, 3L))
                .and(i -> i.ge(User::getAge, 20)));
        List<User> lambdaUsers3 = userMapper.selectList(new QueryWrapper<User>().lambda()
                .nested(i -> i.eq(User::getRoleId, 2L).or().eq(User::getRoleId, 3L))
                .and(i -> i.ge(User::getAge, 20)));
        Assert.assertEquals(plainUsers3.size(), lambdaUsers3.size());
        print(plainUsers3);

        System.out.println("----- 自定义(sql注入) ------");
        List<User> plainUsers4 = userMapper.selectList(new QueryWrapper<User>()
                .apply("role_id = 2"));
        print(plainUsers4);

        UpdateWrapper<User> uw = new UpdateWrapper<>();
        uw.set("email", null);
        uw.eq("id", 4);
        userMapper.update(new User(), uw);
        User u4 = userMapper.selectById(4);
        Assert.assertNull(u4.getEmail());
    }

    private <T> void print(List<T> list) {
        if (!CollectionUtils.isEmpty(list)) {
            list.forEach(System.out::println);
        }
    }

    /**
     * SELECT id,name,age,email,role_id FROM user
     * WHERE ( 1 = 1 ) AND ( ( name = ? AND age = ? ) OR ( name = ? AND age = ? ) )
     */
    @Test
    public void testSql() {
        QueryWrapper<User> w = new QueryWrapper<>();
        w.and(i -> i.eq("1", 1))
                .nested(i ->
                        i.and(j -> j.eq("name", "a").eq("age", 2))
                                .or(j -> j.eq("name", "b").eq("age", 2)));
        userMapper.selectList(w);
    }
}

posted @ 2019-10-30 14:29 天宇轩-王阅读(2952) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

天宇轩-王

SpringBoot整合MybatisPlus3.X之Wrapper(五)

官方文档说明:

AbstractWrapper

allEq

eq

ne

gt

ge

lt

le

between

notBetween

like

notLike

likeLeft

likeRight

isNull

isNotNull

in

notIn

inSql

notInSql

groupBy

orderByAsc

orderByDesc

orderBy

having

or

and

nested

apply

last

exists

notExists

QueryWrapper

select

excludeColumns

@Deprecated

UpdateWrapper

set

setSql

lambda

使用 Wrapper 自定义SQL

Service.java

方案一 注解方式 Mapper.java

方案二 XML形式 Mapper.xml

代码演示

公告

方案一注解方式 Mapper.java