mybatis-plus的使用

pom.xml文件配置

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/mpdb?serverTimezone=UTC
    username: root
    password: root
# 开启mp的日志（输出到控制台）
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  #全局策略配置
  global-config:
    db-config:
      #id生成策略控制
      id-type: assign_id
      #映射表前缀，这样所有表名就不需要设定前缀
      table-prefix: tb_
        # 逻辑删除字段名
      logic-delete-field: deleted
        # 逻辑删除字面值：未删除为0
      logic-not-delete-value: 0
        # 逻辑删除字面值：删除为1
      logic-delete-value: 1

分页查询时需要添加 分页拦截器

 @Test
    public  void  pageTest(){
        IPage<User> page = new Page<>(1,2);
        IPage<User> selectPage = userMapper.selectPage(page, null);
        System.out.println(selectPage);
    }

@Configuration
public class MybatisPlusConfig {
    @Bean
    public MybatisPlusInterceptor  interceptor(){
         //MybatisPlus拦截器
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        //增加分页拦截器
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
        return interceptor;
    }
}

CRUD操作

@SpringBootTest
public class UserMapperTest {

    @Autowired
    private UserMapper userMapper;
    
    /**
     * 分页
     * @throws Exception
     */
    @Test
    public void pageTest() throws Exception {
        IPage<User> ipage = new Page<>(2, 3);
        userMapper.selectPage(ipage, null);
        System.out.println(ipage);
    }

    /**
     * 查询
     * @throws Exception
     */
    @Test
    public void selectTest() throws Exception {
        User user = userMapper.selectById(1);
        System.out.println(user);
        List<User> userList = userMapper.selectBatchIds(Arrays.asList(1, 5, 10));
        userList.forEach(System.out::println);
        //LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<User>()
        //        .eq(User::getName, "tom");//where name = 'tom'
        LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        lambdaQueryWrapper.eq(User::getName, "jack");//where name = 'tom'
        user = userMapper.selectOne(lambdaQueryWrapper);
        System.out.println(user);
        Integer count = userMapper.selectCount(lambdaQueryWrapper);
        System.out.println(count);
        List<User> users = userMapper.selectList(lambdaQueryWrapper);
        System.out.println(users);
    }

    /**
     * 更新
     *
     * @throws Exception
     */
    @Test
    public void updateTest() throws Exception {
        User user = User.builder()
                //.id(8L)
                //.name("UPDATE")
                .build();
        //int row = userMapper.updateById(user);
        UpdateWrapper<User> wrapper = new UpdateWrapper<>();
        wrapper.set("tel", "13412341234");  // set tel = 13412341234
        wrapper.eq("name", "UPDATE");  // where name = 'UPDATE'
        int row = userMapper.update(user, wrapper);
        System.out.println(row);
    }

    /**
     * 删除
     * @throws Exception
     */
    @Test
    public void deleteTest() throws Exception {
        int row = userMapper.deleteById(29);
        System.out.println(row);

        //row = userMapper.deleteBatchIds(Arrays.asList(15, 18, 21));
        //System.out.println(row);
//
        //QueryWrapper<User> wrapper = new QueryWrapper<>();
        //wrapper.like("name", "Test");   // where name like '%Test%'
        //row = userMapper.delete(wrapper);
        //System.out.println(row);

    }

    /**
     * 增加
     * @throws Exception
     */
    @Test
    public void insertTest() throws Exception {

        /*for (int i = 0; i < 20; i++) {
            User user = User.builder()
                    .name("Test" + i)
                    .age(12)
                    .password("123")
                    .build();
            int row = userMapper.insert(user);
        }*/

        User user = User.builder()
                .name("张五")
                .age(12)
                .password("123")
                .build();
        int row = userMapper.insert(user);
        System.out.println(row);
        System.out.println(user.getId());  //自己通过算法算出来的
    }

    /**
     * mp 快速入门
     * @throws Exception
     */
    @Test
    public void quickTest() throws Exception {
        List<User> userList = userMapper.selectList(null);
        userList.forEach(System.out::println);
    }

聚合函数和链式编程

@SpringBootTest
public class DQLTest {

    @Autowired
    private UserMapper userMapper;

    /**
     * 乐观锁测试
     * @throws Exception
     */
    @Test
    public void optimisticLockerTest() throws Exception {
        //A先查询一次id=30的数据
        User user = userMapper.selectById(30);
        //B先查询一次id=30的数据
        User user2 = userMapper.selectById(30);

        //B先改  成功
        user2.setAge(20);
        userMapper.updateById(user2);

        //A再改  失败
        user.setAge(25);
        userMapper.updateById(user);
    }

    /**
     * 分组，排序
     * @throws Exception
     */
    @Test
    public void groupByAndSortTest() throws Exception {
        LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        //lambdaQueryWrapper.select(User::getPassword);   // select password
        //lambdaQueryWrapper.groupBy(User::getPassword);  // group by password
        //List<User> userList = userMapper.selectList(lambdaQueryWrapper);
        String name = null;
        lambdaQueryWrapper.orderBy(name != null, true, User::getAge );   // order by age asc
        lambdaQueryWrapper.orderByDesc(User::getAge, User::getName);  // order by age asc
        List<User> userList = userMapper.selectList(lambdaQueryWrapper);
        for (User user : userList) {
            System.out.println(user);
        }


    }

    /**
     * or
     * @throws Exception
     */
    @Test
    public void orTest() throws Exception {
        LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        //lambdaQueryWrapper.between(User::getAge, 10, 100);
        String name = "Test";
        Integer age = 20;
        lambdaQueryWrapper
                .between(age != null, User::getAge, 10, 100)
                .like(name != null, User::getName, name)
                .or()
                .like(User::getPassword, "123");
        //select 全字段 from user where age between 10 and 20 and name like '%Test%' or password like '%123%'
        List<User> userList = userMapper.selectList(lambdaQueryWrapper);
        for (User user : userList) {
            System.out.println(user);
        }

    }
    
    /**
     * and
     * @throws Exception
     */
    @Test
    public void andTest() throws Exception {
        LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        lambdaQueryWrapper.ge(User::getAge, 10);    //where age >=10
        lambdaQueryWrapper.lt(User::getAge, 100);   //age < 100
        //链接编程
        lambdaQueryWrapper.like(User::getName, "Test")    //name like '%Test%'
                .likeRight(User::getTel, "13");    // tel like '13%'

        List<User> userList = userMapper.selectList(lambdaQueryWrapper);
        for (User user : userList) {
            System.out.println(user);
        }

    }