MyBatis-Plus（簡稱 MP）是一個 MyBatis 的增強工具，在 MyBatis 的基礎上只做增強不做改變，為簡化開發(fā)、提高效率而生。
在繼續(xù)看這篇文章之前，請確保你會 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 '創(chuàng)建時間',
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>
創(chuàng)建實體類
public class User {
private long id;
private String name;
private long age;
private String email;
private long managerId;
private LocalDateTime createTime;
......
// get 和 set 方法、toString 方法省略
}
創(chuàng)建持久層接口
@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
排除非表字段的方式
在實體類中，有的字段可能只用于暫時存儲數(shù)據(jù)，并不需要將其插入數(shù)據(jù)庫中，可使用以下方式達到插入時排除非表字段：
-
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
查詢
根據(jù) 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}
根據(jù)多個 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}
根據(jù) 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");
// 創(chuàng)建日期為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 中字段不全出現(xiàn)的處理方法
// 第一種情況：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")
);
