48

I want make a query where I join 2 tables, using the CriteriaBuilder. In MySQL the query I'm trying to make would look like this:

SELECT * FROM order
LEFT JOIN item
ON order.id = item.order_id
AND item.type_id = 1

I want to get all orders and if they have an item of type #1, I want to join with this item. However, if no item of type #1 is found, I still want to get the order. I can't figure out how to make this with the CriteriaBuilder. All I know how to make is:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Order> cq = cb.createQuery(Order.class);
Root<Order> order = cq.from(Order.class);
Join<Order, Item> item = order.join(Order_.itemList, JoinType.LEFT);
Join<Item, Type> type = order.join(Item_.type, JoinType.LEFT);
cq.select(order);
cq.where(cb.equal(type.get(Type_.id), 1));

This query is broke, since it results in something like this in MySQL:

SELECT * FROM order
LEFT JOIN item
ON order.id = item.order_id
WHERE item.type_id = 1

The result will only contain orders with items of type #1. Orders without are excluded. How can I use the CriteriaBuilder to create a query like in the first example?

Bjørn Stenfeldt
  • 1,432
  • 1
  • 18
  • 25
  • _I want to get all orders and if they have an item of type #1, this item should be included in result_ What result type do you want? Order? Or a tuple / multiselect? What do you want _more_ than all orders? Please explain – perissf Apr 13 '13 at 20:29
  • Sorry, I wasn't being very clear. I've corrected the description. I'm not actually interested in getting the item as part of my result. I just want orders in my result. Basically, I want to sort the result based on items of a specific type, while still getting orders without that type of items. – Bjørn Stenfeldt Apr 13 '13 at 21:17
  • It's possible with CriteriaBuilder ! Look at my answer bellow – hzitoun Jul 29 '16 at 13:44

5 Answers5

50

It is possible starting from the version 2.1 of JPA using the on method Join<Z, X> on(Predicate... restrictions);

Here is how:

Root<Order> order = cq.from(Order.class);
Join<Order, Item> item = order.join(Order_.itemList, JoinType.LEFT);
item.on(cb.equal(item.get(Item_.type), 1));
hzitoun
  • 5,492
  • 1
  • 36
  • 43
  • 2
    If this works that will be quite a few native queries that can be replaced. I try it out next time I need a join-on. – Bjørn Stenfeldt Jun 24 '16 at 07:05
  • 1
    I didn't get what you mean by "few native queries that can be replaced" ! Try it first and you will see that it works perfectly ! – hzitoun Jun 29 '16 at 10:13
  • 1
    I just meant that I have a lot of places where I use `entityManager.createNativeQuery(String)` because of the shortcomings of the CriteriaBuilder. :-) – Bjørn Stenfeldt Jun 29 '16 at 14:25
  • @Mitchapp help me please to build similar query. Take a look at this question please https://stackoverflow.com/questions/46793743/criteria-one-to-many-relationship – VB_ Oct 18 '17 at 11:30
  • 2
    what if we are not using metamodel class . then how to write this query @hzitoun can you please update this answer – harsha kumar Reddy Oct 06 '21 at 17:00
2

I think this is the same problem as posed in this question. It looks like it is not possible in CriteriaBuilder. It is possible in Hibernate Criteria API, but that probably won't help you.

JPA Criteria API: Multiple condition on LEFT JOIN

Community
  • 1
  • 1
carbontax
  • 2,164
  • 23
  • 37
  • The first MySQL is what I want my CriteriaBuilder to generate. The second MySQL represents what the shown CriteriaQuery generates. If I simply add `or item.id is null` to the second MySQL query, I'm not getting what I want. Doing that would exclude orders with items that have a type_id different than 1, and only return orders with items of type 1 or no items at all. – Bjørn Stenfeldt Apr 14 '13 at 15:57
  • Thanks. I might give it a try on a smaller scale, but on my current project it probably won't help me. Right now I'm on EclipseLink (JPA 2.0). As far as I've been able to find out ([link](https://blogs.oracle.com/arungupta/entry/jpa_2_1_early_draft)), the feature I need won't be available until JPA 2.1. Hopefully it will soon. – Bjørn Stenfeldt Apr 14 '13 at 19:14
  • It's possible with CriteriaBuilder ! Look at my answer just under yours – hzitoun Jun 29 '16 at 13:25
0

I know this question was made a long time a go, but recently a had the same problem and i found this solution from an Oracle forum, i copied and pasted just in case the link is not longer available.

MiguelChillitupaArmijos 29-abr-2011 1:41 (en respuesta a 840578) Think you should use something like:

em.createQuery("SELECT DISTINCT e.Id" +
                    " from Email e " +
                    " left join e.idEmailIn e2 *with* e2.responseType = 'response'" +
                    "     where e.type = 'in' and e.responseMandatory = true").getSingleResult();

An this is the link.

JPA Criteria : LEFT JOIN with an AND condition

OJVM
  • 1,403
  • 1
  • 25
  • 37
0

There is a workaround if you are using Hibernate 3.6 with JPA 2.0 It is not the better solution, however it works perfect for me.

I´ve duplicate the entity with the @Where hibernate annotation.It means that everytime you use the join with this entity, hibernate will add the extra condition on the join statement at generated SQL.

For instance, initially we have the follow example:

@Entity
@Table(name = "PERSON")
public class Person {

    @Id
    @Column(name = "PERSON_ID")
    private Long id;

    @Id
    @Column(name = "PERSON_NAME")
    private String name;

   @OneToMany(mappedBy = "person", fetch = FetchType.LAZY)
   private Set<Address> addresses;

}

@Entity
@Table(name = "ADDRESS")
public class Address {

    @Id
    @Column(name = "ADDRESS_ID")
    private Long id;

    @Id
    @Column(name = "ADDRESS_STREET")
    private String street;

   @ManyToOne
   @JoinColumn(name = "PERSON_ID")
    private Person person;

}

In order to add extra conditions on criteria Join, we need duplicate the Address @Entity mapping , adding the @Where annotation @Where(clause = " ADDRESS_TYPE_ID = 2").

@Entity
@Table(name = "ADDRESS")
@Where(clause = " ADDRESS_TYPE_ID = 2")
public class ShippingAddress {

    @Id
    @Column(name = "ADDRESS_ID")
    private Long id;

    @Id
    @Column(name = "ADDRESS_STREET")
    private String street;

   @OneToOne
   @JoinColumn(name = "PERSON_ID")
    private Person person;

}

Also, we need to add the duplicate mapping association for the new entity.

@Entity
@Table(name = "PERSON")
public class Person {

    @Id
    @Column(name = "PERSON_ID")
    private Long id;

    @Id
    @Column(name = "PERSON_NAME")
    private String name;

   @OneToMany(mappedBy = "person", fetch = FetchType.LAZY)
   private Set<Address> addresses;

   @OneToOne(mappedBy = "person")
   private ShippingAddress shippingAddress;

}

Finally, you can use a join with this specific Entity in your criteria :

PersonRoot.join(Person_.shippingAddress, JoinType.LEFT);

The Hibernate Snippet SQL should seems like this :

 left outer join
        address shippingadd13_ 
            on person11_.person_id=shippingadd13_.person_id 
            and (
                shippingadd13_.ADDRESS_TYPE_ID = 2 
            )
Eduardo Fabricio
  • 2,151
  • 2
  • 25
  • 32
0

ON clause is supported in Hibernate 4.3 version, anyone is aware if there is a parameter indexing issue between the parameter index of the additional custom conditions with the index of the existing mapping filters when doing an outer join with ON clause?

Using the Person entity class below as an example, say I am adding this filter to limit the address types and the filter is enabled to populate the IN clause. The parameter index for the IN clause will cause the issue [2] when I add additional conditions (such as using 'street' column) part of the ON clause. Is is a known issue?

[1] @Filter(name = "addressTypes", condition = "ADDRESS_TYPE in (:supportedTypes)")

[2] Caused by: ERROR 22018: Invalid character string format for type BIGINT. private Set addresses;

user3390708
  • 1
  • 1