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

A review,

Now it is more and more popular to develop Web applications based on SpringBoot, among which Mybatis has become the mainstream as database CRUD operation. MySQL as an example, summarized nine categories using Mybatis operating database SQL tips to share with you.

1. Paging query
2. Preset SQL query fields
3. A pair of multi-level federated queries
4. A pair of cascading queries
5. Foreach with in query
6. Assemble dynamic WHERE conditions using if tags
7. The query criteria are assembled using the choose and Otherwise combination labels
8. Dynamically binding query parameter: _parameter
9. Use set with if tag to dynamically set database field update value

01 Paging Query

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}
Copy the code

02 Presetting SQL query fields

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

Query columns from select statement

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

03 Pair multi-level query

Using the Collection tag of Mybatis, you can query the associated table data by queryParamInstancelist cascade every time you query the body of an article.

<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>
Copy the code

SQL statement of queryParamInstancelist

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

04 Pair Cascading Query

Use mybatis association tag, one to one query associated table data.

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

Query SQL statement:

The attribute values of the MsArticlecount entity object can be obtained from the match mapping of the SQL fields after the select above.

05 Foreach with in query

Use foreach to iterate through the parameters of the array collection to form in query conditions

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

Assemble dynamic WHERE conditions with if tags

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>Copy the code

07 Assemble query criteria using choose and Otherwise combination labels

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

08 Invisible binding parameter: _parameter

_parameter Indicates the meaning of a parameter

“

When a Mapper, association, or collection specifies only one parameter, you can use _parameter, which represents this parameter.

In addition, when @param is used with Mapper, 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>Copy the code

09 Use set and IF tag to 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} </updateCopy the code

Mybatis-Plus Lambda expression theory

background

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

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

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 SQL execution performance; 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 Mybatis. The bottom layer of Hibernate deals with HQL and has better compatibility with databases.
• Mybatis directly write native SQL is related to the database, different database SQL is different, then need multiple sets of SQL mapping files.
• Hibernate is inefficient when cascading deletes; When the data volume is large and the tables are large, the relations-based operation becomes complicated.
• Both Mybatis and Hibernate can use third-party caching, and Hibernate has a better second-level caching mechanism than Mybatis.

Why Lambda expressions?

Mybatis-Plus exists to make up for the lack of Mybatis.

When we use Mybatis, we will find that every time we want to write a business logic, we need to write a method in the DAO layer, and then corresponding to a SQL. Even simple conditional 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 save us a lot of repetitive work.

Consider the CRUD examples provided by Mybatis. XML configuration is the majority. CRUD code written in Lambda expressions is very clean, truly zero configuration, and does not require 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 full keyword query ::" + u.getUsername ()));Copy the code

The theoretical basis of lambda expressions

Lambda expression in Java is essentially an anonymous method that does not execute independently, but is the only abstract method used to implement functions defined by a functional interface.

When lambda expressions are used, an anonymous class instance is created that implements the FunctionalInterface, such as the thread Runnable class in Java8 that implements the FunctionalInterface: @functionalinterface.

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

Normally we execute a Thread:

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

Lambda is pretty neat, one line of code.

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

So using lambda expressions in certain scenarios can actually reduce some of the verbosity of Java code and increase the code’s elegance.

Lambda conditional constructor base class: AbstractWrapper AbstractWrapper Conditional constructor specification for wrapper pattern (decorator pattern)

1. 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)
2. Multiple methods within the code block complete individual Boolean type inputs from the top down, default to true
3. Any generic Param that appears is a subclass instance of Wrapper (all with AbstractWrapper methods)
4. Method R appears as a generic in an input parameter, a String in a plain Wrapper, and a function in a LambdaWrapper (e.g. Entity::getId, Entity is an Entity class, and getId is getMethod for the field ID).
5. The R column in the method entry parameter represents the database field, or the database field name when R is a String. ! Not entity class data field name!! In addition, the project Runtime does not support eclipse’s own compiler when R is SFunction.
6. Using plain wrapper, Map and List entries are represented as JSON!
7. If the Map or List of the input parameter is empty, it will not be added to the final generated SQL!

Warning:

Transmission of Wrapper in RPC calls is not supported or approved.

“

The Wrapper is very heavy. Transmitting the Wrapper can be analogous to your controller receiving values with a map. The correct RPC call position is to write a DTO to transmit. We refuse to accept any issue or even PR related to RPC transmission Wrapper error.

AbstractWrapper internal structure

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

• SQL segment function interface: ISqlSegment

@functionalInterface public interface ISqlSegment extends Serializable {/** * SQL segment */ String getSqlSegment(); }Copy the code

• Compare
  
  , such as equivalent 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 and so on
  ,>
• Nested interfaces Nested , such as and, or
  ,>
• Join an interface Join, such as OR exists
• Func
  
  , such as in query, groupby group, having, order by order, etc
  ,>

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) ... @param condition whether to execute @param column attribute @param sqlKeyword SQL keyword @param val conditional value */ protected Children addCondition(boolean condition, R column, SqlKeyword sqlKeyword, Object val) { return doIt(condition, () -> columnToString(column), sqlKeyword, () -> formatSql("{0}", val)); }Copy the code

SQL fragment function interface

The secret to lambda being so useful is the SQL fragment function interface: ISqlSegment object parameter ISqlSegment object parameter ISqlSegment object parameter ISqlSegment object parameter ISqlSegment object parameter

ISqlSegment is the assembly of each conditional fragment in where.

@param sqlSegments array @param sqlSegments @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(); }Copy the code

From the MergeSegments class, we find the getSqlSegment method, where the snippet is

sqlSegment = normal.getSqlSegment() + groupBy.getSqlSegment() + having.getSqlSegment() + orderBy.getSqlSegment()
Copy the code

This code shows that a complete where conditional SQL statement is finally concatenated from a normal SQL fragment, a groupBy SQL fragment, a having SQL fragment, and an 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; }}Copy the code

Mybatis-Plus Lambda expression

01 Environment Preparations

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>
Copy the code

2. Entities (tables) 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;
}
Copy the code

• 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;
}
Copy the code

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);Copy the code

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>
Copy the code

• OrderEntity: OrderEntity

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

Mapper action class

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

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>
Copy the code

• Id CardEntity: CardEntity

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

Mapper action class

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

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>
Copy the code

02 Lambda Basics

Lambda builds a complex query condition constructor: LambdaQueryWrapper

LambdaQueryWrapper four different lambda constructors

• Method one builds a LambdaQueryWrapper using the QueryWrapper member method method lambda

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

• Method 2: Directly new the LambdaQueryWrapper

LambdaQueryWrapper<UserEntity> lambda = new  LambdaQueryWrapper<>();
Copy the code

• Method three uses Wrappers’ static method lambdaQuery to build the LambdaQueryWrapper recommendation

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

• Way four: chain query

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

The authors recommend using Wrappers’ static method lambdaQuery to build the LambdaQueryWrapper conditional constructor.

The Debug debugging

Yml startup file to enable Mybatis -plus SQL statement full stack printing:

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

The execution effect is as follows:

1 Equivalent query: eq

@test public void testLambdaQueryOfEq() {//eq query // equivalent to 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 ()); }Copy the code

Eq queries are equivalent to equivalent queries in native SQL.

select * from sys_user where user_id = 1
Copy the code

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 ())); }Copy the code

In queries are equivalent to in queries in native SQL

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

3 Wildcard fuzzy query: like

@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 full keyword query ::" + u.getUsername ())); }Copy the code

A like query is equivalent to a like full wildcard fuzzy query in native SQL.

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

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 Right with keyword query ::" + u.getUsername ())); }Copy the code

LikeRight queries are equivalent to native SQL’s like right wildcard fuzzy queries.

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

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 with keyword query ::" + u.getUsername ())); }Copy the code

LikeLeft queries are equivalent to the like left wildcard fuzzy queries of native SQL.

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

6 Conditional query

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

@Test public void testLambdaQueryOfBoolCondition() { UserEntity condition = UserEntity.builder() .sex(1) .build(); //eq or like query LambdaQueryWrapper<UserEntity> LQW = Wrappers. LambdaQuery (); lqw.eq(condition.getSex() ! = null, UserEntity::getSex, 0L) // Satisfy bool whether to query by userName. Like (condition.getusername ()! = null, UserEntity::getUserName, "dun"); List<UserEntity> userList = userMapper.selectList(lqw); Userlist.foreach (u -> system.out.println ("like query ::" + u.getUsername ())); }Copy the code

7 Use OR and to construct 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 ())); }Copy the code

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)
Copy the code

8 good at using page sharpener PageHelpler

@test public void testLambdaPage() {//PageHelper select * from sys_user limit 0,2 int pageNumber = 0; 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 paging query ::" + U.getUsername ())); }Copy the code

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

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

In addition, Mybatis-Plus has its own paging component, 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 usually used when specifying the field to be put back, avoiding null values 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);
Copy the code

Note that when Mybatis-Plus comes with a paging component, you need to configure the PaginationInterceptor paging plug-in.

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

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);
}
Copy the code

03 advanced

1. Association

The Association tag applies to tables and tables that have one-to-one associations, such as users and ID cards. A person will have only one ID number, and vice versa.

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

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>
Copy the code

2. Collection

The Collection tag is applicable to the one-to-many association between tables. For example, a user and an order 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()));
}
Copy the code

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>
Copy the code

Note the relationship between Association and Collection. When compiling a ResultMap, Association comes before Collection.

 <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>
Copy the code

If the two are reversed, an error will be displayed.

3. MetaObjectHandler: MetaObjectHandler

The filling principle of MetaObjectHandler metaobject field filler is to directly set the value for the property of the Entity and provide the default method strategy as follows:

“

If the property has a value, it will not be overwritten; if the fill value is null, it will not be filled. The field must declare the TableField annotation, and the property fill selects the corresponding policy. This declaration tells Mybatis-Plus that it needs to reserve the injection of SQL fields. The TableField annotation specifies that the property must have a value in the corresponding case, or null if there is no value.

Custom fill handler MyMetaObjectHandler needs to declare @Component or @bean injection in Spring Boot. To do so, follow the annotation FieldFill. XXX, for example:

@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;
Copy the code

SetInsertFieldValByName or setUpdateFieldValByName must be used to distinguish the field name and field type from the parent class. You can use the setFieldValByName method of the parent class without any distinction.

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); }}Copy the code

In general, FieldFill.INSERT updates the value of the creation property (creator, creation time) with the parent class’s setInsertFieldValByName method; FieldFill.INSERT_UPDATE updates the value of the modified property (who, when) with the parent setUpdateFieldValByName method; If you want something like FieldFill.INSERT or FieldFill.INSERT_UPDATE to stop working at any time, use the parent’s setFieldValByName to set the value of the properties (creator, creation time, modifier, modification time).

4. Customize SQL

Custom SQL using Wrapper requires mybatis-plus version >= 3.0.7, param parameter name either called ew or annotated @param (constants.wrapper), ${ew.customSQLSegment} does not support generating where statements from an entity inside a Wrapper.

Annotation way

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

The XML configuration

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

4. Advantages and disadvantages of Mybatis-Plus lambda expression

Through the above rich examples and analysis of the underlying implementation principle of lambda, you may ask: “Lambda expressions seem to support only single table operations?”

According to my understanding of Mybatis-Plus 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. The advantage of Lambda expressions is to help developers reduce the amount of repetitive CRUD code written in XML, which is very important nice. Lambda expressions are obviously a great way to make programmers more productive, and I think this is one of the main reasons why I like Mybatis-Plus as a programmer.

However, lambda expressions fall short when it comes to associative queries between multiple tables, because Mybatis-Plus does not provide a conditional constructor similar to join queries.

Advantages of lambda expressions:

1. Single table operation, the code is very simple, really zero configuration, such as no need to write a lot of native SQL code in XML or with annotations (@select)
2. Parallel computing
3. Predictions represent future programming trends

Disadvantages of lambda expressions:

1. Single table operation is not supported for associated query of multiple tables
2. Debugging difficulty
3. Complex underlying logic

Five, the summary

Lambda expressions designed to build complex WHERE query constructors is not a silver bullet. It can solve 80% of your actual project’s development efficiency problems, but it does not support complex large SQL query conditions well. For example, some complex SQL report statistics queries.

Therefore, I recommend lambda expressions for single table operations, LambdaQueryWrapper for queries, and LambdaUpdateWrapper for updates. Multi-table operations or just write some native SQL, where do you write native SQL? Mapper files or annotation-based, such as @select, are fine.

reference

• Mp.baomidou.com/guide/wrapp…
• www.jianshu.com/p/613a6118e…
• Blog.csdn.net/Solitude_w/…
• Blog.csdn.net/weixin_4447…
• Blog.csdn.net/weixin_4449…

Author: apes core source: www.toutiao.com/i6951307172…