JPA - Simple DB schema, complicated JPA configuration...

[kernixski]

Hi,

I have 2 tables:

TABLE 1 (
  CODE VARCHAR(8)    -- primary key
  ...
)

TABLE2 (
  TABLE1_CODE VARCHAR(8),    -- primary key, foreign key 1, foreign key 2
  CODE VARCHAR(10),          -- primary key
  PARENT_CODE VARCHAR(10)    -- foreign key 2
)

The primary key of TABLE2 is TABLE1_CODE + CODE.

TABLE2 is linked to TABLE1 (TABLE1.CODE = TABLE2.TABLE1_CODE). It is the first foreign key.

TABLE2 can have a parent, but the parent must have the same TABLE1_CODE than his children. Then the TABLE1_CODE column is part of the primary key, and also part of the foreign key (to link with the TABLE2 parent).

TABLE1_CODE is part of the primary key, is a foreign key, and is part of a second foreign key.

I do not want to create a sedonc TABLE1_CODE column.

How can I define my pojos and how can I configure JPA / Spring-data-jpa to manage this DB schema ?

If you are a JPA master, feel free to help me. ;o)

[kernixski]

I solved this problem: the link to the parent cannot be insertable or updatable, because the column TABLE1_CODE is part of this link and this column is also part of the primary key. To be able to give a value to the column PARENT_CODE, I added the property parentCode (which is insertable and updatable). I do not have a better solution. If anyone has a better one, I take it. ;o)

Here is the code of the Table2 class:

package example;

import javax.persistence.*;
import java.io.Serializable;

@Entity
@Table(name = "TABLE2")
public class Table2 {

    private PK id;


    @EmbeddedId
    public PK getId() {
        return id;
    }


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


    private String parentCode;

    @Column(name = "PARENT_CODE")
    public String getParentCode() {
        return parentCode;
    }


    public void setParentCode(String parentCode) {
        this.parentCode = parentCode;
    }


    private Table2 parent;


    @JoinColumns({
            @JoinColumn(name = "TABLE1_CODE", referencedColumnName = "TABLE1_CODE", insertable = false, updatable = false),
            @JoinColumn(name = "PARENT_CODE", referencedColumnName = "CODE", insertable = false, updatable = false)})
    @ManyToOne(fetch = FetchType.LAZY)
    public Table2 getParent() {
        return parent;
    }


    public void setParent(Table2 parent) {
        this.parent = parent;
    }


    @Embeddable
    public static class PK implements Serializable {

        private Table1 table1;


        @ManyToOne
        @JoinColumn(name = "TABLE1_CODE", updatable = false)
        public Table1 getTable1() {
            return table1;
        }


        public void setTable1(Table1 table1) {
            this.table1 = table1;
        }


        @Column(name = "CODE", updatable = false)
        private String code;


        public String getCode() {
            return code;
        }


        public void setCode(String code) {
            this.code = code;
        }
    }
}