JPA命名查询匹配IN子句中的元组列表

问题描述

使用Oracle 11g的spring data jpa 1.4.3.

spring data jpa 1.4.3 with Oracle 11g.

我有一个这样的实体:

class LinkRecord {
  String value;
  int linkType;
  ...
}

我正在使用(value，linkType)作为复合索引.对于给定的(v，t)元组列表，我们需要选择数据库中的所有记录，以使value = v，linkType = t.

I am using (value, linkType) as a composite index.For a given list of (v, t) tuples, we need to select all the records in the DB so that value = v, linkType = t.

基本上，我想构建此查询:

Basically, I want to build this query:

SELECT * FROM LINK_RECORD WHERE (VALUE, LINK_TYPE) IN (('value1', 0), ('value2', 25), ...)

IN子句中的列表作为参数传入.

where the list in the IN clause is passed in as a param.

由于我们要处理大量数据，因此不希望一一查询元组.

Since we're working with a large volume of data, it would be very undesirable to query for the tuples one by one.

在我的存储库中，我已经尝试过:

In my repository I've tried this:

@Query("select r from LinkRecord r where (r.value, r.linkType) in :keys")
List<LinkRecord> findByValueAndType(@Param("keys")List<List<Object>> keys);

其中keys是(长度为2的列表)的列表.这使我得到ORA_00920:无效的关系运算符.

where keys is a list of (lists of length 2). This gets me ORA_00920: invalid relational operator.

有什么办法可以使用命名查询来完成这项工作?还是我必须求助于本机sql?

Is there any way to make this work using a named query? Or do I have to resort to native sql?

感谢您的帮助.

推荐答案

答案为时已晚，但也许其他人也有同样的问题.这是我的工作示例之一.在这里，我需要搜索与给定组合键匹配的所有条目:

The answer is too late, but maybe some1 else has the same problem. This is one of my working examples. Here I need to search for all entries that match a given composite key:

实体....

@Entity
@NamedQueries({
    @NamedQuery(name = "Article.findByIdAndAccessId", query = "SELECT a FROM Article a WHERE a.articlePk IN (:articlePks) ORDER BY a.articlePk.article")
})
@Table(name = "ARTICLE")
public class Article implements Serializable
{
    private static final long serialVersionUID = 1L;

    @EmbeddedId
    private ArticlePk articlePk = new ArticlePk();

    @Column(name = "art_amount")
    private Float amount;

    @Column(name = "art_unit")
    private String unit;

    public Article()
    {
    }

    //more code
}

PK班....

@Embeddable
public class ArticlePk implements Serializable
{
    private static final long serialVersionUID = 1L;

    @Column(name = "art_article")
    private String article;

    @Column(name = "art_acc_identifier")
    private Long identifier;

    public ArticlePk()
    {
    }

    public ArticlePk(String article, Long identifier)
    {
        this.article = article;
        this.identifier = identifier;
    }

    @Override
    public boolean equals(Object other)
    {
        if (this == other)
        {
            return true;
        }
        if (!(other instanceof ArticlePk))
        {
            return false;
        }

        ArticlePk castOther = (ArticlePk)other;
        return this.article.equals(castOther.article) && this.identifier.equals(castOther.identifier);
    }

    @Override
    public int hashCode()
    {
        final int prime = 31;
        int hash = 17;
        hash = hash * prime + this.article.hashCode();
        hash = hash * prime + this.identifier.hashCode();

        return hash;
    }

    //more code
}

通过...调用.

TypedQuery<Article> queryArticle = entityManager.createNamedQuery("Article.findByIdAndAccessId", Article.class);
queryArticle.setParameter("articlePks", articlePks);
List<Article> articles = queryArticle.getResultList();

哪里......

articlePks是List<ArticlePk>.

这篇关于JPA命名查询匹配IN子句中的元组列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！