NamedQuery中的参数

Question

我正在使用JSF 2.0, Primefaces 3.4, Oracle, NetBeans 7.2.1，我得到了以下错误:

ADVERTENCIA: Local Exception Stack: 
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: ORA-00936: missing expression
Error Code: 936
Call: SELECT t1.OPT_ID, t1.OPT_ANT_FM_DET FROM SAE_PACIENTE t0, SAE_OPTOMETRIA t1 WHERE ((((t0.P_ID = ) AND (t0.P_IDENTIFICACION = ?)) AND (t0.P_TIPO_IDENTIFICACION = ?)) AND (t0.P_ID = t1.OPT_P_ID))

我有两颗豆子:验光和验光;在SaeOPtometria中，我有这个属性，它是与SaePaciente bean的关系:

@JoinColumn(name = "OPT_P_ID", referencedColumnName = "P_ID")
@ManyToOne
private SaePaciente optPId;

还有下面的namedquery:

@NamedQuery(name = "SaeOptometria.findByPaciente", query = "SELECT s FROM SaeOptometria s INNER JOIN s.optPId d WHERE d.pId=s.optPId AND d.pIdentificacion = :pIdentificacion AND d.pTipoIdentificacion = :pTIdentificacion"),

我在SaeOptometriaFacade文件中传递参数:pidentity和:pidentity，代码如下:

public List<SaeOptometria> consultaporIdYTI(String UIpIdentificacion, String UIpTipoIdentificacion){
    Query query= em.createNamedQuery("SaeOptometria.findByPaciente");
    query.setParameter("pIdentificacion", UIpIdentificacion);
    query.setParameter("pTIdentificacion", UIpTipoIdentificacion);
    List<SaeOptometria> SaeOptometriaList= query.getResultList();
    return SaeOptometriaList;
}

我澄清了NamedQuery中的参数pidentity和ptipoidentity属于SaePaciente bean:

@Id
@SequenceGenerator(name="SEQ1",sequenceName = "SAE_AINCR_PACIENTE",allocationSize=1)  
@GeneratedValue(strategy=GenerationType.IDENTITY, generator="SEQ1")
@Basic(optional = false)
@NotNull
@Column(name = "P_ID")
private Integer pId;
@Size(max = 15)
@Column(name = "P_IDENTIFICACION")
private String pIdentificacion;
@Size(max = 20)
@Column(name = "P_TIPO_IDENTIFICACION")
private String pTipoIdentificacion;

为什么不工作?

谢谢。

Answer 1

您的查询似乎是错误的:

SELECT s FROM SaeOptometria s INNER JOIN s.optPId d WHERE d.pId=s.optPId AND d.pIdentificacion = :pIdentificacion AND d.pTipoIdentificacion = :pTIdentificacion

FROM SaeOptometria s INNER JOIN TO_WHAT

听了你的评论，我觉得我最初的理解是错误的。在FROM子句和FROM WHERE子句中都连接。试着只做其中一个。

您可以尝试以下查询并报告结果吗?

第一个:Join由JPA提供。没有明确提供id。该信息应该在JPA映射

中给出。

SELECT s FROM SaeOptometria s INNER JOIN s.optPId d WHERE d.pIdentificacion = :pIdentificacion AND d.pTipoIdentificacion = :pTIdentificacion

第二个:显式连接。我们给表和连接显式地给出ID值。

SELECT s FROM SaeOptometria s WHERE s.optPId.pId=s.optPId and s.optPId.pIdentificacion = :pIdentificacion AND s.optPId.pTipoIdentificacion = :pTIdentificacion