在Hibernate中调用存储过程

Z时代
2024-01-10
分类：问答

昨晚我刚刚开始学习hibernate，它相当有趣。我在使用hibernate将存储过程作为sql查询调用时遇到麻烦。我已附上来源和错误，请帮助我。谢谢：）

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

这是Java文件:::

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

错误是:::

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

回答：

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