How to get filtered results based on two columns from a single table using Specification and Criteria Builder in Spring Boot

Question

I am currently in a requirement to get the filtered results based on two columns from a single table using Specification. I have briefly explained the data model and the entity design below.

I have three different Entities (table) like below:

• EMPLOYEE
• EMPLOYEE_HIKES
• EMPLOYEE_HIKE_TYPE

Employee.java

@Entity
@Table(name = "employee")
public  class Employee {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "EMP_ID")
private Integer employeeId;

@OneToMany
@JoinColumn(name = "EMPLOYEE_HIKE_ID")
private List<EmployeeHike> employeeHike;


// some other entities mapping and other columns

// getters and setters
}

EmployeeHike.java

@Entity
@Table(name = "employee_hikes")
public  class EmployeeHike {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "EMPLOYEE_HIKE_ID")
private Integer hikeId;

@OneToOne
@JoinColumn(name = "HIKE_TYPE_ID")
private EmployeeHikeType hikeTypeId;

@Column(name = "IS_ACTIVE")
private Boolean isActive;

// getters and setters
}

EmployeeHikeType.java

@Entity
@Table(name = "employee_hike_type")
public  class EmployeeHikeType {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "HIKE_TYPE_ID")
private Integer hikeTypeId;

@Column(name = "HIKE_TYPE_NAME")
private String hikeTypename;

// some other columns

// getters and setters
}

My Employee Specification class is like below,

EmployeeSpecification.java

public class EmployeeSpecification implements Specification<Employee> {
     
   @Override
   public Predicate toPredicate (Root<Employee>, CriteriaQuery<?> query, CriteriaBuilder cb) {
    List<Predicate> predicates = new ArrayList<>();

    Predicate hikeTypeIdPredicate = root.join("employeeHike").join("hikeTypeId").get(hikeTypeId).in(<list of hike type id values here from the request>);
    Predicate hikeActivePredicate = root.join("employeeHike").get("isActive").in(1);
    
    predicates.add(hikeActivePredicate);
    predicates.add(hikeTypeIdPredicate);

    // joins for other entities here

    return cb.and(predicates.toArray(new Predicate[0]));
   }
}

Expected Result : I am expecting the list of all employees who has the active hikes based on the hike type values.

Actual Result : Above code returns the list of all employees based on the hike type values alone and it is not getting filtered based on the active predicate.

Please comment if you need any additional information. Thanks in advance for the help.

Answer 1

At last I found an answer for getting the results based on multiple columns from a Single table.

After changing the specification class like below the results are fetched as expected. Hope, it helps to someone. Thanks.

EmployeeSpecification.java

public class EmployeeSpecification implements Specification<Employee> {
 
     @Override
     public Predicate toPredicate (Root<Employee>, CriteriaQuery<?> query, CriteriaBuilder cb) {
        
        List<Predicate> predicates = new ArrayList<>();
        Join<Employee, Employee> hikeJoin = root.join("employeeHike");
        
        Predicate hikeActivePredicate = cb.isTrue(hikeJoin.get("isActive"));
        Predicate hikeTypeIdPredicate = cb.in(hikeJoin.join("hikeTypeId").get("hikeTypeId")).value(<list of hike type id values here>);

        predicates.add(cb.and(hikeActivePredicate,hikeTypeIdPredicate/*add the other column predicates here if needed*/));

        // joins for other entities here

        return cb.and(predicates.toArray(new Predicate[0]));
     }
}

Solution - We have to use the same join in order to get the results based on multiple columns from the same table. If we have the need to filter the results based on third or fourth column then we need to create a new predicate accordingly and then add it to the place holder mentioned in the code.