为什么 JPQL 左连接 ON 子句会导致空指针异常

Question

虽然这个JPQL工作没有任何异常

Query query = em.createQuery("SELECT DISTINCT(r) FROM Request r LEFT JOIN Requesthistory h 
                                                    WHERE h.request = r 
                                                    AND (r.responsible = :employee OR h.employee = :employee) 
                                                    AND r.requestedby != :employee ORDER BY r.objid DESC");

这个 JPQL 不起作用并抛出 NullPointerException

Query query = em.createQuery("SELECT DISTINCT(r) FROM Request r LEFT JOIN Requesthistory h 
                                                ON h.request = r 
                                                WHERE (r.responsible = :employee OR h.employee = :employee) 
                                                AND r.requestedby != :employee ORDER BY r.objid DESC");

这两个JPQL之间的唯一区别是ON条款。

是的，实体和表中的请求和请求历史记录之间存在 1：N 的关系。

这是我得到的例外：

Exception Description: Query failed to prepare, unexpected error occurred: [java.lang.NullPointerException].
Internal Exception: java.lang.NullPointerException
Query: ReportQuery(referenceClass=Request jpql="SELECT DISTINCT(r) FROM Request r LEFT JOIN Requesthistory h ON h.request = r WHERE  (r.responsible = :employee OR h.employee = :employee) AND r.requestedby != :employee ORDER BY r.objid DESC")
    at org.eclipse.persistence.exceptions.QueryException.prepareFailed(QueryException.java:1590) [eclipselink.jar:2.5.2.v20140319-9ad6abd]
    at org.eclipse.persistence.queries.DatabaseQuery.checkPrepare(DatabaseQuery.java:680) [eclipselink.jar:2.5.2.v20140319-9ad6abd]
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.checkPrepare(ObjectLevelReadQuery.java:901) [eclipselink.jar:2.5.2.v20140319-9ad6abd]
    at org.eclipse.persistence.queries.DatabaseQuery.checkPrepare(DatabaseQuery.java:613) [eclipselink.jar:2.5.2.v20140319-9ad6abd]
    at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:194) [eclipselink.jar:2.5.2.v20140319-9ad6abd]
    at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:116) [eclipselink.jar:2.5.2.v20140319-9ad6abd]
    at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:102) [eclipselink.jar:2.5.2.v20140319-9ad6abd]
    at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:86) [eclipselink.jar:2.5.2.v20140319-9ad6abd]
    at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:1603) [eclipselink.jar:2.5.2.v20140319-9ad6abd]
    at com.kadir.dao.notification.RequestDao.findToUserRequests(RequestDao.java:44) [classes:]
    at com.kadir.service.notification.RequestService.getToUserRequests(RequestService.java:43) [classes:]
    at com.kadir.bean.notification.RequestBean.init(RequestBean.java:49) [classes:]
    ... 110 more
Caused by: java.lang.NullPointerException
    at org.eclipse.persistence.internal.expressions.ObjectExpression.getOwnedTables(ObjectExpression.java:583) [eclipselink.jar:2.5.2.v20140319-9ad6abd]
    at org.eclipse.persistence.internal.expressions.FieldExpression.validateNode(FieldExpression.java:294) [eclipselink.jar:2.5.2.v20140319-9ad6abd]
    at org.eclipse.persistence.expressions.Expression.normalize(Expression.java:3275) [eclipselink.jar:2.5.2.v20140319-9ad6abd]
    at org.eclipse.persistence.internal.expressions.DataExpression.normalize(DataExpression.java:369) [eclipselink.jar:2.5.2.v20140319-9ad6abd]
    at org.eclipse.persistence.internal.expressions.FieldExpression.normalize(FieldExpression.java:223) [eclipselink.jar:2.5.2.v20140319-9ad6abd]
    at org.eclipse.persistence.internal.expressions.CompoundExpression.normalize(CompoundExpression.java:224) [eclipselink.jar:2.5.2.v20140319-9ad6abd]
    at org.eclipse.persistence.internal.expressions.RelationExpression.normalize(RelationExpression.java:574) [eclipselink.jar:2.5.2.v20140319-9ad6abd]
    at org.eclipse.persistence.internal.expressions.RelationExpression.normalize(RelationExpression.java:865) [eclipselink.jar:2.5.2.v20140319-9ad6abd]
    at org.eclipse.persistence.expressions.ExpressionBuilder.normalize(ExpressionBuilder.java:267) [eclipselink.jar:2.5.2.v20140319-9ad6abd]
    at org.eclipse.persistence.internal.expressions.RelationExpression.normalize(RelationExpression.java:825) [eclipselink.jar:2.5.2.v20140319-9ad6abd]
    at org.eclipse.persistence.internal.expressions.CompoundExpression.normalize(CompoundExpression.java:232) [eclipselink.jar:2.5.2.v20140319-9ad6abd]
    at org.eclipse.persistence.internal.expressions.CompoundExpression.normalize(CompoundExpression.java:224) [eclipselink.jar:2.5.2.v20140319-9ad6abd]
    at org.eclipse.persistence.internal.expressions.SQLSelectStatement.normalize(SQLSelectStatement.java:1449) [eclipselink.jar:2.5.2.v20140319-9ad6abd]
    at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.buildReportQuerySelectStatement(ExpressionQueryMechanism.java:641) [eclipselink.jar:2.5.2.v20140319-9ad6abd]
    at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.buildReportQuerySelectStatement(ExpressionQueryMechanism.java:586) [eclipselink.jar:2.5.2.v20140319-9ad6abd]
    at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.prepareReportQuerySelectAllRows(ExpressionQueryMechanism.java:1694) [eclipselink.jar:2.5.2.v20140319-9ad6abd]
    at org.eclipse.persistence.queries.ReportQuery.prepareSelectAllRows(ReportQuery.java:1203) [eclipselink.jar:2.5.2.v20140319-9ad6abd]
    at org.eclipse.persistence.queries.ReadAllQuery.prepare(ReadAllQuery.java:744) [eclipselink.jar:2.5.2.v20140319-9ad6abd]
    at org.eclipse.persistence.queries.ReportQuery.prepare(ReportQuery.java:1071) [eclipselink.jar:2.5.2.v20140319-9ad6abd]
    at org.eclipse.persistence.queries.DatabaseQuery.checkPrepare(DatabaseQuery.java:661) [eclipselink.jar:2.5.2.v20140319-9ad6abd]
    ... 120 more

我做错了什么？

实体更新

请求.java

package com.kadir.entity.notification;
import com.kadir.entity.Base;
import com.kadir.entity.humanresource.Employee;
import java.io.Serializable;
import javax.persistence.*;

import java.util.List;

/**
 * The persistent class for the REQUEST database table.
 * 
 */
@Cacheable
@Entity
@Table(name="REQUEST", schema="NOTIFICATION")
@NamedQuery(name="Request.findAll", query="SELECT r FROM Request r")
public class Request extends Base implements Serializable {
    private static final long serialVersionUID = 1L;
    @Column(name="CONTENT")
    private String content;
    @ManyToOne
    @JoinColumn(name="REQUESTEDBY")
    private Employee requestedby;
    @ManyToOne
    @JoinColumn(name="RESPONSIBLEOBJID")
    private Employee responsible;
    @Column(name="TITLE")
    private String title;
    //bi-directional many-to-one association to Requesttype
    @ManyToOne
    @JoinColumn(name="REQUESTTYPEOBJID")
    private Requesttype requesttype;
    //bi-directional many-to-one association to Responsetype
    @ManyToOne
    @JoinColumn(name="RESPONSETYPEOBJID")
    private Responsetype responsetype;
    //bi-directional many-to-one association to Requesthistory
    @OneToMany(mappedBy="request")
    private List<Requesthistory> requesthistories;
    public Request() {
    }
    public String getContent() {
        return this.content;
    }
    public void setContent(String content) {
        this.content = content;
    }
    public Employee getRequestedby() {
        return this.requestedby;
    }
    public void setRequestedby(Employee requestedby) {
        this.requestedby = requestedby;
    }
    public Employee getResponsible() {
        return this.responsible;
    }
    public void setResponsible(Employee responsible) {
        this.responsible = responsible;
    }
    public String getTitle() {
        return this.title;
    }
    public void setTitle(String title) {
        this.title = title;
    }
    public Requesttype getRequesttype() {
        return this.requesttype;
    }
    public void setRequesttype(Requesttype requesttype) {
        this.requesttype = requesttype;
    }
    public Responsetype getResponsetype() {
        return this.responsetype;
    }
    public void setResponsetype(Responsetype responsetype) {
        this.responsetype = responsetype;
    }
    public List<Requesthistory> getRequesthistories() {
        return this.requesthistories;
    }
    public void setRequesthistories(List<Requesthistory> requesthistories) {
        this.requesthistories = requesthistories;
    }
    public Requesthistory addRequesthistory(Requesthistory requesthistory) {
        getRequesthistories().add(requesthistory);
        requesthistory.setRequest(this);
        return requesthistory;
    }
    public Requesthistory removeRequesthistory(Requesthistory requesthistory) {
        getRequesthistories().remove(requesthistory);
        requesthistory.setRequest(null);
        return requesthistory;
    }
}

请求历史记录.java

package com.kadir.entity.notification;
import com.kadir.entity.humanresource.Employee;
import com.kadir.entity.Base;
import java.io.Serializable;
import javax.persistence.*;
/**
 * The persistent class for the REQUESTHISTORY database table.
 * 
 */
@Cacheable
@Entity
@Table(name="REQUESTHISTORY", schema="NOTIFICATION")
@NamedQuery(name="Requesthistory.findAll", query="SELECT r FROM Requesthistory r")
public class Requesthistory extends Base implements Serializable {
    private static final long serialVersionUID = 1L;
    @ManyToOne
    @JoinColumn(name="EMPLOYEEOBJID")
    private Employee employee;
    @Column(name="EXPLANATION")
    private String explanation;
    //bi-directional many-to-one association to Request
    @ManyToOne
    @JoinColumn(name="REQUESTOBJID")
    private Request request;
    //bi-directional many-to-one association to Responsetype
    @ManyToOne
    @JoinColumn(name="RESPONSETYPEOBJID")
    private Responsetype responsetype;
    public Requesthistory() {
    }
    public Employee getEmployee() {
        return this.employee;
    }
    public void setEmployee(Employee employee) {
        this.employee = employee;
    }
    public String getExplanation() {
        return this.explanation;
    }
    public void setExplanation(String explanation) {
        this.explanation = explanation;
    }
    public Request getRequest() {
        return this.request;
    }
    public void setRequest(Request request) {
        this.request = request;
    }
    public Responsetype getResponsetype() {
        return this.responsetype;
    }
    public void setResponsetype(Responsetype responsetype) {
        this.responsetype = responsetype;
    }
}

基地.java

package com.kadir.entity;
import java.math.BigInteger;
import java.sql.Timestamp;
import java.util.Date;
import javax.persistence.Cacheable;
import javax.persistence.Column;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.MappedSuperclass;
import javax.persistence.Version;
@Cacheable
@MappedSuperclass
public abstract class Base {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "OBJID")
    private BigInteger objid;
    @Column(name = "CREATEDBY")
    private String createdby;
    @Column(name = "CREATEDDATE")
    private Timestamp createddate;
    @Version
    @Column(name = "ROWVERSION")
    private Integer rowversion;
    @Column(name = "UPDATEDBY")
    private String updatedby;
    @Column(name = "UPDATEDDATE")
    private Timestamp updateddate;
    @Column(name = "ARCHIVED", columnDefinition = "int default 0")
    private int archived;
    public BigInteger getObjid() {
        return this.objid;
    }
    public void setObjid(BigInteger objid) {
        this.objid = objid;
    }
    public String getCreatedby() {
        return this.createdby;
    }
    public void setCreatedby(String createdby) {
        this.createdby = createdby;
    }
    public Date getCreateddate() {
        return this.createddate;
    }
    public void setCreateddate(Timestamp createddate) {
        this.createddate = createddate;
    }
    public Integer getRowversion() {
        return this.rowversion;
    }
    public void setRowversion(Integer rowversion) {
        this.rowversion = rowversion;
    }
    public String getUpdatedby() {
        return this.updatedby;
    }
    public void setUpdatedby(String updatedby) {
        this.updatedby = updatedby;
    }
    public Timestamp getUpdateddate() {
        return this.updateddate;
    }
    public void setUpdateddate(Timestamp updateddate) {
        this.updateddate = updateddate;
    }
    public int getArchived() {
        return archived;
    }
    public void setArchived(int archived) {
        this.archived = archived;
    }
}

Answer 1

我认为问题出在您的继承映射上。映射的 supperclass Base 没有定义继承策略，因此使用默认的继承策略SINGLE_TABLE。接下来，您在每个子Request上定义@Table(name="...")注释，并Requesthistory SINGLE_TABLE策略没有意义，因为使用此策略，所有子类都映射到一个共享表。我认为TABLE_PER_CLASS是你想要的策略。

@Cacheable
@MappedSuperclass
@Inheritance(strategy = InheritanceType.TABLE_PER_CLASS)
public abstract class Base {

仔细查看查询后更新

在 jpql 中，连接子句的使用与在 sql 中略有不同。不能联接任意实体，然后通过 ON 或 WHERE 子句绑定它们。只能联接抽象架构中关联的实体。

尝试以下（jpql 有效）查询：

 SELECT DISTINCT(r) 
 FROM Request r 
 LEFT JOIN r.requesthistories h 
 WHERE (r.responsible = :employee OR h.employee = :employee)  
     AND r.requestedby != :employee 
 ORDER BY r.objid DESC

（注意：可以查询非关联实体，但这是不同的主题。