JPA Specification multiple join based on foreignkey

Question

I have following relationships between three objects

public class ProductEntity {
    @Id
    private int id;

    @OneToMany(mappedBy = "productEntity",
               fetch = FetchType.LAZY)
    private List<ProductInfoEntity> productInfoEntityList = new ArrayList<>();

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

public class ProductInfoEntity {
    @Id
    private int id;

    @ManyToOne
    @JoinColumn(name = "product_id")
    private ProductEntity productEntity;

    @ManyToOne
    @JoinColumn(name = "support_language_id")
    private SupportLanguageEntity supportLanguageEntity;
}


public class SupportLanguageEntity {
    @Id
    private int id;

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

And this is actual database design

Then, I'd like to make a specification to query as followings:

select * from product_info where product_id = 1 and support_language_id = 2;

I am also using annotation for the specification which means that I use ProductEntity_, ProductInfoEntity_ and so on.

Can you please give me a full working code for the specification for query mentioned above?

Thank you guys

Answer 1

To use Specification your ProductInfoEntityRepository have to extend JpaSpecificationExecutor

@Repository
public interface ProductInfoEntityRepository 
    extends JpaRepository<ProductInfoEntity, Integer>, JpaSpecificationExecutor<ProductInfoEntity> {
}

As far as I understand you use JPA metamodel. So then

@Autowired    
ProductInfoEntityRepository repository;

public List<ProductInfoEntity> findProductInfoEntities(int productId, int languageId) {
    return repository.findAll((root, query, builder) -> {
        Predicate productPredicate = builder.equal(
                root.get(ProductInfoEntity_.productEntity).get(ProductEntity_.id), // or root.get("productEntity").get("id")
                productId); 

        Predicate languagePredicate = builder.equal(
                root.get(ProductInfoEntity_.supportLanguageEntity).get(SupportLanguageEntity_.id),  // or root.get("supportLanguageEntity").get("id")
                languageId);

        return builder.and(productPredicate, languagePredicate);
    });
}

If you want to make specifications reusable you should create utility class contains two static methods productIdEquals(int) and languageIdEquals(int).

To combine them use Specifications(Spring Data JPA 1.*) or Specification(since Spring Data JPA 2.0)