java - 在 where 语句中使用集合的 JPA 查询

Question

我在 MySQL 服务器 5.6 上使用 JPA 和 Spring Data。我有一个“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;
[...]

}

现在我想获取列表“hideForUsers”中没有确定“用户”的所有“Activity ”实体

@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);

这给了我一个异常(exception):

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

我特别注意到:

? not in ( . )

有什么建议可以解决吗？没有 AND ?4 NOT IN c.hideForUsers 语句的查询有效。

谢谢。

Answer 1

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