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
@Table(name = "employee")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer employeeId;
@JoinColumn(name = "EMPLOYEE_HIKE_ID")
private List<EmployeeHike> employeeHike;
// some other entities mapping and other columns
<code>@Entity
@Table(name = "employee")
public class Employee {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "EMP_ID")
private Integer employeeId;
@OneToOne
@JoinColumn(name = "EMPLOYEE_HIKE_ID")
private List<EmployeeHike> employeeHike;
// some other entities mapping and other columns
// getters and setters
}
</code>
@Entity
@Table(name = "employee")
public class Employee {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "EMP_ID")
private Integer employeeId;
@OneToOne
@JoinColumn(name = "EMPLOYEE_HIKE_ID")
private List<EmployeeHike> employeeHike;
// some other entities mapping and other columns
// getters and setters
}
EmployeeHike.java
@Table(name = "employee_hikes")
public class EmployeeHike {
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "EMPLOYEE_HIKE_ID")
@JoinColumn(name = "HIKE_TYPE_ID")
private EmployeeHikeType hikeTypeId;
@Column(name = "IS_ACTIVE")
private Boolean isActive;
<code>@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
}
</code>
@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
@Table(name = "employee_hike_type")
public class EmployeeHikeType {
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "HIKE_TYPE_ID")
private Integer hikeTypeId;
@Column(name = "HIKE_TYPE_NAME")
private String hikeTypename;
<code>@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
}
</code>
@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
<code>public class EmployeeSpecification implements Specification<Employee> {
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]));
<code>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]));
}
}
</code>
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.