I using @Query and Pageable has error: Variables are only allowed when ordering by an expression referencing a column name

Question

I tried to query:

 @Query(value = "SELECT * FROM BookingOffice WHERE tripId in (select Trip.tripId from Trip Where Trip.desfination like ?#{#desfination})   ORDER BY ?#{#pageable}",
   countQuery = "SELECT COUNT(*) FROM BookingOffice WHERE tripId in (select Trip.tripId from Trip Where Trip.desfination like ?#{#desfination})",
   nativeQuery = true)
 public Page<BookingOffice> findAllByTrip(String desfination, Pageable pageable);

My PageRequest:

Sort sort = new Sort(Direction.DESC, "officeId");
PageRequest pageRequest = new PageRequest(page, 7, sort);

Hibernate Query on runtime

**> Hibernate:

SELECT
    * 
FROM
    BookingOffice 
WHERE
    tripId in (
        select
            Trip.tripId 
        from
            Trip 
        Where
            Trip.desfination like ?
    )   
ORDER BY
    ?,
    officeId desc offset 0 rows fetch next ? rows only**

ERROR: May 17, 2020 12:41:29 PM org.apache.catalina.core.StandardWrapperValve invoke SEVERE: Servlet.service() for servlet [dispatcher] in context with path [/CarPark] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause com.microsoft.sqlserver.jdbc.SQLServerException: The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:254) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1608) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:578) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:508) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7240) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2869) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:243) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:218) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:434) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60) at org.hibernate.loader.Loader.getResultSet(Loader.java:2171) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1934) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1896) at org.hibernate.loader.Loader.doQuery(Loader.java:936) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:339) at org.hibernate.loader.Loader.doList(Loader.java:2693) at org.hibernate.loader.Loader.doList(Loader.java:2676) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2510) at org.hibernate.loader.Loader.list(Loader.java:2505) at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:338) at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2262) at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:1069) at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:173) at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1529) at org.hibernate.query.Query.getResultList(Query.java:165) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) ....

**My database**

Please help me! Thanks

You can add your image inline, please follow instructions here: https://meta.stackoverflow.com/a/344853/3519504 — Sandeep Kumar, May 16 '20 at 17:43

score 0 · Accepted Answer · answered May 16 '20 at 17:10

0

I think the problem is that you are using Pageable object in order by clause of your select query. It expects a column name for ordering the results.

answered May 16 '20 at 17:10

Anshul Agarwal

16
1

Thank for your answer but can you review my PageRequest. Sort sort = new Sort(Direction.DESC, "officeId"); PageRequest pageRequest = new PageRequest(page, 7, sort);, it not work, Thanks! – TiNET May 17 '20 at 05:47
Pass officeId as a parameter to your query function directly instead of passing it in PageRequest. PageRequest pageRequest = new PageRequest(page, 7); @Query(value = "SELECT * FROM BookingOffice WHERE tripId in (select Trip.tripId from Trip Where Trip.desfination like ?#{#desfination}) ORDER BY ?#{#sortColumn} DESC", countQuery = "SELECT COUNT(*) FROM BookingOffice WHERE tripId in (select Trip.tripId from Trip Where Trip.desfination like ?#{#desfination})", nativeQuery = true) public Page findAllByTrip(String desfination, String sortColumn, Pageable pageable); – Anshul Agarwal May 18 '20 at 16:53

score 0 · Answer 2 · answered May 17 '20 at 13:21

Please try like below, I have done same kind of implementation in my project and it's working fine.

@Query(value = "SELECT * FROM BookingOffice WHERE tripId in (select Trip.tripId from Trip Where Trip.desfination like %?1%}))",
            nativeQuery = true)
public Page<BookingOffice> findAllByTrip(String desfination, Pageable pageable);

I using @Query and Pageable has error: Variables are only allowed when ordering by an expression referencing a column name

2 Answers2