CODEWITHSUNDEEP
javasqljpa-2.0
feder
feder

Reputation: 1795

How to formulate a JOIN in a typed query?

I want to create a typed query.

TypedQuery<PubThread> query = em.createQuery(queryString, PubThread.class);
query.setParameter("threadId", threadId);
List<PubThread> otherPubThreads = query.getResultList();

In the queryString is the following SQL (currently without param and static selection values)

SELECT pt2 FROM pubthread pt2   
JOIN pub_pubthread ppt2 ON pt2.id = ppt2.pubThreads_id  
JOIN pub p2 ON ppt2.pups_id = p2.id     
JOIN pubcategory pc2 ON p2.pubCategoryId = pc2.id   
WHERE pt2.id != 1 and EXISTS (      
    SELECT DISTINCT(pt.id) 
    FROM pubthread pt       
    JOIN pub_pubthread ppt ON pt.id = ppt.pubThreads_id         
    JOIN pub p ON ppt.pups_id = p.id        
    JOIN pubcategory pc ON p.pubCategoryId = pc.id      
    WHERE pc2.id = pc.id and pt.id = 1  
)

It does work, if I limit the String to a simple select: SELECT Distinct(pt2.id), pt2.name FROM pubthread pt2. As soon I add a JOIN line to it, it will complain. How do you properly query with JOINS in JPA? The error is: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ON near line 1, column 81 [SELECT pt2 FROM com.brayan.webapp.model.PubThread pt2 JOIN pub_pubthread ppt2 ON pt2.id = ppt2.pubThreads_id ]

Doubtless, a criteria query would be nicer. I accept that as part of the solution space.

Upvotes: 1

Views: 7221

Answers (2)

feder
feder

Reputation: 1795

Got it. See below a fully example of joins. It consists of:

  • multiple joins (join-chaining)
  • a subquery
  • a predicate correlation/equation over join tables, other than the root table.

I also commented the obsolete code lines for other to see what a wrong approach.

CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder(); CriteriaQuery mainQuery = criteriaBuilder .createQuery(PubThread.class);

    // 1) MainQuery
    // Create the FROM
    Root<PubThread> rootPubThread = mainQuery.from(PubThread.class);
    // Create the JOIN from the first select: join-chaining. You only need the return for ordering. e.g. cq.orderBy(cb.asc(categoryJoin.get(Pub_.title)));
    Join<Pub, PubCategory> categoryJoin = rootPubThread.join(PubThread_.pups).join(Pub_.pubCategory);
    // Create the WHERE
    mainQuery.where(criteriaBuilder.not(criteriaBuilder.equal(rootPubThread.get(PubThread_.id), threadId)));
    // Create the SELECT, at last
    mainQuery.select(rootPubThread).distinct(true);

    // 2) Subquery
    Subquery<PubThread> subquery = mainQuery.subquery(PubThread.class); 
    Root<PubThread> rootPubThreadSub = subquery.from(PubThread.class); 
    //subquery.where(criteriaBuilder.equal(rootPubThread.get(PubThread_.id), threadId));
    Join<Pub, PubCategory> categoryJoinSub = rootPubThreadSub.join(PubThread_.pups).join(Pub_.pubCategory);
    subquery.select(rootPubThreadSub);

    //Predicate correlatePredicate = criteriaBuilder.equal(rootPubThreadSub.get(PubThread_.id), rootPubThread);
    Predicate correlatePredicate = criteriaBuilder.and(
            //criteriaBuilder.equal(rootPubThreadSub.get(PubThread_.id), rootPubThread),
            criteriaBuilder.equal(categoryJoinSub.get(PubCategory_.id), categoryJoin.get(PubCategory_.id)),

            criteriaBuilder.equal(rootPubThreadSub.get(PubThread_.id), threadId)
            );
    subquery.where(correlatePredicate);     

    //Predicate correlatePredicate = criteriaBuilder.equal(rootPubThreadSub.get(PubThread_.id), rootPubThread);
    Predicate mainPredicate = criteriaBuilder.and(
            criteriaBuilder.not(criteriaBuilder.equal(rootPubThread.get(PubThread_.id), threadId)),
            criteriaBuilder.exists(subquery)
            );
    //cq.where(criteriaBuilder.exists(subquery));
    mainQuery.where(mainPredicate);

Upvotes: 2

Alex
Alex

Reputation: 11579

When you call createQuery you have to write HQL but not SQL (your queryString is not HQL).
In HQL you have to join objects according to your mapping entities.
If you sill need SQL query use createNativeQuery method.
See documentation about how to create HQL query.

Upvotes: 2

Related Questions