Dynamic SELECT and WHERE clause

Question

We have a requirement in our spring boot application where it is required to generate a query with dynamic SELECT clause and WHERE clause. User would choose specific columns during runtime and only those need to be fetched. How can this be achieved? As far as I have searched about dynamic queries, I can understand the dynamic part can be done for WHERE clause. But, not sure how to do the SELECT clause dynamically.

Editing to add example: Table has columns id, name, role, salary.

Query:

select id, name from Table Where role = 'Admin';

Here, WHERE clause (role = Admin) and the columns to be selected(id, name) will be known only at runtime.

What you describe sounds like a query builder - is https://querybuilder.js.org/ what you want? — Neville Kuyt, May 17 '19 at 12:19

score 0 · Answer 1 · answered May 17 '19 at 13:00

Use something like this

final CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
final CriteriaQuery<T> criteriaQuery = criteriaBuilder.createQuery(getDomainClass());
final Root<T> root = criteriaQuery.from(getDomainClass());

criteriaQuery.select(root);
ParameterExpression<Integer> p = criteriaBuilder.parameter(Integer.class);
criteriaQuery.where(criteriaBuilder.gt(root.get("<whatever>"), p));

final TypedQuery<T> query = em.createQuery(criteriaQuery);
return query.getResultList();

If that does not fit your idea, have a look at this post Really dynamic JPA CriteriaBuilder

Dynamic SELECT and WHERE clause

1 Answers1