JPA/ hibernate/PostgresDB and Vaadin 8: SQL select query to get data from inherited tables and show in Vaadin grid

I am using Vaadin 8 with Java/hibernate/JPA and a postgres database. I want to get data from different tables into my Vaadin grid. The relevant part from my database model looks as follows:

Compound.java looks like:

@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

}

Probe.java class:

@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 
}

NegativeControl.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;

}

StructureData.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;

}

The select for only the data from the Probe table works well and the data are displayed in my Vaadin grid:

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

And in the view class:

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

But I need to show also data from StructureData and the special name from the NegativeControl. I tried many different select statements but I always get an hibernate error. Some error versions (just to get the data from StructureData) are as follows:

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();

The error for the first select is:

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)

I am looking for a working select statement and the Vaadin grid.setColumns code.

Thank you for your help!

Edit: I solved the sql part of my problem. The following query is getting the desired data:

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();

But my second part is the Vaadin8 grid problem. I don't know how to set the desired columns which are not Probe columns directly but StructureData columns.

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

This seems to work. But I would like to set the columns with my captions such as

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");

But Probe::getStructureData gives the error "The target type of this expression must be a functional interface".

I hope somebody can help me with this. Thank you!

Solution

I finally found out how to do it. I post the answer in case someone else has the same problem to solve.

As specified in the edit above the query is:

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();

The display of data from different database tables in a Vaadin 8 grid can be done as follows:

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);

I had to remove all columns first because columns of all inherited tables were shown. The variable allProbes is the List of probes which I got from the above database query.