java - 在 hibernate 中调用存储过程错误

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 语句”，但没有显示原因。 hibernate 映射:

<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:由于这是一个类(class)项目，因此需要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:无效的列名

这是用于执行查询和设置绑定(bind)参数值的 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();
              }
          });
      }