How to write query(include subquery and exists) using JPA Criteria Builder

Question

Struggling to write the following query using JPA.

Oracle Query:

Select * from table1 s
where exists (Select 1 from table2 p
              INNER JOIN table3 a ON a.table2_id = p.id
              WHERE a.id = s.table3_id
              AND p.name = 'Test');

Also, would you like to point any good tutorial to write complex queries in JPA.

Sartorial

Answer 1

I will answer the example of the simple car advertisement domain (advert, brand, model) using JpaRepository, JpaSpecificationExecutor, CriteriaQuery, CriteriaBuilder:

• brand [one-to-many] model
• model [one-to-many] advert

Entities:

@Entity
public class Brand {
  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private Long id;
  private String name;
  @OneToMany(mappedBy = "brand", fetch = FetchType.EAGER)
  private List<Model> models;
}

@Entity
public class Model {
  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private Long id;
  private String name;
  @ManyToOne
  @JoinColumn(name = "brand_id")
  private Brand brand;
}

@Entity
public class Advert {
  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private Long id;
  @ManyToOne
  @JoinColumn(name = "model_id")
  private Model model;
  private int year;
  private int price;
}

Repository:

public interface AdvertRepository
  extends JpaRepository<Advert, Long>, JpaSpecificationExecutor<Advert> {
}

Specification:

public class AdvertSpecification implements Specification<Advert> {
  private Long brandId;

  public AdvertSpecification(Long brandId) {
    this.brandId = brandId;
  }

  @Override
  public Predicate toPredicate(Root<Advert> root,
                               CriteriaQuery<?> query,
                               CriteriaBuilder builder) {

    Subquery<Model> subQuery = query.subquery(Model.class);
    Root<Model> subRoot = subQuery.from(Model.class);

    Predicate modelPredicate = builder.equal(root.get("model"), subRoot.get("id"));

    Brand brand = new Brand();
    brand.setId(brandId);
    Predicate brandPredicate = builder.equal(subRoot.get("brand"), brand);

    subQuery.select(subRoot).where(modelPredicate, brandPredicate);
    return builder.exists(subQuery);
  }
}

Effect is this Hibernate SQL:

select advert0_.id as id1_0_,
       advert0_.model_id as model_id5_0_,
       advert0_.price as price3_0_,
       advert0_.year as year4_0_
from advert advert0_
where exists (select model1_.id from model model1_
              where advert0_.model_id=model1_.id
              and model1_.brand_id=?)

Kommunarsk

Hi, this is a great example. (when you do the " brand.setId( "...) I can't find where "criteria.getValue()" is coming from. Should it be "this.brandId" (member variable)? – Alyose

Wow, I just finished converting this to use strongly typed. (My org.springframework.data.jpa.domain.Specification uses strongly typed implemented by creating "import javax.persistence.metamodel.SingularAttribute;" and @javax.persistence.metamodel.StaticMetamodel(MyObject.class) in a MyObject_ class. Youch! This is alot of voodoo to get it working. Thanks for this example, it got me off the ground! – Alyose

Answer 2

You can do it much simpler using JPA Queries or HQL instead of Criteria builders:

SELECT e1 from Entity1 as e1 
where exists
(select e2 from Entity2 as e2 join e2.e3 as ent3
where ent3.id=e1.id and e2.name='Test')

Muldrow

I understand but the requirement is to do it with criteria Builder :( – Sartorial

Criteria builder has its own limitations, so it's better to combine it with queries depending on the situation. Don't try to put nails with screw drivers, it'll convert the ease in difficulty. – Muldrow