Uni-directional Many-to-many problem

[Mohamed Atiea]

Hello all,

I have a uni-direction many-to-many relation with Job and Category, Job has many categories when I try to get the job by its declared categories field (ste<Category>) hibernate generates SQL but with a dot inside it below is how I am using HQL and the exception:

Please note the dot in the generated SQL

HQL:

   select new Job(job.id, job.title, job.postDate, job.employerName, emp.employerId, emp.name) from Job as job left join job.employer as emp where job.categories in (3, 4, 5) 

GENERATED SQL & ERROR:

  

select
        job0_.id as col_0_0_,
        job0_.title as col_1_0_,
        job0_.post_date as col_2_0_,
        job0_.employer_name as col_3_0_,
        employer1_.employer_id as col_4_0_,
        employer1_.name as col_5_0_ 
    from
        jobs job0_ 
    left outer join
        employers employer1_ 
            on job0_.employer_id=employer1_.employer_id,
        jobs_categories categories2_,
        categories category3_ 
    where
        job0_.id=categories2_.job_id 
        and categories2_.category_id=category3_.catId 
        and (
            . in (
                3 , 4 , 5
            )
        )
Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not execute query
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
	at org.hibernate.loader.Loader.doList(Loader.java:2235)
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2129)
	at org.hibernate.loader.Loader.list(Loader.java:2124)
	at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401)
	at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363)
	at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
	at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1149)
	at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
	at com.egyonline.util.HibernateTest.leftJoin(HibernateTest.java:101)
	at com.egyonline.util.HibernateTest.main(HibernateTest.java:88)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'in (3 , 4 , 5))' at line 1
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
	at java.lang.reflect.Constructor.newInstance(Unknown Source)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
	at com.mysql.jdbc.Util.getInstance(Util.java:381)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3536)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3468)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1957)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2107)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2648)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2086)
	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2237)
	at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
	at org.hibernate.loader.Loader.getResultSet(Loader.java:1812)
	at org.hibernate.loader.Loader.doQuery(Loader.java:697)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
	at org.hibernate.loader.Loader.doList(Loader.java:2232)
	... 9 more


  

Correct SQL that I was expecting:

  

select
        job0_.id as col_0_0_,
        job0_.title as col_1_0_,
        job0_.post_date as col_2_0_,
        job0_.employer_name as col_3_0_,
        employer1_.employer_id as col_4_0_,
        employer1_.name as col_5_0_ 
    from
        jobs job0_ 
    left outer join
        employers employer1_ 
            on job0_.employer_id=employer1_.employer_id,
        jobs_categories categories2_,
        categories category3_ 
    where
        job0_.id=categories2_.job_id 
        and categories2_.category_id=category3_.catId 
        and (
            categories2_.category_id in (
                3 , 4 , 5
            )
        )

  

My HBM files are:

  

<?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 package="com.egyonline.beans">
	<class name="Category" table="categories">
		<id name="catId" unsaved-value="null" type="integer">
			<generator class="native" />
		</id>
		<many-to-one name="parentCat" class="Category" not-null="false"/>
		<property name="catName" type="string" not-null="true"/>
	</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 package="com.egyonline.beans">
	<class name="Job" table="jobs">
		<id name="id" column="id" unsaved-value="null" type="integer">
			<generator class="native" />
		</id>
		<property name="postDate" column="post_date" type="timestamp" not-null="true" update="false"/>
		<property name="title" column="title" type="string" not-null="true"/>
		----------------
		---------------- other fileds
		----------------
		<set name="categories" table="jobs_categories" order-by="category_id" lazy="false" cascade="all">
			<key column="job_id" not-null="true" unique="true"/>
			<many-to-many column="category_id" class="Category" unique="true" />
		</set>
	</class>
</hibernate-mapping>
  

Thanks