Parameters in NamedQuery

Question

I am working with JSF 2.0, Primefaces 3.4, Oracle, NetBeans 7.2.1, and i got the following error:

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))

i have two beans SaeOptometria and SaePaciente; in SaeOPtometria i have this atribute that is the relationship with the SaePaciente bean:

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

And also have the following 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"),

I am passing the parameters :pIdentificacion and :pTIdentificacion in the SaeOptometriaFacade file with the following code:

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

I clarified that the parameters pIdentificacion and pTipoIdentificacion in the NamedQuery belong to 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;

why doesnt work?

thanks.

Remove "d.pId=s.optPId AND"

JSS
– JSS

2013-04-01 19:29:59 +00:00
Commented Apr 1, 2013 at 19:29 — JSS
– JSS, Commented Apr 1, 2013 at 19:29

Atilla Ozgur · Accepted Answer · 2013-03-31 17:49:38Z

2

Your query seems wrong:

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

After your comment, I decided that my first understanding is wrong. You both join in FROM clause and in the from WHERE clause. Try to do only one of them.

Can you try following queries and report results:

First one: Join is provided by JPA. No explicitly providing Ids. This information should be given in your JPA mapping

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

Second one: you join explicitly. We give tables and join giving ID values explicitly.

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

edited Mar 31, 2013 at 17:49

answered Mar 31, 2013 at 7:03

Atilla Ozgur

14.8k3 gold badges54 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user2228505 Over a year ago

Thank you for your answer. I applied this tutorial: openjpa.apache.org/builds/1.0.2/apache-openjpa-1.0.2/docs/… and the JPQL (of jsf 2.0) interpreted good the query, the problem is with the parameters.The query interpreted is this: 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))

David Levesque Over a year ago

I think Atilla is right, the problem is with the join, not with the parameters. The problematic part is the (t0.P_ID = ). Did you at least try his suggestions?

Chris Over a year ago

As Atilla mentioned, the query isn't valid. S.optPid is an entity, so it doesn't make sense to compare it to d.pId, an Integer. You don't need this at all because of the inner join defined in the from clause, but you would use 's.optPid = d' otherwise.

user2228505 Over a year ago

I tried with the first one, and it works perfectly. thank you for all.

Collectives™ on Stack Overflow

Parameters in NamedQuery

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related