MyBatis - Plus actual combat - Moment For Technology

Environment to prepare

To build libraries built table

Create a user tableCREATE TABLE user (
    id BIGINT(20) PRIMARY KEY NOT NULL COMMENT 'primary key',
    name VARCHAR(30) DEFAULT NULL COMMENT 'name',
    age INT(11) DEFAULT NULL COMMENT 'age',
    email VARCHAR(50) DEFAULT NULL COMMENT 'email',
    manager_id BIGINT(20) DEFAULT NULL COMMENT 'Immediate superior ID',
    create_time DATETIME DEFAULT NULL COMMENT 'Creation time',
	update_time DATETIME DEFAULT NULL COMMENT 'Modification time',
	version INT(11) DEFAULT '1' COMMENT 'version',
	deleted INT(1) DEFAULT '0' COMMENT 'Logical deletion identifier (0. Not deleted,1. Deleted)'.CONSTRAINT manager_fk FOREIGN KEY (manager_id)
        REFERENCES user (id)
)  ENGINE=INNODB CHARSET=UTF8; Initialize data:INSERT INTO user (id, name, age, email, manager_id, create_time)
VALUES 
(1.'Helpless and miserable'.40.'[email protected]'.NULL.'the 2019-11-23 14:20:20'),
(2.'Funny Beam'.25.'[email protected]'.1.'the 2019-10-01 11:12:22'),
(3.'Meili Sun'.28.'[email protected]'.1.'the 2019-09-28 08:31:16'),
(4.'Old Wang next door'.25.'[email protected]'.6.'the 2019-10-01 09:15:15'),
(5.'Be honest'.30.'[email protected]'.6.'the 2019-01-01 09:48:16'),
(6.'Cheng Xu 妧'.30.'[email protected]'.1.'the 2019-01-01 09:48:17');
Copy the code

Introduction of depend on

Based on Springboot development.

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.2.0</version>
</dependency>
<dependency>
  <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
<! -- Use Lombok to simplify development, need to install lombok plugin -->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>
Copy the code

Entity class

import lombok.Data;
import java.time.LocalDateTime;
/ * * *@author CatWing
 */
@Data
public class User {
    /** * primary key */
    private Long id;
    /** * name */
    private String name;
    /** * age */
    private Integer age;
    /** * email */
    private String email;
    /** * Superior ID */
    private Long managerId;
    /** * create time */
    private LocalDateTime createTime;
    /** * update time */
    private LocalDateTime updateTime;
    /** * Version number */
    private Integer version;
    /** * Logical delete (0. Not deleted,1. Deleted) */
    private Integer deleted;
}
Copy the code

Mapper interfaces

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import edu.xpu.hcp.entity.User;

public interface UserMapper{}Copy the code

configuration

Configure database drivers, urls, and so on.

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/databaseName? useSSL=false&serverTimezone=GMT%2B8&characterEncoding=utf-8
    username: name
    password: pwd

logging:
  level:
    root: warn
    Package name: trace

mybatis-plus:
  type-aliases-package: Entity class package name
  mapper-locations: classpath:mapper/*.xml
Copy the code

Configure the @Mapperscan annotation on the startup class to scan the Mapper interface.

@SpringBootApplication
@MapperScan(basePackageClasses = {UserMapper.class})
public class MybatisPlusDemoApplication {... }Copy the code

A profound

MyBatis-Plus provides developers with a common CRUD approach that saves us from developing a lot of simple, boring code. Using these generic methods is as simple as having the Mapper interface inherit from BaseMapper

.

public interface UserMapper extends BaseMapper<User> {}Copy the code

The general Mapper provides many methods, now use the selectList method to query all data!

@RunWith(SpringRunner.class)
@SpringBootTest
public class MybatisPlusDemoApplicationTests {

    @Autowired
    private UserMapper userMapper;

    @Test
    public void selectAll(a) {
        System.out.println(("----- test selectAll method ------"));
        List<User> userList = userMapper.selectList(null);
        Assert.assertEquals(5, userList.size()); userList.forEach(System.out::println); }}Copy the code

BaseMapper provides the following methods, all of which will be described later.

int insert(T entity);// Insert a record
int deleteById(Serializable id);// Delete by ID
int deleteByMap(@Param(Constants.COLUMN_MAP) Map<String, Object> columnMap);// Delete the record according to the columnMap condition
int delete(@Param(Constants.WRAPPER) Wrapper<T> wrapper);// Delete the record according to the entity condition
int deleteBatchIds(@Param(Constants.COLLECTION) Collection<? extends Serializable> idList);// Delete (delete by ID)
int updateById(@Param(Constants.ENTITY) T entity);// Change according to ID
int update(@Param(Constants.ENTITY) T entity, @Param(Constants.WRAPPER) Wrapper<T> updateWrapper);// Update the record according to the whereEntity condition
T selectById(Serializable id);// Query by ID
List<T> selectBatchIds(@Param(Constants.COLLECTION) Collection<? extends Serializable> idList);// query (by ID)
List<T> selectByMap(@Param(Constants.COLUMN_MAP) Map<String, Object> columnMap);// Query (based on columnMap condition)
T selectOne(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);// Query a record based on the entity condition
Integer selectCount(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);// Query the total number of records according to the Wrapper condition
List<T> selectList(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);// Query all records according to entity condition
List<Map<String, Object>> selectMaps(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);// Query all records according to the Wrapper condition
List<Object> selectObjs(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);// Query all records according to the Wrapper condition
IPage<T> selectPage(IPage<T> page, @Param(Constants.WRAPPER) Wrapper<T> queryWrapper);// Query all records according to entity condition (and turn the page)
IPage<Map<String, Object>> selectMapsPage(IPage<T> page, @Param(Constants.WRAPPER) Wrapper<T> queryWrapper);// Query all records according to the Wrapper condition (and flip the page)
Copy the code

From traditional crafts to MP

Traditional craft

Now the MP

CRUD of actual combat

insert

@Test
public void insertUser(a){
    User user = new User();
    user.setId(6L);
    user.setName("Cheng Xuyuan");
    user.setAge(20);
    user.setEmail("[email protected]");
    user.setManagerId(1L);
    user.setCreateTime(LocalDateTime.now());
    int rows = userMapper.insert(user);
    System.out.println("affect rows : "+rows);
}
Copy the code

Looking at the log, we can find that the detailed SQL statement was inserted. Because the user object did not set updateTime and other attributes, it did not appear in the column name of the inserted statement. This is the result of MP processing. And we can see that the way the MP default database uses underscores to separate words corresponds to hump names in entities.

The 2019-11-23 20:47:27. 59020-886 the DEBUG [main] edu. Xpu. HCP. Mapper. UserMapper. Insert: = = > Preparing: INSERT INTO user ( id, create_time, name, manager_id, email, age ) VALUES ( ? ,? ,? ,? ,? ,?)Copy the code

Insert an object again, except that the id is not set, and view the log.

@Test
public void insertUser(a){
    User user = new User();
    user.setName("Cheng Xu");
    user.setAge(23);
    user.setEmail("[email protected]");
    user.setManagerId(2L);
    user.setCreateTime(LocalDateTime.now());
    int rows = userMapper.insert(user);
    System.out.println("affect rows : "+rows);
}
Copy the code

If the column name is not set, the column name will not appear in the statement. I don’t know how that came up. This is because MP fills the ID attribute by default with an auto-increment ID based on the snowflake algorithm.

The 2019-11-23 20:51:50. 58588-047 the DEBUG [main] edu. Xpu. HCP. Mapper. UserMapper. Insert: = = > Preparing: INSERT INTO user ( id, create_time, name, manager_id, email, age ) VALUES ( ? ,? ,? ,? ,? ,?) The 2019-11-23 20:51:50. 58588-072 the DEBUG [main] edu. Xpu. HCP. Mapper. UserMapper. Insert: = = > the Parameters: 1198222622966423554(Long), 2019-11-23t20:51:49.424 (LocalDateTime), 2(Long), [email protected](String), 23(Integer)Copy the code

Commonly used annotations

@TableName

In general, entity names and database representations correspond according to the hump naming conventions, such as class User to table User and class SysUser to table sys_user. Sometimes we might need to change the database name (or class name), but what if the default naming rules don’t match, and we don’t want to change the code anymore? MP provides the @tablename annotation to specify mappings between classes and tables.

@TableName("sys_user")
public class User {... }Copy the code

@TableId

We did not specify an ID value in the test insert above, but the default rule of MP fills in the primary key ID value for us automatically. Note that this only works with the primary key name ID in the entity class. If you name the ID like userId (the database list is user_id), then MP will not be able to automatically populate it for us. If we don’t want to change it to ID, but we really need to name it something else, shouldn’t we? Luckly! MP provides the @tableID annotation to specify the primary key in the entity.

/** * primary key */
@TableId
private Long userId;
Copy the code

Similar to the @tablename function, column and entity names can be different and can be specified.

/** * primary key,userId corresponds to database column name id */
@TableId(value = "id")
private Long userId;
Copy the code

TableId meaning

attribute	type	required	The default value	meaning
value	String	no	“”	Field name of the database
type	Enum	no	IdType.NONE	The primary key type

IdType meaning

value	meaning
AUTO	Database increment
INPUT	On its own input
ID_WORKER	Distributed globally unique ID Long integer type
UUID	A 32-bit UUID character string
NONE	Primary key type not set (will follow global)
ID_WORKER_STR	Distributed globally unique ID string type

@TableField

@tableField is used for non-primary key fields, and like @tablename we can specify the value of the database field name corresponding to the property name.

/**
 * 姓名
 */
@TableField(value = "user_name")
private String name;
Copy the code

Exclude non-table fields

In real programming scenarios, attribute names in entity classes do not always exist in database tables. Let’s add the note attribute to the User class.

/** * Remarks (non-table fields) */
private String note;
Copy the code

Execute the selectAll method in the test knife. Because MP maps attributes and fields one by one, the newly created note attribute is not in the user table field, so an error must be reported.

org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: Unknown column 'note' in 'field list'
Copy the code

To do this, we need to exclude these non-table fields. MP provides three ways to exclude non-table fields:

Use the TRANSIENT keyword

/** * Remarks (non-table fields) */
private transient String note;
Copy the code

Labeled as static variables

/** * Remarks (non-table fields) */
private static String note;
Copy the code

Since the Lombok plug-in does not create getters and setters for static variables, we need to create them manually.

Exist properties

Use the exist property in the @TableField annotation.

/** * Remarks (non-table fields) */
 @TableField(exist=false)
private String note;
Copy the code

The query

A simple query

/** * Query by id */
@Test
public void testSelectById(a){
    User user = userMapper.selectById(1L);
    System.out.println(user);
}
/** * Query by id set */
@Test
public void testSelectBatchIds(a){
    /* The collection passed by the selectBatchIds method cannot be null or empty */
    List<User> users = userMapper.selectBatchIds(Arrays.asList(1L.2L.3L.4L));
    users.forEach(System.out::println);
}
/** * Use map to query */
@Test
public void testSelectByMap(a){
    Map<String,Object> map = new HashMap<>();
    map.put("name"."Beautiful Sun");
    map.put("age".18);
    /** * note that map is a table field map object, so key represents the database field name, not the entity attribute name */
    List<User> users = userMapper.selectByMap(map);
    SQL:SELECT ID,deleted,create_time,name,update_time,manager_id,version,email,age FROM user WHERE name =? AND age = ? * /
    users.forEach(System.out::println);
}
Copy the code

Conditions of the query

Before using conditional queries, we need to know the conditional constructor, which is the class used to build the query conditions. Abstract class Wrapper is the parent of all conditional constructors. We first use its subclass QueryWrapper to construct the conditions. The conditional constructor will be studied based on a series of problem requirements.

The name contains sun and is younger than 20

@Test
public void test1(a){
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    /** * LIKE '% value %' * LIKE (R column, Object val) * column: Use database field names instead of entity attribute names, as well as other methods. * /
    queryWrapper.like("name"."Sun").lt("age".20);
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}
Copy the code

The name contains sun and the age is greater than or equal to 10 but less than or equal to 25 and the email is not empty

@Test
public void test2(a){
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.like("name"."Sun").between("age".10.25).isNotNull("email");
    / / an: queryWrapper) like (" name ", "sun"). Ge (" age ", 10). Le (" age ", 25). IsNotNull (" email ");
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}
Copy the code

If your name is Cheng or your age is greater than or equal to 25, you will be listed in descending order of age. If your age is the same, you will be listed in ascending order of ID

@Test
public void test3(a){
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    /**
     * like: '%值%'
     * likeLeft: '%值'
     * lifeRight: '值%'
     */
    queryWrapper.likeRight("name"."Cheng").or().ge("age".25)
            .orderBy(true.false."age")
            .orderBy(true.true."id");
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}
Copy the code

The date of establishment is October 1, 2019, and the name of the immediate superior is Surname Cheng

@Test
public void test4(a){
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    /** * apply(String applySql, Object... The params) * apply method can be used for database functions to dynamically enter parameters in the params corresponding to the {index} section inside the previous applySql. There is no SQL injection risk, there is. * apply (" date_format (create_time, 'Y - m - % d % %) =' 2019-10-01 '") will be * / SQL injection risk
    queryWrapper.apply("date_format(create_time,'%Y-%m-%d') = {0}"."2019-10-01").inSql("manager_id"."Select id from user where name = '%'");
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}
Copy the code

Name is Cheng’s surname and (age less than 30 or mailbox is not empty)

@Test
public void test5(a){
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.likeRight("name"."Cheng").and(qw->qw.lt("age".30).or().isNotNull("email"));
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}
Copy the code

The name is Cheng or (younger than 40 and older than 20 and the mailbox is not empty)

@Test
public void test6(a){
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.likeRight("name"."Cheng").or(qw->qw.lt("age".40).gt("age".20).isNotNull("email"));
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}
Copy the code

(the age is less than 40 or the mailbox is not empty) and the first name is Cheng

@Test
public void test7(a){
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    /** * nested */
    queryWrapper.nested(qw->qw.lt("age".40).or().isNotNull("email")).likeRight("name"."Cheng");
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}
Copy the code

The ages are 18, 20, 25, 30

@Test
public void test8(a){
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.in("age",Arrays.asList(18.20.25.30));
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}
Copy the code

Return only one of the statements that meets the criteria

@Test
public void test9(a){
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    / * * * last methods ignore the optimization rules directly joining together to the end of the SQL * note that can only be called once, multiple calls will be subject to the last time, and there is the risk of SQL injection, prudent use * /
    queryWrapper.in("age",Arrays.asList(18.20.25.30)).last("limit 1");
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}
Copy the code

All fields in select do not appear

In the above test, it is not difficult to find that all fields are queried out, but in many cases we only need part of the field.

Name contains sun and age less than 20(only id,name fields required)

@Test
public void test10(a){
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.select("id"."name").like("name"."Sun").lt("age".20);
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}
Copy the code

Name contains sun and is younger than 20(age,email field excluded)

@Test
public void test11(a){
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    /**
     * euqals中填写的是列名
     */queryWrapper.select(User.class,user->! user.getColumn().equals("age") &&! user.getColumn().equals("email"));
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}
Copy the code

The effect of condition

If we look closely, we can see that all methods call another method of the same name, except that the first argument to the other method is Boolean condition, which indicates whether the condition is added to the last generated SQL. If set to false, the condition is not included in the last generated code. Usage scenario: In multi-condition query, the SQL can be generated based on whether to pass the value of this attribute.

@Test
public void test12(a){
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    / * * * queryWrapper likeRight (" name ", "path"). Gt (" age ", 10); WHERE (name LIKE? AND age > ?) * queryWrapper. LikeRight (false, "name", "path"). The gt (" age ", 10); WHERE (age >?) * /
    queryWrapper.likeRight(false."name"."Cheng").gt("age".10);
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}
Copy the code

Entities as arguments to the conditional constructor constructor method

In the condition constructor used above, we used the no-argument constructor. MP allows us to pass entity objects to the condition constructor, where non-null attributes of entity objects are used as WHERE conditions, using equivalent comparators by default. Note: The WHERE condition generated by entity has no behavior associated with the WHERE condition generated using the various apis. All appear in where

@Test
public void test13(a){
    User user = new User();
    user.setName("Be honest.");
    user.setAge(18);
    QueryWrapper<User> queryWrapper = new QueryWrapper<>(user);
    /** * WHERE name=? AND age=? * Add queryWrapper. LikeRight ("name"," process "). Gt ("age",10); WHERE name=? AND age=? AND (name LIKE ? AND age > ?) * /
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}
Copy the code

By default, the where condition passed in to the entity object construction is equal to the value. This is not always the case. For example, we want to use like for the name attribute. Now we need to use the @TableField annotation again, annotating @tableField on name (condition = sqlcondition.like). Change the default comparison conditions. WHERE name LIKE CONCAT(‘%’,? ,’%’) AND age=? .

/** * SQL comparison conditional constant definition class */
public class SqlCondition {
    /** * is equal to */
    public static final String EQUAL = "%s=#{%s}";
    /** ** does not equal */
    public static final String NOT_EQUAL = "%s< > #{%s}";
    /** * % on both sides % */
    public static final String LIKE = "%s LIKE CONCAT('%%',#{%s},'%%')";
    /** * % left */
    public static final String LIKE_LEFT = "%s LIKE CONCAT('%%',#{%s})";
    /** * right % */
    public static final String LIKE_RIGHT = "%s LIKE CONCAT(#{%s},'%%')";
}
Copy the code

If these conditions do not meet our requirements, write your own! It’s just a String.

allEq

The first kind ofallEqMethods:

allEq(Map<R, V> params)
allEq(Map<R, V> params, boolean null2IsNull)
allEq(boolean condition, Map<R, V> params, boolean null2IsNull)
/** * params: key = database field name,value = database field value ** null2IsNull: isNull is invoked when map value isNull; false: ignores null field */
Copy the code

@Test
public void test14(a){
    Map<String,Object> map = new HashMap<>();
    map.put("name"."Be honest.");
    map.put("manager_id".6);
    map.put("age".null);
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    /** * WHERE (manager_id =? AND name = ? AllEq (map,false) AND age IS NULL) * allEq(map,false); * WHERE (manager_id = ? AND name = ?) * /
    queryWrapper.allEq(map,false);
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}
Copy the code

The second,allEqMethods:

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) 
Params and null2IsNull: same as above */
Copy the code

@Test
public void test15(a){
    Map<String,Object> map = new HashMap<>();
    map.put("name"."Be honest.");
    map.put("manager_id".6);
    map.put("age".null);
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    AllEq ((k,v)->! k.equals("name"),map,false); * WHERE (manager_id = ?) * * /
    queryWrapper.allEq((k,v)->k.equals("name"),map,false);
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}
Copy the code

Other query methods

selectMaps

The objects queried by this method are encapsulated by map.

@Test
public void test16(a){
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    List<Map<String, Object>> users = userMapper.selectMaps(queryWrapper);
    System.out.println(users);
}
/** * Query result: * [* {deleted=0, create_time=2019-11-23 14:20:20.0, id=1, version=1, [email protected], age=3}, * {deleted=0, create_time=2019-10-01 11:12:22.0, manager_id=1, name= id=2, version=1, [email protected], age=108}, * {deleted=0, create_time=2019-10-01 11:12:22.0, Manager_id =1, name= id=2, version=1, [email protected], age=108}, * {deleted=0, create_time=2019-09-28 08:31:16.0, manager_id=1, name= mysun, ID =3, version=1, [email protected], age=18}, * {deleted=0, create_time=2019-10-01 09:15:15.0, manager_id=6, name= BJX, id=4, version=1, [email protected], Age =25}, * {deleted=0, create_time=2019-01-01 09:48:16.0, manager_id=6, name= id, id=5, version=1, Email [email protected], age=30}, * {deleted=0, create_time=2019-11-23 21:27:14.0, Manager_id =2, name= chenxu 妧, id=6, version=1, [email protected], age=30} * ] */
Copy the code

It’s the same as using the selectList method. No, there is no null attribute in the map, so that’s how it’s used. When we use selectList, we can find that many of the attributes in the result are null, which is unsightly. This is where we need to introduce selectMaps.

@Test
public void test17(a){
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.select("id"."name");
    List<Map<String, Object>> users = userMapper.selectMaps(queryWrapper);
    System.out.println(users);
    /** * Query result: * [* {name = helpless pain hu, id = 1}, * {name = funny beam, id = 2}, * {name = beautiful sun, id = 3}, * {name = old wang next door, id = 4}, * {name = honesty, id = 5}, * {name = Cheng Xu 妧, id=6} * ] */
}
Copy the code

Eliminating an attribute with a value of NULL is just one use scenario for this method. Q: What if there is no corresponding entity class for the result of the query? It can’t be rebuilt! This is another use scenario for selectMaps.

@Test
public void test18(a){
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.select("avg(age) avg_age"."min(age) min_age"."max(age) max_age")
            .groupBy("manager_id").having("sum(age) < {0}".500);
    List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);
    System.out.println(maps);
    / [* * * * {max_age = 3, avg_age = 3.0000, min_age = 3}, * {max_age = 108, avg_age = 63.0000, min_age = 18}, * {max_age = 30. Avg_age = 30.0000, min_age = 30}, {max_age = 30 avg_age = 27.5000, min_age = 25} *] * /
}
Copy the code

selectObjs

Query all records based on the Wrapper condition. Note: Only the value of the first field is returned.

@Test
public void test19(a){
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    List<Object> objects = userMapper.selectObjs(queryWrapper);
    System.out.println(objects);
    /** * returns only the value of the first column id * [1, 2, 3, 4, 5, 6] */
}
Copy the code

selectCount

@Test
public void test20(a){
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    SQL: SELECT COUNT(1) FROM user WHERE age >=? * /
    queryWrapper.ge("age".25);
    Integer count = userMapper.selectCount(queryWrapper);
    System.out.println(count);
}
Copy the code

selectOne

Query a record based on the entity condition and return the entity object. Use this method to ensure that your query returns only one piece of data or no data, otherwise an error is reported.

@Test
public void test21(a){
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.eq("name"."Be honest.");
    User user = userMapper.selectOne(queryWrapper);
    System.out.println(user);
    /** * If multiple results are returned, throw exception * TooManyResultsException */
}
Copy the code

Lambda conditional constructor

In addition to the QueryWrapper conditional constructor above, MP also provides a lambda conditional constructor, which prevents us from writing the column name incorrectly.

@Test
public void test22(a){
    /** * Three ways to create a lambda conditional constructor: * LambdaQueryWrapper
      
        lambdaWrapper = new QueryWrapper
       
        ().lambda(); * LambdaQueryWrapper
        
          lambdaWrapper = new LambdaQueryWrapper<>(); Instead of simply new the instance, use Wrappers.lambdaquery (entity) * LambdaQueryWrapper
         
           lambdaWrapper = Wrappers.lambdaquery (); * /
         
        
       
      
    LambdaQueryWrapper<User> lambdaWrapper = Wrappers.lambdaQuery();
    lambdaWrapper.like(User::getName,"Wang").ge(User::getAge,20);
    List<User> users = userMapper.selectList(lambdaWrapper);
    users.forEach(System.out::println);
}
Copy the code

MP also provides another lambda conditional constructor, LambdaQueryChainWrapper, which allows us to chain-construct where conditions and call query methods.

@Test
public void test23(a){
    List<User> users = new LambdaQueryChainWrapper<User>(userMapper)
            .like(User::getName, "Into")
            .ge(User::getAge, 25).list();
    users.forEach(System.out::println);
}
Copy the code

Looking at the LambdaQueryChainWrapper source code, it is not hard to see that the usage method is essentially the same as the separate form above. If you look at the constructor, you can see that the external baseMapper is passed to this.baseMapper and a new LambdaQueryWrapper is created inside.

public LambdaQueryChainWrapper(BaseMapper<T> baseMapper) {
    super(a);this.baseMapper = baseMapper;
    super.wrapperChildren = new LambdaQueryWrapper<>();
}
Copy the code

LambdaQueryChainWrapper implements the ChainQuery

interface, which provides methods such as list, one, etc.

/** * get the set **@returnSet * /
default List<T> list(a) {
	Return usermapper.selectList (lambdaWrapper); * /
    return getBaseMapper().selectList(getWrapper());
}
Copy the code

Customize SQL using Wrapper

Only relying on the limited condition constructor is unable to meet our needs, at this time we need to write our own SQL statement to query. After using Mybatis – Plus, what about the convenience of customizing SQL and also using Wrapper? The Mybatis – Plus version 3.0.7 gets the perfect fix for a version that needs to be greater than or equal to 3.0.7.

Annotation way

public interface UserMapper extends BaseMapper<User> {
	/** * do not add where */ to SQL statement
    @Select("select * from user ${ew.customSqlSegment}")
    List<User> selectAll(@Param(Constants.WRAPPER)Wrapper<User> wrapper);
}
Copy the code

XML way

<select id="selectAll" resultType="user">
	select * from user ${ew.customSqlSegment}
</select>
Copy the code

Paging query

All of the above queries are not paging queries, which are basically not suitable in practical programming and require paging queries. MP provides us with a convenient paging plug-in. Create a new MyBatisPlusConfig class.

@EnableTransactionManagement
@Configuration
public class MyBatisPlusConfig {
    @Bean
    public PaginationInterceptor paginationInterceptor(a) {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
        // Set the request page to be larger than the maximum page operation, true to return to the home page, false to continue the request default false
        // paginationInterceptor.setOverflow(false);
        // Set the maximum number of pages per page, default 500, -1 is not limited
        // paginationInterceptor.setLimit(500);
        returnpaginationInterceptor; }}Copy the code

After configuring the paging plug-in, you can use paging queries. BaseMapper provides selectPage methods for paging queries. It takes a Page object as its first argument and a conditional constructor as its second.

@Test
public void test25(a){
    LambdaQueryWrapper<User> lambdaWrapper = Wrappers.lambdaQuery();
    Page<User> page = new Page<>(1.2);
    lambdaWrapper.ge(User::getAge,18);
    IPage<User> userIPage = userMapper.selectPage(page, lambdaWrapper);
    SELECT COUNT(1) FROM user WHERE (age >=?) SELECT id,deleted,create_time,name,update_time,manager_id,version,email,age FROM user WHERE (age >=?) LIMIT ? ,? * /
    System.out.println("Total pages:"+userIPage.getPages());
    System.out.println("Total records:"+userIPage.getTotal());
    List<User> users = userIPage.getRecords();
    users.forEach(System.out::println);
}
Copy the code

According to our tests, we found that a paging query will query the database twice. There are some scenarios where we do not need to query the total number of records first, such as drop-down load, we do not need to build the front page navigation bar. ·Page· The class constructor has an isSearchCount parameter, which defaults to true, the total number of records queried, and is set to false.

Page<User> page = new Page<>(1.2.false);
Copy the code

Custom SQL paging

Most of the time we need to implement paging for our own custom SQL, as shown in Mapper (simple query, just for demonstration). Other methods of use are the same as above.

/** * query: query the user list by age. ** is displayed in pages@paramThe page page object, from which the VALUE can be derived in XML, must come first (you can inherit the page to implement your own page object) *@paramThe age age *@returnPaging object */
IPage<User> selectPageByAge(Page page, @Param("age") Integer age);
Copy the code

<select id="selectPageByAge" resultType="user" parameterType="int">
    select * from user where age > #{age}
</select>
Copy the code

@Test
public void test27(a){
    LambdaQueryWrapper<User> lambdaWrapper = Wrappers.lambdaQuery();
    Page<User> page = new Page<>(1.2);
    IPage<User> userIPage = userMapper.selectPageByAge(page, 25);
    System.out.println("Total pages:"+userIPage.getPages());
    System.out.println("Total records:"+userIPage.getTotal());
    List<User> users = userIPage.getRecords();
    users.forEach(System.out::println);
}
Copy the code

update

Update by ID

@Test
public void test28(a){
    User user = new User();
    /* Must have id */
    user.setId(5L);
    user.setName("Become honest");
    user.setAge(23);
    int rows = userMapper.updateById(user);
    System.out.println("Number of affected records:"+rows);
}
Copy the code

Using UpdateWrapper

Construct the WHERE condition using UpdateWrapper.

@Test
public void test29(a){
    User user = new User();
    user.setId(5L);
    user.setName("Become honest");
    user.setAge(23);
    UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
    * UPDATE user set name=? , age=? WHERE (name = ? AND age = ?) * /
    updateWrapper.eq("name"."Be honest.").eq("age".30);
    int rows = userMapper.update(user,updateWrapper);
    System.out.println("Number of affected records:"+rows);
}
Copy the code

UpdateWrapper can construct a WHERE condition by passing in an entity object, where variables with a value of NULL are not set in where. Eq (“name”,” true “).eq(“age”,30); “, will be repeated.

@Test
public void test30(a){
    User user = new User();
    user.setId(5L);
    user.setName("Become honest");
    user.setAge(23);
    User whereUser = new User();
    whereUser.setName("Old and mature");
    whereUser.setAge(30);
    whereUser.setId(5L);
    * UPDATE user SET name=? , age=? WHERE id=? AND name=? AND age=? AND (name = ? AND age = ?) * /
    UpdateWrapper<User> updateWrapper = new UpdateWrapper<>(whereUser);
    updateWrapper.eq("name"."Be honest.").eq("age".30);
    int rows = userMapper.update(user,updateWrapper);
    System.out.println("Number of affected records:"+rows);
}
Copy the code

Use the set method of UpdateWrapper

Sometimes we may need to update a few fields, but in the first two methods we created a new entity object to update, too troublesome, MP provides a set method for us to update a few fields.

@Test
public void test31(a){
    UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
    /** * UPDATE user SET age=? WHERE (name = ? AND age = ?) * /
    updateWrapper.eq("name"."Be honest.").eq("age".30).set("age".23);
    int rows = userMapper.update(null,updateWrapper);
    System.out.println("Number of affected records:"+rows);
}
Copy the code

Using LambdaUpdateWrapper

As with LambdaQueryWrapper, there is a LambdaUpdateWrapper.

@Test
public void test32(a){
    LambdaUpdateWrapper<User> lambdaUpdateWrapper = Wrappers.lambdaUpdate();
    lambdaUpdateWrapper.eq(User::getName,"Be honest.").eq(User::getAge,23).set(User::getAge,24);
    int rows = userMapper.update(null,lambdaUpdateWrapper);
    System.out.println("Number of affected records:"+rows);
}
Copy the code

Using LambdaUpdateWrapper

@Test
public void test33(a){
    boolean update = new LambdaUpdateChainWrapper<User>(userMapper).eq(User::getName, "Be honest.")
            .eq(User::getAge, 24).set(User::getAge, 20).update();
    System.out.println("Update successful:"+update);
}
Copy the code

delete

Delete by ID

@Test
public void test34(a){
    int rows = userMapper.deleteById(6L);
    System.out.println("Number of affected records:"+rows);
}

@Test
public void test35(a){
    int rows = userMapper.deleteBatchIds(Arrays.asList(1L.2L.3L));
    System.out.println("Number of affected records:"+rows);
}
Copy the code

Delete from Map

@Test
public void test36(a){
    Map<String,Object> map = new HashMap<>();
    map.put("name"."Old Wang next door.");
    map.put("age".26);
    int rows = userMapper.deleteByMap(map);
    System.out.println("Number of affected records:"+rows);
}
Copy the code

Delete using Wrapper

@Test
public void test37(a){
    LambdaQueryWrapper<User> lambdaQueryWrapper = Wrappers.lambdaQuery();
    lambdaQueryWrapper.eq(User::getName,"Old and mature");
    int rows = userMapper.delete(lambdaQueryWrapper);
    System.out.println("Number of affected records:"+rows);
}
Copy the code

The Active Record pattern

Active Record is a domain model pattern characterized by a model class corresponding to a table in a relational database, and an instance of the model class corresponding to a row of records in the table. AR mode is supported in MP. To use the AR mode, perform the following operations:

/** * 1, the entity class needs to inherit from Model class */
public class User extends Model<User>{... }/** * 2, Mapper needs to inherit BaseMapper */
Copy the code

A profound

/** * insert */
public void test38(a){
    User user = new User();
    user.setName("Zhang");
    user.setAge(23);
    user.setManagerId(1L);
    user.setEmail("[email protected]");
    user.setCreateTime(LocalDateTime.now());
    boolean success = user.insert();
    System.out.println(success);
}

/** * selectById */
 @Test
 public void test39(a){
    User user = new User();
    user.setId(1L);
    User res = user.selectById();
    System.out.println(res);
}

/** * updateById */
@Test
public void test40(a){
    User user = new User();
    user.setId(1201836662942973954L);
    user.setEmail("[email protected]");
    user.setAge(25);
    user.setManagerId(2L);
    user.setVersion(2);
    boolean success = user.updateById();
    System.out.println(success);
}

/** * deleteById * MP deletes data that does not exist */
@Test
public void test41(a){
    User user = new User();
    user.setId(1201836662942973954L);
    boolean success = user.deleteById();
    System.out.println(success);
}

/** * insertOrUpdate */
@Test
public void test42(a){
    User user = new User();
    /** * Set the ID to update, otherwise insert(if you set the ID to not query the object is also insert) */
    user.setId(1201836662942973954L);
    user.setEmail("[email protected]");
    user.setAge(25);
    user.setManagerId(2L);
    user.setVersion(2);
    boolean success = user.insertOrUpdate();
    System.out.println(success);
}
Copy the code

MyBatis – Plus actual combat

Environment to prepare

To build libraries built table

Introduction of depend on

Entity class

Mapper interfaces

configuration

A profound

From traditional crafts to MP

Traditional craft

Now the MP

CRUD of actual combat

insert

Commonly used annotations

@TableName

@TableId

@TableField

Exclude non-table fields

The query

A simple query

Conditions of the query

Other query methods

Lambda conditional constructor

Customize SQL using Wrapper

Annotation way

XML way

Paging query

Custom SQL paging

update

Update by ID

Using UpdateWrapper

Use the set method of UpdateWrapper

Using LambdaUpdateWrapper

Using LambdaUpdateWrapper

delete

Delete by ID

Delete from Map

Delete using Wrapper

The Active Record pattern

A profound

Related Posts

Nginx Learning Notes series 1 -Nginx basics

Nginx configure HTTPS (Let’s Encrypt)

Kubernetes practices the Ingress to Pod access process