Criteria API query fails

Question

I'm new at Criteria and can not understand the next thing.

I've got entities with сonnection between each others

User:

 //...fields...

 @OneToOne(fetch = FetchType.LAZY, mappedBy = "user")
 private Document document;

Document:

//...fields...

@OneToOne(fetch = FetchType.LAZY)
@PrimaryKeyJoinColumn
private User user;

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "doc_type_id", nullable = false)
private DocType docType;

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "citizenship_id", nullable = false)
private Country country;

DocType:

//...fields...

@Column(nullable = false, length = 5)
private String code;

Country:

//...fields...

@Column(nullable = false, length = 5)
private String code;

I need to find user with a document that contains the docCode (DocType.code) and citizenshipCode (Country.code).

Dao:

    //private final EntityManager em;
    public List<User> filterUser(UserView userView) {
    List<User> userList;
    List<Document> documentList;
    Long officeId = userView.officeId;
    String docCode = userView.docCode;
    String citizenshipCode = userView.citizenshipCode;

    CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
    CriteriaQuery<User> userQuery = criteriaBuilder.createQuery(User.class);
    Root<User> userRoot = userQuery.from(User.class);
    userQuery.select(userRoot);
    userQuery.where(criteriaBuilder.equal(userRoot.get("office"), officeId));

    //find id of DocType
    Long docTypeId = null;
    if (docCode != null) {
        CriteriaQuery<DocType> docTypeQuery = criteriaBuilder.createQuery(DocType.class);
        Root<DocType> docTypeRoot = docTypeQuery.from(DocType.class);
        docTypeQuery.select(docTypeRoot);
        docTypeQuery.where(criteriaBuilder.equal(docTypeRoot.get("code"), docCode));
        docTypeId = em.createQuery(docTypeQuery).getSingleResult().getId();
    }

    //find id of Country
    Long countryId = null;
    if (citizenshipCode != null) {
        CriteriaQuery<Country> countryQuery = criteriaBuilder.createQuery(Country.class);
        Root<Country> countryRoot = countryQuery.from(Country.class);
        countryQuery.select(countryRoot);
        countryQuery.where(criteriaBuilder.equal(countryRoot.get("code"), citizenshipCode));
        countryId = em.createQuery(countryQuery).getSingleResult().getId();
    }

    if (docTypeId != null || countryId != null) {
        CriteriaQuery<Document> documentQuery = criteriaBuilder.createQuery(Document.class);
        Root<Document> documentRoot = documentQuery.from(Document.class);
        documentQuery.select(documentRoot);
        if (docTypeId != null) {
            //filter for Document which contains docTypeId
            documentQuery.where(criteriaBuilder.equal(documentRoot.get("docType"), docTypeId));
        }
        if (countryId != null) {
            //filter for which contains countryId 
            documentQuery.where(criteriaBuilder.equal(documentRoot.get("country"), countryId));
        }
        documentList = em.createQuery(documentQuery).getResultList();
        List<Integer> idDocuments = new ArrayList<>();
        //find all of required documents     

        documentList.stream().forEach((d) -> idDocuments.add(Math.toIntExact(d.getId())));
        userQuery.where(userRoot.get("document").in(idDocuments));
    }

    userList = em.createQuery(userQuery).getResultList();
    return userList;
}

I'm interested with these lines

userQuery.where(criteriaBuilder.equal(userRoot.get("office"), officeId));

and

userQuery.where(userRoot.get("document").in(idDocuments));

Why the filter on the first line does not execute?

I know that there are nested subqueries, but I can not get it right.

Answer 1

I don't know what you're trying to do with "Office" but you shouldn't put things into post that aren't part of the question. The @PrimaryKeyJoinColumn is causing you problems, it causes user_id to be left out of Document.

@Entity
public class User {
    @Id @GeneratedValue private int id;

     @OneToOne(fetch = FetchType.LAZY, mappedBy = "user")
     private Document document;

@Entity
public class Document {
    @Id @GeneratedValue private int id;

    @OneToOne(fetch = FetchType.LAZY)
    private User user;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "doc_type_id", nullable = false)
    private DocType docType;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "citizenship_id", nullable = false)
    private Country country;


@Entity
public class DocType {
    @Id @GeneratedValue private int id;

    @Column(nullable = false, length = 5)
    private String code;

@Entity
public class Country {
    @Id @GeneratedValue private int id;

    @Column(nullable = false, length = 5)
    private String code;

And to use it:

tx.begin();

DocType docType = new DocType();
docType.setCode("TYPE");
em.persist(docType);
Country country = new Country();
country.setCode("US");
em.persist(country);
User user = new User();
em.persist(user);
Document document = new Document();
document.setCountry(country);
document.setDocType(docType);
document.setUser(user);
em.persist(document);
tx.commit();
em.clear();

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<User> q = cb.createQuery(User.class);
Root<User> userRoot = q.distinct(true).from(User.class);
Join<User, Document> docJoin = userRoot.join("document");
Join<Document, DocType> docTypeJoin = docJoin.join("docType");
Join<Document, Country> countryJoin = docJoin.join("country");

q.where(cb.and(
    cb.equal(countryJoin.get("code"), "US"), 
    cb.equal(docTypeJoin.get("code"), "TYPE")
));

List<User> users = em.createQuery(q).getResultList();

System.out.println(users);