I have a two named native queries in my orm.xml, one for retrieving the data, and one for doing the count for my pagination:
<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>
To load this data, I have a Spring Data Repository, which loads a projection of the data (my actual code is more complex than the provided example):
@Query(nativeQuery = true)
fun findPeople(pageable: Pageable): Page<PersonFirstName>
Now, when I execute the above code, I'm getting an error:
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]
This is caused by Hibernate, which doesn't want to map the native count query to a Long. I've changed the named-native-query in my orm.xml to a named-query, and that does work, but I can't use that in my actual code.
The full code to reproduce the issue is this (and can also be found on 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
)
And the 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>
I almost think this may be a bug in Hibernate, but before reporting it there I'd love to know if I'm not missing something in my configuration.
I'm using Spring 2.7.1, Hibernate 5.6.9, and Kotlin 1.7.0
Like solution you need to specify the return type for your count native query.
It can be done via sql-result-set-mapping.
JPA version 1.0 and 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 version 2.1
Move forward your mapping config to 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>
OR
<?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>
I think this is a defect or possibly a new feature in spring-data-jpa. Problematic source code line.
countQuery = em.createNamedQuery(countQueryName, Long.class);
If Long.class type will not be passed during query creation we will not need to specify an exact type for the count query in the mapping config. Hibernate is working well in this case.
Example of possible solution create raw query for native instead of typed:
Query query = getQueryMethod().isNativeQuery()
? em.createNativeQuery(queryString)
: em.createQuery(queryString, Long.class);
So if you have time please post a new issue for spring-data-jpa project, such a small query correction will significantly simplify the mapping config.