JoinColumn name not used in sql

[vld <kn...@yahoo.com>]

Hi!

I have a problem with mapping many-to-one relationship without exact foreign
key constraint set in database. I use OpenJPA implementation with MySql
database, but the problem is with generated sql scripts for insert and
select statements.

I have LegalEntity table which contains RootId column (among others). I also
have Address table which has LegalEntityId column which is not nullable, and
which should contain values referencing LegalEntity's "RootId" column, but
without any database constraint (foreign key) set.

Address entity is mapped:
@Entity
@Table(name="address")
public class Address implements Serializable {
    ...

    @ManyToOne(fetch=FetchType.LAZY, optional=false)
    @JoinColumn(referencedColumnName="RootId", name="LegalEntityId",
nullable=false, insertable=true, updatable=true, table="LegalEntity")
    public LegalEntity getLegalEntity() {
        return this.legalEntity;
    }
}

SELECT statement (when fetching LegalEntity's addresses) and INSERT statment
are generated:
- SELECT t0.Id, .., t0.LEGALENTITY_ID FROM address t0 WHERE
t0.LEGALENTITY_ID = ? ORDER BY t0.Id DESC [params=(int) 2]
- INSERT INTO address (..., LEGALENTITY_ID) VALUES (..., ?) [params=...,
(int) 2]

If I omit table attribute from mentioned statements are generated:
- SELECT t0.Id, ... FROM address t0 INNER JOIN legalentity t1 ON
t0.LegalEntityId = t1.RootId WHERE t1.Id = ? ORDER BY t0.Id DESC
[params=(int) 2]
- INSERT INTO address (...) VALUES (...) [params=...]

So, LegalEntityId is not included in any of the statements.

Is it possible to have relationship based on such referencing (to column
other than primary key, without foreign key in database)? Is there something
else missing?

Thanks in advance.
-- 
View this message in context: http://openjpa.208410.n2.nabble.com/JoinColumn-name-not-used-in-sql-tp4974728p4974728.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: JoinColumn name not used in sql

[vld <kn...@yahoo.com>]

Thank you for your reply. It turned out that I had corrupted data and
LegalEntity's RootId was null, so insert script ignored null value and it
never showed.
-- 
View this message in context: http://openjpa.208410.n2.nabble.com/JoinColumn-name-not-used-in-sql-tp4974728p4986303.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: JoinColumn name not used in sql

[Pinaki Poddar <pp...@apache.org>]

Hi,
  > Is it possible to have relationship based on such referencing (to column
other than primary key, without   
  > foreign key in database)? Is there something else missing? 

OpenJPA's support for non-standard joins and associated syntax [1].


[1]
http://openjpa.apache.org/builds/latest/docs/manual/manual.html#ref_guide_mapping_notes_nonstdjoins


-----
Pinaki 
-- 
View this message in context: http://openjpa.208410.n2.nabble.com/JoinColumn-name-not-used-in-sql-tp4974728p4982542.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: JoinColumn name not used in sql

[vld <kn...@yahoo.com>]

I changed enhancement, but I still get same results.

Is there anything else I should do?
-- 
View this message in context: http://openjpa.208410.n2.nabble.com/JoinColumn-name-not-used-in-sql-tp4974728p4980541.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: JoinColumn name not used in sql

[Vásáry, Dániel <va...@elgekko.net>]

Hi,
I had same problem (and other strange behaviour) when I used Eclipse plugin
based ByteCode enhancement.
Enhanced OpenJpa recognized for me foreign keys (in many-to-one
relationships) only when entity names followed each other in alphabetical
way.
For example Store -> StoreUri (which contains foreign key to Store) worked
fine, but
MetadataType -> MetadataElement not.

My problem solved with switching to Ant based enhancement implementation.
All problems gone.


2010/4/28 vld <kn...@yahoo.com>

>
> Hi!
>
> I have a problem with mapping many-to-one relationship without exact
> foreign
> key constraint set in database. I use OpenJPA implementation with MySql
> database, but the problem is with generated sql scripts for insert and
> select statements.
>
> I have LegalEntity table which contains RootId column (among others). I
> also
> have Address table which has LegalEntityId column which is not nullable,
> and
> which should contain values referencing LegalEntity's "RootId" column, but
> without any database constraint (foreign key) set.
>
> Address entity is mapped:
> @Entity
> @Table(name="address")
> public class Address implements Serializable {
>    ...
>
>    @ManyToOne(fetch=FetchType.LAZY, optional=false)
>    @JoinColumn(referencedColumnName="RootId", name="LegalEntityId",
> nullable=false, insertable=true, updatable=true, table="LegalEntity")
>    public LegalEntity getLegalEntity() {
>        return this.legalEntity;
>    }
> }
>
> SELECT statement (when fetching LegalEntity's addresses) and INSERT
> statment
> are generated:
> - SELECT t0.Id, .., t0.LEGALENTITY_ID FROM address t0 WHERE
> t0.LEGALENTITY_ID = ? ORDER BY t0.Id DESC [params=(int) 2]
> - INSERT INTO address (..., LEGALENTITY_ID) VALUES (..., ?) [params=...,
> (int) 2]
>
> If I omit table attribute from mentioned statements are generated:
> - SELECT t0.Id, ... FROM address t0 INNER JOIN legalentity t1 ON
> t0.LegalEntityId = t1.RootId WHERE t1.Id = ? ORDER BY t0.Id DESC
> [params=(int) 2]
> - INSERT INTO address (...) VALUES (...) [params=...]
>
> So, LegalEntityId is not included in any of the statements.
>
> Is it possible to have relationship based on such referencing (to column
> other than primary key, without foreign key in database)? Is there
> something
> else missing?
>
> Thanks in advance.
> --
> View this message in context:
> http://openjpa.208410.n2.nabble.com/JoinColumn-name-not-used-in-sql-tp4974728p4974728.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>