How to perform queries on hierarchical entities using querydsl or spring data jpa specification?

Question

I'm having an entity hierarchy like this. Besides some common properties, some properties are shared only by a few subtypes:

@Entity
@Inheritance(strategy = InheritanceType.JOINED)
public class Person {
    private String firstName;
    private String lastName

    ... further properties, getters and setters...
}

@Entity
public class Employee extends Person {
    private String salary;

    ... further properties, getters and setters...
}

@Entity
public class BoardMember extends Person {
    private String salary;

    ... further properties, getters and setters...
}

@Entity
public class ExternalMember extends Person {
    private String clearanceLevel;

    ... further properties, getters and setters...
}

@Repository
public interface PersonRepository extends JpaRepository<Person, Long>, QuerydslPredicateExecutor<Person> {

}

Using QueryDSL I'm trying to search for Persons depending on dynamic filter criterias like this:

@Service
@Transactional
public class PersonService {

  @Autowired
  PersonRepository personRepository;

  public Page<Person> search(String firstName, String salary) {
    var searchCriterias = new BooleanBuilder();
    if (firstName != null) {
      searchCriterias.and(QPerson.firstName.eq(firstName));
    }
    if (salary != null) {
        searchCriterias.andAnyOf(
          QPerson.person.as(QEmployee.class).salary.eq(salary),
          QPerson.person.as(QBoardMember.class).salary.eq(salary),
        );
    }
    personRepository.findAll(searchCriterias);
  }
}

This doesn't seem to be the right way, however, I'm getting a lot of errors like "salary" not a member of Person.

What are the various ways to deal with searches for hierarchical entities? I'd prefer QueryDSL for type-safety, but solutions using Spring Data Specification would be fine as well.

edit: The search criterias could get very complex using 15+ different search criterias. So I need a programmatic approach to formulate them.

I have answered similar question here https://stackoverflow.com/questions/60243712/spring-data-jpa-named-query-ignoring-null-parameters/60246119#60246119 — Oleksii Valuiskyi, Feb 21 '20 at 16:57
@alexvaluiskyi Using the specification api for flat objects is trouble-free. My problem is the hierarchical structure of my entities, which makes criterias much more complex. I need to cast the predicates to multiple subtypes, which hibernate doesn't like. — MattDiMu, Feb 21 '20 at 19:23
isn't the problem here that salary is only known to entities Employee and BoardMember, hence for the PersonRepository it is an unknown field? — Manuel Jain, Feb 26 '20 at 06:23
@ManuelJain Yes, this is the problem why hibernate refuses to execute the queries. So how do i programmatically create queries on such fields using querydsl or the criteria api? There are ways to "cast" the QEntities to their Subtype (http://www.querydsl.com/static/querydsl/latest/reference/html/ch03.html), but this doesn't seem to work. — MattDiMu, Feb 26 '20 at 12:47
@MattDiMu have you tried changin your repository to public interface EmployeeRepository extends JpaRepository, QuerydslPredicateExecutor and use this for executing your findAll operation? — Manuel Jain, Feb 27 '20 at 06:28
@ManuelJain Yes, but this doesn't solve as the problem, as this only allows searches for Employees then. I need to search among multiple different subtypes, though! — MattDiMu, Feb 27 '20 at 09:00

score 1 · Answer 1 · answered Feb 27 '20 at 23:09

I could not reproduce the "not a member of Person" error but I have managed to get the result from your query.

Following Baeldung's tutorial and adapting the code from your question I've managed to get the result without errors. Here is the sample project. Hope this helps

import javax.persistence.*;

@Entity
@Inheritance(strategy = InheritanceType.JOINED)
public class Person {

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

    @Column
    private String firstName;

    @Column
    private String lastName;

    public Person(String firstName, String lastName) {
        this.firstName = firstName;
        this.lastName = lastName;
    }

    public Person() {
    }
}

import javax.persistence.Column;
import javax.persistence.Entity;

@Entity
public class BoardMember extends Person {

    @Column
    private String salary;

    public BoardMember(String firstName, String lastName, String salary) {
        super(firstName, lastName);
        this.salary = salary;
    }

    public BoardMember() {
    }
}

@Service
@Transactional
public class PersonService {

    @Autowired
    PersonRepository personRepository;

    public List<Person> search(String firstName, String salary) {
        var searchCriterias = new BooleanBuilder();
        if (firstName != null) {
            searchCriterias.and(QPerson.person.firstName.eq(firstName));
        }
        if (salary != null) {
            searchCriterias.andAnyOf(
                    QPerson.person.as(QEmployee.class).salary.eq(salary),
                    QPerson.person.as(QBoardMember.class).salary.eq(salary)
            );
        }

        var result = new ArrayList<Person>();
        for (Person person : personRepository.findAll(searchCriterias)) {
            result.add(person);
        }
        return result;
    }
}

Thx for your effort. Unfortunately this doesn't work. It's true, that there is no hibernate exception, but the search doesn't work properly, probably because having 2 salary attributes confuses QueryDSL or Hibernate. When debugging your application the predicate looks like this: `person.salary eq "salary" || person.salary eq "salary"`, it's unable to differentiate between the different subtypes. — MattDiMu, Mar 02 '20 at 15:39
As the bounty was about to expire, I've awarded it to you as you've provided the closest answer and put much effort into it. Still i cannot flag is correct, as the search doesn't work properly and I don't want to confuse other people with the same problem. — MattDiMu, Mar 02 '20 at 15:43

How to perform queries on hierarchical entities using querydsl or spring data jpa specification?

1 Answers1