Hi,

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

Below is table structure and mappings for two tables:

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>

This has nothing to do with Derby. If you want to use a JOIN in HQL you need to map an association between Project and Program. In this case probably as a many-to-one. Replace the <property name="programId"...> with a <many-to-one class="Program" ....>. Then you can join like this:




More information:
Many-to-one: http://docs.jboss.org/hibernate/stable/ ... -manytoone

HQL-joins: http://docs.jboss.org/hibernate/stable/ ... yhql-joins

I used it without specifiying relationships to select columns from differnt tables as below on mysql database.

SELECT proj.projectName, prog.ProgramManager FROM Project
LEFT OUTER JOIN Program ON Program.programId = Project.programId
WHERE Project.projectName = '"MyProject"


However to select complete models probably we need to specify relationsships. Is it correct?

Maybe you used SQL? HQL doesn't support that kind of join syntax.

Right it was SQL infact. But is there any syntax error in query on DERBY. How can i debug query executed. I'm using it in Swing application without any logger configured.

Thanks

Maybe you should ask on a Derby forum what kind of SQL it supports?

Thanks a lot nordborg. I will try to debug query executed and check it for derby syntax.