代码之家 › 专栏 › 技术社区 › Natalie

JPA/hibernate/PostgresDB和Vaadin 8:SQL选择查询从继承的表中获取数据并显示在Vaadin网格中

vaadin8 jpa-2.0 hibernate postgresql

Natalie · 技术社区 · 7 年前

我正在使用带有Java/hibernate/JPA和postgres数据库的Vaadin 8。我想从不同的表中获取数据到我的瓦丁网格中。我的数据库模型中的相关部分如下所示:

复合物java看起来像:

@Entity
@Table(name = "\"Compound\"")
@Inheritance(strategy = InheritanceType.JOINED)
@DiscriminatorColumn(name = "discriminator", discriminatorType = DiscriminatorType.STRING)
public abstract class Compound {

@Id
@GeneratedValue
@Column(name = "id", nullable = false)
private UUID id;

@Column(name = "\"specialName\"", unique = true, nullable = false)
private String specialName;

@Column(name = "\"dateAdded\"", nullable = false)
private LocalDate dateAdded;

@OneToOne(mappedBy = "compound", cascade = CascadeType.ALL, orphanRemoval = true, fetch = FetchType.LAZY)
private StructureData structureData;

//Getter and setter are omitted here

}

探查java类:

@Entity(name = "\"Probe\"")
@Table(name = "\"Probe\"")
@PrimaryKeyJoinColumn(name = "\"compoundId\"")
@DiscriminatorValue(value = "probe")
public class Probe extends Compound {

@Column(name = "\"targetProteinFamily\"", length = 150, nullable = false)
private String targetProteinFamily;

@Column(name = "\"probeClass\"", length = 150, nullable = false)
private String probeClass;

@OneToMany(mappedBy = "probe", cascade = CascadeType.ALL, orphanRemoval = true)
private List<NegativeControl> controls;

  //Getter and setter are omitted 
}

负控制。Java语言

@Entity(name = "\"NegativeControl\"")
@Table(name = "\"NegativeControl\"")
@PrimaryKeyJoinColumn(name = "\"compoundId\"")
@DiscriminatorValue(value = "control")
public class NegativeControl extends Compound {

@ManyToOne(fetch=FetchType.LAZY)
@JoinColumn(name="\"probeId\"")
private Probe probe;

}

结构数据。Java语言

@Entity(name = "\"StructureData\"")
@Table(name = "\"StructureData\"")
public class StructureData {

@Id
private UUID id;

@Column(name = "\"smilesCode\"", columnDefinition = "TEXT")
private String smilesCode;

@Column(name = "\"sdFileName\"", length = 255)
private String sdFileName;

@OneToOne(fetch = FetchType.LAZY)
@MapsId
private Compound compound;

}

仅选择探针表中的数据效果良好,数据显示在我的Vaadin网格中:

List<Probe> allProbes = entityManager.createQuery("select p from " + Probe.class.getName() + " p").getResultList();

 grid.setColumns("specialName", "probeClass", "targetProteinFamily");

但我还需要显示StructureData中的数据和NegativeControl中的特殊名称。我尝试了许多不同的select语句,但总是出现休眠错误。一些错误版本(仅用于从StructureData获取数据)如下所示:

List<Probe> allProbes = entityManager.createQuery("select p , s from " + Probe.class.getName() + " p, " + StructureData.class.getName() + " s where type(p) = probe and Compound.id = s.compound_id").getResultList();
List<Probe> allProbes = entityManager.createQuery("select p , s from " + Probe.class.getName() + " p, " + StructureData.class.getName() + " s where type(p) = probe").getResultList();

第一次选择的错误是:

SEVERE: 
java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: ''Compound'.id' [select p , s from sgc.chemprobesapp.core.model.Probe p, sgc.chemprobesapp.core.model.StructureData s where type(p) = probe and Compound.id = s.compound_id]
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:131)

我正在寻找一个工作选择声明和瓦丁网格。设置列代码。

谢谢你的帮助!

编辑: 我解决了问题的sql部分。以下查询正在获取所需的数据:

List<Probe> allProbes = entityManager
            .createQuery("select p from sgc.chemprobesapp.core.model.Probe p "
                    + "left join fetch p.controls c left join fetch p.structureData s", Probe.class)
            .getResultList();

grid.setColumns("specialName", "probeClass", "targetProteinFamily",  "structureData.sdFileName");

这似乎有效。但是我想用我的标题设置这些列,例如

grid.addColumn(Probe::getSpecialName).setCaption("Name");
grid.addColumn(Probe::getTargetProteinFamily).setCaption("Target protein family");
grid.addColumn(Probe::getProbeClass).setCaption("Probe class");
grid.addColumn(Probe::getStructureData::getSdFileName).setCaption("Structure");

但是Probe::getStructureData给出了错误“此表达式的目标类型必须是函数接口”。

1 回复 | 直到 7 年前

Natalie 7 年前

如上述编辑中所述,查询为:

List<Probe> allProbes = entityManager
        .createQuery("select p from sgc.chemprobesapp.core.model.Probe p "
                + "left join fetch p.controls c left join fetch p.structureData s", Probe.class)
        .getResultList();

在Vaadin 8网格中显示来自不同数据库表的数据可以如下所示:

private Grid<Probe> grid = new Grid<>(Probe.class);
grid.removeAllColumns();
grid.addColumn(probe -> probe.getSpecialName()).setCaption("Name");
grid.addColumn(probe -> probe.getTargetProteinFamily()).setCaption("Target protein family");
grid.addColumn(probe -> probe.getClass()).setCaption("Class");
grid.addColumn(probe -> probe.getStructureData().getSdFileName()).setCaption("Structure"); 
grid.addColumn(probe -> probe.getNegativeControl().get(0).getSpecialName()).setCaption("Negative control");
grid.setItems(allProbes);