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

Resident

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;
}

CodePudding user response：

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";