2

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.

Community
  • 1
  • 1
Harshal
  • 123
  • 7

1 Answers1

0

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).

Community
  • 1
  • 1
Darshan Mehta
  • 30,102
  • 11
  • 68
  • 102
  • Hi Darshan, Thanks for the response. Basically, I am looking for the **QueryDSL** solution for above type of Subqueries(i.e. having subquery in FROM statement). – Harshal Jan 03 '17 at 05:28