Spring Pageable 不翻译@Column 名称 [英] Spring Pageable does not translate @Column name

问题描述

我有实体对象:

@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);
} 

当我使用 url 请求标准数据时，一切正常:http://localhost:8080/someEntity/search/findBySomeParameter?parameter=25&page=0&size=20

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

但是当我添加排序信息时它不起作用:http://localhost:8080/someEntity/search/findBySomeParameter?parameter=25&page=0&size=20&sort=entityFieldName,asc将抛出以下异常(我使用的是 Oracle 数据库):

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

排序字段似乎没有用 @Column(name) 翻译，而是内联到 SQL 查询中.

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?

推荐答案

这篇文章 阐明了这个问题.从第 3.1 节开始阅读.

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

本机查询显然不支持动态排序.实际上，如果您将 findBySomeParameter 方法更改为采用 Sort 而不是 Pageable，您将得到 org.springframework.data.jpa.repository.query.InvalidJpaQueryMethodException:不能使用动态排序的原生查询.

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.

使用 pageable 不会出现异常，而且分页实际上似乎工作正常，但动态排序不会替换您发现的列名.在我看来，唯一的解决方案是使用 JPQL 而不是本机查询，只要您需要进行的查询是您提供的查询，这不是问题.您需要将视图映射到 SomeEntityView 类才能使用 JPQL.

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 目前不支持对原生查询进行动态排序，因为它必须操作声明的实际查询，而对于原生 SQL，它不能可靠地做到这一点.但是，您可以通过自己指定计数查询来使用本机查询进行分页，如下例所示:

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:

这篇关于Spring Pageable 不翻译@Column 名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！