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 1720696548@qq.com
* @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;
}
}