HQL not working "in" with multiple values

Question

I have hibernate which goes something like this.

String queryString = "from MFunction as mFunction where mFunction.functionKey in " 
            + "((select mRole.enterprise  from MRole as mRole where mRole.roleKey=:role), " 
            + "(select mRole2.project from MRole as mRole2 where mRole2.roleKey=:role ), " 
            + "(select mRole3.technology  from MRole  as mRole3 where mRole3.roleKey=:role ))";

But the hibernate query takes in only the first value.

Hibernate: select mfunction0_.FunctionKey as Function1_73_, mfunction0_.`Add` as Add2_73_, mfunction0_.`Audit` as Audit3_73_, mfunction0_.ClientKey as ClientKey73_, mfunction0_.CreatedBy as CreatedBy73_, mfunction0_.CreatedTs as CreatedTs73_, mfunction0_.`Delete` as Delete7_73_, mfunction0_.`Edit` as Edit8_73_, mfunction0_.`Financial` as Financial9_73_, mfunction0_.FunctionName as Functio10_73_, mfunction0_.`General` as General11_73_, mfunction0_.Level as Level73_, mfunction0_.LevelKey as LevelKey73_, mfunction0_.LogicalDeleteTms as Logical14_73_, mfunction0_.UpdatedBy as UpdatedBy73_, mfunction0_.UpdatedTs as UpdatedTs73_, mfunction0_.`View` as View17_73_ from appanalytixdb.M_Function mfunction0_ where mfunction0_.FunctionKey in (select mrole1_.Enterprise from appanalytixdb.M_Role mrole1_ where mrole1_.RoleKey=?)

Hibernate version: 4.1.8.Final

Answer 1

Your problem is that you created a list of lists, and you should create a single list of functionkeys.

I see only 2 options:

1. Change one 'in' to 3 statements connected with or.

String queryString = "from MFunction as mFunction where mFunction.functionKey in (select mRole.enterprise from MRole as mRole where mRole.roleKey=:role) or mFunction.functionKey in (select mRole2.project from MRole as mRole2 where mRole2.roleKey=:role ) or mFunction.functionKey in (select mRole3.technology from MRole as mRole3 where mRole3.roleKey=:role )";

2. Best solution would be to preselect all projects,technologies,enterprise and union them together (to avoid duplicates). But hibernate does not yet support union, according to this post Hibernate Union alternatives. If you have not so many entries in your tables, I would create a view, and used my query on it. Views can be quite cheap and easy to use in this scenarios.