在springboot中使用mybatis调用sql server存储过程

记录一下在调用存储过程时遇到的问题与解决方法

1.微软官网上关于sql server存储过程的执行方法：

• 执行系统存储过程

  EXEC sys.sp_who;

• 执行用户定义的存储过程

  EXEC dbo.uspGetEmployeeManagers @BusinessEntityID = 50; 
  OR
  EXEC AdventureWorks2012.dbo.uspGetEmployeeManagers 50;

2.业务存储过程的原生调用

根据官网调用方法，结合实际存储过程的输入输出参数，编写原生调用写法：

EXEC dbo.xxx_test @SDate ='2019-6-17 00:00:00', @ShiftID ='2'

3.程序直接调用

@Select("exec dbo.shift_oilrecord @SDate =#{sDate},@ShiftID =#{shiftId}")
Map<String, Object> execProc(String sDate, int shiftId);

这是注解的方式，直接通过@select注解执行，当然存储过程有专门的调用方式，但资源较少，调试起来很多报错无法在短时间内解决。

4.出现问题

由于存储过程中有对某表的查询语句，所以在存储过程结束后会有两个结果集，分别是查询结果和最后的return。但无论是在数据库查询工具上还是在程序上调用此存储过程，都会出现的问题是return值被查询结果集覆盖，导致无法获取到return值。

5.查询解决方式

原生sql上是通过声明一个变量来存储返回的值。于是把sql修改为：

declare @return_value int exec @return_value = dbo.xxx_test @SDate ='2019-6-17 00:00:00', @ShiftID ='2' select @return_value

执行以上sql后，在结果窗口就出现两个窗口来接收两个不用的返回值。

6.尝试在程序上调用修改后的sql

控制台报错！

druid连接池报错：sql injection violation, multi-statement not allow

druid的检查把“多条sql同时执行”限制了。因为修改后的sql里包含了定义变量、执行存储过程、结果查询3条语句。首先想法是把sql拆分，但是定义的变量只存在于同一条语句中，所以差分不可取。(可能有其他特殊的方法没有深究到)。

7.druid关闭多条sql同时执行的检查

于是思路去到把druid关于多条sql同时(在一条语句上)执行的检查关闭。

大部分是在配置文件中把multi-statement-allow修改为true，允许这种情况。但是直接设置没有任何作用。

继续深入发现可能是因为druid启动就生成某个关于此属性的对象，于是后面再改就无效，解决方法是重写配置类，光改有关的两个方法后启动报错，继续查询报错的原因是启动创建时没有获取到url等信息。到这里，为了解决这个麻烦，需要在配置类上配置数据源等操作，但由于数据源有多个，修改起来及其复杂，还要考虑多数据源切换等问题。

于是寻找其他解决方式。

因为持久层用的是mybatis-plus，遂在mybatis-plus官网上查看集成druid的相关文档。

官方支持直接在配置文件中修改此项配置。

修改druid.wall中的multi-statement-allow和none-base-statement-allow为true即可关闭此项检查。

spring:
  profiles: test
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
    dynamic:
      primary: mySHOPSHStock
      datasource:
        db1:
          url: xxx
          username: xxx
          password: xxx
        db2:
          url: xxx
          username: xxx
          password: xxx
      druid:
        wall:
          multi-statement-allow: true
          none-base-statement-allow: true

至此，完成druid关闭多条sql同时执行检查。

8.处理程序上的结果集接收

解决了druid的问题，接着回到接口类。

上面提到，按照此方式调用该存储过程会返回两个结果集。然后，接口类的方法以Map<String, Object>定义类型，只接收到了查询的结果集，return的值也许返回了但接收不到。

然而用数据库查询工具调用确实是有返回。于是把return结果存放到一个临时表中，程序通过额外查询临时表获取return值。

sql修改为：

declare @return_value int exec @return_value = dbo.xxx_test @SDate ='2019-6-17 00:00:00', @ShiftID ='2' insert into temp (return_value) values (@return_value)

完整过程：

create table temp(
   return_value int
)
declare @return_value int exec @return_value = dbo.xxx_test @SDate ='2019-6-17 00:00:00', @ShiftID ='2' insert into temp (return_value) values (@return_value)
select * from temp
drop table temp

这样，在程序上就可以分步骤完成本次存储过程的调用及返回值的获取。