如何将SQL查询转换为Lambda或Stream（Java8）？

Question

我从Java开始，希望将查询转换为Lambda中的SQL或Java 8中的Stream。

按照下面的查询操作。

SELECT * FROM Correspondencia c, (SELECT entidadeOrigem, MAX(m1) similaridadeMaxima FROM Correspondencia WHERE m1 <> 0 GROUP BY entidadeOrigem) r WHERE c.entidadeOrigem = r.entidadeOrigem AND c.m1 = 
r.similaridadeMaxima

我创建了下面带有getters和setters的Correpondencia类：

public class Correspondencia {

    int codigo;
    String nome;
    String entidadeOrigem;
    String EntidadeDestino;
    double m1;
    double m2;
    double m3;


double medMax;
    double dem;


    public int getCodigo() {
        return codigo;
    }
    public void setCodigo(int codigo) {
        this.codigo = codigo;
    }
    public String getNome() {
        return nome;
    }
    public void setNome(String nome) {
        this.nome = nome;
    }
    public String getEntidadeOrigem() {
        return entidadeOrigem;
    }
    public void setEntidadeOrigem(String entidadeOrigem) {
        this.entidadeOrigem = entidadeOrigem;
    }
    public String getEntidadeDestino() {
        return EntidadeDestino;
    }
    public void setEntidadeDestino(String entidadeDestino) {
        EntidadeDestino = entidadeDestino;
    }
    public double getM1() {
        return m1;
    }
    public void setM1(double m1) {
        this.m1 = m1;
    }
    public double getM2() {
        return m2;
    }
    public void setM2(double m2) {
        this.m2 = m2;
    }
    public double getM3() {
        return m3;
    }
    public void setM3(double m3) {
        this.m3 = m3;
    }
    public double getMedMax() {
        return medMax;
    }
    public void setMedMax(double medMax) {
        this.medMax = medMax;
    }
    public double getDem() {
        return dem;
    }
    public void setDem(double dem) {
        this.dem = dem;
    }
}

我创建了一个列表：

List<Correspondencia> cor = new ArrayList<Correspondencia>();

cor.add(new Correspondencia(41,"Paper_Organization","Paper", "Organization",0,0.14,0.04,0.23,0.08));
cor.add(new Correspondencia(22,"Paper_Organization","Paper", "Organization",0,0.15,0.04,0.23,0.08));
cor.add(new Correspondencia(22,"Paper_Organization","Paper", "Organization",0,0.36,0.04,0.23,0.08));
cor.add(new Correspondencia(11,"email_hasanemail","email", "hasanemail",0.19,0.21,0.19,0.,0.12));
cor.add(new Correspondencia(11,"email_hasanemail","email", "hasanemail",0.25,0.21,0.19,0.,0.12));
cor.add(new Correspondencia(11,"email_hasanemail","email", "hasanemail",0.37,0.21,0.19,0.,0.12));
cor.add(new Correspondencia(31,"Review_Reviewer","Review","Reviewer",0.36,0.5,0.41,0.,0.25));
cor.add(new Correspondencia(31,"Review_Reviewer","Review","Reviewer",0.38,0.5,0.41,0.,0.25));
cor.add(new Correspondencia(31,"Review_Reviewer","Review","Reviewer",0.37,0.5,0.41,0.,0.25));
cor.add(new Correspondencia(32,"email_hasanemail","email", "hasanemail",0.36,0.5,0.41,0.,0.25));
cor.add(new Correspondencia(01,"PaperAbstract_Abstract","PaperAbstract","Abstract", 0.33,0.45,0.32,0.,0.22));
cor.add(new Correspondencia(01,"PaperAbstract_Abstract","PaperAbstract","Abstract", 0.37,0.45,0.32,0.,0.22));

现在我想根据下面的查询用Lambda或Stream过滤课程：

SELECT * FROM Correspondencia c, (SELECT entidadeOrigem, MAX(m1) similaridadeMaxima FROM Correspondencia WHERE m1 <> 0 GROUP BY entidadeOrigem) r WHERE c.entidadeOrigem = r.entidadeOrigem AND c.m1 = r.similaridadeMaxima

这将返回m1的最大值，按entidadeOrigem的项目分组：

22,"Paper_Organization","Paper", "Organization",0,0.36,0.04,0.23,0.08
11,"email_hasanemail","email", "hasanemail",0.37,0.21,0.19,0.,0.12
31,"Review_Reviewer","Review","Reviewer",0.38,0.5,0.41,0.,0.25
01,"PaperAbstract_Abstract","PaperAbstract","Abstract", 0.37,0.45,0.32,0.,0.22

一位同事给了我以下代码：

Correspondencia maxM1 = cor.stream()
            .filter(x -> x.getM1() != 0)
            .max(Comparator.comparing(x -> x.getM1()))
            .get();

        System.out.println(maxM1);

但是，它只返回entidadeOrigem的最大m1值。

我需要的是根据下面的SQL返回由entidadeOrigem分组的m1的每个重复项的最高值。

SELECT * FROM Correspondencia c, (SELECT entidadeOrigem, MAX(m1) 
similaridadeMaxima FROM Correspondencia WHERE m1 <> 0 GROUP BY 
entidadeOrigem) r WHERE c.entidadeOrigem = r.entidadeOrigem AND c.m1 = 
r.similaridadeMaxima

Answer 1

首先根据entidadeOrigem字段分组到Map

List<Correspondencia> result = cor.stream()
       .collect(Collectors.groupingBy(Correspondencia::getEntidadeOrigem))
       .values()
       .stream()
       .map(c->c.stream().max(Comparator.comparingDouble(Correspondencia::getM1)))
       .filter(p->p.isPresent())
       .map(Optional::get)
       .collect(Collectors.toList());