Mybatis -Plus code of the heavy device lambda expression use guide, development efficiency instantly increased by 80%

A review,

Now it is more and more popular to develop Web application based on SpringBoot, among which using MyBatis as database CRUD operation has become the mainstream. The main building to MySQL as an example, summed up nine categories of use of MyBatis database SQL tips to share with you.

Paging query
Preset SQL query fields
A pair of multilevel concatenated queries
A pair of first-level queries
Foreach with in query
Assemble the dynamic WHERE condition using the if tag
Assemble the query criteria using the CHOOSE and OTHERWISE combination tags
Dynamic binding query parameter: _parameter
Use set and if tag to dynamically set the update value of database field

01 Paging queries

Use limit to set the offset and size of each page.

select * from sys_user u
LEFT JOIN sys_user_site s ON u.user_id = s.user_id
LEFT JOIN sys_dept d ON d.dept_id = s.dept_id
LEFT JOIN sys_emailinfo e ON u.user_id = e.userid AND e.MAIN_FLAG = 'Y'
<where>
 <include refid="userCondition"/>
</where>
limit #{offset}, #{limit}

02 Preset SQL query fields

<sql id="columns">
  id,title,content,original_img,is_user_edit,province_id,status,porder
</sql>

SELECT COLUMNS FROM COLUMNS

<select id="selectById" resultMap="RM_MsShortcutPanel"> seelct <include refid="columns"/> from cms_self_panel where id =  #{_parameter} </select>

A pair of multilevel joint queries

Using the collection tag of MyBatis, you can query the article body and query the associated table data cascading through QueryParamInstanceList every time.

<resultMap id="BaseResultMap" type="com.unicom.portal.pcm.entity.ArticleEntity">
 <id column="id" jdbcType="BIGINT" property="id"/> 
 <collection property="paramList" column="id" select="queryparaminstancelist"/>
</resultMap>

QueryParamInstanceList SQL statement

<select id="queryparaminstancelist" resultMap="ParamInstanceResultMap">
 select * from `cms_article_flow_param_instance` where article_id=#{id} 
</select>

A pair of first-level queries

Association tag of MyBatis is used to query the data of association table one-to-one.

<resultMap id="BaseResultMap" type="com.unicom.portal.pcm.entity.ArticleEntity">
 <association property="articleCount" javaType="com.unicom.portal.pcm.entity.MsArticleCount"/>
</resultMap>

SQL > query SQL >

The value of the property of the MSArticlecount entity object can be obtained from the matching mapping of the SQL field after SELECT above.

05 foreach collocation in query

Use foreach to iterate over the parameters of the array collection to assemble an IN query condition

<foreach collection="array" index="index" item="item" open="(" separator="," close=")">
 #{item}
</foreach>

Assemble the dynamic WHERE condition using the IF tag

select r.*, (select d.org_name from sys_dept d where d.dept_id = r.dept_id) deptName from sys_role r <where> r.wid = #{wid} <if test="roleName ! = null and roleName.trim() ! = ''"> and r.`role_name` like concat('%',#{roleName},'%') </if> <if test="status ! = null and status.trim() ! = ''"> and r.`status` = #{status} </if> </where>

07 Assemblage the query criteria using the CHOOSE and OTHERWISE combination tags

<choose> <when test="sidx ! = null and sidx.trim() ! = ''"> order by r.${sidx} ${order} </when> <otherwise> order by r.role_id asc </otherwise> </choose>

08 Invisible binding parameter: _parameter

The meaning of the _parameter parameter

“

When querying when Mapper, association, and collection specify only one parameter, _parameter can be used, which represents this parameter.

In addition, when @Param is used in a Mapper specified method, the specified parameter value is used instead.

SELECT id, grp_no grpNo, province_id provinceId, status FROM tj_group_province <where> ... <if test="_parameter! =null"> and grp_no = #{_parameter} </if> </where>

09 use set with if label, dynamically set database field update value

<update id="updateById"> UPDATE cms_label <set> <if test="labelGroupId ! = null"> label_group_id = #{labelGroupId}, </if> dept_id = #{deptId}, <if test="recommend ! = null"> is_recommend = #{recommend}, </if> </set> WHERE label_id = #{labelId} </update

II. Mybatis-Plus Lambda Expression Theory

background

If MyBatis -Plus is a wrench, then MyBatis Generator is the factory that produces the wrench.

MyBatis is a database operation ORM framework, provides a Mapper class, support for you to use Java code to add, delete, change and check the database operation, save the trouble of writing SQL statements every time. But you have to write the SQL statement in XML first, which is also very cumbersome.

As an aside: MyBatis vs. Hibernate

MyBatis is a semi-ORM framework; Hibernate is a full ORM framework. MyBatis needs to write its own SQL.
MyBatis directly write native SQL, high flexibility, can strictly control the performance of SQL execution; Hibernate automatically generates HQL, because of better encapsulation, development efficiency is improved at the same time, SQL statement tuning is more troublesome.
Hibernate’s HQL database portability is better than that of MyBatis. Hibernate’s underlying HQL is processed, so it has better database compatibility.
MyBatis directly write native SQL are related to the database, different database SQL is different, then need multiple sets of SQL mapping files.
Hibernate is inefficient when cascading deletes; When there is a large amount of data and many tables, the operation based on relationship will become complicated.
Both MyBatis and Hibernate can use third-party caching, and Hibernate has a better second-level caching mechanism than MyBatis.

Why choose a Lambda expression?

MyBatis -Plus exists to make up for some of MyBatis’s shortcomings.

When we use MyBatis, we will find that whenever we want to write a business logic, we need to write a method in the DAO layer, and then corresponding to a SQL. Even if it is a simple condition query, even if only a condition is changed, we need to add a method in the DAO layer. For this problem, MyBatis Plus provides a good solution: lambda expressions, which can avoid a lot of repetitious work.

Think of the CRUD example provided on MyBatis website. Basically, XML configuration is the majority. The CRUD code written with Lambda expressions is very compact and truly zero configuration, without the need to write a lot of native SQL code in XML or with annotations (@Select).

LambdaQueryWrapper<UserEntity> lqw = Wrappers.lambdaQuery(); lqw.eq(UserEntity::getSex, 0L) .like(UserEntity::getUserName, "dun"); List<UserEntity> userList = userMapper.selectList(lqw); UserList.foreach (u-> System.out.println("like "+ u.getUserName())));

The theoretical basis of lambda expressions

A lambda expression in Java is essentially an anonymous method, but rather than executing in isolation, it is used to implement the only abstract method defined by a functional interface.

With lambda expressions, an anonymous instance of a class is created that implements a FunctionalInterface, such as the thread Runnable class in Java8 that implements a FunctionalInterface: @FunctionalInterface.

@FunctionalInterface
public interface Runnable {
    public abstract void run();
}

Normally we execute a Thread:

new Thread(new Runnable() {
  @Override
  public void run() {
      System.out.println("xxxx");
  }
}).start();

If you do lambda it’s very neat, one line of code.

 new Thread(()-> System.out.println("xxx")).start();

So using lambda expressions in some scenarios can really reduce some of the verbose code in Java and increase the elegance of the code.

Lambda conditional constructor base class: wrapper mode (decorator mode) AbstractWrapper AbstractWrapper conditional constructor specification

The first argument that occurs in the Boolean condition indicates whether the condition is added to the last generated SQL, for example: query.like(StringUtils.isNotBlank(name), Entity::getName, name) .eq(age! =null && age >= 0, Entity::getAge, age)
Multiple methods within a code block complete individual Boolean input arguments from the top down. Default is true
The generic Param that appears is a subclass instance of Wrapper (with all methods of AbstractWrapper)
The R that a method appears in an entry parameter is a generic type, a String in a normal Wrapper, and a function in a lambdaWrapper (e.g., Entity::getId, Entity::getId, GetId).
The R column in the parameter of the method is the database field, and the R column is the database field name if the specific type of R is String. ! Not the name of the entity class data field!! In addition, project Runtime does not support Eclipse’s own compiler when R is of type sFunction!
Using a normal wrapper, Map and List parameters are represented as JSON!
If the Map or List is empty, it will not be added to the generated SQL.

Warning:

Transferring Wrapper in RPC calls is not supported and discouraged.

“

The Wrapper is very heavy. Transferring the Wrapper is analogous to your Controller receiving values with a map (developing for a while, maintaining a crematorium). The proper way to call the RPC is to write a DTO for transmission. We refuse to accept any issue or even PR related to an error in the RPC transfer Wrapper.

AbstractWrapper internal structure

From the above diagram, we can see that AbstractWrapper actually implements five interfaces:

SQL Segment Function Interface: ISQLSegment

@FunctionalInterface public interface isqlSegment extends Serializable {/** * SQL Segment */ String getSQLSegment (); }

Compare

, eq, not equal to: ne, greater than gt, greater than or equal to: ge, less than lt, less than or equal to le, between, fuzzy query: like, etc
,>
Nested interfaces Nested , such as and, or
,>
Concatenation interface Join

, such as OR, EXISTS
Function interface Func

, such as in query, groupby group, having, order by sort, etc
,>

Commonly used where conditional expressions eq, like, in, ne, gt, ge, lt, le

@Override public Children in(boolean condition, R column, Collection<? > coll) { return doIt(condition, () -> columnToString(column), IN, inExpression(coll)); } public Children notIn(boolean condition, R column, Collection<? > coll) public Children inSql(boolean condition, R column, String inValue) public Children notInSql(boolean condition, R column, String inValue) public Children groupBy(boolean condition, R... columns) public Children orderBy(boolean condition, boolean isAsc, R... columns) public Children eq(boolean condition, R column, Object val) public Children ne(boolean condition, R column, Object val) public Children gt(boolean condition, R column, Object val) public Children ge(boolean condition, R column, Object val) public Children lt(boolean condition, R column, Object val) public Children le(boolean condition, R column, Object val) ... /** * normal condition ** @Param condition * @Param column * @Param sqlKeyword * @Param val condition */ protected Children addCondition(boolean condition, R column, SqlKeyword sqlKeyword, Object val) { return doIt(condition, () -> columnToString(column), sqlKeyword, () -> formatSql("{0}", val)); }

SQL fragment function interface

The secret to lambda’s usefulness is the SQL fragment function interface: @FunctionalInterface is a JAVA segment that is based on the @FunctionalInterface segment of the JAVA segment. This is a JAVA segment.

ISQLSegment is the assembly of each conditional segment in a WHERE.

/** * Assembles SQL Segments ** @Param condition whether to execute * @Param SQLSegments array * @Return children */ protected children doIt(boolean condition, ISqlSegment... sqlSegments) { if (condition) { expression.add(sqlSegments); } return typedThis; } @FunctionalInterface public interface isqlSegment extends Serializable {/** * SQL Segment */ String getSQLSegment (); }

From the MergePayments class, we find the getSQLSegment method, which contains the code snippet

sqlSegment = normal.getSqlSegment() + groupBy.getSqlSegment() + having.getSqlSegment() + orderBy.getSqlSegment()

This code shows that a complete WHERE conditional SQL statement is eventually composed of Normal SQL fragment, GroupBy SQL fragment, Having SQL fragment, and OrderBy SQL fragment.

@Getter @SuppressWarnings("serial") public class MergeSegments implements ISqlSegment { private final NormalSegmentList normal = new NormalSegmentList(); private final GroupBySegmentList groupBy = new GroupBySegmentList(); private final HavingSegmentList having = new HavingSegmentList(); private final OrderBySegmentList orderBy = new OrderBySegmentList(); @Getter(AccessLevel.NONE) private String sqlSegment = StringPool.EMPTY; @Getter(AccessLevel.NONE) private boolean cacheSqlSegment = true; public void add(ISqlSegment... iSqlSegments) { List<ISqlSegment> list = Arrays.asList(iSqlSegments); ISqlSegment firstSqlSegment = list.get(0); if (MatchSegment.ORDER_BY.match(firstSqlSegment)) { orderBy.addAll(list); } else if (MatchSegment.GROUP_BY.match(firstSqlSegment)) { groupBy.addAll(list); } else if (MatchSegment.HAVING.match(firstSqlSegment)) { having.addAll(list); } else { normal.addAll(list); } cacheSqlSegment = false; } @Override public String getSqlSegment() { if (cacheSqlSegment) { return sqlSegment; } cacheSqlSegment = true; if (normal.isEmpty()) { if (! groupBy.isEmpty() || ! orderBy.isEmpty()) { sqlSegment = groupBy.getSqlSegment() + having.getSqlSegment() + orderBy.getSqlSegment(); } } else { sqlSegment = normal.getSqlSegment() + groupBy.getSqlSegment() + having.getSqlSegment() + orderBy.getSqlSegment(); } return sqlSegment; }}

3, Mybatis-Plus Lambda expression practice

01 Environmental Preparation

1. The Maven dependencies

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
  <groupId>com.github.pagehelper</groupId>
  <artifactId>pagehelper-spring-boot-starter</artifactId>
</dependency>
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
  <groupId>com.baomidou</groupId>
  <artifactId>mybatis-plus-boot-starter</artifactId>
</dependency>
<dependency>
  <groupId>org.projectlombok</groupId>
  <artifactId>lombok</artifactId>
</dependency>
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-test</artifactId>
  <scope>test</scope>
</dependency>
<dependency>
  <groupId>junit</groupId>
  <artifactId>junit</artifactId>
  <scope>test</scope>
</dependency>
<dependency>
  <groupId>org.apache.commons</groupId>
  <artifactId>commons-lang3</artifactId>
</dependency>
<dependency>
  <groupId>com.google.code.gson</groupId>
  <artifactId>gson</artifactId>
</dependency>

2. Entity (table) and Mapper table mapping files

Base entities

@NoArgsConstructor
@AllArgsConstructor(access = AccessLevel.PACKAGE)
@SuperBuilder(toBuilder = true)
@Data
public class BaseEntity {

    @TableField(value = "created_tm", fill = FieldFill.INSERT)
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private LocalDateTime createdTm;

    @TableField(value = "created_by", fill = FieldFill.INSERT)
    private String createdBy;

    @TableField(value = "modified_tm", fill = FieldFill.INSERT_UPDATE)
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private LocalDateTime modifiedTm;

    @TableField(value = "modified_by", fill = FieldFill.INSERT_UPDATE)
    private String modifiedBy;
}

User account entity: UserEntity

@EqualsAndHashCode(callSuper = true)
@NoArgsConstructor
@AllArgsConstructor(access = AccessLevel.PACKAGE)
@SuperBuilder(toBuilder = true)
@Data
@TableName("sys_user")
public class UserEntity extends BaseEntity{
    private Long userId;
    private String userName;
    private Integer sex;
    private Integer age;
    private String mobile;
}

Mapper action class

List<UserDTO> selectUsers(); UserEntity selectByIdOnXml(long userId); @Results(id = "userResult", value = { @Result(property = "user_id", column = "userId", id = true), @Result(property = "userName", column = "user_name"), @Result(property = "sex", column = "sex"), @Result(property = "mobile", column = "mobile"), @Result(property = "age", column = "age") }) @Select("select * from sys_user where user_id = #{id}") UserEntity selectByIdOnSelectAnnotation(@Param("id") long id); @SelectProvider(type = UserSqlProvider.class, method = "selectById") @ResultMap("BaseResultMap") UserEntity selectByIdOnSelectProviderAnnotation(long id); @Select("select * from sys_user where user_id = #{id} and user_name=#{userName}") @ResultMap("BaseResultMap") UserEntity  selectByIdOnParamAnnotation(@Param("id") long id, @Param("userName") String uerName);

Mapper table mapping file

<mapper namespace="com.dunzung.mybatisplus.query.mapper.UserMapper">
    <resultMap id="BaseResultMap" type="com.dunzung.mybatisplus.query.entity.UserEntity">
        <id column="user_id" property="userId"/>
        <result column="user_name" property="userName"/>
        <result column="sex" property="sex"/>
        <result column="age" property="age"/>
        <result column="mobile" property="mobile"/>
    </resultMap>
    
    <resultMap id="RelationResultMap" type="com.dunzung.mybatisplus.query.entity.UserDTO" extends="BaseResultMap">
        <association property="card" column="{userId,user_id}"
                     select="com.dunzung.mybatisplus.query.mapper.CardMapper.selectCardByUserId"/>
        <collection property="orders" column="{userId,user_id}"
                    select="com.dunzung.mybatisplus.query.mapper.OrderMapper.selectOrders"/>
    </resultMap>
    
    <select id="selectUsers" resultMap="RelationResultMap">
        select * from sys_user
    </select>
    
    <select id="selectByIdOnXml" resultMap="BaseResultMap">
        select * from sys_user where user_id = #{userId}
   </select>
    
</mapper>

Order entity: OrderEntity

@Data
@TableName("sys_user_card")
public class CardEntity {
    private Long cardId;
    private String cardCode;
    private Long userId;
}

Mapper action class

@Mapper
public interface OrderMapper extends BaseMapper<OrderEntity> {
}

Mapper table mapping file

<mapper namespace="com.dunzung.mybatisplus.query.mapper.OrderMapper">
    <resultMap id="BaseResultMap" type="com.dunzung.mybatisplus.query.entity.OrderEntity">
        <id column="order_id" property="orderId"/>
        <result column="order_name" property="orderName"/>
        <result column="user_id" property="userId"/>
        <result column="price" property="price"/>
        <result column="created_tm" property="createdTm"/>
    </resultMap>

    <select id="selectOrders" resultMap="BaseResultMap">
         select * from biz_order where user_id = #{userId}
    </select>

</mapper>

Identity Entity: Cardentity

@Data
@TableName("biz_order")
public class OrderEntity {
    private Long orderId;
    private String orderName;
    private Integer userId;
    private Date createdTm;
    private Integer price;
}

Mapper action class

@Mapper
public interface CardMapper extends BaseMapper<CardEntity> {
}

Mapper table mapping file

<mapper namespace="com.dunzung.mybatisplus.query.mapper.CardMapper">
    <resultMap id="BaseResultMap" type="com.dunzung.mybatisplus.query.entity.CardEntity">
        <id column="card_id" property="cardId"/>
        <result column="card_code" property="cardCode"/>
        <result column="user_id" property="userId"/>
    </resultMap>
    <select id="selectCardByUserId" resultMap="BaseResultMap">
        select * from sys_user_card where user_id = #{userId}
    </select>
</mapper>

02 Lambda Foundation

Lambda builds a complex query condition constructor: LambdaQueryWrapper

LambdaQueryWrapper has four different lambda constructors

Method one uses the QueryWrapper member method lambda to build LambdaQueryWrapper

LambdaQueryWrapper<UserEntity> lambda = new QueryWrapper<UserEntity>().lambda();

LambdaQueryWrapper

LambdaQueryWrapper<UserEntity> lambda = new  LambdaQueryWrapper<>();

Approach three uses Wrappers’ static method lambdaQuery to build a lambdaQueryWrapper recommendation

LambdaQueryWrapper<UserEntity> lambda = Wrappers.lambdaQuery();

Way four: chain query

A List < UserEntity > users = new LambdaQueryChainWrapper < UserEntity > (userMapper.) like (User: : getName, "rain"), ge (User: : getAge, 20).list();

I recommend using Wrappers’ static method lambdaQuery to build the lambdaQueryWrapper conditional constructor.

The Debug debugging

To facilitate debugging, it is necessary to enable full-stack printing of Mybatis-Plus SQL execution statements in the application.yml startup file:

#mybatis
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

The execution effect is as follows:

1 Equivalent query: EQ

@test public void testLambDaQueryoFeq () {select * from sys_user where user_id = 1 LambdaQueryWrapper<UserEntity> lqw = Wrappers.lambdaQuery(); lqw.eq(UserEntity::getUserId, 1L); UserEntity user = userMapper.selectOne(lqw); System.out.println("eq query ::" + User.getUserName ()); }

EQ queries are equivalent to native SQL equivalents.

select * from sys_user where user_id = 1

2 range query: in

@Test public void testLambdaQueryOfIn() { List<Long> ids = Arrays.asList(1L, 2L); LambdaQueryWrapper<UserEntity> lqw = Wrappers.lambdaQuery(); lqw.in(UserEntity::getUserId, ids); List<UserEntity> userList = userMapper.selectList(lqw); UserList.foreach (u-> System.out.println("in query ::" + u.getUserName()))); }

IN queries are equivalent to native SQL IN queries

Select * from sys_user where user_id in (1,2)

SELECT * FROM ‘LIKE’ WHERE ‘LIKE’ IS USED

@Test public void testLambdaQueryOfLikeAll() { LambdaQueryWrapper<UserEntity> lqw = Wrappers.lambdaQuery(); lqw.eq(UserEntity::getSex, 0L) .like(UserEntity::getUserName, "dun"); List<UserEntity> userList = userMapper.selectList(lqw); UserList.foreach (u-> System.out.println("like "+ u.getUserName()))); }

LIKE queries are equivalent to native SQL LIKE all-wildcard fuzzy queries.

select * from sys_user where sex = 0 and user_name like '%dun%'

4 Right wildcard fuzzy query: Likeright

@Test public void testLambdaQueryOfLikeRight() { LambdaQueryWrapper<UserEntity> lqw = Wrappers.lambdaQuery(); lqw.eq(UserEntity::getSex, 0L) .likeRight(UserEntity::getUserName, "dun"); List<UserEntity> userList = userMapper.selectList(lqw); UserList.foreach (u-> system.out. println("like "+ u.getUserName())); }

LikeRight queries are equivalent to native SQL LIKERight wildcard fuzzy queries.

select * from sys_user where sex = 0 and user_name like 'dun%'

5 left wildcard fuzzy query: likeLeft

@Test public void testLambdaQueryOfLikeLeft() { LambdaQueryWrapper<UserEntity> lqw = Wrappers.lambdaQuery(); lqw.eq(UserEntity::getSex, 0L) .likeLeft(UserEntity::getUserName, "zung"); List<UserEntity> userList = userMapper.selectList(lqw); UserList.foreach (u-> system.out. println("like Left ::" + u.getUserName())); }

A likeLeft query is equivalent to a native SQL LIKE left wildcard fuzzy query.

select * from sys_user where sex = 0 and user_name like '%zung'

6 condition judgment query

The condition judgment query is similar to the if tag of MyBatis. The first parameter of the Boolean condition indicates whether the condition is added to the last generated SQL.

@Test public void testLambdaQueryOfBoolCondition() { UserEntity condition = UserEntity.builder() .sex(1) .build(); LambdaQueryWrapper< userEntity > LQW = Wrappers. LambdaQuery (); lqw.eq(condition.getSex() ! = NULL, UserEntity::getSex, 0L) = null, UserEntity::getUserName, "dun"); List<UserEntity> userList = userMapper.selectList(lqw); UserList.foreach (u-> system.out. println("like query ::" + u.getUserName())); }

7. Use OR and AND to build complex query conditions

@Test public void testLambdaQueryOfOr_And() { LambdaQueryWrapper<UserEntity> lqw = Wrappers.lambdaQuery(); lqw.eq(UserEntity::getSex, 0L) .and(wrapper->wrapper.eq(UserEntity::getUserName,"dunzung") .or().ge(UserEntity::getAge, 50)); List<UserEntity> userList = userMapper.selectList(lqw); UserList.foreach (u-> system.out. println("like query ::" + u.getUserName())); }

The above example query is equivalent to a native SQL query:

select * from sys_user where sex = 0 and (use_name = 'dunzung' or age >=50)

8 good at using page sharpen PageHelpler

@test public void testLambdapage () {@test public void testLambdapage () {@test public void testLambdapage () {@test public void testLambdapage () {@test public void testLambdapage () { int pageSize = 2; PageHelper.startPage(pageNumber + 1, pageSize); LambdaQueryWrapper<UserEntity> lqw = Wrappers.lambdaQuery(); lqw.orderByAsc(UserEntity::getAge) .orderByDesc(UserEntity::getMobile); List<UserEntity> userList = userMapper.selectList(lqw); UserList.foreach (u-> System.out.println(" Page ::" + u.getUserName()))); }

The above example query is equivalent to a native SQL paging query:

Select * from sys_user order by age desc,mobile desc limit 0,2

In addition, MyBatis -Plus has its own paging component, and BaseMapper interface provides two paging methods to achieve physical paging.

The first return entity object allows null
The map object returned by the second person is mostly used when the specified field is put back, to avoid the null value for the specified field

IPage<T> selectPage(IPage<T> page, @Param("ew") Wrapper<T> queryWrapper);
IPage<Map<String, Object>> selectMapsPage(IPage<T> page, @Param("ew") Wrapper<T> queryWrapper);

Note that when MyBatis -Plus comes with its own paging component, it needs to configure the PaginationInterceptor paging plug-in.

@Bean
public PaginationInterceptor paginationInterceptor() {
    return new PaginationInterceptor();
}

Update conditional constructor: lambdaupdateWrapper

@Test
public void testLambdaUpdate() {
    LambdaUpdateWrapper<UserEntity> luw = Wrappers.lambdaUpdate();
    luw.set(UserEntity::getUserName, "dunzung01")
            .set(UserEntity::getSex, 1);
    luw.eq(UserEntity::getUserId, 1);
    userMapper.update(null, luw);
}

03 advanced

1. Association

Association tag applies to tables and tables have a one-to-one Association relationship, such as a user and ID card there will only be one ID number of a person, and vice versa.

@Test
public void testOnAssociationTag() {
    List<UserDTO> userList = userMapper.selectUsers();
    userList.forEach(u -> System.out.println(u.getUserName()));
}

The XML configuration

<resultMap id="RelationResultMap" type="com.dunzung.mybatisplus.query.entity.UserDTO" extends="BaseResultMap">
  <association property="card" column="{userId,user_id}"
               select="com.dunzung.mybatisplus.query.mapper.CardMapper.selectCardByUserId"/>
</resultMap>

2. Collection

Collection label applies to tables and tables where there is a one-to-many association relationship, such as the existence of users and orders. One person can purchase multiple items and generate multiple shopping orders.

@Test
public void testOnCollectionTag() {
    List<UserDTO> userList = userMapper.selectUsers();
    userList.forEach(u -> System.out.println(u.getUserName()));
}

The XML configuration

<resultMap id="RelationResultMap" type="com.dunzung.mybatisplus.query.entity.UserDTO" extends="BaseResultMap">
  <collection property="orders" 
      column="{userId,user_id}" 
      select="com.dunzung.mybatisplus.query.mapper.OrderMapper.selectOrders"/>
  </resultMap>

Note the sequential relationship between Association and Collection. When writing a ResultMap, Association comes first and Collection tag comes second.

 <resultMap id="RelationResultMap" type="com.dunzung.mybatisplus.query.entity.UserDTO" extends="BaseResultMap">
        <association property="card" column="{userId,user_id}"
                     select="com.dunzung.mybatisplus.query.mapper.CardMapper.selectCardByUserId"/>
        <collection property="orders" column="{userId,user_id}"
                    select="com.dunzung.mybatisplus.query.mapper.OrderMapper.selectOrders"/>
    </resultMap>

An error will appear if the order is reversed.

3. Meta object field padding property value: MetaObjectHandler

The MetaObjectHandler metaobject field filler works by directly assigning values to properties of an entity. The policies that provide default methods are:

“

If the attribute has a value, it will not be overridden. If the fill value is null, it will not be filled. The field must declare TableField annotations. The TableField annotation specifies that the property must have a value in the corresponding case, and that if it has no value, the repository will be null.

MyMetaObjectHandler needs to declare @Component or @Bean injection in Spring Boot. To do this, use the FieldFill. XXX annotation, such as:

@TableField(value = "created_tm", fill = FieldFill.INSERT)
private LocalDateTime createdTm;

@TableField(value = "modified_tm", fill = FieldFill.INSERT_UPDATE)
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private LocalDateTime modifiedTm;

The setInsertFieldValByName or setUpdateFieldValByName method must be used by the parent class, and the field name and field type. There is no need to distinguish setFieldValByName method that can be used by the parent class.

/** * Handler ** @author * @since 2021/3/30 */ @Component public class FillMetaObjectHandler implements MetaObjectHandler { @Override public void insertFill(MetaObject metaObject) { this.setInsertFieldValByName("createdTm", LocalDateTime.now(), metaObject); this.setInsertFieldValByName("createdBy", MvcContextHolder.getUserName(), metaObject); this.setFieldValByName("modifiedTm", LocalDateTime.now(), metaObject); this.setFieldValByName("modifiedBy", MvcContextHolder.getUserName(), metaObject); } @Override public void updateFill(MetaObject metaObject) { this.setUpdateFieldValByName("modifiedTm", LocalDateTime.now(), metaObject); this.setUpdateFieldValByName("modifiedBy", MvcContextHolder.getUserName(), metaObject); }}

Insert updates the value of the create property (creator, creation time) with the setInsertFieldValByName method of the superclass. FieldFill. Insert_Update updates the value of the modified property (change person, change time) with the setupdateFieldValByName method of the superclass. If you want things like FieldFlow. INSERT or FieldFlow. insert_Update to be inert at any time, use the setFieldValByName of the superclass to set the value of the property (creator, create time, modifier, modify time).

4. Customize SQL

Customizable SQL using Wrapper requires MyBatis – Plus version >= 3.0.7, with either ew or @Param(Constants.wrapper) annotation, ${ew. customsqlSegment} does not support an entity within a Wrapper to generate WHERE statements.

Annotation way

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

The XML configuration

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

IV. Advantages and disadvantages of Mybatis-Plus lambda expression

With the above rich examples to explain and analyze the underlying implementation of lambda, you may ask: “Lambda expressions only seem to support single-table operations?”

According to my understanding of MyBatis -Plus official website, this is indeed the case at present. According to my practical experience, most of the functions developed by programmers are basically for single table operations. Lambda expressions have the advantage of helping developers to reduce the amount of repetitive CRUD code written in XML, which is very important and nice. Obviously, Lambda expressions are self-evident to improve the development efficiency of programmers, which I think is also an important reason why I like Mybatis-Plus very much as a programmer.

However, when it comes to associative queries between multiple tables, lambda expressions are inadequate because MyBatis -Plus does not provide a conditional constructor similar to a join query.

Advantages of lambda expressions:

Single table operation, the code is very simple, truly zero configuration, such as do not need to write a lot of native SQL code in XML or with annotations (@Select)
Parallel computing
Forecasts represent future programming trends

Disadvantages of lambda expressions:

Single table operation, for multi-table associative query support is not good
Debugging difficulty
Complex underlying logic

Five, the summary

Lambda expressions from Mybatis Plus are designed to build complex WHERE queries. The constructor is not a silver bullet. It can solve 80% of the development efficiency problems in your real project, but it is not well supported for some large complex SQL query conditions. For example, some complex SQL report statistics queries.

Therefore, I recommend lambda expressions for single-table operations, lambdaQueryWrapper for queries, and lambdaupdateWrapper for updates. Multiple table operations or just write some native SQL, but where to write the native SQL? Mapper files or based on annotations such as @Select are both acceptable.

reference

https://mp.baomidou.com/guide…
https://www.jianshu.com/p/613…
https://blog.csdn.net/Solitud…
https://blog.csdn.net/weixin_…
https://blog.csdn.net/weixin_…

Author: Ape Xin

Source:
https://www.toutiao.com/i6951…

Mybatis -Plus code of the heavy device lambda expression use guide, development efficiency instantly increased by 80%

A review,

01 Paging queries

02 Preset SQL query fields

A pair of multilevel joint queries

A pair of first-level queries

05 foreach collocation in query

Assemble the dynamic WHERE condition using the IF tag

07 Assemblage the query criteria using the CHOOSE and OTHERWISE combination tags

08 Invisible binding parameter: _parameter

09 use set with if label, dynamically set database field update value

II. Mybatis-Plus Lambda Expression Theory

background

As an aside: MyBatis vs. Hibernate

Why choose a Lambda expression?

The theoretical basis of lambda expressions

Lambda conditional constructor base class: wrapper mode (decorator mode) AbstractWrapper AbstractWrapper conditional constructor specification

AbstractWrapper internal structure

SQL fragment function interface

3, Mybatis-Plus Lambda expression practice

01 Environmental Preparation

1. The Maven dependencies

2. Entity (table) and Mapper table mapping files

02 Lambda Foundation

Lambda builds a complex query condition constructor: LambdaQueryWrapper

The Debug debugging

1 Equivalent query: EQ

2 range query: in

SELECT * FROM ‘LIKE’ WHERE ‘LIKE’ IS USED

4 Right wildcard fuzzy query: Likeright

5 left wildcard fuzzy query: likeLeft

6 condition judgment query

7. Use OR and AND to build complex query conditions

8 good at using page sharpen PageHelpler

Update conditional constructor: lambdaupdateWrapper

03 advanced

1. Association

2. Collection

3. Meta object field padding property value: MetaObjectHandler

4. Customize SQL

IV. Advantages and disadvantages of Mybatis-Plus lambda expression

Five, the summary

reference

Related Posts

Solves the subscript for the range of a non-decrement array

Simple and practical Redis distributed lock usage

How are @Component, @Service and other annotations resolved?