java 连接SQLServer 调用存储过程

问题

	项目中遇到一个需要，需要调用MES系统的一个存储过程。

环境

	idea+SpringBoot+Gradle

代码

package com.atue.smarthome.bussiness.application;
import com.alibaba.fastjson.JSONObject;
import com.atue.smarthome.bussiness.interfaces.dto.SMTRequest;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import org.springframework.stereotype.Service;

@Service
public class SMTService {

  final static String URL = "jdbc:sqlserver://XX;DatabaseName=XX";
  final static String SQLSERVERDRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
  final static String USERNAME = "XX";
  final static String PASSWORD = "XX";

  private static Connection connection = null;

  public static Connection queryMesProcedure() {
    try {
      Class.forName(SQLSERVERDRIVER);
      connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return connection;
  }
  }

测试

package com.atue.smarthome.bussiness.util;/*
 *@ClassName test
 *@Description TODO
 *@Author 
 *@Date 2020/9/16 10:39
 *@Version 1.0
 */

import com.atue.smarthome.bussiness.application.SMTService;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;

public class test {

  public static void main(String[] args) {

    Connection connection = SMTService.queryMesProcedure();
    String sql = "{call  uspUnitComplete(?,?,?)}";
    try{
      //ResultSet rs = null;
      // 设置调用的存储过程名及参数情况,注意的是存储过程不可以有输出参数
      CallableStatement proc = connection.prepareCall(sql);
      // 设置输入参数
      proc.setLong(1, 111027880000206L);
      proc.setInt(2, 100161);
      proc.setInt(3, 1);

      // 调入存储过程
      proc.execute();
      // 取出存储过程的结果集
      //rs = proc.getResultSet();
      connection.close();
    }catch (SQLException e){
      System.out.println("Msg:" + e.getMessage());
      e.printStackTrace();
    }


  }

}

gradle配置

implementation("com.microsoft.sqlserver:mssql-jdbc")