Spring Pageable NamedNativeQuery

user_12926947 0
Viewed 301

I've run into an issue where it is difficult to find good examples, or reference for this kind of issue. I am trying to add paging to my already existing NamedNativeQuery. From what I understand, you need to provide another NamedNativeQuery for paging with .count added to the end of the name.

My current query looks something like this:

@NamedNativeQuery(
    name="Foo.getObj",
    resultSetMapping="obj",
    query="SELECT item1, item2, item3, ... 
        FROM Table1 t1 Join Table2 t2 On t1.id = t2.id ...
        WHERE t1.num = :num" 
)

This is my attempted paging query:

@NamedNativeQuery(
    name="Foo.getObj.count",
    resultSetMapping="obj",
    query="SELECT COUNT(item1, item2, item3, ...) 
        FROM Table1 t1 Join Table2 t2 On t1.id = t2.id ...
        WHERE t1.num = :num" 
)

my caller is as so:

@Query(nativeQuery=true)
public List<Foo>retrieveFoo(ServiceRequest req){
    return repo.retrieveFoo(@Param("num") foo.getNum(), PageRequest.of(0, req.pages)); //req.pages is never less than 1
}

I am still very new to Spring, JPA, and Hibernate, but I am limited to implementing it in this way, or someway similar to this. I am able to test it with Pageable being new Pageable(0, 1). But any amount larger than 1 throws this exception: org.springframework.orm.jpa.JpaSystemException "Unable to call class service.sources.Foo constructor; nested exception is org.HibernateException: Unable to call class service.sources.Foo constructor"

Any tips to handle paging NamedNativeQueries or documentation would be helpful, I cant seem to find many examples for paging with these kinds of queries.

jpa hibernate spring java
1 Answers

Turns out this way of calling a pageable @NamedNativeQuery is correct. One of the arguments being returned from the database was null and was being assigned to primitive when it should have been to the class variant of it. So rather than assigning it to type Integer I was assigning it to type int. This caused it to fail on finding the proper constructor for my Query. It also just so happens the num I was trying for this case had that null variable for that argument, and the other nums didn't. After fixing replacing the primitive with the class variant, everything works fine.

user_129269470
Related