What is the best solution to handle ID table in JPA?

Question

Hi, for my job I'm trying to handle correctly a table used to keep consistency between other tables.
Architects decided to provide this solution on an Oracle Database 12c:

Relational architecture

Due to the amount of data, there is a partitionning controled by the partition colomn on every entity. The entity id is generated by each entity type.

I tryed to use the JPA entity inheritance to simplify insertion in database. This code is the best solution that I've found:

@Entity
@Table(name="ID_TABLE")
@Inheritance(strategy=InheritanceType.JOINED)
@DiscriminatorColumn(name="Entity_Type", discriminatorType=DiscriminatorType.STRING)
public abstract class IdTable implements Serializable {
    private static final long serialVersionUID = 1L;

    @EmbeddedId
    protected IdTablePK id;
}

@Embeddable
public class IdTablePK implements Serializable {
    @Column(name="Partition_col")
    protected short partitionCol;

    @Column(name="Entity_ID")
    protected long entityID;

    @Column(name="Version_ID")
    protected short versionID;

    @Column(name="Entity_Type")
    protected String entityType;
}

@Entity
@DiscriminatorValue("01")
@Table(name = "ENTITY1")
@PrimaryKeyJoinColumns({
    @PrimaryKeyJoinColumn(name="Partition_col1", referencedColumnName="Partition_col"),
    @PrimaryKeyJoinColumn(name="Entity_ID1", referencedColumnName="Entity_ID"),
    @PrimaryKeyJoinColumn(name="Version_ID1", referencedColumnName="Version_ID"),
    @PrimaryKeyJoinColumn(name="Entity_Type1", referencedColumnName="Entity_Type")
})
public class Entity1 extends IdTable{
...
}

The problem is that I'm expecting very poor performance on select command because of the inner join automatically made by JPA:

select
    entity1.Partition_col1
    entity1.Entity_ID1
    entity1.Version_ID1
    entity1.Entity_Type1
    entity1.Data1
from
    entity1
inner join
    table_ID 
    on entity1.Partition_col=table_ID.Partition_col
        entity1.Entity_ID=table_ID.Entity_ID
        entity1.Version_ID=table_ID.Version_ID
        entity1.Entity_Type=table_ID.Entity_Type
where
    entity1.Partition_col1=?
    and entity1.Entity_ID1=?
    and entity1.Version_ID1=?
    and entity1.Entity_Type1=?

Am I right to worry about performance with this kind of relation ? Is there any better solution or any way to avoid that join on select command?

**EDIT** : Chris gave me an idea. What if the IdTable entity have all related entity joined like this ?

@Entity
@Table(name="ID_TABLE")
public class IdTable implements Serializable {
    private static final long serialVersionUID = 1L;

    @EmbeddedId
    protected IdTablePK id;

    @OneToOne(fetch=FetchType.LAZY, cascade = CascadeType.PERSIST, optional = true)
    @JoinColumns({
        @JoinColumn(name="Partition_col1", referencedColumnName="Partition_col"),
        @JoinColumn(name="Entity_ID1", referencedColumnName="Entity_ID"),
        @JoinColumn(name="Version_ID1", referencedColumnName="Version_ID"),
        @JoinColumn(name="Entity_Type1", referencedColumnName="Entity_Type")
    })
    private Entity1 e1;
    ...
}

@Embeddable
public class IdTablePK implements Serializable {
    @Column(name="Partition_col")
    protected short partitionCol;

    @Column(name="Entity_ID")
    protected long entityID;

    @Column(name="Version_ID")
    protected short versionID;

    @Column(name="Entity_Type")
    protected String entityType;
}

@Entity
@Table(name = "ENTITY1")
public class Entity1 implements Serializable{
    @EmbeddedId
    protected Entity1PK id;
    ...
}

@Embeddable
public class Entity1PK implements Serializable {
    @Column(name="Partition_col1")
    protected short partitionCol1;

    @Column(name="Entity_ID1")
    protected long entityID1;

    @Column(name="Version_ID1")
    protected short versionID1;

    @Column(name="Entity_Type1")
    protected String entityType1;
}

IdTable is inserted before Entity1 and there is no direct link to IdTable when requesting Entity1.

Now, is it possible to use Entity1 sequence to fill Entity_ID in IdTable ?

Chris · Answer 1 · 2015-10-13T13:00:33.667

0

Using the Entity1PK from your second try (you can remove the annotations), try:

@Entity
@Table(name="ID_TABLE")
@IdClass(Entity1PK.class)
public class IdTable implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @OneToOne(fetch=FetchType.LAZY, cascade = CascadeType.PERSIST, optional = true)
    @JoinColumns({
        @JoinColumn(name="Partition_col1", referencedColumnName="Partition_col"),
        @JoinColumn(name="Entity_ID1", referencedColumnName="Entity_ID"),
        @JoinColumn(name="Version_ID1", referencedColumnName="Version_ID"),
        @JoinColumn(name="Entity_Type1", referencedColumnName="Entity_Type")
    })
    private Entity1 e1;
    ..
 }

@Entity
@Table(name = "ENTITY1")
@IdClass(Entity1PK.class)
public class Entity1 {

  @Id
  @Column(name="Partition_col1")
  protected short partitionCol1;

  @Id
  @GeneratedValue(strategy=GenerationType.TABLE)
  @Column(name="Entity_ID1")
  protected long entityID1;

  @Id
  @Column(name="Version_ID1")
  protected short versionID1;

  @Id
  @Column(name="Entity_Type1")
  protected String entityType1;
})

Put the sequencing annotation of any form on which ever field you want to use sequencing on.

edited Oct 13 '15 at 13:00

answered Oct 08 '15 at 14:23

Chris

20,138
2
29
43

Your solution is elegant but that's not what I'm looking for. The result here is that JPA is seeking in the IdTable before loading Entity1 and does not persist IdTable entity before Entity1. The problem is that we want to use Entity1 sequence but we have to persist IdTable before. I'm editing my question to add the second try we made. – TomLaco Oct 09 '15 at 14:51
EclipseLink will persist IdTable for you if that is what you are looking for. You just need to set the cascade.Persist cascade option on the mapping, which will cause the referenced IdTable to be found and inserted with the Entity1, and populate Entity1 fields with values from the IdTable. I am not clear what you are asking, but if you want values set in Entity1 to populate IdTable, just switch the reference direction using the model above as the example, essentially just renaming the two classes above. – Chris Oct 09 '15 at 18:23
It took me a while to understand the project. Like I said, we have to insert Entity1 with it's id set by it's sequencer, but the IdTable has to be set before. Plus, when reading Entity1, we don't want to read IdTable in any way. Moreover, EclipseLink is not allowed in this project... It looks like a dead end ! I think that I'm not far from the best solution with the last edit, and I'm looking for the best way to get the value of Entity1 sequencer in JPA (or JPQL), I'm trying this : [link](http://stackoverflow.com/questions/6386888/get-next-sequence-value-from-database-using-hibernate) – TomLaco Oct 12 '15 at 08:04
I'm not sure what you are referring to about EclipseLink being a dead-end, but it is just one example of a JPA provider; my prefered one as I am involved in the open source project. I should have said JPA as any provider would work the same. – Chris Oct 13 '15 at 13:02
I'd never say that about EclipseLink. I was speaking about the solution i'm looking for with restrictions I have. I'm always impressed by the work done by the open source project teams and I'm sorry if I made myself misunderstood. About the solution, we're finally putting all _Entity_ inside the _TableID_ to persist in cascade mode then we only read _Entity_. – TomLaco Oct 13 '15 at 14:36
It seemed like the tables were going for a form of inheritance, or a variable 1:1 mapping that would allow IdTable to reference an Entity1 or Entity2, and while this is available in EclipseLink, it isn't portable in JPA. If you can change the tables design, that might be the better solution. – Chris Oct 13 '15 at 15:17
Nevermind, they're changing everything in the project. I'm going to have tough sleep soon ! Many thanks for your help. – TomLaco Oct 14 '15 at 14:43

What is the best solution to handle ID table in JPA?

1 Answers1