mybatis puls学习笔记（二）

mapper

package com.ychen.mybatis.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.ychen.mybatis.model.User;
import org.apache.ibatis.annotations.Param;
import org.springFramework.stereotype.Component;

/**
 * @Author: chenyuanqing
 * @Description:
 * @Date: Created in 13:41 2022/2/23
 */
@Component
public interface UserMapper extends BaseMapper<User> {

    IPage<User> selectByPage(IPage<User> userPage, @Param(Constants.WRAPPER) Wrapper<User> userWrapper);

}

controller

package com.ychen.mybatis.controller;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.ychen.mybatis.mapper.UserMapper;
import com.ychen.mybatis.model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("/query")
public class QueryController {

    @Autowired
    private UserMapper userMapper;

    /**
     * 使用wrapper
     * 单表分页查询
     * 模糊查询
     * @return
     */
    @RequestMapping("/test1")
    @ResponseBody
    public String test1(){
        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 "success";
    }

    /**
     * 使用wrapper
     * 单表分页查询
     * 模糊查询
     * 查询指定字段
     * @return
     */
    @RequestMapping("/test2")
    @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 "success";
    }

    /**
     * 不使用wrapper
     * 单表查询所有
     * @return
     */
    @GetMapping("/test3")
    @ResponseBody
    public String test3() {
        List<User> users = userMapper.selectList(null);
        users.forEach(user-> System.out.println("user = " + user));
        return "success";
    }

    /**
     * 不使用wrapper
     * 单表根据id查询
     * @return
     */
    @GetMapping("/test4")
    @ResponseBody
    public String test4() {
        User user = userMapper.selectById("1");
        System.out.println(user);
        return "success";
    }

    /**
     * 使用wrapper
     * 条件查询
     * @return
     */
    @GetMapping("/test5")
    @ResponseBody
    public String test5() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        // 设置等值查询，即查询age=23的对象
        queryWrapper.eq("age",23);
        // 设置小于查询
        //queryWrapper.lt("age",23);
        // 小于等于查询
        //queryWrapper.ge("age",23);
        // gt 大于
        // ge 大于等于
        List<User> users = userMapper.selectList(queryWrapper);
        users.forEach(user-> System.out.println(user));
        return "success";
    }

    /**
     * 使用wrapper
     * 模糊查询
     * like 表示包含某个字符
     * likeLeft 表示以某个字符结尾
     * likeRight 表示以某个字符开头的
     * @return
     */
    @GetMapping("/test6")
    @ResponseBody
    public String test6() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.likeRight("name","g");
        List<User> users = userMapper.selectList(queryWrapper);
        users.forEach(user-> System.out.println("user = " + user));
        return "success";
    }

    /**
     * 使用wrapper
     * 根据id查询
     * @return
     */
    @GetMapping("/test7")
    @ResponseBody
    public String test7() {
        User user = userMapper.selectOne(new QueryWrapper<User>().lambda().eq(User::getId, 10086));
        return "success";
    }

    /**
     * 使用wrapper
     * 查询所有
     * @return
     */
    @GetMapping("/test8")
    @ResponseBody
    public String test8() {
        userMapper.selectList(Wrappers.<User>lambdaQuery().select(User::getId))
                .forEach(x -> {
                    x.getId();
                    x.getAge();
                    x.getUsername();
                });
        return "success";
    }

    /**
     * 使用wrapper
     * 查询所有
     * @return
     */
    @GetMapping("/test9")
    @ResponseBody
    public String test9() {
        userMapper.selectList(new QueryWrapper<User>().select("id", "name"))
                .forEach(x -> {
                    x.getId();
                    x.getAge();
                    x.getUsername();
                });
        return "success";
    }

    /**
     * 使用wrapper
     * 排序
     * @return
     */
    @RequestMapping(value = "/test10")
    @ResponseBody
    public String test10(){
        // 查询所有，根据age升序
        List<User> users = userMapper.selectList(Wrappers.<User>query().orderByAsc("age"));
        //多字段排序
        List<User> users2 = userMapper.selectList(Wrappers.<User>query().orderByAsc("age", "name"));
        //先按age升序排列，age相同再按name降序排列
        List<User> users3 = userMapper.selectList(Wrappers.<User>query().orderByAsc("age").orderByDesc("name"));
        // end
        return "success";
    }

    /**
     * 使用wrapper
     * 将查询到的结果放到map集合，打印出第一个位置的值
     * @return
     */
    @RequestMapping(value = "/test11")
    @ResponseBody
    public String test11(){
        List<Map<String, Object>> mapList = userMapper.selectMaps(Wrappers.<User>query().orderByAsc("age"));
        System.out.println("mapList: " + mapList.get(0));
        // end
        return "success";
    }

    /**
     * 使用wrapper
     * 排序
     * @return
     */
    @RequestMapping(value = "/test12")
    @ResponseBody
    public String test12(){
        // 查询所有，根据age升序排序
        List<User> users = userMapper.selectList(Wrappers.<User>lambdaQuery().orderByAsc(User::getAge));
        //多字段排序
        List<User> users2 = userMapper.selectList(Wrappers.<User>lambdaQuery().orderByAsc(User::getAge, User::getUsername));
        //先按age升序排列，age相同再按name降序排列
        List<User> users3 = userMapper.selectList(Wrappers.<User>lambdaQuery().orderByAsc(User::getAge).orderByDesc(User::getUsername));
        // end
        return "success";
    }

    /**
     * 最大值
     * @return
     */
    @RequestMapping(value = "/test13")
    @ResponseBody
    public String test13(){
        // 创建wapper，查询最大的id
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.select("max(id) as id");
        User user = userMapper.selectOne(wrapper);
        System.out.println("maxId = " + user.getId());
        return "success";
    }

    /**
     * 分组
     * @return
     */
    @RequestMapping(value = "/test14")
    @ResponseBody
    public String test14(){
        // 根据age分组
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.select("age, count(*)")
                .groupBy("age");
        List<Map<String, Object>> maplist = userMapper.selectMaps(wrapper);
        for (Map<String, Object> mp : maplist) {
            System.out.println(mp);
        }

        /**
         * 方式二
         */
        LambdaQueryWrapper<User> lambdaQueryWrapper = new QueryWrapper<User>().lambda()
                .select(User::getAge)
                .groupBy(User::getAge)
                .orderByAsc(User::getAge);
        for (User user : userMapper.selectList(lambdaQueryWrapper)) {
            System.out.println(user);
        }
        // end
        return "success";
    }

    /**
     * 求和
     * @return
     */
    @RequestMapping(value = "/test15")
    @ResponseBody
    public String test15(){
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.select("age, count(age) as count")
                .groupBy("age");
        List<User> list = userMapper.selectList(wrapper);
        list.forEach(System.out::println);
        list.forEach(x -> {
            System.out.println(x.getId());
        });
        // end
        return "success";
    }

    /**
     * 排序
     * SELECT id,name FROM user
     * WHERE (age BETWEEN ? AND ?) ORDER BY role_id ASC,id ASC
     */
    @RequestMapping("/test16")
    @ResponseBody
    public String test16(){
        QueryWrapper<User> qw = new QueryWrapper<>();
        qw.select("id","name").between("age",20,25)
                .orderByAsc("role_id","id");
        List<User> plainUsers = userMapper.selectList(qw);

        // 方式二
        LambdaQueryWrapper<User> lwq = new LambdaQueryWrapper<>();
        lwq.select(User::getId,User::getUsername).between(User::getAge,20,25)
                .orderByAsc(User::getId,User::getId);
        List<User> lambdaUsers = userMapper.selectList(lwq);
        // end
        return "success";
    }

}

package com.ychen.mybatis.controller;

import com.ychen.mybatis.mapper.UserMapper;
import com.ychen.mybatis.model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import java.util.Date;

@RestController
@RequestMapping("/add")
public class AddController {

    @Autowired
    private UserMapper userMapper;

    /**
     * 不使用wrapper
     * 单表添加
     * @return
     */
    @GetMapping("/test1")
    @ResponseBody
    public String test1() {
        User entity = new User();
        entity.setId("4");
        entity.setAge(19);
        userMapper.insert(entity);
        return "success";
    }

}

package com.ychen.mybatis.controller;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.ychen.mybatis.mapper.UserMapper;
import com.ychen.mybatis.model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;

import java.util.Arrays;

@RestController
@RequestMapping("/delete")
public class DeleteController {

    @Autowired
    private UserMapper userMapper;

    /**
     * 不使用wrapper
     * 单表删除
     * 单表批量删除
     * @return
     */
    @GetMapping("/test1")
    @ResponseBody
    public String test1() {
        // 删除单个
        userMapper.deleteById("4");
        // 通过id批量删除
        userMapper.deleteBatchIds(Arrays.asList(5, 6));
        return "success";
    }

    /**
     * 使用wrapper
     * 按条件删除
     * @return
     */
    @GetMapping("/test2")
    @ResponseBody
    public String test2() {
        userMapper.delete(new QueryWrapper<User>()
                .lambda().eq(User::getUsername, "Sandy"));
        return "success";
    }

}

package com.ychen.mybatis.controller;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.ychen.mybatis.mapper.UserMapper;
import com.ychen.mybatis.model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("/update")
public class UpdateController {

    @Autowired
    private UserMapper userMapper;

    /**
     * 不使用wrapper
     * 单表修改
     * @return
     */
    @GetMapping("/test1")
    @ResponseBody
    public String test1() {
        User user = userMapper.selectById("1");
        user.setAge(99);
        userMapper.updateById(user);
        return "success";
    }

    /**
     * 使用wrapper
     * 按条件修改
     * @return
     */
    @GetMapping("/test2")
    @ResponseBody
    public String test2() {
        User user = userMapper.selectById("1");
        user.setUsername("狗蛋");
        QueryWrapper<User> updateWrapper = new QueryWrapper<>();
        // 修改age=99的对象
        updateWrapper.eq("age",99);
        userMapper.update(user, updateWrapper);
        return "success";
    }

    /**
     * 使用wrapper
     * 根据id修改
     * @return
     */
    @GetMapping("/test3")
    @ResponseBody
    public String test3() {
        userMapper.update(
                null,
                Wrappers.<User>lambdaUpdate().set(User::getUsername, null).eq(User::getId, 2)
        );
        return "success";
    }

    /**
     * 使用wrapper
     * 按条件更新数据
     * @return
     */
    @RequestMapping("/test4")
    @ResponseBody
    public String test4(){
        userMapper.update(null,new UpdateWrapper<User>()
                // 更新name为Tom的数据
                .set("age",29).set("email","test3update@baomidou.com").eq("name","Tom"));

        // 方式二：使用lambda条件构造器
        userMapper.update(null,new LambdaUpdateWrapper<User>()
                .set(User::getAge,29).set(User::getUsername,"test3update@baomidou.com").eq(User::getPassword,"Tom"));
        // end
        return "success";
    }

}