假设mysql数据库中有两张表:
user用户表
company企业表
当这以上两种类型的数据不是存放在数据库中,而是分别来自两个接口,如果想要对分别来自两个不同的接口的数据做一些join,group(sum),order,limit等操作的时候,我们就需要使用stream api来进行处理
在java中用list来封装用户,企业信息:
static ListbuildUserList(){ 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 ListprocessOrder(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 ListprocessOrderLimit(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)实现:
ListuserList = processOrderLimitPage(buildUserList(),1,2); List userList = processOrderLimitPage(buildUserList(),2,2); List userList = processOrderLimitPage(buildUserList(),3,2);
static ListprocessOrderLimitPage(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 MapprocessGroupSum(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 MapprocessMergeSum(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实现:
ListuserCompanyList = processJoin(buildUserList(),buildCompanyList(),"inner"); List userCompanyList = processJoin(buildUserList(),buildCompanyList(),"left"); List userCompanyList = processJoin(buildUserList(),buildCompanyList(),"right");
static ListprocessJoin(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();
}
}
}



