Geek Answers Handbook

Calling a stored procedure in sleep mode

I just started learning sleep mode last night and it's pretty funny. It is incorrect for me to call a stored procedure as an sql query with hibernate. I added 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(); } } }

Error::

 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
+3
java mysql stored-procedures orm hibernate
source share
3 answers
 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 class is java.sql and it works !!!

+1
source share

According to the documentation, the procedure should return a result set and should be called as follows:

 { ? = call procedureName(<parameters>) }

So try the following:

 <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

+3
source share

You can get the connection as described in the accepted answer to How to get the jdbc connection from a hibernate session? and run stored functions / procedures against it.

0
source share

More articles:


All Articles