Let’s imagine following entities:
@Inheritance(JOINED)
@Entity
abstract class AbstractCompany {
@Id
private Long id;
private String name;
//...
}
@Entity
class Company extends AbstractCompany {
private String city;
//...
}
@Entity Invitation extends AbstractCompany {
private boolean expired;
//...
}
Question is: how can I query all AbstractCompanies that are either Companies from London or Invitations that are not expired using Spring Data Jpa Specification (using only one query: I want consistent paging and order “by name”) ?
Since we have inheritance involved I have to use jakarta.persistence.criteria.CriteriaBuilder#treat but that implies inner join and selects only one type.
We should use separate INNER JOINS to solve double JOIN problem. If you want to use Specification we should implement static methods to use:
import org.springframework.data.jpa.domain.Specification;
import javax.persistence.criteria.*;
public class AbstractCompanySpecification {
public static Specification<AbstractCompany> isCityLondon() {
return (root, query, criteriaBuilder) -> {
Join<AbstractCompany, Company> companyJoin = root.join("company", JoinType.INNER);
return criteriaBuilder.equal(companyJoin.get("city"), "London");
};
}
public static Specification<AbstractCompany> isInvitationNotExpired() {
return (root, query, criteriaBuilder) -> {
Join<AbstractCompany, Invitation> invitationJoin = root.join("invitation", JoinType.INNER);
return criteriaBuilder.isFalse(invitationJoin.get("expired"));
};
}
}
public static Specification<AbstractCompany> isCityLondonAndInvitationNotExpired() {
return Specification.where(isCityLondon())
.and(isInvitationNotExpired());
}
public static Specification<AbstractCompany> orderByName() {
return (root, query, criteriaBuilder) -> {
query.orderBy(criteriaBuilder.asc(root.get("name")));
return null; // return null, beacuse we only order it
};
}
And in Repository interface:
public interface AbstractCompanyRepository extends JpaRepository<AbstractCompany, Long>, JpaSpecificationExecutor<AbstractCompany> {
}
Last of all, we can use the Specification on field.
Specification<AbstractCompany> specification = Specification
.where(isCityLondonAndInvitationNotExpired())
.and(orderByName());
List<AbstractCompany> result = abstractCompanyRepository.findAll(specification);
I hope it can help you. Good luck 🙂
Ali İhsan TAŞDELEN is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I see you are using @Inheritance(JOINED) annotation so the subclasses got foreign key column(s) which primary key of superclass.
I am going to recommend you solution on Spring Data JPA. On implementation, I didn’t see any problem so I can help you for implement @Repository interface.
@Repository
public interface AbstractCompanyRepository extends JpaRepository<AbstractCompany, Long> {
@Query("SELECT DISTINCT a FROM AbstractCompany a " +
"JOIN Company c ON c.id = a.id " +
"JOIN Invitation i ON i.id = a.id " +
"WHERE c.city = 'London' " +
"AND NOT i.expired")
List<AbstractCompany> getLondonAndNotExpired();
}
Ali İhsan TAŞDELEN is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
2