CODEWITHSUNDEEP
javajpasubquerycriteria-api
Burs
Burs

Reputation: 15

Create a subquery based on main query JPA Criteria API

I need to write a query similar to this in JPA:

SELECT
  a.id,
  b.status
FROM
  atable a
  JOIN btable b ON a.btable_id = b.id
where
  (
    (
      b.status = 'INITIAL'
      and a.main_atable_id is null
    )
    or exists (
      SELECT
        1
      FROM
        atable a2
        JOIN btable b2 ON a2.btable_id = b2.id
      WHERE
        b2.status = 'INITIAL'
        and b2.main_atable_id = a.id
    )
  );

As you can see, atable has a column named main_atable_id which creates a parent-children relationship with the idea being that there is a main version and its children are duplicates.

I need to build a subquery that is almost identical to the query on the parent. I would write it from hand just duplicating the code, but I want to keep it simple if at all possible by REUSING the Specification of the main query.

My main query looks like this now:

public Page<AtableDTO> findAtables(AtableSearchDTO filter, Pageable pageable) {
    Specifications<Atable> where = Specifications.where(alwaysTrue());

    if(filter.getStatus() != null) {
        where = where.and(statusEquals(filter.getStatus()));
    }

    Page<AtableDTO> resultPage = atableRepository.findAll(where, new PageRequest(pageable.getPageNumber(), pageable.getPageSize(), Sort.Direction.DESC, "id")).map(atableMapper::toDto);
}

public Specification<Atable> alwaysTrue() {
    return (root, query, cb) -> cb.and();
}

public Specification<Atable> statusEquals(AtableStatus value) {
    return (root, query, cb) -> cb.equal(root.get("status"), value);
}

I just need to know:

1) Is it possible to reuse the same Specification
2) If it is, can you demonstrate on this or any other simple example

Thank you

Upvotes: 0

Views: 700

Answers (1)

crizzis
crizzis

Reputation: 10716

Why the EXISTS clause? Your query is equivalent to: 

SELECT
  a.id,
  b.status
FROM
  atable a
  JOIN btable b ON a.btable_id = b.id
  JOIN btable b2 ON a.btable_id = b2.id
WHERE (b.status = 'INITIAL' AND a.main_atable_id IS NULL)
OR (b2.status = 'INITIAL' AND b2.main_atable_id = a.id);

(see fiddle)

Assuming the following structure of your entities: 

@Entity
public class ATable {

    @Id
    private Long id;

    @OneToOne
    @JoinColumn(name = "btable_id")
    private BTable b;

    @ManyToOne
    @JoinColumn(name = "atable_main_id")
    private ATable main;
}

@Entity
public class BTable {

    @Id
    private Long id;

    private Status status;

    @ManyToOne
    @JoinColumn(name = "atable_main_id")
    private ATable main;
}

you want the following query: 

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Object[]> criteria = cb.createQuery(Object[].class);
Root<ATable> a = criteria.from(ATable.class);
Join<ATable, BTable> b = a.join("b");
Join<ATable, BTable> b2 = a.join("b");

criteria.where(cb.or(
        cb.and(
                cb.equal(b.get("status"), cb.literal(Status.INITIAL)),
                a.get("main").isNull()),
        cb.and(
                cb.equal(b2.get("status"), cb.literal(Status.INITIAL)),
                cb.equal(b2.get("main"), a)
                )));

criteria.multiselect(a.get("id"), b.get("status"));

Upvotes: 1

Related Questions