【mybatis-plus

Mp can also be convenient to achieve a slightly complex point of conditional query, of course, very complex or XML to write SQL.

First, wapper introduction

Let’s take a look at the structure of mp conditional construction abstract class:

Wrapper: Condition constructs the abstract class, the topmost parent class
AbstractWrapper: Used to encapsulate query conditions and generate SQL WHERE conditions
QueryWrapperThe: Entity object encapsulates the action class, not in lambda syntax
UpdateWrapper: Update Conditional encapsulation for Entity object Update operations
AbstractLambdaWrapperLambda syntax uses Wrapper to uniformly handle parsing Lambda to retrieve database fields
LambdaQueryWrapper: query Wrapper for Lambda syntax use
LambdaUpdateWrapperLambda updates the Wrapper

But the most common ones are QueryWrapper, UpdateWrapper, etc. Again, create the QueryWrapper object and then call the various methods.

@test void testQueryWrapper() {// create an object, add an entity QueryWrapper<User> wrapperUser = new QueryWrapper<>(); Wrapperuser. ge("age", 40); wrapperuser. ge("age", 40); // Call the query method and pass in the Wrapper object List<User> Users = usermapper.selectList (wrapperUser); System.out.println(users); }Copy the code

Select * from age>=40 where age>=40;

2. Common conditional methods

In addition to the above ge, there are many other methods for constructing conditions. Here is a brief introduction to the more common ones and post the SQL executed.

1. `gt`said`>`

. . Wrapperuser. gt("age", 40); . .Copy the code

SQL:

==>  Preparing: SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 AND age > ? 
==> Parameters: 40(Integer)
Copy the code

2. `le`said`< =`

. . Wrapperuser. le("age", 40); wrapperuser. le("age", 40); . .Copy the code

SQL:

==>  Preparing: SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 AND age <= ? 
==> Parameters: 40(Integer)
Copy the code

3. `lt`said`<`

. . Wrapperuser. lt("age", 40); . .Copy the code

SQL:

==>  Preparing: SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 AND age < ? 
==> Parameters: 40(Integer)
Copy the code

4. `isNull`said`The query value is NULL`

. . // isNull wrapperUser.isNull("name"); . .Copy the code

SQL:

==>  Preparing: SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 AND name IS NULL 
==> Parameters: 
<==      Total: 0
Copy the code

5. `isNotNull`said`The query value is not null`

. . // isNotNull wrapperUser.isNotNull("name"); . .Copy the code

SQL:

==>  Preparing: SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 AND name IS NOT NULL 
==> Parameters:
Copy the code

6. `eq`said`=`

        ... ...
        // eq
        wrapperUser.eq("name", "大周4");
        ... ...
Copy the code

SQL:

==> Preparing: SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 AND name = ? ==> Parameters: 4(String)Copy the code

7. `ne`said`! =`

        ... ...
        // eq
        wrapperUser.ne("name", "大周4");
        ... ...
Copy the code

SQL:

==> Preparing: SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 AND name <> ? ==> Parameters: 4(String)Copy the code

8. `between`Contains boundary values between ranges

. . // between wrapperUser.between("age", 40, 50); . .Copy the code

SQL:

==>  Preparing: SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 AND age BETWEEN ? AND ? 
==> Parameters: 40(Integer), 50(Integer)
Copy the code

9. `notBetween`Represents out of range, without boundary values

. . // between wrapperUser.notBetween("age", 40, 50); . .Copy the code

SQL:

==>  Preparing: SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 AND age NOT BETWEEN ? AND ? 
==> Parameters: 40(Integer), 50(Integer)
Copy the code

10. `notBetween`Represents out of range, without boundary values

. . // between wrapperUser.notBetween("age", 40, 50); . .Copy the code

SQL:

==>  Preparing: SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 AND age NOT BETWEEN ? AND ? 
==> Parameters: 40(Integer), 50(Integer)
Copy the code

11. `allEq`Multi-condition query

If I want to add more conditions after where, I can use allEq. Create a HashMap, put the conditions in it, and call allEq.

    @Test
    void testQueryWrapper() {
        QueryWrapper<User> wrapperUser = new QueryWrapper<>();
        Map<String, Object> map = new HashMap<>();
        map.put("id", 5);
        map.put("name", "wesson5");
        map.put("age", 29);
        wrapperUser.allEq(map);
        List<User> users = userMapper.selectList(wrapperUser);
        System.out.println(users);

    }
Copy the code

SQL:

==>  Preparing: SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 AND name = ? AND id = ? AND age = ? 
==> Parameters: wesson5(String), 5(Integer), 29(Integer)
Copy the code

12. `.`Chain programming, multi – condition query

In addition, you can use chained programming to continue directly later. Call another method.

@test void testQueryWrapper() {// Create an object with an entity QueryWrapper<User> wrapperUser = new QueryWrapper<>(); wrapperUser.eq("age", 29) .eq("name", "wesson5") .eq("id", 5); List<User> users = userMapper.selectList(wrapperUser); System.out.println(users); }Copy the code

SQL:

==>  Preparing: SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 AND age = ? AND name = ? AND id = ? 
==> Parameters: 29(Integer), wesson5(String), 5(Integer)
Copy the code

13. Or, the and

By default, and() is used when the or() method is not invoked.

@test void testQueryWrapper() {// Create an object with an entity QueryWrapper<User> wrapperUser = new QueryWrapper<>(); wrapperUser.eq("age", 29) .or() .eq("name", "wesson5") .or() .eq("id", 5); List<User> users = userMapper.selectList(wrapperUser); System.out.println(users); }Copy the code

SQL:

==>  Preparing: SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 AND age = ? OR name = ? OR id = ? 
==> Parameters: 29(Integer), wesson5(String), 5(Integer)
Copy the code

14. Nested OR, nested and

SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL

@test void testQueryWrapper() {// Create an object with an entity QueryWrapper<User> wrapperUser = new QueryWrapper<>(); wrapperUser.eq("age", 29) .or( i -> i.eq("name", "wesson5") .or() .eq("id", 5) ); List<User> users = userMapper.selectList(wrapperUser); System.out.println(users); }Copy the code

SQL:

==>  Preparing: SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 AND age = ? OR ( name = ? OR id = ? ) 
==> Parameters: 29(Integer), wesson5(String), 5(Integer)
Copy the code

15., notIn in

Is equal to in and not in in in SQL.

@test void testQueryWrapper() {// Create an object with an entity QueryWrapper<User> wrapperUser = new QueryWrapper<>(); wrapperUser.in("id", 1, 2, 3); List<User> users = userMapper.selectList(wrapperUser); System.out.println(users); }Copy the code

SQL:

==> Preparing: SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 AND id IN (? ,? ,?) ==> Parameters: 1(Integer), 2(Integer), 3(Integer)Copy the code

16. InSql, notinSql

InSql, notinSql can be used for sub-query, such as where ID in (select *… …).

@test void testQueryWrapper() {// Create an object with an entity QueryWrapper<User> wrapperUser = new QueryWrapper<>(); wrapperUser.in("id", "select id from user where id < 5"); List<User> users = userMapper.selectList(wrapperUser); System.out.println(users); }Copy the code

SQL:

==>  Preparing: SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 AND id IN (?) 
==> Parameters: select id from user where id < '5'(String)
Copy the code

17. last

Last can directly concatenate SQL to the end, and can only be called once. Note: There is a risk of SQL injection, use with caution.

@test void testQueryWrapper() {// Create an object with an entity QueryWrapper<User> wrapperUser = new QueryWrapper<>(); wrapperUser.last("limit 1"); List<User> users = userMapper.selectList(wrapperUser); System.out.println(users); }Copy the code

SQL:

==>  Preparing: SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 limit 1 
==> Parameters: 
Copy the code

18. Specify the columns to be queried

Query only the specified fields, such as “ID “, “name”, “age”.

@test void testQueryWrapper() {// Create an object with an entity QueryWrapper<User> wrapperUser = new QueryWrapper<>(); wrapperUser.select("id", "name", "age"); List<User> users = userMapper.selectList(wrapperUser); System.out.println(users); }Copy the code

SQL:

==>  Preparing: SELECT id,name,age FROM user WHERE deleted=0 
==> Parameters: 
Copy the code

These are some of the more complex conditional queries commonly used in business development, and there may be other combinations of variations.

First, wapper introduction

2. Common conditional methods

1. gtsaid>

2. lesaid< =

3. ltsaid<

4. isNullsaidThe query value is NULL

5. isNotNullsaidThe query value is not null

6. eqsaid=

7. nesaid! =

8. betweenContains boundary values between ranges

9. notBetweenRepresents out of range, without boundary values

10. notBetweenRepresents out of range, without boundary values

11. allEqMulti-condition query

12. .Chain programming, multi – condition query

13. Or, the and

14. Nested OR, nested and

15., notIn in

16. InSql, notinSql

17. last

18. Specify the columns to be queried

Related Posts

2018 Internet love survey report: Programmer is the most stable, product manager X life frequency is extremely high

500 data per page render optimization ideas

Weblogic installation and deployment details

1. `gt`said`>`

2. `le`said`< =`

3. `lt`said`<`

4. `isNull`said`The query value is NULL`

5. `isNotNull`said`The query value is not null`

6. `eq`said`=`

7. `ne`said`! =`

8. `between`Contains boundary values between ranges

9. `notBetween`Represents out of range, without boundary values

10. `notBetween`Represents out of range, without boundary values

11. `allEq`Multi-condition query

12. `.`Chain programming, multi – condition query