[FIXED] Count query generated by returning Page<> from Repository method crashes on org.hibernate.QueryException: unexpected char: '`'

Issue

At great peril, I am trying to combine the use of a native query, @SqlResultSetMapper + non-entity POJO (MyDto), and a repository method that takes a Pageable as a param and returns Page. After overcoming a number of hurdles, it's almost working:

• My entity class is called Order, as is the database table
• My DTO class, to which I am mapping the results of the query, is MyDto
• My repository is defined as public interface MyRepository extends JpaRepository<Order, Long>

In my repository, if I use: List<MyDto> findResults(Pageable page) I get a list of 1 page of results; everything works fine.

However, if I use: Page<MyDto> findResults(Pageable page), this happens:

 Error occurred while processing the request: org.springframework.dao.InvalidDataAccessApiUsageException: 
org.hibernate.QueryException: unexpected char: '`' [select count(o) from `order` o ...

(the query continues after "..." and there are no backticks anywhere else in it)

Returning Page causes Hibernate to generate a query it can't parse (but should be able to)

I don't think it's worth it to supply the @NamedNativeQuery stuff because it works and doesn't impact how returning Page causes this count query to generate. I'm inclined to think this a Hibernate bug, but am hoping there is a work-around.

How can I fix this? The backticks are necessary, as order is a MySQL keyword, and even if they weren't, I fail to see why it can't parse it (especially because it generated it).

Solution

Well, I actually figured it out. The count query generated by returning Page from the repository method can be avoided by supplying your own via the following conversion:

Convert this:

@Entity
@Table(name=`order`)
@SqlResultSetMapping(
    name="myDtoMapping",
    classes={
            @ConstructorResult(
                    targetClass=MyDto.class,
                    columns={
                            @ColumnResult(name="id", type=Long.class),
                            //Other columns...
                    }
            )
    }
)
@NamedNativeQuery(name="Order.findtResults", query=myVariablePointingToQuery, resultSetMapping="myDtoMapping")
public class Order{
...

To this:

@Entity
@Table(name=`order`)
@SqlResultSetMappings({
    @SqlResultSetMapping(
            name="myDtoMapping",
            classes={
                    @ConstructorResult(
                            targetClass=MyDto.class,
                            columns={
                                    @ColumnResult(name="id", type=Long.class),
                                    //Other columns...
                            }
                    )
            }
    ),
    @SqlResultSetMapping(name="myDtoMapping.count", columns=@ColumnResult(name="cnt"))}
)
@NamedNativeQueries({
    @NamedNativeQuery(name="Order.findResults", query=myVariablePointingToQuery, resultSetMapping="myDtoMapping"),
    @NamedNativeQuery(name = "Order.findResults.count", query=myVariablePointingToCOUNTINGQuery, resultSetMapping = "myDtoMapping.count")
})

Note that there is a SqlResultSetMapping and @NamedNativeQuery for count now, as well as a custom count query, which by the way is defined as

"select count(*) as cnt \n" //newline is important!

because even with a trailing space, the query comes out to "select count(*) as cntfrom". A bug.

But now everything works!

Answered By - Xenalin