Improve the development efficiency of a mybatis development artifact

In the past, when using Mybatis, it is often necessary to configure XML mapping files first, and then each SQL operation needs to be written manually. For some complex SQL operations, such operations are indeed necessary, but if only some very simple insert, update, delete, Statements like SELECT require developers to spend extra time writing them manually, which can be time-consuming and laborious.

Is it possible to customize a powerful framework for Mybatis that can automatically generate some simple SQL functions for us and support us to customize SQL Settings?

Mybatis Plus was born.

Mybatis Plus is a framework technology specifically aimed at the traditional Mybatis development SQL need manual mapping configuration tedious defects, this framework technology provides a very rich API for developers to use, on the basis of MyBatis only enhance without change, to simplify development and improve efficiency.

Mybatis Plus in the end how convenient it, we do not further talk, directly hands-on code examples to demonstrate:

First we need to import some relevant POM dependency configurations

<? xml version="1.0" encoding="UTF-8"? > <project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> < modelVersion > 4.0.0 < / modelVersion > < groupId > com. The sise < / groupId > < artifactId > mybatis - plus < / artifactId > < version > 1.0 - the SNAPSHOT < / version > < the parent > < groupId > org. Springframework. Boot < / groupId > The < artifactId > spring - the boot - starter - parent < / artifactId > < version > 1.5.9. RELEASE < / version > < relativePath / > <! -- lookup parent from repository --> </parent> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> < project. Reporting. OutputEncoding > utf-8 < / project. Reporting. OutputEncoding > < Java version > 1.8 < / Java version > </properties> <dependencies> <dependency> <groupId>com.baomidou</groupId> < artifactId > mybatisplus - spring - the boot - starter < / artifactId > < version > 1.0.5 < / version > < / dependency > < the dependency > <groupId>com.baomidou</groupId> <artifactId>mybatis-plus</artifactId> <version>2.3</version> </dependency> <dependency> < the groupId > org. Mybatis. Spring. The boot < / groupId > < artifactId > mybatis - spring - the boot - starter < / artifactId > < version > 1.3.1 < / version >  </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> </dependencies> </project>Copy the code

After importing the JAR package, in order to facilitate the test, we first set up the relevant data table information in the database

CREATE TABLE `teacher` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `teacher_name` varchar(60) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `teacher_pwd` varchar(60) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=gbk;Copy the code

The entity class corresponding to this table:

package com.sise.model;

import com.baomidou.mybatisplus.annotations.TableName;

/**
 * @author idea
 * @data 2019/5/24
 */
@TableName(value = "teacher")
public class Teacher {
    private int id;

    private String teacherName;

    private String teacherPwd;

    public int getId() {
        return id;
    }


    public Teacher() {
    }

    public Teacher(int id) {
        this.id = id;
    }

    public Teacher setId(int id) {
        this.id = id;
        return this;
    }

    public String getTeacherName() {
        return teacherName;
    }

    public Teacher setTeacherName(String teacherName) {
        this.teacherName = teacherName;
        return this;
    }

    public String getTeacherPwd() {
        return teacherPwd;
    }

    public Teacher setTeacherPwd(String teacherPwd) {
        this.teacherPwd = teacherPwd;
        return this;
    }

    @Override
    public String toString() {
        return "Teacher{" +
                "id=" + id +
                ", teacherName='" + teacherName + ' '' + ", teacherPwd='" + teacherPwd + ''' + '}'; }}Copy the code

Usually we will customize a Dao layer during development. Mybatis Plus provides an interface called BaseMapper, which has provided a lot of ENCAPSULATION of CRUD operation functions. Take a closer look at the contents of this interface:

Copyright (c) 2011-2020, Hubin ([email protected]). * <p> * Licensed under the Apache License, Version 2.0 (the"License"); you may not
 * use this file except incompliance with the License. You may obtain a copy of * the License at * <p> * http://www.apache.org/licenses/LICENSE-2.0 * < p > * Unless required by applicable law or agreed toin writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 * License forthe specific language governing permissions and limitations under * the License. */ package com.baomidou.mybatisplus.mapper; import java.io.Serializable; import java.util.Collection; import java.util.List; import java.util.Map; import org.apache.ibatis.annotations.Param; import org.apache.ibatis.session.RowBounds; /** * <p> * /** * <p> This Mapper supports id generics * </p> * * @author hubin * @date 2016-01-23 */ public interface BaseMapper<T> {/** ** <p> * Inserts a record * </p> ** @param entity entity object * @returnint */ Integer insert(T entity); / * * * < p > * insert a record * < / p > @ param * * * @ entity entity objectreturnint */ Integer insertAllColumn(T entity); / * * * < p > * delete by ID * < / p > * * * @ @ param ID primary key IDreturnint */ Integer deleteById(Serializable id); /** ** <p> * Delete the record according to the columnMap condition * </p> ** @param columnMap table field map object * @return int
     */
    Integer deleteByMap(@Param("cm") Map<String, Object> columnMap); /** * <p> * Delete records based on entity condition * </p> ** @param Wrapper Entity object encapsulates operation class (can be null) * @return int
     */
    Integer delete(@Param("ew") Wrapper<T> wrapper); /** ** <p> ** @param idList Primary key ID list * @return int
     */
    Integer deleteBatchIds(@Param("coll") Collection<? extends Serializable> idList); /** ** <p> * modify according to ID * </p> ** @param entity Entity * @return int
     */
    Integer updateById(@Param("et") T entity); /** ** <p> * modify according to ID * </p> ** @param entity Entity * @return int
     */
    Integer updateAllColumnById(@Param("et") T entity); /** * <p> * according towhereEntity condition, updated record * </p> * * @param Entity Entity object * @param wrapper Entity object encapsulates operation class (can be null) * @return
     */
    Integer update(@Param("et") T entity, @Param("ew") Wrapper<T> wrapper); /** * <p> * according towhereEntity condition, update record * </p> * * @paramsetStr  setThe string * @param Wrapper entity object encapsulates the action class (which can be null) * @return
     */
    Integer updateForSet(@Param("setStr") String setStr, @Param("ew") Wrapper<T> wrapper); / * * * according to the ID of the < p > * * < / p > * * * @ @ param ID primary key IDreturnT */ T selectById(Serializable id); /** ** <p> ** @param idList Primary key ID list * @return List<T>
     */
    List<T> selectBatchIds(@Param("coll") Collection<? extends Serializable> idList); /** * <p> * Query (based on columnMap condition) * </p> ** @param columnMap table field map object * @return List<T>
     */
    List<T> selectByMap(@Param("cm") Map<String, Object> columnMap); /** ** <p> * query a record based on the entity condition * </p> ** @param entity object * @return T
     */
    T selectOne(@Param("ew") T entity); /** ** <p> * Query the total number of records according to the Wrapper condition * </p> ** @param Wrapper entity object * @return int
     */
    Integer selectCount(@Param("ew") Wrapper<T> wrapper); /** ** <p> * query all records based on entity condition * </p> ** @param wrapper Entity object encapsulates operation class (can be null) * @return List<T>
     */
    List<T> selectList(@Param("ew") Wrapper<T> wrapper); /** ** <p> * query all records according to the Wrapper condition * </p> ** @param Wrapper entity object encapsulates the operation class (can be null) * @return List<T>
     */
    List<Map<String, Object>> selectMaps(@Param("ew") Wrapper<T> wrapper); /** * <p> ** Return only the value of the first field * </p> ** @param Wrapper entity object encapsulates the operation class (can be null) * @return List<Object>
     */
    List<Object> selectObjs(@Param("ew") Wrapper<T> wrapper); /** * <p> * Query all records (and page over) * </p> * * @param rowBounds paging query criteria (can be rowbound.default) * @param Wrapper entity object (can be null) * @return List<T>
     */
    List<T> selectPage(RowBounds rowBounds, @Param("ew") Wrapper<T> wrapper); /** * <p> * according to the Wrapper condition, Query all records (and page over) * </p> * * @param rowBounds paging query criteria (can be rowbound.default) * @param Wrapper entity object encapsulates the action class * @return List<Map<String, Object>>
     */
    List<Map<String, Object>> selectMapsPage(RowBounds rowBounds, @Param("ew") Wrapper<T> wrapper);

}Copy the code

These built-in functions have been customized in advance, so for some common simple SQL we can avoid the use of manual Mosaic to achieve, greatly improve the development efficiency of developers, combined with the teacher object mentioned above, we define a Mapper interface to achieve CRUD operations:

package com.sise.dao;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.sise.model.Teacher;
import org.apache.ibatis.annotations.Mapper;


/**
 * @author idea
 * @data 2019/5/24
 */
@Mapper
public interface TeacherMapper extends BaseMapper<Teacher> {
}Copy the code

To facilitate testing, I chose to reference the DAO function directly in the Controller.

1. Pre-defined insertion function:

BaseMapper encapsulates the already defined INSERT statement, which can be called directly without the need to manually write SQL

@GetMapping(value = "/insert")
    public void insert(){ Teacher teacher=new Teacher(); teacher.setTeacherName(createRandomStr(6)); teacher.setTeacherPwd(createRandomStr(6)); teacherMapper.insert(teacher); } /** * generates a random string ** @return
     */
    private static String createRandomStr(int length){
        String str="abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ";
        Random random=new Random();
        StringBuffer sb=new StringBuffer();
        for(int i=0; i<length; i++){ int number=random.nextInt(str.length()); sb.append(str.charAt(number)); }return sb.toString();
    }Copy the code

Generated SQL statement:

INSERT INTO teacher ( id, teacher_name, teacher_pwd ) VALUES ( 0, 'mNJXIf'.'LKTnam' );Copy the code

2. Pre-defined deletion functions

BaseMapper provides a defined delete function,

   @GetMapping(value = "/delete")
    public void delete(){
        Teacher  teacher=new Teacher();
        teacher.setId(11);
        EntityWrapper entityWrapper=new EntityWrapper(teacher);
        teacherMapper.delete(entityWrapper);
    }Copy the code

Generated SQL statement:

DELETE FROM teacher WHERE id=11;Copy the code

3. Pre-defined updates

BaseMapper defines the related update function, but there is a point to pay attention to when performing an update. Take a look at the following code

    @GetMapping(value = "/update")
    public void updateEntityWrapper EntityWrapper =new EntityWrapper(new Teacher(1)); Teacher Teacher =new Teacher(); teacher.setTeacherPwd("new-pwd");
        teacherMapper.update(teacher,entityWrapper);
    }Copy the code

Generated SQL statement:

UPDATE teacher SET teacher_pwd='new-pwd'  WHERE id=1;Copy the code

Mybatis – Plus provides an object wrapper called EntityWrapper class to determine the incoming query parameters by passing in the relevant Entity. The use of this class reminds me of hibernate. I have to say that the design of this feature has really helped the development efficiency of developers.

Mybatis – Plus saves a lot of time by wrapping EntityWrapper in XML.

4. Query information based on the specified ID

Mybatis – Plus also provides the default keyword query function, you can query parameters through the Entity form injection, very convenient.

  @GetMapping(value = "/selectAllById")
    public Teacher selectByTeacherName(int id){
        return teacherMapper.selectOne(new Teacher(id));
    }Copy the code

Generated SQL statement:

SELECT id,teacher_name AS teacherName,teacher_pwd AS teacherPwd FROM teacher WHERE id=0;Copy the code

5. Use Map to query multiple keywords

In addition to using Entity keyword for query, you can also use Map for multi-keyword search, as shown in the following code:

    @GetMapping(value = "/selectAllByMap")    public List<Teacher> selectAllByEntity(String name){        Map<String,Object> hashMap=new HashMap<>();        hashMap.put("teacher_name",name);        return teacherMapper.selectByMap(hashMap);    }Copy the code

Note that the key values in the map must be named the same as the fields in the table.

Generated SQL statement:

SELECT id,teacher_name AS teacherName,teacher_pwd AS teacherPwd FROM teacher WHERE teacher_name = 'qwe';Copy the code

6. Statistical query

    @GetMapping(value = "/selectCountByEntity")    public int selectCount(String name){        Teacher teacher=new Teacher();        teacher.setId(1);        teacher.setTeacherName(name);        EntityWrapper<Teacher> entityWrapper=new EntityWrapper<>(teacher);        return teacherMapper.selectCount(entityWrapper);    }Copy the code

Generated SQL statement:

SELECT COUNT(1)  FROM teacher  WHERE id=1 AND teacher_name='qwe';Copy the code

7. Paging query

In the actual development, the paging query function has always been a very common point to use, in Mybatis – Plus, he provides a class called Page for us to use, the corresponding code is shown as follows:

   @GetMapping(value = "/selectAllInPage")    public List<Teacher> selectAllInPage(int pageNumber,int pageSize){        Page<Teacher> page =new Page<>(pageNumber,pageSize);        EntityWrapper<Teacher> entityWrapper = new EntityWrapper<>();        entityWrapper.ge("id", 1);        return teacherMapper.selectPage(page,entityWrapper);    }Copy the code

Generated SQL statement:

SELECT id,teacher_name AS teacherName,teacher_pwd AS teacherPwd  FROM teacher  WHERE (id >= 1) LIMIT 0,1;Copy the code

Mybatis – Plus uses the traditional limit physical paging technique.

8. In the query

SelectBatchIds = selectBatchIds = selectBatchIds = selectBatchIds = selectBatchIds = selectBatchIds = selectBatchIds = selectBatchIds

    @GetMapping(value = "/selectInIdArr")    public List<Teacher> selectInIdArr(){        List<Integer> idList=new ArrayList<>();        idList.add(1);        idList.add(10);        idList.add(11);        return teacherMapper.selectBatchIds(idList);    }Copy the code

Generated SQL statement:

SELECT id,teacher_name AS teacherName,teacher_pwd AS teacherPwd FROM teacher WHERE id IN ( 1 , 10 , 11 );Copy the code

9. Query complex conditions

For complex multi-condition queries, the EntityWrapper support provided inside Mybatis – Plus has many rich query API features to use.

For ease of understanding, the following code examples are used to explain