@Query的查询结果与预期类型不匹配 [java.util.Collection (n/a)]

huangapple go评论130阅读模式
英文:

@Query did not match expected type [java.util.Collection (n/a)]

问题

以下是翻译好的内容:

我有一个名为Tasks的表,其中包含以下列:author、responsible 和 observers,它们都是指向Users表中用户ID的外键。我还有一个表示Users和Tasks之间多对多关系的表,只包含两列:task_id 和 user_id。我正在使用Spring Boot中的@Query来从Tasks中选择所有行。我的问题是,我想要选择所有当前授权用户作为观察者的任务。

我的任务模型:

@Entity
@Table
public class Task {

    private String title;
    private String description;

    @Column(name = "delete_status")
    private Boolean deleteStatus;

    @OneToOne
    @JoinColumn(name = "task_status_id")
    private TaskStatus status;

    @OneToMany(mappedBy = "task")
    @LazyCollection(LazyCollectionOption.FALSE)
    private List<Comment> comments;

    @JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd HH:mm:ss")
    @Column(name = "due_date")
    private Date dueDate;

    @ManyToOne
    @JoinColumn(name = "author_id", updatable = false)
    private User author;

    @ManyToOne
    @JoinColumn(name = "responsible_id")
    private User responsible;

    @JsonManagedReference
    @ManyToMany(cascade = CascadeType.ALL)
    @JoinTable(name = "observers_users",
            joinColumns = {@JoinColumn(name = "observers_id", referencedColumnName = "id")},
            inverseJoinColumns = {@JoinColumn(name = "users_id", referencedColumnName = "id")})
    @LazyCollection(LazyCollectionOption.FALSE)
    private List<User> observers;
}

我的查询:

@Query("FROM Task t WHERE :user MEMBER OF t.observers ORDER BY t.id DESC")
List<Task> findWhereUserObserver(
        @Param("user") User user
);

无论我如何更改查询,我始终收到相同的错误:参数值 [User (...)] 与预期类型 [java.util.Collection (n / a)] 不匹配

因为t.observers的类型是List,我无法弄清楚如何在SQL查询的WHERE条件中使用它。我想要在这个列表中找到当前授权用户,但不太明白在SQL查询中应该如何做到这一点。或者我应该完全避免这样做吗?

英文:

I have a table named Tasks with following columns in it: author, responsible and observers all of them are FKs of user ids which are located in Users table. And I have the table representing many-to-many relation between Users and Tasks which contains only two columns: task_id and user_id. I am using @Query from Spring boot to select all the rows from Tasks. My issue is that I want to select all the tasks in which currently authorized user is being observer.

My task model:

@Entity
@Table
public class Task {

private String title;

private String description;

@Column(name = &quot;delete_status&quot;)
private Boolean deleteStatus;

@OneToOne
@JoinColumn(name = &quot;task_status_id&quot;)
private TaskStatus status;

@OneToMany(mappedBy = &quot;task&quot;)
@LazyCollection(LazyCollectionOption.FALSE)
private List&lt;Comment&gt; comments;

@JsonFormat(shape = JsonFormat.Shape.STRING, pattern = &quot;yyyy-MM-dd HH:mm:ss&quot;)
@Column(name = &quot;due_date&quot;)
private Date dueDate;

@ManyToOne
@JoinColumn(name = &quot;author_id&quot;, updatable = false)
private User author;

@ManyToOne
@JoinColumn(name = &quot;responsible_id&quot;)
private User responsible;

@JsonManagedReference
@ManyToMany(cascade = CascadeType.ALL)
@JoinTable(name = &quot;observers_users&quot;,
        joinColumns = {@JoinColumn(name = &quot;observers_id&quot;, referencedColumnName = &quot;id&quot;)},
        inverseJoinColumns = {@JoinColumn(name = &quot;users_id&quot;, referencedColumnName = &quot;id&quot;)})
@LazyCollection(LazyCollectionOption.FALSE)
private List&lt;User&gt; observers;
}

My query:

@Query(&quot;FROM Task t WHERE t.observers = ?1 ORDER BY t.id DESC&quot;)
List&lt;Task&gt; findWhereUserObserver(
        @Param(&quot;observers&quot;) List&lt;User&gt; user
);

No matter how I change the query, I keep getting the same error: Parameter value [User (...)] did not match expected type [java.util.Collection (n / a)]

Because t.observers is type of List<User> I can't figure out how to use it within SQL query in WHERE condition. I want to find my currently authorized user in this list but don't understand how should I do so in SQL query. Or should I at all?

答案1

得分: 1

好的,以下是翻译好的内容:

当你说像 T.OBSERVERS IN 这样的东西时,意味着你会在一个列表中拥有多个观察者,比如 IN(User1,User2,User3)等。但是你只有 1 个用户。请查看 SQL 的 IN 语句,链接:https://www.w3schools.com/sql/sql_in.asp

英文:

Well when you say sth. like T.OBSERVERS IN it means that you will have more than one observer in a list such as IN (User1, User2, User3) etc. But you only have 1 User. Checkout SQL IN statement https://www.w3schools.com/sql/sql_in.asp

答案2

得分: 0

我知道这有点晚了,但是…
你必须通过左连接在 T.OBSERVERS 集合中进行搜索。具体操作如下:

"SELECT t FROM Task t LEFT JOIN t.observers o WHERE o.id = ?1 ORDER BY t.id DESC")

英文:

I know it's a little bit too late, but...
You have to search through the collection T.OBSERVERS via left join. This is what to do:

"SELECT t FROM Task t LEFT JOIN t.observers o where o.id= ?1 ORDER BY t.id DESC")

huangapple
  • 本文由 发表于 2020年8月26日 21:33:18
  • 转载请务必保留本文链接:https://go.coder-hub.com/63598838.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定