CODEWITHSUNDEEP
jpatree
Capacytron
Capacytron

Reputation: 3739

Declare JPA entity for a tree element

I do have a DB table with unidirectional trees. Leafs of these trees can have several children/parents. Cycles are restricted. Here is my DB table definition:

CREATE MULTISET TABLE WORKFLOW_SEQ_REL ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      WORKFLOW_SEQ_ID INTEGER NOT NULL,
      REL_WORKFLOW_SEQ_ID INTEGER NOT NULL,
      JOB_ID BIGINT)
PRIMARY INDEX ( WORKFLOW_SEQ_ID );

As you can see it doesn't have a primary key right now. But it would appear later :) Really PK is: JOB_ID+PARENT_ID+CHILD_ID.

The idea is:

  1. REL_WORKFLOW_SEQ_ID = PARENT
  2. WORKFLOW_SEQ_ID = CHILD
  3. JOB_ID = TREE_IDENTIFICATOR (a determinant to separate different trees stored in one table).

I'm trying to declare a JPA entity:

@Entity
@Table(name="WORKFLOW_SEQ_REL")
public class EtlWorkflowSeqNode {


    @EmbeddedId 
    public EtlWorkflowSeqNodeId etlWorkflowSeqNodeId;

    //@Column(name="JOB_ID")
    //public Integer jobId;

    @Embeddable
    class EtlWorkflowSeqNodeId{

        @Column(name="JOB_ID")
        public Integer jobId;


        @ManyToOne(fetch = FetchType.EAGER)
        @JoinColumn(name="REL_WORKFLOW_SEQ_ID")
            //EtlWorkflowSeq.id = PK of EtlWorkflowSeq entity
        public EtlWorkflowSeq parent;

        @OneToMany(fetch=FetchType.EAGER /*, mappedBy="parent"*/)
        @JoinColumn(name="WORKFLOW_SEQ_ID")
        public Set<EtlWorkflowSeq> children;
    }
}

And I gen an error: Caused by: org.hibernate.AnnotationException: A Foreign key refering models.EtlWorkflowSeqNode from models.EtlWorkflowSeq has the wrong number of column. should be 2

Here is EtlWorkflowSeq entity:

@Entity
@Table(name="WORKFLOW_SEQ")
public class EtlWorkflowSeq {

    @Id
    @Column(name="WORKFLOW_SEQ_ID")
    public Integer id;

    @OneToOne(fetch=FetchType.EAGER)
    @JoinColumn(name="WORKFLOW_ID")
    public EtlWorkflow etlWorkflow;
}

What do I do wrong?

UPD: Here are table definitions: --a bad design. PK should be: WORKFLOW_SEQ_ID + REL_WORKFLOW_SEQ_ID + JOB_ID 

 CREATE MULTISET TABLE WORKFLOW_SEQ_REL ,NO FALLBACK ,
         NO BEFORE JOURNAL,
         NO AFTER JOURNAL,
         CHECKSUM = DEFAULT,
         DEFAULT MERGEBLOCKRATIO
         (
          WORKFLOW_SEQ_ID INTEGER NOT NULL, --a ref to child
          REL_WORKFLOW_SEQ_ID INTEGER NOT NULL, -- a ref to parent
          START_TYPE_ID SMALLINT NOT NULL, -- a type of connection
          DISABLE_START_TYPE_ID SMALLINT, -- other type of connection
          JOB_ID BIGINT) -- a tree determinant, 
    PRIMARY INDEX ( WORKFLOW_SEQ_ID );

    CREATE MULTISET TABLE   WORKFLOW_SEQ ,NO FALLBACK ,
         NO BEFORE JOURNAL,
         NO AFTER JOURNAL,
         CHECKSUM = DEFAULT,
         DEFAULT MERGEBLOCKRATIO
         (
          WORKFLOW_SEQ_ID INTEGER NOT NULL, -- an id
          WORKFLOW_ID BIGINT NOT NULL, -- a ref to original workflow, not interesting
          IS_NAME VARCHAR(255) CHARACTER SET UNICODE NOT CASESPECIFIC, -- some name
          INFO_SYSTEM_INST_CD VARCHAR(255) CHARACTER SET UNICODE NOT CASESPECIFIC, -- other name
          DISABLE BYTEINT) -- so garbage
    UNIQUE PRIMARY INDEX ( WORKFLOW_SEQ_ID ); -- it should also be a PK

The Idea is that several trees are stored in WORKFLOW_SEQ_REL JOB_ID is a determinant for trees. WORKFLOW_SEQ_ID, REL_WORKFLOW_SEQ_ID refer some cutomized template from REL_WORKFLOW_SEQ table.

Upvotes: 0

Views: 670

Answers (1)

siebz0r
siebz0r

Reputation: 20339

I cannot help noticing that there is an inconsistency in your question.

You first state that:

Leafs of these trees can have several children/parents.

This in my believe makes the relationship between leafs many to many.

As I make of your question that EtlWorkflowSeq represent leafs, I think EtlWorkflowSeqNode represents the relationship between EtlWorkflowSeq objects?

However, the nodes point to one parent and many children.

You can use something like this to create something similar:

@Entity
@Table(name="WORKFLOW_SEQ")
public class EtlWorkflowSeq
{
    @Id
    @GeneratedValue
    @Column(name="WORKFLOW_SEQ_ID")
    public Integer id;

    @ManyToOne
    @JoinColumn(name="WORKFLOW_ID")
    public EtlWorkflow etlWorkflow;

    @ManyToMany
    @JoinTable(name = "WORKFLOW_SEQ_REL")
    private Set<EtlWorkflowSeq> children;

    @ManyToMany(mappedBy = "children")
    private Set<EtlWorkflowSeq> parents;

    @ManyToOne
    @JoinColumn(name = "JOB_ID", referencedColumnName = "id")
    private Job job;
}

This would make EtlWorkflowSeqNode and EtlWorkflowSeqNodeId obsolete.

I also would like to state that when using an @Embeddable you should only use base types in them. Using other than base types is not possible/causes problems/is not standard (correct me if I'm wrong).

If you would like to use foreign keys in a composite primary key you can use this:

@Entity
public class Foo
{
    @Id
    private Long id;
}

@Entity
public class Bar
{
    @EmbeddedId
    private BarPK key;

    @MapsId(value = "fooId")
    @ManyToOne
    @JoinColumns({
        @JoinColumn(name = "foo_id", referencedColumnName = "id")
    })
    private Foo foo;
}

@Embeddable
public class BarPK
{
    @Column(name = "id")
    private Long id;

    @Column(name = "foo_id")
    private Long fooId;
}

Upvotes: 1

Related Questions