JpaRepository when used @Query, add the

[schotten]

I use the SpringBoot and JPA, but when i updated the spring-boot-starter-parent to 2.5.15 from 2.7.17.
After the update, my app thorw the exception when execute one especific query from the database by JPA (spring-data-jpa) with @query.

In sumary, i have this repository

public interface BulkDrawingGenerationRepository<T extends Serializable> extends JpaRepository<BulkDrawingGeneration, Long>, JpaSpecificationExecutor<BulkDrawingGeneration> {
    @Query(value = "SELECT bdg.* FROM BULK_DRAWING_GENERATION bdg INNER JOIN (SELECT PROCESS_ID, MAX(ID) AS MaxId FROM BULK_DRAWING_GENERATION GROUP BY PROCESS_ID) groupedbdg ON bdg.PROCESS_ID = groupedbdg.PROCESS_ID AND bdg.ID = groupedbdg.MaxId WHERE (:processId IS NULL OR bdg.PROCESS_ID LIKE %:processId%) AND (:userName IS NULL OR bdg.USER_NAME LIKE %:userName%) ORDER BY bdg.DT_PROCESS DESC",
            countQuery = "SELECT COUNT(bdg.ID) FROM BULK_DRAWING_GENERATION bdg INNER JOIN (SELECT PROCESS_ID, MAX(ID) AS MaxId FROM BULK_DRAWING_GENERATION GROUP BY PROCESS_ID) groupedbdg ON bdg.PROCESS_ID = groupedbdg.PROCESS_ID AND bdg.ID = groupedbdg.MaxId WHERE (:processId IS NULL OR bdg.PROCESS_ID LIKE %:processId%) AND (:userName IS NULL OR bdg.USER_NAME LIKE %:userName%) ORDER BY bdg.DT_PROCESS DESC",
            nativeQuery = true)
    Page<BulkDrawingGeneration> findMaxIdsByDistinctProcessId(Pageable pageable, @Param("processId") String processId, @Param("userName") String userName);
}

My entity of database:

package net.weg.maestro.wdd3d.document.service.drawing.generation;

import javax.persistence.*;
import java.io.Serializable;
import java.time.ZonedDateTime;

@Entity
public class BulkDrawingGeneration implements Serializable {

    @Id
    @GeneratedValue(generator = "BULK_DRAWING_GENERATION_SEQ")
    @SequenceGenerator(name = "BULK_DRAWING_GENERATION_SEQ", sequenceName = "BULK_DRAWING_GENERATION_SEQ", allocationSize = 1)
    private Long id;

    private Long configurationRootId;

    private Long queueId;

    private String grouperId;

    private String processId;

    private String grouperRevision;

    private String messageError;

    private String userName;

    private ZonedDateTime dtProcess;

    private boolean generateDrawing;

    private boolean middlePriority;

    @Enumerated(EnumType.STRING)
    private BulkDrawingGenerationStatusType status = BulkDrawingGenerationStatusType.NONSTARTER;

    @Enumerated(EnumType.STRING)
    private BulkDrawingGenerationStatusType documentStatus = BulkDrawingGenerationStatusType.NONSTARTER;


    public BulkDrawingGeneration() {
        middlePriority = false;
    }

    public Long getConfigurationRootId() {
        return configurationRootId;
    }

    public void setConfigurationRootId(Long projectId) {
        this.configurationRootId = projectId;
    }

    public String getGrouperId() {
        return grouperId;
    }

    public void setGrouperId(String grouperId) {
        this.grouperId = grouperId;
    }

    public String getGrouperRevision() {
        return grouperRevision;
    }

    public void setGrouperRevision(String grouperRevision) {
        this.grouperRevision = grouperRevision;
    }

    public ZonedDateTime getDtProcess() {
        return dtProcess;
    }

    public void setDtProcess(ZonedDateTime dtProcess) {
        this.dtProcess = dtProcess;
    }

    public BulkDrawingGenerationStatusType getStatus() {
        return status;
    }

    public void setStatus(BulkDrawingGenerationStatusType bulkDrawingGenerationStatusType) {
        this.status = bulkDrawingGenerationStatusType;
    }

    public Long getId() {
        return id;
    }

    public Long getQueueId() {
        return queueId;
    }

    public void setQueueId(Long queueId) {
        this.queueId = queueId;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getMessageError() {
        return messageError;
    }

    public void setMessageError(String messageError) {
        this.messageError = messageError;
    }

    public String getProcessId() {
        return processId;
    }

    public void setProcessId(String processId) {
        this.processId = processId;
    }

    public BulkDrawingGenerationStatusType getDocumentStatus() {
        return documentStatus;
    }

    public void setDocumentStatus(BulkDrawingGenerationStatusType documentStatus) {
        this.documentStatus = documentStatus;
    }

    public boolean isGenerateDrawing() {
        return generateDrawing;
    }

    public void setGenerateDrawing(boolean generateDrawing) {
        this.generateDrawing = generateDrawing;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public boolean isMiddlePriority() {
        return middlePriority;
    }

    public void setMiddlePriority(boolean middlePriority) {
        this.middlePriority = middlePriority;
    }
}

When, the system execute this query with any value in the param processId and userName the database oracle throw the exception:

Checked the SQL executed is different that the named Query in JPA.
@query the JPARepository:

"SELECT bdg.* FROM BULK_DRAWING_GENERATION bdg INNER JOIN (SELECT PROCESS_ID, MAX(ID) AS MaxId FROM BULK_DRAWING_GENERATION GROUP BY PROCESS_ID) groupedbdg ON bdg.PROCESS_ID = groupedbdg.PROCESS_ID AND bdg.ID = groupedbdg.MaxId WHERE (:processId IS NULL OR bdg.PROCESS_ID LIKE %:processId%) AND (:userName IS NULL OR bdg.USER_NAME LIKE %:userName%) ORDER BY bdg.DT_PROCESS DESC"

SQL executed inside the database:

"SELECT bdg.* FROM BULK_DRAWING_GENERATION bdg INNER JOIN (SELECT PROCESS_ID, MAX(ID) AS MaxId FROM BULK_DRAWING_GENERATION GROUP BY PROCESS_ID) groupedbdg ON bdg.PROCESS_ID = groupedbdg.PROCESS_ID AND bdg.ID = groupedbdg.MaxId WHERE (:1  IS NULL OR bdg.PROCESS_ID LIKE CONCAT('%',:2 ,'%')) AND (:3  IS NULL OR bdg.USER_NAME LIKE CONCAT('%',:4 ,'%')) ORDER BY bdg.DT_PROCESS DESC, bdg.id desc fetch first :5  rows only"

Observe, the SQL executed have in the end of command the fetch first :5 rows only. I not put this command in the @query.

Can you help me?

[gregturn]

This looks it would benefit from 2cd3aca, however that patch was not suitable for backporting to 2.7.x.

Thoughts @mp911de?

[gregturn]

You are always free to use a custom implementation. When limited by whatever reason, the docs at https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.custom-implementations show how you can leverage custom solutions but still hook your solution into your repository.

[rishiraj88]

Thanks, @gregturn , for citing the doc link.

[schotten]

From what I understand, this feature no longer works, however, isn't there a workaround?

[schotten]

wrong closed issue...

[mp911de]

As Greg mentioned, we typically do not backport enhancements. Also, that one was more elaborate and introduces quite some risk, hence we do not want to introduce the risk of breaking an otherwise stable version line.

Please also note that Spring Data 2.7 is no longer supported and we recommend upgrading to Spring Data JPA 3.1 at your earliest convenience.