aspose file tools*
The moose likes Object Relational Mapping and the fly likes Problems in Hibernate executing stored procedure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Problems in Hibernate executing stored procedure" Watch "Problems in Hibernate executing stored procedure" New topic
Author

Problems in Hibernate executing stored procedure

walid mansia
Greenhorn

Joined: Nov 07, 2008
Posts: 1
Hi everyone,
first of all, i will say that i've looked during 2 days now in internet to find a solution but i couldn't find anyone.
my problem is: i cant run a stored procedure, my files are:
HBM:
<hibernate-mapping>
<class name="com.allence.opcapl.alpha2.common.model.dossieradherent.DossierAdherentList" >
<meta attribute="extends" inherit="false">com.allence.commons.model.BusinessObject</meta>

<id name="id" column="DOSSIER_ADHERENT_ID" type="java.lang.Long">
<generator class="assigned"/>
</id>


<property name="dossierAdherentId" type="java.lang.Long" />
<property name="dossierFormationStdIdFk" type="java.lang.Long" />
<property name="dossierFormationNumero" type="string" />
<property name="dossierAdherentNumero" type="string" />
<property name="dossierFormationType" type="string" />
<property name="dossierFormationLibelle" type="string" />
<property name="dossierFormationDateDebut" type="java.util.Date" />
<property name="dossierFormationDateFin" type="java.util.Date" />
<property name="dossierAdherentStatut" type="string" />
<property name="dossierFormationStatut" type="string" />
<property name="typeFormationLibelle" type="string" />
<property name="dossierFormationStdTypeAccordEngagement" type="string" />
<property name="dossierAdherentStatutEnvoiContrat" type="string" />
<property name="codeNAF" type="string" />


<loader query-ref="getDossierAdherent" />
</class>
<sql-query name="getDossierAdherent" callable="true" >
<return alias="SpSearchDA" class="com.allence.opcapl.alpha2.common.model.dossieradherent.DossierAdherentList">
<return-property name="id" column="DOSSIER_ADHERENT_ID" />
<return-property name="dossierAdherentId" column="DOSSIER_ADHERENT_ID" />
<return-property name="dossierFormationStdIdFk" column="DOSSIER_FORMATION_STD_ID_FK" />
<return-property name="dossierFormationNumero" column="DOSSIER_FORMATION_NUMERO" />
<return-property name="dossierAdherentNumero" column="DOSSIER_ADHERENT_NUMERO"/>
<return-property name="dossierFormationType" column="DOSSIER_FORMATION_TYPE" />
<return-property name="dossierFormationLibelle" column="DOSSIER_FORMATION_LIBELLE" />
<return-property name="dossierFormationDateDebut" column="DOSSIER_FORMATION_DATE_DEBUT" />
<return-property name="dossierFormationDateFin" column="DOSSIER_FORMATION_DATE_FIN" />
<return-property name="dossierAdherentStatut" column="DOSSIER_ADHERENT_STATUT" />
<return-property name="dossierFormationStatut" column="DOSSIER_FORMATION_STATUT" />
<return-property name="typeFormationLibelle" column="TYPE_FORMATION_LIBELLE" />
<return-property name="dossierFormationStdTypeAccordEngagement" column="DOSSIER_FORMATION_STD_TYPE_ACCORD_ENGAGEMENT" />
<return-property name="dossierAdherentStatutEnvoiContrat" column="DOSSIER_ADHERENT_STATUT_ENVOI_CONTRAT" />
<return-property name="codeNAF" column="CODE_NAF" />

</return>
{call SEARCH_ADHERENT()}

</sql-query>
</hibernate-mapping>


SQL SERVER STORED PROCEDURE:
ALTER PROCEDURE [dbo].[SEARCH_ADHERENT]

as
DECLARE @requete varchar(4000);

SET @requete ='SELECT TOP 1000 dbo.DOSSIER_ADHERENT.DOSSIER_ADHERENT_ID, dbo.DOSSIER_FORMATION_STD.DOSSIER_FORMATION_STD_ID_FK,
dbo.DOSSIER_FORMATION.DOSSIER_FORMATION_NUMERO, dbo.DOSSIER_ADHERENT.DOSSIER_ADHERENT_NUMERO,
dbo.DOSSIER_FORMATION.DOSSIER_FORMATION_TYPE, dbo.DOSSIER_FORMATION.DOSSIER_FORMATION_LIBELLE,
dbo.DOSSIER_FORMATION.DOSSIER_FORMATION_DATE_DEBUT, dbo.DOSSIER_FORMATION.DOSSIER_FORMATION_DATE_FIN,
dbo.DOSSIER_ADHERENT.DOSSIER_ADHERENT_STATUT, dbo.TYPE_FORMATION.TYPE_FORMATION_LIBELLE,
dbo.DOSSIER_FORMATION_STD.DOSSIER_FORMATION_STD_TYPE_ACCORD_ENGAGEMENT,
dbo.DOSSIER_FORMATION.DOSSIER_FORMATION_STATUT, dbo.DOSSIER_ADHERENT.DOSSIER_ADHERENT_STATUT_ENVOI_CONTRAT,
dbo.PROFESSION.PROFESSION_CODE_APE AS CODE_NAF
FROM dbo.DOSSIER_ADHERENT INNER JOIN
dbo.DOSSIER_FORMATION_STD ON
dbo.DOSSIER_ADHERENT.DOSSIER_ADHERENT_DOSSIER_FORMATION_STD_ID_FK = dbo.DOSSIER_FORMATION_STD.DOSSIER_FORMATION_STD_ID_FK
INNER JOIN
dbo.DOSSIER_FORMATION ON
dbo.DOSSIER_FORMATION_STD.DOSSIER_FORMATION_STD_ID_FK = dbo.DOSSIER_FORMATION.DOSSIER_FORMATION_ID INNER JOIN
dbo.CODE_OBJET ON
dbo.DOSSIER_FORMATION.DOSSIER_FORMATION_CODE_OBJET_CODE_FK = dbo.CODE_OBJET.CODE_OBJET_CODE INNER JOIN
dbo.TYPE_FORMATION ON dbo.CODE_OBJET.CODE_OBJET_TYPE_FORMATION_ID_FK = dbo.TYPE_FORMATION.TYPE_FORMATION_ID INNER JOIN
dbo.ADHERENT ON dbo.DOSSIER_ADHERENT.DOSSIER_ADHERENT_ADHERENT_ID_FK = dbo.ADHERENT.ADHERENT_ID INNER JOIN
dbo.PROFESSION ON dbo.ADHERENT.ADHERENT_PROFESSION_CODE_APE_FK = dbo.PROFESSION.PROFESSION_CODE_APE
WHERE (dbo.DOSSIER_FORMATION.DOSSIER_FORMATION_TYPE IN ('+char(39)+'COLLECTIF'+char(39)+', '+char(39)+'INDIVIDUEL'+char(39)+'))';


SET @requete=@requete+' ORDER BY dbo.DOSSIER_ADHERENT.DOSSIER_ADHERENT_DATE_CREATION DESC';

exec( @requete);

HIBERNATE DAO METHODE:
public final <T extends BusinessObject> List<T> getLimitedListFromSp(@NotNull final Class<T> clazz, @NotNull final String queryName,final int pageSize,final int pageResults) {
try {
return getHibernateTemplate().executeFind(new HibernateCallback() {
public Object doInHibernate(final Session session) throws HibernateException {
Query query = session.getNamedQuery("getDossierAdherent");
query.setMaxResults(pageSize);
query.setFirstResult(pageResults * pageSize);
List l= query.list();
return l;
}
});
} catch (DataAccessException dae) {
log.warn(dae + " : " + dae.getMessage());
throw new DAOException(dae);
}
}


the error log is:
JDBCExceptionReporter.logExceptions:77 | SQL Error: 0, SQLState: 22025
JDBCExceptionReporter.logExceptions:78 | Invalid JDBC call escape at line position 6.
DossierAdherentDAOHibernate.getLimitedListFromSp:792 | org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; nested exception is org.hibernate.exception.DataException: could not execute query : could not execute query; nested exception is org.hibernate.exception.DataException: could not execute query
PropertyMessageResources.loadLocale:352 | Resource org/apache/struts/action/LocalStrings_fr_FR.properties Not Found.
PropertyMessageResources.loadLocale:352 | Resource org/apache/struts/action/LocalStrings_fr.properties Not Found.
ERROR [qtp0-3] ActionExceptionHandler.execute:74 | 127.0.0.1: com.allence.opcapl.alpha2.common.persistence.DAOException: org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; nested exception is org.hibernate.exception.DataException: could not execute query: org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; nested exception is org.hibernate.exception.DataException: could not execute query


thanks
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Problems in Hibernate executing stored procedure