Image by Gerd Altmann from Pixabay

Join using criteria query, Jpa Specification and paged search

I have two entities called PersonEntity and EmployeeEntity. EmployeeEntitiy has a one to one relationship to PersonEntity

PersonEntity

person table

@Entity
@Table(name = "person")
public class PersonEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

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

    @Column(name = "age")
    private Integer age;
}

EmployeeEntitiy

employee table

person_id is a foreign key and references id in Person table.

@Entity
@Table(name = "employee")
public class EmployeeEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @Column(name = "department")
    private String department;

    @OneToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    @JoinColumn(name = "person_id", referencedColumnName = "id")
    private PersonEntity personEntity;
}

I want to write a specification to get all employees with age > a specific value

public class EmployeeSpecification implements Specification<EmployeeEntity> {
    private final Integer age;

    public BankTransactionSpecification(final Integer age) {
        this.age = age;
    }

    @Override
    public Predicate toPredicate(Root<EmployeeEntity> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
        List<Predicate> predicates = new ArrayList<>();
        Join<EmployeeEntity, PersonEntity> joinPerson = root.join("personEntity");

        predicates.add(criteriaBuilder.gt(joinPerson.get("age"), age));
        return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
    }
}

Paged search using above specification to find all employees with age > a specific value

Repository with paging

@Repository
public interface EmployeeSearchRepository
        extends PagingAndSortingRepository<EmployeeEntity, Long>, JpaSpecificationExecutor {
}

Searching using specification

public class EmployeeSearchService {
    @Autowired
    private EmployeeSearchRepository employeeSearchRepository;

    public List<EmployeeEntity> search(Integer age, 
                                       Integer page, 
                                       Integer maxFetchCount) {
        EmployeeSpecification employeeSpecification = new EmployeeSpecification(age);

        Pageable page = PageRequest.of(page, maxFetchCount, Sort.by(Sort.Direction.ASC, "id"));
        Page<EmployeeEntity> employeeEntities = employeeSearchRepository.findAll(employeeSpecification, page);

        return employeeEntities.toList();
    }
}