Getting entity from table without having primary key in Hibernate

Question

I'm currently working on a project where I'm trying to get a list of enities from table which does not have a primary key (dk_systemtherapie_merkmale). This table is 1:n related to another table (dk_systemtherapie). See the screenshot for the table structure. When getting an entry for dk_systemtherapie, the program fetches the Collection "dkSystemtherapieMerkmalesById". However, the first table entry is fetched as often as the number of actual entries in the table is. It never fetches the other entries from dk_systemtherapie_merkmale. I assume it has something to do with the fact that hibernate can't differ between the entries, but I don't know how to fix it.

Table schema

I've created two corresponding entity classes, dk_systemtherapie:

@Entity
@Table(name = "dk_systemtherapie", schema = "***", catalog = "")
public class DkSystemtherapieEntity {
    private int id;
    private Collection<DkSystemtherapieMerkmaleEntity> dkSystemtherapieMerkmalesById;

    @Id
    @Column(name = "id")
    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

 @OneToMany(mappedBy = "dkSystemtherapieByEintragId")
    public Collection<DkSystemtherapieMerkmaleEntity> getDkSystemtherapieMerkmalesById() {
        return dkSystemtherapieMerkmalesById;
    }

    public void setDkSystemtherapieMerkmalesById(Collection<DkSystemtherapieMerkmaleEntity> dkSystemtherapieMerkmalesById) {
        this.dkSystemtherapieMerkmalesById = dkSystemtherapieMerkmalesById;
    }
}

Here the second one, which is accessing the table without a primary key, dk_systhemtherapie_merkmale:

@Entity @IdClass(DkSystemtherapieMerkmaleEntity.class)
@Table(name = "dk_systemtherapie_merkmale", schema = "***", catalog = "")
public class DkSystemtherapieMerkmaleEntity implements Serializable {
    @Id private Integer eintragId;
    @Id private String feldname;
    @Id private String feldwert;
    private DkSystemtherapieEntity dkSystemtherapieByEintragId;

    @Basic
    @Column(name = "eintrag_id")
    public Integer getEintragId() {
        return eintragId;
    }

    public void setEintragId(Integer eintragId) {
        this.eintragId = eintragId;
    }

    @Basic
    @Column(name = "feldname")
    public String getFeldname() {
        return feldname;
    }

    public void setFeldname(String feldname) {
        this.feldname = feldname;
    }

    @Basic
    @Column(name = "feldwert")
    public String getFeldwert() {
        return feldwert;
    }

    public void setFeldwert(String feldwert) {
        this.feldwert = feldwert;
    }

    @Id
    @ManyToOne
    @JoinColumn(name = "eintrag_id", referencedColumnName = "id")
    public DkSystemtherapieEntity getDkSystemtherapieByEintragId() {
        return dkSystemtherapieByEintragId;
    }

    public void setDkSystemtherapieByEintragId(DkSystemtherapieEntity dkSystemtherapieByEintragId) {
        this.dkSystemtherapieByEintragId = dkSystemtherapieByEintragId;
    }
 }

Erik Wolf · Accepted Answer · 2018-01-10T13:05:59.357

I assume the problem is releated to the fact that Hibernate is using the following annotation as the one and only id for fetching data from database.

@Id
@ManyToOne
@JoinColumn(name = "eintrag_id", referencedColumnName = "id")
public DkSystemtherapieEntity getDkSystemtherapieByEintragId() {
    return dkSystemtherapieByEintragId;
}

This leads to the problem that when getting more than one entry with the same id (as the id is not unique), you will get the number of entries you would like to but hibernate is always fetching the first entry for this id. So in fact you are getting dublicate entries.

So how to fix this?

According to this question: Hibernate and no PK, there are two workarounds which are actually only working when you don't have NULL entries in your table (otherwise the returning object will be NULL as well) and no 1:n relationship. For my understanding, hibernate is not supporting entities on tables without primary key (documentation). To make sure getting the correct results, I would suggest using NativeQuery.

Remove the Annotations and private DkSystemtherapieEntity dkSystemtherapieByEintragId; (incl. beans) from DkSystemtherapieMerkmaleEntity.java und add a constructor.

public class DkSystemtherapieMerkmaleEntity {
    private Integer eintragId;
    private String feldname;
    private String feldwert;

    public DkSystemtherapieMerkmaleEntity(Integer eintragId, String feldname, String feldwert) {
        this.eintragId = eintragId;
        this.feldname = feldname;
        this.feldwert = feldwert;
    }

    public Integer getEintragId() {
        return eintragId;
    }

    public void setEintragId(Integer eintragId) {
        this.eintragId = eintragId;
    }

    public String getFeldname() {
        return feldname;
    }

    public void setFeldname(String feldname) {
        this.feldname = feldname;
    }

    public String getFeldwert() {
        return feldwert;
    }

    public void setFeldwert(String feldwert) {
        this.feldwert = feldwert;
    }
}

Remove private Collection<DkSystemtherapieMerkmaleEntity> dkSystemtherapieMerkmalesById; (incl. beans) from DkSystemtherapieEntity.java.

Always when you need to get entries for a particular eintrag_id, use the following method instead of the Collection in DkSystemtherapieEntity.java.

public List<DkSystemtherapieMerkmaleEntity> getDkSystemtherapieMerkmaleEntities(int id) {
    Transaction tx = session.beginTransaction();
    String sql = "SELECT * FROM dk_systemtherapie_merkmale WHERE eintrag_id =:id";

    List<Object[]> resultList;
    resultList = session.createNativeQuery(sql)
            .addScalar("eintrag_id", IntegerType.INSTANCE)
            .addScalar("feldname", StringType.INSTANCE)
            .addScalar("feldwert", StringType.INSTANCE)
            .setParameter("id", id).getResultList();

    tx.commit();

    List<DkSystemtherapieMerkmaleEntity> merkmale = new ArrayList<>();
    for (Object[] o : resultList) {
        merkmale.add(new DkSystemtherapieMerkmaleEntity((Integer) o[0], (String) o[1], (String) o[2]));
    }

    return merkmale;
}

Call getDkSystemtherapieMerkmaleEntities(dkSystemtherapieEntityObject.getid()) instead of getDkSystemtherapieMerkmalesById().

Getting entity from table without having primary key in Hibernate

1 Answers1