Mybatis Plus 入门
2019-12-12 本文已影响0人
Edwinpanzzz
MyBatis-Plus（简称 MP）是一个 MyBatis 的增强工具，在 MyBatis 的基础上只做增强不做改变，为简化开发、提高效率而生。
在继续看这篇文章之前，请确保你会 SpringBoot 以及 Mybatis，以便有更好的观看体验。
首先，丢出 SQL 语句：
/*
Navicat Premium Data Transfer
Source Server : MySQL
Source Server Type : MySQL
Source Server Version : 50623
Source Host : localhost:3306
Source Schema : mp
Target Server Type : MySQL
Target Server Version : 50623
File Encoding : 65001
Date: 09/12/2019 14:19:03
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` bigint(20) NOT NULL COMMENT '主键',
`name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
`age` int(11) NULL DEFAULT NULL COMMENT '年龄',
`email` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '邮箱',
`manager_id` bigint(20) NULL DEFAULT NULL COMMENT '直属上级id',
`create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`) USING BTREE,
INDEX `manager_fk`(`manager_id`) USING BTREE,
CONSTRAINT `manager_fk` FOREIGN KEY (`manager_id`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1087982257332887525, '刘明强', 31, 'lmq@baomidou.com', 1088248166370832385, '2019-12-06 02:29:20');
INSERT INTO `user` VALUES (1087982257332887553, '大boss', 40, 'boss@baomidou.com', NULL, '2019-01-11 14:20:20');
INSERT INTO `user` VALUES (1088248166370832385, '王天风', 25, 'wtf@baomidou.com', 1087982257332887553, '2019-02-05 11:12:22');
INSERT INTO `user` VALUES (1088250446457389058, '李艺伟', 28, 'lyw@baomidou.com', 1088248166370832385, '2019-02-14 08:31:16');
INSERT INTO `user` VALUES (1094590409767661570, '张雨琪', 31, 'zjq@baomidou.com', 1088248166370832385, '2019-01-14 09:15:15');
INSERT INTO `user` VALUES (1094592041087729666, '刘红雨', 32, 'lhm@baomidou.com', 1088248166370832385, '2019-01-14 09:48:16');
SET FOREIGN_KEY_CHECKS = 1;
Mybatis Plus 快速入门
导入依赖
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.2.0</version>
</dependency>
创建实体类
public class User {
private long id;
private String name;
private long age;
private String email;
private long managerId;
private LocalDateTime createTime;
......
// get 和 set 方法、toString 方法省略
}
创建持久层接口
@Mapper
public interface UserMapper extends BaseMapper<User> {
}
继承 BaseMapper 后不需要任何操作，就可以使用了！！！
测试
@SpringBootTest
class MybatisPlusApplicationTests {
@Autowired
private UserMapper userMapper;
@Test
public void selectList(){
// 不添加任何条件查询
List<User> users = userMapper.selectList(null);
for (User user1: users){
System.out.println(user1);
}
}
}
查询结果：
DEBUG==> Preparing: SELECT id,create_time,name,manager_id,age,email FROM user
DEBUG==> Parameters:
TRACE<== Columns: id, create_time, name, manager_id, age, email
TRACE<== Row: 1087982257332887525, 2019-12-06 02:29:20, 刘明强, 1088248166370832385, 31, lmq@baomidou.com
TRACE<== Row: 1087982257332887553, 2019-01-11 14:20:20, 大boss, null, 40, boss@baomidou.com
TRACE<== Row: 1088248166370832385, 2019-02-05 11:12:22, 王天风, 1087982257332887553, 25, wtf@baomidou.com
TRACE<== Row: 1088250446457389058, 2019-02-14 08:31:16, 李艺伟, 1088248166370832385, 28, lyw@baomidou.com
TRACE<== Row: 1094590409767661570, 2019-01-14 09:15:15, 张雨琪, 1088248166370832385, 31, zjq@baomidou.com
TRACE<== Row: 1094592041087729666, 2019-01-14 09:48:16, 刘红雨, 1088248166370832385, 32, lhm@baomidou.com
DEBUG<== Total: 6
User{id=1087982257332887525, name='刘明强', age=31, email='lmq@baomidou.com', managerId=1088248166370832385, createTime=2019-12-06T02:29:20}
User{id=1087982257332887553, name='大boss', age=40, email='boss@baomidou.com', managerId=0, 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}
常用注解
- 指定表名：@TableName
- 指定主键：@TableId
- 指定字段：@TableField
排除非表字段的方式
在实体类中，有的字段可能只用于暂时存储数据，并不需要将其插入数据库中，可使用以下方式达到插入时排除非表字段：
-
transient
private transient String remark; -
static
private static String remark; -
@TableField
@TableField(exist=false) private String remark;
CRUD
新增
@Test
public void insert(){
User user = new User();
user.setId(1087982257332117525L);
user.setName("张三");
user.setAge(31);
user.setEmail("zs@baomidou.com");
user.setManagerId(1088248166370832385L);
user.setCreateTime(LocalDateTime.now());
int result = userMapper.insert(user);
System.out.println(result);
}
运行结果：
DEBUG==> Preparing: INSERT INTO user ( id, create_time, name, manager_id, age, email ) VALUES ( ?, ?, ?, ?, ?, ? )
DEBUG==> Parameters: 1087982257332117525(Long), 2019-12-09T14:38:34.424022600(LocalDateTime), 张三(String), 1088248166370832385(Long), 31(Long), zs@baomidou.com(String)
DEBUG<== Updates: 1
1
查询
根据 id 查询
@Test
public void selectById(){
User user = userMapper.selectById(1088248166370832385L);
System.out.println(user);
}
查询结果：
DEBUG==> Preparing: SELECT id,create_time,name,manager_id,age,email FROM user WHERE id=?
DEBUG==> Parameters: 1088248166370832385(Long)
TRACE<== Columns: id, create_time, name, manager_id, age, email
TRACE<== Row: 1088248166370832385, 2019-02-05 11:12:22, 王天风, 1087982257332887553, 25, wtf@baomidou.com
DEBUG<== Total: 1
User{id=1088248166370832385, name='王天风', age=25, email='wtf@baomidou.com', managerId=1087982257332887553, createTime=2019-02-05T11:12:22}
根据多个 id 查询
@Test
public void selectByIds() {
List<Long> list = new ArrayList<>();
list.add(1088248166370832385L);
list.add(1087982257332887553L);
List<User> users = userMapper.selectBatchIds(list);
users.forEach(System.out::println);
}
查询结果：
DEBUG==> Preparing: SELECT id,create_time,name,manager_id,age,email FROM user WHERE id IN ( ? , ? )
DEBUG==> Parameters: 1088248166370832385(Long), 1087982257332887553(Long)
TRACE<== Columns: id, create_time, name, manager_id, age, email
TRACE<== Row: 1087982257332887553, 2019-01-11 14:20:20, 大boss, null, 40, boss@baomidou.com
TRACE<== Row: 1088248166370832385, 2019-02-05 11:12:22, 王天风, 1087982257332887553, 25, wtf@baomidou.com
DEBUG<== Total: 2
User{id=1087982257332887553, name='大boss', age=40, email='boss@baomidou.com', managerId=0, createTime=2019-01-11T14:20:20}
User{id=1088248166370832385, name='王天风', age=25, email='wtf@baomidou.com', managerId=1087982257332887553, createTime=2019-02-05T11:12:22}
根据 map 查询
@Test
public void selectByMap() {
Map<String, Object> map = new HashMap<>();
// key 为表中列名
map.put("name", "王天风");
map.put("age", 25);
List<User> users = userMapper.selectByMap(map);
users.forEach(System.out::println);
}
查询结果：
DEBUG==> Preparing: SELECT id,create_time,name,manager_id,age,email FROM user WHERE name = ? AND age = ?
DEBUG==> Parameters: 王天风(String), 25(Integer)
TRACE<== Columns: id, create_time, name, manager_id, age, email
TRACE<== Row: 1088248166370832385, 2019-02-05 11:12:22, 王天风, 1087982257332887553, 25, wtf@baomidou.com
DEBUG<== Total: 1
User{id=1088248166370832385, name='王天风', age=25, email='wtf@baomidou.com', managerId=1087982257332887553, createTime=2019-02-05T11:12:22}
条件构造器查询
QueryWrapper<User> wrapper = new QueryWrapper<>();
// 名字中包含雨并且年龄小于 40
// name like '%雨%' and age < 40
wrapper.like("name", "雨").lt("age", 40);
// 名字中包含雨年并且龄大于等于 20 且小于等于 40 并且 email 不为空
// name like '%雨%' and age between 20 and 40 and email is not null
wrapper.like("name", "雨").between("age", 20, 40).isNotNull("email");
// 名字为王姓或者年龄大于等于 25，按照年龄降序排列，年龄相同按照 id 升序排列
// name like '王%' or age >= 25 order by age desc, id asc
wrapper.likeRight("name", "王").or().ge("age", 25).orderByDesc("age").orderByAsc("id");
// 创建日期为2019年2月14日并且直属上级为名字为王姓
// date_format(create_time,'%Y-%m-%d')='2019-02-14' and manager_id in (select id from user where name like '王%')
wrapper.apply("date_format(create_time,'%Y-%m-%d') = {0}", "2019-02-14").inSql("manager_id", "select id from user where name like '王%'");
// 名字为王姓并且年龄小于40或邮箱不为空
// name like '王%' and (age < 40 or email is not null)
wrapper.likeRight("name", "王").and(wq->wq.lt("age", 40).or().isNotNull("email"));
// 名字为王姓或者年龄小于 40 并且年龄大于 20 并且邮箱不为空
// name like '王%' or (age < 40 and age > 20 and email is not null)
wrapper.likeRight("name", "王").or(wq->wq.lt("age", 40).gt("age", 20).isNotNull("email"));
// 年龄小于40或邮箱不为空并且名字为王姓
// (age < 40 or email is not null) and name like '王%'
wrapper.nested(wq->wq.lt("age", 40).or().isNotNull("email")).like("name", "王");
// 年龄为 30、31、34、35
// age in (30, 31, 34, 35)
wrapper.in("age", Arrays.asList(30, 31, 34, 35));
// 只返回满足条件的其中一条语句即可
// limit 1
wrapper.in("age", Arrays.asList(30, 31, 34, 35)).last("limit 1");
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
select 中字段不全出现的处理方法
// 第一种情况：select id,name
// from user
// where name like '%雨%' and age < 40
wrapper.select("id", "name").like("name", "雨").lt("age", 40);
// 第二种情况：select id,name,age,email
// from user
// where name like '%雨%' and age < 40
wrapper.like("name", "雨").lt("age", 40).select(
User.class,
info -> !info.getColumn().equals("create_time") && !info.getColumn().equals("manager_id")
);
