栏目分类:
子分类:
返回
名师互学网用户登录
快速导航关闭
当前搜索
当前分类
子分类
实用工具
热门搜索
名师互学网 > IT > 软件开发 > 后端开发 > Java

使用stream api替代sql

Java 更新时间: 发布时间: IT归档 最新发布 模块sitemap 名妆网 法律咨询 聚返吧 英语巴士网 伯小乐 网商动力

使用stream api替代sql

假设mysql数据库中有两张表:

user用户表

  company企业表

当这以上两种类型的数据不是存放在数据库中,而是分别来自两个接口,如果想要对分别来自两个不同的接口的数据做一些join,group(sum),order,limit等操作的时候,我们就需要使用stream api来进行处理

在java中用list来封装用户,企业信息:

    static List buildUserList(){
        ArrayList list = new ArrayList();
        list.add(new User(1,"yc",31,2));
        list.add(new User(2,"yf",32,2));
        list.add(new User(3,"yy",29,1));
        list.add(new User(4,"yl",26,1));
        list.add(new User(5,"ygf",27,3));
        return  list;
    }
    static List buildCompanyList(){
        ArrayList list = new ArrayList();
        list.add(new Company(1,1,"H001","WN"));
        list.add(new Company(2,2,"H001","WN"));
        list.add(new Company(3,3,"H002","CXY"));
        return  list;
    }
@Data
public class Company {
    private Integer id;
    private Integer userId;
    private String code;
    private String name;

    public Company(Integer id, Integer userId, String code, String name) {
        this.id = id;
        this.userId = userId;
        this.code = code;
        this.name = name;
    }
}
@Data
public class User {
    private Integer id;
    private String name;
    private Integer age;
    private Integer type;

    public User(Integer id, String name, Integer age, Integer type) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.type = type;
    }
}

 

order

用mysql实现:

SELECt * FROM user order by age DESC;

 

 用stream api(sorted)实现:

    static List processOrder(List userList){
       return userList.stream().sorted(Comparator.comparing(User::getAge).reversed()).collect(Collectors.toList());
    }

 limit

 用mysql实现:

SELECt * FROM user order by age DESC limit 2;

 用stream api(limit)实现:

    static List processOrderLimit(List userList){
        return userList.stream().sorted(Comparator.comparing(User::getAge).reversed()).limit(2).collect(Collectors.toList());
    }

  

limt分页

 用mysql实现:

SELECt * FROM user order by age DESC limit 0,2;
SELECt * FROM user order by age DESC limit 2,2;
SELECt * FROM user order by age DESC limit 4,2;

 其中limit offset,length其中 offset= (pageIndex-1)*pageSize ,length=pageSize

用stream api(skip,limit)实现:

List userList = processOrderLimitPage(buildUserList(),1,2);
List userList = processOrderLimitPage(buildUserList(),2,2);
List userList = processOrderLimitPage(buildUserList(),3,2);
    static List processOrderLimitPage(List userList,Integer pageIndex,Integer pageSize){
        return userList.stream().sorted(Comparator.comparing(User::getAge).reversed()).skip((pageIndex-1)*pageSize).limit(pageSize).collect(Collectors.toList());
    }

group(sum),order

 用sql实现:

SELECt SUM(age) num,type FROM user GROUP BY type ORDER BY num DESC;

 用stream api(groupingBy,sorted)实现:

    static Map processGroupSum(List userList){
        return userList.stream().collect(Collectors.groupingBy(User::getType,Collectors.summingInt(User::getAge))).entrySet().stream().sorted(Map.Entry.comparingByValue(Comparator.reverseOrder())).collect(Collectors.toMap(Map.Entry::getKey,
                Map.Entry::getValue,
                (oldVal, newVal) -> oldVal,
                LinkedHashMap::new));
    }

  用stream api(merge,sorted)实现:

    static Map processMergeSum(List userList){
        Map map = new HashMap<>();
        userList.stream().forEach(x->map.merge(x.getType(),x.getAge(),Integer::sum));
        return map.entrySet().stream().sorted(Map.Entry.comparingByValue(Comparator.reverseOrder())).collect(Collectors.toMap(Map.Entry::getKey,
                Map.Entry::getValue,
                (oldVal, newVal) -> oldVal,
                LinkedHashMap::new));
    }

 inner join,left join,right join

 用mysql实现:

select * from `user` INNER JOIN company ON `user`.id = company.user_id;
select * from `user` left  JOIN company ON `user`.id = company.user_id;
select * from `user` right JOIN company ON `user`.id = company.user_id;

用stream api实现:

List userCompanyList = processJoin(buildUserList(),buildCompanyList(),"inner");
List userCompanyList = processJoin(buildUserList(),buildCompanyList(),"left");
List userCompanyList = processJoin(buildUserList(),buildCompanyList(),"right");
    static List processJoin(List userList, List companyList,String type) {
        Map> userMap = userList.stream().collect(Collectors.groupingBy(User::getId));
        Map> companyMap = companyList.stream().collect(Collectors.groupingBy(Company::getUserId));
        Set integerList = new HashSet<>();
        if("join".equals(type)){
            integerList = userMap.keySet().stream().filter(companyMap.keySet()::contains).collect(Collectors.toSet());
            Set finalIntegerList = integerList;
            userMap = userMap.entrySet().stream().filter(x -> finalIntegerList.contains(x.getKey())).collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue));
            companyMap = companyMap.entrySet().stream().filter(x -> finalIntegerList.contains(x.getKey())).collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue));
        }

        if("left".equals(type)){
            integerList = userMap.keySet();
        }else if("right".equals(type)){
            integerList = companyMap.keySet();
        }
        List userCompanyList = new ArrayList<>();
        Map> finalUserMap = userMap;
        Map> finalCompanyMap = companyMap;
        integerList.forEach(x -> {
            List userList1 = finalUserMap.get(x);
            List companyList1 = finalCompanyMap.get(x);
            userList1.forEach(m -> {
                if(!CollectionUtils.isEmpty(companyList1)) {
                    companyList1.forEach(n -> {
                        userCompanyList.add(new UserCompany(m, n));
                    });
                }else{
                    userCompanyList.add(new UserCompany(m,null));
                }
            });
        });
        return userCompanyList;
    }
@Data
public class UserCompany {
    private Integer userId;
    private String name;
    private Integer age;
    private Integer type;

    private Integer companyId;
    private String code;
    private String companyName;


    public UserCompany(User user,Company company) {
        if(user != null){
            this.userId = user.getId();
            this.name = user.getName();
            this.age = user.getAge();
            this.type = user.getType();
        }

        if(company != null){
            this.companyId = company.getId();
            this.code = company.getCode();
            this.companyName = company.getCode();
        }
    }
}

转载请注明:文章转载自 www.mshxw.com
本文地址:https://www.mshxw.com/it/978027.html
我们一直用心在做
关于我们 文章归档 网站地图 联系我们

版权所有 (c)2021-2022 MSHXW.COM

ICP备案号:晋ICP备2021003244-6号