Hi fellas,

I need to execute a request in native SQL through Hibernate. I want to retreive all the 'Programme' entities related to the 'Etudiant' entities for a given 'enquete_id' of the 'Etudiant'. Simply put, in which academic programs are the students of a given survey.

I use :



The resulting SQL is valid, since I can put it in SQL Plus and get my results. Still, Hibernate gives me an error and says that my SQL has an error.

Of course, I can't be sure of the exactitude of my query, since the outputted SQL contains a '?' character, and the error comes from this place.

What are my options ?

FYI. I can't add a students collection to the program entity, for it would be WAY TOO MUCH data to fetch each time. We tried that and the performances go down drastically. That's why I'm using native SQL here.



Hibernate version:

3.2.0 CR5


Mapping documents:


For the Enquete entity :

<?xml version="1.0" encoding='UTF-8'?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="ca.uquebec.icope.data.model.populationCible">

<class name="Enquete" table="ENQUETE" proxy="Enquete" lazy="true">

<id name="id" column="ID" type="long" access="field">
<generator class="hilo" />
</id>

<version name="_version" column="VERSION" access="field" />

<property name="libelle" type="java.lang.String" not-null="true" access="field">
<column name="LIBELLE" length="30"/>
</property>

<property name="dateDebut" column="DATE_DEBUT" type="java.sql.Timestamp" />

<property name="dateFin" column="DATE_FIN" type="java.sql.Timestamp" />

<property name="typeEnquete" column="TYPE_ENQUETE" type="ca.uquebec.icope.data.hibernate.mappingType.custom.EnumTypeEnqueteUserType" not-null="true"/>

<many-to-one name="questionnaire" column="QUESTIONNAIRE_ID" class="ca.uquebec.icope.data.model.questionnaire.QuestionnaireReel" not-null="true" lazy="no-proxy"/>

<!-- J'ai mis la collection lazy=extra pour accéder à get() sans provoquer l'initialisation de la collection -->
<set name="envois" table="ENVOI" cascade="all-delete-orphan" lazy="extra" inverse="true">
<key column="ENQUETE_ID" />
<one-to-many class="Envoi" />
</set>

<set name="criteres" table="CRITERE_SELECTION" lazy="false" cascade="all-delete-orphan" inverse="true">
<key column="ENQUETE_ID"/>
<one-to-many class="ca.uquebec.icope.data.model.populationCible.CritereSelection" />
</set>

<!-- J'ai mis la collection lazy=extra pour accéder à size() sans provoquer l'initialisation de la collection -->
<set name="etudiants" table="ETUDIANT" lazy="extra" batch-size="100" cascade="all-delete-orphan" inverse="true"> <!-- cascade="save-update" -->
<key column="ENQUETE_ID"/>
<one-to-many class="ca.uquebec.icope.data.model.populationCible.Etudiant" />
</set>


</class>

</hibernate-mapping>





SQL for the Enquete entity :

create table "QUEST"."ENQUETE"(
"ID" NUMBER(19) not null,
"VERSION" NUMBER(10) not null,
"LIBELLE" VARCHAR2(30),
"DATE_DEBUT" DATE,
"DATE_FIN" DATE,
"TYPE_ENQUETE" VARCHAR2(255) not null,
"QUESTIONNAIRE_ID" NUMBER(19) not null,
constraint "SYS_C0017207" primary key ("ID")
);

alter table "QUEST"."ENQUETE"
add constraint "FKCBC519C9171E4AB4"
foreign key ("QUESTIONNAIRE_ID")
references "QUEST"."QUESTIONNAIRE"("ID");
create unique index "QUEST"."SYS_C0017207" on "QUEST"."ENQUETE"("ID");





For the Etudiant entity :

<?xml version="1.0" encoding='UTF-8'?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="ca.uquebec.icope.data.model.populationCible">

<class name="Etudiant" table="ETUDIANT" proxy="Etudiant" batch-size="25">

<id name="id" column="ID" type="long" access="field">
<generator class="hilo" />
</id>

<version name="_version" column="VERSION" access="field" />

<property name="codePermanent" column="PERM_CD" type="java.lang.String" length="12" not-null="true" access="field"/>

<property name="reponseRecu" column="REPONSE_RECU" type="boolean" not-null="true"/>

<property name="dernierEnvoiReussi" column="DERNIER_ENVOI_REUSSI" type="boolean" not-null="true"/>

<property name="nom" column="NOM" type="java.lang.String" length="40" not-null="true" access="field"/>

<property name="prenom" column="PRENOM" type="java.lang.String" length="40" not-null="true" access="field"/>

<one-to-one name="questionnaireComplete" class="ca.uquebec.icope.data.model.reception.QuestionnaireComplete" cascade="save-update,delete" property-ref="etudiant" />

<property name="courriel" type="java.lang.String" not-null="true" access="field">
<column name="COURRIEL" length="80" />
</property>

<!-- <one-to-one name="courriel" class="Courriel" property-ref="etudiant" cascade="save-update, delete" lazy="proxy"/>-->

<property name="rue" column="RUE" type="java.lang.String" length="40" access="field" />
<property name="ville" column="VILLE" type="java.lang.String" length="60" access="field" />
<property name="codePostal" column="CODE_POSTAL" type="java.lang.String" length="6" access="field" />

<!-- <one-to-one name="adresse" class="Adresse" property-ref="etudiant" cascade="save-update, delete" lazy="proxy"/>-->

<many-to-one name="enquete" column="ENQUETE_ID" class="ca.uquebec.icope.data.model.populationCible.Enquete" not-null="true" lazy="proxy" />

<many-to-one name="etablissement" column="ETA_CD" class="ca.uquebec.icope.data.model.populationCible.Etablissement" not-null="true" lazy="proxy" />

<many-to-one name="programme" column="PROGRAMME_ID" class="ca.uquebec.icope.data.model.populationCible.Programme" not-null="true" lazy="proxy" />

</class>

</hibernate-mapping>




SQL for the 'Enquete' entity :

create table "QUEST"."ETUDIANT"(
"ID" NUMBER(19) not null,
"VERSION" NUMBER(10) not null,
"PERM_CD" VARCHAR2(12) not null,
"REPONSE_RECU" NUMBER(1) not null,
"DERNIER_ENVOI_REUSSI" NUMBER(1) not null,
"NOM" VARCHAR2(40) not null,
"PRENOM" VARCHAR2(40) not null,
"COURRIEL" VARCHAR2(80),
"RUE" VARCHAR2(40),
"VILLE" VARCHAR2(60),
"CODE_POSTAL" VARCHAR2(6),
"ENQUETE_ID" NUMBER(19) not null,
"ETA_CD" CHAR(2) not null,
"PROGRAMME_ID" VARCHAR2(4) not null,
constraint "SYS_C0017227" primary key ("ID")
);

alter table "QUEST"."ETUDIANT"
add constraint "FKF028E15CD98BC79"
foreign key ("ENQUETE_ID")
references "QUEST"."ENQUETE"("ID");
create unique index "QUEST"."SYS_C0017227" on "QUEST"."ETUDIANT"("ID");




Mapping for the 'Programme' entity :

<?xml version="1.0" encoding='UTF-8'?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="ca.uquebec.icope.data.model.populationCible">
<class name="Programme" table="PROGRAMME" proxy="Programme" schema="PRISME" >
<cache usage="read-only"/>
<id name="pgmCd" column="PGM_CD" type="java.lang.String" length="4">
<generator class="assigned" />
</id>
<property name="dtv" type="java.util.Date">
<column name="DTV" length="19" />
</property>
<property name="pgmNomAbg" type="java.lang.String">
<column name="PGM_NOM_ABG" length="63" />
</property>
<property name="pgmOffCd" type="java.lang.String">
<column name="PGM_OFF_CD" length="4" />
</property>
<property name="genCd" type="java.lang.String">
<column name="GEN_CD" length="3" />
</property>
<property name="pgmCl" type="java.lang.String">
<column name="PGM_CL" length="1" />
</property>
<property name="durMin" type="java.lang.Integer">
<column name="DUR_MIN" />
</property>
<property name="cdrPrinCd" type="java.lang.String">
<column name="CDR_PRIN_CD" length="4" />
</property>
<property name="cdrSecnCd" type="java.lang.String">
<column name="CDR_SECN_CD" length="4" />
</property>
<property name="etdNiv" type="java.lang.String">
<column name="ETD_NIV" length="2" />
</property>
<property name="finaEtdNiv" type="java.lang.String">
<column name="FINA_ETD_NIV" length="2" />
</property>
<property name="iclmCd" type="java.lang.String">
<column name="ICLM_CD" length="4" />
</property>
<property name="dtm" type="java.util.Date">
<column name="DTM" length="19" />
</property>
<property name="nomenNt" type="java.lang.String">
<column name="NOMEN_NT" length="232" />
</property>
<property name="crTrsfInd" type="java.lang.String">
<column name="CR_TRSF_IND" length="1" />
</property>
<property name="discUqCd" type="java.lang.Integer">
<column name="DISC_UQ_CD" />
</property>
<property name="pgmAnnTri" type="java.lang.String">
<column name="PGM_ANN_TRI" length="60" />
</property>
<property name="pgmHistInd" type="java.lang.String">
<column name="PGM_HIST_IND" length="1" />
</property>
<property name="postDocInd" type="java.lang.String">
<column name="POST_DOC_IND" length="1" />
</property>
<property name="siscuGr" type="java.lang.Integer">
<column name="SISCU_GR" />
</property>
</class>
</hibernate-mapping>




SQL for the 'Programme' table :

create table PROGRAMME (
PGM_CD varchar2(4) not null,
DTV date,
PGM_NOM_ABG varchar2(63),
PGM_OFF_CD varchar2(4),
GEN_CD varchar2(3),
PGM_CL varchar2(1),
DUR_MIN number(10,0),
CDR_PRIN_CD varchar2(4),
CDR_SECN_CD varchar2(4),
ETD_NIV varchar2(2),
FINA_ETD_NIV varchar2(2),
ICLM_CD varchar2(4),
DTM date,
NOMEN_NT varchar2(232),
CR_TRSF_IND varchar2(1),
DISC_UQ_CD number(10,0),
PGM_ANN_TRI varchar2(60),
PGM_HIST_IND varchar2(1),
POST_DOC_IND varchar2(1),
SISCU_GR number(10,0),
primary key (PGM_CD));




Code between sessionFactory.openSession() and session.close():





Full stack trace of any exception that occurs:

WARN - 2006-10-13 10:10:47,109 org.hibernate.util.JDBCExceptionReporter [http-8080-Processor3]
SQL Error: 904, SQLState: S0022
WARN - 2006-10-13 10:10:47,109 org.hibernate.util.JDBCExceptionReporter [http-8080-Processor3]
SQL Error: 904, SQLState: S0022
ERROR - 2006-10-13 10:10:47,109 org.hibernate.util.JDBCExceptionReporter [http-8080-Processor3]
[Oracle] #1 ORA-00904: "E"."ENQUETE_ID": invalid identifier

ERROR - 2006-10-13 10:10:47,109 org.hibernate.util.JDBCExceptionReporter [http-8080-Processor3]
[Oracle] #1 ORA-00904: "E"."ENQUETE_ID": invalid identifier

org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2147)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2028)
at org.hibernate.loader.Loader.list(Loader.java:2023)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:150)
at ca.uquebec.icope.data.dao.hibernate.populationCible.EnqueteDAOHibernate.getProgrammesConcernes(EnqueteDAOHibernate.java:52)
at ca.uquebec.icope.service.populationCible.EnqueteImpl.getProgrammesConcernes(EnqueteImpl.java:430)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

[...]

Caused by: java.sql.SQLException: [Oracle] #1 ORA-00904: "E"."ENQUETE_ID": invalid identifier

at com.inet.ora.OraDriver.a(Unknown Source)
at com.inet.ora.o.a(Unknown Source)
at com.inet.ora.o.a(Unknown Source)
at com.inet.ora.ad.try(Unknown Source)
at com.inet.ora.ad.a(Unknown Source)
at com.inet.ora.x.a(Unknown Source)
at com.inet.ora.x.for(Unknown Source)
at com.inet.ora.x.executeQuery(Unknown Source)
at com.inet.pool.a.executeQuery(Unknown Source)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1668)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2144)
... 95 more





Name and version of the database you are using:

Oracle 9i






The generated SQL (show_sql=true):

select distinct p.PGM_CD as PGM1_23_0_, p.DTV as DTV23_0_, p.PGM_NOM_ABG as PGM3_23_0_, p.PGM_OFF_CD as PGM4_23_0_, p.GEN_CD as GEN5_23_0_, p.PGM_CL as PGM6_23_0_, p.DUR_MIN as DUR7_23_0_, p.CDR_PRIN_CD as CDR8_23_0_, p.CDR_SECN_CD as CDR9_23_0_, p.ETD_NIV as ETD10_23_0_, p.FINA_ETD_NIV as FINA11_23_0_, p.ICLM_CD as ICLM12_23_0_, p.DTM as DTM23_0_, p.NOMEN_NT as NOMEN14_23_0_, p.CR_TRSF_IND as CR15_23_0_, p.DISC_UQ_CD as DISC16_23_0_, p.PGM_ANN_TRI as PGM17_23_0_, p.PGM_HIST_IND as PGM18_23_0_, p.POST_DOC_IND as POST19_23_0_, p.SISCU_GR as SISCU20_23_0_ from programme p, etudiant e where e.programme_id = p.pgm_cd and e.enquete_id = ?