QueryDSL - add subquery into FROM statement using JPA

Question

Would you please help me to create QueryDSL construct for SQL like below using JPA Query. I am using 4.1.3.

SELECT * FROM (SELECT a FROM b WHERE a.z = 1) WHERE rownum <=1;

The Solutions mentioned is not working for me QueryDSL - add subquery into FROM statement

Thanks in advance.

Answer 1

The below query:

SELECT a FROM b WHERE a.z = 1

does not have any order by clause, so default ordering will be applied. As per this SO answer, the ordering is not predicted. So, I recommend adding the order by clause.

If this is just an example and the actual query contains order by then you can implement similar logic in a single query; rather than wrapping it into another query and get the first row, e.g. something like (SELECT a FROM b WHERE a.z = 1 order by z) with rownum. Below are the steps to do it via JPA way:

• Write a repository for that table (you can extend PagingAndSortingRepository as it already has some methods)
• Write a findBy() method that accepts Pageble argument (along with z), it would look like this: public List<T> findByZ(int z, Pageable pageable)

• Call it with Z and PageRequest, e.g.:

  final PageRequest page1 = new PageRequest( 0, 1, Direction.ASC, "somefield" );

It would apply the limit/rownnum based on db you are using and give you the record(s).