使用Hibernate调用SQL Server存储过程

Question

我正在尝试使用Hibernate中的createSQLQuery方法调用SQL Server存储过程，但遇到了异常。由于我是Hibernate的新手，请帮忙解决。

异常信息：

   org.hibernate.exception.SQLGrammarException: could not execute query

SQL Server 存储过程-

  CREATE PROCEDURE [dbo].[sqlServerProc]
  (@SOURCE_AID NUMERIC(18,0),
  @DESTI_BID NUMERIC(18,0),
  @E_CID NUMERIC(18,0),
  @MOVE_DID BIT,
  @RST_ADDED VARCHAR(MAX) = '' OUTPUT,
  @LST_ADDED VARCHAR(MAX) = ''  OUTPUT,
  @PST_ADDED VARCHAR(MAX) = '' OUTPUT,
  @IS_VALUE BIT = 0 OUTPUT
 )     

Hibernate 代码：

 public String Function(String AId,String BId,String CId,String DID ) throws Exception
{ 

try{
            session = getSessionFactory().openSession();
            session.beginTransaction();
            Query query = session.createSQLQuery(
            "CALL sqlServerProc(:AId,:BId,:CId,:DID)").setParameter("AId",  AId).
            setParameter("BId", BId).
            setParameter("CId", CId).
            setParameter("DID", DID).
            ;

            ListOfEnvData=  query.list();

            for (Object[] aRow :ListOfEnvData) {

                String a = (String) aRow[0];
                String b = (String) aRow[1];
                String c = (String) aRow[2];
                String d = (String) aRow[3];
                System.out.println("a"+a+"b"+b+"c"+c+"d"+d);
            //  value=d;
                break;
             }


          }catch(Exception e){
              session.close();
              e.printStackTrace();

          }}

- user3449214

Answer 1

尝试：

Query query = em.createNativeQuery("EXECUTE sp_log :objectName, :log, :additionalInfo");
            query.setParameter("objectName", name);
            query.setParameter("log", log);
            query.setParameter("additionalInfo", info);
            query.executeUpdate();

但是，你可以不使用Hibernate来实现这个功能，例如：

public void callMyProc(SomeEntity someEntity) {
        Connection connection = null;

        try {
            connection = getConnection();
            CallableStatement cstmt = connection.prepareCall("{call dbo.my_procedure(?)}");
            cstmt.setLong(1, someEntity.getId());
            cstmt.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(connection);
        }
    }

- Dherik