You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by op...@reider.net on 2010/02/25 03:52:30 UTC
JPA adding class name to join column name
I'm having an issue with JPA generating column names containing Java
class names, with some mix of
@Inheritance(strategy=InheritanceType.SINGLE_TABLE and
@SecondaryTables. This results in an error because of a non existent
column:
<openjpa-1.2.2-SNAPSHOT-r422266:821449 nonfatal general error>
org.apache.openjpa.persistence.PersistenceException: DB2 SQL error:
SQLCODE: -206, SQLSTATE: 42703, SQLERRMC: T1.VALUEDCUSTOMER_ID
1607819221 SELECT ... FROM CUSTOMERRATING t0 INNER JOIN CUSTOMER t1 on
t0.ID = t1.VALUEDCUSTOMER_ID INNER JOIN GREATCUSTOMERDATA t2 ON t0.id
= t2.id ...
ValuedCustomer is the class of one of the Java entities on the table.
Why would JPA prefix the column name with the class name
(VALUEDCUSTOMER_ID)? The column name is just ID. The error goes away
when removing the name field from ValuedCustomer class. It can be
undesirable to have to define all columns in the root class. I did try
copying down the @SecondaryTables annotation but it didnt help.
This entity is being migrated from TOPLink (11g) which does not have
this issue. (side problem: why does JPA insist the discriminator
column be in the primary table and has this been addressed in later
versions? I had to swap CUSTOMERRATING and CUSTOMER to make JPA happy
(arguably, bad legacy table design to begin with).
The configuration follows. The above error occurs for a find() for a
row that maps to GREATCUSTOMER (not VALUEDCUSTOMER, which makes it
even more insteresting).
Hopefully i haven't mangled the example while substituting fake names...
//CLASS 1
@Entity
@Table(name="CUSTOMERRATING")
@Inheritance(strategy=InheritanceType.SINGLE_TABLE)
// in the real code, pkJoinColumns needed...
@SecondaryTables({
@SecondaryTable(name="CUSTOMER",pkJoinColumns = @PrimaryKeyJoinColumn
(name="ID" , referencedColumnName="ID"))
,@SecondaryTable(name="MORECUSTOMERDATA",pkJoinColumns = @PrimaryKeyJoinColumn(
name="ID" , referencedColumnName="ID"))
@DiscriminatorColumn(name="RATING")
public class Customer extends EntityImpl implements Serializable {
@Column(name="ID")
@Id
protected String id;
@Column(table="CUSTOMERRATING",name="RATING")
private String rating;
@Column(table="CUSTOMER",name="NAME")
private String name;
//Class 2
@Entity
@DiscriminatorValue(value="2")
public class ValuedCustomer extends Customer implements Serializable {
@Column(table="CUSTOMER",name="NAME")
private String name;}
// Class 3
@Entity
@SecondaryTable(
name="GREATCUSTOMERDATA"
,pkJoinColumns = @PrimaryKeyJoinColumn(
name="ID"
, referencedColumnName="ID"))
@DiscriminatorValue(value="9")
public class GreatCustomer extends ValuedCustomer implements Serializable {
@Column(table="GREATCUSTOMERDATA",name="ID")
private String greatCustomerdataId;
RE: JPA adding class name to join column name
Posted by Tradingfours <na...@reider.net>.
typos corrected:
If a subclass of an entity using SINGLE_TABLE inheritance and which
specifies @SecondaryTable(SEC_TBL), contains a
@Column(table="SEC_TBL",name="...") then a find(subclass.class,pk) results
in
'INNER JOIN PRI_TABLE on PRI_TABLE.id = SEC_TBL.subclass_ID'
where 'subclass_ID' is not in the table, and should simply be 'ID'.
For example:
@Entity
@Table(name="CUSTOMERTYPE")
@Inheritance(strategy=InheritanceType.SINGLE_TABLE)
@SecondaryTable(name="CUSTOMERDATA",pkJoinColumns = @PrimaryKeyJoinColumn
(name="ID" , referencedColumnName="ID"))
@DiscriminatorColumn(name="ROLE",length=4)
public class CustomerClass extends EntityImpl implements Serializable {
@Id
@Column(name="ID")
protected String id;
}
@Entity
@DiscriminatorValue(value="0010")
public class ValuedCustomerClass extends CustomerClass implements
Serializable {
@Column(table="CUSTOMERDATA", name="NAME")
private String name;
}
find(CustomerClass.class,aValuedCustomerPK)
<openjpa-1.2.2-SNAPSHOT-r422266:821449 nonfatal general error>
org.apache.openjpa.persistence.PersistenceException: DB2 SQL error: SQLCODE:
-206, SQLSTATE: 42703,
SQLERRMC: T1.VALUEDCUSTOMERCLASS_ID
SELECT t0.ROLE, t1.NAME
FROM CUSTOMERTYPE t0
INNER JOIN CUSTOMERDATA t1 ON t0.ID = t1.VALUEDCUSTOMERCLASS_ID <<<
should be t1.ID
--
View this message in context: http://n2.nabble.com/JPA-adding-class-name-to-join-column-name-tp4630454p4642827.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
RE: JPA adding class name to join column name
Posted by Tradingfours <na...@reider.net>.
Thanks crispyoz.
I think I've whittled this down to a decent test case. The issue appears to
be:
If a subclass of an entity using SINGLE_TABLE inheritance and which
specifies @SecondaryTable(SEC_TBL), contains a
@Column(table="SEC_TBL",name="...") then a find(subclass.class,pk) results
in
'INNER JOIN PRI_TABLE on PRI_TABLE.id = subclass_ID'
where 'subclass_ID' is not in the table, and should simply be 'ID'.
For example:
@Entity
@Table(name="CUSTOMERTYPE")
@Inheritance(strategy=InheritanceType.SINGLE_TABLE)
@SecondaryTable(name="CUSTOMERDATA",pkJoinColumns = @PrimaryKeyJoinColumn
(name="ID", referencedColumnName="ID"))
@DiscriminatorColumn(name="ROLE",length=4)
public class CustomerClass extends EntityImpl implements Serializable {
@Id
@Column(name="ID")
protected String id;
}
//***************************
@Entity
@DiscriminatorValue(value="0010")
public class ValuedCustomerClass extends Customer implements Serializable {
@Column(table="CUSTOMERDATA",name="NAME")
private String name;
}
find(Customer.class,aValuedCustomerPK)
<openjpa-1.2.2-SNAPSHOT-r422266:821449 nonfatal general error>
org.apache.openjpa.persistence.PersistenceException: DB2 SQL error: SQLCODE:
-206, SQLSTATE: 42703,
SQLERRMC: T1.CUSTOMER_ID
SELECT t0.ROLE, t1.NAME
FROM CUSTOMERTYPE t0
INNER JOIN CUSTOMERDATA t1 ON t0.ID = t1.CUSTOMERCLASS_ID <<< should be
t1.ID
--
View this message in context: http://n2.nabble.com/JPA-adding-class-name-to-join-column-name-tp4630454p4640216.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
RE: JPA adding class name to join column name
Posted by C N Davies <cn...@cndavies.com>.
OpenJPA does use the class name as a prefix for columns, for example if I
have a many to one relationship and I am using TablePerClass, then the
column name for the external reference is named like this:
ReferenceClass_key
For example in my "Asset" entity I have a reference to a "Contract" entity:
/** The contract. */
@ManyToOne(cascade={CascadeType.REFRESH})
private Contract contract;
This results in my Asset table having column named "contract_uniqueid"
because the field "uniqueid" is the primary key of my "Contract" entity.
Hope that makes sense.
Chris
-----Original Message-----
From: Tradingfours [mailto:nabble@reider.net]
Sent: Friday, 26 February 2010 7:30 PM
To: users@openjpa.apache.org
Subject: Re: JPA adding class name to join column name
Thanks for taking the time to look at my example. I did try your suggestions
and they didn't resolve the issue. Since the example seems to have some
non-essential-to-the-problem definitions in it, I tried paring it down by
removing the interior class from the entity hierarchy and some other parts
of the entity, after which I CNR. So I started mapping the entities as per
my problem domain, and eventually I hit the issue again, this time on the
leaf entity, but again the entity is rather complex for a demo or test case.
Part of the problem is that I am constrained to make openjpa compatible with
an existing legacy table, rather than being able to make any change to the
table to conform to openjpa,
It would really be helpful for me in zeroing in on this, if I could know
understand the answer to the following 2 precise questions
1) Does openjpa EVER, under any circumstances,prefix "ENTITYCLASSNAME_" to a
column name, eg t1.MYCLASSNAME_MYFIELD? I have not seen anything in the doc
that talks about that.
If the answer is no, then knowing this is a bug, I'll make a proper test
case. If the answer is yes, then
2) WHY and under what circumstances, would openjpa be motivated to insert
entityclassname_ into a column name? Understaning that would probalby
pinpoint the part of my configuration that is causing it.
--
View this message in context:
http://n2.nabble.com/JPA-adding-class-name-to-join-column-name-tp4630454p463
7794.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: JPA adding class name to join column name
Posted by Tradingfours <na...@reider.net>.
Thanks for taking the time to look at my example. I did try your suggestions
and they didn't resolve the issue. Since the example seems to have some
non-essential-to-the-problem definitions in it, I tried paring it down by
removing the interior class from the entity hierarchy and some other parts
of the entity, after which I CNR. So I started mapping the entities as per
my problem domain, and eventually I hit the issue again, this time on the
leaf entity, but again the entity is rather complex for a demo or test case.
Part of the problem is that I am constrained to make openjpa compatible with
an existing legacy table, rather than being able to make any change to the
table to conform to openjpa,
It would really be helpful for me in zeroing in on this, if I could know
understand the answer to the following 2 precise questions
1) Does openjpa EVER, under any circumstances,prefix "ENTITYCLASSNAME_" to a
column name, eg t1.MYCLASSNAME_MYFIELD? I have not seen anything in the doc
that talks about that.
If the answer is no, then knowing this is a bug, I'll make a proper test
case. If the answer is yes, then
2) WHY and under what circumstances, would openjpa be motivated to insert
entityclassname_ into a column name? Understaning that would probalby
pinpoint the part of my configuration that is causing it.
--
View this message in context: http://n2.nabble.com/JPA-adding-class-name-to-join-column-name-tp4630454p4637794.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: JPA adding class name to join column name
Posted by Fay Wang <fy...@yahoo.com>.
Hi,
I just realized that you mentioned removing name field will make the problem go away, and it is undesirable to define all columns in the root class. Can you try the following ValuedCustomer definition? I added a new field "age" to this class, and also added the SecondaryTable annotation to it:
@Entity
@SecondaryTable(
name="CUSTOMER",
pkJoinColumns = @PrimaryKeyJoinColumn(
name="ID",
referencedColumnName="ID"))
@DiscriminatorValue(value="2")
public class ValuedCustomer extends Customer implements Serializable {
@Column(table="CUSTOMER",name="AGE")
private int age;
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
}
I also modified your Customer.class by taking out the rating field. You don't really need this rating field because your discriminator and the rating field map to the same column.
@Entity
@Table(name="CUSTOMERRATING")
@Inheritance(strategy=InheritanceType.SINGLE_TABLE)
@SecondaryTables({
@SecondaryTable(
name="CUSTOMER",
pkJoinColumns = @PrimaryKeyJoinColumn(name="ID", referencedColumnName="ID")),
@SecondaryTable(
name="MORECUSTOMERDATA",
pkJoinColumns = @PrimaryKeyJoinColumn(name="ID" , referencedColumnName="ID"))
})
@DiscriminatorColumn(name="RATING")
public class Customer implements Serializable {
@Column(name="ID")
@Id
protected String id;
@Column(table="CUSTOMER",name="NAME")
private String name;
Finally, in your GreatCustomer.java, again your greatCustomerdataId originally is mapped to the same ID column of the GREATCUSTOMERDATA. However, this ID column is supposed to be the join column, you should map it to a different column. Otherwise, this value can not be stored.
@Entity
@SecondaryTable(
name="GREATCUSTOMERDATA",
pkJoinColumns = @PrimaryKeyJoinColumn(
name="ID",
referencedColumnName="ID"))
@DiscriminatorValue(value="9")
public class GreatCustomer extends ValuedCustomer implements Serializable {
@Column(table="GREATCUSTOMERDATA",name="GC_ID")
private String greatCustomerdataId;
With these entity definitions, the tables created by openjpa are:
CREATE TABLE CUSTOMER (ID VARCHAR(254), NAME VARCHAR(254), AGE INTEGER)
CREATE TABLE CUSTOMERRATING (ID VARCHAR(254) NOT NULL, RATING VARCHAR(31), PRIMARY KEY (ID))
CREATE TABLE GREATCUSTOMERDATA (ID VARCHAR(254), GC_ID VARCHAR(254))
Fay
----- Original Message ----
From: Fay Wang <fy...@yahoo.com>
To: users@openjpa.apache.org
Sent: Thu, February 25, 2010 11:11:57 AM
Subject: Re: JPA adding class name to join column name
Hi,
I noticed that you have "name" field in your Customer.java and ValuedCustomer.java. This field in both classes is mapped to the same column in the same table. Since ValuedCustomer inherits from Customer, I am wondering why you need to have a separate "name" field in the ValuedCustomer. If you take out this field from the ValuedCustomer, this column VALUEDCUSTOMER_ID will not be generated.
Fay
----- Original Message ----
From: "openjpa@reider.net" <op...@reider.net>
To: users@openjpa.apache.org
Sent: Wed, February 24, 2010 6:52:30 PM
Subject: JPA adding class name to join column name
I'm having an issue with JPA generating column names containing Java
class names, with some mix of
@Inheritance(strategy=InheritanceType.SINGLE_TABLE and
@SecondaryTables. This results in an error because of a non existent
column:
<openjpa-1.2.2-SNAPSHOT-r422266:821449 nonfatal general error>
org.apache.openjpa.persistence.PersistenceException: DB2 SQL error:
SQLCODE: -206, SQLSTATE: 42703, SQLERRMC: T1.VALUEDCUSTOMER_ID
1607819221 SELECT ... FROM CUSTOMERRATING t0 INNER JOIN CUSTOMER t1 on
t0.ID = t1.VALUEDCUSTOMER_ID INNER JOIN GREATCUSTOMERDATA t2 ON t0.id
= t2.id ...
ValuedCustomer is the class of one of the Java entities on the table.
Why would JPA prefix the column name with the class name
(VALUEDCUSTOMER_ID)? The column name is just ID. The error goes away
when removing the name field from ValuedCustomer class. It can be
undesirable to have to define all columns in the root class. I did try
copying down the @SecondaryTables annotation but it didnt help.
This entity is being migrated from TOPLink (11g) which does not have
this issue. (side problem: why does JPA insist the discriminator
column be in the primary table and has this been addressed in later
versions? I had to swap CUSTOMERRATING and CUSTOMER to make JPA happy
(arguably, bad legacy table design to begin with).
The configuration follows. The above error occurs for a find() for a
row that maps to GREATCUSTOMER (not VALUEDCUSTOMER, which makes it
even more insteresting).
Hopefully i haven't mangled the example while substituting fake names...
//CLASS 1
@Entity
@Table(name="CUSTOMERRATING")
@Inheritance(strategy=InheritanceType.SINGLE_TABLE)
// in the real code, pkJoinColumns needed...
@SecondaryTables({
@SecondaryTable(name="CUSTOMER",pkJoinColumns = @PrimaryKeyJoinColumn
(name="ID" , referencedColumnName="ID"))
,@SecondaryTable(name="MORECUSTOMERDATA",pkJoinColumns = @PrimaryKeyJoinColumn(
name="ID" , referencedColumnName="ID"))
@DiscriminatorColumn(name="RATING")
public class Customer extends EntityImpl implements Serializable {
@Column(name="ID")
@Id
protected String id;
@Column(table="CUSTOMERRATING",name="RATING")
private String rating;
@Column(table="CUSTOMER",name="NAME")
private String name;
//Class 2
@Entity
@DiscriminatorValue(value="2")
public class ValuedCustomer extends Customer implements Serializable {
@Column(table="CUSTOMER",name="NAME")
private String name;}
// Class 3
@Entity
@SecondaryTable(
name="GREATCUSTOMERDATA"
,pkJoinColumns = @PrimaryKeyJoinColumn(
name="ID"
, referencedColumnName="ID"))
@DiscriminatorValue(value="9")
public class GreatCustomer extends ValuedCustomer implements Serializable {
@Column(table="GREATCUSTOMERDATA",name="ID")
private String greatCustomerdataId;
Re: JPA adding class name to join column name
Posted by Fay Wang <fy...@yahoo.com>.
Hi,
I noticed that you have "name" field in your Customer.java and ValuedCustomer.java. This field in both classes is mapped to the same column in the same table. Since ValuedCustomer inherits from Customer, I am wondering why you need to have a separate "name" field in the ValuedCustomer. If you take out this field from the ValuedCustomer, this column VALUEDCUSTOMER_ID will not be generated.
Fay
----- Original Message ----
From: "openjpa@reider.net" <op...@reider.net>
To: users@openjpa.apache.org
Sent: Wed, February 24, 2010 6:52:30 PM
Subject: JPA adding class name to join column name
I'm having an issue with JPA generating column names containing Java
class names, with some mix of
@Inheritance(strategy=InheritanceType.SINGLE_TABLE and
@SecondaryTables. This results in an error because of a non existent
column:
<openjpa-1.2.2-SNAPSHOT-r422266:821449 nonfatal general error>
org.apache.openjpa.persistence.PersistenceException: DB2 SQL error:
SQLCODE: -206, SQLSTATE: 42703, SQLERRMC: T1.VALUEDCUSTOMER_ID
1607819221 SELECT ... FROM CUSTOMERRATING t0 INNER JOIN CUSTOMER t1 on
t0.ID = t1.VALUEDCUSTOMER_ID INNER JOIN GREATCUSTOMERDATA t2 ON t0.id
= t2.id ...
ValuedCustomer is the class of one of the Java entities on the table.
Why would JPA prefix the column name with the class name
(VALUEDCUSTOMER_ID)? The column name is just ID. The error goes away
when removing the name field from ValuedCustomer class. It can be
undesirable to have to define all columns in the root class. I did try
copying down the @SecondaryTables annotation but it didnt help.
This entity is being migrated from TOPLink (11g) which does not have
this issue. (side problem: why does JPA insist the discriminator
column be in the primary table and has this been addressed in later
versions? I had to swap CUSTOMERRATING and CUSTOMER to make JPA happy
(arguably, bad legacy table design to begin with).
The configuration follows. The above error occurs for a find() for a
row that maps to GREATCUSTOMER (not VALUEDCUSTOMER, which makes it
even more insteresting).
Hopefully i haven't mangled the example while substituting fake names...
//CLASS 1
@Entity
@Table(name="CUSTOMERRATING")
@Inheritance(strategy=InheritanceType.SINGLE_TABLE)
// in the real code, pkJoinColumns needed...
@SecondaryTables({
@SecondaryTable(name="CUSTOMER",pkJoinColumns = @PrimaryKeyJoinColumn
(name="ID" , referencedColumnName="ID"))
,@SecondaryTable(name="MORECUSTOMERDATA",pkJoinColumns = @PrimaryKeyJoinColumn(
name="ID" , referencedColumnName="ID"))
@DiscriminatorColumn(name="RATING")
public class Customer extends EntityImpl implements Serializable {
@Column(name="ID")
@Id
protected String id;
@Column(table="CUSTOMERRATING",name="RATING")
private String rating;
@Column(table="CUSTOMER",name="NAME")
private String name;
//Class 2
@Entity
@DiscriminatorValue(value="2")
public class ValuedCustomer extends Customer implements Serializable {
@Column(table="CUSTOMER",name="NAME")
private String name;}
// Class 3
@Entity
@SecondaryTable(
name="GREATCUSTOMERDATA"
,pkJoinColumns = @PrimaryKeyJoinColumn(
name="ID"
, referencedColumnName="ID"))
@DiscriminatorValue(value="9")
public class GreatCustomer extends ValuedCustomer implements Serializable {
@Column(table="GREATCUSTOMERDATA",name="ID")
private String greatCustomerdataId;