I am stuck with the issue of getting the queries 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();

P.S. I am using Hibernate 3.1 and have read the documentation atleast 3-5 times and found quite deprecated methods there. Also the examples mentioned in the V3 documentation for Native SQL queries using Many-To-Many relationship is deprecated or doesn't work (http://www.hibernate.org/hib_docs/v3/re ... /#querysql) .

Regards
Bansi

deprecated does not mean "doesn't work" so please either be concrete in what you are referring to or even better provide a patch to make the doc more correct if you think there is errors.

querying with HQL on many-to-many is no different than any other collection so i do not know what to tell you besides what is in the docs.

...and i can't find any example in the doc you refer to that has to do explicitly with many-to-many!?

Max

All i need is a help to fix my problem which i have been working for more than a week now and what i am saying is the docs are not of much help in fixing my problem and hence posting on the Forums

Any pointers/suggestions in helping me fix the problem will be highly appreciated

Regards
Bansi

since you are being very inprecise then i'll jus tpoint you to check out our unit test for manytomanby mappings and check the queries used there.

Thanks for pointing out to the test folder for many-to-many mappings. Just wanna let you know i already looked at those before writing the queries , I even looked at the sql folder . I wrote my query by looking at HandSQLTest.java shipped with the hibernate 3.1

protected String getOrganizationFetchJoinEmploymentSQL() {
return "SELECT org.orgid as {org.id}, " +
" org.name as {org.name}, " +
" emp.employer as {emp.key}, " +
" emp.empid as {emp.element}, " +
" {emp.element.*} " +
"FROM ORGANIZATION org " +
" LEFT OUTER JOIN EMPLOYMENT emp ON org.ORGID = emp.EMPLOYER";
}


Here is a similar query which doesnt work for me

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);
query.setParameter("id", bemsId);

that is not a similar query...unless last_name and first_name is the *only* columns for your User entity.

last_name and first_name are the *only* columns for User entity in the mapping file as shown below

<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="USERS_ROLES" inverse="true" cascade="all">
<key column="USER_ID" />
<many-to-many column="ROLE_ID" class="com.mycompany.Role"/>
</set>

</class>

no they are not the only columns, there is also an ID column.

So how should i rewrite my query

this is my *final* comment on this since you are only asking about stuff that is already documented.



or




or in H3.2, simply


sine h3.2 supports using the mapped column names directly in native sql queries.