Join a table with CriteriaBuilder and an additional parameter seems to have no effect

24 Views Asked by At

I have two entities that are structured as follows

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

   @EmbeddedId
   private PrimaryKey personPK = new PrimaryKey();

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

   @Column(name = "lastname")
   private String lastname;

   @OneToMany(mappedBy = "books")
   private Set<Books> books = new HashSet<>();
}

@Embeddable
public class PrimaryKey {

   @Column(name = "ID")
   private long id;

   @Column(name = "SOCIALNUMBER"
   private String socialNumber;
}

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

   @EmbeddedId
   private PrimaryKeyBooks booksPK = new PrimaryKeyBooks();

   @MapsId("personPK")
   @ManyToOne
   @JoinColumns({ @JoinColumn(name = "ID", referencedColumnName = "ID"), @JoinColumn(name = "SOCIALNUMBER",
        referencedColumnName = "SOCIALNUMBER") })
   private Person person;

   @Column(name = "title")
   private String title;

   @Column(name = "publisher")
   private String publisher;

   @Column(name = "isListed")
   private Boolean isListed;
}

PrimaryKey and PrimaryKeyBooks has the same attributes.

The attribute "isListed" could be true or false. Now I want a query that returns all persons. But the list of books should only be filled if the attribute "isListed" is true. Otherwise the list should be null. The SQL query I want is this:

SELECT DISTINCT * 
FROM Person t1 
left join books t2 on t1.id = t2.id and t1.socialNumber = t2.socialNumber 
and t2.isListed = true;

This query works exactly as I want it to. Now I want the same with the Criteria API. What I try is this:

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Person> query = criteriaBuilder.createQuery(Person.class);
Root<Person> personRoot = query.from(Person.class);
        
Join<Person, Books> join = personRoot.join(Person_.books, JoinType.LEFT);
join.on(criteriaBuilder.equal(join.get(Books_.isListed), true));
                
return entityManager.createQuery(query).getResultList()

But it looks like the additional parameter in the on function is ignored. As a result, I also get the people who have books whose isListed value is set to false. Could it be a problem, that the on function and the @JoinColumn annotation is not usable at the same time?

0

There are 0 best solutions below