An overview,

Although MyBatis can directly operate the database in XML through SQL statements, it is very flexible. But because it operates through SQL statements, it has to write a large number of XML files, which is very troublesome. Mybatis – Plus is a good solution to this problem.

Official definition: Mybatis-Plus (referred to as MP) is a Mybatis enhancement tool, on the basis of Mybatis only do enhancement do not change, to simplify the development, improve efficiency and born. This is the official definition of MyBtis-Plus. For more information and features, please refer to the myBtis-Plus website. So how is it enhanced? The fact is that it already encapsulates some CRUD methods, so we don’t need to write XML, we just call them, just like JPA.

• Official address: mp.baomidou.com/

• The official documents have been written in detail, this article is mainly a self-learning summary of the official documents

Two, preparation work

Spring Boot and Mysql are used here

1. Add a dependency

Add Mybatis-Plus and Mysql driver dependencies to the Spring Boot project:

Maven MybatisPlus dependence in the warehouse: mvnrepository.com/artifact/co…

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.3.2 rainfall distribution on 10-12</version>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

<! -- Lombok simplifies code -->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>
Copy the code

2. Configure the database

Configure the database in the application.yaml file

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/test? useUnicode=true&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=CT T
    username: root
    password: 123
Copy the code

3. Add @MapPerscan annotation

Add @mapperscan to the Spring Boot class and scan the Mapper folder:

@SpringBootApplication
@MapperScan("com.test.mapper")
public class Application {

    public static void main(String[] args) { SpringApplication.run(QuickStartApplication.class, args); }}Copy the code

4. Write entity classes

The entity class will need some annotations, more on that later.

@Data
@TableName("t_user")
public class User {
    private Long id;
    private String name;
    private Integer age;
    private String email;
}
Copy the code

5. Write the Mapper interface

When writing Mapper interface, inherit BaseMapper

parent interface, generic T is the Mapper corresponding entity class, BaseMapper

parent interface has been universal CRUD encapsulation, direct use can, later will also say.

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

At this point, a simple Mybatis-Plus has been integrated

Annotations in entity classes

1. @tablename (TableName annotation)

attribute	type	You must specify	The default value	describe
value	String	no	“”	Specify table name (use this property to specify the table name for the entity class when the table name does not match the entity class name (does not satisfy the hump and underscore mapping)
## 2. @tableid (primary key annotation)
attribute	type	You must specify	The default value	describe
—	—	—	—	—
value	String	no	“”	Primary key field name
type	Enum	no	IdType.NONE	The primary key type

IdType (primary key type)

value	describe
AUTO	The database ID is incremented
NONE	The type is not set to primary key type (will follow global)
INPUT	Enter manually before inserting
ID_WORKER	Globally unique ID (idWorker, snowflake algorithm) (automatically filled only if the insert object ID is empty) (== Default ==)
UUID	Global unique ID (UUID) (automatically filled only if the insert object ID is empty)
ID_WORKER_STR	String Globally unique ID (string representation of idWorker) (automatically populated only if the insert object ID is empty)
## 3. @tableField (non-primary key field annotation)
attribute	type
—	—
value	String
exist	boolean
fill	Enum

BaseMapper<T

1. Insert the

// Insert a record
int insert(T entity);
Copy the code

• Parameter description:

  type	Parameter names	describe
  T	entity	Entity objects

2. Delete the

// Delete the record according to the wrapper condition
int delete(@Param(Constants.WRAPPER) Wrapper<T> wrapper);
// Delete in batches according to the set of ids
int deleteBatchIds(@Param(Constants.COLLECTION) Collection<? extends Serializable> idList);
// Delete based on ID
int deleteById(Serializable id);
// Delete the columnMap based on the condition
int deleteByMap(@Param(Constants.COLUMN_MAP) Map<String, Object> columnMap);
Copy the code

• Parameter description:

  type	Parameter names	describe
  Wrapper	wrapper	Entity object encapsulates operation class (can be null)
  Collection<? extends Serializable>	idList	List of primary key ids (not null or EMPTY)
  Serializable	id	The primary key ID
  Map<String, Object>	columnMap	Table field Map object, the key is the table column name

Update 3.

// Update the record according to the updateWrapper condition
int update(@Param(Constants.ENTITY) T entity, @Param(Constants.WRAPPER) Wrapper<T> updateWrapper);
If a field in the Entity is empty, the field will not appear in the SQL statement
int updateById(@Param(Constants.ENTITY) T entity);
Copy the code

• Parameter description:

  type	Parameter names	describe
  T	entity	Entity object (set conditional value, can be null)
  Wrapper	updateWrapper	Entity object encapsulates operation class (can be null, where entity is used to generate WHERE statement)

Query 4.

• Query only one:

  // Query by ID
  T selectById(Serializable id);
  // Query a record based on the entity condition
  T selectOne(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
  Copy the code

• Query multiple:

  // Query (batch query based on ID)
  List<T> selectBatchIds(@Param(Constants.COLLECTION) Collection<? extends Serializable> idList);
  // The entity class is returned based on the entity condition
  List<T> selectList(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
  // Query (based on the values of certain columns). Note: the keys in the map are the column names of the table
  List<T> selectByMap(@Param(Constants.COLUMN_MAP) Map<String, Object> columnMap);
  // The map class is returned from the Wrapper condition
  List<Map<String, Object>> selectMaps(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
  // Query all records according to the Wrapper conditions. Note: Only the value of the first field is returned
  List<Object> selectObjs(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
  Copy the code

• Paging query (see “9, Paging Query”) :

  // To perform paging queries based on the queryWrapper condition, configure the paging plug-in. See "nine, paging query" for details.
  New page (current, size); current indicates the current page; size indicates the number of items to display per page
  IPage<T> selectPage(IPage<T> page, @Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
  // As in the previous method, the return value is map
  IPage<Map<String, Object>> selectMapsPage(IPage<T> page, @Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
  // Query the total number of records according to the Wrapper condition
  Integer selectCount(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
  Copy the code

• Parameter description:

  type	Parameter names	describe
  Serializable	id	The primary key ID
  Wrapper	queryWrapper	Entity object encapsulates operation class (can be null)
  Collection<? extends Serializable>	idList	List of primary key ids (not null or EMPTY)
  Map<String, Object>	columnMap	Table field map object
  IPage page	Paging query conditions (either rowbounds.default or using Page objects)

5. Conditional constructor

This part of the official document has been written very clear, but also relatively simple, I will say the commonly used good.

The official document: mp.baomidou.com/guide/wrapp…

1. Commonly used AbstractWrapper superclass method, the parent class is used to generate the SQL where conditions:

   == note that == : Each of the following methods basically has an overload method, which adds the: Boolean condition parameter to one of the parameters (with the rest of the parameters moved back one bit) to indicate whether the condition is included in the resulting SQL

   eq(R column, Object val)Eq ("name", "Lao Wang ")-- >name = 'Lao Wang'
   ne(R column, Object val)/ / is not equal to
   gt(R column, Object val)/ / is greater than the
   ge(R column, Object val)/ / > =
   lt(R column, Object val)//<
   le(R column, Object val)/ / < =
   
   between(R column, Object val1, Object val2)BETWEEN = 1 AND 2
   notBetween(R column, Object val1, Object val2)NOT BETWEEN value 1 AND value 2
   
   like(R column, Object val)/ / LIKE '% % values'
   notLike(R column, Object val)//NOT LIKE '% value %'
   likeLeft(R column, Object val)/ / LIKE '% values'
   likeRight(R column, Object val)/ / LIKE 'value %'
   
   isNull(R column)// The field IS NULL
   isNotNull(R column)// The field IS NOT NULLin(R column, Collection<? > value)// Field IN (value.get(0), value.get(1)...)
   in(R column, Object... values)IN (v0, v1,...)
   inSql(R column, String inValue)// Field IN (SQL statement)notIn(R column, Collection<? > value)// NOT IN (value.get(0), value.get(1),...)
   notIn(R column, Object... values)// NOT IN (v0, v1,...)
   notInSql(R column, String inValue)// Field NOT IN (SQL statement)
   
   groupBy(R... columns)// GROUP: GROUP BY field...
   having(String sqlHaving, Object... params)//HAVING (SQL); Example: having (" sum (age) > {0} ", 11) -- - > having sum (age) > 11
   
   orderBy(boolean condition, boolean isAsc, R... columns)// Sort: ORDER BY field,...
   orderByAsc(R... columns)// Sort: ORDER BY field,... ASC
   orderByDesc(R... columns)// Sort: ORDER BY field,... DESC
   
   or()/ / stitching OR; Example: eq (" id ", 1) or (.) eq (" name ", "wang") -- - > id = 1 or name = 'Lao wang'
   
   or(Consumer<Param> consumer)/ / OR nested; Example: the or (I - > appropriate precautions q (" name ", "li bai"). Ne (" status ", "live") -- - > the or (name = 'li bai' and status < > 'alive')
   and(Consumer<Param> consumer)/ / AND nested; Example: and (I - > appropriate precautions q (" name ", "li bai"). Ne (" status ", "live") -- - > the and (name = 'li bai' and status < > 'alive')
   nested(Consumer<Param> consumer)// Normal nesting without AND OR OR
   
   apply(String applySql, Object... params)// Concatenate SQL, using dynamic concatenation parameters without the risk of SQL injection
   last(String lastSql)// Ignore the optimization rules and directly join the SQL to the end of the call. The call can be invoked only once. If the call is repeated, the last call shall prevail
   
   exists(String existsSql)// EXISTS EXISTS (SQL statement)
   notExists(String notExistsSql)NOT EXISTS (SQL statement)
   Copy the code

2. QueryWrapper inherits AbstractWrapper and adds the select() method

   QueryWrapper(T entity)// When you create a QueryWrapper object, you can pass in an entity class. The default condition is =
   
   select(String... sqlSelect)Select ("id", "name", "age");
   Copy the code

3. UpdateWrapper inherits AbstractWrapper and adds the set() and setSql() methods

   UpdateWrapper(T entity)// An entity class can be passed in when the UpdateWrapper object is created. The default condition is =
   
   set(String column, Object val)//SQL SET field; Set ("name", "Lao Li Tou ")
   setSql(String sql)// SET part of SQL; Example: setSql("name = 'old' header '")
   Copy the code

Here are a few examples:

QueryWrapper<User> queryWrapper = new QueryWrapper<>();

//SELECT name,id,age FROM user WHERE age BETWEEN ? AND ? AND ( name = ? ) OR email LIKE ?
queryWrapper.select("name"."id"."age")
			.between("age".20.31)
			.nested(i -> i.eq("name"."lele"))
			.or()
			.like("email"."@qq");
 
//SELECT id,name,age,email FROM user WHERE name like ? and age > ? limit 2
queryWrapper.select("id"."name"."age"."email")
			.apply("name like {0} and age > {1}"."%l%".19)
			.last("limit 2");

UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();

//UPDATE user SET age=? WHERE name = ? 
updateWrapper.set("age".23)
			.eq("name"."lele");
Copy the code

Vi. Common configuration

1. Table field hump name and underline mapping

mybatis-plus.configuration.mapUnderscoreToCamelCase

Whether to enable camel case mapping from the classic database column name A_COLUMN (named by the underscore) to the classic Java property name aColumn (named by the hump). You do not need to use the @tableField annotation to specify the database field name if the database name complies with the rules

• Types: Boolean
• Default: true (enabled by default)

2. Prints the CONFIGURATION of SQL statement logs

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

7. Automatic filling plug-ins

Fields in the table, such as creation time and modification time, can be automatically populated using the following two methods

1. Database level

Set these two fields in the database as follows:

CREATE TABLE `mytest` (
    `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
    `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update time'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

When creating the time field

• DEFAULT CURRENT_TIMESTAMP: When data is inserted, the DEFAULT value of this field is the current time

• ON UPDATE CURRENT_TIMESTAMP: Indicates that the field is updated to the current time each time the data is updated

2. Code level (using MybatisPlus)

MybatisPlus is not allowed to modify the database, so you can use MybatisPlus in two steps:

1. Annotate @tableField (.. fill = FieldFill.INSERT)

   // Add padding to the field
   @TableField(fill = FieldFill.INSERT)// indicates automatic padding during insertion
   private Date createTime;
   
   @TableField(fill = FieldFill.INSERT_UPDATE)// indicates automatic padding during inserts and updates
   private Date updateTime;
   Copy the code

2. The handlers, realizes the meta object processor interface: com. Baomidou. Mybatisplus. Core. Handlers. MetaObjectHandler

   @Slf4j
   @Component
   public class MyMetaObjectHandler implements MetaObjectHandler {
   
       @Override
       public void insertFill(MetaObject metaObject) {
           log.info("start insert fill ....");
           this.strictInsertFill(metaObject, "createTime", LocalDateTime.class, LocalDateTime.now()); // Starting version 3.3.0(recommended)
           this.fillStrategy(metaObject, "createTime", LocalDateTime.now()); // It can also be used (3.3.0 this method has a bug, please upgrade to a later version such as' 3.3.1.8-snapshot ')
           /* Use one of the above, the one below is obsolete (note that strictInsertFill has multiple methods, check the source code for details) */
           //this.setFieldValByName("operator", "Jerry", metaObject);
           //this.setInsertFieldValByName("operator", "Jerry", metaObject);
       }
   
       @Override
       public void updateFill(MetaObject metaObject) {
           log.info("start update fill ....");
           this.strictUpdateFill(metaObject, "updateTime", LocalDateTime.class, LocalDateTime.now()); // Starting version 3.3.0(recommended)
           this.fillStrategy(metaObject, "updateTime", LocalDateTime.now()); // It can also be used (3.3.0 this method has a bug, please upgrade to a later version such as' 3.3.1.8-snapshot ')
           /* Use one of the above, the following is obsolete (note that strictUpdateFill has multiple methods, check the source code for details) */
           //this.setFieldValByName("operator", "Tom", metaObject);
           //this.setUpdateFieldValByName("operator", "Tom", metaObject);}}Copy the code

Eight, optimistic lock plug-in

Based on the official document: Optimistic locking plug-in

MybatisPlus implements optimistic locking in two steps:

1. Add @Version annotation to the field of the entity class

   Note:

   • Only the following data types are supported:int.Integer.long.Long.Date.Timestamp.LocalDateTime
   • Integer typenewVersion = oldVersion + 1
   • newVersionWill be back to writeentity 中
   • Only supportupdateById(id) 与 update(entity, wrapper)methods

   @Version
   private Integer version;
   Copy the code

2. Add an interceptor

   @Bean
   public OptimisticLockerInterceptor optimisticLockerInterceptor(a) {
       return new OptimisticLockerInterceptor();
   }
   Copy the code

Example:

// 1. Query the user information and find version 2
User user = userMapper.selectById(1);
// 2. Modify user information
user.setName("A small code farmer.");
user.setAge("25");
// 3. Perform the update operation
userMapper.updateById(user);
Copy the code

The above code is equivalent to the following SQL statement:

# Query user information firstselect * from tbl_user where id=1; Update tbl_userset name = 'A Small code farmer', age = 25, version = 3 where id = 1 and version = 2
Copy the code

Nine, paging plug-in

The official document: mp.baomidou.com/guide/page….

1. Configure a PaginationInterceptor: PaginationInterceptor:

   / / Spring boot mode
   @Configuration
   public class MybatisPlusConfig {
   
       @Bean
       public PaginationInterceptor paginationInterceptor(a) {
           PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
           // Set the request page to be larger than the maximum page, true to return to the home page, false to continue the request default false
           // paginationInterceptor.setOverflow(false);
           // Set the maximum number of pages. The default value is 500. -1 is not limited
           // paginationInterceptor.setLimit(500);
           // Enable join optimization for count, only for some left joins
           paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));
           returnpaginationInterceptor; }}Copy the code

2. Then use the paging query directly:

   // Parameter 1: current page
   // Parameter 2: page size
   Page<User> page = new Page<>(2.5);
   userMapper.selectPage(page, null);
   
   // The result of the query is placed in the Page object passed in
   page.getRecords().forEach(System.out::println);
   
   // Total is a property of the Page object, indicating the total number of entries
   System.out.println(page.getTotal());
   Copy the code

Page object

Properties of the Page object:

The field name	The field type	instructions	The default value
records	List<T>	A list of the queried data	Empty collection
total	long	The total number of	0
size	long	Number of entries per page	10
current	long	The current page	1
orders	List<OrderItem>	Sort field information	Empty collection
optimizeCountSql	boolean	Automatically tune the COUNT SQL	true
isSearchCount	boolean	Whether to perform the count query	true
hitCount	boolean	Whether the count cache is hit	false

Common Page object methods:

The method name	The return value	instructions
hasPrevious()	boolean	Does the previous page exist
hasNext()	boolean	Whether there is a next page

10. Logical deletion

Before 3.3.0, you need to add interceptors for logical deletion. From 3.3.0, you only need to do some simple configuration

1. Application. Yml add configuration:

   mybatis-plus:
     global-config:
       db-config:
         logic-delete-field: flag  # Global logic removes field values from 3.3.0. See below for details.
         logic-delete-value: 1 # Logical deleted value (default: 1)
         logic-not-delete-value: 0 # Logical undeleted value (default: 0)
   Copy the code

2. To the entity class field@TableLogicannotations

   @TableLogic
   private Integer deleted;
   Copy the code

MybatisPlus will automatically concatenate @tablelogic modified fields to the database, so that the deleted data will not be displayed

Xi. Code generation

Code generation can use the official code: mp.baomidou.com/guide/gener…

You can also use the Easycode to generate code: blog.csdn.net/leisurelen/…

Use of EasyCode plug-in

You can also use the following code to generate code:

1. Add code generation dependencies:

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-generator</artifactId>
    <version>3.3.2 rainfall distribution on 10-12</version>
</dependency>
Copy the code

2. Use the code generator (there are some things that need to be modified)

// Code automatic generator
class GeneratorCodeTest {
    public static void main(String[] args) {
        // We need to build a code automatic generator object
        AutoGenerator mpg = new AutoGenerator();
        // Configure the policy
        
        // 1
        GlobalConfig gc = new GlobalConfig();
        String projectPath = System.getProperty("user.dir");
        gc.setOutputDir(projectPath + "/src/main/java");
        gc.setAuthor("Said the mad god.");
        gc.setOpen(false);
        gc.setFileOverride(false); // Whether to override
        gc.setServiceName("%sService"); // Go to the I prefix of Service
        gc.setIdType(IdType.ID_WORKER);
        gc.setDateType(DateType.ONLY_DATE);
        gc.setSwagger2(true);
        mpg.setGlobalConfig(gc);

        //2. Set data source
        DataSourceConfig dsc = new DataSourceConfig();
        dsc.setUrl("jdbc:mysql://localhost:3306/kuang_community? useSSL = false & useUnicode = true & characterEncoding = utf - 8 & serverTimezone = GMT % 2B8");
        dsc.setDriverName("com.mysql.cj.jdbc.Driver");
        dsc.setUsername("root");
        dsc.setPassword("123456");
        dsc.setDbType(DbType.MYSQL);
        mpg.setDataSource(dsc);

        //3, package configuration
        PackageConfig pc = new PackageConfig();
        pc.setModuleName("blog");
        pc.setParent("com.kuang");
        pc.setEntity("entity");
        pc.setMapper("mapper");
        pc.setService("service");
        pc.setController("controller");
        mpg.setPackageInfo(pc);

        //4
        StrategyConfig strategy = new StrategyConfig();
        strategy.setInclude("blog_tags"."course"."links"."sys_settings"."user_record"."user_say"); // Set the table name to be mapped
        strategy.setNaming(NamingStrategy.underline_to_camel);
        strategy.setColumnNaming(NamingStrategy.underline_to_camel);
        strategy.setEntityLombokModel(true); // Automatic lombok;
        strategy.setLogicDeleteFieldName("deleted");

        // Automatically populate the configuration
        TableFill gmtCreate = new TableFill("gmt_create", FieldFill.INSERT);
        TableFill gmtModified = new TableFill("gmt_modified", FieldFill.INSERT_UPDATE);
        ArrayList<TableFill> tableFills = new ArrayList<>();
        tableFills.add(gmtCreate);
        tableFills.add(gmtModified);
        strategy.setTableFillList(tableFills);

        / / optimistic locking
        strategy.setVersionFieldName("version");
        strategy.setRestControllerStyle(true);
        strategy.setControllerMappingHyphenStyle(true); //localhost:8080 / hello_id_2
        mpg.setStrategy(strategy);
        mpg.execute(); / / execution}}Copy the code