我有2个实体:用户和事件。每个映射到适当的表。我还有第三张表,user_event
因为这两个实体具有多对多关系。我需要从数据库中选择用户参与的所有事件。
事件:
@Entity
@Table(name = "event")
public class Event extends AbstractPersistable<Long> {
@ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
@JoinTable(name = "user_event",
joinColumns = @JoinColumn(name = "event_id", referencedColumnName = "id"),
inverseJoinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"))
private Collection<User> participants;
用户:
@Entity
@Table(name = "user")
public class User extends AbstractPersistable<Long> {
private String nickname;
user_event
表在Java代码中没有实体。我试过这个查询:
@Query("select e from Event e join user_event ue on ue.event_id = e.id where ue.user_id = :userId and e.startDate > CURRENT_TIMESTAMP")
Page<Event> findAllForUser(Pageable pageable, @Param("userId") Long userId);
但是此查询在应用程序启动时导致异常:
java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: Path expected for join! [select e from Event e join user_event ue on ue.event_id = e.id where ue.user_id = :userId and e.startDate > CURRENT_TIMESTAMP]
在MySQL Workbench中,我尝试这样做:
select * from event e join user_event ue on e.id = ue.event_id where ue.user_id = 1 and e.startDate > now();
而且有效。但是如何为spring数据创建良好的工作查询呢?
SQL转储:
select count(event0_.id) as col_0_0_ from event event0_ inner join address address1_ on event0_.address_id=address1_.id
cross join user_event participan2_, user user3_ where event0_.id=participan2_.event_id and participan2_.user_id=user3_.id
and (? in (.)) and event0_.startDate>CURRENT_TIMESTAMP
@JoinTable(name =“ event_user_event”
但是在查询中您正在使用user_event
。我猜其中之一是拼写错误?
在您的查询中
select e
from Event e join user_event ue on ue.event_id = e.id
where ue.user_id = :userId and e.startDate > CURRENT_TIMESTAMP"
您使用user_event
的不是实体(因为它在异常消息中已正确指向)。因此查询应如下所示:
select e
from Event e join e.participants u
where u.id = :userId and e.startDate > CURRENT_TIMESTAMP
假设您的User
实体有一个名为的属性id
。并且此查询应返回与用户关联的所有事件:userId
。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句