How can I left join two unrelated tables using JPA Criteria?

Question

Here I have two tables users and orders, users has two fields first_name and last_name, and orders has a field full_name. For some reason, I cannot change the database schema.

Now I have a query:

SELECT u.* FROM users u LEFT JOIN orders o ON o.full_name = CONCAT(u.first_name, ' ', u.last_name)

And I need convert this into JPA Specifications because it is a part of other specifications. The following is my try:

Entity:

@Entity
@Table(name = "users")
class User {
    @Id
    private Integer id;

    @Column(name = "first_name")
    private String firstName;

    @Column(name = "last_name")
    private String lastName;
}

@Entity
@Table(name = "orders")
class Order {
    @Id
    private Integer orderId;

    @Column(name = "full_name")
    private String fullName;
}

The problem is, what should I fill in the following statement for the first parameter

(root, query, criteraBuilder) -> {
  Join<User,Order> join = root.join(???,JoinType.LEFT);
  join.on(blablabla_condition)
}

Is this doable?

Thanks

score 1 · Accepted Answer · answered Aug 29 '17 at 10:54

1

In order to go down the route you are trying to implement, you'll have to map the association within the User/Order as a @OneToOne or @OneToMany lazy association based on whether there can be multiple orders for a user or not. For that you'll have to map the join operation that is the complicated logic you have mapped in the native query at the top. I suggest you take a look in the @JoinFormula and @JoinColumnOrFormula as ways to map that join.

answered Aug 29 '17 at 10:54

dimitrisli

20,895
12
59
63

Thanks. Never knew Joinformula, very interesting. Will give it a try – haohaolee Aug 29 '17 at 12:03
1

If you find this answer helpful you can upvote and/or accept it – dimitrisli Aug 29 '17 at 13:04

Cepr0 · Answer 2 · 2017-08-29T12:59:26.183

0

First attempt... What if you make a method with your native query?

@Query(value = "SELECT u.* FROM users u LEFT JOIN orders o ON o.full_name = CONCAT(u.first_name, ' ', u.last_name)", nativeQuery = true)
List<User> getUserJoinedWithOrder();

UPDATED

Specification<One> p = (user, query, cb) -> {
    return cb.equal(
            query.from(Order.class).get("fullName"), 
            cb.concat(user.get("firstName"), cb.concat(" ", user.get("lastName"))));
};

List<One> list = oneRepo.findAll(p);

edited Aug 29 '17 at 12:59

answered Aug 29 '17 at 11:56

Cepr0

28,144
8
75
101

I need specifications because I am building a complex query of which this is a part – haohaolee Aug 29 '17 at 12:02
thank you for this. But what I need is left outer join, because left outer join can join two tables as one where I can put additional conditions even some calculations, not just a specification to find a user. – haohaolee Aug 29 '17 at 13:09
@haohaolee I think with JPA it's not impossible, to make a join it need a field to related object... – Cepr0 Aug 29 '17 at 13:34

How can I left join two unrelated tables using JPA Criteria?

2 Answers2