From: Markus Fuchs <Markus.Fuchs_at_Sun.COM>
Date: Fri, 23 Mar 2007 12:42:11 -0700

Hi,

You're correct the generated SQL misses the second part of the composite pk for joining Project's primary and secondary table. Your mapping is correct. This problem has not been logged before. Please file a glassfish issue at

https://glassfish.dev.java.net/issues/enter_bug.cgi

It would be great if you also could attach your test case.

Thanks!

-- markus.

glassfish@javadesktop.org wrote:

Hi,

   i am using toplink essentials (Build 060829) and the latest build(V2_build_39)

   i found toplink essentials generate incorrect SQL under the following situation:

@Entity
@Table(name="project")
@IdClass(xxx.class)
@SecondaryTables
(
{ @SecondaryTable(name="project_detail1", 
                    pkJoinColumns={
                        @PrimaryKeyJoinColumn(name="project_id",referencedColumnName="project_id"),
@PrimaryKeyJoinColumn(name="dept_id", referencedColumnName="dept_id")

})
class Project
{
       @Id
     @Column(name="project_id")
       String projectID;

       @Id
       @Column(name="dept_id")
       String deptID;

       @Column(table="project_detail1", name="name")
        String name;       
.....
.....
 }

@Entity
@Table(name="audit_log")
class AuditLog
{
     @Id
     @Column(name="log_id")
     int logID;
   
     @Column(name="project_id")
     String projectID;

     @Column(name="dept_id")
     String deptID; 

    @ManyToOne
    @JoinColumns
    (
@JoinColumn(name="project_id", referencedColumnName="project_id", insertable=false, updatable=false),
@JoinColumn(name="dept_id", referencedColumnName="dept_id", insertable=false, updatable=false)
    )
    Project relatedProject;
.....
.....
}

****the entity Project is consist of data from 2 tables: "project" and "project_detail1"
****the primary key is (dept_id, project_id)


***the entity AuditLog is consist of data from one table only: audit_log
***AuditLog.relatedProject point to the Project, being log, by joining the dept_id and ***project_id 

now if i issue a left join query as the following:

select al from AuditLog al left join al.relatedProject

toplink generate incorrect SQL:

select t0.log_id, t0.project_id, t0.dept_id, t1.dept_id, t2.name

from audit_log t0 left outer join 

(project t1 join project_detail1 t2 on [b](t1.dept_id = t2.dept_id)[/b])  

on ((t0.project_id = t1.project_id) and (t0.dept_id = t1.dept_id))


  

problem --- joining table project and project_id should use 2 columns (dept_id, project_id)
but the SQL generated use only one, not the complete composite primary key !!
            

the SQL should be:

select t0.log_id, t0.project_id, t0.dept_id, t1.dept_id, t2.name

from audit_log t0 left outer join 

(project t1 join project_detail1 t2 on [b]((t1.dept_id = t2.dept_id) and (t1.project_id = t2.project_id))[/b])   /* join using 2 columns*/

on ((t0.project_id = t1.project_id) and (t0.dept_id = t1.dept_id))

is this a know bug ? (if yes, any workaround)
 or 
somebody know if there is something wrong in entity setting / configuration?



thank you!!
[Message sent by forum member 'imperfect' (imperfect)]

http://forums.java.net/jive/thread.jspa?messageID=209136

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