2

Beware: this question has been falsely marked as duplicate. Anyways I found a solution that I will add to my question below, as answers cannot be posted anymore on "duplicate" questions.

I want to transform the following sql query into a JPA query using querydsl and spring-data-jpa:

SELECT DISTINCT age FROM `my_table`
  WHERE firstname = 'john' AND lastname = 'doe'
  GROUP BY age
  ORDER BY COUNT(*) DESC
  LIMIT 1

Currently I have the following, which just selects all entries by first+lastname:

public interface MyRepository extends JpaRepository<MyEntity, Long>, QueryDslPredicateExecutor<MyEntity> {

}

Usage:

@Autowired
private MyRepository dao;

//select * from my_table where firstname = ? and lastname = ?
BooleanExpression query = p.firstname.eq(firstname).and(p.lastname.eq(lastname));
dao.findAll(query);

//TODO how to add group, order and limit?

But I now want to group by age field and select the age that occurs the most. But how can I add DISTINCT, GROUP BY, ORDER BY COUNT and LIMIT statements in the querydsl?

Solution: it's not possible directly using CrudRepository, but still easy by using com.querydsl.jpa.impl.JPAQuery as follows:

QPersonEntity p = QPersonEntity.person; //the querydsl definition

int age = new JPAQuery(em)
    .select(p.age)
    .from(p)
    .where(p.firstname.eq(firstname).and(p.lastname.eq(lastname)))
    .groupBy(p.age)
    .orderBy(p.age.count().desc())
    .limit(1)
    .fetchOne();
membersound
  • 81,582
  • 193
  • 585
  • 1,120
  • I'm not sure if returning count and other information is an option unless you do some special projection class to cover that, maybe this https://stackoverflow.com/questions/43352967/spring-jpa-using-multiple-projection-on-same-query-interface/43373337#43373337 will help – Nadir Oct 09 '17 at 13:29
  • I think `querydsl` supports keywords like `group` and `distinct`, but I don't know how to hook this into the `QueryDslPredicateExecutor`. – membersound Oct 09 '17 at 13:31
  • apart from that you can do findDistinctByFirstNameAndLastNameOrderByFirstName(String first, String last, Pageable limit) – Nadir Oct 09 '17 at 13:31
  • I know, but I have to "group by" and "order by count". How to achieve this using the functional approach? – membersound Oct 09 '17 at 13:33
  • 2
    @Jarrod Roberson this is **not** about `projection`. It's about how to use `DISTINCT, GROUP BY, ORDER BY` with spring-data-jpa! That's a totally different subject! – membersound Oct 09 '17 at 13:56
  • 1
    Not a dup I guess. Apart from what the OP says, this question refers to how to implement it with querydsl, whereas in the linked question it's done using standard spring data repo. – Aritz Oct 09 '17 at 13:58

1 Answers1

1

You can define a method in your @Repository in order to expose a method that performs the required operation with a native query. In fact, as report in the Spring Data JPA reference docs:

The @Query annotation allows executing native queries by setting the nativeQuery flag to true.

You can add this code snippet in your @Repository:

@Query(value = "SELECT DISTINCT age "+
               "FROM my_table "+
               "WHERE firstname = :firstname AND lastname = :lastname " +
               "GROUP BY age " +
               "ORDER BY COUNT(*) DESC " +
               "LIMIT 1", nativeQuery = true)
int retrieveAgeByFirstNameAndLastName(@Param("firstname") String firstname,
                                      @Param("lastname") String lastname);
araknoid
  • 3,065
  • 5
  • 33
  • 35