How to make this HQL work between different databases?
It works in MSSQL, but it doesn't in MySQL.
Query:
"select u" +
" from User u, Role r" +
" where u.username = ?" +
" and u.password = ?" +
" and r.rolename = ?" +
" and r in elements(u.roles)",
new String[]{username, password, role});
Thanx, Ales
-----------------------------------
Hibernate version: 2.1.6
Mapping documents:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class
name="com.generalynx.ecos.data.User"
table="euser"
proxy="com.generalynx.ecos.data.User"
>
<id
name="id"
column="euser_id"
type="int"
unsaved-value="0"
>
<generator class="native">
</generator>
</id>
<version
name="version"
type="integer"
column="uversion"
access="property"
unsaved-value="null"
/>
<component
name="person"
class="com.generalynx.ecos.data.Person"
>
<property
name="name"
type="java.lang.String"
column="name"
/>
<property
name="surname"
type="java.lang.String"
column="surname"
/>
<property
name="telephone"
type="java.lang.String"
column="phone"
/>
<property
name="email"
type="java.lang.String"
column="email"
/>
</component>
<component
name="address"
class="com.generalynx.ecos.data.Address"
>
<property
name="street"
type="java.lang.String"
column="street"
/>
<property
name="city"
type="java.lang.String"
column="city"
/>
<property
name="post"
type="java.lang.Integer"
column="post"
/>
<property
name="country"
type="java.lang.String"
column="country"
/>
</component>
<property
name="username"
type="java.lang.String"
column="username"
/>
<property
name="password"
type="java.lang.String"
column="password"
/>
<property
name="statusKey"
type="int"
column="status_key"
/>
<component
name="creator"
class="com.generalynx.ecos.data.CreatorComponent"
>
<property
name="creatorId"
type="int"
column="creator_id"
/>
<property
name="creationTime"
type="java.util.Date"
column="creation_time"
/>
</component>
<property
name="defaultModule"
type="java.lang.Integer"
column="def_module"
/>
<set
name="roles"
table="eusererole"
lazy="true"
inverse="false"
cascade="none"
sort="unsorted"
>
<key
column="euser_id"
>
</key>
<many-to-many
class="com.generalynx.ecos.data.Role"
column="erole_id"
outer-join="auto"
/>
</set>
<set
name="modules"
table="moduleuser"
lazy="false"
inverse="false"
cascade="none"
sort="unsorted"
>
<cache
usage="read-write"
/>
<key
column="euser_id"
>
</key>
<many-to-many
class="com.generalynx.common.web.module.Module"
column="modul_id"
outer-join="auto"
/>
</set>
<!--
To add non XDoclet property mappings, create a file named
hibernate-properties-User.xml
containing the additional properties and place it in your merge dir.
-->
</class>
</hibernate-mapping>
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class
name="com.generalynx.ecos.data.Role"
table="erole"
proxy="com.generalynx.ecos.data.Role"
>
<id
name="id"
column="erole_id"
type="int"
unsaved-value="0"
>
<generator class="native">
</generator>
</id>
<property
name="rolename"
type="java.lang.String"
column="rolename"
/>
<set
name="users"
table="eusererole"
lazy="true"
inverse="false"
cascade="none"
sort="unsorted"
>
<key
column="erole_id"
>
</key>
<many-to-many
class="com.generalynx.ecos.data.User"
column="euser_id"
outer-join="auto"
/>
</set>
<!--
To add non XDoclet property mappings, create a file named
hibernate-properties-Role.xml
containing the additional properties and place it in your merge dir.
-->
</class>
</hibernate-mapping>
Code between sessionFactory.openSession() and session.close():
Full stack trace of any exception that occurs:
Could not execute query
java.sql.SQLException: 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 'select roles2_.erole_id from eusererole roles2_ where user0_.eu
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2851)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1534)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1625)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2297)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2226)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1812)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1657)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:875)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:269)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:1033)
at net.sf.hibernate.loader.Loader.list(Loader.java:1024)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1544)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
Name and version of the database you are using:
MySQL 4.0.17
The generated SQL (show_sql=true):
select user0_.euser_id as euser_id, user0_.uversion as uversion, user0_.name as name, user0_.surname as surname, user0_.phone as phone, user0_.email as email, user0_.street as street, user0_.city as city, user0_.post as post, user0_.country as country, user0_.username as username, user0_.password as password, user0_.status_key as status_key, user0_.creator_id as creator_id, user0_.creation_time as creatio15_, user0_.def_module as def_module from euser user0_, erole role1_ where (user0_.username=? )and(user0_.password=? )and(role1_.rolename=? )and(role1_.erole_id in(select roles2_.erole_id from eusererole roles2_ where user0_.euser_id=roles2_.euser_id))
Debug level Hibernate log excerpt:
Login
FAQ
Search
