SpringBoot调用存储过程

程序中需要直接调用存储过程的场景

• 第一种：调用带输入输出参数的存储过程
• 第二种：调用存储过程返回结果集
• 第三种：第一种+第二种（不讨论）

数据库环境准备

• 数据库：MySQL
• 创建表及初始化数据表

CREATE TABLE `order`  (
  `uuid` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `number` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`uuid`) USING BTREE
) ；

INSERT INTO `order` VALUES ('d14f8eec-ec1a-11ea-98de-00ffc575', '20200901000001', '单据1', NULL);
INSERT INTO `order` VALUES ('d14f8eec-ec1a-11ea-98de-1234567', '20200901000003', '单据3', NULL);
INSERT INTO `order` VALUES ('d14f8eec-ec1a-11ea-98de-sdfafdd', '20200901000002', '单据2', NULL);

• 创建存储过程

CREATE PROCEDURE Get_Order_By_Uuid(
IN piUuid varchar(32),
OUT poNumber varchar(32),
OUT poName varchar(255),
OUT poErrMsg varchar(255)
) 
BEGIN
	DECLARE notfound INT DEFAULT 0;    #定义一个辅助变量用于判断
	DECLARE cur1 CURSOR FOR SELECT number,name FROM iponkan_dbcp.order where uuid = piUuid;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET notfound = 1;

	OPEN cur1;
	FETCH cur1 INTO poNumber,poName;

	if notfound = 1 then
		set poErrMsg = 'uuid='+ piUuid +'的单据信息不存在';
  end if;

	CLOSE cur1;
	
END;

CREATE PROCEDURE Get_ALL_Order()
BEGIN
	SELECT number,name FROM iponkan_dbcp.order;
END;

SpringBoot程序调用

情景一：调用带有输入输出参数的存储过程

/**
 * 调用存储过程返回参数-执行器
 * 
 * @author dongtangqiang
 */
@Component
public class ProcedureReturnParametersExecutor {

  @Autowired
  private JdbcTemplate jdbcTemplate;
  @Autowired
  private TransactionTemplate template;

  public boolean save() {
    String insertSql = "INSERT INTO `order`(`uuid`, `number`, `name`, `type`) VALUES ('d14f8eec-ec1a-11ea-98de-1234567', '20200901000003', '单据3', NULL)";
    return jdbcTemplate.update(insertSql) > 0;
  }

  public Order getByUuid(String uuid) {
    ProcedureReturnParametersTransactionCallback callback = new ProcedureReturnParametersTransactionCallback(
        uuid);
    return template.execute(callback);
  }

  class ProcedureReturnParametersTransactionCallback implements TransactionCallback<Order> {
    private String uuid;

    public ProcedureReturnParametersTransactionCallback(String uuid) {
      super();
      this.uuid = uuid;
    }

    @Override
    public Order doInTransaction(TransactionStatus transactionStatus) {
      return jdbcTemplate.execute(new CallableStatementCreator() {

        @Override
        public CallableStatement createCallableStatement(Connection con) throws SQLException {
          String procedure = "{call Get_Order_By_Uuid(?,?,?,?)}";
          CallableStatement cs = con.prepareCall(procedure);
          cs.setString(1, uuid);
          cs.registerOutParameter(2, java.sql.Types.VARCHAR);
          cs.registerOutParameter(3, java.sql.Types.VARCHAR);
          cs.registerOutParameter(4, java.sql.Types.VARCHAR);
          return cs;
        }

      }, new CallableStatementCallback<Order>() {
        @Override
        public Order doInCallableStatement(CallableStatement cs)
            throws SQLException, DataAccessException {
          cs.execute();
          String number = cs.getString(2);
          String name = cs.getString(3);
          String msg = cs.getString(4);
          if (msg != null) {
            throw new RuntimeException(msg);
          }

          Order order = new Order();
          order.setNumber(number);
          order.setName(name);

          return order;
        }
      });
    }
  }

}

情景二：调用返回结果集合的存储过程

/**
 * 调用存储过程返回结果集-执行器
 * 
 * @author dongtangqiang
 */
@Component
public class ProcedureReturnListExecutor {
  @Autowired
  private JdbcTemplate jdbcTemplate;
  @Autowired
  private TransactionTemplate template;

  public List<Order> getAll() {
    ProcedureReturnListTransactionCallback callback = new ProcedureReturnListTransactionCallback();
    return template.execute(callback);
  }

  class ProcedureReturnListTransactionCallback implements TransactionCallback<List<Order>> {

    @Override
    public List<Order> doInTransaction(TransactionStatus transactionStatus) {
      return jdbcTemplate.execute(new CallableStatementCreator() {

        @Override
        public CallableStatement createCallableStatement(Connection con) throws SQLException {
          String procedure = "{call Get_ALL_Order()}";
          CallableStatement cs = con.prepareCall(procedure);
          return cs;
        }

      }, new CallableStatementCallback<List<Order>>() {
        @Override
        public List<Order> doInCallableStatement(CallableStatement cs)
            throws SQLException, DataAccessException {
          ResultSet rs = cs.executeQuery();

          List<Order> list = new ArrayList<>();
          while (rs.next()) {
            String number = rs.getString(1);
            String name = rs.getString(2);

            Order order = new Order();
            order.setNumber(number);
            order.setName(name);
            list.add(order);
          }

          return list;
        }
      });
    }
  }
}

测试

• 结果：达到预期

/**
 * @author dongtangqiang
 */
@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest
public class TestProcedure {

  @Autowired
  private ProcedureReturnParametersExecutor procedure1;
  @Autowired
  private ProcedureReturnListExecutor procedure2;

  @Test
  public void test() {
    procedure1.save();
  }

  @Test
  public void testGetByUuid() {
    Order order = procedure1.getByUuid("d14f8eec-ec1a-11ea-98de-1234567");
    assertNotNull(order);
    assertEquals(order.getNumber(), "20200901000003");
  }

  @Test
  public void testGetAll() {
    List<Order> orderList = procedure2.getAll();
    assertEquals(3, orderList.size());
  }

}

完整代码

Github::iponkan-dbcp