使用Oracle 11g的
spring data jpa 1.4.3.
我有这样一个实体:
- class LinkRecord {
- String value;
- int linkType;
- ...
- }
我使用(value,linkType)作为复合索引.
对于给定的(v,t)元组列表,我们需要选择DB中的所有记录,以便value = v,linkType = t.
基本上,我想构建此查询:
- SELECT * FROM LINK_RECORD WHERE (VALUE,LINK_TYPE) IN (('value1',0),('value2',25),...)
IN子句中的列表作为参数传入的位置.
由于我们正在处理大量数据,因此逐个查询元组是非常不可取的.
在我的存储库中我试过这个:
- @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:无效的关系运算符.
有没有办法使用命名查询来完成这项工作?或者我是否必须使用本机sql?
谢谢你的帮助.
解决方法
答案为时已晚,但也许其他人有同样的问题.这是我的一个工作示例.在这里,我需要搜索与给定复合键匹配的所有条目:
实体……
- @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>.