Spring boot jpa query builder for aggregate values?

Question

I'd like to create customizable query that output aggregate values. I know three ways to get and execute a query, but none of them seem to suffice. The queries I want to build look something like this:

Select max(category), min(price) as minprice from mytable where k='v' group by category

tldr: skip 1 & 2.

1. SQL as String
   • private NamedParameterJdbcTemplate template; template.query("select ..." , new MapSqlParameterSource("...", "...") , rs -> {...rs.getString("minprice")...
   • Pro: we can access the result from the query
   • Con: It is not using a query builder: we have to build the "select..." string ourselves.
2. Using repositories
   • public interface MytableRepository extends CrudRepository<Mytable, Integer> { @Query("Select ...") public List<Object[]> findMinMaxPrice(@Param("myParam") String myParam);
   • Pro: We can access the result from the query.
   • Con: The query is hardcoded
3. Using the query builder
   • Specification<MyTable> spec = Specifications.<>where((mytable, query, cb) -> { Predicate sql = cb.equal(mytable.get("k"), "v"; return sql; } List<Mytable> result = myJpaSpecificationExecutor.findall(spec);
   • Pro: It is using the query builder
   • Con: The query is not using the groupBy. Since our groupBy query is not returning records of class Mytable but aggregate values, I don't see how I can make this work. I start selecting from Mytable so I think I need to use it as type parameter to Specification, but that immediately implies that the result should also be of type MyTable, doesn't it?

How can I use a query builder with a flexible result type?

Answer 1

You might consider looking in to jOOQ, which would give you as much flexibility as you wish while keeping your queries "in code", so to speak.

It can generate Java classes from your database schema, which you use to form queries using its DSL. Here's an example: https://github.com/benjamin-bader/droptools/blob/master/droptools-example/src/main/java/com/bendb/example/resources/PostsResource.java#L99

Here's the meat of the code I linked above:

    final Record4<Integer, String, OffsetDateTime, String[]> record = create
            .select(BLOG_POST.ID, BLOG_POST.BODY, BLOG_POST.CREATED_AT, arrayAgg(POST_TAG.TAG_NAME))
            .from(BLOG_POST)
            .leftOuterJoin(POST_TAG)
            .on(BLOG_POST.ID.equal(POST_TAG.POST_ID))
            .where(BLOG_POST.ID.equal(id.get()))
            .groupBy(BLOG_POST.ID, BLOG_POST.BODY, BLOG_POST.CREATED_AT)
            .fetchOne();

It looks a little funky, but here's what it generates:

SELECT blog_post.id, blog_post.body, blog_post.created_at, array_agg(post_tag.tags)
FROM blog_post
LEFT JOIN post_tag ON blog_post.id = post_tag.post_id
WHERE blog_post.id = ?
GROUP BY blog_post.id, blog_post.body, blog_post.created_at

You can see that the Java code closely mirrors the generated SQL, but thanks to jOOQ's generated code, it is still perfectly typesafe. Because it's code, you can dynamically build up queries as you see fit.

It's a heavy dependency to take on, but it can do a lot for you if your SQL needs are specialized or dynamic.

Answer 2

I don't know what level of flexibility is good enough, but if you have one entity superclass which has all the fields that you're interested in, then you can use generic repository

public interface MyGenericRepository<E extends MEntity> extends JpaRepository<E,Long> {

  @Query("select e from #{#entityName} e where u.someField = ?1 or e.someOtherField = ?1")
  List<E> findBySomeFieldOrSomeOtherField(String query);
}

docs