Java 在 Hibernate 中调用存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3681045/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-08-14 03:27:27 来源:igfitidea点击:
Calling a Stored Procedure in Hibernate
提问by ferronrsmith
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 :)
我昨晚刚开始学习休眠，它很有趣。我在使用 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>
This is the java file :::
这是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();
}
}
}
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
采纳答案by ferronrsmith
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!!!
我基本上重新设计了输入而不使用 hibernate getNamedQuery，但是 java.sql 类并且它起作用了！！！
回答by Pascal Thivent
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
- Hibernate 核心参考指南
