mybatis plus 总结

• 案例一

    /**
     * 分页查询
     * @return
     */
    @RequestMapping("/get0")
    @ResponseBody
    public String test0(){
        LambdaQueryWrapper<User> userLambdaQueryWrapper = Wrappers.lambdaQuery();
        // 查询姓名中包含a的
        userLambdaQueryWrapper.like(User::getUsername , "a");
        // 查看第1页，每页3条
        Page<User> userPage = new Page<>(1 , 3);
        IPage<User> userIPage = userMapper.selectPage(userPage , userLambdaQueryWrapper);
        System.out.println("总页数： "+userIPage.getPages());
        System.out.println("总记录数： "+userIPage.getTotal());
        // 打印出第1页数据
        userIPage.getRecords().forEach(System.out::println);
        return "aaa";
    }
 
    /**
     * 不记录总记录数、不记录总页数
     * @return
     */
    @RequestMapping("/get1")
    @ResponseBody
    public String test1(){
        LambdaQueryWrapper<User> userLambdaQueryWrapper = Wrappers.lambdaQuery();
        userLambdaQueryWrapper.like(User::getUsername , "a");
        Page<User> userPage = new Page<>(1 , 2 , false);
        IPage<User> userIPage = userMapper.selectPage(userPage , userLambdaQueryWrapper);
        System.out.println("总页数： "+userIPage.getPages());
        System.out.println("总记录数： "+userIPage.getTotal());
        userIPage.getRecords().forEach(System.out::println);
        return "aaa";
    }
 
    /**
     * selectMapsPage()的用法
     * @return
     */
    @RequestMapping("/get2")
    @ResponseBody
    public String test2(){
        LambdaQueryWrapper<User> userLambdaQueryWrapper = Wrappers.lambdaQuery();
        // 查询用户名包含a的
        userLambdaQueryWrapper.select(User::getUsername).like(User::getUsername , "a");
        // 查询1页，每页2条
        Page<Map<String , Object>> mapPage = new Page<>(1 , 2 , false);
        IPage<Map<String , Object>> mapIPage = userMapper.selectMapsPage(mapPage , userLambdaQueryWrapper);
        System.out.println("总页数： "+mapIPage.getPages());
        System.out.println("总记录数： "+mapIPage.getTotal());
        // 打印查询到的数据
        mapIPage.getRecords().forEach(System.out::println);
        return "aaa";
    }
 
    /**
     * 自定义sql
     * @return
     */
    @RequestMapping("/get3")
    @ResponseBody
    public String test3(){
        // 构建wrapper对象
        LambdaQueryWrapper<User> userLambdaQueryWrapper = Wrappers.lambdaQuery();
        // 查询用户名包含a的
        userLambdaQueryWrapper.like(User::getUsername , "a");
        // 查询第1页，每页2条
        Page<User> mapPage = new Page<>(1 , 2 , false);
        // 调用mapper层方法
        IPage<User> mapIPage = userMapper.selectByPage(mapPage , userLambdaQueryWrapper);
        System.out.println("总页数： "+mapIPage.getPages());
        System.out.println("总记录数： "+mapIPage.getTotal());
        // 打印出查询结果
        mapIPage.getRecords().forEach(System.out::println);
        return "aaa";
    }
 
@Component
public interface UserMapper extends BaseMapper<User> {
    IPage<User> selectByPage(IPage<User> userPage, @Param(Constants.WRAPPER) Wrapper<User> userWrapper);
}
 
<select id="selectByPage" resultType="com.ychen.mybatis.demo01.model.User">
    select * from user ${ew.customSqlSegment}
</select>

• 案例二

    @GetMapping("/findUser")
    @ResponseBody
    public String testFind(){
        List<UserBookPo> users = userMapper.findUser();
        System.out.println(users);
        return "aaa";
    }
 
    @GetMapping("/findUsers")
    @ResponseBody
    public String testLimit(){
        List<User> users = userMapper.findUsers();
        System.out.println(users);
        return "aaa";
    }
 
    /**
     * http://localhost:8080/test/listAll
     * {
     *     "username": "chen1",
     *     "bookname": "book1"
     * }
     * @param req
     * @return
     */
    @RequestMapping(value = "/listAll", method = RequestMethod.POST)
    @ResponseBody
    public String testList(@RequestBody UserBookReqDTO req){
        List<UserBookRespDTO> list = userMapper.getUserBook(req);
        System.out.println(list);
        return "aaa";
    }
 
    // 分页查询
    @RequestMapping("/get0")
    @ResponseBody
    public String test0(){
        // 构建wrapper对象
        LambdaQueryWrapper<User> userLambdaQueryWrapper = Wrappers.lambdaQuery();
        // 查询用户名包含a的
        userLambdaQueryWrapper.like(User::getUsername , "c");
        // 查询第1页，每页2条
        Page<User> mapPage = new Page<>(1 , 2 , false);
        // 调用mapper层方法
        IPage<User> mapIPage = userMapper.selectByPage(mapPage , userLambdaQueryWrapper);
        System.out.println("总页数： "+mapIPage.getPages());
        System.out.println("总记录数： "+mapIPage.getTotal());
        // 打印出查询结果
        mapIPage.getRecords().forEach(System.out::println);
        return "aaa";
    }
 
    /**
     * 分页连接
     *
     * http://localhost:8080/test/listAll
     *      * {
     *      *     "username": "chen1",
     *      *     "bookname": "book1"
     *      * }
     * @param req
     * @return
     */
    @RequestMapping("/get1")
    @ResponseBody
    public String test1(@RequestBody UserBookReqDTO req){
        int pageNo = 1;
        int pageSize = 3;
        String username = req.getUsername();
        String sex = req.getSex();
        String bookname = req.getBookname();
        // 构建wrapper对象
        QueryWrapper<UserBookRespDTO> wrapper = new QueryWrapper<>();
        // 构建page对象
        Page<UserBookRespDTO> page = new Page<UserBookRespDTO>(pageNo, pageSize);
        // 调用mapper层方法
        IPage<UserBookRespDTO> userPageList = userMapper.getUserBooks(username, sex, bookname, page, wrapper);
        System.out.println(userPageList);
        return "aaa";
    }
 
 
    // 连接查询
    @SelectProvider(type = findUserProvider.class, method = "findUser")
    List<UserBookPo> findUser();
 
    class findUserProvider {
        public String findUser() {
            String sql = new SQL()
                    .SELECT("u.username", "u.password", "b.bookname", "b.author")
                    .FROM("user u")
                    .INNER_JOIN("book b")
                    .WHERE("u.username = b.author")
                    .toString();
            return sql;
        }
    }
 
    // 分页查询
    @SelectProvider(type = findUsersProvider.class, method = "findUsers")
    List<User> findUsers();
 
    class findUsersProvider {
        public String findUsers() {
            String sql = new SQL()
                    .SELECT("id", "username", "password", "sex", "birthday")
                    .FROM("user")
                    .LIMIT("2, 3")
                    .toString();
            return sql;
        }
    }
 
    // 分页连接查询
    @SelectProvider(type = getUserBookProvider.class, method = "getUserBook")
    List<UserBookRespDTO> getUserBook(UserBookReqDTO user);
 
    class getUserBookProvider {
        public String getUserBook(UserBookReqDTO user) {
            SQL sql = new SQL();
            sql.SELECT("u.id", "u.username", "u.password", "u.sex", "u.birthday", "b.id", "b.bookname", "b.author", "b.price")
                    .FROM("user u").INNER_JOIN("book b").WHERE("u.username = b.author");
            if(user.getUsername() != null) {
                sql.SET("username=#{user.username}");
            }
            if(user.getSex() != null) {
                sql.SET("sex = #{user.sex}");
            }
            if(user.getBookname() != null) {
                sql.SET("bookname = #{user.bookname}");
            }
            if(user.getAuthor() != null) {
                sql.SET("author = #{user.author}");
            }
            if(user.getPrice() != null) {
                sql.SET("price = #{user.price}");
            }
            if(user.getPageSize() != null && user.getPageNum() != null) {
                sql.LIMIT("#{user.pageNum}, #{user.pageSize}");
            }
            return sql.toString();
        }
    }
 
    // 分页
    IPage<User> selectByPage(IPage<User> userPage, @Param(Constants.WRAPPER) Wrapper<User> userWrapper);
 
    // 分页连接
    IPage<UserBookRespDTO> getUserBooks(@Param("username") String username, @Param("sex") String sex, @Param("bookname") String bookname, @Param("page")    Page<UserBookRespDTO> page, @Param(Constants.WRAPPER) Wrapper<UserBookRespDTO> wrapper);
 
 
    <select id="selectByPage" resultType="com.ychen.mybatis.demo02.model.User">
        select * from user ${ew.customSqlSegment}
    </select>
 
    <select id="getUserBooks" resultType="com.ychen.mybatis.demo02.model.dto.UserBookRespDTO">
        SELECT
        u.id,
        u.username,
        u.password,
        u.sex,
        u.birthday,
        b.bookname,
        b.author,
        b.price
        FROM
        user AS u
        LEFT JOIN book AS b ON u.username = b.author
        <where>
            <if test="username !=null and username!=''">
                or u.username like concat('%',#{username},'%')
            </if>
            <if test="sex !=null and sex!=''">
                or u.sex like concat('%',#{sex},'%')
            </if>
 
            <if test="bookname != null and bookname!=''">
                or b.bookname like concat('%',#{bookname}'%')
            </if>
        </where>
        ${ew.customSqlSegment}
    </select>

• 案例三

public interface UserDao extends BaseMapper<User> {
 
}
 
 
    /**
     * 单表查询所有
     * @return
     */
    @GetMapping("/test1")
    @ResponseBody
    public String test1() {
        List<User> users = userDao.selectList(null);
        users.forEach(user-> System.out.println("user = " + user));
        return "success";
    }
 
    /**
     * 单表，根据id查询
     * @return
     */
    @GetMapping("/test2")
    @ResponseBody
    public String test2() {
        User user = userDao.selectById("1");
        System.out.println("user = " + user);
        return "success";
    }
 
    /**
     * 条件查询
     * @return
     */
    @GetMapping("/test3")
    @ResponseBody
    public String test3() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        // 设置等值查询，即查询age=23的对象
        queryWrapper.eq("age",23);
        // 设置小于查询
        //queryWrapper.lt("age",23);
        // 小于等于查询
        //queryWrapper.ge("age",23);
        // gt 大于
        // ge 大于等于
        List<User> users = userDao.selectList(queryWrapper);
        users.forEach(user-> System.out.println(user));
        return "success";
    }
 
    /**
     * 模糊查询
     * like 表示包含某个字符
     * likeLeft 表示以某个字符结尾
     * likeRight 表示以某个字符开头的
     * @return
     */
    @GetMapping("/test4")
    @ResponseBody
    public String test4() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.likeRight("name","g");
        List<User> users = userDao.selectList(queryWrapper);
        users.forEach(user-> System.out.println("user = " + user));
        return "success";
    }
 
    /**
     * 添加
     * @return
     */
    @GetMapping("/test5")
    @ResponseBody
    public String test5() {
        User entity = new User();
        entity.setId("4").setAge(23).setName("小明明").setBir(new Date());
        userDao.insert(entity);
        return "success";
    }
 
    /**
     * 删除
     * @return
     */
    @GetMapping("/test6")
    @ResponseBody
    public String test6() {
        // 删除单个
        //userDao.deleteById("4");
        // 通过id批量删除
        userDao.deleteBatchIds(Arrays.asList(5, 6));
        return "success";
    }
 
    /**
     * 修改
     * @return
     */
    @GetMapping("/test7")
    @ResponseBody
    public String test7() {
        User user = userDao.selectById("1");
        user.setAge(99);
        userDao.updateById(user);
        return "success";
    }
 
    /**
     * 按条件修改
     * 将age=99的 用户名改为狗蛋
     * @return
     */
    @GetMapping("/test8")
    @ResponseBody
    public String test8() {
        User user = userDao.selectById("1");
        user.setName("狗蛋");
        QueryWrapper<User> updateWrapper = new QueryWrapper<>();
        // 修改age=99的对象
        updateWrapper.eq("age",99);
        userDao.update(user, updateWrapper);
        return "success";
    }
 
    /**
     * 分页查询
     * @return
     */
    @GetMapping("/test9")
    @ResponseBody
    public String test9() {
        // new一个page对象，传入2个参数，第一个参数表示页数，第二个参数表示每页多少条数据
        IPage<User> page = new Page<>(1,2);
        // 调用分页的方法
        page = userDao.selectPage(page, null);
        page.getRecords().forEach(user -> System.out.println("user = " + user));
        return "success";
    }
 
    /**
     * 带条件的分页查询
     * 查询age=99的对象
     * @return
     */
    @GetMapping("/test10")
    @ResponseBody
    public String test10() {
        // new一个条件对象，例如age=99
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("age",99);
        // new一个page对象
        IPage<User> page = new Page<>(1,2);
        // 调用分页的方法，传入2个参数
        page = userDao.selectPage(page, queryWrapper);
        page.getRecords().forEach(user-> System.out.println("user = " + user));
        return "success";
    }