Parametrised order in spring-data-jdbc

Question

I'm struggling with making order parametrised. The best would be to have

... order by :orderColumn :orderDirection ...

Is it even possible to turn:

    @Query("select * from Document where folderId = :folderId AND documentType = :documentType"
            + " order by created desc limit :limit")
    List<Document> findByFolderIdAndDocumentTypeOrderByWithLimit(Long folderId, String documentType,
                                                                     String orderColumn, Integer limit);

Into:

    @Query("select * from Document where folderId = :folderId AND documentType = :documentType"
            + " order by :orderColumn desc limit :limit")
    List<Document> findByFolderIdAndDocumentTypeOrderByWithLimit(Long folderId, String documentType,
                                                                     String orderColumn, Integer limit);

I'm using spring-data-jdbc 1.1.3.REELASE version. Even doing it for just column name would help me a lot.

score 3 · Answer 1 · answered Feb 24 '20 at 06:44

Replacing a column name with a bind parameter is not possible. This is a limitation of JDBC, and possibly even of SQL.

What you can do is use an expression that evaluates to the value of different columns based on a bind parameter. Something like

... ORDER BY CASE
    WHEN :orderColumn = 'created' THEN created
    WHEN :orderColumn = 'updated' THEN updated
    WHEN :orderColumn = 'deleted' THEN deleted
    ELSE 1
END;

Note that this kind of constructed isn't well supported by query optimisers and therefore will not utilise any index that would otherwise apply.

If you require better support by the optimiser you can always write a custom query that you create dynamically. If you decide to do so, make sure you do not incorporate Strings from untrusted sources in your SQL statement to avoid SQL injection vulnerabilities.

score 2 · Answer 2 · answered Feb 23 '20 at 11:45

2

Use the PagingAndSortingRepository

  @Query("select * from Document where folderId = :folderId AND documentType = :documentType")
    List<Document> findByFolderIdAndDocumentTypeOrderByWithLimit(Long folderId, String documentType, Pageable pageable);

And call the method:

findByFolderIdAndDocumentTypeOrderByWithLimit(1,"Type", PageRequest.of(0, <limit>, Sort.by(<column name>).descending());

sume examples you can fine here

answered Feb 23 '20 at 11:45

Jens

67,715
15
98
113

yeah, but it's for jpa :( as far as I know spring-data-jdbc will provide ability to use it in 2.0.0 witch is now in M2. I have to use RELEASE version. In 1.X.X.RELEASE versions passing Pageable object is allowed by api but ignored in runtime ;/ – ppysz Feb 23 '20 at 11:52

score 0 · Answer 3 · answered Mar 13 '20 at 10:37

Until there will be version of spring-data-jdbc (as far as I know 2.X.X.RELEASE) supporting Pagable object I've decided for this particular case use NamedParameterJdbcTemplate and tail my own query. I hope this or other answers here will help someone. Thank you for your help :)

Parametrised order in spring-data-jdbc

3 Answers3