Hi Fellas
Their is a huge user community like us who would like to leverage the flexibility of SQL & persistence management of Hibernate . Not sure whether any performance is involved here. Till now I didn't have a good feeling on using Hibernate3 for native sql queries thinking JdbcTemplate from Spring will be ideal for native sql queries. Hope Gavin and Christian are listening.

I am struggling to run a simple Many-To-Many relationship example using createSQLQuery. Yes i read the Hibernate 3 docs
CAUTION : The docs contains examples which are deprecated e.g creatSQL(String, String,String), addJoin(String)

Anyhow any pointers/suggestions will be highly appreciated in resolving the following snippet of code which i have been working on a week for now

Here is the scenario

I would like to list the Roles of a user based on user_id


[b]Hibernate version:3.0[/b]

[b]Mapping documents:hello.hbm.xml
<class name="com.mycompany.User" table="CED">
<id name="id" column="ID">
<generator class="assigned"/>
</id>
<property name="lastName" column="LAST_NAME"/>
<property name="firstName" column="FIRST_NAME"/>

<set name="roles" table="USER_GROUP_ROLES" inverse="true" cascade="all">
<key column="USER_ID" />
<many-to-many column="ROLE_ID" class="com.mycompany.Role"/>
</set>

</class>

<class name="com.mycompany.Role" table="USER_ROLES">
<id name="id" column="ID">
<generator class="assigned">
</generator>
</id>

<property name="name" column="NAME" />

</class>

<class name="com.mycompany.UserGroupRole" table="USER_GROUP_ROLES">
<id name="id" column="ID">
<generator class="assigned">
</generator>
</id>
<property name="userId" column="USER_ID" />
<property name="roleId" column="ROLE_ID" />
</class>


[/b]

[b]Code between sessionFactory.openSession() and session.close():
public List getUser(final String bemsId) throws DataAccessException {

return (List) hibernateTemplate.execute(new HibernateCallback() {
public Object doInHibernate(Session session) throws HibernateException {


/* Many To Many : Not Working */

String sql = "select {c.*} from ced {c}, user_roles {ur}, user_group_roles {ugr} " +
"where {ugr}.user_id={c}.id and {ugr}.role_id={ur}.id and {ugr}.user_id=20";

SQLQuery query = session.createSQLQuery(sql).addEntity("c",User.class);

return query.list();
}
});

}


[/b]

[b]Full stack trace of any exception that occurs:

org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not execute query; uncategorized SQLException for SQL [select c.ID as ID0_, c.LAST_NAME as LAST2_1_0_, c.FIRST_NAME as FIRST3_1_0_ from ced c, user_roles {ur}, user_group_roles {ugr} where {ugr}.user_id=c.id and {ugr}.role_id={ur}.id and {ugr}.user_id=20]; SQL state [null]; error code [17034]; Non supported SQL92 token at position: 102: ur; nested exception is java.sql.SQLException: Non supported SQL92 token at position: 102: urjava.sql.SQLException: Non supported SQL92 token at position: 102: ur

[/b]

[b]Name and version of the database you are using: Oracle 9[/b]

[b]The generated SQL (show_sql=true):
select c.ID as ID0_, c.LAST_NAME as LAST2_1_0_, c.FIRST_NAME as FIRST3_1_0_ from ced c, user_roles {ur}, user_group_roles {ugr} where {ugr}.user_id=c.id and {ugr}.role_id={ur}.id and {ugr}.user_id=20

[/b]

[b]Database Schema:
SQL> desc ced
Name Null? Type
----------------------------------------- -------- --------------------
ID NOT NULL NUMBER
LAST_NAME NOT NULL VARCHAR2(60)
FIRST_NAME NOT NULL VARCHAR2(60)
BEMSID NOT NULL NUMBER
PHONE VARCHAR2(15)

SQL> desc user_roles
Name Null? Type
----------------------------------------- -------- --------------------
ID NOT NULL NUMBER
NAME NOT NULL VARCHAR2(60)
DESCRIPTION VARCHAR2(100)
ARCHIVE NOT NULL VARCHAR2(1)
CHANGE_USER NOT NULL VARCHAR2(60)
CHANGE_DATE DATE

SQL> desc user_group_roles
Name Null? Type
----------------------------------------- -------- --------------------
ID NOT NULL NUMBER
GROUP_ID NUMBER
USER_ID NUMBER
ROLE_ID NUMBER
CHANGE_USER NOT NULL VARCHAR2(60)
CHANGE_DATE DATE
[/b]


Regards
Bansi

Forgot to mention the mapping association in the above post. Here it is

User (mapped to CED table) has many-to-many relationship with Role(mapped to user_roles table). The association table is User_Group_Roles (mapped to user_group_roles table)


Regards
Bansi

Quick update

I modified the sql query as

String sql = "select c.first_name as firstName, c.last_name as lastName, u.name as roleName from ced c, user_roles u, user_group_roles ugr " +
"where ugr.user_id=c.id and ugr.role_id=u.id and ugr.user_id=20";

SQLQuery query = session.createSQLQuery(sql).addEntity("c",User.class).addEntity("u",Role.class);


Now i get the following error

org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: could not execute query; bad SQL grammar [select c.first_name as firstName, c.last_name as lastName, u.name as roleName from ced c, user_roles u, user_group_roles ugr where ugr.user_id=c.id and ugr.role_id=u.id and ugr.user_id=20];

nested exception is

java.sql.SQLException: Invalid column namejava.sql.SQLException: Invalid column name


NOTE : I copied the above sql query & it runs perfectly fine in SQL Prompt so from where the error "Invalid column name" is coming

I did not know that JdbcTemplate from Spring could map into *persistent* objects (and graphs) ? Please show me how.

p.s. just having a RowMapper on a JdbcTemplate does not give you persistent objects nor graphs.



May I ask why you need to use native sql to fetch a many-to-many graph of objects ?! Why not just use HQL for this case ?

If you want to use native sql you will need to return the same columns that hibernate expects from such a query (the many to many id1 and id2 and both sides of the relationship etc.) This is not trivial and definitly not something that is covered by e.g. Spring JdbcTemplate.

The reason you get "column name not found" is because hibernate is looking for a column that you specified in the mapping files but did not list in your query!

p.s. Hibernate 3.2 is definitly worth the upgrade if you want better native sql integration.

Thanks Max

You are right in saying

[color=blue][i]If you want to use native sql you will need to return the same columns that hibernate expects from such a query (the many to many id1 and id2 and both sides of the relationship etc.) This is not trivial[/i][/color]

I realized this during the research & hence changed the query which works now
[color=blue][i]String sql = "select {c.*}, {u.*} from ced c, user_roles u, user_group_roles ugr " +
"where ugr.user_id=c.id and ugr.role_id=u.id and ugr.user_id=20";[/i][/color]

But i know this is not the ideal or recommended way. Also i am getting
ClassCastException as it is unable to handle the results returned by the query as shown below

[color=red][i]List userList=testSpringBean.getUser(userId);

System.out.println("User List:"+userList.size());
for (Iterator it = userList.iterator(); it.hasNext();) {
user= (User) it.next();
/* The exception occurs at the above line as the query returned two objects i.e. User & Role . Not sure how to handle this situation. Any pointers/suggestions will be highly appreciated*/
}[/i][/color]

Max the reason i am using Native SQL is two-fold reason
1) The team has good experience in writing SQL queries
2) Native SQL is highly recommended for writing Complex queries, subqueries, complex expressions such as
select (select count(id) from Foo) / (select count(id) from Bar) from Something

Note : We have a query which joins 8 tables.

Yes, subslects in the "select" clause is not permitted in HQL....but otherwise HQL will most likely suffice.

Max
I am still stuck with the issue of getting my query worked. I am using Many-To-Many Unidirectional relationship between User and Role i.e. i want to list the Roles of a User.


I am getting "Invalid Colum" error for Native SQL query
Here is my Native SQL query
[color=blue]String sql = "select c.last_name as {c.lastName}, c.first_name as
{c.firstName} from ced c where c.bemsid=:id";
SQLQuery query = session.createSQLQuery(sql).addEntity("c",User.class);[/color]


I am getting following error for HQL query
org.springframework.orm.hibernate3.HibernateQueryException: could not resolve property: bemsId of: com.mycompany.User [select user.lastName, user.firstName from com.mycompany.User as user where user.bemsId=:id]; nested exception is org.hibernate.QueryException: could not resolve property: bemsId of: com.mycompany.User

Here is my HQL query
[color=blue]String sql = "select user.lastName, user.firstName from User as user " +
"where user.bemsId=:id";

Query query = session.createQuery(sql);
query.setParameter("id", bemsId); [/color]


Any pointers /suggestions on writing Native SQL / HQL queries for Many-To-Many relationship will be highly appreciated.

Also please let me know how do i Iterate thru the Objects returned by the query. The following results in ClassCastException

userList=testSpringBean.getUser(bemsId);
System.out.println("User List:"+userList.size()); // returns size = 2
user= (User) userList.get(0);
role = (Role) userList.get(1);
String lastName = user.getLastName();
String roleName = role.getName();


Regards
Bansi



[/code][/quote]

ok - you need to read the docs plus try out the tutorials.

I am wondering isn't it possible in Hibernate for Queries to Return two objects . If yes then how do we iterate thru the List

Its very common to have queries returning two objects like this

String sql = "select c.last_name as {c.lastName}, c.first_name as {c.firstName}, u.role_name as {u.roleName}from ced c, user_roles u, user_group_roles ugr " +
"where ugr.user_id=c.id and ugr.role_id=u.id ;

The above query returns User and Role objects. Wondering how would we iterate thru these two objects using List Iterator

what about reading the docs ?

what about trying to print out/debug what type the elements returned are ?

Then you will soon figure out that the returned element is an Object[] by default if more than one object is returned by row.

If you want it to be different then look at ResultTransformer (also in the docs)

Hi Max
I am doing the following to retrieve array of objects

Iterator iter = userList.iterator();
if (!iter.hasNext())
{
System.out.println("No objects to display.");
return;
}
while (iter.hasNext())
{
System.out.println("New object");
Object[] obj = (Object[]) iter.next();
for (int i=0;i<=obj.length;i++)
{
System.out.println("Object="+obj[i]);
System.out.println("Object Name:"+obj[i].getClass().getName());
}


}

Now i have object name in hand , how do i access the properties of each of those objects.

For example in the above code
obj[i].getClass().getName()) prints two object names i.e. User and Role

Now how do i access something like User.getFirstName() or User.getLastName() or for that matter Role.getName()

Regards
Bansi

Person p = ((Person)obj[i]);

its called casting - look up the java tutorial on java.sun.com

i know type casting :)-
It throws ClassCast Exception

so it is not of the type you cast it to. Use the type that your printout says it is.