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?