I am using Hibernate 2.5 for interaction with database and Spring. DB is MS SQL .Application server JBoss4.0.2
Now I need to perform an outer join on two tables for which I have specified in mapping file as:
<class name="Amigo" table="USERS.DBO.AMIGO" persister="com.bolt.service.hibernate.persister.BoltEntityPersister">
<id name="id" type="java.lang.Integer" column="ID" unsaved-value="null" >
<generator class="native"/>
</id>
<property name="userId" type="java.lang.Integer" column="USERID"/>
<property name="amigoId" type="java.lang.Integer" column="AMIGOID"/>
<property name="amigoType" type="java.lang.Integer" column="AMIGOTYPE"/>
<property name="createDate" type="java.util.Date" column="CREATEDATE" not-null="false"/>
<many-to-one name="owner" column="userId" update="false" insert="false" outer-join="false"/>
<many-to-one name="friend" column="amigoId" update="false" insert="false" outer-join="false"/>
<set name="onlineFriendsList">
<key column="USERID"/>
<one-to-many class="com.bolt.app.amigo.model.OnlineFriends"/>
</set>
</class>
<class name="OnlineFriends" table="v_OnLineUser" persister="com.bolt.service.hibernate.persister.BoltEntityPersister">
<id name="id" type="java.lang.Integer" column="ID" unsaved-value="null" >
<generator class="native"/>
</id>
<property name="userId" type="java.lang.Integer" column="USERID"/>
<property name="memberName" type="java.lang.String" column="MEMBERNAME"/>
<property name="hostIP" type="java.lang.String" column="HOSTIP" not-null="true"/>
<property name="dateStarted" type="java.util.Date" column="DATESTARTED" update="false" not-null="true"/>
<property name="dateLastAccessed" type="java.util.Date" column="DATELASTACCESSED" not-null="true"/>
<property name="refererURI" type="java.lang.String" column="REFERERURI"/>
<property name="hostName" type="java.lang.String" column="HOSTNAME"/>
</class>
The Hibernate query is:
from Amigo amigo left join fetch amigo.onlineFriendsList
where amigo.amigoType = :typeId
and amigo.userId = :userId
Now, when we run the application we get an SQLException with error code 156 saying Incorrect SQL syntex 'with'
Now we got the generated SQL query which is:
select top 10 amigo0_.ID as ID0_,
onlinefrie1_.ID as ID1_,
amigo0_.USERID as USERID0_,
amigo0_.AMIGOID as AMIGOID0_,
amigo0_.AMIGOTYPE as AMIGOTYPE0_,
amigo0_.CREATEDATE as CREATEDATE0_,
amigo0_.userId as userId0_,
amigo0_.amigoId as amigoId0_,
onlinefrie1_.USERID as USERID1_,
onlinefrie1_.MEMBERNAME as MEMBERNAME1_,
onlinefrie1_.HOSTIP as HOSTIP1_,
onlinefrie1_.DATESTARTED as DATESTAR5_1_,
onlinefrie1_.DATELASTACCESSED as DATELAST6_1_,
onlinefrie1_.REFERERURI as REFERERURI1_,
onlinefrie1_.HOSTNAME as HOSTNAME1_,
onlinefrie1_.ID as ID__,
onlinefrie1_.USERID as USERID__
from USERS.DBO.AMIGO amigo0_ with (nolock) left outer join v_OnLineUser onlinefrie1_
on amigo0_.ID=onlinefrie1_.USERID with (nolock) where (amigo0_.AMIGOTYPE=1)and(amigo0_.USERID
=1001045649 )
Observe the words "with (no lock)" in the query. That's the problem..If we remove it then the query executes successfully. My question is that the sql query that is getting generated appends the "with (no lock)" keyword. Is this the problem of hibernate mapping, spring or database? We are performing the hubernate query call through Spring framework....PLEASE REPLY.
|