hibernate错误中的java调用存储过程

Question

存储过程（仅限规范）：

CREATE OR REPLACE PACKAGE "PACK_USER"     
as   
type contact is table of nvarchar2(50) INDEX BY BINARY_INTEGER;  
procedure create_user(  
user_id out number,  
pwd in nvarchar2,  
birthday in date,  
gender in number,  
address in nvarchar2,  
realName in nvarchar2,  
identity in nvarchar2,  
salary in float,  
contractTime in date,  
departmentId in number,  
positionId in number 
);  
end pack_user;
/

当我从癞蛤蟆那里叫它时，它是好的。但当我从hibernate调用它时，它说的是“无效的SQL语句”，但没有显示原因。 休眠映射：

<sql-query name="create_user" callable="true">
    <return-scalar column="user_id" type="integer"/>
    { call PACK_USER.CREATE_USER(?,:pwd,:birthday,:gender,:address,:realName,:identity,:salary,:contractTime,:departmentId,:positionId)}
</sql-query>

呼叫人：

Session session = this.getSession();
Query q = session.getNamedQuery("create_user");
q.setString("pwd", userInfo.getIndentity());
q.setDate("birthday", userInfo.getBirthday());
q.setInteger("gender", userInfo.isGender() ? 1 : 0);
q.setString("address", userInfo.getHomeAddress());
q.setString("realName", userInfo.getRealname());
q.setString("identity", userInfo.getIndentity());
q.setFloat("salary", userInfo.getBaseSalary());
q.setDate("contractTime", userInfo.getContractTime());
q.setInteger("departmentId", userInfo.getDepartmentId());
q.setInteger("positionId", userInfo.getPositionId());
int res = (Integer)q.uniqueResult();
return res;

PS：由于这是一个课程项目，oracle和存储过程是必需的，而hibernate不是
我已经尝试将该过程移出包，或者创建一个函数来代替该过程。两者都不管用
oracle版本10g

Answer 1

您的配置文件中可能存在一些问题，请查看下面的示例以了解更多说明：

CREATE OR REPLACE PROCEDURE SP_LIB_DTL(p_cursor    out sys_refcursor,
                                       in_brnch_cd in number,
                                       in_auth_cd in number)
as
  bookName varchar2(8);
  ISBN     number;
begin
  bookName := null;
  ISBN     := 0;
  open p_cursor for
    select l.book_name, l.isbn_nbr
      into bookName, ISBN
      from LIB_BRNCH_DTL l
     where l.branch_code = in_brnch_cd
     and l.auth_code = in_auth_cd;

end;

Hibernate xml

<?xml version=”1.0″ encoding=”utf-8″?>
<!DOCTYPE hibernate-mapping PUBLIC “-//Hibernate/Hibernate Mapping DTD 3.0//EN”
“http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd“>
<hibernate-mapping>
<class name=”com.org.lib.LibraryDetails”>
<id name=”ISBN” type=”long” />
<property name=”bookName” type=”string” />
</class>
 <sql-query name=”LIB_SP” callable=”true”>
 <return class=”com.org.lib.LibraryDetails”>
   <return-property name=”ISBN” column=”isbn_nbr” />
   <return-property name=”bookName” column=”book_name” />
 </return>
  {  call SP_LIB_DTL(? , :branchCD ,:authorCD) }
 </sql-query>
</hibernate-mapping>

确保为返回属性映射的列属性使用了正确的数据库字段名值。如果不映射正确的数据库字段名，将出现以下错误

无法执行查询；糟糕的SQL语法[{call SP_LIB_DTL（？，）}]； 嵌套的例外是java。sql。SQLException:列名无效

下面是用于执行查询和设置绑定参数值的DAO实现

public  List selectBooks(final BigDecimal branchCode,final BigDecimal authorCode){
        return (List) getHibernateTemplate().execute(new HibernateCallback() {
              public Object doInHibernate(Session session) throws HibernateException, SQLException
              {
                  Query q = session.getNamedQuery(“LIB_SP”);
                  q.setLong(“branchCD”, branchCode.longValue());
                  q.setLong(“authorCD”, authorCode.longValue());
                  return q.list();
              }
          });
      }