时间戳

首页 > 解决方案 > Spring Data JPA Criteria API - 如何在两个实体中按字段进行搜索？

spring - Spring Data JPA Criteria API - 如何在两个实体中按字段进行搜索？

问题描述

我有 3 个不同的实体：Partner是主要实体，Offer是合作伙伴的报价（多对一），Location是合作伙伴中的任何位置。

@Entity
class ObjectLocation(@ManyToOne var place: Place, var partnerId: String) {

    constructor() : this(Place(), "")

    @Id
    var id: String = IDGenerator.longId()
    // other fields omitted
    ...
}

@Entity
class Offer(var partnerId: String, ...) {
    constructor() : this(...)

    @Id
    var id: String = IDGenerator.longId()
    ...
}

@Entity
class Partner(...) {
    constructor() : this(...)

    @Id
    var id: String = IDGenerator.longId()
    ...
}

因此，我需要按 Place 条件查找所有 Offer。我试过这个：

Specification {
    root: Root<Offer>, criteriaQuery: CriteriaQuery<*>, criteriaBuilder: CriteriaBuilder ->

    val objectLocationRoot = criteriaQuery.from(ObjectLocation::class.java)

    val objectCityId: Expression<String> = objectLocationRoot
        .get<Place>("place")
        .get<City>("parentCity")
        .get<String>("id")

    val objectPartnerId: Expression<String> = objectLocationRoot.get<String>("partnerId")

    val offerPartnerId: Expression<String> = root.get<String>("partnerId")

    val goodLocations: Predicate = criteriaBuilder.equal(objectCityId, cityId)

    val objQuery: Subquery<String> = criteriaQuery.subquery(String::class.java)
        .select(objectPartnerId)
        .where(goodLocations)

    return@Specification criteriaBuilder.equal(objQuery, offerPartnerId)
}

但这只给了我以下例外：

antlr.NoViableAltException: unexpected token: where
    at org.hibernate.hql.internal.antlr.HqlBaseParser.fromRange(HqlBaseParser.java:1519) [hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.fromClause(HqlBaseParser.java:1343) [hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.selectFrom(HqlBaseParser.java:1063) [hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:748) [hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.subQuery(HqlBaseParser.java:3910) [hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.primaryExpression(HqlBaseParser.java:967) [hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.atom(HqlBaseParser.java:3549) [hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    ....

org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: where near line 1, column 175 
[select generatedAlias0 from com.arkell.entity.Offer as generatedAlias0, com.arkell.entity.geo.ObjectLocation as generatedAlias1 where 
(select generatedAlias1.partnerId from  where generatedAlias1.place.parentCity.id=:param0)=generatedAlias0.partnerId]; nested exception is java.lang.IllegalArgumentException: 
org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: where near line 1, column 175 [select generatedAlias0
from com.arkell.entity.Offer as generatedAlias0, com.arkell.entity.geo.ObjectLocation as generatedAlias1 where 
(select generatedAlias1.partnerId from  where generatedAlias1.place.parentCity.id=:param0)=generatedAlias0.partnerId]

    at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:367)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:227)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:527)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:153)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:135)

如何通过合作伙伴所在城市找到所有报价？

标签： springhibernatejpacriteria-api

解决方案

哦，哇，这很容易。

Specification {
    root: Root<Offer>, criteriaQuery: CriteriaQuery<*>, criteriaBuilder: CriteriaBuilder ->

    val objectLocationRoot: Root<ObjectLocation> = criteriaQuery.distinct(true).from(ObjectLocation::class.java)

    return@Specification criteriaBuilder.and(
            criteriaBuilder.equal(root.get<String>("partnerId"), objectLocationRoot.get<String>("partnerId")),
            criteriaBuilder.equal(objectLocationRoot.get<Place>("place")
                  .get<City>("parentCity")
                  .get<String>("id")("streetType"), cityId)
    )
}

推荐阅读