spring data jpa - How to create specification using JpaSpecificationExecutor by combining tables?

Question

I am using JpaSpecificationExecutor for creating custom queries. How do I create a Specification for the following SQL?

select * from employee e, address a where e.id=23415 and e.name="Foo" and a.city="London";

Java Class :

public static Specification<Employee> searchEmployee(final Map<String,String> myMap) {
    
    return new Specification<Employee>(){
        @Override
        public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
               
             //Need to query two tables Employee and Address  
             
           }
      }

Answer 1

Here is a test that works

@Test
public void test1() {

    repository.save(makeEmployee("billy", "London"));
    repository.save(makeEmployee("noby", "London"));
    repository.save(makeEmployee("fred", "London"));

    assertEquals(3, repository.count());

    final Long id = 3l;
    final String name = "noby";
    final String city = "London";

    Specification<Employee> specification = new Specification<Employee>() {
        public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
            List<Predicate> predicates = new ArrayList<Predicate>();
            predicates.add(builder.equal(root.get("id"), id));
            predicates.add(builder.equal(root.get("name"), name));
            predicates.add(builder.equal(root.get("address").get("city"), city));
            return builder.and(predicates.toArray(new Predicate[predicates.size()]));
        }
    };

    List<Employee> find = repository.findByIdAndNameAndAddressCity(id, name, city);
    assertEquals(1, find.size());

    find = repository.findAll(specification);
    assertEquals(1, find.size());
}

private Employee makeEmployee(String name, String city) {

    Address address = new Address();
    address.setCity(city);

    Employee employee = new Employee();
    employee.setName(name);
    employee.setAddress(address);
    return employee;
}

}

Repository looks like this

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long>, JpaSpecificationExecutor<Employee> {

    List<Employee> findByIdAndNameAndAddressCity(Long id, String name, String city);
}

Entity looks like this

@Entity(name = "EMPLOYEE")
public class Employee {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

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

    @Column(name = "DATE_OF_BIRTH")
    private Date dob;

    @OneToOne(cascade=CascadeType.ALL)
    @JoinColumn(name = "address_id", referencedColumnName = "id", nullable = false)
    private Address address;

Hope this helps.