You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by anyz <an...@gmail.com> on 2010/06/01 07:07:21 UTC
Re: Hibernate 3 Outer Join Problem
Thanks Brett for your time. Yes it is more of Hibernate issue i was just
trying to find if some of derby user encounter with this.
Here are tables structure and mapping.
CREATE TABLE PROJECT (
PROJECT_ID VARCHAR(50) NOT NULL,
PROJECT_NAME VARCHAR(150) NOT NULL,
PROJECT_MANAGER VARCHAR(150) NOT NULL,
START_DATE TIMESTAMP NOT NULL,
END_DATE TIMESTAMP NOT NULL,
PROGRAM_ID INTEGER,
PRIMARY KEY (PROJECT_ID)
);
ALTER TABLE PROJECT
ADD FOREIGN KEY (PROGRAM_ID)
REFERENCES PROGRAM (PROGRAM_ID);
CREATE TABLE PROGRAM (
PROGRAM_ID INTEGER DEFAULT AUTOINCREMENT: start 1 increment 1 NOT NULL,
PROGRAM_NAME VARCHAR(150) NOT NULL,
PROGRAM_MANAGER VARCHAR(150) NOT NULL,
PRIMARY KEY (PROGRAM_ID)
);
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD
3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="a.b.model.Project" table="PROJECT">
<id name="projectId" type="string">
<column name="PROJECT_ID" length="50" />
<generator class="assigned" />
</id>
<property name="projectName" type="string">
<column name="PROJECT_NAME" length="150" not-null="true" />
</property>
<property name="projectManager" type="string">
<column name="PROJECT_MANAGER" length="150" not-null="true" />
</property>
<property name="startDate" type="timestamp">
<column name="START_DATE" length="26" not-null="true" />
</property>
<property name="endDate" type="timestamp">
<column name="END_DATE" length="26" not-null="true" />
</property>
<property name="programId" type="java.lang.Integer">
<column name="PROGRAM_ID" />
</property>
</class>
</hibernate-mapping>
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD
3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="a.b.model.Program" table="PROGRAM">
<id name="programId" type="int">
<column name="PROGRAM_ID" />
<generator class="identity" />
</id>
<property name="programName" type="string">
<column name="PROGRAM_NAME" length="150" not-null="true" />
</property>
<property name="programManager" type="string">
<column name="PROGRAM_MANAGER" length="150" not-null="true" />
</property>
</class>
</hibernate-mapping>
Thanks once again.
On Mon, May 31, 2010 at 7:55 PM, Brett Wooldridge <
brett.wooldridge@gmail.com> wrote:
> When you say "Hibernate 3", what specific version are you talking about?
> While this is more of a Hibernate question than a Derby question, what is
> the structure of the two tables (for Project and Program) and what does the
> mapping look like?
>
> Brett
>
>
> On Mon, May 31, 2010 at 10:52 PM, anyz <an...@gmail.com> wrote:
>
>> Its seems to be hibernate issue. Dialect in hibernate 3 is not uptodate i
>> think.
>>
>> We tried it as below:
>>
>> SELECT proj, prog, FROM Project proj, Program prog
>>
>> but it didn't work.
>>
>>
>> On Mon, May 31, 2010 at 6:19 PM, Donald McLean <dm...@gmail.com>wrote:
>>
>>> Have you tried explicitly specifying the table? i.e. Project.proj
>>>
>>> On Mon, May 31, 2010 at 7:00 AM, anyz <an...@gmail.com> wrote:
>>> > i'm trying to use hibernate 3 with Derby 10.6. Normal queries work good
>>> but
>>> > using left outer join creates problem. The query i'm trying loads
>>> project
>>> > program if it exists and if programm not exists the project information
>>> is
>>> > loaded:
>>> >
>>> > SELECT proj, prog FROM Project
>>> > LEFT OUTER JOIN Program ON Program.programId = Project.programId
>>> > WHERE Project.projectName = '"MyProject"
>>> >
>>> > The error on executing query is :
>>> >
>>> > unexpected token: ON near line 1, column xxx [SELECT proj, prog, FROM
>>> > a.b.Project LEFT OUTER JOIN Program ON Program.programId =
>>> Project.programId
>>> > WHERE Project.projectName = 'MyProject' ]
>>> > at org.hibernate.hql.ast.QuerySyntax
>>> >
>>> > Could you please guid if Hibernate dialect support for Derbby is not
>>> that
>>> > good of i'm missing something.
>>> >
>>> > thanks
>>> >
>>>
>>>
>>>
>>> --
>>> Family photographs are a critical legacy for
>>> ourselves and our descendants. Protect that
>>> legacy with a digital backup and recovery plan.
>>>
>>> Join the photo preservation advocacy Facebook group:
>>> http://www.facebook.com/home.php?ref=logo#/group.php?gid=148274709288
>>>
>>
>>
>
Re: Hibernate 3 Outer Join Problem
Posted by Brett Wooldridge <br...@gmail.com>.
Hi anyz,
It seems to me that it is just luck that the mySQL dialect generated a
useable query. These are trivial tables (and I presume classes), but the
mapping seems incorrect to me. Without an explicit expresses relation
between Project and Program, it seems unlikely that Hibernate can reliably
process HQL (ie generate valid SQL).
I think programId in the Project mapping should be of type a.b.model.Program
not java.lang.Integer, and the relationship expresses as a join in the
mapping.
Anyway, I would suggest reading up on Hibernate mappings, and if you still
have trouble, ask over on the Hibernate forums. I am 99% sure there is
nothing wrong with the Derby dialect in this case.
Good luck.
Brett
Sent from my iPhone
On Jun 1, 2010, at 14:07, anyz <an...@gmail.com> wrote:
Thanks Brett for your time. Yes it is more of Hibernate issue i was just
trying to find if some of derby user encounter with this.
Here are tables structure and mapping.
CREATE TABLE PROJECT (
PROJECT_ID VARCHAR(50) NOT NULL,
PROJECT_NAME VARCHAR(150) NOT NULL,
PROJECT_MANAGER VARCHAR(150) NOT NULL,
START_DATE TIMESTAMP NOT NULL,
END_DATE TIMESTAMP NOT NULL,
PROGRAM_ID INTEGER,
PRIMARY KEY (PROJECT_ID)
);
ALTER TABLE PROJECT
ADD FOREIGN KEY (PROGRAM_ID)
REFERENCES PROGRAM (PROGRAM_ID);
CREATE TABLE PROGRAM (
PROGRAM_ID INTEGER DEFAULT AUTOINCREMENT: start 1 increment 1 NOT NULL,
PROGRAM_NAME VARCHAR(150) NOT NULL,
PROGRAM_MANAGER VARCHAR(150) NOT NULL,
PRIMARY KEY (PROGRAM_ID)
);
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD
3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="a.b.model.Project" table="PROJECT">
<id name="projectId" type="string">
<column name="PROJECT_ID" length="50" />
<generator class="assigned" />
</id>
<property name="projectName" type="string">
<column name="PROJECT_NAME" length="150" not-null="true" />
</property>
<property name="projectManager" type="string">
<column name="PROJECT_MANAGER" length="150" not-null="true" />
</property>
<property name="startDate" type="timestamp">
<column name="START_DATE" length="26" not-null="true" />
</property>
<property name="endDate" type="timestamp">
<column name="END_DATE" length="26" not-null="true" />
</property>
<property name="programId" type="java.lang.Integer">
<column name="PROGRAM_ID" />
</property>
</class>
</hibernate-mapping>
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD
3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="a.b.model.Program" table="PROGRAM">
<id name="programId" type="int">
<column name="PROGRAM_ID" />
<generator class="identity" />
</id>
<property name="programName" type="string">
<column name="PROGRAM_NAME" length="150" not-null="true" />
</property>
<property name="programManager" type="string">
<column name="PROGRAM_MANAGER" length="150" not-null="true" />
</property>
</class>
</hibernate-mapping>
Thanks once again.
On Mon, May 31, 2010 at 7:55 PM, Brett Wooldridge <
brett.wooldridge@gmail.com> wrote:
> When you say "Hibernate 3", what specific version are you talking about?
> While this is more of a Hibernate question than a Derby question, what is
> the structure of the two tables (for Project and Program) and what does the
> mapping look like?
>
> Brett
>
>
> On Mon, May 31, 2010 at 10:52 PM, anyz <an...@gmail.com> wrote:
>
>> Its seems to be hibernate issue. Dialect in hibernate 3 is not uptodate i
>> think.
>>
>> We tried it as below:
>>
>> SELECT proj, prog, FROM Project proj, Program prog
>>
>> but it didn't work.
>>
>>
>> On Mon, May 31, 2010 at 6:19 PM, Donald McLean <dm...@gmail.com>wrote:
>>
>>> Have you tried explicitly specifying the table? i.e. Project.proj
>>>
>>> On Mon, May 31, 2010 at 7:00 AM, anyz <an...@gmail.com> wrote:
>>> > i'm trying to use hibernate 3 with Derby 10.6. Normal queries work good
>>> but
>>> > using left outer join creates problem. The query i'm trying loads
>>> project
>>> > program if it exists and if programm not exists the project information
>>> is
>>> > loaded:
>>> >
>>> > SELECT proj, prog FROM Project
>>> > LEFT OUTER JOIN Program ON Program.programId = Project.programId
>>> > WHERE Project.projectName = '"MyProject"
>>> >
>>> > The error on executing query is :
>>> >
>>> > unexpected token: ON near line 1, column xxx [SELECT proj, prog, FROM
>>> > a.b.Project LEFT OUTER JOIN Program ON Program.programId =
>>> Project.programId
>>> > WHERE Project.projectName = 'MyProject' ]
>>> > at org.hibernate.hql.ast.QuerySyntax
>>> >
>>> > Could you please guid if Hibernate dialect support for Derbby is not
>>> that
>>> > good of i'm missing something.
>>> >
>>> > thanks
>>> >
>>>
>>>
>>>
>>> --
>>> Family photographs are a critical legacy for
>>> ourselves and our descendants. Protect that
>>> legacy with a digital backup and recovery plan.
>>>
>>> Join the photo preservation advocacy Facebook group:
>>> http://www.facebook.com/home.php?ref=logo#/group.php?gid=148274709288
>>>
>>
>>
>