Calling a Stored Procedure in Hibernate
32,770
Solution 1
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.util.Iterator;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
public class SelectPP {
public static void main(String[] args) {
Session session = null;
//String query = "select example.id, example.data from Example example";
CallableStatement callableStatement = null;
ResultSet resultSet = null;
try{
SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
session = sessionFactory.openSession();
System.out.println("selecting records");
// Query q = session.getNamedQuery("getRecordsSP");
// Query q = session.createSQLQuery("call getRecords","",Example.class);
// Query q = session.getNamedQuery("getRecordsSP");
callableStatement = session.connection().prepareCall("{call getRecords}");
callableStatement.execute();
resultSet = callableStatement.getResultSet();
while(resultSet.next()){
System.out.println(resultSet.getInt(1));
System.out.println(resultSet.getString(2));
}
//resultSet.
System.out.print("Done");
//List l = q.list();
// for(Iterator it = l.iterator() ;it.hasNext();){
// Object row [] = (Object []) it.next();
// Example row = (Example) it.next();
// System.out.println(row.getUser_id());
// System.out.println(row.getUser_data());
// System.out.println(row.length);
// System.out.println("ID " + row[0]);
// System.out.println("Data "+ row[1]);
}
catch (Exception e) {
e.printStackTrace();
}
finally{
session.flush();
session.close();
}
}
}
I basically redesign the input without using hibernate getNamedQuery, but the java.sql class and it worked!!!
Solution 2
According to the documentation, the procedure must return a result set and should be called like this:
{ ? = call procedureName(<parameters>) }
So try the following instead:
<sql-query name="getRecordsSP" callable="true" >
<return class="Example">
<return-property name="user_id" column="user_id"></return-property>
<return-property name="user_data" column="user_data"></return-property>
</return>
{ ? = call getRecords }
</sql-query>
References
- Hibernate Core Reference Guide
Comments
-
ferronrsmith almost 2 years
I just started learning hibernate last night and its rather fun. I am some trouble calling a stored procedure as a sql-query with hibernate. I have attached the source and the error, please help me. Thanks :)
<?xml version="1.0"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "hibernate-mapping-3.0.dtd"> <hibernate-mapping> <class name="Example" table="example"> <id name="user_id" column="id" > <generator class="increment"/> </id> <property name="user_data"> <column name="data"/> </property> </class> <!-- sql-query must be after class --> <sql-query name="getRecordsSP" callable="true" > <return class="Example"> <return-property name="user_id" column="user_id"></return-property> <return-property name="user_data" column="user_data"></return-property> </return> {call getRecords} </sql-query> </hibernate-mapping>
This is the java file :::
import java.util.Iterator; import java.util.List; import org.hibernate.Query; import org.hibernate.Session; import org.hibernate.SessionFactory; import org.hibernate.cfg.Configuration; public class SelectProc { public static void main(String[] args) { Session session = null; //String query = "select example.id, example.data from Example example"; try{ SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory(); session = sessionFactory.openSession(); System.out.println("selecting records"); Query q = session.getNamedQuery("getRecordsSP"); // Query q = session.createSQLQuery("call getRecords","",Example.class); System.out.print("Done"); List l = q.list(); for(Iterator it = l.iterator() ;it.hasNext();){ Object row [] = (Object [])it.next(); System.out.println(row.length); // System.out.println(row[0]); // System.out.println("ID " + row[0]); // System.out.println("Data "+ row[1]); } } catch (Exception e) { e.printStackTrace(); } finally{ session.flush(); session.close(); } } }
The error is:::
call getRecords} 18:33:41,346 WARN JDBCExceptionReporter:38 - SQL Error: 0, SQLState: S0022 18:33:41,347 ERROR JDBCExceptionReporter:46 - Column 'id0_' not found. 18:33:41,348 WARN JDBCExceptionReporter:38 - SQL Error: 0, SQLState: S0022 18:33:41,349 ERROR JDBCExceptionReporter:46 - Column 'id0_' not found. 18:33:41,349 ERROR JDBCExceptionReporter:38 - Could not execute native SQL query java.sql.SQLException: Column 'id0_' not found. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929) at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1144) at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2813) at org.hibernate.type.IntegerType.get(IntegerType.java:18) at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:62) at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:53) at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:541) at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:233) at org.hibernate.loader.Loader.doQuery(Loader.java:337) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:167) at org.hibernate.loader.Loader.doList(Loader.java:1201) at org.hibernate.loader.Loader.list(Loader.java:1186) at org.hibernate.loader.SQLLoader.list(SQLLoader.java:121) at org.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:2212) at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:85) at SelectProc.main(SelectProc.java:28) org.hibernate.JDBCException: Could not execute native SQL query at org.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:2215) at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:85) at SelectProc.main(SelectProc.java:28) Caused by: java.sql.SQLException: Column 'id0_' not found. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929) at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1144) at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2813) at org.hibernate.type.IntegerType.get(IntegerType.java:18) at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:62) at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:53) at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:541) at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:233) at org.hibernate.loader.Loader.doQuery(Loader.java:337) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:167) at org.hibernate.loader.Loader.doList(Loader.java:1201) at org.hibernate.loader.Loader.list(Loader.java:1186) at org.hibernate.loader.SQLLoader.list(SQLLoader.java:121) at org.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:2212) ... 2 more