I have functionality which uses RSQL (I use this library) and Specification to make a filter call with where clauses to the db.
The model I query is quite simple: it just contains a bunch of properties without any relationships with other models.
The repository (notice that I use JpaSpecificationExecutor):
public interface BaseRepository<M extends BaseModel> extends JpaRepository<M, Long>, JpaSpecificationExecutor<M>{
}
So I retrieve filter conditions as a string (I get them on runtime) then just create a new specification which will be used to generate the result sql query with where clauses.
In the service I make a call to the repository method findAll with a Specificator:
String filter = "createdDate > 2019-09-04T11:52:59.449";
return repository.findAll(toSpecification(filter), pageable).getContent();
Now I need to add new functionality which allows to group by some specific fields which I also get on runtime thus I don't know what fields exactly will be used for this.
So I've managed to add group by section to the result sql query by creating a new specification and combining it with the original one:
String filter = "createdDate > 2019-09-04T11:52:59.449";
Specification<M> groupBySpec = new Specification<M>() {
@Override
public Predicate toPredicate(Root<M> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
List<Expression<?>> groupByParams = new ArrayList<>();
groupByParams.add(root.get("param1"));
groupByParams.add(root.get("param2"));
query.groupBy(groupByParams);
// Here I try to select specific columns
query.multiselect(root.get("column1"), root.get("column2")).distinct(true);
return query.getGroupRestriction();
}
};
return repository.findAll(groupBySpec.and(toSpecification(filter)), pageable).getContent();
But I get a sql exception ERROR: column "columnName" must appear in the GROUP BY clause or be used in an aggregate function.
The result of this because it actually selects all the columns and just ignores my query.multiselect.
I've tried to implement it using just Criteria Api without JPA Specification like this:
public List<Object[]> getAllWithSpecific(){
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Object[]> q = cb.createQuery(Object[].class);
Root<Model> c = q.from(Model.class);
List<Expression<?>> groupByParams = new ArrayList<>();
groupByParams.add(c.get("column1"));
groupByParams.add(c.get("column2"));
q.groupBy(groupByParams);
q.select(cb.array(c.get("column1"), c.get("column2"), cb.count(q.from(Model.class))));
return em.createQuery(q).getResultList();
}
And it actually worked, it selected just what I need. But I can't just use it that way because I need this group by functionality to work in combination with filter, pagination functionality I described earlier.
Since JpaRepository works in the terms of the given model, I don't know how to say to it "grab only columns I need, not the whole model"
Also I've tried to add a method to the jpa repository annoted with @Query("actual jpql query") where I can describe what to select:
@Query("select m.column1, m.column2, count(*) from Model m group by m.column1, m.column2")
List<Object[]> customMethod();
And it works too. So now it understands that I need to grab only specific columns even though it still works in the terms of the same model.
I also know about projections which is actually an interface where I can define columns to select and use it in the repo like this: List<Projection> findAll().
But that's not my case as well as using @Query because I retrieve columns to select\group by during runtime, so it's kinda dynamic, not static.
To summarize: is possible to select specific columns using Jpa Specifications without adding new classes which would describe what to select (that's how @Query works, it just gets everything from jpql query without any additional classes\interfaces and it's fine). Maybe I need to override a jpa repo's method findAll to return List<Object[]> just as it works with @Query and pure Criteria Api?
