How to write JPA criteria query select a with JOIN

Question

I am having a problem on how to write Join and than select from Application

so I have to entities Application and Deactivated

deactivated has a field:

@OneToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "application_id")
private Application application;

And Application has:

@Id
private Long id;

I don't know how describe the problem in words, so i'll just paste whe JPQL:

SELECT a FROM applications a LEFT OUTER JOIN deactivated_applications da on a.id = da.application_id WHERE da.filedeactivated=false;

CODE FOR THE QUERY that i recive params for:

{    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Application> cq = criteriaBuilder.createQuery(Application.class);
        Root<Application> root = cq.from(Application.class);
        //SearchQuery to TO SQLsearchQuery to specification, to Predicate
        Predicate searchQueryPredicate = null;
        if (!searchQuery.getCriteria().isEmpty())
            searchQueryPredicate = SqlSearchQuery.of(searchQuery).toSpecification(Application.class).toPredicate(root, cq, criteriaBuilder);}

now i need to get this query running on previous SQL query

Just write query but instead of using database structure use java objects. — Conrad, Jul 10 '20 at 11:24
the problem is: i need to write this and than I also recive a SearchQuery which needs do be on results of the first one. Can I somehow combine 2 querries into 1? Like: Query q2 = entityManager.createNamedQuery("Application.findWithFileActive") Query q1 = entityManager.createQuery(cq.where(searchQueryPredicate)) and than q1.where(q2) ? — Victor M, Jul 10 '20 at 12:07
No. Either combine it into single query or filter fetched data with java. If you want dynamically created queires then check jpa criteria. — Conrad, Jul 10 '20 at 12:13
I am using criteria for the first Querry, but i cannot get the first one from criteria Api. I'll update question with code — Victor M, Jul 10 '20 at 12:22
SELECT a FROM applications a LEFT OUTER JOIN deactivated_applications da on a.id = da.application_id WHERE da.filedeactivated=false; application doesnt have reference to deactivated, only deactivated has: @OneToOne(fetch = FetchType.LAZY) @JoinColumn(name = "application_id") private Application application; — Victor M, Jul 10 '20 at 12:48
So either change model or make reverse query: select * from deactivated d join d.application where ... — Conrad, Jul 10 '20 at 12:55
I am NOT to chane the model, and apparently i cannot have 2 roots. — Victor M, Jul 10 '20 at 12:59
This way it's not going to work, neither with criteria or jpql. Better to stick to sql. — Conrad, Jul 10 '20 at 13:02

score 0 · Answer 1 · answered Jul 10 '20 at 13:27

This looks like the kind of problem that is possible to solve with a subselect.

To make a selection from Applications where the Application's ID belongs to a deactivated application where filedeactivated is false, use the following kind of query structure:

SELECT a FROM applications a WHERE a.Id MEMBER OF (SELECT b.application_id FROM deactivatedapplications b WHERE b.filedeactivated = false)

This method is described using a books and authors example in https://thorben-janssen.com/jpql/#Subselects where Thorben uses a subselect to select authors who have written more than one book.

How to write JPA criteria query select a with JOIN

1 Answers1