Use tuple queries for findBy(Specification) using interface projections

[SchlauFuchs]

Hi,
I am running into this problem using a Specification query - all entity fields are queried instead of the ones we really name in the projection interface, and the interface definition does not cause the hibernate entity manager to eager join the queried entities. This is with Spring Boot 3.1.3.

Here is my Projection:

package ourdomain.repository.projection

import ourdomain.FeeItemStatusCode
import java.math.BigDecimal
import java.time.LocalDateTime

interface FeeItemSearchProjection {

    fun getId(): Int
    fun getFeeHeader(): FeeItemHeaderProjection

    fun getOrderItem(): ProductOrderItemProjection?

    fun getStatus(): FeeItemStatusCode

    fun getDescription(): String

    fun getService(): String
    fun getQuantity(): Int

    fun getUnitAmount(): BigDecimal
    fun getRefDatetime(): LocalDateTime
    fun getCredit(): Boolean
    fun getNotes(): String?

    interface FeeItemHeaderProjection {
        fun getId(): Int
        fun getGroup(): String

        fun getTransactionId(): Int?

        fun getClientReference(): String?

        fun getUser(): UserProjection

        fun getFirm(): UserProjection?
    }

    interface ProductOrderItemProjection {
        fun getProductId(): String
        fun getProductType(): String

        fun getProductSubtype(): String
    }

    interface UserProjection {
        fun getId(): String
    }
}

And this is how I generate the Specification:

package ourdomain.repository

import ourdomain.model.User_
import ourdomain.model.FeeHeader_
import ourdomain.model.FeeItem
import ourdomain.model.FeeItem_
import org.springframework.data.jpa.domain.Specification
import java.math.BigDecimal
import java.time.LocalDateTime
import java.util.Objects

data class FeeItemSpecs(
    val userId: String?,
    val firmId: String?,
    val clientReference: String?,
    val fromDate: LocalDateTime?,
    val toDate: LocalDateTime?,
    val zeroChargeItemsIncluded: Boolean
) {
    val specification by lazy {
        withUsername(userId)
            .and(withFirmName(firmId))
            .and(withClientReference(clientReference))
            .and(billedBetween(fromDate, toDate))
            .and(withZeroAmountPositions(zeroChargeItemsIncluded))
    }

    companion object {
        fun withUsername(userId: String?): Specification<FeeItem> {
            return Specification<FeeItem> { root, _, builder ->
                userId?.let {
                    val userPath = root.get(FeeItem_.feeHeader).get(FeeHeader_.user)
                    builder.equal(userPath.get(User_.id), it)
                }
            }
        }

        fun withFirmName(firmId: String?): Specification<FeeItem> {
            return Specification<FeeItem> { root, _, builder ->
                firmId?.let {
                    val userPath = root.get(FeeItem_.feeHeader).get(FeeHeader_.firm)
                    builder.equal(userPath.get(User_.id), it)
                }
            }
        }

        fun billedBetween(fromDate: LocalDateTime?, toDate: LocalDateTime?): Specification<FeeItem> {
            return Specification<FeeItem> { root, _, builder ->
                builder.and(
                    *listOfNotNull(
                        fromDate?.let {
                            builder.greaterThanOrEqualTo(root.get(FeeItem_.refDatetime), it)
                        },
                        toDate?.let {
                            builder.lessThan(root.get(FeeItem_.refDatetime), it)
                        }
                    ).toTypedArray()
                )
            }
        }

        fun withClientReference(clientReference: String?): Specification<FeeItem> {
            return Specification<FeeItem> { root, _, builder ->
                clientReference?.let {
                    val clientRef = root.get(FeeItem_.feeHeader).get(FeeHeader_.clientReference)
                    builder.equal(clientRef, it)
                }
            }
        }

        fun withZeroAmountPositions(zeroChargeItemsIncluded: Boolean): Specification<FeeItem> {
            return Specification<FeeItem> { root, _, builder ->
                if (zeroChargeItemsIncluded) {
                    null
                } else {
                    builder.greaterThan(root.get(FeeItem_.unitAmount), BigDecimal.ZERO)
                }
            }
        }
    }

    override fun equals(other: Any?) = (this === other) || other is FeeItemSpecs &&
        userId == other.userId &&
        firmId == other.firmId &&
        clientReference == other.clientReference &&
        fromDate == other.fromDate &&
        toDate == other.toDate &&
        zeroChargeItemsIncluded == other.zeroChargeItemsIncluded

    override fun hashCode() = Objects.hash(userId, firmId, clientReference, fromDate, toDate, zeroChargeItemsIncluded)
}

As far as I can tell this projection does not contain collections at all. not only does it query all fields, it also kind of does not eager join, leading to a lot of queries in hibernate.

Here is what it looks in hibernate trace:

select skip ? first ? f1_0.id,f1_0.credit,
        f1_0.debtor_code,
        f1_0.description,
        f1_0.export_date,
        f1_0.fhr_id,
        f1_0.fsc_id,
        f1_0.notes,
        f1_0.off_code_lodged,
        f1_0.order_item_id,
        f1_0.quantity,
        f1_0.ref_datetime,
        f1_0.region_code,
        f1_0.service,
        f1_0.status,
        f1_0.unit_amount
from fee_item f1_0 join fee_header f2_0 on f2_0.id=f1_0.fhr_id
where f2_0.usr_id=? and f2_0.usr_id_firm=? and f1_0.ref_datetime>=? and f1_0.ref_datetime <? and f1_0.unit_amount >?;

-- n times:
select f1_0.id,
       f1_0.group,
       f1_0.client_reference,
       f1_0.db_user,
       f1_0.usr_id_firm,
       f1_0.group,
       f1_0.off_code_org,
       f1_0.receipt_no,
       f1_0.total_amount,
       f1_0.usr_id,
       f1_0.dlg_id,
       r1_0.id,
       r1_0.fhr_id,
       s1_0.wrk_id,
       s1_0.fhr_id
from fee_header f1_0
         left join request r1_0 on f1_0.id = r1_0.fhr_id
         left join survey s1_0 on f1_0.id = s1_0.fhr_id
where f1_0.id = ?

and here the service level method we use:

    @Transactional(readOnly = true)
    fun searchFees(
        searchDefinition: FeeItemSpecs,
        pageRequest: Pageable
    ): PaginatedFeeResults {
        val result: Page<FeeItemSearchProjection> =
            feeItemRepository.findBy(searchDefinition.specification) { queryFunction: FetchableFluentQuery<FeeItem> ->
                queryFunction.`as`(FeeItemSearchProjection::class.java).page(pageRequest)
            }
// [...]

The entity relationship we are joining here:

@Entity
class FeeItem {
// [...]
    @ManyToOne
    @JoinColumn(name = "fhr_id")
    lateinit var feeHeader: FeeHeader
// [...]
}

@Entity
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(name = "group", discriminatorType = DiscriminatorType.STRING, length = 4)
class FeeHeader {
// [...]
    @OneToMany(mappedBy = "feeHeader", cascade = [CascadeType.ALL])
    var items: MutableList<FeeItem> = mutableListOf()

    @JoinColumn(name = "usr_id")
    @ManyToOne(fetch = FetchType.LAZY)
    lateinit var user: User

    @JoinColumn(name = "usr_id_firm")
    @ManyToOne(fetch = FetchType.LAZY)
    var firm: User? = null
// [...]
}

[haydenrear]

Last time I checked, specification didn't work with projection... https://github.com/pramoth/specification-with-projection

…

On Wed, Sep 6, 2023 at 7:19 PM Kai ***@***.***> wrote: Hi, I am running into this problem using a Specification query - all entity fields are queried instead of the ones we really name in the projection interface, and the interface definition does not cause the hibernate entity manager to eager join the queried entities Here is my Projection: package ourdomain.repository.projection import ourdomain.FeeItemStatusCodeimport java.math.BigDecimalimport java.time.LocalDateTime interface FeeItemSearchProjection { fun getId(): Int fun getFeeHeader(): FeeItemHeaderProjection fun getOrderItem(): ProductOrderItemProjection? fun getStatus(): FeeItemStatusCode fun getDescription(): String fun getService(): String fun getQuantity(): Int fun getUnitAmount(): BigDecimal fun getRefDatetime(): LocalDateTime fun getCredit(): Boolean fun getNotes(): String? interface FeeItemHeaderProjection { fun getId(): Int fun getGroup(): String fun getTransactionId(): Int? fun getClientReference(): String? fun getUser(): UserProjection fun getFirm(): UserProjection? } interface ProductOrderItemProjection { fun getProductId(): String fun getProductType(): String fun getProductSubtype(): String } interface UserProjection { fun getId(): String } } And this is how I generate the Specification: package ourdomain.repository import ourdomain.model.User_import ourdomain.model.FeeHeader_import ourdomain.model.FeeItemimport ourdomain.model.FeeItem_import org.springframework.data.jpa.domain.Specificationimport java.math.BigDecimalimport java.time.LocalDateTimeimport java.util.Objects data class FeeItemSpecs( val userId: String?, val firmId: String?, val clientReference: String?, val fromDate: LocalDateTime?, val toDate: LocalDateTime?, val zeroChargeItemsIncluded: Boolean ) { val specification by lazy { withUsername(userId) .and(withFirmName(firmId)) .and(withClientReference(clientReference)) .and(billedBetween(fromDate, toDate)) .and(withZeroAmountPositions(zeroChargeItemsIncluded)) } companion object { fun withUsername(userId: String?): Specification<FeeItem> { return Specification<FeeItem> { root, _, builder -> userId?.let { val userPath = root.get(FeeItem_.feeHeader).get(FeeHeader_.user) builder.equal(userPath.get(User_.id), it) } } } fun withFirmName(firmId: String?): Specification<FeeItem> { return Specification<FeeItem> { root, _, builder -> firmId?.let { val userPath = root.get(FeeItem_.feeHeader).get(FeeHeader_.firm) builder.equal(userPath.get(User_.id), it) } } } fun billedBetween(fromDate: LocalDateTime?, toDate: LocalDateTime?): Specification<FeeItem> { return Specification<FeeItem> { root, _, builder -> builder.and( *listOfNotNull( fromDate?.let { builder.greaterThanOrEqualTo(root.get(FeeItem_.refDatetime), it) }, toDate?.let { builder.lessThan(root.get(FeeItem_.refDatetime), it) } ).toTypedArray() ) } } fun withClientReference(clientReference: String?): Specification<FeeItem> { return Specification<FeeItem> { root, _, builder -> clientReference?.let { val clientRef = root.get(FeeItem_.feeHeader).get(FeeHeader_.clientReference) builder.equal(clientRef, it) } } } fun withZeroAmountPositions(zeroChargeItemsIncluded: Boolean): Specification<FeeItem> { return Specification<FeeItem> { root, _, builder -> if (zeroChargeItemsIncluded) { null } else { builder.greaterThan(root.get(FeeItem_.unitAmount), BigDecimal.ZERO) } } } } override fun equals(other: Any?) = (this === other) || other is FeeItemSpecs && userId == other.userId && firmId == other.firmId && clientReference == other.clientReference && fromDate == other.fromDate && toDate == other.toDate && zeroChargeItemsIncluded == other.zeroChargeItemsIncluded override fun hashCode() = Objects.hash(userId, firmId, clientReference, fromDate, toDate, zeroChargeItemsIncluded) } As far as I can tell this projection does not contain collections at all. not only does it query all fields, it also kind of does not eager join, leading to a lot of queries in hibernate. Here is what it looks in hibernate trace: select skip ? first ? f1_0.id,f1_0.credit, f1_0.debtor_code, f1_0.description, f1_0.export_date, f1_0.fhr_id, f1_0.fsc_id, f1_0.notes, f1_0.off_code_lodged, f1_0.order_item_id, f1_0.quantity, f1_0.ref_datetime, f1_0.region_code, f1_0.service, f1_0.status, f1_0.unit_amountfrom fee_item f1_0 join fee_header f2_0 on f2_0.id=f1_0.fhr_idwhere f2_0.usr_id=? and f2_0.usr_id_firm=? and f1_0.ref_datetime>=? and f1_0.ref_datetime <? and f1_0.unit_amount >?; -- n times:select f1_0.id, f1_0.group, f1_0.client_reference, f1_0.db_user, f1_0.usr_id_firm, f1_0.group, f1_0.off_code_org, f1_0.receipt_no, f1_0.total_amount, f1_0.usr_id, f1_0.dlg_id, r1_0.id, r1_0.fhr_id, s1_0.wrk_id, s1_0.fhr_idfrom fee_header f1_0 left join request r1_0 on f1_0.id = r1_0.fhr_id left join survey s1_0 on f1_0.id = s1_0.fhr_idwhere f1_0.id = ? and here the service level method we use: @transactional(readOnly = true) fun searchFees( searchDefinition: FeeItemSpecs, pageRequest: Pageable ): PaginatedFeeResults { val result: Page<FeeItemSearchProjection> = feeItemRepository.findBy(searchDefinition.specification) { queryFunction: FetchableFluentQuery<FeeItem> -> queryFunction.`as`(FeeItemSearchProjection::class.java).page(pageRequest) }// [...] The entity relationship we are joining here: @EntityClass FeeItem {// [...] @manytoone @joincolumn(name = "fhr_id") lateinit var feeHeader: FeeHeader// [...] } @entity @Inheritance(strategy = InheritanceType.SINGLE_TABLE) @DiscriminatorColumn(name = "group", discriminatorType = DiscriminatorType.STRING, length = 4)class FeeHeader {// [...] @onetomany(mappedBy = "feeHeader", cascade = [CascadeType.ALL]) var items: MutableList<FeeItem> = mutableListOf() @joincolumn(name = "usr_id") @manytoone(fetch = FetchType.LAZY) lateinit var user: User @joincolumn(name = "usr_id_firm") @manytoone(fetch = FetchType.LAZY) var firm: User? = null// [...] } — Reply to this email directly, view it on GitHub <#3144>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AKQI5S3A2QBGH3PHPV7RIQTXZEAHHANCNFSM6AAAAAA4OBJASY> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

-- Hayden Rear (971) 888-3862

[SchlauFuchs]

does that still work for SpringBoot 3 and Spring Data JPA 3?

[haydenrear]

Well I think I may have mis-spoke. In fact, if you use querydsl you can include the projection in that specification (the querydsl specification interface). I found a ticket tracking it here. However, when I updated to Spring Boot 3, I remember it being a bit of a pain, however here is an answer.