JPA查询语言中的IN和=运算符

Question

JPA查询语言中的IN和=运算符

6

我是一位有用的助手，可以为您翻译文本。

我有一个关于JPA查询的问题。有两个表，即帖子表和标签表，在帖子和标签之间存在多对多的关系。

现在我想编写一个查询，当选择多个标签时，应选择与这些标签相关联的所有帖子。例如，

post1 has tags friends and motivation
post2 has tags motivation and pune
post3 has tag boxing

如果选择了标签“friends”和“pune”，则应检索“post1”和“post2” 如果选择了标签“boxing”，则只应检索“post3” 如果选择了标签“boxing”和“motivation”，则应检索全部三篇文章。

我尝试了以下方法。

SELECT DISTINCT p FROM Post p JOIN p.tags tags WHERE p.tags IN :tags

但是它会产生验证器错误，提示如下：

The state field path 'p.tags' cannot be resolved to a collection type.

如果我像这样尝试

SELECT DISTINCT p FROM Post p JOIN p.tags tags WHERE p.tags = :tags

然后它可以正常编译，但在传递标签列表后会出现错误。

 java.lang.IllegalArgumentException: You have attempted to set a value of type class java.util.ArrayList for parameter tags with expected type of class com.justme.model.entities.Tag from query string SELECT DISTINCT p FROM Post p JOIN p.tags tags WHERE p.tags = :tags.

感谢您阅读至此 :) 请问您可以指导我如何实现上述结果吗？我的持久性提供者是eclipseLink。这是“Post”实体。

@Entity
@NamedQueries({
    @NamedQuery(name = "Post.selectAllPosts", query = "SELECT p FROM Post p ORDER BY p.dateCreated DESC"),
    @NamedQuery(name = "Post.selectPostForUser", query = "SELECT p FROM Post p WHERE p.user = :user ORDER BY p.dateCreated DESC"),
    @NamedQuery(name = "Post.selectPostsByTags", query = "SELECT DISTINCT p FROM Post p JOIN p.tags tags WHERE p.tags IN :tags") })
public class Post implements Serializable {
private static final long serialVersionUID = 1L;

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int idpost;

@Lob
private String content;

private String title;

// bi-directional many-to-one association to User
@ManyToOne(cascade = CascadeType.PERSIST)
@JoinColumn(name = "iduser")
private User user;

// bi-directional many-to-many association to Tag
@ManyToMany(cascade = CascadeType.PERSIST)
@JoinTable(name = "post_tag", joinColumns = @JoinColumn(name = "idpost"),         inverseJoinColumns = @JoinColumn(name = "idtag"))
private List<Tag> tags = new ArrayList<Tag>();

@Temporal(TemporalType.DATE)
private Date date = null;
@Temporal(TemporalType.TIMESTAMP)
private Date dateCreated = new Date();

public Post() {
}

public int getIdpost() {
    return this.idpost;
}

public void setIdpost(int idpost) {
    this.idpost = idpost;
}

public String getContent() {
    return this.content;
}

public void setContent(String content) {
    this.content = content;
}

public String getTitle() {
    return this.title;
}

public void setTitle(String title) {
    this.title = title;
}

public User getUser() {
    return this.user;
}

public void setUser(User user) {
    this.user = user;
}

public List<Tag> getTags() {
    return this.tags;
}

public void setTags(List<Tag> tags) {
    this.tags = tags;
}

public Date getDate() {
    return date;
}

public void setDate(Date date) {
    this.date = date;
}

public Date getDateCreated() {
    return dateCreated;
}

public void setDateCreated(Date dateCreated) {
    this.dateCreated = dateCreated;
}

@Override
public String toString() {
    return "Post [idpost=" + idpost + ", content=" + content + ", title="
            + title + ", date=" + date + "]";
}

}

这是标签实体。

@Entity
@NamedQueries({
    @NamedQuery(name = "Tag.selectTags", query = "SELECT tag FROM Tag tag WHERE tag.tagName LIKE :keyword"),
    @NamedQuery(name = "Tag.selectMatchingTags", query = "SELECT t.tagName FROM Tag t WHERE t.tagName LIKE :keyword"),
    @NamedQuery(name = "Tag.selectTagByName", query = "SELECT tag FROM Tag tag WHERE tag.tagName = :tagName"),
    @NamedQuery(name = "Tag.selectTagsForAllPosts", query = "SELECT DISTINCT tag FROM Tag tag, Post post JOIN tag.posts posts WHERE post.user = :user")})

public class Tag implements Serializable {
private static final long serialVersionUID = 1L;

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int idtag;

private String tagName;

// bi-directional many-to-many association to Post
@ManyToMany(mappedBy = "tags", cascade = CascadeType.PERSIST)
private List<Post> posts;


public Tag() {

}

public Tag(String tagName) {
    this.tagName = tagName;
}


public int getIdtag() {
    return this.idtag;
}

public void setIdtag(int idtag) {
    this.idtag = idtag;
}

public String getTagName() {
    return this.tagName;
}

public void setTagName(String tagName) {
    this.tagName = tagName;
}

public List<Post> getPosts() {
    return this.posts;
}

public void setPosts(List<Post> posts) {
    this.posts = posts;
}

@Override
public String toString() {
    return tagName;
}

}

- Prasad Kharkar

请问您能否发布一下您的“Post”实体的代码？看起来问题可能出在那里。 - kostja

@kostja 根据您的建议添加了实体代码。您可以注意到，NamedQuery Tag.selectTagForAllPosts 可以成功检索用户的所有标签。我认为从帖子中检索标签也应该类似，所以我尝试了上述方法，但它并不起作用。 - Prasad Kharkar

我认为这个简化版本可以工作：SELECT DISTINCT p FROM Post p WHERE p.tags IN :tags。 - kostja

@kostja 谢谢你的回答，但是当我输入你给我的查询时，它仍然显示如上所述的验证错误。“p.tags”状态字段路径无法解析为集合类型。 - Prasad Kharkar

@kostja，你在说什么啊？你绝对没有浪费我的时间。我想感谢你的时间。 - Prasad Kharkar

显示剩余7条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- acdcjunior · Accepted Answer

尝试：

    ...
    @NamedQuery(name = "Post.selectPostsByTags", query =
       "SELECT DISTINCT p FROM Post p JOIN p.tags tags WHERE tags IN (:tags)") })
public class Post implements Serializable {
    ...

使用方法如下：

@PersistenceContext
public EntityManager em;
...
List<Tag> ltags = new ArrayList<Tag>();
ltags.add(tagOne);
ltags.add(tagTwo);
List<?> list = em.createNamedQuery("Post.selectPostsByTags")
                                                    .setParameter("tags", ltags)
                                                    .getResultList();

for (Object object : list) {
    System.out.println("Results: "+object);
}