'How to perform left join on two sql queries using criteria API

I have two tables and their classes as follows:

@Entity
@Table(name="Master")    
public class Master {

    @Column(name = "master_col_one")
    private String masterColumnOne;


    @Column(name = "master_col_two")
    private String masterColumnTwo;

   // Other fields and so on

    @NotNull
    @JoinColumn(name = "slave_col_three", nullable = false)
    @ManyToOne(optional = false)
    private Slave slaveData;

}

@Entity
@Table(name="slave")    
public class Slave {

    @Column(name = "slave_col_one")
    private String slaveColumnOne;


    @Column(name = "slave_col_two")
    private String slaveColumnTwo;

  // Other fields

}

My native SQL query is like this:

SELECT * FROM master m LEFT JOIN 
 (SELECT DISTINCT slave_col_one, slave_col_two FROM slave) s ON m.master_col_one = s.slave_col_one 
    AND m.master_col_two = s.slave_col_two

I need to build a criteria query for the above native SQL query.

I have built this much query so far:

final CriteriaBuilder criteriaBuilder = getEntityManager().getCriteriaBuilder();
final CriteriaQuery<Master> criteriaQuery = criteriaBuilder
        .createQuery(Master.class);
final Root<Master> root = criteriaQuery.from(Master.class);
Join<Master, Slave> join = root.join(Master_.slaveData);

I think my join is not correct as it's not fetching distinct selective columns from slave table as per requirements, I searched the internet a lot but couldn't come up with a solution.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source