学科表有两个字段id和descripiton用户表有众多字段不罗列了
Field中有SET集合users
User中有SET集合fields
现在要查掌握id like '520%'女性用户
我这样写:
from User u,Field f where u.gender='m' in (select f.users from Field where f.id like '520%')
持续报错!
我项目代码中恰好有个多对多关联映射类:User(用户)和Role(角色)其中User类中有roles集合而Role类中有users集合我用我代码测试了番
首先写了下面这句:
select user from User user where user.sex='男' and user in (select role.users from Role role where role.id>5)
这样不行报错原因是HibernateHQL语句中“in条件”句如:x in(‘a’’b’’c’)要求是x是(‘a’’b’’c’)中个元素而select role.users这样查出来是集合集合它内部元素应该是个集合:<user> in (select role.users…)而不是个对象: user in (select role.users…)
可惜是HQL语句没有<user> in (select role.users….)这样子句后来查了下Hibernate参考文档得到解决思路方法原来HibernateHQL语句中“in条件”中可加入”elements”关键词即上面查询语句变成:
select user from User userRole role where user.sex='男' and user in elements(role.users) and role.id>5
这样就行了
不过还有个小问题就是这样查询出来会出现许多重复记录原因是查询出每个符合role后会通过role.getUsers查出users集合这样当然就可能出现有几个role拥有相同user现象很容易解决加多个“distinct ”关键词就行
select distinct user from User userRole role where user.sex='男' and user in elements(role.users) and role.id>5
好了解决了那位CSDN中朋友提问题也解决了它hql语句如下:
select distinct u from User u,Field f where u.gender='m' and u in elements(f.users) and f.id like '520%'
最新评论