Mybatis-Plus (MP) on the basis of Mybatis only enhance without change, simplify development, improve efficiency.
This article is based on the MyBatis-Plus tutorial video, after learning the summary. Have interest can look, very helpful to beginner.
The course is integrated by SpringBoot + MyBatis-Plus.
The project address
If the description is unclear, or if you want to look at the code, clone the repository
immoc-mybatis-plus
Pre configuration
Database and table configuration
In this study, only 1 library, a table, library name mp, table name mp_user. Directly copy to the SQL tool import can be.
/* Navicat Premium Data Transfer Source Server : mac Source Server Type : MySQL Source Server Version : 50716 Source Host : localhost:3306 Source Schema : mp Target Server Type : MySQL Target Server Version : 50716 File Encoding : 65001 Date: 30/07/2020 23:12:46 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for mp_user -- ---------------------------- DROP TABLE IF EXISTS `mp_user`; CREATE TABLE 'mp_user' (' id 'bigint(20) NOT NULL COMMENT '主键',' name 'varchar(30) DEFAULT NULL COMMENT '主键', 'age' int(11) DEFAULT NULL COMMENT 'age ',' email 'varchar(50) DEFAULT NULL COMMENT' email ', 'manager_id' bigint(20) DEFAULT NULL COMMENT 'Create time ',' create_time 'datetime DEFAULT NULL COMMENT' create time ', PRIMARY KEY (`id`), KEY `manager_fk` (`manager_id`), CONSTRAINT `manager_fk` FOREIGN KEY (`manager_id`) REFERENCES `mp_user` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of mp_user -- ---------------------------- BEGIN; INSERT INTO 'mp_user' VALUES (1087982257332887553, 'da ', 40, '[email protected]', NULL, '2019-01-11 14:20:20'); INSERT INTO 'mp_user' VALUES (1088248166370832385, '[email protected]', 1087982257332887553, 'the 11:12:22 2019-02-05'); INSERT INTO 'mp_user' VALUES (1088250446457389058, '[email protected]', 1088248166370832385, 'the 08:31:16 2019-02-14'); INSERT INTO 'mp_user' VALUES (1094590409767661570, '[email protected]', 1088248166370832385, 'the 09:15:15 2019-01-14'); INSERT INTO 'mp_user' VALUES (1094592041087729666, 32, '[email protected]', 1088248166370832385, 'the 09:48:16 2019-01-14'); INSERT INTO 'mp_user' VALUES (1288460195017072641, 'liu ', 31, NULL, 1088248166370832385, '2020-07-29 21:03:44'); INSERT INTO 'mp_user' VALUES (1288842439342780417, '[email protected]', 1088248166370832385, 'the 22:23:33 2020-07-30'); INSERT INTO 'mp_user' VALUES (1288848935841488897, 'liu Hua ', 29, '[email protected]', 1088248166370832385, 'the 22:48:27 2020-07-30'); COMMIT; SET FOREIGN_KEY_CHECKS = 1;Copy the codeDepend on the configuration
Specifies the SpringBoot parent project
- Rely on SpringBootStarter
- Lombok, generate GET, set, reduce template code
- MyBatis-Plus provides us with Starter
- Finally, the mysql driver package.
<? The XML version = "1.0" encoding = "utf-8"? > < project XMLNS = "http://maven.apache.org/POM/4.0.0" XMLNS: xsi = "http://www.w3.org/2001/XMLSchema-instance" Xsi: schemaLocation = "http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd" > < modelVersion > 4.0.0 < / modelVersion > < groupId > com. Immoc. Mybatisplus < / groupId > < artifactId > immoc mybatis - plus < / artifactId > <version>1.0-SNAPSHOT</version> <modules> <module>first</module> <module>crud</module> </modules> <packaging>pom</packaging> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> < project. Reporting. OutputEncoding > utf-8 < / project. Reporting. OutputEncoding > < Java version > 1.8 < / Java version > The < mybatis. Starter. Version > 3.1.0 < / mybatis. Starter. Version > < / properties > <! --> <parent> <groupId>org.springframework. Boot </groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.1.3.RELEASE</version> </parent> <dependencies> <! --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <! --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> </dependency> <! <dependency> <groupId>org.projectlombok</groupId> <artifactId> Lombok </artifactId> <optional>true</optional> </dependency> <! MyBatis --> <dependency> <groupId>com.baomidou</groupId> <artifactId> MyBatis -- plus-boot-starter</artifactId> <version>${mybatis.starter.version}</version> </dependency> <! -- MySQL JDBC driver --> <dependency> <groupId> MySQL </groupId> <artifactId>mysql-connector-java</artifactId> </dependency> </dependencies> </project>Copy the codeDatabase and related configuration of MybaitS-Plus
In the resource directory, create the application.yml file. Configuration:
- The project name
- Mysql > alter database user name and password
- Log Print level
- MyBatis-Plus configuration, etc., notes are written very clear, will not repeat
Spring: Application: name: crud # datasource: driver-class-name: com.mysql.cj.jdbc.driver jdbc:mysql://localhost:3306/mp? UseSSL =false&serverTimezone=GMT%2B8 username: root password: hezihao123 warn com.imooc.mybatisplus: trace pattern: console: '%p%m%n' mybatis-plus: # configuration mapper file location, note that if you are a maven module, path before need to add the classpath *, namely load mapper XML file under the multiple jars - locations: - the classpath * : com/imooc/mybatisplus/global mapper / * - config: db - config: id # id global strategy - type: SQL > select * from id_worker where (id_worker, id_worker, id_worker, id_worker, id_worker, id_worker, id_worker, id_worker, id_worker, id_worker, id_worker, id_worker, id_worker); It overwrites the original value with null. If the value is null, #not_empty will not be updated. If the value is null, #not_empty will be ignored and not added to the SQL statement. Table underline: true # mybatis config file location #config-location: Classpath :mybatis-config. XML # entity alias package configuration type-aliases-package: Com. Imooc. Mybatisplus. Entity # note configuration cannot appear at the same time and the config - the location, or complains the configuration: The default is true map-underscore-to-camel-case: trueCopy the codeConfigure the original MyBatis profile
MyBatis -config. XML file can be created in the resources folder. For example, MyBatis -config. XML file can be created in the resources folder. Do not do the configuration for now.
<? The XML version = "1.0" encoding = "utf-8"? > <! DOCTYPE configuration PUBLIC "- / / mybatis.org//DTD Config / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-config.dtd" > <configuration> </configuration>Copy the codeConfigure the paging plug-in for MyBatis-Plus
Create a Configuration package and add the MyBatisPlusConfig class, which is a configuration class that provides an instance of the PaginationInterceptor paging plug-in.
@configuration Public class MyBatisPlusConfig {/** * MyBatisPlus public PaginationInterceptor paginationInterceptor() { return new PaginationInterceptor(); }}Copy the codeApplication gateways
SpringBoot requires us to provide an entry with a main method, for example we provide an Application class for launching
Note: Specify the package path to be scanned by MyBatis with the @mapperscan annotation
@ SpringBootApplication MyBatis scan packages specified path / / @ MapperScan (" com. Imooc. Mybatisplus. Dao ") public class Application {public static void main(String[] args) { SpringApplication.run(Application.class, args); }}Copy the codeCreate the entity class corresponding to the table
- @data annotation, generate entity class get, set methods
- The @equalsandHashCode annotation generates the equals and hashcode methods and specifies that the equals and hashcode methods of the parent class are not called
- The @tablename annotation specifies the name of the table corresponding to the entity class. If the TableName and the entity class name are the same, you can omit the annotation, but if they are different, the annotation is required to specify the TableName
- The @tableID annotation specifies that the member variable on the entity class is the primary key field in the database table, and the value attribute specifies the name of the primary key in the table. The default primary key name is ID. If the two primary key names are the same as those in the database, you do not need to add them
- The @tablefield annotation indicates that the member variable is the corresponding field of the database. If the member variable and the TableField name are the same, you can not add, if not, you need to add
- Value property that specifies the name of the corresponding table field
- By default, data fields are inserted and updated only when the field is not null. You can configure multiple policies, such as fieldStrategy. NOT_EMPTY, to be added only when the field is not null or a non-empty string
- Exist attribute, which indicates that the member variable is not a field in the database table. During CRUD, the field will not be added to SQL
@data @equalSandHashCode (callSuper = false) @tablename ("mp_user") public class User {/** ** If the variable name is inconsistent with the database field name, you can specify */ // @tableId (value = "ID ") /** * primary key policy * 1.idType. AUTO: automatic increment of the database * 2.idtype. NONE: Idtype. ID_WORKER: snowflake algorithm, value type * 4. idtype. UUID: Idtype. ID_WORKER_STR, string type */ @tableId (value = "id") private Long userId; /** * name * @tableField, which is used as a database field, if the name of the variable is not the same as the name of the database field, you can specify the * condition attribute, which can be used as an equivalent or fuzzy query for MyBatis-Plus. */ / @tableField (value = "name", condition = sqlcondition.like) /** * strategy */ // @tableField (value = "name", strategy = FieldStrategy.NOT_EMPTY) @TableField(value = "name") private String readName; /** * age */ private Integer age; /** * email */ private String email; /** * private Long managerId; /** * private LocalDateTime createTime; /** * note, non-database field, default MyBatis-Plus will treat all variable names of the entity as database fields, if there is no field will be an error * 3 ways to identify non-database fields: * 1. Transient keyword, marking the field does not participate in the serialization * 2. Use a static identifier for a field, but this will cause the entire class to share an attribute. * 3 is generally not used. */ /private TRANSIENT String remark; //private static String remark; @TableField(exist = false) private String remark; }Copy the codeConfigure the Dao, or Mapper
UserMapper is the Mapper interface corresponding to the User class. Each custom Mapper interface inherits the BaseMapper interface, and the generics specify the entity class for the operation.
BaseMapper provides many single table operations, batch operations, conditional query, paging query, and other common methods. Our custom Mapper interface only writes special operation methods, such as custom multi-table lookup, etc., for now, we will not add, later when we explain the custom method, we will add.
public interface UserMapper extends BaseMapper<User> {
}
Copy the codeTesting CRUD
- InsertTest. Under the test package, create the InsertTest test class
Insert calls the Insert () method of UserMapper, which is provided by BaseMapper.
RunWith(springrunner.class); // Specify that the Junit test @runwith (springrunner.class) can be used in the Spring environment. @springbooTtest (classes = application.class) public class InsertTest {@autoWired private UserMapper UserMapper; @test public void insert() {// Insert User User = new User(); SetReadName (" treadname "); user.setAge(31); user.setManagerId(1088248166370832385L); user.setCreateTime(LocalDateTime.now()); int rows = userMapper.insert(user); System.out.println(" rows: "+ rows); } @test public void insert2() {// insert User User = new User(); User. SetReadName (" to "); user.setAge(25); user.setManagerId(1088248166370832385L); user.setEmail("[email protected]"); user.setCreateTime(LocalDateTime.now()); User.setremark (" I am a note message "); int rows = userMapper.insert(user); System.out.println(" rows: "+ rows); }}Copy the code- DeleteTest, test package, create DeleteTest test class
Delete methods, BaseMapper provides several
- DeleteById, delete by primary key
- DeleteByMap: adds the deletion criteria to the Map for deletion
- DeleteBatchIds: Deletes batch ids
- Delete, passing in the Wrapper class, which is a conditional constructor with conditional addition methods such as OR and get greater. For example, delete records aged 27 or older than 41
@RunWith(SpringRunner.class) @SpringBootTest(classes = Application.class) public class DeleteTest { @Autowired private UserMapper userMapper; @test public void deleteById() {int rows = usermapper.deleteById (1288677289033801729L); System.out.println(" rows: "+ rows); } /** * provide a Map that records the deletion criteria, */ @test public void deleteByMap() {HashMap<String, Object> columnMap = new HashMap<>(); columnMap.put("id", 1288676754557825026L); columnMap.put("age", 25); int rows = userMapper.deleteByMap(columnMap); System.out.println(" rows: "+ rows); } /** * pass multiple ids, For batch delete * / @ Test public void deleteIds () {int rows = userMapper. DeleteBatchIds (arrays.aslist (1288461659789660161 l, 1288671248736870402L, 1288676285735272450L)); System.out.println(" rows: "+ rows); } /** * use the conditional constructor, */ @test public void deleteByWrapper() {LambdaQueryWrapper<User> lambdaQuery = Wrappers.lambdaquery (); lambdaQuery.eq(User::getAge, 27) .or() .gt(User::getAge, 41); int rows = userMapper.delete(lambdaQuery); System.out.println(" rows: "+ rows); }}Copy the code- UpdateTest, test package, create UpdateTest test class
BaseMapper provides the following updates:
- UpdateById, updated based on the primary key Id of the entity
- Update, pass in the entity and Wrapper classes, set the updated value for the entity, and configure the conditions for the Wrapper
- Update, similar to the second, passes in the entity class when constructing the Wrapper class
- Update, use Wrapper only, entity class passes NULL, update value set with Wrapper set method
- Update, as with the fourth, replace the Wrapper class with LambdaXXXWrapper, which supports Lambda expressions
- The Update method is not in the Mapper class. Instead, Mapper is passed in during Wrapper construction and the European Canopy Mapper class is indirectly called when the Update method of the Wrapper is called
@RunWith(SpringRunner.class) @SpringBootTest(classes = Application.class) public class UpdateTest { @Autowired private UserMapper userMapper; @test public void updateById() {User User = new User(); user.setUserId(1088248166370832385L); user.setAge(26); user.setEmail("[email protected]"); int rows = userMapper.updateById(user); System.out.println(" rows: "+ rows); } /** * Update information on entities, update conditions using condition constructors, */ @test public void updateByWrapper() {UpdateWrapper<User> UpdateWrapper = new UpdateWrapper<>(); Eq ("name", "updateWrapper.eq "). Eq ("age", 28); User user = new User(); user.setEmail("[email protected]"); user.setAge(29); int rows = userMapper.update(user, updateWrapper); System.out.println(" rows: "+ rows); @test public void updateByWrapper2() {User whereUser = new User(); @test public void updateByWrapper2() {User whereUser = new User(); WhereUser. SetReadName (" Li Yiwei "); whereUser.setEmail("[email protected]"); whereUser.setAge(29); UpdateWrapper<User> updateWrapper = new UpdateWrapper<>(whereUser); int rows = userMapper.update(whereUser, updateWrapper); System.out.println(" rows: "+ rows); } /** * conditional Settings via UpdateWrapper, */ @test public void updateByWrapper3() {UpdateWrapper<User> UpdateWrapper = new UpdateWrapper<>(); UpdateWrapper. Eq (" name ", "Li Yiwei"). The eq (" age ", 29). The set (" age ", 30); int rows = userMapper.update(null, updateWrapper); System.out.println(" rows: "+ rows); } /** * conditional Settings via UpdateWrapper, */ @test public void updateByWrapperLambda() {LambdaUpdateWrapper<User> updateWrapper = Wrappers.lambdaUpdate(); Updatewrapper.eq (User::getAge, 30).eq(User::getAge, 30).set(User::getAge, 31); int rows = userMapper.update(null, updateWrapper); System.out.println(" rows: "+ rows); } / * * * * / @ another Lambda expressions way Test public void updateByWrapperLambdaChain () {LambdaUpdateChainWrapper < User > updateWrapper = new LambdaUpdateChainWrapper<>(userMapper); Boolean isUpdateSuccess = updatewrapper.eq (User::getAge, 31).set(User::getAge, 32) .update(); System.out.println(" update successfully: "+ isUpdateSuccess); }}Copy the code- Query tests. Under the test package, a new RetrieveTest test class is added
- SelectById, query by primary key Id
- SelectBatchIds, batch Id query
- SelectByMap, query criteria into the Map, query
- SelectList, which provides an instance of the QueryWrapper class and is used as a query condition constructor
- SelectMaps, which provides an instance of the QueryWrapper class, is used as a query condition constructor, but returns a List of query results, not entity-like objects, but a Map containing each property and value
- SelectObjs, which provides an instance of the QueryWrapper class, is used as a constructor for the query condition. This method returns only the properties and values of the first column of the query result.
- SelectCount, statistics query, statistics query results, how many records
- Select * from selectOne; select * from selectOne; select * from selectOne;
- SelectList, using LambdaQueryWrapper, to use Lambda expressions (benefit: miswrite, pass in database field names in the normal way, error will be reported if written wrong, Lambda expressions use method references to get field information)
@RunWith(SpringRunner.class)
@SpringBootTest(classes = Application.class)
public class RetrieveTest {
@Autowired
private UserMapper userMapper;
/**
* 根据主键Id查询
*/
@Test
public void selectById() {
User user = userMapper.selectById(1094590409767661570L);
System.out.println(user);
}
/**
* 一次使用多个Id进行查询
*/
@Test
public void selectIds() {
List<Long> idList = Arrays.asList(1088248166370832385L, 1288460195017072641L, 1094590409767661570L);
List<User> userList = userMapper.selectBatchIds(idList);
for (User user : userList) {
System.out.println(user);
}
}
/**
* 使用Mao存放查询字段和条件来进行查询
*/
@Test
public void selectByMap() {
//Map存放查询条件，注意key存放的是数据库的字段名，而不是实体中的变量名
HashMap<String, Object> columnMap = new HashMap<>();
columnMap.put("name", "王天风");
columnMap.put("age", 25);
List<User> userList = userMapper.selectByMap(columnMap);
for (User user : userList) {
System.out.println(user);
}
}
/**
* 使用条件构造器进行查询
* <p>
* 需求：
* 名字中包含"雨"，并且年龄小于40
* sql: name like %雨% and age < 40
*/
@Test
public void selectByWrapper() {
//直接创建一个条件构造器，获取使用Wrappers工具类
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//QueryWrapper<User> queryWrapper = Wrappers.query();
queryWrapper
//模糊查询，注意这里的字段都是数据库字段，而不是实体的变量名
.like("name", "雨")
//小于
.lt("age", 40);
List<User> userList = userMapper.selectList(queryWrapper);
for (User user : userList) {
System.out.println(user);
}
}
/**
* 使用条件构造器进行查询
* <p>
* 需求：
* 名字中包含"雨"，并且年龄大于等于20，且小于等于40，并且email不为空
* sql: name like '%雨%' and age between 20 and 40 and email is not null
*/
@Test
public void selectByWrapper2() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper
//模糊查询，注意这里的字段都是数据库字段，而不是实体的变量名
.like("name", "雨")
//年龄大于20，并且小于40
.between("age", 20, 40)
//不为null
.isNotNull("email");
List<User> userList = userMapper.selectList(queryWrapper);
for (User user : userList) {
System.out.println(user);
}
}
/**
* 使用条件构造器进行查询
* <p>
* 需求：
* 名字为王姓，或者年龄大于等于25，按照年龄降序排列，年龄相同按照id升序排列
* name like '王%' or age>=25 order by age desc,id asc
*/
@Test
public void selectByWrapper3() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper
//模糊查询，只包含右边一个%，注意这里的字段都是数据库字段，而不是实体的变量名
.likeRight("name", "王")
.or()
//年龄大于等于25
.ge("age", 25)
//先按年龄降序排（从大到小）
.orderByDesc("age")
//年龄相同的，再按id的升序排（从小到大）
.orderByAsc("id");
List<User> userList = userMapper.selectList(queryWrapper);
for (User user : userList) {
System.out.println(user);
}
}
/**
* 使用条件构造器进行查询
* <p>
* 创建日期为2019年2月14日，并且直属上级为名字为王姓
* sql: date_format(create_time,'%Y-%m-%d')='2019-02-14' and manager_id in (select id from user where name like '王%')
*/
@Test
public void selectByWrapper4() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper
//直接用，不使用占位符，可能会有sql注入的风险
//.apply("date_format(create_time,'%Y-%m-%d')=2019-02-14")
//apply占位符查询，目的是为了防止sql注入
.apply("date_format(create_time,'%Y-%m-%d')={0}", "2019-02-14")
//inSql子查询
.inSql("manager_id", "select id from mp_user where name like '王%'");
List<User> userList = userMapper.selectList(queryWrapper);
for (User user : userList) {
System.out.println(user);
}
}
/**
* 使用条件构造器进行查询
* <p>
* 名字为王姓并且（年龄小于40或邮箱不为空）
* sql: name like '王%' and (age<40 or email is not null)
*/
@Test
public void selectByWrapper5() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.likeRight("name", "王")
//函数式编程
//.and(wrapper-> wrapper.lt("age", 40).or().isNotNull("email"))
.and(new Function<QueryWrapper<User>, QueryWrapper<User>>() {
@Override
public QueryWrapper<User> apply(QueryWrapper<User> wrapper) {
//年龄小于40
return wrapper.lt("age", 40)
//或者邮箱不为空
.or()
.isNotNull("email");
}
});
List<User> userList = userMapper.selectList(queryWrapper);
for (User user : userList) {
System.out.println(user);
}
}
/**
* 使用条件构造器进行查询
* <p>
* 名字为王姓，或者（年龄小于40并且年龄大于20并且邮箱不为空）
* sql: name like '王%' or (age<40 and age>20 and email is not null)
*/
@Test
public void selectByWrapper6() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper
//王姓开头
.likeRight("name", "王")
//or()使用Function参数的，将获取年龄小于40，并且年龄大于20
.or(wrapper -> wrapper.lt("age", 40)
.gt("age", 20)
//并且邮箱不为空的条件用括号包裹起来
.isNotNull("email"));
List<User> userList = userMapper.selectList(queryWrapper);
for (User user : userList) {
System.out.println(user);
}
}
/**
* 使用条件构造器进行查询
* <p>
* （年龄小于40或邮箱不为空）并且名字为王姓
* sql: (age<40 or email is not null) and name like '王%'
*/
@Test
public void selectByWrapper7() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper
//nested()，嵌套，就是加括号
.nested(wrapper -> wrapper.lt("age", 40).or().isNotNull("email"))
//邮箱不为null
.likeRight("name", "王");
List<User> userList = userMapper.selectList(queryWrapper);
for (User user : userList) {
System.out.println(user);
}
}
/**
* 使用条件构造器进行查询
* <p>
* 年龄为30、31、34、35
* sql: age in (30、31、34、35)
*/
@Test
public void selectByWrapper8() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper
.in("age", Arrays.asList(30, 31, 34, 35));
List<User> userList = userMapper.selectList(queryWrapper);
for (User user : userList) {
System.out.println(user);
}
}
/**
* 使用条件构造器进行查询
* <p>
* 9、只返回满足条件的其中一条语句即可
* sql: limit 1
*/
@Test
public void selectByWrapper9() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//字符串拼接到sql，会有sql注入的风险
queryWrapper.last("limit 1");
List<User> userList = userMapper.selectList(queryWrapper);
for (User user : userList) {
System.out.println(user);
}
}
/**
* 使用条件构造器进行查询
* <p>
* 需求：
* select中字段不全部出现的查询，例如只查询出id和姓名（默认会查询出实体中的所有字段）
* sql: select id,name from user where name like '%雨%' and age<40
*/
@Test
public void selectByWrapperSupper() {
//直接创建一个条件构造器，获取使用Wrappers工具类
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//QueryWrapper<User> queryWrapper = Wrappers.query();
queryWrapper
//<重点>，相比上面的selectByWrapper，多了调用select()方法，传入需要查询的列名
.select("id", "name")
//模糊查询，注意这里的字段都是数据库字段，而不是实体的变量名
.like("name", "雨")
//小于
.lt("age", 40);
List<User> userList = userMapper.selectList(queryWrapper);
for (User user : userList) {
System.out.println(user);
}
}
/**
* 使用条件构造器进行查询
* <p>
* 需求：
* select中字段不全部出现的查询，例如只查询出id和姓名、年龄、邮箱（默认会查询出实体中的所有字段）
* sql: select id,name,age,email from user where name like '%雨%' and age<40
*/
@Test
public void selectByWrapperSupper2() {
//直接创建一个条件构造器，获取使用Wrappers工具类
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//QueryWrapper<User> queryWrapper = Wrappers.query();
queryWrapper
//模糊查询，注意这里的字段都是数据库字段，而不是实体的变量名
.like("name", "雨")
//小于
.lt("age", 40)
//如果字段比较多，我们每个都写上会比较麻烦，我们可以使用排除法，毕竟只是去掉少量的字段，其他字段都保留
//参数一：实体类的Class
//参数二：Predicate函数式接口，test()方法返回boolean，表示是否保留当前遍历到的字段，返回true代表需要，false代表不需要
.select(User.class, info -> !info.getColumn().equals("create_time")
&& !info.getColumn().equals("manager_id"));
List<User> userList = userMapper.selectList(queryWrapper);
for (User user : userList) {
System.out.println(user);
}
}
/**
* 测试动态条件
*/
@Test
public void testCondition() {
String name = "王";
String email = "";
condition(name, email);
}
/**
* 查询条件，条件可传可不传
*
* @param name 姓名
* @param email 邮箱
*/
private void condition(String name, String email) {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//手动判空后，加入条件
// if (StringUtils.isNotEmpty(name)) {
// queryWrapper.like("name", name);
// }
// if (StringUtils.isNotEmpty(email)) {
// queryWrapper.like("email", email);
// }
//上面不够优雅，代码量大
queryWrapper.like(StringUtils.isNotEmpty(name), "name", name)
.like(StringUtils.isNotEmpty(email), "email", email);
List<User> userList = userMapper.selectList(queryWrapper);
for (User user : userList) {
System.out.println(user);
}
}
/**
* 使用实体Entity中的字段作为查询条件
*/
@Test
public void selectByWrapperEntity() {
//使用实体作为查询条件加入到where中
User whereUser = new User();
whereUser.setReadName("刘雨红");
whereUser.setAge(32);
QueryWrapper<User> queryWrapper = new QueryWrapper<>(whereUser);
//再给查询条件加like等操作也是可以的
queryWrapper
.like("name", "雨")
.lt("age", 40);
List<User> userList = userMapper.selectList(queryWrapper);
for (User result : userList) {
System.out.println(result);
}
}
/**
* 使用map作为sql的查询条件，map中的所有非空属性会作为sql的等于条件来拼接
*/
@Test
public void selectByWrapperAllEq() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
Map<String, Object> params = new HashMap<>();
params.put("name", "王天风");
//age参数，如果为null，会给生成的sql加上age is null，如果想过滤掉为null的属性字段，则将allEq的null2IsNull属性设置为false，默认为true
params.put("age", null);
//queryWrapper.allEq(params, false);
//函数式方式，传入BiPredicate过滤器，test()方法返回当前遍历到的键值对是否加入到条件中，返回true表示加入条件中，返回false代表不加入到条件中
queryWrapper.allEq(new BiPredicate<String, Object>() {
@Override
public boolean test(String key, Object value) {
//例如过滤掉name的字段
return !key.equals("name");
}
}, params);
List<User> userList = userMapper.selectList(queryWrapper);
for (User result : userList) {
System.out.println(result);
}
}
/**
* 查询，返回列表，但列表里面的元素不是实体，而是一个Map，每个Map就是一条记录的所有属性以键值对的形式存在
* 当我们查询的字段相比实体字段少很多的时候，使用实体去存放，会有很多属性是null，不是很优雅，我们使用Map存放会更加明确有什么属性和值
*/
@Test
public void selectByWrapperMaps() {
//直接创建一个条件构造器，获取使用Wrappers工具类
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//QueryWrapper<User> queryWrapper = Wrappers.query();
queryWrapper
.select("id", "name")
//模糊查询，注意这里的字段都是数据库字段，而不是实体的变量名
.like("name", "雨")
//小于
.lt("age", 40);
List<Map<String, Object>> userList = userMapper.selectMaps(queryWrapper);
for (Map<String, Object> map : userList) {
System.out.println(map);
}
}
/**
* 按照直属上级分组，查询每组的平均年龄、最大年龄、最小年龄。并且只取年龄总和小于500的组。
* sql:
* select avg(age) avg_age,min(age) min_age,max(age) max_age
* from user
* group by manager_id
* having sum(age) <500
*/
@Test
public void selectByWrapperMaps2() {
//直接创建一个条件构造器，获取使用Wrappers工具类
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//QueryWrapper<User> queryWrapper = Wrappers.query();
queryWrapper
//字段起别名：数据库字段名 别名
.select("avg(age) avg_age", "min(age) min_age", "max(age) max_age")
.groupBy("manager_id").having("sum(age) < {0}", 500);
List<Map<String, Object>> userList = userMapper.selectMaps(queryWrapper);
for (Map<String, Object> map : userList) {
System.out.println(map);
}
}
/**
* selectObjs()，只拿出数据的第一列的数据，其他列都被舍弃（sql中是会查询其他字段的，但是selectObjs()方法只选择第一列的数据）
* 场景：只返回第一列的时候可以用它
*/
@Test
public void selectByWrapperObjs() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper
.select("id", "name")
//模糊查询，注意这里的字段都是数据库字段，而不是实体的变量名
.like("name", "雨")
//小于
.lt("age", 40);
List<Object> userList = userMapper.selectObjs(queryWrapper);
for (Object o : userList) {
System.out.println(o);
}
}
/**
* 统计查询
*/
@Test
public void selectByWrapperCount() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper
//会帮我们在sql中添加 COUNT( 1 )，所以我们就不能使用select()方法来指定要查询的列了，否则会作为count的参数来使用
//.select("id", "name")
//除非你想count其他字段，就可以使用
.select("id")
//模糊查询，注意这里的字段都是数据库字段，而不是实体的变量名
.like("name", "雨")
//小于
.lt("age", 40);
Integer count = userMapper.selectCount(queryWrapper);
System.out.println("count：" + count);
}
/**
* 只查询出1条数据（必须查询结果只有1条，多条会报错）
*/
@Test
public void selectByWrapperOne() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper
.select("id", "name")
//模糊查询，注意这里的字段都是数据库字段，而不是实体的变量名
.eq("name", "刘雨红");
User user = userMapper.selectOne(queryWrapper);
System.out.println(user);
}
/**
* Lambda条件构造器
* 好处：仿误写，如果是普通方式，传入数据库字段名，如果写错了就会报错，Lambda表达式使用方法引用来获取字段信息
*/
@Test
public void selectByWrapperLambda() {
//Lambda条件构造器的3种创建方式
// LambdaQueryWrapper<User> queryWrapper = new QueryWrapper<User>().lambda();
// LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
LambdaQueryWrapper<User> queryWrapper = Wrappers.lambdaQuery();
queryWrapper
//where name like '%雨%'
.like(User::getReadName, "雨")
//and age < 40
.lt(User::getAge, 40);
List<User> userList = userMapper.selectList(queryWrapper);
for (User user : userList) {
System.out.println(user);
}
}
/**
* 查询，姓名为王姓，并且（年龄小于40岁或者邮箱不为空）
*/
@Test
public void selectByWrapperLambda2() {
LambdaQueryWrapper<User> queryWrapper = Wrappers.lambdaQuery();
queryWrapper
//where name like '%王%'
.likeRight(User::getReadName, "王")
//and (age < 40 or email is not null)
.and(lqw -> lqw.lt(User::getAge, 40).or().isNotNull(User::getEmail));
List<User> userList = userMapper.selectList(queryWrapper);
for (User user : userList) {
System.out.println(user);
}
}
/**
* 查询，姓名为王姓，并且年龄大于等于20
*/
@Test
public void selectByWrapperLambda3() {
LambdaQueryChainWrapper<User> chainWrapper = new LambdaQueryChainWrapper<>(userMapper);
List<User> userList = chainWrapper
//姓名为王姓
.like(User::getReadName, "雨")
//年龄大于等于20
.ge(User::getAge, 20)
.list();
for (User user : userList) {
System.out.println(user);
}
}
}
Copy the code- Paging query
BaseMapper also provides methods for paging queries.
- SelectPage, passed in as a Page parameter Page object, and the QueryWrapper conditional constructor, returned as an IPage object with total pages, total records, and other Page parameters
- SelectMapsPage, the second type of paging, is a generic Map, which is not an entity object, but encapsulates the fields and values of the entity class into a Map
- The constructor sets isSearchCount to false, indicating that the total number of pages is not queried. When the total number of pages is not needed, a query can be saved. For example, on the App side, loading more pages does not require the total number of records to judge
@test public void selectPage() {// Create a conditional constructor. Wrappers tool class QueryWrapper<User> QueryWrapper = new QueryWrapper<>(); queryWrapper.ge("age", 26); Page<User> page = new Page<>(1, 2); // page IPage<User> IPage = usermapper.selectPage (page, queryWrapper); System.out.println(" total pages: "+ iPage.getPages()); System.out.println(" iPage.getTotal() "); List<User> userList = iPage.getRecords(); for (User user : userList) { System.out.println(user); @test public void selectPage2() {// Create a conditional constructor. Wrappers tool class QueryWrapper<User> QueryWrapper = new QueryWrapper<>(); queryWrapper.ge("age", 26); Page<User> page = new Page<>(1, 2); // The second page, but the returned IPage generic type is Map, Is the entity class fields and values packaging to the Map in the IPage < Map < String, Object > > IPage = userMapper. SelectMapsPage (page, queryWrapper); System.out.println(" total pages: "+ iPage.getPages()); System.out.println(" iPage.getTotal() "); List<Map<String, Object>> userList = iPage.getRecords(); for (Map<String, Object> map : userList) { System.out.println(map); * * *}} / paging, not query to the total number of records (default will check, will have a total number of queries and paging query, query 2 times, such as constant tensile load on the scene is not to need to look up to the total number of records, */ @test public void selectPage3() {QueryWrapper<User> QueryWrapper = new QueryWrapper<>(); queryWrapper.ge("age", 26); Page<User> Page = new Page<>(1, 2, false); // page IPage<User> IPage = usermapper.selectPage (page, queryWrapper); System.out.println(" total pages: "+ iPage.getPages()); List<User> userList = iPage.getRecords(); for (User user : userList) { System.out.println(user); }}Copy the codeCustom Mapper methods
Provides custom Mapper methods
Add 2 custom methods to UserMapper.
There are two ways of customizing methods: annotations and XML. Here, XML is mainly used, but an annotation is also provided. Since they cannot exist at the same time, you can comment them out and refer to them again if necessary
- SelectAll, a custom query parameter supplied by the wrapper passed in, annotated with @param, and with a fixed parameter name, both Constants. Wrapper
- SelectUserPage, custom query parameter, paginating function, need to pass 2 parameters, Page object and Wrapper object, same parameter annotated with @param, and the parameter name is fixed, both Constants. Wrapper
Public interface UserMapper extends BaseMapper<User> {/** * */ / @select (" Select * from mp_user ${ew.customSQLSegment}") List<User> selectAll(@Param(Constants.WRAPPER) Wrapper<User> wrapper); */ IPage<User> selectUserPage(Page<User> Page, @param (Constants.WRAPPER) WRAPPER <User> WRAPPER); }Copy the codeConfigure the Mapper XML file
There’s a custom Mapper interface, either with annotations, or with XML, which is usually XML. In the resource directory, set up multilayer folder, com/imooc/mybatisplus mapper, new XML file folder: UserMapper. XML
Note: The Mapper interface must have the same name as the corresponding XML file! And the namespace is the full class name of Mapper
<? The XML version = "1.0" encoding = "utf-8"? > <! DOCTYPE mapper PUBLIC "- / / mybatis.org//DTD mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > <! -- the namespace, a namespace must be the same as the Dao interface - > < mapper namespace = "com. Imooc. Mybatisplus. Dao. UserMapper" > <! <select id="selectAll" resultType="User"> select * from mp_user ${ew.customsqlSegment} <select id="selectUserPage" resultType="User"> select * from mp_user ${ew. </mapper>Copy the codeTest methods for customizing SQL
Conditions are configured in the Wrapper instance, and the query fields are provided by custom SQL statements.
SelectAll () all and selectMyPage() paging queries.
@test public void selectMy() {LambdaQueryWrapper<User> lambdaQuery = Wrappers.lambdaquery (); LambdaQuery // WHERE name like '% king %'. LikeRight (User::getReadName, And (LQW -> lqw.lt(User::getAge, 40).or().isnotnull (User::getEmail)); List<User> userList = userMapper.selectAll(lambdaQuery); for (User user : userList) { System.out.println(user); @test public void selectMyPage() {QueryWrapper<User> QueryWrapper = new QueryWrapper<>(); queryWrapper.ge("age", 26); Page<User> page = new Page<>(1, 2); / / custom paging query IPage < User > IPage = userMapper. SelectUserPage (page, queryWrapper); System.out.println(" total pages: "+ iPage.getPages()); System.out.println(" iPage.getTotal() "); List<User> userList = iPage.getRecords(); for (User user : userList) { System.out.println(user); }}Copy the codeGeneral Service
We used a generic Mapper above, whereas we would normally have a Service that combines multiple Mapper to do business logic.
MyBatis-Plus also provides us with a general Service solution.
Establish the Service interface and implementation
Establish service package, UserService interface, inherit IService interface, generic write entity class type
public interface UserService extends IService<User> {
}
Copy the codeCreate the UserServiceImpl implementation class and inherit ServiceImpl, passing in the Mapper interface and entity class type
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
}
Copy the codetest
- GetOne, gets 1 result. If there are more than one results, an error is reported. If you do not want to report errors, the first one is taken by default, and the subsequent throwEx property is set to false
- SaveBatch: saves multiple objects to the database table. If the entity object ID field has a value, it is updated; if it is null, it indicates new
- LambdaQuery, call list, chain programming query
- LambdaUpdate, call update, chain programming update
- LambdaUpdate, call remove, chain programming delete
@RunWith(SpringRunner.class) @SpringBootTest(classes = Application.class) public class ServiceTest { @Autowired private UserService userService; @test public void getOne() {public void getOne() {public void getOne(); The default value is true. If there are more than one exceptions, the exception will be thrown. If the value is set to false, no exceptions will be thrown and a warning will be printed. And take the first return User result = userservice.getone (Wrappers.<User>lambdaQuery().gt(User::getAge, 25), false); System.out.println(result); } @test public void batch() {User user1 = new User(); Xu Li user1. SetReadName (" 1 "); user1.setAge(28); User user2 = new User(); User2. SetReadName (" Xu Li 2 "); user2.setAge(29); List<User> userList = Arrays.asList(user1, user2); boolean isSuccess = userService.saveBatch(userList); System.out.println(" insert successfully: "+ isSuccess); */ @test public void batch2() {User user1 = new User(); User1. SetReadName (" Xu Li 3 "); user1.setAge(28); User user2 = new User(); user2.setUserId(1289004540795330562L); A. user2. SetReadName (" "); user2.setAge(30); List<User> userList = Arrays.asList(user1, user2); boolean isSuccess = userService.saveOrUpdateBatch(userList); System.out.println(" insert successfully: "+ isSuccess); } /** * chain programming, Query */ @test public void chain() {List<User> userList = userservice.lambdaQuery (). Gt (User::getAge, 25). Like (User::getReadName, "treadname ").list(); for (User user : userList) { System.out.println(user); }} /** * */ @test public void chain2() {Boolean isSuccess = userservice.lambdaupdate ().eq(User::getAge, 25) .set(User::getAge, 26) .update(); System.out.println(" update successful: "+ isSuccess); } /** * chain programming, */ @test public void chain3() {Boolean isSuccess = userservice.lambdaupdate ().eq(User::getAge, 24).remove(); System.out.println(" delete successfully: "+ isSuccess); }}Copy the codeAR model
The AR mode, that is, the ActiveRecord mode, uses entity classes to add, delete, and change data. You need to modify entity classes.
- Entity classes inherit from Model classes, and generics pass in the type of the entity class
- Generate the serialized UID
@Data @EqualsAndHashCode(callSuper = false) @TableName("mp_user") public class User extends Model<User> { private static final long serialVersionUID = 1L; / /... Omit field, same as before}Copy the codetest
- Insert, insert data
- SelectById, based on the primary key query on the entity, can also be passed in as a method parameter
- UpdateById updates the data set on the entity based on the entity primary key
- DeleteById, deleted based on the entity primary key, can also be passed in as a method parameter
- InsertOrUpdate, new or update, judged according to whether the entity primary key has a value, if there is a value, it is updated, if it is null, it is new
@runwith (springrunner.class) @springboottest (classes = application.class) public class ARTest {/** * insert data */ @test public void insert() { User user = new User(); User. SetReadName (" Liu Hua "); user.setAge(29); user.setEmail("[email protected]"); user.setManagerId(1088248166370832385L); user.setCreateTime(LocalDateTime.now()); boolean isSuccess = user.insert(); System.out.println(" insert successfully: "+ isSuccess); } /** * select */ @test public void selectById() {User User = new User(); User result = user.selectById(1288460195017072641L); System.out.println(result); } @test public void selectById2() {User User = new User();} @test public void selectById2() {User User = new User(); user.setUserId(1288839790966910978L); User result = user.selectById(); System.out.println(result); @test public void updateById() {User User = new User(); user.setUserId(1288839790966910978L); User. SetReadName (" liu grass "); boolean isSuccess = user.updateById(); System.out.println(" update successful: "+ isSuccess); } @test public void deleteById() {User User = new User(); boolean isSuccess = user.deleteById(1288839790966910978L); System.out.println(" delete successfully: "+ isSuccess); } @test public void deleteById2() {User User = new User();} @test public void deleteById2() {User User = new User(); user.setUserId(1288839790966910978L); boolean isSuccess = user.deleteById(); System.out.println(" delete successfully: "+ isSuccess); */ @test public void insertOrUpdate() {User User = new User(); // user.setuserID (1288842439342780417L); User. SetReadName (" b "); user.setAge(29); user.setEmail("[email protected]"); user.setManagerId(1088248166370832385L); user.setCreateTime(LocalDateTime.now()); boolean isSuccess = user.insertOrUpdate(); System.out.println(" insert successfully: "+ isSuccess); }}Copy the code