一、mybatis-plus入门
MyBatis-Plus（简称 MP）是一个 MyBatis 的增强工具，在 MyBatis 的基础上只做增强不做改变，为简化开发、提高效率而生。
1、创建一个spring boot工程
参考：https://www.cnblogs.com/zwh0910/p/15484263.html#autoid-3-0-0
2、导入pom文件
<!--spring boot 父工程-->
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.0.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.zwh</groupId>
<artifactId>mybatis-plus</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.1.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.16</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
3、创建数据库
#创建用户表 CREATE TABLE user ( id BIGINT(20) PRIMARY KEY NOT NULL COMMENT '主键', name VARCHAR(30) DEFAULT NULL COMMENT '姓名', age INT(11) DEFAULT NULL COMMENT '年龄', email VARCHAR(50) DEFAULT NULL COMMENT '邮箱', manager_id BIGINT(20) DEFAULT NULL COMMENT '直属上级id', create_time DATETIME DEFAULT NULL COMMENT '创建时间', CONSTRAINT manager_fk FOREIGN KEY (manager_id) REFERENCES user (id) ) ENGINE=INNODB CHARSET=UTF8; #初始化数据： INSERT INTO user (id, name, age, email, manager_id , create_time) VALUES (1087982257332887553, '大boss', 40, 'boss@baomidou.com', NULL , '2019-01-11 14:20:20'), (1088248166370832385, '王天风', 25, 'wtf@baomidou.com', 1087982257332887553 , '2019-02-05 11:12:22'), (1088250446457389058, '李艺伟', 28, 'lyw@baomidou.com', 1088248166370832385 , '2019-02-14 08:31:16'), (1094590409767661570, '张雨琪', 31, 'zjq@baomidou.com', 1088248166370832385 , '2019-01-14 09:15:15'), (1094592041087729666, '刘红雨', 32, 'lhm@baomidou.com', 1088248166370832385 , '2019-01-14 09:48:16');
4、启动类
启动类上加@MapperScan,指定要变成实现类的接口所在的包，然后包下面的所有接口在编译之后都会生成相应的实现类
@SpringBootApplication @MapperScan("com.zwh.dao") public class MySpringBootApplication { public static void main(String[] args) { SpringApplication.run(MySpringBootApplication.class); } }
5、创建实体类User
@Data public class User extends Model<User> { private Long id; private String name; private Integer age; private String email; private Long managerId; private LocalDateTime createTime; }
6、dao接口
public interface UserMapper extends BaseMapper<User> { }
继承BaseMapper
如果添加了mapper.xml，那么需要配置xml的位置，否则会出现无法绑定。
mybatis-plus.mapperLocations=classpath*:mapper/*.xml
7、配置数据源
#mysql spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver spring.datasource.url=jdbc:mysql://localhost:3306/mybatis_plus?characterEncoding=utf8&useoldAliasMetadataBehavior=true&allowMultiQueries=true&serverTimezone=CTT spring.datasource.username=root spring.datasource.password=123456
8、测试查询所有
新建测试类
@RunWith(SpringRunner.class) @SpringBootTest public class SampleTest { @Resource private UserMapper userMapper; @Test public void testSelect() { System.out.println(("----- selectAll method test ------")); List<User> userList = userMapper.selectList(null); userList.forEach(System.out::println); } }
结果如下：
----- selectAll method test ------ User(id=1087982257332887553, name=大boss, age=40, email=boss@baomidou.com, managerId=null, createTime=2019-01-11T14:20:20) User(id=1088248166370832385, name=王天风, age=25, email=wtf@baomidou.com, managerId=1087982257332887553, createTime=2019-02-05T11:12:22) User(id=1088250446457389058, name=李艺伟, age=28, email=lyw@baomidou.com, managerId=1088248166370832385, createTime=2019-02-14T08:31:16) User(id=1094590409767661570, name=张雨琪, age=31, email=zjq@baomidou.com, managerId=1088248166370832385, createTime=2019-01-14T09:15:15) User(id=1094592041087729666, name=刘红雨, age=32, email=lhm@baomidou.com, managerId=1088248166370832385, createTime=2019-01-14T09:48:16)
9、测试保存
@RunWith(SpringRunner.class) @SpringBootTest public class SampleTest { @Resource private UserMapper userMapper; @Test public void insert(){ User user = new User(); user.setName("马伟奇"); user.setAge(18); user.setManagerId(1088248166370832385L); user.setCreateTime(LocalDateTime.now()); int rows = userMapper.insert(user); System.out.println("影响记录数:" + rows); } }
增加和修改操作，只会根据对象中存在不为null的属性来操作数据，当数据为null时不会改变数据库中的数据。
10、测试删除
@RunWith(SpringRunner.class) @SpringBootTest public class SampleTest { @Resource private UserMapper userMapper; @Test public void delete(){ int i = userMapper.deleteById(1465286349850615810L); System.out.println("影响记录数:" + i); } }
11、测试修改
@Test public void update(){ User user = new User(); user.setId(1465290164649811969L); user.setName("杨利伟"); user.setAge(38); user.setManagerId(1088248166370832385L); user.setCreateTime(LocalDateTime.now()); int rows = userMapper.updateById(user); System.out.println("影响记录数:" + rows); }
12、日志级别和日志格式
级别控制
#级别控制
logging.level.com.zwh=debug
logging.level设置日志级别，后面跟生效的区域，比如root表示整个项目，也可以设置为某个包下，也可以具体到某个类名（日志级别的值不区分大小写）
日志格式
logging.pattern.console='%d{yyyy-MM-dd HH:mm:ss,SSS} [%t] [%c]-[%p] %m%n'
测试
[DEBUG] ==> Preparing: INSERT INTO user ( id, name, age, manager_id, create_time ) VALUES ( ?, ?, ?, ?, ? ) [DEBUG] ==> Parameters: 1465286349850615810(Long), 马伟奇(String), 18(Integer), 1088248166370832385(Long), 2021-11-29T19:47:43.788(LocalDateTime) [DEBUG] <== Updates: 1
二、常见注解
常用注解：@TableName、@TableId、@TableField
@TableName:其用来将实体对象与数据库表名进行对应。当实体名与数据库表名不一致时使用。
@TableId:其是主键注解，指明是主键，默认把id作为主键。属性type用来指定类型：
1）IdType.AUTO是自动增长；
2）IdType.ASSIGN_UUID是String类型的uuid
3）IdType.INPUT是根据用户的输入作为主键
@TableField:其是表的其他字段注解。属性exist用来指明是否是数据库的字段，值为false时不映射数据库表字段。
注意：MP开启了驼峰转下划线，操作的sql语句，会依据属性名转换为下划线的列名称，如，会将userId转换为user_id 会将userIdS转换为user_id_s
@Data // 数据库表的名字叫mp_user ，类名字如果叫User，就需要添加当前注解 @TableName("mp_user") public class User extends Model<User> { // 数据库主键id如果叫user_id，属性叫做userId，也找不到会报错，因为 // mybatis-plus：默认会找属性叫id的为主键，如果把主键的换成别的名字，比如userId，那就需要添加当前注解 @TableId private Long id; // 数据库名字叫name，属性叫realName，就需要给属性添加当前注解 @TableField("name") private String realName; private Integer age; private String email; private Long managerId; private LocalDateTime createTime; }
将数据库中表mp_user的字段id改为t_id，然后修改实体User的属性id并设置自动增长
@Data @TableName(value = "mp_user") public class User { //指定自动增长 @TableId(value = "t_id",type = IdType.AUTO) private Long id; ...... }
三、排除非表字段
1、添加属性remark
在javabean中添加一个属性 remark ，但是这个属性在数据表中并没有这个字段。
@Data public class User extends Model<User> { private Long id; private String name; private Integer age; private String email; private Long managerId; private LocalDateTime createTime; private String remark; }
数据表字段
测试没有排除非表字段得情况：
@Test public void insert(){ User user = new User(); user.setName("马伟奇"); user.setAge(18); user.setManagerId(1088248166370832385L); user.setCreateTime(LocalDateTime.now()); user.setRemark("备注信息"); int rows = userMapper.insert(user); System.out.println("影响记录数:" + rows); }
报错如下：
报错原因是因为属性有这个字段，数据库没有这个列，但是mybatis-plus框架映射了这个属性，导致数据库找不到这个列，所以报错，我们就需要在bean对象里面排除这个对象
@Data public class User extends Model<User> { private Long id; private String name; private Integer age; private String email; private Long managerId; private LocalDateTime createTime; /** * 排除非表字段的三种方式 * 1、private transient String remark; 无法序列化 * 2、private static String remark; 实例无法独有一份 * 3、@TableField(exist=false) */ @TableField(exist = false) private String remark; }
四、mybatis-plus查询方法
user实体类
@Data public class User extends Model<User> { private Long id; private String name; private Integer age; private String email; private Long managerId; private LocalDateTime createTime; @TableField(exist = false) private String remark; }
1、根据id查询selectById
@RunWith(SpringRunner.class) @SpringBootTest public class SampleTest { @Autowired private UserMapper userMapper; @Test public void selectById(){ User user = userMapper.selectById(1094592041087729666L); System.out.println(user); } }
结果：
User(id=1094592041087729666, name=刘红雨, age=32, email=lhm@baomidou.com, managerId=1088248166370832385, createTime=2019-01-14T09:48:16, remark=null)
2、根据多个id查询selectBatchIds
@Test public void selectIds(){ List<Long> idsList = Arrays.asList(1088248166370832385L, 1153268754419863553L, 1094590409767661570L); List<User> userList = userMapper.selectBatchIds(idsList); userList.forEach(System.out::println); }
结果：
User(id=1088248166370832385, name=王天风, age=25, email=wtf@baomidou.com, managerId=1087982257332887553, createTime=2019-02-05T11:12:22, remark=null) User(id=1094590409767661570, name=张雨琪, age=31, email=zjq@baomidou.com, managerId=1088248166370832385, createTime=2019-01-14T09:15:15, remark=null)
3、根据map查询selectByMap
@Test public void selectByMap(){ Map<String, Object> map = new HashMap<>(); // 注意key是数据库里面的列，不是javabean的属性 map.put("name","王天风"); map.put("age",25); List<User> userLists = userMapper.selectByMap(map); userLists.forEach(System.out::println); }
结果：
User(id=1088248166370832385, name=王天风, age=25, email=wtf@baomidou.com, managerId=1087982257332887553, createTime=2019-02-05T11:12:22, remark=null)
4、Java命令里面的EQ、NE、GT、LT、GE、LE分别代表含义
EQ 就是 EQUAL等于
NE 就是 NOT EQUAL不等于
GT 就是 GREATER THAN大于
LT 就是 LESS THAN小于
GE 就是 GREATER THAN OR EQUAL 大于等于
LE 就是 LESS THAN OR EQUAL 小于等于
详细说明如下：
| 方法 | 说明 | 示例 |
| eq | 等于 |
|
| allEq | 全等于 | eq("name", "老王")--->name = '老王' |
| ne | 不等于 | ne("name", "老王")--->name <> '老王' |
| gt | 大于 | gt("age", 18)--->age > 18 |
| ge | 大于等于 | ge("age", 18)--->age >= 18 |
| lt | 小于 | lt("age", 18)--->age < 18 |
| le | 小于等于 | le("age", 18)--->age <= 18 |
| between | BETWEEN 值1 AND 值2 | between("age", 18, 30)--->age between 18 and 30 |
| notBetween | NOT BETWEEN 值1 AND 值2 | notBetween("age", 18, 30)--->age not between 18 and 30 |
| like | LIKE '%值%' | |
| notLike | NOT LIKE '%值%' | notLike("name", "王")--->name not like '%王%' |
| likeLeft | LIKE '%值' | likeLeft("name", "王")--->name like '%王' |
| likeRight | LIKE '值%' | likeRight("name", "王")--->name like '王%' |
| isNull | 字段为null | isNull("name")--->name is null |
| isNotNull | 字段不为null | isNotNull("name")--->name is not null |
| in | 同数据库in | in("age",{1,2,3})--->age in (1,2,3) |
| notIn | 同数据库not in | notIn("age",{1,2,3})--->age not in (1,2,3) |
| inSql | 相当于子查询和in |
|
| notInSql | 相当于子查询和not in |
|
| groupBy | 同数据库group by | groupBy("id", "name")--->group by id,name |
| orderBy/orderByDesc | 同数据库order by 字段/order by 字段 desc | orderBy("id")--->order by id ASC |
| having | 同数据库having | having("sum(age) > 10")--->having sum(age) > 10 |
| or/and | 同数据库or/and | 略 |
| netsted | 正常嵌套 不带 AND 或者 OR | |
| apply | 字符串拼接 |
|
| last | 拼接在最后，谨慎使用 | last("limit 1")---> limit 1 |
| exists/notExists | 拼接sql | exists("select id from table where age = 1")--->exists (select id from table where age = 1) |
| select | 指定要查询的字段 | |
| set | 全局修改 | set("name", "")--->数据库字段值变为空字符串 |
5、条件构造查询
@Test public void selectByWrapper(){ QueryWrapper<User> userQueryWrapper = new QueryWrapper<>(); userQueryWrapper.like("name","雨").lt("age",40); List<User> userList = userMapper.selectList(userQueryWrapper); userList.forEach(System.out::println); }
结果：
User(id=1094590409767661570, name=张雨琪, age=31, email=zjq@baomidou.com, managerId=1088248166370832385, createTime=2019-01-14T09:15:15, remark=null) User(id=1094592041087729666, name=刘红雨, age=32, email=lhm@baomidou.com, managerId=1088248166370832385, createTime=2019-01-14T09:48:16, remark=null)
6、条件构造查询
/** * 名字中包含雨 年龄大于等于20 并且 小于等于40 并且email 不为空 * name like '%雨%' and age between 20 and 40 and email is not null */ @Test public void selectByWrapper2(){ QueryWrapper<User> userQueryWrapper = new QueryWrapper<>(); userQueryWrapper.like("name","雨").between("age",20,40).isNotNull("email"); List<User> userList = userMapper.selectList(userQueryWrapper); userList.forEach(System.out::println); }
结果：
User(id=1094590409767661570, name=张雨琪, age=31, email=zjq@baomidou.com, managerId=1088248166370832385, createTime=2019-01-14T09:15:15, remark=null) User(id=1094592041087729666, name=刘红雨, age=32, email=lhm@baomidou.com, managerId=1088248166370832385, createTime=2019-01-14T09:48:16, remark=null)
selectCount：查询符合条件的条数，一般和selectList结合使用。
@Test public void selectByWrapper2(){ QueryWrapper<User> userQueryWrapper = new QueryWrapper<>(); userQueryWrapper.like("name","雨").between("age",20,40).isNotNull("email"); List<User> userList = userMapper.selectList(userQueryWrapper); Integer integer = userMapper.selectCount(userQueryWrapper); userList.forEach(System.out::println); System.out.println(integer); }
7、条件构造查询
/** * 名字为王姓或者年龄大于等于25，按照年龄降序排列额，年龄相同按照id升序排列 * name like '王%' or age>25 order by age desc,id asc */ @Test public void selectByWrapper3(){ QueryWrapper<User> userQueryWrapper = new QueryWrapper<>(); userQueryWrapper.likeRight("name","王").or().ge("age",25).orderByDesc("age").orderByAsc("id"); List<User> userList = userMapper.selectList(userQueryWrapper); userList.forEach(System.out::println); }
结果：
User(id=1087982257332887553, name=大boss, age=40, email=boss@baomidou.com, managerId=null, createTime=2019-01-11T14:20:20, remark=null) User(id=1094592041087729666, name=刘红雨, age=32, email=lhm@baomidou.com, managerId=1088248166370832385, createTime=2019-01-14T09:48:16, remark=null) User(id=1094590409767661570, name=张雨琪, age=31, email=zjq@baomidou.com, managerId=1088248166370832385, createTime=2019-01-14T09:15:15, remark=null) User(id=1088250446457389058, name=李艺伟, age=28, email=lyw@baomidou.com, managerId=1088248166370832385, createTime=2019-02-14T08:31:16, remark=null) User(id=1088248166370832385, name=王天风, age=25, email=wtf@baomidou.com, managerId=1087982257332887553, createTime=2019-02-05T11:12:22, remark=null)
8、条件构造查询
/** * 名字为王姓并且(年龄小于40或邮箱不为空) * name like '王%' and (age<40or email is not null) */ @Test public void selectByWrapper5(){ QueryWrapper<User> userQueryWrapper = new QueryWrapper<>(); userQueryWrapper.likeRight("name","王").and(wq->wq.lt("age",40).or().isNotNull("email")); List<User> userList = userMapper.selectList(userQueryWrapper); userList.forEach(System.out::println); }
结果：
User(id=1088248166370832385, name=王天风, age=25, email=wtf@baomidou.com, managerId=1087982257332887553, createTime=2019-02-05T11:12:22, remark=null)
9、条件查询
@Test public void selectByWrapper6(){ QueryWrapper<User> userQueryWrapper = new QueryWrapper<>(); userQueryWrapper.likeRight("name","王").or(new Function<QueryWrapper<User>, QueryWrapper<User>>() { @Override public QueryWrapper<User> apply(QueryWrapper<User> userQueryWrapper) { return userQueryWrapper.lt("age",40).gt("age",20).isNotNull("email"); } }); // userQueryWrapper.likeRight("name","王") // .or(wq->wq.lt("age",40).gt("age",20).isNotNull("email")); List<User> userList = userMapper.selectList(userQueryWrapper); userList.forEach(System.out::println); }
结果
User(id=1088248166370832385, name=王天风, age=25, email=wtf@baomidou.com, managerId=1087982257332887553, createTime=2019-02-05T11:12:22, remark=null) User(id=1088250446457389058, name=李艺伟, age=28, email=lyw@baomidou.com, managerId=1088248166370832385, createTime=2019-02-14T08:31:16, remark=null) User(id=1094590409767661570, name=张雨琪, age=31, email=zjq@baomidou.com, managerId=1088248166370832385, createTime=2019-01-14T09:15:15, remark=null) User(id=1094592041087729666, name=刘红雨, age=32, email=lhm@baomidou.com, managerId=1088248166370832385, createTime=2019-01-14T09:48:16, remark=null)
10、条件查询
/** * (年龄小于40或邮箱不为空)并且名字为王姓 * (age < 40 or email is not null) and name like '王%' */ @Test public void selectByWrapper7(){ QueryWrapper<User> userQueryWrapper = new QueryWrapper<>(); userQueryWrapper.nested(new Function<QueryWrapper<User>, QueryWrapper<User>>() { @Override public QueryWrapper<User> apply(QueryWrapper<User> userQueryWrapper) { return userQueryWrapper.lt("age", 40).or().isNotNull("email"); } }).likeRight("name", "王"); // userQueryWrapper.nested(wq->wq.lt("age",40).or().isNotNull("email")).likeRight("name","王"); List<User> userList = userMapper.selectList(userQueryWrapper); userList.forEach(System.out::println); }
结果
User(id=1088248166370832385, name=王天风, age=25, email=wtf@baomidou.com, managerId=1087982257332887553, createTime=2019-02-05T11:12:22, remark=null)
11、条件查询
/** * 年龄为30，31，34，35 * age in(30，31，34，35) */ @Test public void selectByWrapper8(){ QueryWrapper<User> userQueryWrapper = new QueryWrapper<>(); userQueryWrapper.in("age",Arrays.asList(30,31,34,35)); List<User> userList = userMapper.selectList(userQueryWrapper); userList.forEach(System.out::println); }
结果
User(id=1094590409767661570, name=张雨琪, age=31, email=zjq@baomidou.com, managerId=1088248166370832385, createTime=2019-01-14T09:15:15, remark=null)
12、条件查询
/** * 返回满足条件的一条语句 */ @Test public void selectByWrapper9(){ QueryWrapper<User> userQueryWrapper = new QueryWrapper<>(); userQueryWrapper.in("age",Arrays.asList(30,31,34,35)).last("limit 1"); List<User> userList = userMapper.selectList(userQueryWrapper); userList.forEach(System.out::println); }
结果：
User(id=1094590409767661570, name=张雨琪, age=31, email=zjq@baomidou.com, managerId=1088248166370832385, createTime=2019-01-14T09:15:15, remark=null)
13、不列出全部的列
/** * 名字中包含 雨 并且年龄小于40 * name like '%雨%' and age < 40 */ @Test public void selectByWrapper10(){ QueryWrapper<User> userQueryWrapper = new QueryWrapper<>(); userQueryWrapper.select("id","name").like("name","雨").lt("age",40); List<User> userList = userMapper.selectList(userQueryWrapper); userList.forEach(System.out::println); }
结果：
User(id=1094590409767661570, name=张雨琪, age=null, email=null, managerId=null, createTime=null, remark=null) User(id=1094592041087729666, name=刘红雨, age=null, email=null, managerId=null, createTime=null, remark=null)
14、不列出所有的列
/** * 名字中包含 雨 并且年龄小于40 * name like '%雨%' and age < 40 * 排除manager_id和create_time */ @Test public void selectByWrapper11(){ QueryWrapper<User> userQueryWrapper = new QueryWrapper<>(); userQueryWrapper.like("name","雨").lt("age",40) .select(User.class,info->!info.getColumn().equals("create_time")&& !info.getColumn().equals("manager_id")); List<User> userList = userMapper.selectList(userQueryWrapper); userList.forEach(System.out::println); }
结果：
User(id=1094590409767661570, name=张雨琪, age=31, email=zjq@baomidou.com, managerId=null, createTime=null, remark=null) User(id=1094592041087729666, name=刘红雨, age=32, email=lhm@baomidou.com, managerId=null, createTime=null, remark=null)
五、分页查询
1、单表分页查询
新建一个类配置其分页
@EnableTransactionManagement @Configuration @MapperScan("com.zwh.dao") public class MybatisPlusConfig { @Bean public PaginationInterceptor paginationInterceptor() { PaginationInterceptor paginationInterceptor = new PaginationInterceptor(); return paginationInterceptor; } }
测试
@Test public void selectByWrapper12(){ QueryWrapper<User> userQueryWrapper = new QueryWrapper<>(); userQueryWrapper.likeRight("name","王").or(new Function<QueryWrapper<User>, QueryWrapper<User>>() { @Override public QueryWrapper<User> apply(QueryWrapper<User> userQueryWrapper) { return userQueryWrapper.lt("age",40).gt("age",20).isNotNull("email"); } }); IPage<User> page=new Page<>(1,2); // userQueryWrapper.likeRight("name","王") // .or(wq->wq.lt("age",40).gt("age",20).isNotNull("email")); IPage<User> userIPage = userMapper.selectPage(page, userQueryWrapper); List<User> records = userIPage.getRecords(); records.forEach(System.out::println); }
结果：
User(id=1088248166370832385, name=王天风, age=25, email=wtf@baomidou.com, managerId=1087982257332887553, createTime=2019-02-05T11:12:22, remark=null) User(id=1088250446457389058, name=李艺伟, age=28, email=lyw@baomidou.com, managerId=1088248166370832385, createTime=2019-02-14T08:31:16, remark=null)
2、单表自定义分页查询
@Test public void selectByWrapper13(){ QueryWrapper<User> userQueryWrapper = new QueryWrapper<>(); userQueryWrapper.likeRight("name","王").or(new Function<QueryWrapper<User>, QueryWrapper<User>>() { @Override public QueryWrapper<User> apply(QueryWrapper<User> userQueryWrapper) { return userQueryWrapper.lt("age",40).gt("age",20).isNotNull("email"); } }); IPage<User> page=new Page<>(1,2); // userQueryWrapper.likeRight("name","王") // .or(wq->wq.lt("age",40).gt("age",20).isNotNull("email")); IPage<User> userIPage = userMapper.selectPageList(page, userQueryWrapper); List<User> records = userIPage.getRecords(); records.forEach(System.out::println); }
在dao的接口中定义方法
public interface UserMapper extends BaseMapper<User> { IPage<User> selectPageList(IPage<User> page, @Param(Constants.WRAPPER) QueryWrapper<User> userQueryWrapper); }
对第二个参数queryWrapper使用了@Param注解起别名，其名称使用的是常量的值（实际值是ew），常量的定义也是由官方定义好的，如下：
public interface Constants extends StringPool { String MD5 = "MD5"; String ENTITY = "et"; String ENTITY_DOT = "et."; String WRAPPER = "ew"; String WRAPPER_DOT = "ew."; String WRAPPER_ENTITY = "ew.entity"; String WRAPPER_SQLSEGMENT = "ew.sqlSegment"; String WRAPPER_EMPTYOFNORMAL = "ew.emptyOfNormal"; String WRAPPER_NONEMPTYOFNORMAL = "ew.nonEmptyOfNormal"; String WRAPPER_NONEMPTYOFENTITY = "ew.nonEmptyOfEntity"; String WRAPPER_EMPTYOFWHERE = "ew.emptyOfWhere"; String WRAPPER_NONEMPTYOFWHERE = "ew.nonEmptyOfWhere"; String WRAPPER_ENTITY_DOT = "ew.entity."; String U_WRAPPER_SQL_SET = "ew.sqlSet"; String Q_WRAPPER_SQL_SELECT = "ew.sqlSelect"; String COLUMN_MAP = "cm"; String COLUMN_MAP_IS_EMPTY = "cm.isEmpty"; String COLLECTION = "coll"; String WHERE = "WHERE"; String MP_OPTLOCK_VERSION_ORIGINAL = "MP_OPTLOCK_VERSION_ORIGINAL"; String MP_OPTLOCK_VERSION_COLUMN = "MP_OPTLOCK_VERSION_COLUMN"; String MP_OPTLOCK_ET_ORIGINAL = "MP_OPTLOCK_ET_ORIGINAL"; }
在xml中编写sql
<?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"> <mapper namespace="com.zwh.dao.UserMapper"> <select id="selectPageList" resultType="com.zwh.entity.User"> select * from user ${ew.customSqlSegment} </select> </mapper>
那么后面的条件就直接使用"${ew.customSqlSegment}"进行代替了，它会根据设置的条件去查询。
结果如下：
User(id=1088248166370832385, name=王天风, age=25, email=wtf@baomidou.com, managerId=1087982257332887553, createTime=2019-02-05T11:12:22, remark=null) User(id=1088250446457389058, name=李艺伟, age=28, email=lyw@baomidou.com, managerId=1088248166370832385, createTime=2019-02-14T08:31:16, remark=null)
3、多表自定义分页查询
多表分页和单表的分页原来是一样的道理。现假设有两个表，t_student（学生表）和t_clazz（班级表）
1）先创建两个表
CREATE TABLE `clazz` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL COMMENT '班级名称', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL COMMENT '学生姓名', `clazzId` int(11) DEFAULT NULL COMMENT '班级id', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8; insert into clazz values(1,'计算机1班'),(2,'计算机2班'),(3,'计算机3班'); insert into student values(null,'赵敏',1),(null,'张明',3),(null,'李慧',3),(null,'赵美美',2),(null,'张峰',2),(null,'孙强',2);
2）创建学生实体类
@Data @TableName(value = "user") public class Student { @TableId(value = "id",type = IdType.AUTO) private Long id; @TableField(value = "name") private String name; @TableField(value = "clazzId") private Integer clazzId; @TableField(exist = false) private String clazzName; }
3）创建controller接口
@GetMapping("/list") public JSONObject getList(Integer curr, Integer limit) { QueryWrapper<Student> queryWrapper = new QueryWrapper<>(); queryWrapper.like("t1.name", "张"); IPage<Student> page = new Page<>(curr, limit); IPage<Student> stu = studentDao.selectPageList(page, queryWrapper); JSONObject jsonObject = new JSONObject(); jsonObject.put("data", stu.getRecords()); jsonObject.put("total", stu.getTotal()); return jsonObject; }
4）在dao接口定义方法
IPage<Student> selectPageList(IPage<Student> page, @Param(Constants.WRAPPER) QueryWrapper<Student> queryWrapper);
5）在xml编写sql
<select id="selectPageList" resultType="com.zys.mybatisplusdemo.entity.Student"> select t1.*,t2.name "clazzName" from student t1 left join clazz t2 on t1.clazzId = t2.id ${ew.customSqlSegment} </select>
6）调用此接口，数据可以正常查询。执行日志如下：
当然，对于多表查询，如果使用默认的条件查询不能满足要求，可以把QueryMapper换成Mmap，那么在xml中使用对应的参数查询即可。 不过这种方式有些麻烦，但是可以实现更为复杂的需求
dao接口的方法：
IPage<Student> selectPageList2(IPage<Student> page, @Param(Constants.WRAPPER)Map<String, Object> map);
xml的sql：
<select id="selectPageList2" resultType="com.zys.mybatisplusdemo.entity.Student"> select t1.*, t2.name "clazzName" from student t1 left join clazz t2 on t1.clazzId = t2.id <where> <if test="ew.name != null and ew.name != ''"> and t1.name like concat('%',#{ew.name},'%') </if> </where> </select>
需要注意的是，在if标签中也需要带ew，否则会报错。
六、实际项目使用mybatis-plus
controller
import com.heima.model.common.dtos.ResponseResult; import com.heima.model.user.dtos.LoginDto; import com.heima.user.service.ApUserService; import io.swagger.annotations.Api; import io.swagger.annotations.ApiOperation; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.RequestBody; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; @RestController @RequestMapping("/api/v1/login") @Api(value = "app端用户登录", tags = "ap_user", description = "app端用户登录API") public class AppUserLoginController { @Autowired private ApUserService apUserService; @PostMapping("/login_auth") @ApiOperation("用户登录") public ResponseResult login(@RequestBody LoginDto loginDto){ ResponseResult result = apUserService.login(loginDto); return result; } }
service接口
import com.baomidou.mybatisplus.extension.service.IService; import com.heima.model.common.dtos.ResponseResult; import com.heima.model.user.dtos.LoginDto; import com.heima.model.user.pojos.ApUser; public interface ApUserService extends IService<ApUser> { ResponseResult login(LoginDto loginDto); }
service实现类
import com.baomidou.mybatisplus.core.toolkit.Wrappers; import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; import com.heima.model.common.dtos.ResponseResult; import com.heima.model.common.enums.AppHttpCodeEnum; import com.heima.model.user.dtos.LoginDto; import com.heima.model.user.pojos.ApUser; import com.heima.user.mapper.ApUserMapper; import com.heima.utils.common.AppJwtUtil; import com.heima.utils.common.MD5Utils; import lombok.extern.slf4j.Slf4j; import org.apache.commons.lang.StringUtils; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import org.springframework.util.DigestUtils; import java.util.HashMap; import java.util.Map; @Service @Transactional @Slf4j public class ApUserServiceImpl extends ServiceImpl<ApUserMapper, ApUser> implements ApUserService { @Override public ResponseResult login(LoginDto loginDto) { // 用户名和密码均不为空则正常登录，否则为游客登录 if (StringUtils.isNotBlank(loginDto.getPhone()) && StringUtils.isNotBlank(loginDto.getPassword())) { // 根据手机号查询用户信息 ApUser apUser = getOne(Wrappers.<ApUser>lambdaQuery().eq(ApUser::getPhone, loginDto.getPhone())); if (null == apUser) { return ResponseResult.errorResult(AppHttpCodeEnum.DATA_NOT_EXIST, "用户信息不存在！"); } // 存在则获取盐 String salt = apUser.getSalt(); String password = loginDto.getPassword(); String pwd = DigestUtils.md5DigestAsHex((password + salt).getBytes()); if (!pwd.equals(apUser.getPassword())) { return ResponseResult.errorResult(AppHttpCodeEnum.LOGIN_PASSWORD_ERROR); } // 如果密码正确，则返回jwt String token = AppJwtUtil.getToken(apUser.getId().longValue()); // 使用Map封装返回的数据 Map<String, Object> map = new HashMap<>(); // 注意：用户的盐和密码不能返回 apUser.setSalt(""); apUser.setPassword(""); map.put("user",apUser); map.put("token",token); return ResponseResult.okResult(map); } else { // 游客登录只需要返回token Map<String, Object> map = new HashMap<>(); String token = AppJwtUtil.getToken(0L); map.put("token",token); return ResponseResult.okResult(map); } } }
mapper接口
import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.heima.model.user.pojos.ApUser; import org.apache.ibatis.annotations.Mapper; @Mapper public interface ApUserMapper extends BaseMapper<ApUser> { }
实体类
import com.baomidou.mybatisplus.annotation.IdType; import com.baomidou.mybatisplus.annotation.TableField; import com.baomidou.mybatisplus.annotation.TableId; import com.baomidou.mybatisplus.annotation.TableName; import lombok.Data; import java.io.Serializable; import java.util.Date; @Data @TableName("ap_user") public class ApUser implements Serializable { private static final long serialVersionUID = 1L; /** * 主键 */ @TableId(value = "id", type = IdType.AUTO) private Integer id; /** * 密码、通信等加密盐 */ @TableField("salt") private String salt; /** * 用户名 */ @TableField("name") private String name; /** * 密码,md5加密 */ @TableField("password") private String password; /** * 手机号 */ @TableField("phone") private String phone; /** * 头像 */ @TableField("image") private String image; /** * 0 男 1 女 2 未知 */ @TableField("sex") private Boolean sex; /** * 0 未 1 是 */ @TableField("is_certification") private Boolean certification; /** * 是否身份认证 */ @TableField("is_identity_authentication") private Boolean identityAuthentication; /** * 0正常 1锁定 */ @TableField("status") private Boolean status; /** * 0 普通用户 1 自媒体人 2 大V */ @TableField("flag") private Short flag; /** * 注册时间 */ @TableField("created_time") private Date createdTime; }
