Multi tool use

Clash Royale CLAN TAG

Criteria API query fails

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.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.