day 01 mp

【1】MybatisPlus概述

MyBatis-Plus（简称 MP）是一个 MyBatis 的增强工具，在 MyBatis 的基础上只做增强不做改变，为简化开发、提高效率而生（提供了快速使用mybatis的方式）。

【2】MP操作

mp单表的增删改
定义对应的mapper接口继承BaseMapper<泛型是表对应的实体类>
BaseMapper中提供了相应的增删改的方法
增：
int insert(T entity) 参数传入实体类对象 返回值是被影响行数
删：
根据id删除：
int deleteById(args) 参数是主键值 返回值是被影响行数
根据id集合批量删除：
int deleteBatchIds(lists) 参数是主键值构成的集合 返回值是被影响行数
根据map构造条件，删除：
int deleteByMap(map)
参数是主键值构成的集合 返回值是被影响行数
集合中 key为 字段名 value 为值 put进去
根据entity条件，删除
int delete(wrapper) wrapper相当于sql语句中where后面的条件 写清楚 放到对象中
改：
int updateById(entity) 参数传入实体类对象 返回值是被影响行数 相当于是传入了一个新对象但是只对相应的字段值做更新
int update(entity,wrapper) wrapper相当于sql语句中where后面的条件 写清楚 放到对象中
更新传入对象的相应字段值

练习题

@SpringBootTest
public class MPTest {

    @Autowired
    private UserMapper userMapper;

    @Test
    public void testQueryUesrById() {

        User user = userMapper.selectById(2L);
        System.out.println("user = " + user);
    }

    /*
     * 要求：添加名为小明的user数据
     * sql语句：insert into tb_user(xx,xx,xx,...) values (xx,xx,xx,....)
     * */
    @Test
    public void testAddUesrById() {

        User user = new User();
        user.setUserName("小王aB");
        user.setAge(989);
        user.setPassword("1234");

        int result = userMapper.insert(user);
        System.out.println("result = " + result);


    }

    /*
     * 要求：删除id为 16 的用户
     * sql语句：DELETE FROM tb_user WHERE id=?
     * */
    @Test
    public void testDelUesrById() {
        int result = userMapper.deleteById(19L);
        System.out.println("result = " + result);
    }

    /*
     * 要求：删除id为 15、14 的用户
     * sql语句：DELETE FROM tb_user WHERE id=? or id=?
     * sql语句：DELETE FROM tb_user WHERE id IN ( ? , ? )
     * */
    @Test
    public void testDelUesrByIds() {
        List<Long> ids = Arrays.asList(20L, 21L,22L);
        int result = userMapper.deleteBatchIds(ids);
        System.out.println("result = " + result);
    }

    /*
     * 要求：删除user_name为王八衰 age 为22 的用户
     * sql语句：DELETE FROM tb_user WHERE user_name = ? AND age = ?
     * */


    @Test
    public void testDelUesrByCondition() {

        HashMap<String, Object> map = new HashMap<>();
        map.put("user_name", "王八衰");
        map.put("age", "22");

        int result = userMapper.deleteByMap(map);
        System.out.println("result = " + result);
    }

    /*
     * 修改用户id为 4 的username修改为 李催
     * sql语句：UPDATE tb_user SET user_name=? WHERE id=?
     */
    @Test
    public void testUpdate(){

        User user = User.builder().id(4L)
                .userName("李催")
                .build();

        int i = userMapper.updateById(user);
        System.out.println("i = " + i);

    }

    /*
     * 要求：分页查询user数据，每页5条数据,查询第一页数据
     * sql语句：SELECT * FROM tb_user LIMIT 0,5
     * sql语句：SELECT * FROM tb_user LIMIT 5 （简写形式）
     */
    @Test
    public void testPage(){

        Page<User> page = new Page<>(1,5);


        Page<User> userPage = userMapper.selectPage(page, null);
        List<User> records = userPage.getRecords();
        System.out.println("records = " + records);
        long total = userPage.getTotal();

        System.out.println("total = " + total);


    }


    /*
     * 要求：查询用户中姓名包含"伤"，密码为"123456",且年龄为19或者25或者29，查询结果按照年龄降序排序；
     * sql语句：SELECT * FROM tb_user WHERE (user_name LIKE ? AND password = ? AND age IN (?,?,?)) ORDER BY age DESC
     */
    @Test
    public void testConditions1Query(){

        //QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        //queryWrapper.like("user_name", "伤");
        //queryWrapper.eq("password", "123456");
        //queryWrapper.in("age", 19, 25, 29);
        //queryWrapper.orderByDesc("age");


        //lambda表达式的形式，更加解耦
        LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.like(User::getUserName, "伤");
        queryWrapper.eq(User::getPassword, "123456");
        queryWrapper.in(User::getAge, 19, 25, 29);
        queryWrapper.orderByDesc(User::getAge);

        List<User> users = userMapper.selectList(queryWrapper);
        System.out.println("users = " + users);


    }


    /*
     * 要求：查询用户中姓名包含"伤"，密码为"123456",且年龄为19或者25或者29，查询结果按照年龄降序排序；
     * sql语句：SELECT user_name,age FROM tb_user WHERE (user_name LIKE ? AND password = ? AND age IN (?,?,?)) ORDER BY age DESC
     */
    @Test
    public void testConditions2Query(){


        LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.like(User::getUserName, "伤");
        queryWrapper.eq(User::getPassword, "123456");
        queryWrapper.in(User::getAge, 19, 25, 29);
        queryWrapper.orderByDesc(User::getAge);

        queryWrapper.select(User::getUserName, User::getAge);

        List<User> users = userMapper.selectList(queryWrapper);
        System.out.println("users = " + users);

    }

    /*
     * 要求：查询用户密码为"123456"的数据，并且要求每页显示5条，查询第二页的数据；
     * sql语句：SELECT user_name,age FROM tb_user WHERE limit 5,5
     */
    @Test
    public void testConditionsPageQuery(){

        Page<User> page = new Page<>(2, 5);

        LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(User::getPassword, "123456");
        queryWrapper.orderByDesc(User::getAge);


        Page<User> pageResult = userMapper.selectPage(page, queryWrapper);
        System.out.println("pageResult = " + pageResult);


        List<User> records = pageResult.getRecords();
        System.out.println("records = " + records);
        long total = pageResult.getTotal();
        System.out.println("total = " + total);

    }


    /*
     * 要求：查询用户密码为"123456",或者 age 为 20、21、22，并且要求每页显示5条，查询第二页的数据；
     * sql语句：SELECT user_name,age FROM tb_user WHERE  password = ? or age IN (?,?,?)
     */
    @Test
    public void testConditionsOrQuery(){


        Page<User> page = new Page<>(2, 5);

        LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(User::getPassword, "123456")
                .or()
                .in(User::getAge, 19, 25, 29)
                .select(User::getUserName, User::getAge);

        Page<User> pageResult = userMapper.selectPage(page, queryWrapper);
    }

    @Autowired
    private UserService userService;

    @Test
    public void two() {

        Page<User> page = new Page<>(2, 5);

        LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(User::getPassword, "123456")
                .or()
                .in(User::getAge, 19, 25, 29)
                .select(User::getUserName, User::getAge);

        Page<User> pageResult = userService.page(page, queryWrapper);

    }