Mybatis Plus 入门

使用SpringBoot集成MybatisPlus

Mybatis-Plus官网

1. pom

<dependency>
	 <groupId>com.baomidou</groupId>
	 <artifactId>mybatis-plus-boot-starter</artifactId>
	 <version>3.2.0</version>
</dependency>

2. yml

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/user?useSSL=false&serverTimezone=GMT%2B8
    username: root
    password: 123456
#打开打印sql
logging:
  level:
    com.scitc.dao: trace
#  pattern:
#    console: '%p%m%n'
########################################  后面配置的
mybatis-plus:
  mapper-locations: classpath:mapper/*.xml   #配置mapper.xml位置
  global-config:
    db-config:
      id-type: uuid   #全局主键策略
#  config-location: classpath:mybatis-config.xml
  type-aliases-package: com.scitc.entity

#  config-location和configuration同时出现会错
  configuration:
    map-underscore-to-camel-case: true

3. entity

@Data
@TableName("tb_user") //指定表名
@EqualsAndHashCode(callSuper = false)
public class User extends Model<User> {
    @TableId(type = IdType.AUTO) //指定主键自增策略
    private Long id;
    //指定列名 使用entity作为条件查询的时候，模糊字段
    @TableField(value = "name" condition = SqlCondition.LIKE)
    private String name;
    //指定默认查询条件
    //@TableField(condition = "%s&gt;#{%s}")
    private Integer age;
    private String email;
    private Long managerId;
    private LocalDateTime createTime;
}

4. mapper

继承BaseMapper<T>

public interface UserMapper extends BaseMapper<User> {
}

常用注解

1. @TableName 表名注解
   
2. @TableId 主键注解,不设置的话默认为id字段
   
   IdType
   
3. @TableField

• 也可以自定义 condition

• 设置了@TableField(update = "%s+1")，不管设置多少就只+1

• 忽略表字段三种方式

  • transient关键字

  private transient String remark;
  

  • 使用 static关键字

  private static String remark;
  

  • @TableField(exist = false) 用这个

crud操作

1. insert

添加成功后，主键id会被添加到user.id中

//junit5
@SpringBootTest
@ExtendWith(SpringExtension.class)

public class UserMapperTest {
    @Autowired
    private UserMapper userMapper;


    @Test
    public void insertUserTest() {
        User user = new User();
        user.setName("小咸鱼233333");
        user.setAge(30);
        user.setEmail("4444444@qq.com");
        user.setManagerId(1L);
        user.setCreateTime(LocalDateTime.now());
        int rows = userMapper.insert(user);
        System.out.println("rows:" + rows);
        System.out.println("user:" + user);
    }
}

2. 查询

普通查询

• 根据id查询

@Test
public void selectByIdTest() {
    User user = userMapper.selectById(10L);
    System.out.println(user);
}

• 根据id批量查询

@Test
public void selectBatchIdsTest() {
    List<Long> longList = Arrays.asList(1L, 2L);
    List<User> users = userMapper.selectBatchIds(longList);
    users.forEach(System.out::println);
}

• map条件查询

@Test
public void selectByMapTest() {
    Map<String, Object> map = new HashMap<>();
    map.put("age", 58);
//    key对应的数据库字段
//    where age=85
    List<User> users = userMapper.selectByMap(map);
    users.forEach(System.out::println);
}

条件构造查询

构建条件的方式

QueryWrapper<User> queryWrapper = new QueryWrapper<>();

QueryWrapper<User> query = Wrappers.query();

几个案例

1. 名字中包含雨，并且年龄小于40
   name like '%雨%' and age<40

   @Test
   public void selectByWrapperTest1() {
       QueryWrapper<User> queryWrapper = new QueryWrapper<>();
       queryWrapper.like("name", "雨").lt("age", 40);
       List<User> userList = userMapper.selectList(queryWrapper);
       userList.forEach(System.out::println);
   }
   

2. 名字中包含 雨，并且年龄大于等于20且小于等于40并且email不为空
   name like '%雨%' and age between 20 and 40 and email is not null

   @Test
   public void selectByWrapperTest2() {
       QueryWrapper<User> queryWrapper = new QueryWrapper<>();
       queryWrapper.like("name", "雨").between("age", 20, 40).isNotNull("email");
       List<User> userList = userMapper.selectList(queryWrapper);
       userList.forEach(System.out::println);
   }
   

3. 姓王或者年龄大于25，按照年龄呢降序排序，年龄相同按照id升序排序
   name like '王%' or age>=25 order by age desc,id asc

   @Test
   public void selectByWrapperTest3() {
       QueryWrapper<User> queryWrapper = new QueryWrapper<>();
       queryWrapper.likeRight("name", "王").or().ge("age", 25).orderByDesc("age").orderByAsc("id");
       List<User> userList = userMapper.selectList(queryWrapper);
       userList.forEach(System.out::println);
   }
   

4. 创建日期为2019年2月14日并且直属上级为名字为王姓
   date_format(create_time,'%Y-%m-%d')='2019-02-14' and manager_id in (select id from user where name like '王%')

   @Test
   public void selectByWrapperTest4() {
       QueryWrapper<User> queryWrapper = new QueryWrapper<>();
       queryWrapper.apply("date_format(create_time,'%Y-%m-%d') = {0}", "2019-02-14")
               .inSql("manager_id", "select id from tb_user where name like '王%'");
       List<User> userList = userMapper.selectList(queryWrapper);
       userList.forEach(System.out::println);
   }
   

5. 名字为王姓并且（年龄小于40或邮箱不为空）
   name like '王%' and (age<40 or email is not null)

   @Test
   public void selectByWrapperTest5() {
       QueryWrapper<User> queryWrapper = new QueryWrapper<>();
       queryWrapper.likeRight("name", "王").and(wq -> wq.lt("age", 40).or().isNotNull("email"));
       List<User> userList = userMapper.selectList(queryWrapper);
       userList.forEach(System.out::println);
   }
   

6. 名字为王姓或者（年龄小于40并且年龄大于20并且邮箱不为空）
   name like '王%' or (age<40 and age>20 and email is not null)

   @Test
   public void selectByWrapperTest6() {
       QueryWrapper<User> queryWrapper = new QueryWrapper<>();
       queryWrapper.likeRight("name", "王").or(wp -> wp.lt("age", 40).gt("age", 20).isNotNull("email"));
       List<User> userList = userMapper.selectList(queryWrapper);
       userList.forEach(System.out::println);
   }
   

7. （年龄小于40或邮箱不为空）并且名字为王姓
   (age<40 or email is not null) and name like '王%'

   @Test
   public void selectByWrapperTest7() {
       QueryWrapper<User> queryWrapper = new QueryWrapper<>();
       queryWrapper.nested(wp -> wp.lt("age", 40).or().isNotNull("email")).likeRight("name", "王");
       List<User> userList = userMapper.selectList(queryWrapper);
       userList.forEach(System.out::println);
   }
   

8. 年龄为30、31、34、35
   age in (30、31、34、35)

   @Test
   public void selectByWrapperTest8() {
       QueryWrapper<User> queryWrapper = new QueryWrapper<>();
       queryWrapper.in("age", Arrays.asList(30, 31, 34, 35));
       List<User> userList = userMapper.selectList(queryWrapper);
       userList.forEach(System.out::println);
   }
   

9. 只返回满足条件的其中一条语句即可
   limit 1

   @Test
   public void selectByWrapperTest9() {
       QueryWrapper<User> queryWrapper = new QueryWrapper<>();
       queryWrapper.in("age", Arrays.asList(30, 31, 34, 35)).last("limit 1");
       List<User> userList = userMapper.selectList(queryWrapper);
       userList.forEach(System.out::println);
   }
   

select不查处全字段

1. 名字中包含雨并且年龄小于40
   select id,name,age,email

   @Test
   public void selectByWrapperSupper2() {
       QueryWrapper<User> queryWrapper = new QueryWrapper<>();
       queryWrapper.select(User.class, info -> !info.getColumn().equals("create_time") &&
               !info.getColumn().equals("manager_id")).like("name", "雨").lt("name", 40);
       List<User> userList = userMapper.selectList(queryWrapper);
       userList.forEach(System.out::println);
   }
   

2. 排除不相关字段
   名字中包含雨并且年龄小于40(需求1加强版)
   select id,name,age,email

   @Test
   public void selectByWrapperSupper2() {
       QueryWrapper<User> queryWrapper = new QueryWrapper<>();
       queryWrapper.select(User.class, info -> !info.getColumn().equals("create_time") &&
               !info.getColumn().equals("manager_id")).like("name", "雨").lt("age", 40);
       List<User> userList = userMapper.selectList(queryWrapper);
       userList.forEach(System.out::println);
   }
   

• entity 查询

@Test
public void selectByWrapperEntity() {

    User user = new User();
    user.setName("张");
    user.setAge(30);
    QueryWrapper<User> queryWrapper = new QueryWrapper<>(user);
    List<User> userList = userMapper.selectList(queryWrapper);
    userList.forEach(System.out::println);
}

• allEq

@Test
public void selectByWrapperAllEq() {

    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    Map<String, Object> params = new HashMap<>();
    params.put("name", "王天风");
    params.put("age", null);
    queryWrapper.allEq(params);

    List<User> userList = userMapper.selectList(queryWrapper);
    userList.forEach(System.out::println);
}

• selectMaps
  查什么就只有什么字段映射

@Test
public void selectByWrapperMaps() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.select("id", "name").like("name", "雨").lt("age", 40);

    List<Map<String, Object>> userList = userMapper.selectMaps(queryWrapper);
    userList.forEach(System.out::println);
   /*结果
    {name=张雨琪, id=1094590409767661570}
	{name=刘红雨, id=1094592041087729666}
	{name=大雨, id=1194180916445003778}
	*/
}

• 按照直属上级分组，查询每组的平均年龄、最大年龄、最小年龄。并且只取年龄总和小于500的组
  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() {

    QueryWrapper<User> queryWrapper = new QueryWrapper<>();

    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);
    userList.forEach(System.out::println);
}

• selectObjs 只返回第一个字段的值

@Test
public void selectByWrapperObjs() {

    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.select("id", "name").like("name", "雨").lt("age", 40);

    List<Object> objectList = userMapper.selectObjs(queryWrapper);
    System.out.println("objectList:" + objectList);
//        objectList:[1094590409767661570, 1094592041087729666, 1194180916445003778]
}

• selectCount 根据 Wrapper 条件，查询总记录数
• selectOne 查一条，多了报错

lambda三种方式

1.LambdaQueryWrapper<User> lambda = new QueryWrapper<User>().lambda();
2.LambdaQueryWrapper<User> lambda2 = new LambdaQueryWrapper<>();
3. LambdaQueryWrapper<User> lambda3 = Wrappers.<User>lambdaQuery();

@Test
public void selectLambda() {
    LambdaQueryWrapper<User> lambda3 = Wrappers.<User>lambdaQuery();
    lambda3.like(User::getName,"雨").lt(User::getAge,40);
    List<User> users = userMapper.selectList(lambda3);
    users.forEach(System.out::println);
}

@Test
public void selectLambda2() {
    List<User> userList = new LambdaQueryChainWrapper<User>(userMapper)
            .like(User::getName, "雨")
            .gt(User::getAge, 20).list();
    userList.forEach(System.out::println);
}

分页查询

他的分页已经封装好了，直接传入 当前页码（current）—> page，记录条数（size）—>pageSize

@Test
public void selectPage(){
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
   queryWrapper.ge("age", 50);

    Page<User> page = new Page<>(1,5);
    IPage<User> userIPage = userMapper.selectPage(page, queryWrapper);
    List<User> userList = userIPage.getRecords();
    userList.forEach(System.out::println);
}

自定义分页

mapper

  List<User> selectMyLimit(@Param("start") Integer start, @Param("size") Integer size);

mapper.xml

    <select id="selectMyLimit" resultType="com.scitc.entity.User">
        SELECT id,name,age,email,manager_id,create_time FROM tb_user
        ORDER BY create_time
        limit  #{start},#{size}
    </select>

调用

    @Test
    public void MyLimit(){
        List<User> userList = userMapper.selectMyLimit(2,5);
        userList.forEach(System.out::println);
    }

Wrapper 构造条件分页

mapper

IPage<User> selectPageWrapper(Page page, @Param(Constants.WRAPPER) Wrapper<User> wrapper);

mapper.xml

<select id="selectPageWrapper" resultType="com.scitc.entity.User">
    SELECT id,name,age,email,manager_id,create_time FROM tb_user
    ${ew.customSqlSegment}
</select>

调用

@Test
public void queryWrapperLimit2(){
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.gt("age",30).like("name","雨");
    Page<User> page = new Page<>(1,10);
    IPage<User> userIPage = userMapper.selectPageWrapper(page, queryWrapper);
    List<User> userList = userIPage.getRecords();

    userList.forEach(System.out::println);
}

更新

@Test
public void updateUser(){
    User user = new User();
    user.setName("哈哈哈哈");
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    wrapper.eq("id","1194173798547456001");
    int update = userMapper.update(user, wrapper);
    System.out.println(update);
}

删除

@Test
public void deleteUser(){
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    wrapper.eq("id","1194173798547456001");
    int effectedNum = userMapper.delete(wrapper);
    System.out.println(effectedNum);`在这里插入代码片`

}

批量删除

@Test
public void deleteUser2(){
    List<Long> longList = Arrays.asList(1194525879024263169L, 1194208277894840321L);
    int effectedNum = userMapper.deleteBatchIds(longList);
    System.out.println(effectedNum);
}

AR模式

public class User extends Model<User> {
	...
}

通过user对象去调crud方法

User user = new User();

1. 添加：user.insert()
2. 查询：user.selectById();
3. 更新：user.updateById()
   4.删除： user.deleteById();

通用service

继承 extends IService<User>

public interface UserService extends IService<User> {
}

impl

@Service
public class UserServiceImpl extends ServiceImpl<UserMapper,User> implements UserService{
}

测试：
getOne() 有多个 result 是否抛出异常

    @Test
    public void getOne() {
        User one = userService.getOne(Wrappers.<User>lambdaQuery().ge(User::getAge, 30), false);

        System.out.println(one);
    }

saveBatch():批量添加

@Test
public void saveBatch() {
    User user1 = new User();
    user1.setName("批量用户1");
    user1.setAge(30);
    user1.setManagerId(1L);
    user1.setEmail("123466789@qq.com");

    User user2 = new User();
    BeanUtils.copyProperties(user1, user2);
    user2.setName("批量用户2");
    List<User> userList = Arrays.asList(user1, user2);

    boolean batch = userService.saveBatch(userList);
    System.out.println(batch);
}

• 批量修改 updateBatchById

@Test
public void updateBatch() {
    User user1 = new User();
    user1.setId(1L);
    user1.setName("批量用户11");

    User user2 = new User();
    user2.setId(2L);
    user2.setName("批量用户22");

    List<User> userList = Arrays.asList(user1, user2);
    boolean batch = userService.updateBatchById(userList);
    System.out.println(batch);
}

• 调用lambdaQuery

@Test
public void chain() {
    List<User> userList = userService.lambdaQuery().gt(User::getAge, 25).list();
    userList.forEach(System.out::println);
}

学习于 慕课网