In a optional OneToOne join scenario, how to get entity A that does not have a B?

Question

I have a class A, that has an optional link to an instance of a B class.

For the sake of user-friendliness, I want to filter the output of the grid displaying the instances of the A class, with a switch allowing to display all of As, only As with a B or only As without a B.

My abstract DAO is taking care of the process by dynamically building criteria queries according to the user desire for sorting/filtering.

Works great for the two first scenarios, however, the last one always returns an empty list.

Looking at the generated SQL code, I've got something like this (stripped down on purpose):

Select t0.id as id [...] from person t0, user_profile t1 where t1.person_id = t0.id and t1.people_id is null;

I see the logic behind this. However, this will definitely never work for my last case. I wonder how to get around this use case ? Any help ?

The criteria is built programmatically, here is how it would look like if built manually:

final CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
CriteriaQuery<T> cq = getContextQuery(context); // Juste create the base Criteria
Root<T> root = findRoot(cq); // Retrieve the root.
Path p = root.join(part, JoinType.LEFT); // 'part' is the entity to join on, got from metamodel.
cq.where(cb.isNull(p));
return getEntityManager().createQuery(cq).getResultList();

What entities look like (stripped down to the meaningful part):

@Entity
@Table(name = "person")
@Getter
@Setter
public class Person extends AbstractEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    protected Integer id;

    @XmlTransient
    @OneToOne(optional = true, mappedBy = "person")
    private UserProfile user;
}

@Entity
@Table(name = "user_profile")
public class UserProfile extends AbstractEntity implements Authenticated {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    protected Integer id;

    @OneToOne(optional = false)
    @JoinColumn(name = "person_id", referencedColumnName = "id")
    @Getter
    @Setter
    private Person person;
}

Answer 1

Using your entities asumming A = UserProfile and B = Person

I want to filter the output of the grid displaying the instances of the A class, with a switch allowing to display all of As,

CriteriaBuilder cb = ...
CriteriaQuery<UserProfile> q = cb.createQuery(UserProfile .class);
Root<UserProfile> userProfile= q.from(UserProfile.class);
q.fetch(userProfile.get("person"), JoinType.LEFT);
q.select(userProfile)

only As with a B

CriteriaBuilder cb = ...
CriteriaQuery<UserProfile> q = cb.createQuery(UserProfile .class);
Root<UserProfile> userProfile= q.from(UserProfile .class);
q.select(userProfile)
 q.where(cb.isNotEmpty(userProfile.get("person"));

OR

CriteriaBuilder cb = ...
CriteriaQuery<UserProfile> q = cb.createQuery(UserProfile .class);
Root<UserProfile> userProfile= q.from(UserProfile .class);
Join<UserProfile, Person> person = userProfile.join("person", JoinType.INNER);
q.select(userProfile)

or only As without a B

CriteriaBuilder cb = ...
CriteriaQuery<UserProfile> q = cb.createQuery(UserProfile .class);
Root<UserProfile> userProfile= q.from(UserProfile .class);
q.select(userProfile)
 q.where(cb.isEmpty(userProfile.get("person"));

OR

CriteriaBuilder cb = ...
CriteriaQuery<UserProfile> q = cb.createQuery(UserProfile .class);
Root<UserProfile> userProfile= q.from(UserProfile .class);
Join<UserProfile, Person> person = userProfile.join("person", JoinType.lEFT);
q.select(userProfile)

And then

return getEntityManager().createQuery(q).getResultList();

That should work.