Mybatis-Plus常见操作

package com.ideas.system.service.impl;

import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.google.common.collect.HashBasedTable;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.google.common.collect.Table;
import com.ideas.system.entity.MybatisSysUser;
import com.ideas.system.entity.Apple;
import com.ideas.system.entity.DropDownList;
import com.ideas.system.entity.User;
import com.ideas.system.mapper.MybatisPlusOptionMapper;
import com.ideas.system.service.MybatisPlusOptionService;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.*;
import java.util.function.Consumer;
import java.util.function.Function;
import java.util.stream.Collectors;

@Slf4j
@Service
@RequiredArgsConstructor
public class MybatisPlusOptionServiceImpl extends ServiceImpl<MybatisPlusOptionMapper, MybatisSysUser> implements MybatisPlusOptionService {
    @Override
    public void mybatisPlusOption() {
        // AR模式
        MybatisSysUser mybatisSysUser = MybatisSysUser.builder().build();
        mybatisSysUser.selectById(1L);
        mybatisSysUser.selectById();
        mybatisSysUser.selectList(new LambdaQueryWrapper<>());
        LambdaQueryWrapper<MybatisSysUser> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        lambdaQueryWrapper.last("limit 1");
        mybatisSysUser.selectOne(lambdaQueryWrapper);
        mybatisSysUser.selectAll();
        mybatisSysUser.selectPage(new Page<MybatisSysUser>(), new LambdaQueryWrapper<>());
        mybatisSysUser.selectCount(new LambdaQueryWrapper<>());
        mybatisSysUser.insert();
        mybatisSysUser.insertOrUpdate();
        mybatisSysUser.deleteById();
        mybatisSysUser.deleteById(1L);
        mybatisSysUser.delete(new LambdaQueryWrapper<>());
        mybatisSysUser.updateById();
        mybatisSysUser.update(new LambdaUpdateWrapper<>());


        Long id = 1L;
        // 根据主键查询一条数据
        MybatisSysUser sysUser = baseMapper.selectById(id);
        log.info("根据主键查询一条数据-sysUser={}", JSON.toJSONString(sysUser));

        LambdaQueryWrapper<MybatisSysUser> queryWrapper = new LambdaQueryWrapper<>();
        // 等于，eq(true, "name", "zhangsan")  ==>  name='zhangsan'
        queryWrapper.eq(StringUtils.isNotBlank(sysUser.getUserName()), MybatisSysUser::getUserName, sysUser.getUserName());
        // 不等于，ne(true, "name", "zhangsan")  ==>  name <> 'zhangsan'
        queryWrapper.ne(StringUtils.isNotBlank(sysUser.getUserName()), MybatisSysUser::getUserName, sysUser.getUserName());

        // 模糊，like(true, "name", "zhangsan")  ==>  name like '%zhangsan%'
        queryWrapper.like(StringUtils.isNotBlank(sysUser.getPhonenumber()), MybatisSysUser::getPhonenumber, sysUser.getPhonenumber());
        // 模糊，likeLeft(true, "name", "zhangsan")  ==>  name like '%zhangsan'
        queryWrapper.likeLeft(StringUtils.isNotBlank(sysUser.getPhonenumber()), MybatisSysUser::getPhonenumber, sysUser.getPhonenumber());
        // 模糊，likeRight(true, "name", "zhangsan")  ==>  name like 'zhangsan%'
        queryWrapper.likeRight(StringUtils.isNotBlank(sysUser.getPhonenumber()), MybatisSysUser::getPhonenumber, sysUser.getPhonenumber());
        // 模糊，notLike(true, "name", "zhangsan")  ==>  name not like '%zhangsan%'
        queryWrapper.notLike(StringUtils.isNotBlank(sysUser.getPhonenumber()), MybatisSysUser::getPhonenumber, sysUser.getPhonenumber());

        // 小于，lt(true, "age", 19)  ==>  age < 19
        queryWrapper.lt(Objects.nonNull(sysUser.getRoleId()), MybatisSysUser::getPhonenumber, sysUser.getPhonenumber());
        // 小于等于，le(true, "age", 19)  ==>  age <= 19
        queryWrapper.le(Objects.nonNull(sysUser.getRoleId()), MybatisSysUser::getPhonenumber, sysUser.getPhonenumber());
        // 大于，gt(true, "age", 19)  ==>  age > 19
        queryWrapper.gt(Objects.nonNull(sysUser.getRoleId()), MybatisSysUser::getPhonenumber, sysUser.getPhonenumber());
        // 大于等于，ge(true, "age", 19)  ==>  age >= 19
        queryWrapper.ge(Objects.nonNull(sysUser.getRoleId()), MybatisSysUser::getPhonenumber, sysUser.getPhonenumber());

        // 两者之间，between(true, "age", 19, 30)  ==>  age between 19 and 30
        queryWrapper.between(Objects.nonNull(sysUser.getRoleId()), MybatisSysUser::getPhonenumber, sysUser.getPhonenumber(), sysUser.getPhonenumber());
        // 非两者之间，notBetween(true, "age", 19, 30)  ==>  age not between 19 and 30
        queryWrapper.notBetween(Objects.nonNull(sysUser.getRoleId()), MybatisSysUser::getPhonenumber, sysUser.getPhonenumber(), sysUser.getPhonenumber());

        // 字段 IS NULL，isNull(true, "name")  ==>  name is null
        queryWrapper.isNull(StringUtils.isNotBlank(sysUser.getUserName()), MybatisSysUser::getUserName);
        // 字段 IS NOT NULL，isNotNull(true, "name")  ==>  name is not null
        queryWrapper.isNotNull(StringUtils.isNotBlank(sysUser.getUserName()), MybatisSysUser::getUserName);

        // 字段 IN，in(true, "name", "zhangsan", "lisi")  ==>  name in('zhangsan', 'lisi')
        queryWrapper.in(StringUtils.isNotBlank(sysUser.getUserName()), MybatisSysUser::getUserName, sysUser.getUserName(), sysUser.getPhonenumber());
        queryWrapper.in(StringUtils.isNotBlank(sysUser.getUserName()), MybatisSysUser::getUserName, Lists.newArrayList("zhangsna", "list"));
        // 字段 NOT IN，notIn(true, "name", "zhangsan", "lisi")  ==>  name not in('zhangsan', 'lisi')
        queryWrapper.notIn(StringUtils.isNotBlank(sysUser.getUserName()), MybatisSysUser::getUserName, sysUser.getUserName(), sysUser.getPhonenumber());
        queryWrapper.notIn(StringUtils.isNotBlank(sysUser.getUserName()), MybatisSysUser::getUserName, Lists.newArrayList("zhangsna", "list"));
        // 字段 inSql，inSql(true, "roleId", "select role_id from sys_role where role_name like '%三'")  ==>  roleId in(select role_id from sys_role where role_name like '%三')
        queryWrapper.inSql(sysUser.getRoleIds().length > 0, MybatisSysUser::getRoleId, "select role_id from sys_role where role_name like '%三'");
        // 字段 notInSql，notInSql(true, "roleId", "select role_id from sys_role where role_name like '%三'")  ==>  roleId not in(select role_id from sys_role where role_name like '%三')
        queryWrapper.notInSql(sysUser.getRoleIds().length > 0, MybatisSysUser::getRoleId, "select role_id from sys_role where role_name like '%三'");

        // 字段 exists，exists(true, "select role_id from sys_role where role_name = 'zhangsan'")  ==>  roleId exists(select role_id from sys_role where role_name = 'zhangsan')
        queryWrapper.exists(sysUser.getRoleIds().length > 0, "select role_id from sys_role where role_name = 'zhangsan'");
        // 字段 notExists，notExists(true, "select role_id from sys_role where role_name = 'zhangsan'")  ==>  roleId not exists(select role_id from sys_role where role_name = 'zhangsan')
        queryWrapper.notExists(sysUser.getRoleIds().length > 0, "select role_id from sys_role where role_name = 'zhangsan'");

        // 字段 groupBy，groupBy(true, "roleId", "userName")  ==>  group by roleId, userName
        queryWrapper.groupBy(Objects.nonNull(sysUser.getRoleId()), MybatisSysUser::getRoleId, MybatisSysUser::getUserName);

        // 字段 order by，orderByAsc(true, "roleId", "userName")  ==>  gorder by roleId ASC, userName ASC
        queryWrapper.orderByAsc(Objects.nonNull(sysUser.getRoleId()), MybatisSysUser::getRoleId, MybatisSysUser::getUserName);
        // 字段 order by，orderByDesc(true, "roleId", "userName")  ==>  gorder by roleId DESC, userName DESC
        queryWrapper.orderByDesc(Objects.nonNull(sysUser.getRoleId()), MybatisSysUser::getRoleId, MybatisSysUser::getUserName);
        queryWrapper.orderByDesc(MybatisSysUser::getRoleId).orderByAsc(MybatisSysUser::getUserName);
        // 字段 order by 自定排序规则，所有字段同一排序规则，orderBy(true, false，"roleId", "userName")  ==>  order by roleId DESC, userName DESC
        // 字段 order by 自定排序规则，所有字段同一排序规则，orderBy(true, true，"roleId", "userName")  ==>  order by roleId ASC, userName ASC
        queryWrapper.orderBy(Objects.nonNull(sysUser.getRoleId()), Boolean.FALSE, MybatisSysUser::getRoleId, MybatisSysUser::getUserName);

        // 字段 having，having(true, "sum(age) > {0}", 11)  ==>  having sum(age) > 11
        queryWrapper.having(Objects.nonNull(sysUser.getRoleId()), "sum(age) > {0}", 11);

        // 无视优化规则，直接拼接到sql最后面，只能调用一次，多次调用以最后一次为准，有sql注入风险，
        queryWrapper.last("limit 1");

        // ************************** TODO 以下几个待确定sql **************************
        // 拼接or，注意：主动调用or表示紧接着下一个方法不是and连接！（不调用or则默认使用and连接）
        queryWrapper.or();
        queryWrapper.or(Objects.nonNull(sysUser.getRoleId()));
        // and嵌套，
        queryWrapper.and(Objects.nonNull(sysUser.getRoleId()), (Consumer<LambdaQueryWrapper<MybatisSysUser>>) queryWrapper);
        // 拼接sql,apply("date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")
        // apply("date_format(dateColumn,'%Y-%m-%d') = {0}", LocalDate.now())
        queryWrapper.apply(Objects.nonNull(sysUser.getRoleId()), "date_format(dateColumn,'%Y-%m-%d') >= {0}", LocalDate.now());
        // 正常嵌套，不带and和or
        queryWrapper.nested(Objects.nonNull(sysUser.getRoleId()), (Consumer<LambdaQueryWrapper<MybatisSysUser>>) queryWrapper);

        List<MybatisSysUser> mybatisSysUserList = baseMapper.selectList(queryWrapper);
    }

    public static void main(String[] args) {
        List<Apple> appleList = Lists.newArrayList();
        appleList.add(new Apple(1, "苹果1", new BigDecimal("3.25"), 10));
        appleList.add(new Apple(1, "苹果2", new BigDecimal("1.35"), 20));
        appleList.add(new Apple(2, "香蕉", new BigDecimal("2.89"), 30));
        appleList.add(new Apple(3, "荔枝", new BigDecimal("9.99"), 40));

        // 1、分组 List里面的对象元素，以某个属性来分组，例如，以id分组，将id相同的放在一起：
        Map<Integer, List<Apple>> groupBy = appleList.stream().collect(Collectors.groupingBy(Apple::getId));
        System.out.println("groupBy:" + JSON.toJSONString(groupBy));
        // groupBy:{1:[{"id":1,"money":3.25,"name":"苹果1","num":10},{"id":1,"money":1.35,"name":"苹果2","num":20}],2:[{"id":2,"money":2.89,"name":"香蕉","num":30}],3:[{"id":3,"money":9.99,"name":"荔枝","num":40}]}

        // 分组且排序
        Map<Integer, List<Apple>> groupingByMap = appleList
                .stream()
                .collect(Collectors.groupingBy(Apple::getNum))
                .entrySet()
                .stream()
                .sorted(Map.Entry.comparingByKey())
                .collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue, (oldValue, newValue) -> oldValue, LinkedHashMap::new));


        /**
         * 2. id为key，apple对象为value，可以这么做：
         * List -> Map
         * 需要注意的是：
         * toMap 如果集合对象有重复的key，会报错Duplicate key ....
         * apple1,apple12的id都为1。
         * 可以用 (k1,k2)->k1 来设置，如果有重复的key,则保留key1,舍弃key2
         */
        // 2.1. 重复key时指定要留的key的顺序
        Map<Integer, Apple> appleMap = appleList.stream().collect(Collectors.toMap(Apple::getId, apple -> apple, (k1, k2) -> k1));
        System.out.println("appleMap:" + JSON.toJSONString(appleMap));
        // appleMap:{1:{"id":1,"money":3.25,"name":"苹果1","num":10},2:{"id":2,"money":2.89,"name":"香蕉","num":30},3:{"id":3,"money":9.99,"name":"荔枝","num":40}}
        // 重复key的情况下 简单的使用后者覆盖前者的
        Map<Integer, Apple> collect2 = appleList.stream().collect(Collectors.toMap(Apple::getNum, Function.identity(), (key1, key2) -> key2));
        System.out.println("collect2:" + JSON.toJSONString(collect2));
        // collect2:{20:{"id":1,"money":1.35,"name":"苹果2","num":20},40:{"id":3,"money":9.99,"name":"荔枝","num":40},10:{"id":1,"money":3.25,"name":"苹果1","num":10},30:{"id":2,"money":2.89,"name":"香蕉","num":30}}
        // 指定map的具体实现
        LinkedHashMap<Integer, Apple> collect3 = appleList.stream().collect(Collectors.toMap(Apple::getNum, Function.identity(), (key1, key2) -> key2, LinkedHashMap::new));
        System.out.println("collect3:" + JSON.toJSONString(collect3));
        // collect3:{10:{"id":1,"money":3.25,"name":"苹果1","num":10},20:{"id":1,"money":1.35,"name":"苹果2","num":20},30:{"id":2,"money":2.89,"name":"香蕉","num":30},40:{"id":3,"money":9.99,"name":"荔枝","num":40}}
        // 当key不冲突时
        // List<Apple> to Map<Integer, String> num = key，name = value
        Map<Integer, String> collect4 = appleList.stream().collect(Collectors.toMap(Apple::getNum, Apple::getName));
        List<Map<String, String>> columnRoleList = Lists.newArrayList();
        Map<String, String> collect4_1 = columnRoleList.stream().collect(Collectors.toMap(column->column.get("column_name"), column->column.get("role_ids"), (k1, k2) -> k1));
        List<Map<String, String>> multiSourceDefaultList = columnRoleList.stream().filter(a -> StringUtils.isNotBlank(a.get("data_value")) && StringUtils.isNotBlank(a.get("data_value"))).collect(Collectors.toList());
        System.out.println("collect4:" + JSON.toJSONString(collect4));
        // collect4:{20:"苹果2",40:"荔枝",10:"苹果1",30:"香蕉"}
        // num = key，Apple = value
        // num = key，Apple = value 比较优雅的写法是这样的
        Map<Integer, Apple> collect6 = appleList.stream().collect(Collectors.toMap(Apple::getNum, Function.identity()));
        Map<String, Integer> collect8 = appleList.stream().collect(Collectors.toMap(Apple::getName, Apple::getId));
        System.out.println("collect6:" + JSON.toJSONString(collect6));
        // collect6:{20:{"id":1,"money":1.35,"name":"苹果2","num":20},40:{"id":3,"money":9.99,"name":"荔枝","num":40},10:{"id":1,"money":3.25,"name":"苹果1","num":10},30:{"id":2,"money":2.89,"name":"香蕉","num":30}}

        //3、过滤Filter 从集合中过滤出来符合条件的元素：
        List<Apple> filterList = appleList.stream().filter(a -> a.getName().equals("香蕉")).collect(Collectors.toList());
        System.out.println("filterList:" + JSON.toJSONString(filterList));
        // filterList:[{"id":2,"money":2.89,"name":"香蕉","num":30}]

        // 4.求和 将集合中的数据按照某个属性求和:
        BigDecimal totalMoney = appleList.stream().map(Apple::getMoney).reduce(BigDecimal.ZERO, BigDecimal::add);
        System.out.println("totalMoney:" + JSON.toJSONString(totalMoney));
        // totalMoney:17.48

        // 5.统计list数量/list to list 指定属性
        // 方法一
        List<Integer> collect1 = appleList.stream().map(apple -> apple.getId()).collect(Collectors.toList());
        // 方法二
        List<Integer> collect = appleList.stream().map(Apple::getId).collect(Collectors.toList());
        System.out.println("collect1.size():" + collect1.size() + " --- collect.size():" + collect.size());

        // 6. List<A> 转 List<B>
        List<DropDownList> collect7 = appleList.stream().map(x -> DropDownList.builder().value(x.getId().toString()).text(x.getName()).build()).collect(Collectors.toList());

        // 7.转成set
        Set<Integer> ageSet = appleList.stream().map(Apple::getNum).collect(Collectors.toSet()); // [20, 10]

        // 8.字符串分隔符连接
        String joinName = appleList.stream().map(Apple::getName).collect(Collectors.joining(",", "(", ")")); // (aa,bb,cc)

        // 9.学生总数
        Long count = appleList.stream().collect(Collectors.counting()); // 3

        // 10.最大年龄 (最小的minBy同理)
        Integer maxAge = appleList.stream().map(Apple::getNum).collect(Collectors.maxBy(Integer::compare)).get(); // 20
        // 11.最小值
        Integer minAge = appleList.stream().map(Apple::getNum).collect(Collectors.minBy(Integer::compare)).get(); // 20
        // 12.求和
        Integer sumAge = appleList.stream().collect(Collectors.summingInt(Apple::getNum)); // 40
        // 13.平均年龄
        Double averageAge = appleList.stream().collect(Collectors.averagingDouble(Apple::getNum)); // 13.333333333333334
        // 14.分成两部分，一部分大于10岁，一部分小于等于10岁
        Map<Boolean, List<Apple>> partMap = appleList.stream().collect(Collectors.partitioningBy(v -> v.getNum() > 10));


        // map操作
        Map<String, Object> entryMap = Maps.newHashMap();
        Map<String, Map.Entry<String, Object>> collectMaps = entryMap.entrySet().stream().collect(Collectors.toMap(Map.Entry::getKey, e -> e));

        Table<String, String, String> imageTextTable = HashBasedTable.create();
        imageTextTable.put("text_1", "1111", "2222");
        imageTextTable.put("text_1", "3333", "4444");
        imageTextTable.put("text_2", "5555", "6666");
        imageTextTable.put("text_2", "7777", "8888");

        //rowKey或columnKey的集合
        Set<String> rowKeys = imageTextTable.rowKeySet();
        Set<String> columnKeys = imageTextTable.columnKeySet();
        //value集合
        Collection<String> values = imageTextTable.values();
        log.info("rowKeys={},columnKeys={},values={}", rowKeys, columnKeys, values);
        // rowKeys=[text_1, text_2],columnKeys=[1111, 3333, 5555, 7777],values=[2222, 4444, 6666, 8888]

        Set<Table.Cell<String, String, String>> cells = imageTextTable.cellSet();
        for (Table.Cell<String, String, String> cell : cells) {
            String RowKey = cell.getRowKey();
            String ColumnKey = cell.getColumnKey();
            String Value = cell.getValue();

            log.info("RowKey={},ColumnKey={},Value={}", RowKey, ColumnKey, Value);
            // RowKey=text_1,ColumnKey=1111,Value=2222
            // RowKey=text_1,ColumnKey=3333,Value=4444
        }
        System.out.println("*************************");
        for (String key : rowKeys) {
            Set<Map.Entry<String, String>> rows = imageTextTable.row(key).entrySet();
            for (Map.Entry<String, String> row : rows) {
                log.info("row.getKey()={}，row.getValue()={}", row.getKey(), row.getValue());
                // row.getKey()=1111，row.getValue()=2222
            }
            log.info("rows={}", JSON.toJSONString(rows));
            // rows=[{"1111":"2222"},{"3333":"4444"}]
        }
    }

    //public static void main(String[] args) {
    //    // TODO jdk8 之前的排序
    //    System.out.println("============jdk8 之前的排序");
    //    List<User> list = initList();
    //    Collections.sort(list, new Comparator<User>() {
    //        @Override
    //        public int compare(User o1, User o2) {
    //            return o1.getAge().compareTo(o2.getAge());
    //        }
    //    });
    //    list.forEach(System.out::println);

    //    // TODO jdk8 lambda 排序-升序
    //    System.out.println("============jdk8 lambda排序，升序排序 --> 从小到大");
    //    list = initList();
    //    list.sort((u1, u2) -> u1.getAge().compareTo(u2.getAge()));   //不带参  带参: list.sort((User u1, User u2) -> u1.getAge().compareTo(u2.getAge()));
    //    list.forEach(System.out::println);

    //    // TODO jdk8 升序排序
    //    System.out.println("============jdk8 升序排序 --> 从小到大，Comparator提供的静态方法");
    //    list = initList();
    //    Collections.sort(list, Comparator.comparing(User::getAge));
    //    list.forEach(System.out::println);

    //    // TODO jdk8 降序排序
    //    System.out.println("============jdk8 降序排序 --> 从大到小，Comparator提供的静态方法");
    //    list = initList();
    //    Collections.sort(list, Comparator.comparing(User::getAge).reversed());
    //    list.forEach(System.out::println);
    //    System.out.println();

    //    // TODO jdk8 组合排序
    //    System.out.println("============jdk8 组合排序，Comparator提供的静态方法，先按年纪排序，年纪相同的按名称排序");
    //    list = initList();
    //    Collections.sort(list, Comparator.comparing(User::getAge).thenComparing(User::getName));
    //    list.forEach(System.out::println);
    //}

    /**
     * TODO  初始化List数据
     *
     * @return java.util.List<com.ws.ldy.common.utils.JDK8ListSort.User>
     * @author ws
     * @mail [email protected]
     * @date 2020/5/10 0010 22:49
     */
    private static List<User> initList() {
        List<User> list = new ArrayList<>();
        list.add(new User("lisa", 23));
        list.add(new User("tom", 11));
        list.add(new User("john", 16));
        list.add(new User("jennis", 26));
        list.add(new User("tin", 26));
        list.add(new User("army", 26));
        list.add(new User("mack", 19));
        list.add(new User("jobs", 65));
        return list;
    }
}