用存储过程执行插入操作,结果插入操作成功,但是同时还会插入一条空数据这个怎么解决?
已经设置默认值的参数还是null怎么能把默认值取出来?
还有,执行其他操作时,会报错
org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation; uncategorized SQLException for SQL []; SQL state [null]; error code [0];
--- The error occurred in config/sql/hbpp/hbpp04/Hbpp04021000Dao.xml.
--- The error occurred while applying a parameter map.
--- Check the Hbpp04021000Dao.procParaMap.
--- Check the output parameters (retrieval of output parameters failed).
--- Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 没有为参数号 0 设置值。; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in config/sql/hbpp/hbpp04/Hbpp04021000Dao.xml.
--- The error occurred while applying a parameter map.
--- Check the Hbpp04021000Dao.procParaMap.
--- Check the output parameters (retrieval of output parameters failed).
存储过程
CREATE PROCEDURE [dbo].[hjp_bpp_lxxx_operate](
@strOperationFlg char(2),
@strLxId char(40),
@strLxType char(2),
@strLxNo nvarchar(20),
@strLxCode nvarchar(20),
@strLxName nvarchar(100),
@strLxJm nvarchar(20),
@strLxSm nvarchar(1000),
@strShowName nvarchar(100),
@numShowOrder int,
@strValidFlg char(1)=1,
@strOperateStatus char(2)=00,
@strComments nvarchar(100),
@strCreateId char(40),
@strModifyId char(40),
@datModifyTime datetime,
@strDelFlg char(1)=0,
@resultValue varchar(100) output
)
AS
BEGIN
--设置事务自动提交
SET IMPLICIT_TRANSACTIONS OFF
--开始捕获异常
BEGIN TRY
--开始事务
BEGIN TRANSACTION
--不返回计数
SET NOCOUNT ON --新建
declare @OPERATIONFLG_01 char(2)
set @OPERATIONFLG_01='01' --修改
declare @OPERATIONFLG_02 char(2)
set @OPERATIONFLG_02='02' --删除
declare @OPERATIONFLG_03 char(2)
set @OPERATIONFLG_03='03'
--执行********************************************************************************************** --新建
if (@strOperationFlg = @OPERATIONFLG_01) begin
declare @strNewId char(40)
set @strNewId = newid()
set @strLxId = 'HIT'+ substring(@strNewId, 1, 8)
+ substring(@strNewId, 10, 4)
+ substring(@strNewId, 15, 4)
+ substring(@strNewId, 20, 4)
+ substring(@strNewId, 25, 12)
insert into hjt_edb_lx_info(strLxId,strLxType,strLxNo,strLxCode,strLxName,strLxJm,strLxSm,strShowName,strComments,strCreateId,datCreateTime,strModifyId,datModifyTime)
values(@strLxId,@strLxType,@strLxNo,@strLxCode,@strLxName,@strLxJm,@strLxSm,@strShowName,@strComments,@strCreateId,getDate(),@strModifyId,getDate())
print '新建成功'
set @resultValue = '1'
end
--返回计数
SET NOCOUNT OFF
--提交事务
COMMIT
--结束捕获异常
END TRY
--抛出异常
BEGIN CATCH PRINT @@ERROR --异常返回值-99
SET @resultValue = '-99' IF @@Trancount>0 BEGIN
ROLLBACK TRANSACTION
ENDEND CATCH
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO调用
/**
* 删除方法
*
* @param vo 页面提交参数
* @return int 0:删除成功 -1:数据已被删除 -2:数据已被修改 -3:数据已被使用
*/
public int delete(Hbpp04021000Vo vo) throws RuntimeException {
Hbpp04021000Vo delVo = new Hbpp04021000Vo();
delVo.setStrLxId(vo.getStrLxId());
String returnValue = (String) hbpp04021000Dao.callProOperate(vo);
return Integer.valueOf(returnValue);
}
已经设置默认值的参数还是null怎么能把默认值取出来?
还有,执行其他操作时,会报错
org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation; uncategorized SQLException for SQL []; SQL state [null]; error code [0];
--- The error occurred in config/sql/hbpp/hbpp04/Hbpp04021000Dao.xml.
--- The error occurred while applying a parameter map.
--- Check the Hbpp04021000Dao.procParaMap.
--- Check the output parameters (retrieval of output parameters failed).
--- Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 没有为参数号 0 设置值。; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in config/sql/hbpp/hbpp04/Hbpp04021000Dao.xml.
--- The error occurred while applying a parameter map.
--- Check the Hbpp04021000Dao.procParaMap.
--- Check the output parameters (retrieval of output parameters failed).
存储过程
CREATE PROCEDURE [dbo].[hjp_bpp_lxxx_operate](
@strOperationFlg char(2),
@strLxId char(40),
@strLxType char(2),
@strLxNo nvarchar(20),
@strLxCode nvarchar(20),
@strLxName nvarchar(100),
@strLxJm nvarchar(20),
@strLxSm nvarchar(1000),
@strShowName nvarchar(100),
@numShowOrder int,
@strValidFlg char(1)=1,
@strOperateStatus char(2)=00,
@strComments nvarchar(100),
@strCreateId char(40),
@strModifyId char(40),
@datModifyTime datetime,
@strDelFlg char(1)=0,
@resultValue varchar(100) output
)
AS
BEGIN
--设置事务自动提交
SET IMPLICIT_TRANSACTIONS OFF
--开始捕获异常
BEGIN TRY
--开始事务
BEGIN TRANSACTION
--不返回计数
SET NOCOUNT ON --新建
declare @OPERATIONFLG_01 char(2)
set @OPERATIONFLG_01='01' --修改
declare @OPERATIONFLG_02 char(2)
set @OPERATIONFLG_02='02' --删除
declare @OPERATIONFLG_03 char(2)
set @OPERATIONFLG_03='03'
--执行********************************************************************************************** --新建
if (@strOperationFlg = @OPERATIONFLG_01) begin
declare @strNewId char(40)
set @strNewId = newid()
set @strLxId = 'HIT'+ substring(@strNewId, 1, 8)
+ substring(@strNewId, 10, 4)
+ substring(@strNewId, 15, 4)
+ substring(@strNewId, 20, 4)
+ substring(@strNewId, 25, 12)
insert into hjt_edb_lx_info(strLxId,strLxType,strLxNo,strLxCode,strLxName,strLxJm,strLxSm,strShowName,strComments,strCreateId,datCreateTime,strModifyId,datModifyTime)
values(@strLxId,@strLxType,@strLxNo,@strLxCode,@strLxName,@strLxJm,@strLxSm,@strShowName,@strComments,@strCreateId,getDate(),@strModifyId,getDate())
print '新建成功'
set @resultValue = '1'
end
--返回计数
SET NOCOUNT OFF
--提交事务
COMMIT
--结束捕获异常
END TRY
--抛出异常
BEGIN CATCH PRINT @@ERROR --异常返回值-99
SET @resultValue = '-99' IF @@Trancount>0 BEGIN
ROLLBACK TRANSACTION
ENDEND CATCH
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO调用
/**
* 删除方法
*
* @param vo 页面提交参数
* @return int 0:删除成功 -1:数据已被删除 -2:数据已被修改 -3:数据已被使用
*/
public int delete(Hbpp04021000Vo vo) throws RuntimeException {
Hbpp04021000Vo delVo = new Hbpp04021000Vo();
delVo.setStrLxId(vo.getStrLxId());
String returnValue = (String) hbpp04021000Dao.callProOperate(vo);
return Integer.valueOf(returnValue);
}
+ substring(@strNewId, 10, 4)
+ substring(@strNewId, 15, 4)
+ substring(@strNewId, 20, 4)
+ substring(@strNewId, 25, 12)
@strLxId 这里也只是被赋值
set @strLxId = 'HIT'+ substring(@strNewId, 1, 8)
+ substring(@strNewId, 10, 4)
+ substring(@strNewId, 15, 4)
+ substring(@strNewId, 20, 4)
+ substring(@strNewId, 25, 12)
--改為
SELECT 'HIT'+REPLACE(@strLxId,'-','')
除了主键,还有两个用getDate()取到的时间外,其他需要赋值的全部为null
太诡异了
程序调用地方传的正确不?
--新建
declare @OPERATIONFLG_01 char(2)
set @OPERATIONFLG_01='01' --修改
declare @OPERATIONFLG_02 char(2)
set @OPERATIONFLG_02='02' --删除
declare @OPERATIONFLG_03 char(2)
set @OPERATIONFLG_03='03'
/** 调用路线信息操作存储过程 */
@SuppressWarnings("unchecked")
public Object callProOperate(Hbpp04021100Vo vo) throws RuntimeException {
Map<String, Object> parMap = new HashMap<String, Object>();
parMap.put("strOperationFlg", vo.getStrOperationFlg());
parMap.put("strLxId", vo.getStrLxId());
parMap.put("strLxType", vo.getStrLxType());
parMap.put("strLxNo", vo.getStrLxNo());
parMap.put("strLxCode", vo.getStrLxCode());
parMap.put("strLxName", vo.getStrLxName());
parMap.put("strLxJm", vo.getStrLxJm());
parMap.put("strLxSm", vo.getStrLxSm());
parMap.put("strShowName", vo.getStrShowName());
parMap.put("numShowOrder", vo.getNumShowOrder());
parMap.put("strValidFlg", "1");
parMap.put("strOperateStatus", vo.getStrOperateStatus());
parMap.put("strComments", vo.getStrComments());
parMap.put("strCreateId", vo.getStrCreateId());
parMap.put("datModifyTime", vo.getDatModifyTime());
parMap.put("strModifyId", vo.getStrModifyId());
parMap.put("strDelFlg", "0");
query("Hbpp04021100Dao.call_hjp_bpp_lxxx_operate", parMap);
return parMap.get("resultValue");
}
<parameterMap id="procParaMap" class="HashMap">
<parameter property="strOperationFlg" jdbcType="VARCHAR" mode="IN"/>
<parameter property="strLxId" jdbcType="VARCHAR" mode="IN"/>
<parameter property="strLxType" jdbcType="VARCHAR" mode="IN"/>
<parameter property="strLxNo" jdbcType="VARCHAR" mode="IN"/>
<parameter property="strLxCode" jdbcType="VARCHAR" mode="IN"/>
<parameter property="strLxName" jdbcType="VARCHAR" mode="IN"/>
<parameter property="strLxJm" jdbcType="VARCHAR" mode="IN"/>
<parameter property="strLxSm" jdbcType="VARCHAR" mode="IN"/>
<parameter property="strShowName" jdbcType="VARCHAR" mode="IN"/>
<parameter property="numShowOrder" jdbcType="VARCHAR" mode="IN"/>
<parameter property="strValidFlg" jdbcType="VARCHAR" mode="IN"/>
<parameter property="strOperateStatus" jdbcType="VARCHAR" mode="IN"/>
<parameter property="strComments" jdbcType="VARCHAR" mode="IN"/>
<parameter property="strCreateId" jdbcType="VARCHAR" mode="IN"/>
<parameter property="strModifyId" jdbcType="VARCHAR" mode="IN"/>
<parameter property="datModifyTime" jdbcType="VARCHAR" mode="IN"/>
<parameter property="strDelFlg" jdbcType="VARCHAR" mode="IN"/>
<parameter property="resultValue" jdbcType="VARCHAR" mode="OUT"/>
</parameterMap> <procedure id="call_hjp_bpp_lxxx_operate" parameterMap="procParaMap" resultClass="HitListDataVo">
{call hjp_bpp_lxxx_operate(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) }
</procedure>