person

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")
);

新评论