Quickly master MyBatis plus, an enhanced version of MyBatis

What is mybatis plus

It's a MyBatis (opens new window) On the basis of MyBatis, only enhancements are made without changes

MyBatis Puls benefits:

There is no need to add, delete, modify and query sql
Built in code generator, paging plug-in, etc
Provide a functional conditional constructor for rapid sql free development
Do some small and medium-sized projects with fast development efficiency

Disadvantages:

Slow efficiency of large projects and distributed projects

quick get start

Import dependent

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.4.0</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
</dependency>

Four element configuration

spring.datasource.url=jdbc:mysql://localhost:3306/mp?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true
spring.datasource.username=root
spring.datasource.password=admin
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
# Configure slq print log
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

Create mapper and inherit basemapper < T> Interface, no need to write mapping file
```
@Repository
public interface EmployeeMapper extends BaseMapper<Employee> {
}
```
Create a custom service interface and inherit iservice < T> Interface

Create a service implementation class, inherit serviceimpl < < mapper interface corresponding to entity object, entity class object >, and implement a user-defined interface
```
public interface IEmployeeService extends IService<Employee> {
}  
```
```
@Service
public class EmployeeService extends ServiceImpl<EmployeeMapper, Employee> implements IEmployeeService{
}
```
Package scanning is required when starting the container
```
@MapperScan(basePackages = "cn.kjcoder.mp.mapper")
```

test

@SpringBootTest
public class CrudTest {
    @Autowired
    private EmployeeMapper employeeMapper;
    @Test
    public void selectList(){
        List<Employee> employees = employeeMapper.selectList(null);
        System.out.println(employees);
    }
}

Code structure:

public interface UserMapper extends BaseMapper<User>{
    
}

public interface IUserService extends IService<User>{
    
}

public class UserServiceImpl extends ServiceImpl<UserMapper,User > implements IUserService{
    
}

reflection

★ question 1:EmployeeMapper does not define crud method. Why can it be used directly in the test class*

EmployeeMapper inherits the BaseMapper interface. Naturally, you can use the parent BaseMapper interface to define crud methods

★ why can CRUD operations be performed when crud sql statements are not written in the project?

★ principle of mybatis plus

Mybatis plus automatically splices sql. It is observed that the table name is the same as the class name of the entity object operated by the mapper interface, and the column name is the same as the attribute name of the entity object operated by the mapper interface. Then, you can use the introspection mechanism and reflection in java to parse the class name of the entity object as the table name and the attribute field as the column name, The obtained table and column names are automatically spliced into sql statements and injected into the mybatis container

Common notes

@TableName(xxxx): used to map the table name corresponding to the current class when the table name is inconsistent with the class name. It is consistent with the entity class name by default

@TableId(value="id", type= IdType.AUTO): acts on the field of the class and specifies the type as self growth id policy

@TableField(value="ename"): used on the field of the class. When the attribute name in the class is inconsistent with the field name in the table, use this annotation for mapping

@TableField(exist = false): acts on the field of the class, indicating that the current attribute does not participate in sql splicing and column mapping

Print log

Mode 1:

logging.level.cn.kjcoder.mp.mapper=debug

Mode 2:

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

Mapper interface method

preservation

insert(domain)

/**
 * Insert a record
 * INSERT INTO employee ( name, password, email, age, admin, dept_id ) VALUES ( ?, ?, ?, ?, ?, ? )
 */
Employee employee = new Employee();
employee.setName("lisa");
employee.setPassword("123455");
employee.setEmail("lisa@qq.com");
employee.setAge(20);
employee.setDeptId(2L);
employee.setAdmin(0);
employeeMapper.insert(employee);

to update

updateById(domain): if the attribute value of the domain object to be updated is null, the attribute value will not participate in sql splicing. If it is a non null field and mybatis plus thinks it has a value, it will participate in sql splicing

Solution:

Change basic data type to package type

/**
 * Change the user name with id=1 to hkj
 * UPDATE employee SET name=?, age=?, admin=? WHERE id=?
 */
Employee employee = new Employee();
employee.setId(1L);
employee.setName("gd");
employeeMapper.updateById(employee);

First query, then set, and then update

/**
 * UPDATE employee SET name=?, password=?, email=?, age=?, admin=?, dept_id=? WHERE id=?
 */
Employee employee = employeeMapper.selectById(1L);
employee.setName("hkj");
employeeMapper.updateById(employee);

Use the update(null, wrapper) method

update(null,Wrapper): use the sql statement of custom update method to update some fields

/**
 * Update the age of the employee with name=hkj to 18
 * UPDATE employee SET age=? WHERE (name = ?)
 */
UpdateWrapper<Employee> wrapper = new UpdateWrapper<>();
wrapper.eq("name","hkj");
wrapper.set("age",18);
employeeMapper.update(null,wrapper);

***Selection question: * * * if the update condition is id and all fields are updated, use updateById

If you only need to update some fields, use update

saveOrUpdate(domain,UpdateWapper):
When the data is automatically generated by the primary key, you must write the UpdateWrapper, or you must insert it all the time! It will not be updated at all, because the default query is by id. when saveOrUpdate does not use the condition constructor, it will first query according to the primary key. If the result is 0, the insert operation will be performed. If the result is not 0, the update operation will be performed.

delete

deleteById(Serializable id)

/**
 * Delete employee information with id=21
 * DELETE FROM employee WHERE id=?
 */
employeeMapper.deleteById(21L);

deleteBatchIds(Collection): batch deletion, similar to the in condition in sql statements

/**
 * Delete employee information with id=1 and id=2
 * DELETE FROM employee WHERE id IN ( ? , ? )
 */
employeeMapper.deleteBatchIds(Arrays.asList(1L,2L));

deleteByMap(Map): deletes the object with the specified attribute value. The key in the map is the database field name and the value is the field value

/**
 * Delete employee information with name=xiaofei and age=0
 * DELETE FROM employee WHERE name = ? AND age = ?
 */
Map<String,Object> map = new HashMap<>();
map.put("name","xiaofei");
map.put("age",0);
employeeMapper.deleteByMap(map);

delete(Wrapper): Wrapper is used to specify the condition to delete

/**
 * Delete the employee information with name = Li Liuming and age=25
 * DELETE FROM employee WHERE (name = ? AND age = ?)
 */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.eq("name","Li Liuming").eq("age",25);
employeeMapper.delete(wrapper);

query

selectById(Serializable id)

/**
 * Query employee information with id=3
 * SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE id=?
 */
Employee employee = employeeMapper.selectById(3L);

Select batchids (Collection): batch queries, similar to in conditions in sql statements

/**
 *Query employee information with id=3 and id=4
 *  SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE id IN ( ? , ? )
 */
List<Employee> employees = employeeMapper.selectBatchIds(Arrays.asList(3L, 4L));

selectByMap(Map): query the object with the specified attribute value. The key in the map is the column name and the value is the corresponding value

/**
 * Query employee information with name=yoonas, age=18
 * SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE name = ? AND age = ?
 */
Map<String,Object> map = new HashMap<>();
map.put("name", "yoonas");
map.put("age",18);
List<Employee> employees = employeeMapper.selectByMap(map);

selectCount(Wrapper)

/**
  *  Query the number of all employees that meet the criteria
  *  SELECT COUNT( 1 ) FROM employee WHERE (age = ?)
  */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
//employeeMapper.selectCount(null);
wrapper.eq("age",18);
Integer count = employeeMapper.selectCount(wrapper);

selectList(Wrapper)

/**
 *  Query all qualified employee information and return list < employee >
 *  SELECT id,name,password,email,age,admin,dept_id FROM employee
 */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
//employeeMapper.selectList(null);
List<Employee> employees = employeeMapper.selectList(wrapper);
employees.forEach(System.out::println);

selectMaps(Wrapper): the return value is list < map < string, Object > >. If the queried data cannot be encapsulated into objects, this method is used. The sql of similar queries contains group by

/**
 * Query all employee information that meet the conditions and return list < map < string, Object > >
 *  SELECT id,name,password,email,age,admin,dept_id FROM employee
 */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
List<Map<String, Object>> maps = employeeMapper.selectMaps(wrapper);
maps.forEach(System.out::println);

selectOne

/**
 * Query records with name yoonas
 * SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (name = ?)
 */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.eq("name","yoonas");
Employee employee = employeeMapper.selectOne(wrapper);

paging

selectPage(Page,Wrapper)

To achieve the paging effect, you need to configure the paging interceptor in the startup class

//Paging interceptor
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
    MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
    PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor(DbType.MYSQL);
    paginationInnerInterceptor.setOverflow(true); //Rationalization
    interceptor.addInnerInterceptor(paginationInnerInterceptor);
    return interceptor;
}

@Test
public void testSelectPage(){
    QueryWrapper<Employee> wrapper = new QueryWrapper<>();
    //Parameter 1: current page, parameter 2: number of pieces displayed per page
    IPage<Employee> page = new Page<>(2,3);
    employeeMapper.selectPage(page,wrapper);
    System.out.println("Current page:" + page.getCurrent());
    System.out.println("Number of items displayed per page:" + page.getSize());
    System.out.println("Total pages:" + page.getPages());
    System.out.println("total:" + page.getTotal());
    System.out.println("Current page data:" + page.getRecords());
}

Write paging logic code

//service
@Override
public IPage<Employee> query(QueryObject qo) {
    IPage<Employee> page = new Page<>(qo.getCurrentPage(),qo.getPageSize());
    QueryWrapper<Employee> wrapper = new QueryWrapper<>();
    return super.page(page,wrapper);
}

//controller
@Test
public void testQuery(){
    QueryObject qo = new QueryObject();
    qo.setCurrentPage(2);
    qo.setPageSize(3);
    IPage<Employee> page = employeeService.query(qo);
    System.out.println("Current page:" + page.getCurrent());
    System.out.println("Total pages:" + page.getPages());
    System.out.println("Number of items displayed per page:" + page.getSize());
    System.out.println("Total records:" + page.getTotal());
    System.out.println("Current page display record:" + page.getRecords());
}

Conditional constructor

It can be simply understood as used to generate dynamic sql or sql statement fragments (< sql. > <. / sql >)

update operation

UpdateWrapper method:

set:

/**
 * Change the employee age with id=3 to 18. If the passed in username variable value is not equal to null or "", change it to employee name as username variable value
 * UPDATE employee SET age=?,name=? WHERE (id = ?)
 */
String username = "angle";
UpdateWrapper<Employee> wrapper = new UpdateWrapper<>();
wrapper.eq("id", 3L);
wrapper.set("age",18);
if(StringUtils.hasLength(username)){
    wrapper.set("name",username);
}
employeeMapper.update(null,wrapper);

setSql:

/**
 * Change the user name with id=3 to yoona
 * UPDATE employee SET name = 'yoona' WHERE (id = ?)
 */
UpdateWrapper<Employee> wrapper = new UpdateWrapper<>();
wrapper.eq("id",3L);
wrapper.setSql("name = 'yoona'");
employeeMapper.update(null,wrapper);

LambdaUpdateWrapper method:

/**
 * Change the user name with id=3 to hkj
 * UPDATE employee SET name=? WHERE (id = ?)
 */
LambdaUpdateWrapper<Employee> wrapper = new LambdaUpdateWrapper<>();
wrapper.eq(Employee::getId,3);
wrapper.set(Employee::getName,"hkj");
employeeMapper.update(null,wrapper);

Query operation

/** General query
 * Query employees with name=dafei, age=18
 * SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE name = ? AND age = ?
 */
Map<String,Object> map = new HashMap<>();
map.put("name", "dafei");
map.put("age",18);
List<Employee> employees = employeeMapper.selectByMap(map);

/** QueryWrapper query
 * Query employees with name=dafei, age=18
 * SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (name = ? AND age = ?)
 */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.eq("name", "dafei");
wrapper.eq("age",18);
List<Employee> employees = employeeMapper.selectList(wrapper);

/** LambdaQueryWrapper query
 * Query employees with name=dafei, age=18
 * SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (name = ? AND age = ?)
 */
LambdaQueryWrapper<Employee> wrapper = new LambdaQueryWrapper<>();
wrapper.eq(Employee::getName,"dafei").eq(Employee::getAge,18);
List<Employee> employees = employeeMapper.selectList(wrapper);

Advanced query

Column projection

/**Column projection
 * : Query all employees and return the employee name and age columns
 * SELECT name,age FROM employee
 */
ueryWrapper<Employee> wrapper = new QueryWrapper<>();
//wrapper.select("name,age");
wrapper.select("name","age");
List<Employee> employees = employeeMapper.selectList(wrapper);

/**Column projection
 * Query all employees and return the columns starting with h in the employee table
 */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.select(Employee.class,columnValue->columnValue.getProperty().startsWith("h"));
List<Employee> employees = employeeMapper.selectList(wrapper);

sort

orderByAsc/orderByDesc:

/**Sort orderByAsc/orderByDesc
 * Query all employee information and arrange it in the positive (descending) order of age. If age is the same, arrange it in the positive (descending) order of id
 * SELECT id,name,password,email,age,admin,dept_id FROM employee ORDER BY age ASC,id ASC
 * SELECT id,name,password,email,age,admin,dept_id FROM employee ORDER BY age DESC,id DESC
 */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
//wrapper.orderByAsc("age","id");
wrapper.orderByDesc("age","id");
List<Employee> employees = employeeMapper.selectList(wrapper);

orderBy:

/**Sort orderBy
 * Query all employee information and arrange it in positive order by age. If age is the same, arrange it in positive order by id
 * SELECT id,name,password,email,age,admin,dept_id FROM employee ORDER BY age ASC,id ASC
 */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
//Parameter 1 is a sort switch. true means sorting is performed. false otherwise, parameter 2 determines whether to sort the columns in positive order. Parameter 3 determines whether to sort the columns
wrapper.orderBy(true,false,"age","id");
List<Employee> employees = employeeMapper.selectList(wrapper);

Grouping query

groupBy:

/**Group query groupBy
 * Group query by department id to check the number of employees in each department
 * Since the returned column names cannot be encapsulated by objects, only selectMaps can be used
 * SELECT dept_id,count(id) count FROM employee GROUP BY dept_id
 */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.groupBy("dept_id");
wrapper.select("dept_id","count(id) count");
List<Map<String, Object>> maps = employeeMapper.selectMaps(wrapper);
maps.forEach(System.out::println);

having:

/**Grouping query having
 * Group query by department id, check the number of employees in each department, and filter out departments with more than 3 employees
 * SELECT dept_id,count(id) count FROM employee GROUP BY dept_id HAVING count > 3
 */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.groupBy("dept_id")
    .select("dept_id","count(id) count")
    .having("count > 3");
List<Map<String, Object>> maps = employeeMapper.selectMaps(wrapper);
maps.forEach(System.out::println);

Condition query

Equal to, not equal to

/**Condition query: not equal to
 * Query name= HKJ employee information
 * SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (name <> ?)
 */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.ne("name","hkj");
List<Employee> employees = employeeMapper.selectList(wrapper);

Comparison operators gt, ge, lt, le

/**Conditional query: comparison operators gt, ge, lt, le
* Query employee information older than 18 years old
 * SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (age > ?)
*/
ueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.gt("age",18);
List<Employee> employees = employeeMapper.selectList(wrapper);

between/notBetween: contains critical values

/**Condition query: between/notBetween: include critical value
     * Query the information of employees younger than 18 or older than 30
     * SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (age NOT BETWEEN ? AND ?)
     */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.notBetween("age",18,30);
List<Employee> employees = employeeMapper.selectList(wrapper);

isNull/isNotNull

/**Conditional query: isNull/isNotNull
 * Query employee information with null admin
 * SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (admin IS NULL)
 */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.isNull("admin");
List<Employee> employees = employeeMapper.selectList(wrapper);

in/notIn/inSql/notInSql

/**Condition query: in/notIn/inSql/notInSql
 * Query employee information with id 3 and 6
 * SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (id IN (?,?))
 * Query employee information whose id is not 3 or 6
 * SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (id NOT IN (3,6))
 */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.in("id",3L,6L);
List<Employee> employees = employeeMapper.selectList(wrapper);
----------------------------------------------------------------
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.notInSql("id","3,6");
List<Employee> employees = employeeMapper.selectList(wrapper);

Fuzzy query

/**Conditional query: fuzzy query
 * Query employees with hkj in name
 * SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (name LIKE ?)
 */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.like("name","hkj");
List<Employee> employees = employeeMapper.selectList(wrapper);

/**Conditional query: fuzzy query
 * Query employee information surnamed Wang
 * SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (name LIKE ?) Wu%
 */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.likeRight("name","Wu");
List<Employee> employees = employeeMapper.selectList(wrapper);
employees.forEach(System.out::println);

Logical operator

/**Conditional queries: logical operators
     * Query employees whose name contains yoona or whose age is between 18 and 30
     * SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (name LIKE ? OR (age BETWEEN ? AND ?))
     */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.like("name","yoona")
    .or(
    qw->qw.between("age",18,30)
);
List<Employee> employees = employeeMapper.selectList(wrapper);

/**Conditional queries: logical operators
 * Query employees whose name contains hkj and whose age is less than 18 or greater than 30
 * SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (name LIKE ? AND (age < ? OR age > ?))
 */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.like("name","hkj")
    .and(
    qw->qw.lt("age",18)
    .or()
    .gt("age",30)
);
List<Employee> employees = employeeMapper.selectList(wrapper);

/**Conditional queries: logical operators
 * Query employees whose name contains hkj and whose age is less than 18 or greater than 30
 * SELECT id,name,password,email,age,admin,dept_id FROM employee WHERE (name LIKE ? AND (age < ? OR age > ?))
 */
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.like("name","hkj")
    .and(
    qw->qw.lt("age",18)
    .or()
    .gt("age",30)
);
List<Employee> employees = employeeMapper.selectList(wrapper);

QueryWrapper<Employee> wrapper = new QueryWrapper<>();
        wrapper.like("name","hkj")
                .and(
                        qw->qw.notBetween("age",18,30)
                );
        employeeMapper.selectList(wrapper);

General Service interface

Create a custom service interface and inherit the IService interface

/**
 * mybatis-plus Custom service interface
 * 1>Custom interface IEmployeeService inherits IService interface
 * 2>Specify the generic type explicitly. The current interface operates on the entity object: Employee
 */
public interface IEmployeeService extends IService<Employee> {
}

Create a service implementation class, inherit serviceimpl < < mapper interface corresponding to entity object, entity class object >, and implement a user-defined interface

/**
 * mybatis-plus Service layer interface implementation class:
 * 2>Specify 2 generics on serviceimpl < m, t >
 *  1:The mapper interface EmployeeMapper corresponding to the current operation entity object
 *  2:Current operation entity class object Employee
 */
@Service
public class EmployeeService extends ServiceImpl<EmployeeMapper, Employee> implements IEmployeeService{
}

Paging operation using mybatis plus

Write an entity class that encapsulates paging data

@Getter
@Setter
public class QueryObject {
    private int currentPage;
    private int pageSize;
}

@Getter
@Setter
public class EmployeeQuery extends QueryObject {
    private String keyword;
}