Mapping multiple db rows to one object?

ShawnInDenver · **Joined:** Tue Mar 17, 2009 12:19 pm **Posts:** 22

(using Hibernate 3.3.1 and Annotations 3.4) I have all of my mappings working for my legacy database but ran into a roadblock on the design of one them.

A quick overview. I have a base object:

Code:

@Entity
@Table(name = "Objects")
@PrimaryKeyJoinColumn(name = "objectid", referencedColumnName = "id"))
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
public abstract class SuperObject {

And its identifier:

Code:

    @Id
    @DocumentId
    @GeneratedValue(strategy = GenerationType.TABLE, generator = "ObjectId")
    @TableGenerator(name = "ObjectId", table = "TableKeys", initialValue = 1, pkColumnName = "tablename", pkColumnValue = "Objects", valueColumnName = "id")
    @Column(name = "id")
    protected int getSurrogateKey() {
        return _surrogateKey;
    }

Several classes extend this object (and their corresponding tables use this surrogateKey as the foreign key to the superclass table). Example:

Code:

@Entity
@SecondaryTable(name = "Concepts")
@DiscriminatorValue(value = ValueTypeIds.CONCEPT)
public class Concept {

All is well so far, but then there is an exceptional case. I have a subclass of this SuperObject called "Relation". Relations are between concepts (shown above). Relations have one sourceConcept and one-to-many targetConcepts. But here's where it gets tricky. For performance reasons, all of this is stored in the Relations table. Thus, a single Relation can have multiple rows in the Relations table (multiple occurrences of the same surrogateKey), with the db composite key being the surrogateKey + target.

A more graphical explanation of the paragraph above...

Objects Table:
id
1 (concept)
2 (concept)
3 (concept)
4 (relation)

Relations Table:
id/surrogateKey_____sourceConcept_____targetConcept
4_________________1________________2___________
4_________________1________________3___________

So my basic question is how to map this in Hibernate.

Will the Relation class be allowed to extend the above SuperObject class and can Hibernate know to store and retrieve multiple rows from the Relations table given a single surrogateKey?

Obviously, I need to reflect the composite key in my object model. So I have played around with @Embeddable/@EmbeddedId and I believe I have that working but I have a total of three classes. Relations (which is an entity class and the business representation of a Relation), RelationEntity (which is also an entity class for the same table but includes a specific target). The Relations class has a list of RelationEntities to contain all the relation targets within one Relation. And then the RelationPK class, which is the @embeddable defining the actual composite key for Hibernate.

Does anyone have a different approach for this problem? Let me know if you need more information.

Thanks!

robb.greathouse · **Joined:** Wed Mar 18, 2009 10:26 am **Posts:** 4

OK,

There is absolutely positively no way to map multiple rows in the same table to an object. This is because an Object has to have a unique primary key.

That said that wouldn't be the solution to your problem anyway.

What you want to do is map two different Classes to the same table. One class would be the Relation class and the other class would map to the same table but with a different column as the primary key. A column that is unique for that Object.

You would need to have a unique id for each class. You might have to add a column for grouping the collection members.

ShawnInDenver · **Joined:** Tue Mar 17, 2009 12:19 pm **Posts:** 22

Thanks, Robb. I have the two classes each with their own unique id.

Relation.java (extending its id from the superObject class), here's its mapping to the 2nd entity.

Code:

    @OneToMany(targetEntity = com.xxx.RelationEntity.class, mappedBy = "relation", cascade = {CascadeType.PERSIST,
            CascadeType.REMOVE })
    @org.hibernate.annotations.Cascade(value = org.hibernate.annotations.CascadeType.DELETE_ORPHAN)
    public List<Concept> getTargets() {
        return _targets;
    }

RelationEntity.java (also mapped to the same table), here's its id

Code:

    @Id
    @Column(name = "sequence", nullable = false, insertable = false, updatable = false)
    public int getSequence() {
        return _sequence;
    }

The other side of the above "mappedBy" in RelationEntity looks like this:

Code:

    @ManyToOne(targetEntity = com.xxx.Relation.class)
    @JoinColumn(name = "id", insertable = false, updatable = false)
    public Relation getRelation() {
        return _relation;
    }

But when persisting a Relation, this very method incurs an IllegalArgumentException "object is not an instance of declaring class" in class: RelationEntity, getter method of property: relation

Seems Hibernate is trying to invoke getRelation() on a Relation object instance. I would expect it to invoke this method on a RelationEntity object instead. Why is that? What's the problem here?

robb.greathouse · **Joined:** Wed Mar 18, 2009 10:26 am **Posts:** 4

Couple of things. How are you going to be able to add a record with @ID marked insertable=false and updatable=false ?

Can you post the entire RelationEntity Class?

robb.greathouse · **Joined:** Wed Mar 18, 2009 10:26 am **Posts:** 4

OK. I think I see the problem.

Here is one way to do this. Have both classes use the same ID column. But then you will need to add a discriminator column for differentiating the two classes.

That will be the only way for Hibernate to differentiate between the two classes.

Then you will need to have another column that acts as the 'Parent' column to link the child object (object in collection, RelationEntity) to the 'Parent' Relation object.

robb.greathouse · **Joined:** Wed Mar 18, 2009 10:26 am **Posts:** 4

One last comment.

Instead of using a discriminator column and a parent column you could just have the parent column and then use the discriminator formula to determine class. Null value would mean that it is a Relation.class object and a non-null value would mean that it is a RelationEntity.

ShawnInDenver · **Joined:** Tue Mar 17, 2009 12:19 pm **Posts:** 22

As for the @ID question, Hibernate requests that it be marked insertable = false , updatable = false because the "id" column is duplicated.

So I have the discriminator formula on the RelationEntity now but I don't think the application is even getting that far yet. I'm getting:

Code:

org.hibernate.cfg.NotYetImplementedException: Collections having FK in secondary table
   at org.hibernate.cfg.AnnotationBinder.processElementAnnotations(AnnotationBinder.java:1555)

For the following mapping in Relation.java:

Code:

    @OneToMany(targetEntity = com.xxx.RelationEntity.class)
    @JoinColumn(name = "targetid", table = "Relations", nullable = false)
    public List<RelationEntity> getTargets() {
        return _relationEntities;
    }

And here's the inverse in RelationEntity.java:

Code:

    @ManyToOne(targetEntity = com.xxx.Relation.class)
    @JoinColumn(name = "id", table = "Relations", insertable = false, updatable = false)
    public Relation getRelation() {
        return _relation;
    }

What's going on here?