In hibernate using CriteriaBuilder how to add condition to left join immediately and not as part of where condition?

Question

I want criteriabuilder to put AND condition immediately with join (1st left join in below query). But When I put condition in predicate it adds if to final where clause.

I want output like this:


    select
            *
        from
            milestone milestone0_ 
        left outer join
            disc_user_milestone_watch userswatch1_ 
                on milestone0_.id=userswatch1_.milestone_id AND userswatch1_.disc_user_id=2  
        left outer join
            disc_user discuser2_ 
                on userswatch1_.disc_user_id=discuser2_.id cross 
        join
            campaign campaign3_ cross 
        join
            department department4_ 
        where
            milestone0_.campaign=campaign3_.id 
            and milestone0_.department=department4_.id;

But after putting condition in predicate I get that AND condition in final where clause. Currently I am getting like this:


    select
            *
        from
            milestone milestone0_ 
        left outer join
            disc_user_milestone_watch userswatch1_ 
                on milestone0_.id=userswatch1_.milestone_id   
        left outer join
            disc_user discuser2_ 
                on userswatch1_.disc_user_id=discuser2_.id cross 
        join
            campaign campaign3_ cross 
        join
            department department4_ 
        where
            milestone0_.campaign=campaign3_.id 
            and milestone0_.department=department4_.id 
            AND userswatch1_.disc_user_id=2;

What is a way to add AND condition immediately to join ?

My current code looks like this:


    EntityManager em = Milestone.entityManager();
    CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
    CriteriaQuery criteriaQuery = criteriaBuilder.createQuery(UserMilestoneWatchStatus.class);

    Root milestoneRoot = criteriaQuery.from(Milestone.class);
    Join milestonediscUserJoin = milestoneRoot.join("usersWatching", JoinType.LEFT);

    // this is used for extending the milestone table to the user table
    Expression userId = milestonediscUserJoin.get("id"); // id
    int currUserId = Integer.parseInt(params.get("currUserId").toString());

    milestoneRoot.alias("x");
    criteriaQuery.select(criteriaBuilder.construct(UserMilestoneWatchStatus.class, milestoneRoot, userId));
    criteriaQuery.distinct(true);

    List predicatesList = new ArrayList();
    predicatesList.add(criteriaBuilder.or(criteriaBuilder.equal(userId, currUserId), criteriaBuilder.isNull(userId)));

    criteriaQuery.where(criteriaBuilder.and(predicatesList.toArray(new Predicate[predicatesList.size()]))).orderBy(sort);

Whats is way to add AND condition immediately to join and not in where ?

As an aside: without Criteria, but in plain JPQL, one could use the Hibernate-specific WITH to add additional clauses to the join, like left join disc_user d with w.disc_user_id = d.id. — Arjan

Mikko Maunu Mikko Maunu · Accepted Answer · 2012-10-18T18:58:34

No, there is no way to do that. JPA 2.0 does not support join with ON clause. Possibly it will be part of JPA 2.1.

In hibernate using CriteriaBuilder how to add condition to left join immediately and not as part of where condition?

1 Answers