What is mybatis plus?
To explain mybatis plus, you must first explain what mybatis is:
(1) Mybatis is a semi ORM (object relational mapping) framework. It encapsulates JDBC internally. During development, you only need to pay attention to the sql statement itself, and you don't need to spend energy dealing with the complicated processes such as loading driver, creating connection and creating statement. Programmers directly write original sql, which can strictly control the execution performance of sql and has high flexibility.
(2) MyBatis can use XML or annotations to configure and map native information, and map POJO s to records in the database, avoiding almost all JDBC code, manually setting parameters and obtaining result sets.
(3) Various statements to be executed are configured through xml files or annotations, and the final executed sql statements are generated through the mapping between java objects and the dynamic parameters of sql in the statement. Finally, the MySQL framework executes sql and maps the results into java objects and returns them. (the process from executing sql to returning result).
I've learned about mybatis here. In fact, mybatis plus is an enhanced framework in mybatis that only enhancements are made and no changes are made. It's a very popular framework written by the Chinese people. This framework makes our development easier. We only need to add relevant dependencies in the project and implement the interface in the service layer. In this way, we can reduce the writing of many SQL statements and greatly improve the development efficiency.
Introduction case of mybatis plus
Add dependency
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.4.3</version>
<relativePath/>
</parent>
<dependencies>
<!--mybatis-plus-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.0</version>
</dependency>
<!--springboot-test-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
<!--druid-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.17</version>
</dependency>
<!--mysql-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.22</version>
</dependency>
<!--lombok-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.16</version>
<scope>provided</scope>
</dependency>
</dependencies>
application.properties
#Set the time zone for the new version of mysql, otherwise an error will be reported. serverTimezone=GMT%2B8 spring.datasource.url=jdbc:mysql:///mybatis-plus?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8 spring.datasource.username=root spring.datasource.password=admin spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver # Configure slq print log logging.level.cn.wolfcode.mp.mapper=info
Employee entity class
@Setter
@Getter
@ToString
//Table name annotation: specifies which database table the current entity class maps to. By default, it is consistent with the entity class name
@TableName("t_employee")
public class Employee {
//Primary key annotation: marks the primary key of the current attribute mapping table. IdType.AUTO indicates that the database ID increases automatically
@TableId(value = "id",type = IdType.AUTO)
private Long id;
//Field annotation: specifies which column of the current attribute mapping database table is consistent with the attribute name by default
@TableField("name")
private String username;
private String password;
private String email;
private int age;
private int admin;
private Long deptId;
}
EmployeeMapper
Question: can CRUD operations be performed without writing crud SQL statements?
Thinking: crud the data in mysql database must depend on SQL, and the SQL statement is not written in the code, so we can be sure that mybatis plus helped us write it
<!-- Take query as an example: selectById(1L) To realize the query function: it must be spliced SQL And conditions --> <!--Splicing SQL--> select id,name,password......from employee <!--Condition parameters--> id: 1L
How mybatis plus implements the above operations:
1. After the spring container starts, mybatis plus starts to parse the generic class specified above EmployeeMapper: Employee
2. The Employee class is parsed through reflection to obtain:
Class name ----- > as SQL table name
Attribute ----- > as SQL column name
3. The parsed data is spliced with different SQL statements according to the called method, and then executed
public interface EmployeeMapper extends BaseMapper<Employee> {
}
@Service
@Transactional
public class EmployeeServiceImpl extends ServiceImpl<EmployeeMapper, Employee> implements IEmployeeService {
}
Test class code:
//Here is a test using springboot
@SpringBootTest
class EmployeeMapperTest {
@Autowired
private IEmployeeService employeeService;
@Test
public void save(){
Employee employee = new Employee();
employee.setName("James");
employee.setPassword("23");
employeeService.save(employee);
}
@Test
public void deleteById(){
employeeService.removeById(21);
}
@Test
public void update(){
Employee employee = new Employee();
employee.setId(21L);
employee.setName("Davis");
employee.setPassword("33");
employeeService.updateById(employee);
}
@Test
public void queryById(){
System.out.println(employeeService.getById(1));
}
}
General mapper
Suggestions for use:
The id is known, and all updates use the field updateById
If there is a default value in the database of the basic type of "mybat plus", the default value will be updated if there is no corresponding value in the database
updateById
public void testUpdate1(){
Employee employee = new Employee();
employee.setId(3L);
employee.setName("dafei3");
employee.setPassword("123");
employee.setEmail("dafei3@xx.com");
employee.setAge(18);
employee.setAdmin(1);
employee.setDeptId(2L);
employeeMapper.updateById(employee);
}
To update some fields, use update:
Usage: wrapper Conditions set the fields to be updated
update
//The name of the employee with id 1 is James and the password is 789
public void testUpdate2(){
UpdateWrapper<Employee> wrapper = new UpdateWrapper<>();
wrapper.eq("id",1L)
.set("name","James")
.set("password","789");
employeeMapper.update(null,wrapper);
}
How to delete:
Delete employee by Id
deleteById
//Requirement: delete the employee with Id 3
@Test
public void testDeleteById(){
employeeMapper.deleteById(3L)
}
Delete only if one or more conditions are met
deleteByMap
//Requirement: delete the employee information with name = Li Liuming and age=25
@Test
public void testDeleteByMap(){
HashMap map = new HashMap();
map.put("name","Li Liuming");
map.put("age",25);
employeeMapper.deleteByMap(map);
}
Delete only if one or more conditions are met
delete
//Requirement: delete employee information with name=dafei and age=18
//delete from employee where name ='dafei' and age=18
@Test
public void testDeleteWrapper(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.eq("name","xiaofei").eq("age",18);
employeeMapper.delete(wrapper);
}
Batch delete
deleteBatchIds
//Delete employee with id 17 or 18
//delete from employee where id in(17,18)
@Test
public void testDeleteBatchIds(){
employeeMapper.deleteBatchIds(Arrays.asList(17,18));
}
Query by Id
selectById
//Requirement: query all the information of the employee with id 1
//select * from employee where id= 1
@Test
public void testSelectById(){
System.out.println(employeeMapper.selectById(1));
}
Batch query
SelectBatchIds
//Requirement: query all information of employees with id 4 or 5 or 6
//select * from employee where id in(4,5,6)
@Test
public void testSelectBatchIds(){
System.out.println(employeeMapper.selectBatchIds(Arrays.asList(4,5,6)));
}
Delete only when one or more conditions are met
selectByMap
//Requirement: query employee information with name = James and age = 40
//select * from employee where name= 'James' and age=40
@Test
public void testSelectByMap(){
HashMap map = new HashMap();
map.put("name","James");
map.put("age",40);
System.out.println(employeeMapper.selectByMap(map));
}
Query the number of data pieces that meet some conditions
selectCount
//Demand: query the number of all employees that meet the (name = James) condition
@Test
public void testSelectCount(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.eq("name","James");
System.out.println(employeeMapper.selectCount(wrapper));
}
Query the collection of all data that meet certain conditions (type: Employee)
selectList
//Requirement: query all employees who meet the (name = James) criteria
@Test
public void testSelectList(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.eq("name","James");
System.out.println(employeeMapper.selectList(wrapper));
}
Query the collection of all data that meet certain conditions (type: Map < string, Object >)
selectMaps
Application scenario: Joint table advanced query
//Requirement: query all employee information that meets the conditions, return list < map < string, Object > > and encapsulate each data into HashMap at the bottom
//SELECT id,name,age FROM employee WHERE (name = ?)
@Test
public void testSelectMaps(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.eq("name","James");
wrapper.select("id,name,age");
List<Map<String, Object>> list = employeeMapper.selectMaps(wrapper);
list.forEach(System.out::println);
}
Paging query
selectPage
Application scenario: single table paging advanced query
Note: before using the paging function, you must remember to configure the paging interceptor (MybatisPlusInterceptor) in the startup class, otherwise it will not work
@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<>(1, 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());
}
Paging query
selectMapsPage
Application scenario: advanced query by linking tables and paging, which is equivalent to the use of resultMap
Note: before using the paging function, you must remember to configure the paging interceptor (MybatisPlusInterceptor) in the startup class, otherwise it will not work
//Requirement: query the employee data on the second page and display 3 items on each page (the data returned by paging is HashMap)
@Test
public void testSelectMapsPage(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
IPage<Map<String, Object>> page = new Page<>(2,3);
employeeMapper.selectMapsPage(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());
}
Conditional constructor
updateWrapper.set("age",18)
UpdateWrapper
This method is equivalent to SQL fragment: "set age =?" = > Paramter: 18, placeholder used
//UpdateWrapper update
//Requirement: change the employee age with id=1 to 18
@Test
public void testUpdate(){
UpdateWrapper<Employee> wrapper = new UpdateWrapper<>();
wrapper.set("age",18).eq("id",1L);
employeeMapper.update(null,wrapper);
}
LambdaUpdateWrapper
//LambdaUpdateWrapper update
//Requirement: change the employee age with id=1 to 18
@Test
public void testLambdaUpdate1(){
LambdaUpdateWrapper<Employee> wrapper = new LambdaUpdateWrapper<>();
wrapper.eq(Employee::getId,1L)
.set(Employee::getAge,18);
employeeMapper.update(null,wrapper);
}
updateWrapper.setSql("name ='dafei'")
This method is equivalent to an SQL fragment: "name = 'dafei'" does not use placeholders
//Requirement: change the user name with id=1 to dafei
@Test
public void testUpdate3(){
UpdateWrapper<Employee> wrapper = new UpdateWrapper<>();
wrapper.eq("id",1L);
wrapper.setSql("name ='dafei'");
employeeMapper.update(null,wrapper);
}
queryWrapper.select("name","age");
Use the select method to specify the columns to be queried. If not set, all columns will be queried by default (select *)
//Requirement: query all employees and return the columns of employee name and age
@Test
public void testQuery1(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.select("name","age");
System.out.println(employeeMapper.selectList(wrapper));
}
queryWrapper.orderByAsc("age")
Sort employees by age in positive order. If age is the same, sort employees by id in positive order
//Demand: query all employee information and arrange it in positive order by age. If age is the same, arrange it in positive order by id
@Test
public void testQuery3(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.orderByAsc("age").orderByAsc("id");
List<Employee> employees = employeeMapper.selectList(wrapper);
employees.forEach(System.out::println);
}
queryWrapper.orderByDesc("id")
Sort employees by age in positive order. If age is the same, sort employees by id in reverse order
//Requirement: query all employee information and sort them in positive order by age. If age is the same, sort them in reverse order by id
@Test
public void testQuery5(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.orderByAsc("age");
wrapper.orderByDesc("id");
List<Employee> employees = employeeMapper.selectList(wrapper);
employees.forEach(System.out::println);
}
Priority: priority from top to bottom
queryWrapper.eq("name","dafei")
Equivalent to: "where name = 'dafei'"
Using multiple EQS together is eq uivalent to adding the and keyword: WHERE (name = "dafei" AND age = 18)
//Requirement: query employee information with name=dafei and age=18
//SELECT * FROM employee WHERE (name = ? AND age = ?)
@Test
public void testQuery6(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.eq("name","dafei");
wrapper.eq("age",18);
List<Employee> employees = employeeMapper.selectList(wrapper);
employees.forEach(System.out::println);
}
queryWrapper.allEq(map)
Use the map to store the conditions to be filtered, and pass in the map object to call the alleq (congruent) method to query the data collection that meets all the conditions in the map. The bottom layer of this method calls another method allEq(params, true). Here, there is a default value of true, which means that if the value of the previous key is null, the isNull method will be called automatically. On the contrary, if it is automatically set to false, the condition will not be added to the sql statement when the value of the current key is null.
Example 1: allEq({id:1,name: "Lao Wang", age:null}) - > id = 1 and name = 'Lao Wang' and age is null
Example 2: allEq({id:1,name: "Lao Wang", age:null}, false) - > id = 1 and name = 'Lao Wang'
//Requirement: query employee information with name=dafei and age=18
//SELECT * FROM employee WHERE (name = ? AND dept_id IS NULL AND age = ?)
@Test
public void testQuery7(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
Map<String,Object>map = new HashMap<>();
map.put("name","dafei");
map.put("age",18);
map.put("dept_id", null);
wrapper.allEq(map);
List<Employee> employees = employeeMapper.selectList(wrapper);
employees.forEach(System.out::println);
}
//Requirement: query employee information with name=dafei and age=18
//SELECT * FROM employee WHERE (name = ? AND age = ?)
@Test
public void testQuery8(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
Map<String,Object>map = new HashMap<>();
map.put("name","dafei");
map.put("age",18);
map.put("dept_id", null);
wrapper.allEq(map,false);
List<Employee> employees = employeeMapper.selectList(wrapper);
employees.forEach(System.out::println);
}
queryWrapper.ne("name","dafei")
Query name= Dafei employee information
//Requirement: query name= Dafei employee information
@Test
public void testQuery11(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.ne("name","dafei");
System.out.println(employeeMapper.selectList(wrapper));
}
queryWrapper.gt("age",18)
Query the information of employees older than 18 years old
//Demand: query the information of employees older than 18 years old
@Test
public void testQuery12(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.gt("age",18);
System.out.println(employeeMapper.selectList(wrapper));
}
queryWrapper.lt("age",30)
Query information of employees age d less than 18
//Demand: query the information of employees whose age is less than 30 years old
@Test
public void testQuery12(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.lt("age",30);
System.out.println(employeeMapper.selectList(wrapper));
}
queryWrapper.ge("age",18)
Query the information of employees age d 18 or older
//Demand: query the information of employees whose age is greater than or equal to 18 years old
@Test
public void testQuery12(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.ge("age",18);
System.out.println(employeeMapper.selectList(wrapper));
}
queryWrapper.le("age",30)
Query employee information whose age is less than or equal to 30 years old
//Demand: query the information of employees whose age is less than or equal to 30 years old
@Test
public void testQuery12(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.le("age",30);
System.out.println(employeeMapper.selectList(wrapper));
}
queryWrapper.isNull("dept_id")
Query Dept_ Employee information whose ID is null
//Demand: query Dept_ Employee information whose ID is null
@Test
public void testQuery15(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.isNull("dept_id");
System.out.println(employeeMapper.selectList(wrapper));
}
queryWrapper.isNotNull("dept_id")
Query Dept_ Employee information whose ID is not null
//Demand: query Dept_ Employee information whose ID is null
@Test
public void testQuery15(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.isNotNull("dept_id");
System.out.println(employeeMapper.selectList(wrapper));
}
queryWrapper.in("id",1,2)
Query the employee information with id 1 and 2. Placeholders are used in this way
//Requirement: query employee information with id 1 and 2
@Test
public void testQuery17(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.in("id",1,2);
System.out.println(employeeMapper.selectList(wrapper));
}
queryWrapper.in("id","1,2")
Query the employee information with id 1 and 2. This method does not use placeholders, but sql fragments. The above method is generally recommended to prevent sql injection
//Requirement: query employee information with id 1 and 2
@Test
public void testQuery18(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.inSql("id","1,2");
System.out.println(employeeMapper.selectList(wrapper));
}
queryWrapper.like("name","fei")
Query employees with fei in name
//Requirement: query employees whose name contains fei
@Test
public void testQuery19(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.like("name","fei");
System.out.println(employeeMapper.selectList(wrapper));
}
queryWrapper.notLike("name","fei")
Query employees whose name does not contain fei
//Requirement: query employees whose name does not contain fei
@Test
public void testQuery20(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.notLike("name","fei");
System.out.println(employeeMapper.selectList(wrapper));
}
queryWrapper.likeLeft("name","fei")
Querying employee information whose name ends with fei is equivalent to like "% fei"
//Requirement: query the employee information whose name ends with fei
@Test
public void testQuery21(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.likeLeft("name","fei");
System.out.println(employeeMapper.selectList(wrapper));
}
queryWrapper.likeRight("name","fei")
Querying employee information surnamed Wang is equivalent to like "Wang%"
//Demand: query employee information surnamed Wang
@Test
public void testQuery22(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.likeRight("name","king");
System.out.println(employeeMapper.selectList(wrapper));
}
queryWrapper.or()
Equivalent to or keyword
//Or query user's requirement: name = Fei = 1
@Test
public void testQuery23(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.eq("age",18)
.or()
.eq("name","dafei")
.or()
.eq("id",1);
System.out.println(employeeMapper.selectList(wrapper));
}
queryWrapper.and()
Equivalent to and keyword
//Requirement: query users whose name contains fei and whose age is less than 18 or more than 30
@Test
public void testQuery26(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.like("name", "fei")
.and(wr->wr.le("age",18)
.or()
.ge("age",30));
System.out.println(employeeMapper.selectList(wrapper));
}
queryWrapper.groupBy("dept_id")
Group query by department id to check the number of employees in each department
The columns after select can only be those after group by
//Demand: group query by department id, and check the number of employees in each department
@Test
public void testQuery27(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.select("id","count(*) count");
wrapper.groupBy("dept_id");
System.out.println(employeeMapper.selectList(wrapper));
}
queryWrapper.having("count > {0}",3)
Here {0} is a placeholder
Group query by department id, check the number of employees in each department, and filter out departments with more than 3 employees
//Demand: group query by department id, check the number of employees in each department, and filter out departments with more than 3 employees
@Test
public void testQuery28(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.select("id","count(*) count");
wrapper.groupBy("dept_id");
wrapper.having("count > {0}",3);
System.out.println(employeeMapper.selectMaps(wrapper));
}
General mode
1: Custom service interface integration IService Interface
public interface IEmployeeService extends IService<Employee> {
}
/*2: The service interface implementation class integrates the IService interface implementation class ServiceImpl and implements the user-defined interface at the same time
Note that ServiceImpl implements class generics:
Generic 1: mapper interface of entity class
Generic 2: entity class*/
@Service
public class EmployeeServiceImpl extends ServiceImpl<EmployeeMapper, Employee> implements IEmployeeService {
}