JPA many-to-one mapping for potentially null foreign key

Question

Problem

When running a query on an entity and an attribute of a many-to-one mapped entity, that may or may not be null, no records are returned on records where the mapped many-to-one is null, even if the main entity may match the search criteria. This behavior seems to be acting like an INNER JOIN instead of an OUTER JOIN.

Everything works fine for CRUD operations, the only problem is when the query is run.

Expected

I expect this to behave like a LEFT OUTER JOIN, and return records that match even if the mapped entity is null.

Expecting a query similar to this to return both property records below, when only preperty #10 is returned.

SELECT Property.* FROM Property
LEFT JOIN Resident ON Resident.id = Property.Resident_id
WHERE Property.Address LIKE "%test%" OR Resident.Name LIKE "%test%"

Environment

• MySQL v8
• Eclipselink v2.7.7

Tables

Property

id	Address	Resident_id
10	123 Test Dr.	20
11	456 Test St.	null

Resident

id	Name
10	John Doe
11	James Williams

Entities

Property

@Entity
@Table(name = "Property")
public class Property
{
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(name = "Resident_id")
    private Integer residentId;

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

    @ManyToOne(optional = true, fetch = FetchType.LAZY)
    @JoinColumn(name = "Resident_id", referencedColumnName = "id", updatable = false, insertable = false, nullable = true)
    private Resident resident;

Resident

@Entity
@Table(name = "Resident")
public class Resident
{
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

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

Query

public List<Property> search(String str)
{
    List<Property> list = new ArrayList<>();

    if(str == null || str.isEmpty())
        return list;

    try
    {

        String sql = "SELECT x FROM Property x WHERE " +
                " x.address LIKE :str OR x.resident.name LIKE :str" +
                " ORDER BY x.address";

        Query q = this.getEntityManager().createQuery(sql);

        q.setParameter("str", "%"+str+"%");

        list.addAll(q.getResultList());

    }
    catch(Exception e)
    {
        e.printStackTrace();
    }

    return list;
}

Answer 1

JPQL requires providers to treat '.' path expressions as inner joins, so calling x.resident.name will exclude null residents from the selection.

What you want is more along the lines of

String JPQL = "SELECT x FROM Property x left join x.resident resident " +
                "WHERE x.address LIKE :str OR resident.name LIKE :str" +
                " ORDER BY x.address";