jpa条件查询，匹配作为参数传递的任何列表 [英] jpa criteria query, match any of list passed as parameter

问题描述

我正在尝试编写一种DAO方法，从中选择任意数量的具有匹配标签的Widget。因此在以下实体中：

I'm trying to write a DAO method where I select any number of Widgets that have a matching tag. So in entities like:

@Entity
public class Widget {
    @Id
    private id;

    @OneToMany (cascade = CascadeType.ALL)
    private List<Tag> tagList;
}


@Entity
public class Tag {
    @Id
    private int id;

    String tagValue;
}

在我的DAO中，我试图编写一种方法在字符串列表中

and in my DAO, I'm trying to write a method that will take in a list of strings

List<String> myList = new ArrayList<String>();
myList.add("tom");
myList.add("dick");
myList.add("harry");

到DAO

public List<Widget> getWidgetsMatchingTags(EntityManager entityManager, List<String> tagValues) {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<Widget> query = cb.createQuery(Widget.class);
    Root<Widget> widgetRoot = query.from(Widget.class);
    Join<Widget, Tag> tagJoin = widgetRoot.join(Widget_.tagList);

    // this works
    query.select(widgetRoot).where(cb.and(
           cb.equal(tagJoin.get(Tag_.value), "tom")));

   // but if I try to add another predicate, it does not work, 
   // it returns a list size of 0

   query.select(widgetRoot).where(cb.and(
           cb.equal(tagJoin.get(Tag_.value), "tom")),
           cb.equal(tagJoin.get(Tag_.value), "harry"));

}

希望能够匹配全部或任何给定的标签，以为那是cb.and（）或cb.or（）

would like to be able to be able to match all or any of the given tags, and thought that that would be a matter of cb.and() or cb.or()

我在Google周围搜索并找到了一些链接，在收藏上也是如此，也这，我认为这不是完全相关...我还浏览了pdf jpa文档，在线用户指南，但并未明确涵盖此内容。请让我知道我是否错过了某事

I google around and found a few link, like this on collections, also this which I don't think was totally relevant...I also went thru the pdf jpa documentation, the online user guide and did not see this covered explicitly. Please let me know if I missed something

还请注意，我需要匹配一个 tag值，因为字符串是标签的字段对象

Also note that I need to match a tag value, as the string is a field of the tag obj.

感谢任何帮助！

感谢任何帮助

推荐答案

假设标签上有Widget属性，例如：

Assuming there is a Widget property on the Tag, like this:

@ManyToOne
private Widget widget;

如果您想匹配任何标签，甚至不需要动态查询。使用 FluentJPA 可以这样实现：

In case you want to match any tag, you even don't need a dynamic query. With FluentJPA it can be realized like this:

List<Object> tags; // passed as a parameter

FluentQuery query = FluentJPA.SQL((Widget w,
                                   Tag tag) -> {
    SELECT(DISTINCT(w));
    FROM(w).JOIN(tag).ON(tag.getWidget() == w);
    WHERE(tags.contains(tag.getTagValue()));
});

query.createQuery(getEntityManager(), Widget.class).getResultList();

如果要匹配所有标签，则需要动态查询：

in case you want to match all the tags, you need a dynamic query:

public List<Widget> filterWidgets(List<String> tags) {
    Function1<Tag, Boolean> dynamicFilter = buildAnd(tags);

    FluentQuery query = FluentJPA.SQL((Widget w,
                                       Tag tag) -> {

        SELECT(DISTINCT(w));
        FROM(w).JOIN(tag).ON(tag.getWidget() == w);
        WHERE(dynamicFilter.apply(tag));
    });
    return query.createQuery(getEntityManager(), Widget.class).getResultList();
}

private Function1<Tag, Boolean> buildAnd(List<String> tags) {
    Function1<Tag, Boolean> criteria = Function1.TRUE();

    for (String tag : tags)
        criteria = criteria.and(t -> t.getTagValue() == parameter(tag));

    return criteria;
}

带有3个标记，这是结果查询：

with 3 tags this is the resulting query:

SELECT DISTINCT t0.*  
FROM Widget t0  INNER JOIN Tag t1  ON (t1.widget_id = t0.id) 
WHERE (((t1.tag_value = ?1) AND (t1.tag_value = ?2)) AND (t1.tag_value = ?3))

这篇关于jpa条件查询，匹配作为参数传递的任何列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！