Skip to main content

@many-to-one with composite key for multiple columns challenge..

6 replies [Last post]
buraluit
Offline
Joined: 2007-04-19

Hi,

I am new the JPA and wondering how to solve this problem:

Thanks in advance.

buraluit

Here is the scenario:

I have following tables:

1. Table SETUP_TYPE
VPD_ID NUMBER DEFAULT 0 NOT NULL,
SETUP_TYPE VARCHAR2(25) NOT NULL,
DESCRIPTION VARCHAR2(40) NOT NULL,

(Primary Key is SETUP_TYPE + VPD_ID)

2. Table TEST (PK is ID)
Columns:
1) ID
2) VPD_ID
3) SPEC_SETUP_TYPE1 VARCHAR2(25) (FK to SETUP_TYPE on SPEC_SETUP_TYPE1 + VPD_ID)
4) SPEC_SETUP_TYPE2 VARCHAR2(25) (FK to SETUP_TYPE on SPEC_SETUP_TYPE2 + VPD_ID)

Here is the Entity:

package test.entity;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinColumns;
import javax.persistence.ManyToOne;
import javax.persistence.Table;

import com.famis.ejb3.entity.core.SetupTypeEntity;

@SuppressWarnings("serial")
@Entity(name = "Test")
@Table(name = "TEST")
public class TestEntity implements Serializable {

private Long id;
private Long vpdId;
private SetupTypeEntity specSetupType1;
private SetupTypeEntity specSetupType2;

public TestEntity() {
}

@Id
@Column(name = "ID")
public Long getId() {
return id;
}

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

@Column(name = "VPD_ID", insertable=false, updatable=false)
public Long getVpdId() {
return vpdId;
}

public void setVpdId(Long value) {
this.vpdId = value;
}

/*
* Following are the two columns in the database table which has a FK to another table name SETUP_TYPE
* whose primary key is a copmosite key as SETUP_TYPE + VPD_ID
*/

@ManyToOne
@JoinColumns( {
@JoinColumn(name="SPEC_SETUP_TYPE01", referencedColumnName="SETUP_TYPE"),
@JoinColumn(name="VPD_ID", referencedColumnName="VPD_ID", insertable=false, updatable=false)
})
public SetupTypeEntity getSpecSetupType1() {
return specSetupType1;
}

public void setSpecSetupType1(SetupTypeEntity data) {
specSetupType1 = data;
}

@ManyToOne
@JoinColumns( {
@JoinColumn(name="SPEC_SETUP_TYPE02", referencedColumnName="SETUP_TYPE"),
@JoinColumn(name="VPD_ID", referencedColumnName="VPD_ID", insertable=false, updatable=false)
})
public SetupTypeEntity getSpecSetupType2() {
return specSetupType2;
}

public void setSpecSetupType2(SetupTypeEntity data) {
this.specSetupType2 = data;
}
}

Problems:

When I load up the data and try to create it:

1. This entity doesn

Reply viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
mf125085
Offline
Joined: 2005-03-29

> mf125085: I didn't quite understand what you meant by
> "Meaning that updating one relationship could
> possibly
> modify another relationship mapped to the same
> column".

For example:

EntityManager em = ....;
TestEntity t = new TestEntity(1);
SetupTypeEntity s1 = new SetupTypeEntity(1, 1);
SetupTypeEntity s2 = new SetupTypeEntity(2, 2);

em.getTransaction().begin();
t.setSpecSetupType1(s1);
t.setSpecSetupType2(s2);
em.persist(t);
em.getTransaction().commit();

The numbers in the constructors stand for different primary keys, this way setting conflicting values for the column TESTENTITY.VPD_ID. How would you ensure, the vpdId's for both relationships are always the same?

buraluit
Offline
Joined: 2007-04-19

In that context it makes sense! Thanks a lot.

I just wish that JPA Impl takes care of the data consistency and error out whenever I try to add wrong data. Of course I also understand it could lead to more complicated things like participating column could be nullable, then what should JPA do etc..

Thanks again.

buraluit.

Markus Fuchs

Hi buraluit,

I'm afraid you can't map TEST.VPD_ID to two different relationships. Can
you change the TEST table as in:

Table TEST (PK is ID)
Columns:
1) ID
2) VPD_ID1
3) SPEC_SETUP_TYPE1 VARCHAR2(25) (FK to SETUP_TYPE on SPEC_SETUP_TYPE1 + VPD_ID1)
2) VPD_ID2
4) SPEC_SETUP_TYPE2 VARCHAR2(25) (FK to SETUP_TYPE on SPEC_SETUP_TYPE2 + VPD_ID2)

Then map the relationship to the two non-overlapping column pairs. You
can use your application logic to ensure that VPD_ID1 and VPD_ID2 always
have the same value, e.g.:

public void setSpecSetupType1(SetupTypeEntity data) {

if (specSetupType2 != null && data != null) {
if (specSetupType2.getVpdId() == data.getVpdId()) {
specSetupType1 = data;
} else {
// throw Exception
}
}
}

... similar for setSpecSetupType2.

HTH,

-- markus.

glassfish@javadesktop.org wrote:
> Hi,
>
> I am new the JPA and wondering how to solve this problem:
>
> Thanks in advance.
>
> buraluit
>
> Here is the scenario:
>
> I have following tables:
>
> 1. Table SETUP_TYPE
> VPD_ID NUMBER DEFAULT 0 NOT NULL,
> SETUP_TYPE VARCHAR2(25) NOT NULL,
> DESCRIPTION VARCHAR2(40) NOT NULL,
>
> (Primary Key is SETUP_TYPE + VPD_ID)
>
> 2. Table TEST (PK is ID)
> Columns:
> 1) ID
> 2) VPD_ID
> 3) SPEC_SETUP_TYPE1 VARCHAR2(25) (FK to SETUP_TYPE on SPEC_SETUP_TYPE1 + VPD_ID)
> 4) SPEC_SETUP_TYPE2 VARCHAR2(25) (FK to SETUP_TYPE on SPEC_SETUP_TYPE2 + VPD_ID)
>
>
> Here is the Entity:
>
> package test.entity;
>
> import java.io.Serializable;
>
> import javax.persistence.Column;
> import javax.persistence.Entity;
> import javax.persistence.Id;
> import javax.persistence.JoinColumn;
> import javax.persistence.JoinColumns;
> import javax.persistence.ManyToOne;
> import javax.persistence.Table;
>
> import com.famis.ejb3.entity.core.SetupTypeEntity;
>
> @SuppressWarnings("serial")
> @Entity(name = "Test")
> @Table(name = "TEST")
> public class TestEntity implements Serializable {
>
> private Long id;
> private Long vpdId;
> private SetupTypeEntity specSetupType1;
> private SetupTypeEntity specSetupType2;
>
> public TestEntity() {
> }
>
> @Id
> @Column(name = "ID")
> public Long getId() {
> return id;
> }
>
> public void setId(Long value) {
> this.id = value;
> }
>
> @Column(name = "VPD_ID", insertable=false, updatable=false)
> public Long getVpdId() {
> return vpdId;
> }
>
> public void setVpdId(Long value) {
> this.vpdId = value;
> }
>
> /*
> * Following are the two columns in the database table which has a FK to another table name SETUP_TYPE
> * whose primary key is a copmosite key as SETUP_TYPE + VPD_ID
> */
>
> @ManyToOne
> @JoinColumns( {
> @JoinColumn(name="SPEC_SETUP_TYPE01", referencedColumnName="SETUP_TYPE"),
> @JoinColumn(name="VPD_ID", referencedColumnName="VPD_ID", insertable=false, updatable=false)
> })
> public SetupTypeEntity getSpecSetupType1() {
> return specSetupType1;
> }
>
> public void setSpecSetupType1(SetupTypeEntity data) {
> specSetupType1 = data;
> }
>
> @ManyToOne
> @JoinColumns( {
> @JoinColumn(name="SPEC_SETUP_TYPE02", referencedColumnName="SETUP_TYPE"),
> @JoinColumn(name="VPD_ID", referencedColumnName="VPD_ID", insertable=false, updatable=false)
> })
> public SetupTypeEntity getSpecSetupType2() {
> return specSetupType2;
> }
>
> public void setSpecSetupType2(SetupTypeEntity data) {
> this.specSetupType2 = data;
> }
> }
>
> Problems:
>
> When I load up the data and try to create it:
>
> 1. This entity doesn’t insert any of SPEC_SETUP_TYPEs because of the insertable=false.
> 2. And if I take off the insertable=false then Toplink complains:
> [TEST.VPD_ID]. Only one may be defined as writable, all others must be specified read-only.
>
> This seems to me something very simple but I am not able to figure this out as a beginner.
> [Message sent by forum member 'buraluit' (buraluit)]
>
> http://forums.java.net/jive/thread.jspa?messageID=213276
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@glassfish.dev.java.net
> For additional commands, e-mail: users-help@glassfish.dev.java.net
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@glassfish.dev.java.net
For additional commands, e-mail: users-help@glassfish.dev.java.net

mf125085
Offline
Joined: 2005-03-29

The a database column (like TEST.VPD_ID) can not be mapped to two different relationships, because setting one relationship would (over-)write half of the foreign key of the other one. Meaning that updating one relationship could possibly modify another relationship mapped to the same column. JPA does not support this scenario.

buraluit
Offline
Joined: 2007-04-19

Thanks for the responses.

Marcus: The solution you provided is an alternative but it's not something we'd
like to do at this point.

mf125085: I didn't quite understand what you meant by "Meaning that updating one relationship could possibly
modify another relationship mapped to the same column".

Both relating columns are different (physically as well as in the entity definition) only common thing is the VPD_ID in the relationship.
so if I update the first one based on its value + vpdid I don't see why it will confuse with the
second column. It would have been all fine if I had a single-column PK on the other table.

But anyways I would like to discuss this situation a bit more because after
working on it for a while I have a feeling that it might be a bug
(I may be wrong though) in Toplink (no sure about Hibernate). I couldn't
find anything in the JPA Final Spec stating that this is not allowed, or maybe I don't know what I am looking for. I even went
thru Gavin King's book on JP with Hib and it's not mentioned there as well.

Here is a detail analyses to prove my claim about the bug:

1. The cut down version of the TestEntity

//This will make sure that Toplink includes the VPD_ID in the INSERT Statement
@Column(name="VPD_ID")
public Long getVpdId() {
return itemCategory.getVpdId();
}

@ManyToOne
@JoinColumns( {
@JoinColumn(name="SPEC_SETUP_TYPE01", referencedColumnName="SETUP_TYPE")
, @JoinColumn(name="VPD_ID", referencedColumnName="VPD_ID", insertable=false, updatable=false)
} )
public SetupTypeEntity getSpecSetupType1()
{
.......
}

@ManyToOne
@JoinColumns( {
@JoinColumn(name="SPEC_SETUP_TYPE02", referencedColumnName="SETUP_TYPE")
, @JoinColumn(name="VPD_ID", referencedColumnName="VPD_ID", insertable=false, updatable=false)
} )
public SetupTypeEntity getSpecSetupType2()
{
.......
}

- In this state there is no complaint from Toplink and the it inserts a new record. But if we
pass any one or both SpecSetupTypes then there values don't get inserted.

2. Here is another cut down version of the TestEntity

//This will make sure that Toplink doesn't include the VPD_ID in the INSERT Statement using this annotation
@Column(name="VPD_ID", insertable=false, updatable=false)
public Long getVpdId() {
return itemCategory.getVpdId();
}

//Notice that VPD_ID doesn't have insertable=false.... and toplink uses this
//annotation to place the VPD_ID into the insert statement.
@ManyToOne
@JoinColumns( {
@JoinColumn(name="SPEC_SETUP_TYPE01", referencedColumnName="SETUP_TYPE")
, @JoinColumn(name="VPD_ID", referencedColumnName="VPD_ID")
} )
public SetupTypeEntity getSpecSetupType1()
{
.......
}

@ManyToOne
@JoinColumns( {
@JoinColumn(name="SPEC_SETUP_TYPE02", referencedColumnName="SETUP_TYPE")
, @JoinColumn(name="VPD_ID", referencedColumnName="VPD_ID", insertable=false, updatable=false)
} )
public SetupTypeEntity getSpecSetupType2()
{
.......
}

- In this state also it inserts a new record without any problem. It also
inserts the value for the SpecSetupType01. In the generated Insert statement VPD_ID is adjacent to the SPEC_SETUP_TYPE01 which
made me thinking that Toplink is generating the insert statements based on the annotations.

And this is why I feel that Toplink has this bug because in the first case it should have just included
the SPEC_SETUP_TYPE01 and SPEC_SETUP_TYPE02 columns in the insert statement because they are
not marked read-only.

Well, the message became too long I hope you have enough patience

Thanks a lot for reading.

buraluit

buraluit
Offline
Joined: 2007-04-19

Well, hibernate even doesn't allow me to have a relationship defined the way I tried with toplink:

Here is the exception

javax.persistence.PersistenceException: org.hibernate.AnnotationException: Mixing insertable and non insertable columns in a property is not allowed:

I will just say JPA should have this feature because it's a valid use case.

Thanks a lot for going thru to my post.. it's 3.37AM.. I'm going to hit the sack.

Buraluit.