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  
             
           }
      }

Essex Boy · Accepted Answer · 2016-02-05T08:27:17.487

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.

I agree this works perfectly for one table. In the above code you have one table(EMPLOYEE) to hold both Employee and Address details. But I have 2 tables EMPLOYEE and ADDRESS table separately. I need to query on both the tables using the Specification. Any suggestion? — DarkCrow, Feb 04 '16 at 13:43
Apologies for not reading the question, I've corrected the answer. — Essex Boy, Feb 04 '16 at 14:28
The @OneToMany is on the Employee so it expects the FK to Address PK to be on the Employee table. i.e. there should be an address_id column on the employee. Not sure why you get the above, doesn't look right to me. — Essex Boy, Feb 05 '16 at 12:09
I have a FK reference in the table. the issue is with defining the predicates. I had used the following to overcome it Inside the specification Join join = root.join("address"); and while building predicates instead of using root I used predicates.add(builder.equal(join.get("city"), city)); — DarkCrow, Feb 05 '16 at 15:59

How to create specification using JpaSpecificationExecutor by combining tables?

1 Answers1

Linked