Generating dynamic query in spring boot

Question

I have a native Mysql query

select tl_id,c_name,m_name,u_first_name,t_name, 
tl_logged_at,tl_minutes,tl_description
from users inner join clients on u_id=c_frn_owner_id
inner join matters on m_frn_client_id = c_id
inner join tasks on t_frn_matter_id = m_id
inner join task_logs on tl_frn_task_id = t_id
where c_id =2 and m_id=4 and t_id= 3 and u_id = 4

for which I can write JPQL instead of native query. But how to get the same query using JPA Specification, since the columns or fields in the where conditions c_id, m_id, t_id and u_id are optionals. They are filter options provided to the user.

Generation of native query can be done using if conditions. But they are prone to typo and SQL Injections.

The documentation of JPASpecification has no informations related to joining multiple tables.

Spring Official JPA Specification Doc

I am very new to JPASpecification so any guidance will be valuable.

Answer 1

JPA 2 introduces a criteria API that you can use to build queries programmatically.

If you use JPA 2 and you wanted to create dynamic query, I think you should look at the Criteria API to build dynamic queries. Or you can use JpaSpecificationExecutor(I haven't tried, yet.) Check this related question.

Some useful links attached:

1. JPA Specifications
2. JPA Criteria Api

Answer 2

Have a look at the querydsl project, I use it extensively in a mid size ERP and hasn't disappointed until now.

http://www.querydsl.com/static/querydsl/latest/reference/html/

for dynamic where clause: you can use com.querydsl.core.BooleanBuilder

So when you have querydsl in place, you can first generate your where clause like the following.

QEmployee employee = QEmployee.employee;
 BooleanBuilder dynamicWhere = new BooleanBuilder();
 if(true) { //Any condition
     dynamicWhere.and(employee.name.equalsIgnoreCase("someName"));
 }
 if(true){
     dynamicWhere.and(employee.age.gt(18));
 }

and then you can write your query like the following.

queryFactory.selectFrom(employee)
    //joins, group by, order by goes here
    .where(dynamicWhere)
    .fetch();