java - 在where语句中具有集合的JPA查询

我在MySQL服务器5.6上使用JPA和Spring数据。
我有一个“Campaign”实体，其中有一个名为“list hideForUsers”的“User”实体列表；它引用一个连接表。

@Entity
@Table(name = "CAMPAIGN")
@NamedQuery(name="Campaign.findAll", query="SELECT c FROM Campaign c")
public class Campaign implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="CAMPAIGN_ID", columnDefinition="INT(11)")
    private long campaignId;
[...]

    //uni-directional many-to-many association to User
    //@ManyToMany
    @ManyToMany(fetch = FetchType.EAGER)
    @Fetch(value = FetchMode.SUBSELECT)
    @JoinTable(
        name="campaign_user"
        , joinColumns={
            @JoinColumn(name="CAMPAIGN_ID")
            }
        , inverseJoinColumns={
            @JoinColumn(name="USER_ID")
            }
        )
    private List<User> hideForUsers;

[...]
}


@Entity
@Table(name = "USER")
@NamedQuery(name="User.findAll", query="SELECT u FROM User u")
public class User implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @Column(name="USER_ID", columnDefinition="INT(11)")
    private long userId;
[...]

}

现在我想得到所有的“活动”实体，在“隐藏者”列表中没有一个确定的“用户”

@Query("SELECT c FROM Campaign c "
        + "WHERE c.organization = ?1 AND ?2 <= c.endDate AND ?3 >= c.startDate "
        + "AND c.targetLead > c.targetProduct "
        + "AND c.targetLead > c.targetCustomer "
        + "AND c.targetLead > c.targetBrand "
        + "AND c.deleted=false "
        + "AND ?4 NOT IN c.hideForUsers ")
public Page<Campaign> findByOrganizationDateRangeTargetLeadUserPaged(Organization organization, Date startDate, Date endDate, User user, Pageable pageable);

这给了我一个例外：

2016-11-04 15:48:04,342 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http--0.0.0.0-443-3) SQL Error: 1064, SQLState: 42000
2016-11-04 15:48:04,343 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http--0.0.0.0-443-3) You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax to use near '))' at line 1

如果启用hibernate SQL日志，我可以看到：

2016-11-04 15:48:04,286 INFO  [stdout] (http--0.0.0.0-443-3) Hibernate:
2016-11-04 15:48:04,287 INFO  [stdout] (http--0.0.0.0-443-3)     select
2016-11-04 15:48:04,288 INFO  [stdout] (http--0.0.0.0-443-3)         count(campaign0_.CAMPAIGN_ID) as col_0_0_
2016-11-04 15:48:04,290 INFO  [stdout] (http--0.0.0.0-443-3)     from
2016-11-04 15:48:04,291 INFO  [stdout] (http--0.0.0.0-443-3)         CAMPAIGN campaign0_ cross
2016-11-04 15:48:04,293 INFO  [stdout] (http--0.0.0.0-443-3)     join
2016-11-04 15:48:04,294 INFO  [stdout] (http--0.0.0.0-443-3)         campaign_user hideforuse1_, USER user2_
2016-11-04 15:48:04,297 INFO  [stdout] (http--0.0.0.0-443-3)     where
2016-11-04 15:48:04,299 INFO  [stdout] (http--0.0.0.0-443-3)         campaign0_.CAMPAIGN_ID=hideforuse1_.CAMPAIGN_ID
2016-11-04 15:48:04,301 INFO  [stdout] (http--0.0.0.0-443-3)         and hideforuse1_.USER_ID=user2_.USER_ID
2016-11-04 15:48:04,303 INFO  [stdout] (http--0.0.0.0-443-3)         and campaign0_.ORGANIZATION_ID=?
2016-11-04 15:48:04,304 INFO  [stdout] (http--0.0.0.0-443-3)         and ?<=campaign0_.END_DATE
2016-11-04 15:48:04,306 INFO  [stdout] (http--0.0.0.0-443-3)         and ?>=campaign0_.START_DATE
2016-11-04 15:48:04,307 INFO  [stdout] (http--0.0.0.0-443-3)         and campaign0_.TARGET_LEAD>campaign0_.TARGET_PRODUCT
2016-11-04 15:48:04,310 INFO  [stdout] (http--0.0.0.0-443-3)         and campaign0_.TARGET_LEAD>campaign0_.TARGET_CUSTOMER
2016-11-04 15:48:04,312 INFO  [stdout] (http--0.0.0.0-443-3)         and campaign0_.TARGET_LEAD>campaign0_.TARGET_BRAND
2016-11-04 15:48:04,313 INFO  [stdout] (http--0.0.0.0-443-3)         and campaign0_.DELETED=0
2016-11-04 15:48:04,314 INFO  [stdout] (http--0.0.0.0-443-3)         and (
2016-11-04 15:48:04,315 INFO  [stdout] (http--0.0.0.0-443-3)             ? not in  (
2016-11-04 15:48:04,316 INFO  [stdout] (http--0.0.0.0-443-3)                 .
2016-11-04 15:48:04,317 INFO  [stdout] (http--0.0.0.0-443-3)             )
2016-11-04 15:48:04,317 INFO  [stdout] (http--0.0.0.0-443-3)         )

2016-11-04 15:48:04,342 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http--0.0.0.0-443-3) SQL Error: 1064, SQLState: 42000
2016-11-04 15:48:04,343 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http--0.0.0.0-443-3) You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax to use near '))' at line 1

我特别注意到：
? 不在（.）
有什么解决的建议吗？
不带AND ?4 NOT IN c.hideForUsers语句的查询可以工作。
谢谢您。

最佳答案

尝试使用+ "AND ?4 NOT MEMBER OF c.hideForUsers ")而不是+ "AND ?4 NOT IN c.hideForUsers ")