CODEWITHSUNDEEP
hibernateexceptionjpaone-to-manyjoincolumn
user613114
user613114

Reputation: 2821

Join by a single column instead of a composite primary key

I have single JPA entity and I would to add self join on this table. Table looks like e.g.

@Entity
@Table(name = "TABLE_A")
@IdClass(TableAPk.class)
public class TableA implements Serializable {

    private static final long serialVersionUID = 1L;


    @Id
    @Column(name = "COLUMN_1", nullable = false, length = 64)
    private String            column_1;

    @Id
    @Column(name = "COLUMN_2", nullable = false, precision = 10, scale = 2)
    private BigDecimal        column_2;


    @ManyToOne
    @JoinColumn(name = "COLUMN_1", insertable = false, updatable = false)
    //@ManyToOne(optional = true, fetch = FetchType.LAZY)
    //@JoinTable(name = "KEY_MAPPING", 
    //        joinColumns = { @JoinColumn(name = "J_COLUMN_1", referencedColumnName = "COLUMN_1", insertable = false, updatable = false) } )
    private TableA tableA;

    @OneToMany(mappedBy="tableA", fetch = FetchType.LAZY)
    private Set<TableA> tableASet;

And IdClass looks like:

public class TableAPk implements Serializable {
    // default serial version id, required for serializable classes.
    /** The Constant serialVersionUID. */
    private static final long serialVersionUID = 1L;

    private String            column_1;

    private BigDecimal        column_2;

As per my business logic I need to add self join only on single column

final Join<TableA, TableA> joinASelf = joinX.join("tableA", JoinType.INNER);

But table has composite primary key, so more than one fields are annotated with @Id. And I get exception like:

Caused by: org.hibernate.AnnotationException: A Foreign key refering com.data.TableA from com.data.TableA has the wrong number of column. should be 2.

How do i add self join only on single column here? I am new to JPA, so please let me know if I missed anything.

Update 21 Feb 2015: I added annotation @AssociationOverride to override associations:

@ManyToOne
@AssociationOverride(name="tableA", 
        joinColumns=@JoinColumn(name="COLUMN_1"))
private TableA tableA;

Generated column name is shown as "TABLE_A_COLUMN_2". I am not able to find out why. Any clue?

Upvotes: 4

Views: 6862

Answers (1)

V G
V G

Reputation: 19002

  1. The correct way would be (not what OP wants)

You could use@JoinColumns annotation (note the plural instead of the singular), thus joining the :

@Entity
@Table(name = "TABLE_A")
@IdClass(TableAPk.class)
public class TableA implements Serializable {

    private static final long serialVersionUID = 1L;


    @Id
    @Column(name = "COLUMN_1", nullable = false, length = 64)
    private String            column1;

    @Id
    @Column(name = "COLUMN_2", nullable = false, precision = 10, scale = 2)
    private BigDecimal        column2;


    @ManyToOne
    @JoinColumns({
        @JoinColumn(name = "FK_COL1", referencedColumnName="COLUMN_1"),
        @JoinColumn(name = "FK_COL2", referencedColumnName="COLUMN_2")
    })
    private TableA tableA;

    @OneToMany(mappedBy="tableA", fetch = FetchType.LAZY)
    private Set<TableA> tableASet;
  1. The solution to the problem:

Remove those @ManyToOne/@OneToMany relationships and add a single private String refCol1;. With that, write the corresponding Criteria Query for the JPQL: SELECT t1 FROM TableA t1, TableA t2 WHERE t2.refCol1 = t1.column1

With CriteriyQuqeries the solution looks like:

final CriteriaBuilder criteriaBuilder = entityManagerMds.getCriteriaBuilder();
// This Pojo is used to fetch only selected fields
final CriteriaQuery<DummyPojo> createQuery = criteriaBuilder.createQuery(DummyPojo.class);

final Root<TableX> tableX = createQuery.from(TableX.class);
final Join<TableX, TableA> joinTableA = tableX.join("tableAs", JoinType.INNER);


// Every time you want to add a self join, create new root
final Root<TableA> tableA = createQuery.from(TableA.class);
final Predicate predicateSelfJoin = criteriaBuilder.equal(joinTableA.<String>get("column_1"), tableA.<String>get("column_1"));

Upvotes: 7

Related Questions