SQL Query Returns Result, But Empty List Returned

tspear · **Joined:** Thu Dec 15, 2005 11:24 pm **Posts:** 5

Sorry for cross posting (I posted this incorrectly in the User forum).

Problem:
I have a complex query using sub queries (yes I probably could do it with joins but wanted to find out why this does not work). Using simple queries against each object or limited subsets work fine. Taking the generated query and running it directly in the database also works. However, when running it in the Hibernate Entity Manager I get no results returned. I have traced a fair percentage of the code, and I see no obvious errors; I have also eliminated the params and hard coded values in the select to no avail. If desired, I can either post or email a sample project.
Any pointers in where to look to debug this further would be appreciated.

TIA,

Tim

Hibernate version:
hibernate-3.1
hibernate-annotations-3.1beta7
hibernate-entitymanager-3.1beta5

Mapping documents:
Autogenerated by EntityManager based on POJO

Name and version of the database you are using:
MySQL 5.0

Object Structure
(Since group is a reserved word, I used cartel :-)

User [one to many to] UserCartel [many to one to] Cartel [one to many to] CartelPermission [many to one to] Permission

HQL Query

Code:

Select p from Permission as p
 where p.permissionName like :menuroot
 and p in (
 select cp.permission from CartelPermission as cp
 where cp.cartel in
 (select uc.cartel from UserCartel as uc where uc.user.userID = :userid))

The generated SQL (show_sql=true):

Code:

select
        permission0_.permissionID as permissi1_3_,
        permission0_.permissionName as permissi2_3_,
        permission0_.pageName as pageName3_
    from
        Permission permission0_
    where
        (
            permission0_.permissionName like ?
        )
        and (
            permission0_.permissionID in (
                select
                    cartelperm1_.permission_permissionID
                from
                    CartelPermission cartelperm1_
                where
                    cartelperm1_.cartel_cartelID in (
                        select
                            usercartel2_.cartel_cartelID
                        from
                            UserCartel usercartel2_
                        where
                            usercartel2_.user_userID=?
                    )
                )
        )

tspear · **Joined:** Thu Dec 15, 2005 11:24 pm **Posts:** 5

Tracing the code, I found my error. I was trying to set the param as a primitive value, and Hibernate required that it be a Class representation.

Tim

emmanuel · **Posted:** Mon Dec 19, 2005 7:11 am

hug! bad autoboxing

tspear · **Joined:** Thu Dec 15, 2005 11:24 pm **Posts:** 5

Sometime I think we move two steps backward in an attempt to move one step forward. I believe this change will break many applications, it has already busted two applications I am aware of, plus it caught me in my new development. (We make extensive passing of primitives for performance, and autoboxing, as I understand it, will kill us by instantiating then dropping wrapper classes.)

Tim