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

Answer 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.

Answer 2

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);