英文:
@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
英文:
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 = "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;
}
My query:
@Query("FROM Task t WHERE t.observers = ?1 ORDER BY t.id DESC")
List<Task> findWhereUserObserver(
@Param("observers") List<User> 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")
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论