使用本机查询时，Hibernate分页不工作(已命名查询存在，但其结果类型不兼容)

Question

我的orm.xml中有两个已命名的本机查询，一个用于检索数据，另一个用于分页计数:

<named-native-query name="Person.findPeople.count">
<query>select count(*) from
from person
</query>
</named-native-query>

<named-native-query name="Person.findPeople">
<query>select first_name, last_name
from person
</query>
</named-native-query>

要加载这些数据，我有一个Spring data Repository，它加载数据的投影(我的实际代码比提供的示例更复杂):

@Query(nativeQuery = true)
fun findPeople(pageable: Pageable): Page<PersonFirstName>

现在，当我执行上面的代码时，我得到一个错误:

Caused by: java.lang.IllegalArgumentException: Named query exists but its result type is not compatible
at org.hibernate.internal.AbstractSharedSessionContract.resultClassChecking(AbstractSharedSessionContract.java:984) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
at org.hibernate.internal.AbstractSharedSessionContract.createNativeQuery(AbstractSharedSessionContract.java:942) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
at org.hibernate.internal.AbstractSharedSessionContract.buildQueryFromName(AbstractSharedSessionContract.java:920) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]

这是由Hibernate引起的，它不想将本机计数查询映射到一个Long。我已经将orm.xml中的named-native-query更改为named-query，这确实有效，但我不能在实际代码中使用它。

重现问题的完整代码如下(也可以在Github上找到):

@SpringBootApplication
class HibernateBugApplication : ApplicationRunner {
@Autowired
lateinit var personRepository: PersonRepository
override fun run(args: ApplicationArguments?) {
personRepository.saveAll(listOf(Person("a", "a1"), Person("b", "b1"), Person("c", "c1")))
personRepository.findPeople(Pageable.ofSize(2))
}
}
@Repository
interface PersonRepository : JpaRepository<Person, Long> {
@Query(nativeQuery = true)
fun findPeople(pageable: Pageable): Page<PersonFirstName>
}
interface PersonFirstName {
fun getName(): String
}
@Entity
class Person(
val firstName: String,
val lastName: String,
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
var id: Long? = null
)

和orm.xml:

<!-- This query works, but the named version does not.
<named-query name="Person.findPeople.count">
<query>select count(p) from Person p</query>
</named-query>
-->
<named-native-query name="Person.findPeople.count">
<query>select count(*) from
from person
</query>
</named-native-query>

<named-native-query name="Person.findPeople">
<query>select first_name, last_name
from person
</query>
</named-native-query>

我几乎认为这可能是Hibernate中的一个bug，但在报告之前，我想知道我的配置中是否遗漏了什么。

我用的是Spring 2.7.1, Hibernate 5.6.9, Kotlin 1.7.0

Answer 1

与解决方案类似，您需要为计数本机查询指定返回类型。
可以通过sql-result-set-mapping完成。
JPA 1.0和2.0版本

<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings version="2.0" xmlns="http://java.sun.com/xml/ns/persistence/orm"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm
http://java.sun.com/xml/ns/persistence/orm_2_0.xsd ">
<named-native-query name="Person.findPeople.count" result-set-mapping="cntColumnResult">
<query>select count(*) cnt from person</query>
</named-native-query>
<named-native-query name="Person.findPeople">
<query>select first_name, last_name from person</query>
</named-native-query>
<sql-result-set-mapping name="cntColumnResult">
<column-result name="cnt"/>
</sql-result-set-mapping>
</entity-mappings>

JPA 2.1版
将您的映射配置移到entity-mappings version="2.1"

<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence/orm"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence/orm
http://xmlns.jcp.org/xml/ns/persistence/orm_2_1.xsd">
<named-native-query name="Person.findPeople.count" result-set-mapping="cntColumnResult">
<query>select count(*) cnt from person</query>
</named-native-query>
<named-native-query name="Person.findPeople">
<query>select first_name, last_name from person</query>
</named-native-query>
<sql-result-set-mapping name="cntColumnResult">
<column-result name="cnt" class="java.lang.Long"/>
</sql-result-set-mapping>
</entity-mappings>

或

<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence/orm"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence/orm
http://xmlns.jcp.org/xml/ns/persistence/orm_2_1.xsd">
<named-native-query name="Person.findPeople.count" result-set-mapping="cntConstructorResult">
<query>select count(*) cnt from person</query>
</named-native-query>
<named-native-query name="Person.findPeople">
<query>select first_name, last_name from person</query>
</named-native-query>
<sql-result-set-mapping name="cntConstructorResult">
<constructor-result target-class="java.lang.Long">
<column name="cnt" class="java.lang.Long"/>
</constructor-result>
</sql-result-set-mapping>
</entity-mappings>

我认为这是spring-data-jpa的一个缺陷或者可能是一个新特性。源代码行有问题

countQuery = em.createNamedQuery(countQueryName, Long.class);

如果在创建查询时不传递Long.class类型，则不需要在映射配置中为计数查询指定确切的类型。Hibernate在这种情况下工作得很好。
可能的解决方案示例:为本机而不是键入的创建原始查询:

Query query = getQueryMethod().isNativeQuery()
? em.createNativeQuery(queryString)
: em.createQuery(queryString, Long.class);

所以如果你有时间，请发布一个新的问题为spring-data-jpa项目，这样一个小的查询更正将大大简化映射配置。