Hi all.

I am trying to use JOIN on two DB views, my SQL query (which works perfect on my DB) looks lke this:

SQL :
====
select map.object_type, map.function_name from authorization_mappings as map join authorizations as auth USING(permission_id) where auth.person_id=4720


I am trying to run this query from HQL like this:

------------------------------------------------------------------------------------------------------------------
String query = "select map.object_type, map.function_name from authorization_mappings as map join authorizations as auth USING(permission_id) where auth.person_id=4720";

Iterator i = session.createQuery(query).list().iterator();

------------------------------------------------------------------------------------------------------------------

and eventually getting an error:

========================================================================
log4j:WARN No appenders could be found for logger (org.hibernate.cfg.Environment).
log4j:WARN Please initialize the log4j system properly.
Exception in thread "main" org.hibernate.hql.ast.QuerySyntaxError: unexpected token: USING near line 1, column 134 [select map.object_type, map.function_name from authorization_mappings as map join authorizations as auth USING(permission_id) where auth.person_id=4720]
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:63)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:215)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:127)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:83)
at org.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:427)
at org.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:884)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:834)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at com.mms.test.HibernateTest.createAndStore(HibernateTest.java:36)
at com.mms.test.HibernateTest.main(HibernateTest.java:18)
Caused by: line 1:134: unexpected token: USING
at org.hibernate.hql.antlr.HqlBaseParser.fromJoin(HqlBaseParser.java:1380)
at org.hibernate.hql.antlr.HqlBaseParser.fromClause(HqlBaseParser.java:1053)
at org.hibernate.hql.antlr.HqlBaseParser.selectFrom(HqlBaseParser.java:759)
at org.hibernate.hql.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:611)
at org.hibernate.hql.antlr.HqlBaseParser.selectStatement(HqlBaseParser.java:263)
at org.hibernate.hql.antlr.HqlBaseParser.statement(HqlBaseParser.java:150)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:209)
... 8 more
========================================================================

which makes me think keywords like "USING" and "ON" are not supported within HQL.

So if anybody could explain me the best way to do this and how can I use JOIN on my VIews ????

All suggestions appreciated, thanks in advance.

I'm a newbie myself but, did you try using the ClassicQueryTranslatorFactory
instead of the ast.ASTQueryTranslatorFactory?

If not you might want to try it. In my environment I changed it in hibernate.cfg by adding the following entry:


<hibernate-configuration>
<session-factory>
<property name="hibernate.query.factory_class">org.hibernate.hql.classic.ClassicQueryTranslatorFactory</property>
</session-factory>
</hibernate-configuration>


Good luck.

Thanks massep, it helped but gives me another error ..... which says

Exception in thread "main" org.hibernate.QueryException: in expected: map [select map.object_type, map.function_name from authorization_mappings map join authorizations auth USING(permission_id) where auth.person_id=4706]


What I am curious to know is, whether writing such SQL queries within HQL is a good propramming practice from Hibernate point of view ????


All I wanna do is a JOIN On two views which look like this:

Authorizations View:
=================

auth_id | permission_id | person_id
____________________________
1 | 4 | 4706
2 | 7 | 4706
3 | 9 | 4706


AUthorizations Mapping View:
========================

permission_id | object_type | fucntion_name |
______________________________________
4 | employee | read
7 | customer | write
9 | employee | read


The Join should fetch me out the "object_type" and "function_name" columns for all person_id = 4706


SO my question is what is the best way to implement such a JOIN????

How about the dialect, did you configure the right one?

Mine's Sybase;

<property name="hibernate.dialect">org.hibernate.dialect.SybaseDialect</property>