Hi all,

I'm trying to construct a Native SQL Query from Hibernate where the REAL sql is:

select pch.*
from product_category pc inner join product_category_hierarchy pch on pch.child_id = pc.id
where pc.aid = 4 and pch.parent_id=0;

My best attempt at this is:

Session ssn = TestSessionFactory.currentSession();
Query sqlQuery = ssn.createSQLQuery(

"select {pch.*} from product_category {pc} " +
"inner join product_category_hierarchy {pch} on {pch}.child_id = {pc}.id " +
"where {pc}.aid = 4 and {pch}.parent_id=0l",
"pch", ProductCategoryHierarchy.class);

sqlQuery.setMaxResults(50);
List list = sqlQuery.list();


Here are the significant aspects to my problem domain:

Hibernate version: 2.1.8
database: MySql v.4.1

ProductCategory.hbm.xml
<hibernate-mapping>

<class name="com.tll.valueobject.ProductCategory" table="product_category">

<meta attribute="extends">TimeStampValueObject</meta>

<id name="id" unsaved-value="null" column="id" type="long">
<generator class="hilo"/>
</id>

<property name="dateCreated" column="date_created" type="date" not-null="true"/>
<property name="dateLastModified" column="date_last_modified" type="date" not-null="true"/>

<property name="name" column="name" type="string" length="128" not-null="true"/>
<property name="description" column="description" type="string" length="128"/>
<property name="image" column="image" type="string" length="64"/>

<many-to-one name="account" class="com.tll.valueobject.Account" column="aid" foreign-key="fk_pc_a"/>

</class>

</hibernate-mapping>

ProductCategoryHierarchy.hbm.xml:
<hibernate-mapping>

<class name="com.tll.valueobject.ProductCategoryHierarchy" table="product_category_hierarchy">

<meta attribute="extends">ValueObject</meta>

<id name="id" unsaved-value="null" column="id" type="long">
<generator class="hilo"/>
</id>

<many-to-one name="parentId" class="com.tll.valueobject.ProductCategory" column="parent_id"/>
<many-to-one name="childId" class="com.tll.valueobject.ProductCategory" column="child_id"/>

</class>

</hibernate-mapping>

DB STRUCT:

create table product_category (
id bigint not null,
date_created date not null,
date_last_modified date not null,
name varchar(128) not null,
description varchar(128),
image varchar(64),
aid bigint,
primary key (id)
);
create table product_category_hierarchy (
child_id bigint not null,
parent_id bigint not null
);


I know this is verbose. Any guidance would be appreciated. TIA!

jon
jopaki@yahoo.com

and what is the error if any you have ?

14:09:10,013 WARN JDBCExceptionReporter:57 - SQL Error: 1064, SQLState: 42000

14:09:10,013 ERROR JDBCExceptionReporter:58 - Syntax error or access violation message from server: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '{pc} inner join product_category_hierarchy pch on pch.child_id = {pc}.id where {' at line 1"

seem you havent defined what the pc alias should expand to.

please read the createSQLQuery docs, and if you use hibernate 3 look at the unittests since that is more uptodate at the moment than the docs.