ibatis 执行sqlserver存储过程

sqlserver 存储过程:
create procedure insert_normal_user
@userId int output,
@loginName nvarchar(80),
@password nvarchar(80),
@company nvarchar(80),
@roleId int,
@isEnable tinyint,
@hasCoInfo tinyint,
@financeToolType tinyint
as 
begin
--不生成影响行数
SET NOCOUNT ON;
insert into CN_NORMAL_USER 
(LOGIN_NAME,PASSWORD,COMPANY,ROLE_ID,IS_ENABLE,
 CREATE_TIME,UPDATE_TIME,HAS_CO_INFO,FINANCE_TOOL_TYPE)
values
(@loginName,@password,@company,@roleId,@isEnable,
 getDate(),getDate(),@hasCoInfo,@financeToolType);
select @userId=SCOPE_IDENTITY() ;
end

JDBC执行存储过程方法一:

Connection conn=getConnection();
		
Connection conn=getConnection();
		
CallableStatement  call=conn.prepareCall("{call insert_normal_user(?,?,?,?,?,?,?,?)}");
		
call.registerOutParameter(1,java.sql.Types.INTEGER);
		
call.setObject(2, "这是测试插入----");
call.setObject(3, "123");
call.setObject(4, "12");
call.setObject(5, 1);
call.setObject(6, 4);
call.setObject(7, 0);
call.setObject(8, 1);
call.executeUpdate();

System.out.println(call.getInt(1));
		
conn.close();

没有问题，如果将call.registerOutParameter(8,java.sql.Types.INTEGER);

改为call.registerOutParameter(1,-99999);会报错

Exception in thread "main" java.lang.NullPointerException
	at com.microsoft.sqlserver.jdbc.AppDTVImpl$SetValueOp.executeDefault(Unknown Source)
	at com.microsoft.sqlserver.jdbc.DTV.executeOp(Unknown Source)
	at com.microsoft.sqlserver.jdbc.AppDTVImpl.setValue(Unknown Source)
	at com.microsoft.sqlserver.jdbc.DTV.setValue(Unknown Source)
	at com.microsoft.sqlserver.jdbc.Parameter.getTypeDefinition(Unknown Source)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.buildParamTypeDefinitions(Unknown Source)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.buildPreparedStrings(Unknown Source)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doPrepExec(Unknown Source)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source)
	at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(Unknown Source)
	at com.caineng.util.ibatis.JDBC.main(JDBC.java:115)

JDBC执行存储过程方法二:

Connection conn=getConnection();
		
CallableStatement  call=conn.prepareCall("{call insert_normal_user(?,?,?,?,?,?,?,?)}");
		
call.registerOutParameter(1,-99999);
		
call.setObject(1, 1);// 新添加的语句
call.setObject(2, "这是测试插入----");
call.setObject(3, "123");
call.setObject(4, "12");
call.setObject(5, 1);
call.setObject(6, 4);
call.setObject(7, 0);
call.setObject(8, 1);

call.executeUpdate();
		
System.out.println(call.getInt(1));

如果设置call.registerOutParameter(1,-99999);再添加call.setObject(1,1);则可以执行正确。

Ibatis执行存储过程一:

配置文件:

<parameterMap class="NormalUser" id="normalUserParameterMap">
       <parameter property="userId"   javaType="Integer"  jdbcType="INTEGER" mode="OUT" />
       <parameter property="loginName"  mode="IN"/>
       <parameter property="password"   mode="IN"/>
       <parameter property="company"    mode="IN"/>
       <parameter property="roleId"     mode="IN" />
       <parameter property="isEnable"   mode="IN" />
       <parameter property="hasCoInfo"  mode="IN" />
       <parameter property="financeToolType" mode="IN"/>
    </parameterMap>
    
    <procedure id="insertNormalUserByProcedure" parameterMap="normalUserParameterMap">
    	{call insert_normal_user(?,?,?,?,?,?,?,?)}
    </procedure>

可以通过，如果将<parameterproperty="userId"javaType="Integer"jdbcType="INTEGER"mode="OUT"/>

改为<parameterproperty="userId"javaType="Integer"jdbcType="int"mode="OUT"/>则报错：

org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation; uncategorized SQLException for SQL []; SQL state [null]; error code [0];   
--- The error occurred in sqlmaps/mssql/account/NormalUser.ibatis.xml.  
--- The error occurred while applying a parameter map.  
--- Check the CN_NORMAL_USER.normalUserParameterMap.  
--- Check the statement (update procedure failed).  
--- Cause: java.lang.NullPointerException; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:   
--- The error occurred in sqlmaps/mssql/account/NormalUser.ibatis.xml.  
--- The error occurred while applying a parameter map.  
--- Check the CN_NORMAL_USER.normalUserParameterMap.  
--- Check the statement (update procedure failed).  
--- Cause: java.lang.NullPointerException

---Cause:java.lang.NullPointerException;nestedexceptioniscom.ibatis.common.jdbc.exception.NestedSQLException:

空指针和上面jdbcjava代码中设置call.registerOutParameter(1,-99999);之后报的错一样。

Ibatis执行存储过程二:

<parameterMap class="NormalUser" id="normalUserParameterMap">
       <parameter property="userId"   javaType="Integer"  jdbcType="int" mode="INOUT" />
       <parameter property="loginName"  mode="IN"/>
       <parameter property="password"   mode="IN"/>
       <parameter property="company"    mode="IN"/>
       <parameter property="roleId"     mode="IN" />
       <parameter property="isEnable"   mode="IN" />
       <parameter property="hasCoInfo"  mode="IN" />
       <parameter property="financeToolType" mode="IN"/>
    </parameterMap>
    
    <procedure id="insertNormalUserByProcedure" parameterMap="normalUserParameterMap">
    	{call insert_normal_user(?,?,?,?,?,?,?,?)}
    </procedure>

现在<parameterproperty="userId"javaType="Integer"jdbcType="int"mode="INOUT"/>

将jdbcType="int"mode="INOUT"可以通过，但是前提是传进来的对象NormalUser的userId属性不能为空。

联想到上面jdbcjava代码中设置call.registerOutParameter(1,-99999);之后有加入代码call.setObject(1,1);

附上IbatisparameterMap的子元素parameter的属性jdbcType的解释

本人小菜，文笔欠佳，请见谅!