Hibernate pagination not working when using native queries (Named query exists but its result type is not compatible)

Question

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

Answer 1

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.