SpringBoot: integrate MyBatis-Plus various use point interface explains super detailed

Introduction to the

MyBatis-Plus is a MyBatis enhanced version of the tool, in MyBatis to expand other functions without changing the basic function, that is, only do encapsulation enhancement do not change, in order to simplify the development of submission efficiency, there is no need to write XML and other SQL statements can be realized

Official document: mp.baomidou.com/guide/

Use SpringBoot to quickly use MyBatis-Plus

Step1: preparation

The Java development environment (JDK) and the response development tool (IDEA) need maven (to download the relevant JAR package) to install Mybatis-Plus plug-in using Springboot integration Mybatis-Plus IDEA

Step2: Create a SpringBoot project

Method 1: go to the official websiteIO/initialize one and import I…Method 2: Create one using IDEA. Spring, Initializer.

Step3: required dependencies

<parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> The < version > 2.3.5. RELEASE < / version > < relativePath / > <! -- lookup parent from repository --> </parent> <! <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> The < version > 1.18.10 < / version > < / dependency > <! <dependency> <groupId>com.baomidou</groupId> <artifactId> Mybatis -plus-boot-starter</artifactId> The < version > 3.4.1 track < / version > < / dependency > <! <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <! <dependency> <groupId>org.apache.velocity</groupId> <artifactId>velocity-engine-core</artifactId> The < version > 2.2 < / version > < / dependency >Copy the code

Step4: Create test data table

create table `user_test1` ( `id` int (11), `username` varchar (60), `age` int (11), `tel` int (11), `create_time` timestamp , `update_time` timestamp , `version` int (11) ); insert into `user_test1` (`id`, `username`, `age`, `tel`, `create_time`, `update_time`, ` version `) values (' 1 ', 'zhang', '18', '180', NULL, NULL, NULL); insert into `user_test1` (`id`, `username`, `age`, `tel`, `create_time`, `update_time`, ` version `) values (' 2 ', 'bill', '20', '137', NULL, NULL, NULL); insert into `user_test1` (`id`, `username`, `age`, `tel`, `create_time`, `update_time`, ` version `) values (' 3 ', 'Cathy', '22', '138', NULL, NULL, NULL);Copy the code

Step5: configure the Mysql data source information in the application.yml file

Spring: a datasource: # driver driver - class - name: com. Mysql. Cj). The JDBC driver url: JDBC: mysql: / / 127.0.0.1:3306 / educationapp? useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai password: root username: Root # console print SQL mybatis - plus: configuration: log - impl: org. Apache. The ibatis. Logging. Stdout. StdOutImplCopy the code

Step6: write the corresponding entity class

Note: If the final test finds that the entity class cannot map the data information, add the get set method

@data // Public class UserTest {private int id; private String username; private int age; private int tel; private Date create_time; private Date update_time; private int version; }Copy the code

Step7: Write a Mapper class that operates on entity classes

Directly inherits BaseMapper, which is a class packaged by Mybatis – Plus

CTRL + left mouse button to access BaseMapper wrapped methods

Step8: testing

Start the class with the MapperScan annotation. Specify a mapper scan path. Do not annotate the mapper layer with @mapper annotations

Instead of unit testing, use control layer mapping path testing

@restController@requestMapping (" API /v1/test/my") Public class TestMyController { @autowired private UserTestMapper UserTestMapper; @getMapping ("list") public Dto test(){// Call BaseMapper CRUD. Can achieve unconditionally query data List < UserTest > List = userTestMapper. The selectList (null); For (UserTest UserTest :list){system.out.println (usertest.getUsername ()); } return DtoUtil.returnDataSuccess(list); }}Copy the code

You can see that the console also prints SQL statements

Commonly used annotations

@tablename is used to define the TableName. @tablename is used to define the TableName. Value Specifies the primary key field name. Type Specifies the primary key type (primary key policy IdType). Primary key policy: Idtype. AUTO The primary key is automatically added and assigned by the system. You do not need to manually enter IdType.NONE The primary key is not set. Idtype. ASSIGN_ID Specifies the ID assigned by the system to numeric data (Long, corresponding to BIGINT in mysql). Idtype. ASSIGN_UUID Specifies the UUID assigned by the system. The UUID is used for String data (String, corresponding to varchar(32) in mysql). @tableField is used to define non-primary key fields of a table. Note: Common attribute: value used to define non-primary key field name exist used to specify whether it is a field in a data table. True means yes, false means no. Fill is used to specify a FieldFill strategy (FieldFill). Field filling strategy: INSERT Fills FieldFill when inserting.UPDATE fills FieldFill.INSERT_UPDATE when updating Fill when inserting, updating. [@TABLelogic] @TABLelogic Is used to define fields in a table for logical deletion (not physical deletion). Value is used to define the value of a field when it is not deleted. Delval is used to define the value of a field when it is deletedCopy the code

user_test1@tablename (value = "user_test1") public class UserTest {/** * Primary key ID Set self-growth policy */ @TableId(value = "id",type = IdType.AUTO) private int id; /** * username */ @tablefield (value = "username") private String username; /** * age */ @tableField (value = "age") private int age; /** ** @tableField (value = "tel") private int tel; /** * create time */ @tableField (value = "create_time") private Date create_time; @tableField (value = "update_time") private Date update_time; /** * Version number (used for optimistic locking, default is 1) * @return */ / @version @tableField (value = "Version ") private int Version; }Copy the code

Mybatis – Plus CRUD operations

Step1: Introduction to Dao layer interface:

public interface UserTestMapper extends BaseMapper<UserTest> {
}

Copy the code

The BaseMapper interface encapsulates a series of common CRUD operations, which can be used directly, rather than using custom XML and SQL statements for CRUD operations (although custom SQL is still necessary according to actual development needs).

Introduction to the BaseMapper method

Insert (T entity); insert(T entity); Int deleteById(Serializable ID); Int deleteByMap(@param (Constants.COLUMN_MAP) Map<String, Object> columnMap); int deleteByMap(@param (Constants. Int delete(@param (Constants.WRAPPER) WRAPPER <T> WRAPPER); int delete(@param (Constants.WRAPPER) WRAPPER <T> WRAPPER); Int deleteBatchIds(@param (Constants.COLLECTION) COLLECTION <? extends Serializable> idList); Note: ID indicates the id of the primary key columnMap indicates the map object of the table field wrapper indicates the entity object wrapper operation class, which can be null. Int updateById(@param (Constants.ENTITY) T ENTITY); int updateById(@param (Constants.ENTITY) T ENTITY); // Modify the entity object according to the ID. int update(@Param(Constants.ENTITY) T entity, @Param(Constants.WRAPPER) Wrapper<T> updateWrapper); // Modify entity objects according to the updateWrapper condition. SelectById (Serializable ID); selectById(Serializable ID); List<T> selectBatchIds(@param (Constants.COLLECTION) COLLECTION <? extends Serializable> idList); List<T> selectByMap(@param (Constants.COLUMN_MAP) Map<String, Object> columnMap); T selectOne(@param (Constants.WRAPPER) WRAPPER <T> queryWrapper); Integer selectCount(@param (constants.wrapper) WRAPPER <T> queryWrapper); List<T> selectList(@param (Constants.WRAPPER) WRAPPER <T> queryWrapper); List<Map<String, Object>> selectMaps(@param (Constants.WRAPPER) WRAPPER <T> queryWrapper); List<Object> selectObjs(@param (Constants.WRAPPER) WRAPPER <T> queryWrapper); List<Object> selectObjs(@param (Constants.WRAPPER) WRAPPER <T> queryWrapper); <E extends IPage<T>> E selectPage(E page, @param (Constants.WRAPPER) WRAPPER <T> queryWrapper); // Query all records (return entity set), Page< E extends IPage<Map<String, Object>>> E selectMapsPage(E page, @param (Constants.WRAPPER) WRAPPER <T> queryWrapper); // Query all records (return map collection), paging note: queryWrapper represents the entity object encapsulates the operation class (can be null) Page represents the paging query criteriaCopy the code

Step2: Service layer interface introduction:

IService further encapsulates the methods of the BaseMapper interface internally (and of course provides more detailed methods). When used, CRUD operations can be performed using the generated Mapper class or the generated Service implementation class. (Of course, custom code execution is also available, without choosing to inherit IService.)

public interface UserTestService extends IService<UserTest> {
	List<UserTest> LikeListUser(String username);
}
Copy the code

IService methods:

【 add data :(add) 】 default Boolean save(T entity); // Call BaseMapper's insert method to add a piece of data. boolean saveBatch(Collection<T> entityList, int batchSize); BatchSize specifies the amount of data to be inserted in a batch. Default value: 1000 【 Add or modify data: (add or modify) 】 Boolean saveOrUpdate(T entity); Default Boolean saveOrUpdate(T entity, Wrapper<T> updateWrapper); // If the id exists, change it. If the ID does not, add data. Boolean saveOrUpdateBatch(Collection<T> entityList, int batchSize); 【 delete data :(delete data) 】 default Boolean removeById(Serializable id); // Call BaseMapper's deleteById method to delete data based on the ID. default boolean removeByMap(Map<String, Object> columnMap); Default Boolean remove(Wrapper<T> queryWrapper); // Call BaseMapper deleteByMap. // Call BaseMapper's delete method to delete objects according to the conditions defined by the entity class. default boolean removeByIds(Collection<? extends Serializable> idList); // Use BaseMapper's deleteBatchIds method to delete a batch. Default Boolean updateById(T entity); // Call BaseMapper's updateById method and change it according to the ID. default boolean update(T entity, Wrapper<T> updateWrapper); // Call BaseMapper's update method to modify the entity object according to the updateWrapper condition. boolean updateBatchById(Collection<T> entityList, int batchSize); Default T getById(Serializable ID); // Call BaseMapper's selectById method to return data based on the primary key ID. default List<T> listByIds(Collection<? extends Serializable> idList); // Call BaseMapper selectBatchIds to batch query data. default List<T> listByMap(Map<String, Object> columnMap); // Call BaseMapper selectByMap to query default T getOne(Wrapper<T> queryWrapper) based on table field conditions; // Return a record (held by the entity class). Map<String, Object> getMap(Wrapper<T> queryWrapper); // Return a record (map save). default int count(Wrapper<T> queryWrapper); // Return the number of records based on the condition. default List<T> list(); // Return all data. default List<T> list(Wrapper<T> queryWrapper); // Call BaseMapper's selectList method to query all records (return entity collection). default List<Map<String, Object>> listMaps(Wrapper<T> queryWrapper); // Call BaseMapper's selectMaps method to query all records (return a map collection). default List<Object> listObjs(); Return all records, but only the value of the first field. default <E extends IPage<T>> E page(E page, Wrapper<T> queryWrapper); // Call BaseMapper selectPage, Default <E extends IPage<Map<String, Object>>> E pageMaps(E page, Wrapper<T> queryWrapper); // Call BaseMapper's selectMapsPage method. List is used to return multiple records. Count is used to return the total number of records. Page is used for paging query. Default QueryChainWrapper<T> query(); // LambdaQueryChainWrapper<T> lambdaQuery(); // Support changes to Lambda expressions default UpdateChainWrapper<T> update(); LambdaUpdateChainWrapper<T> lambdaUpdate(); // Support the modification of Lambda expressions note: query indicates the query. Update indicates the modification of Lambda. Lambda indicates the internal support for Lambda. Query ().eq("column", value).one(); lambdaQuery().eq(Entity::getId, value).list(); update().eq("column", value).remove(); lambdaUpdate().eq(Entity::getId, value).update(entity);Copy the code

Step3: condition constructor (Wrapper, define where condition)

Introduction: Various wrappers, such as queryWrapper and updateWrapper, appear in the arguments to the interface methods described above. Wrapper is used to define various query conditions (where).

Wrapper condition construct abstract class AbstractWrapper Query condition Wrapper used to generate WHERE statement in SQL. -- QueryWrapper Entity object encapsulates the action class and is used for queries. UpdateWrapper Update conditional encapsulates the action class for updates. -- AbstractLambdaWrapper uses Lambda expressions to encapsulate wrappers -- LambdaQueryWrapper uses Lambda syntax to encapsulate conditions for queries. LambdaUpdateWrapper uses Lambda syntax to encapsulate conditions for updatesCopy the code

Common conditions for Wrapper

General conditions: 【 】 compare size: (=, < >, >, > =, <, < =) 】 eq (R column, Object val); / / equivalent to =, example: eq (" name ", "wang") -- - > name = 'Lao wang' ne (R column, Object val); / / equivalent to the < >, example: ne (" name ", "wang") -- - > name < > 'Lao wang' gt (R column, Object val); / / equivalent to the >, example: gt (" name ", "wang") -- - > name > 'Lao wang ge (R column, Object val); / / equivalent to the > =, example: ge (" name ", "wang") -- - > name > = 'Lao wang' lt (R column, Object val); / / equivalent to the <, example: lt (" name ", "wang") -- - > name < 'Lao wang' le (R column, Object val); // Equivalent to <=, for example: Le ("name", "id ") --> name <= 'id' 【 表 :(between, not between, in, not in) 】 between(R column, Object val1, Object val2); // Between a and B, e.g.  between("age", 18, 30) ---> age between 18 and 30 notBetween(R column, Object val1, Object val2); // Equivalent to not between a and B, e.g. NotBetween ("age", 18, 30) --> age not between 18 and 30 in(R column, Object... values); // Equivalent to field IN (v0, v1...) , example: in (" age ", {1, 2, 3}) - > age in (1, 2, 3) notIn (R column, the Object... values); // Equivalent to field NOT IN (v0, v1...) , example: notIn (" age ", {1, 2, 3}) - > age not in (1, 2, 3) inSql (R column, the Object... values); // Equivalent to field IN (SQL statement), for example: inSql("id", "select id from table where id < 3") ---> id in (select id from table where id < 3) notInSql(R column, Object... values); // equivalent to NOT IN (SQL statement) [fuzzy matching :(like)] like(R column, Object val); // equivalent to LIKE '% value %', e.g. : LIKE ("name", "king ") --> name LIKE '% king %' notLike(R column, Object val); // equivalent to NOT LIKE '% value %', e.g. NotLike ("name", "king ") --> name NOT LIKE '% king %' likeLeft(R column, Object val); // equivalent to LIKE '% value ', e.g. LikeLeft ("name", "king ") --> name LIKE '% king 'likeRight(R column, Object val); // equivalent to LIKE '%', e.g. : likeRight("name", "king ") --> name LIKE' %' [null: (isNull, isNotNull)] isNull(R column); // IS NULL, for example: isNull("name") --> name IS NULL isNotNull(R column); IsNotNull ("name") --> name IS NOT NULL [group, having, order] groupBy(R... columns); // Equivalent to GROUP BY,... , e.g. GroupBy ("id", "name") --> group by id,name orderByAsc(R... columns); // Equivalent to the ORDER BY field... OrderByAsc ("id", "name") --> Order by id ASC,name ASC orderByDesc(R... columns); // Equivalent to the ORDER BY field... OrderByDesc (" ID ", "name") --> Order by ID DESC,name DESC having(String sqlHaving, Object... params); // Equivalent to HAVING (SQL statement), for example: Having ("sum(age) > {0}", 11) --> having sum(age) > 11 【 CONCatenate, nested, apply 】 or(); Eq ("id",1).or().eq("name"," old wang ") --> id = 1 or name = 'old Wang' or(Consumer<Param> Consumer); // equivalent to or(a or/and b), or nested Ex. : Or (I - > appropriate precautions q (" name ", "li bai"). Ne (" status ", "live") -- - > the or (name = "li bai" and the status of < > 'alive') and (Consumer < Param > Consumer); // equivalent to and(a or/and b), and nested. Ex. : And (I -> I.E Q ("name", "LI Bai ").ne("status", > (name = 'libai' and status <> 'libai ') nested(Consumer<Param> Consumer); // equivalent to (a or/and b), common nesting. Ex. : Nested (I - > appropriate precautions q (" name ", "li bai"). Ne (" status ", "live")) - > (name = "li bai" and the status of < > 'alive') apply (String applySql, Object... params); If params is not used, SQL injection may exist. Example: apply("date_format(dateColumn,'%Y-%m-%d') = {0}", "2008-08-08") ---> date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'") last(String lastSql); // Ignore optimization rules and splice directly to the end of SQL. exists(String existsSql); // Concatenate an EXISTS statement. Ex. : Exists ("select id from table where age = 1") --> exists(select ID from table where age = 1) 】 the select (String... sqlSelect); // Used to define fields that need to be returned. Example: select("id", "name", "age") --> select ID, name, age select(Predicate<TableFieldInfo> Predicate); // Lambda expression, filter required fields. lambda(); // return a LambdaQueryWrapper (String column, Object val); // Set the value of the set field. Example: set("name", null) --> set name = null etSql(String SQL); // Set the value of the set field. Example: setSql (" name = 'Lao li head ") -- - > set name =' Lao li head 'lambda (); // Return a LambdaUpdateWrapperCopy the code

A simple test Impl implementation class inherits the Service interface

@service Public class UserTestServiceImpl implements UserTestService {@autoWired UserTestMapper userTestMapper; @override public List<UserTest> LikeListUser(String username) {// Step1: create a QueryWrapper object QueryWrapper<UserTest> QueryWrapper = new QueryWrapper<>(); Select ("username","age") //.eq("age","20") // query condition. Like ("username",username); Fuzzy query / / / / Step3: execute the query List < UserTest > userTestList = userTestMapper. SelectList (queryWrapper); return userTestList; }}Copy the code

The Controller control layer

@getMapping ("CrudTestUser") public Dto CrudTestUser(@requestParam ("username") String username){ Is my own / / Dto and DtoUtil encapsulate utility class comrades directly back to the List of the return DtoUtil. ReturnDataSuccess (userTestService. LikeListUser (username)); }Copy the code

Paging plug-in usage

Step1: configure the paging plug-in

Write a configuration class that uses the @bean annotation internally to give the PaginationInterceptor to the Spring container for management.

Step2: customize the Service layer interface

public interface UserTestService{
    List<UserTest> pagelist(int current,int size);
}
Copy the code

Step3: ServiceImpl implements the class retrieval interface

Write paging code. New a Page object directly. The object needs to pass two arguments (the current Page, the number of pages to display on each Page). Call the paging query method provided by Mybatis – Plus, which encapsulates the paging query data into a Page object.

@Service public class UserTestServiceImpl implements UserTestService { @Autowired UserTestMapper userTestMapper; @override public List<UserTest> pagelist(int current, int size) { <UserTest> Page = new Page<>(current,size); // Page<User> page = new Page<>(2, 5); QueryWrapper<UserTest> queryWrapper = new QueryWrapper<>(); / / Step2: Calling mybatis - plus provide paging query methods. Remove getRecords collection data do not take returns some other value List < UserTest > userTestList = userTestMapper. SelectPage (page, null).getRecords(); System.out.println(page.getCurrent())); System.out.println(page.gettotal ())); System.out.println(page.getsize ())); // Get the number of entries per page by default 10 system.out.println (page.getRecords()); System.out.println(page.getPages()); System.out.println(page.hasnext ())); System.out.println(page.hasprevious ())); Return userTestList; }}Copy the code

Step4: Control layer test

@RestController @RequestMapping("api/v1/test/my") public class TestMyController { @Autowired private UserTestService userTestService; @getMapping ("pagelist") public Dto pagelist(@requestParam ("current") int current, @requestParam ("size"))  int size){ List<UserTest> userTestList = userTestService.pagelist(current,size); Return dtoutil. returnSuccess(" Return normal data ",userTestList); }}Copy the code

Automatic data filling function

When data is added or modified, it is populated in the same way each time. For example, the creation time and modification time of data. Mybatis- Plus supports automatic filling of data for these fields.

The data table for you has the creation time and modification time fields

I’m going to add it manually just to show you the results

Step1: Service Adds an interface

Public interface UserTestService {/** * add method * @param userTest * @return */ int addUser(userTest userTest); }Copy the code

Step2: ImpL implementation class

@service public class UserTestServiceImpl implements UserTestService {@autoWired UserTestMapper userTestMapper; @override public int addUser(userTest userTest) {userTest userTest1 = new  UserTest(); // Add user name usertest1.setUsername (usertest.getUsername ()); // Add the current system time usertest1.setcreate_time (new Date()); int num = userTestMapper.insert(userTest1); // Insert returns int num; }}Copy the code

Step3: Controller test

@RestController @RequestMapping("api/v1/test/my") public class TestMyController { @Autowired private UserTestService userTestService; @PostMapping("addUser") public Dto addUser(@RequestBody UserTest userTest){ int num = userTestService.addUser(userTest);  If (num==0){return dtoutil. returnSuccess(" add failed "); }else {return dtoutil. returnSuccess(" added successfully "); }}}Copy the code

Let’s see what happens when we add the time parameter manually

Use autofill

Step1: annotate the fields that need to be filled with the @tablefield annotation

Step2: Customize the class, implement the MetaObjectHandler interface, and rewrite the method

@Component public class MyMetaObjectHandler implements MetaObjectHandler {/** * * @param metaObject */ @override public void insertFill(metaObject) { this.strictInsertFill(metaObject, "create_time", Date.class, new Date()); } @Override public void updateFill(MetaObject metaObject) { this.strictUpdateFill(metaObject, "update_time", Date.class, new Date()); }}Copy the code

Step3: Remove manual add data and test again

@Service public class UserTestServiceImpl implements UserTestService { @Autowired UserTestMapper userTestMapper; @param userTest @return */ @override public int addUser(userTest userTest) {int num = userTestMapper.insert(userTest); return num; }}Copy the code

Logic to delete

Data can be deleted either physically or logically. Physical deletion means that data is directly deleted from the database without retention. Logical deletion refers to modifying a field of data so that it is represented as deleted, rather than deleting the data, leaving the data in the database but not showing it when querying (filtering out the data when querying).

Add a field to the table: delete_flag, which indicates whether the data has been logically deleted.

Step1: use logical deletion

You can define an automatic population rule with an initial value of 0,0 for undeleted, and 1 for deleted

Step2: Service Add an interface

Public interface UserTestService {/** * delete method * @param id * @return */ int deleteUser(int id); }Copy the code

Step3: impl implementation class

@Service public class UserTestServiceImpl implements UserTestService { @Autowired UserTestMapper userTestMapper; @ Override public int deleteUser (int id) {/ / may due to the different versions The delete method may be different here Specific look at the official documentation int num = userTestMapper. DeleteById (id); return num; }}Copy the code

Step4: testing

Num = 0; num = 1

You can see that the data in the database does not disappear. Instead, the delete_flag field is changed to 0. When the SQL is executed, the change statement is executed

After executing the query, we find that there is no delete_flag=1 data, because we define the value of the logical deletion annotation delval on the property, which can be defined according to the database design

Using physical Deletion

Step1: Delete the TableLogic annotation, which will be physically deleted

//@TableLogic(value = "0", delval = "1")
Copy the code

Step2: perform the delete method again

As you can see, the SQL statement is DELETE, and the query is executed again without the condition delete_flag

Optimistic lock implementation

Use the Version mechanism to implement the implementation roadmap: 1. When fetching data, obtain the current version 2. Set version = newVersion where version = oldVersion 4 when updating. If the version is incorrect, the update fails

Step1: configure the optimistic lock plug-in

@component // Public class MyConfig {/** * optimistic lock plugin * @return optimistic lock plugin instance */  @Bean public OptimisticLockerInterceptor optimisticLockerInterceptor() { return new OptimisticLockerInterceptor(); }}Copy the code

Step2: Define a database field version

Step3: annotate the corresponding entity class with the @version annotation. Data can be filled automatically with @tableField.

/** * Version number (for optimistic locking, default is 1) */ @version@tableField (fill = FieldFill.INSERT) private Integer Version; Override public void insertFill(MetaObject MetaObject) {this.strictinsertfill (MetaObject, "version", Integer.class, 1); }Copy the code

Step4: Simple test

@Test public void testVersion() { User user = new User(); user.setName("tom").setAge(20).setEmail("[email protected]"); // Insert userservice.save (user); Userservice.list ().foreach (system.out ::println); user.setName("jarry"); Userservice. update(user, null); Userservice.list ().foreach (system.out ::println); }Copy the code

The code generator will not be demonstrated here, because MyBatis-Plus depends on different, many methods will be changed, you can go to the official website to check

Please give me a thumbs-up