java - 无法从 hibernate 调用存储过程

Question

请尝试回答以下代码中从 hibernate 调用 ORACLE 中的存储过程的问题。我正在 hbm 文件中使用命名查询，从 testclient.java 调用。

正在使用Hibernate3.2.1核心文件。我的存储过程:

create or replace  procedure get_empdetails2(mycursor out sys_refcursor, cond in varchar2)

as 
begin
open mycursor for

select eb.first_name,eb.last_name from employee3 eb where eb.email like cond  ;

end;

员工.java:

public class Employee{

public Employee()
{
}
    private int eid;
    private String fname,lname,email;
    public int getEid() {
        return eid;
    }
    public void setEid(int eid) {
        this.eid = eid;
    }
    public String getFname() {
        return fname;
    }
    public void setFname(String fname) {
        this.fname = fname;
    }
    public String getLname() {
        return lname;
    }
    public void setLname(String lname) {
        this.lname = lname;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }

}

表员工3:

Column Name Data Type      Nullable   Default   Primary Key
EID         NUMBER          Yes            -     -
FIRST_NAME  VARCHAR2(20)    Yes            -     -
LAST_NAME   VARCHAR2(20)    Yes            -     -
EMAIL       VARCHAR2(20)    Yes            -     -

hibernate.cfg:

<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
          "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
          "http://hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<!-- Generated by MyEclipse Hibernate Tools.                   -->
<hibernate-configuration>

    <session-factory>
        <property name="hbm2ddl.auto">update</property>
        <property name="dialect">org.hibernate.dialect.Oracle9Dialect</property>
        <property name='connection.url'>jdbc:oracle:thin:@localhost:1521/xe</property>
        <property name='connection.username'>system</property>
        <property name='connection.password'>oracle123</property>
        <property name='connection.driver_class'>oracle.jdbc.driver.OracleDriver</property>
        <property name="show_sql">true</property>
    <mapping resource="employee3.hbm.xml"/>
    </session-factory>

</hibernate-configuration>

测试客户端.java

import java.util.*; 
import org.hibernate.*;
import org.hibernate.cfg.Configuration;
import org.hibernate.Criteria;
public class TestClient
{
   private static SessionFactory factory; 
   public static void main(String[] args)
 {

          System.out.println("--------------done-----------");
            Configuration cfg= new Configuration();
            System.out.println("--------------got cfg object-----------");
            cfg = cfg.configure("hibernate.cfg.xml");
            System.out.println("--------------hbm loaded into cfg-----------");
            SessionFactory sf= cfg.buildSessionFactory();

            System.out.println("--------------done-----------");


System.out.println("--------------getting session-----------");
Session ses =sf.openSession();

Transaction tx = ses.beginTransaction();
System.out.println("--------------got tx object-----------");
Employee eb =null;

SQLQuery q1=(SQLQuery)ses.getNamedQuery("test1");

//q1.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);


System.out.println("--------------q1 processing-----------");
//q1.setInteger(0,0);
q1.setString(0,"v%");

//q1.setParameter(0, "%a%");
System.out.println("--------------q1 processing-----------");
List res=q1.list();
System.out.println(res);

tx.commit();
ses.close();

System.out.println("--------------closing connection-----------");
sf.close();
System.out.println("--------------connection closed-----------");



}}

employee3.hbm:

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC 
 "-//Hibernate/Hibernate Mapping DTD//EN"
 "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd"> 


          <hibernate-mapping>
          <class name="Employee" table="employee3">
            <id name="eid" column="eid"/>
      <property name="fname" column="first_name"/>
      <property name="lname" column="last_name"/>
      <property name="email" column="email"/>
   </class>


 <sql-query name="test1" callable="true">
 <return  alias ="test1" class ="Employee">
    <return-property name="fname" column="FIRST_NAME"/>
    <return-property name="lname" column="LAST_NAME"/>
</return> 

  {call get_empdetails2(?,?)}

</sql-query>

</hibernate-mapping>

在调用 get_empdetails(?,?) 中，第一个 ?用于输出 mycursor，根据 hibernate doc必须是第一个参数和第二个？是过程中 cond 变量的输入参数。我的输出错误是:

 Oct 10, 2014 2:38:54 PM org.hibernate.tool.hbm2ddl.TableMetadata <init>
 INFO: indexes: [] Oct 10, 2014 2:38:54 PM

  org.hibernate.tool.hbm2ddl.SchemaUpdateexecuteINFO:schema update complete  
   --------------done-----------  
   --------------getting session-----------  
   --------------got tx object----------- 
   --------------q1 processing----------- 
   --------------q1 processing----------- 
   Hibernate: {call get_empdetails2(?,?)}
   Oct 10, 2014 2:38:54 PM org.hibernate.type.NullableType nullSafeGet
   INFO: **could not read column value from result set: eid0_0_; Invalid column name**  
   Oct 10, 2014 2:38:54 PM org.hibernate.util.JDBCExceptionReporter logExceptions  
   WARNING: SQL Error: 17006, SQLState: null  
   Oct 10, 2014 2:38:54 PM org.hibernate.util.JDBCExceptionReporter logExceptions 
   SEVERE: Invalid column name  Exception in thread "main" 

  org.hibernate.exception.GenericJDBCException: could not execute query at
org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
  at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
  at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
  at org.hibernate.loader.Loader.doList(Loader.java:2214)
  at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2095)
  at org.hibernate.loader.Loader.list(Loader.java:2090)
  at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
  at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
  at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
  at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:150)
  at TestClient.main(TestClient.java:44)    
  Caused by: java.sql.SQLException: Invalid column name 
  at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)  

如输出中所示，我收到此信息 - 无法读取列。配置有什么问题吗？

谢谢

Answer 1

Hibernate 正在尝试从存储过程的返回结果映射整个表。您将从存储过程返回 eb.first_name,eb.last_name。

可能的解决方案1:

选择存储过程SELECT查询中的所有字段，例如select * from employee3 eb where eb.email like cond;

如果解决方案 1 适合您，则使用它，但如果您只想从存储过程返回两个字段，我建议您使用第二个解决方案。因为我不知道如何执行仅返回选定字段的命名查询。

可能的解决方案 2:

使用SQLQuery。我将其与 MySQL 一起使用，您可以尝试使用 Oracle。希望它能发挥作用。

Query callStoredProcedure_MYSQL = session.createSQLQuery("CALL SP_MYSQL_HIBERNATE (:param1, :param2, :param3)");
callStoredProcedure_MYSQL.setInteger("param1", 10);
callStoredProcedure_MYSQL.setInteger("param2", 10);
callStoredProcedure_MYSQL.setString("param3", "test");*/

/* callStoredProcedure_MYSQL.list() will execute stored procedure and return the value */
List customResult = callStoredProcedure_MYSQL.list();
if (customResult != null && !customResult.isEmpty()) {
 Object[] obj = customResult.get(0);
 System.out.println(obj[0]);
 System.out.println(obj[1]);            
}