Adding parameters to a @Subselect Entity using @FilterDef in Hibernate 6.3.1

Hibernate ORM
1

In Hibernate 5.6.4.Final, this code was working normally. (spring boot 2.6.3)

I’m adding the filter variable inside of @Subselect (calendar_id = :calendarFilter.calendarId), instead of adding the @Filter annotation.

@Entity
@Immutable
@Data
@FilterDef(
     name = "calendarFilter",
     parameters = @ParamDef(name = "calendarId", type = "long")
)
@Subselect(
    " SELECT ROW_NUMBER() OVER() AS row_number, description, " +
    " MIN(dater) AS from_date, MAX(dater) AS to_date " +
    " FROM paycal " +
    " WHERE status1 = 'H' AND calendar_id=:calendarFilter.calendarId " +
    " GROUP BY description")
public class HolidayQuery implements Serializable {
    @Id
    private Long rowNumber;
    private String description;
    private Instant fromDate;
    private Instant toDate;
}

When i call this function, the filter parameter was bound successfully

@Transactional(readOnly = true)
public Page<HolidayQueryDTO> findByCriteria(Long calendarId, Pageable page) {

    Session session = entityManager.unwrap(Session.class);
    Filter calendarFilter = session.enableFilter("calendarFilter");
    calendarFilter.setParameter("calendarId", calendarId);

    Page<HolidayQuery> result = holidayQueryRepository.findAll(page);

    session.disableFilter("calendarFilter");

    return result.map(holidayQueryMapper::toDto);
}

When I upgraded to Hibernate 6.3.1.Final (and spring boot 3.0.2), there is a little change in type value in @ParamDef.
Now, the filter variable (:calendarFilter.calendarId) inside of @Subselect is not bound anymore to the query.
I’m getting an exception (thrown on findAll method call).

@Entity
@Immutable
@Data
@FilterDef(
     name = "calendarFilter",
     parameters = @ParamDef(name = "calendarId", type = Long.class)
)
@Subselect(
    " SELECT ROW_NUMBER() OVER() AS row_number, description, " +
    " MIN(dater) AS from_date, MAX(dater) AS to_date " +
    " FROM paycal " +
    " WHERE status1 = 'H' AND calendar_id=:calendarFilter.calendarId " +
    " GROUP BY description")
public class HolidayQuery implements Serializable {
    @Id
    private Long rowNumber;
    private String description;
    private Instant fromDate;
    private Instant toDate;
}

The exception is:

{
    "type": "https://www.jhipster.tech/problem/problem-with-message",
    "title": "Internal Server Error",
    "status": 500,
    "detail": "JDBC exception executing SQL 
               [
                 select hq1_0.row_number,hq1_0.description,hq1_0.from_date,
                 hq1_0.to_date from (  SELECT ROW_NUMBER() OVER() AS row_number, 
                 description,  MIN(dater) AS from_date, MAX(dater) AS to_date  
                 FROM paycal  WHERE status1 = 'H' 
                 AND calendar_id=:calendarFilter.calendarId  
                 GROUP BY description ) hq1_0 offset ? rows fetch first ? rows only
               ] 
               [ERROR: syntax error at or near \":\"\n  Position: 234]
               [n/a]; 
               SQL [n/a]",
    "path": "/timesheet/api/holidays/1",
    "message": "error.http.500"
}

How to solve this issue ?

2

You were relying on unsupported behavior: @Subselect has nothing to do with filter parameters, they are only used for @Filters, and :calendarFilter.calendarId is not the correct syntax for a named parameter in Hibernate. This used to work by chance in previous versions, but it was never documented nor supported.

This was also recently reported here: [HHH-17703] - Hibernate JIRA.

I suggest using a parameterized native query, and mapping to your entity mapping through NativeQuery#addEntity or JPA’s @SqlResultSetMapping.