JPA criteria to only include records with a NULL @OneToOne child reference-CodePudding

I have two classes:

public class ComicBook {
  @OneToOne(mappedBy = "comicBook", cascade = CascadeType.ALL, orphanRemoval = true)
  private ComicMetadataSource metadata;
}

and:

public class ComicMetadataSource {
  @OneToOne
  private ComicBook comicBook;
}

I'm trying to use CriteriaBuilder to programmatically create a query to load records from ComicBook that do not have an associated ComicMetadatSource record. Specifically, I tried this:

  final CriteriaBuilder criteriaBuilder = this.entityManager.getCriteriaBuilder();
  CriteriaQuery<ComicBook> criteriaQuery = criteriaBuilder.createQuery(ComicBook.class);
  criteriaQuery.distinct(true);
  Root<ComicBook> comicBook = criteriaQuery.from(ComicBook.class);
  if (unscraped) {
    comicBook.join("metadata");
    comicBook.get("metadata").isNull();
  }
  return query.getResultList();

However, this is returning records from ComicBook that definitely have a ComicMetadataSource record attached to them.

I've avoided CriteriaBuilder because of the complexity when working with multiple tables, but I'm in a situation where the best solution is to use criteria, but it's really confusing me how to work across tables like this.

CodePudding user response：

Solution 1: LEFT JOIN
Criteria:

    public List<ComicBook> comicBooksWithNullMetadata() {
        final CriteriaBuilder criteriaBuilder = this.entityManager.getCriteriaBuilder();
        CriteriaQuery<ComicBook> criteriaQuery = criteriaBuilder.createQuery(ComicBook.class);
        Root<ComicBook> comicBook = criteriaQuery.from(ComicBook.class);
        Join<ComicBook, ComicMetadataSource> join = comicBook.join("metadata", JoinType.LEFT);

        criteriaQuery.select(comicBook).where(join.isNull());

        return entityManager.createQuery(criteriaQuery).getResultList();
    }

Generated query:

    select
        comicbook0_.id as id1_10_ 
    from
        comic_book comicbook0_ 
    left outer join
        comic_metadata_source comicmetad1_ 
            on comicbook0_.id=comicmetad1_.comic_book_id 
    where
        comicmetad1_.id is null

Solution 2: NOT EXISTS
Criteria:

    public List<ComicBook> comicBooksWithNullMetadata() {
        final CriteriaBuilder criteriaBuilder = this.entityManager.getCriteriaBuilder();
        CriteriaQuery<ComicBook> criteriaQuery = criteriaBuilder.createQuery(ComicBook.class);
        Root<ComicBook> comicBook = criteriaQuery.from(ComicBook.class);

        //create Exists sub query
        Subquery<ComicMetadataSource> subQuery = criteriaQuery.subquery(ComicMetadataSource.class);
        Root<ComicMetadataSource> comicMetadataSource = subQuery.from(ComicMetadataSource.class);
        subQuery.select(comicMetadataSource).where(criteriaBuilder.equal(comicBook.get("id"), comicMetadataSource.get("comicBook")));

        //create Main query with Not Exists condition
        criteriaQuery.select(comicBook).where(criteriaBuilder.exists(subQuery).not());

        return entityManager.createQuery(criteriaQuery).getResultList();
    }

Generated query:

    select
        comicbook0_.id as id1_10_ 
    from
        comic_book comicbook0_ 
    where
        not (exists (select
            comicmetad1_.id 
        from
            comic_metadata_source comicmetad1_ 
        where
            comicbook0_.id=comicmetad1_.comic_book_id))