Dynamic where JPA Spring Boot

Question

I have an Table and Pojo in my Spring Boot application like below.

@Entity
@Table(name = "attendance_summary")
public class AttendanceSummary implements Serializable {

  @Id
  @SequenceGenerator(name = "attendance_summary_id_seq",
    sequenceName = "attendance_summary_id_seq",
    allocationSize = 1)
  @GeneratedValue(strategy = GenerationType.SEQUENCE,
    generator = "attendance_summary_id_seq")
  @Column(name = "id", updatable = false)
  public Integer id;

  @Column(name = "emp_id", nullable = false)
  public Integer empId;

  @Column(name = "designation_id")
  public Integer designationId;

  @Column(name = "designation_category_id")
  public Integer designationCategoryId;

  @Column(name = "department_id")
  public Integer departmentId;

  ......
}

Now I want have dynamic inputs for these fields. Meaning user might select a list of empIds, designationIds.... or any combinations of them or even none of them. If they select none of the fields I need to return all the rows from the table in the database.

But in jpa when we write methods in repository we have to specify the field names like

public interface AttendanceSummaryRepository extends JpaRepository<Integer,AttendanceSummary>{
     List<AttendanceSummary> findByEmpIdAndDesignationId....(List<Integer> empIdList,List<Integer> designationIdList ... );
}

Which means if any of these parameters are null I will get an error or an exception and as a result I will miss some data.

where as in PHP or some other language like that I can just check the value of the desired filters and just dynamically add a where clause in the query.

query= "Select * from attendance_summary where ";
if(empIdList != null)
    query = query + " emp_id in empIdList "
if(designationIdList != null)
    query = query + " designation_id in designationIdList "
 .....
 //You get the idea.

Is there any way to do so with jpaDataMethods and if yes how. Any detailed explanation / link to resources are much appreciated.

Sorry for poor english and if I couldn't explain my problem properly.

score 0 · Answer 1 · answered Mar 18 '19 at 13:34

0

Take a look at Criteria API. It allows you to create dynamic queries.

In your example, something similar to this could work:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<AttendanceSummary> query = cb.createQuery(AttendanceSummary.class);
Root<AttendanceSummary> root = query.from(AttendanceSummary.class);

List<Predicate> predList = new ArrayList<>();
if (empIdList != null) {
    predList.add(root.get('empId').in(empIdList));
}
if (designationIdList != null) {
    predList.add(root.get('designationId').in(designationIdList));
}
// ... You get the idea.

Predicate[] predicates = new Predicate[predList.size()];
predList.toArray(predicates);

query.where(predicates);

return entityManager.createQuery(query).getResultList();

answered Mar 18 '19 at 13:34

Marcel Goldammer

72
1
1
6

Sorry i am not experienced with spring boot much. What is the entity manager ? Looks like you injected it here to use it. Could you point me to some resources so that I can learn more about it? – Risalat Zaman Mar 20 '19 at 06:42
The entity manager is able to access your entities like AttendanceSummary and can create SQL queries for you. [This](https://stackoverflow.com/questions/2421339/how-to-inject-jpa-entitymanager-using-spring) shows you how to inject the entity manager with spring. – Marcel Goldammer Mar 20 '19 at 07:05
I think i have some problems in my usage of entity manager. I created the repository and its according implementation following this guide. https://www.baeldung.com/spring-data-criteria-queries [link]I used above logic for constructing my queries. Sadly my code is getting a null pointer on the line CriteriaBuilder cb = entityManager.getCriteriaBuilder(); I turned on debug and saw my entityManager is null. Inspecting the issue and will let you know – Risalat Zaman Mar 20 '19 at 08:00

Amith Kumar · Answer 2 · 2019-03-16T20:13:16.933

-1

You can achieve this by using @Query annotation. Please refer spring guide for more details.

@Query(value = "from attendance_summary where (emp_id in (?1) or ?1 is null) and (designation_id in (?2) or ?2 is null)" )

Query Detail:

SELECT * #implicit so removed
FROM   attendance_summary 
WHERE  ( emp_id IN (?1) #true when IDs are not null, thus apply filter
          OR ?1 IS NULL #true when user input null, return all rows ) 
       AND ( designation_id IN (?2) #true when IDs are not null, thus apply filter
              OR ?2 IS NULL #true user input null, return all rows)

Example Project on github with spring-boot, jpa & h2. Look for SchoolController & SchoolRepo classes, applying the same logic, the endpoints \school will filter the result for input Ids & \allschool will return everything as input is null.

edited Mar 16 '19 at 20:13

answered Mar 15 '19 at 21:28

Amith Kumar

4,400
1
21
28

I only listed 2/3 fields here for example. In reality there are more fields. So if I do it this way, I have to handle a huge number of cases ( since the user can select any number of fields for the search. Eg: they selected emp_id and designation_id but not department id. Or department_id and designation_id and not any other.) Also I am told that the value inside @Query needs to be static. So I can't chain logic according to input. Might there be a cleaner way to do this? – Risalat Zaman Mar 15 '19 at 21:47
I changed the query to achieve the desired result, so you don't have to make multiple method signatures. – Amith Kumar Mar 15 '19 at 22:33
The db field values will never be null. If the user input for any field is null, I would have to exclude it from the search parameters. So this query won't work. – Risalat Zaman Mar 16 '19 at 05:45
You didn't try the solution nor paid close attention to the query, I am not comparing the DB field to be null but the input itself. Added explanation to the query, so you will understand how its handling null filter – Amith Kumar Mar 16 '19 at 06:53
I tried the query. Got this exception Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near ")" – Risalat Zaman Mar 16 '19 at 09:53
There was syntax error in `IN` clause, but logically it works. I corrected the answer & also added example to refer in my github project. Hopefully that helps. – Amith Kumar Mar 16 '19 at 20:15

Dynamic where JPA Spring Boot

2 Answers2

Query Detail: