Spring Pageable does not translate @Column name

Question

I have Entity object :

@Entity(name = "table")
public class SomeEntity {

    @Id
    @Column(name = "id_column_name")
    public final BigDecimal entityId;

    @Column(name = "table_column_name")
    public final String entityFieldName;

}

And I have database view defined like this:

CREATE OR REPLACE FORCE EDITIONABLE VIEW "V_TABLE" ("ID_COLUMN_NAME", "TABLE_COLUMN_NAME", "SOME_OTHER_COLUMN") AS ... (some SQL magic)

And I have repository with custom query:

@RepositoryRestResource
interface SomeEntityRepository extends PagingAndSortingRepository<SomeEntity, BigDecimal> {

    @Query(value = "select id_column_name, table_column_name FROM V_TABLE where some_other_column = ?#{#parameter} order by ?#{#pageable}",
        countQuery = "SELECT count(*) from V_TABLE v where  some_other_column = ?#{#parameter}",
        nativeQuery = true)
    Page<SomeEntity> findBySomeParameter(@Param("parameter") long parameter, Pageable pageable);
}

Everything works fine when I request standard data with url: http://localhost:8080/someEntity/search/findBySomeParameter?parameter=25&page=0&size=20

But when I add sorting information it doesn't work: http://localhost:8080/someEntity/search/findBySomeParameter?parameter=25&page=0&size=20&sort=entityFieldName,asc will throw following exception (I'm using Oracle database):

Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "ENTITYFIELDNAME": invalid identifier

It seems like sorting field are not translated with @Column(name), but are inlined into SQL query.

Is there any way to make pageable sort translated, so that it will use not field name but column name?

Pedro · Accepted Answer · 2018-07-22T13:54:34.277

This article sheds light on the issue. Read from section 3.1 on.

Apparently dynamic sorting is not supported for native queries. Actually, if you change your findBySomeParameter method to take a Sort instead of a Pageable you will get org.springframework.data.jpa.repository.query.InvalidJpaQueryMethodException: Cannot use native queries with dynamic sorting.

Using pageable you don't get the exception, and pagination actually seems to work fine, but dynamic sorting does not substitute the column name as you found. Looks to me like the only solution is to use JPQL instead of native query, which is not a problem as long as the query you need to make is the one you provide. You would need to map the view though to a SomeEntityView class in order to use JPQL.

EDIT I thought the issue was not documented but it actually is here in the official doc

Spring Data JPA does not currently support dynamic sorting for native queries, because it would have to manipulate the actual query declared, which it cannot do reliably for native SQL. You can, however, use native queries for pagination by specifying the count query yourself, as shown in the following example:

Your solution was one of mine workaround for that problem. I thought that there is some Claas/annotation provided by spring team that could help. Anyway thanks for response! :) — Maciek Murawski, Jul 22 '18 at 13:46
I just added the official docs warning on this issue, so I guess they have no workaround for this — Pedro, Jul 22 '18 at 13:55

score 0 · Answer 2 · answered Mar 03 '21 at 21:20

This workaround works for me in SpringBoot 2.4.3:

    @PersistenceContext
    private EntityManager entityManager;

// an object ptoperty name to a column name adapter
    private Pageable adaptSortColumnNames(Pageable pageable) {
        if (pageable.getSort().isSorted()) {
            SessionFactory sessionFactory;
            if (entityManager == null || (sessionFactory = entityManager.getEntityManagerFactory().unwrap(SessionFactory.class)) == null)
                return pageable;
            AbstractEntityPersister persister = (AbstractEntityPersister) ((MetamodelImplementor) sessionFactory.getMetamodel()).entityPersister(CommentEntity.class);
            Sort adaptedSort = pageable.getSort().get().limit(1).map(order -> {
                String propertyName = order.getProperty();
                String columnName = persister.getPropertyColumnNames(propertyName)[0];
                return Sort.by(order.getDirection(), columnName);
            }).findFirst().get();
            return PageRequest.of(pageable.getPageNumber(), pageable.getPageSize(), adaptedSort);
        }
        return pageable;
    }

    @GetMapping()
    public ResponseEntity<PagedResponse<CommentResponse>> findByTextContainingFts(@RequestParam(value = "text", required = false) String text, Pageable pageable) {
// apply this adapter in controller
        pageable = adaptSortColumnNames(pageable);
        Page<CommentEntity> page = commentRepository.find(text, pageable);
        return ResponseEntity.ok().body(domainMapper.fromPageToPagedResponse(page));
    }

Spring Pageable does not translate @Column name

2 Answers2

Linked