SQL Server学习：Java调用SQL Server存储过程（Spring方式）

Java代码 —— 测试类代码：

public class SpringInvokeSP {public static void main(String[] args) {String driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver";String url="jdbc:sqlserver://192.168.0.3:1433;DatabaseName=CTU_WMS";String username="CTU_WMS_user";String password="ctuwms";// 创建datasourceDriverManagerDataSource ds = new DriverManagerDataSource();ds.setDriverClassName(driverClassName);ds.setUrl(url);ds.setUsername(username);ds.setPassword(password);resultSP(ds);}/** * 返回多个结果集的存储过程 * @param ds */public static void resultSP(DriverManagerDataSource ds){SQLStoredProcedure sp = new SQLStoredProcedure(ds,"my_sp_test");sp.addParameter("i", 11);sp.addOutStringParameter("outstr");sp.addInOutParameter("returnCode", "Hello vicky");sp.addOutDateParameter("date");sp.addOutDateParameter("datetime");sp.addResultSetParameter("result-set-1", CdBillNoRuleModel.class);sp.addResultSetParameter("result-set-2", EsUserModel.class);Map<String,Object> result = sp.execute();String resultStr = (String)result.get("outstr");String returnCode = (String)result.get("returnCode");System.out.println(resultStr);System.out.println(returnCode);System.out.println((Date)result.get("date"));System.out.println((Date)result.get("datetime"));List<CdBillNoRuleModel> results = (List<CdBillNoRuleModel>)                                                  result.get("result-set-1");if(null != results){try {for(CdBillNoRuleModel model : results){System.out.println(model.getCdbrDesc());}} catch (Exception e) {e.printStackTrace();}}List<EsUserModel> users = (List<EsUserModel>)result.get("result-set-2");//if(users != null){for(EsUserModel user:users){System.out.println(user.getEsusLoginName());}}}}

Java代码 —— 工具类代码：

public class SQLStoredProcedure extends StoredProcedure {private Map<String, Object> parameters = new HashMap<String, Object>();public SQLStoredProcedure(DataSource ds,String spName){super(ds,spName);}public void addParameter(String name, String value) {super.declareParameter(new SqlParameter(name, Types.VARCHAR));parameters.put(name, value);}public void addParameter(String name, int value) {super.declareParameter(new SqlParameter(name, Types.INTEGER));parameters.put(name, value);}public void addParameter(String name, double value) {super.declareParameter(new SqlParameter(name, Types.DOUBLE));parameters.put(name, value);}public void addParameter(String name, Date value) {super.declareParameter(new SqlParameter(name, Types.TIMESTAMP));parameters.put(name, value);}public void addInOutParameter(String name, String value) {super.declareParameter(new SqlInOutParameter(name, Types.VARCHAR));parameters.put(name, value);}public void addInOutParameter(String name, int value) {super.declareParameter(new SqlInOutParameter(name, Types.INTEGER));parameters.put(name, value);}public void addInOutParameter(String name, double value) {super.declareParameter(new SqlInOutParameter(name, Types.DOUBLE));parameters.put(name, value);}public void addInOutParameter(String name, Date value) {super.declareParameter(new SqlInOutParameter(name, Types.TIMESTAMP));parameters.put(name, value);}public void addOutStringParameter(String name) {super.declareParameter(new SqlOutParameter(name, Types.VARCHAR));}public void addOutIntParameter(String name) {super.declareParameter(new SqlOutParameter(name, Types.INTEGER));}public void addOutDoubleParameter(String name) {super.declareParameter(new SqlOutParameter(name, Types.DOUBLE));}public void addOutDateParameter(String name) {super.declareParameter(new SqlOutParameter(name, Types.TIMESTAMP));}public void addResultSetParameter(String name,Class<?> entityTypeClass){super.declareParameter(new SqlReturnResultSet(name,ParameterizedBeanPropertyRowMapper.newInstance(entityTypeClass)));}public Map<String, Object> execute() {return super.execute(parameters);}@Overridepublic Map<String, Object> execute(Map inParams) throws DataAccessException {Map allParams = new HashMap();allParams.putAll(parameters);allParams.putAll(inParams);return super.execute(allParams);}@Overridepublic Map<String, Object> execute(final ParameterMapper inParamMapper)throws DataAccessException {return super.execute(new ParameterMapper() {public Map createMap(Connection con) throws SQLException {Map allParams = new HashMap();allParams.putAll(parameters);allParams.putAll(inParamMapper.createMap(con));return allParams;}});}}

SQL存储过程代码：

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE name='my_sp_test' AND TYPE='P') BEGINDROP PROCEDURE my_sp_test;END;GOcreate procedure my_sp_test @i int, @outstr varchar(100) out,@returnCode varchar(100) out asbegin try    begin trandeclare @j int;set @returnCode = @returnCode +';OUT PUT 参数测试';if @i<10 begin  set @outstr = 'system exception.';  set @j = 10/0;endelse begin  begin try    if @i>=20 beginset @outstr = 'customer exception 2222222222222222';set @j = 10/0;    end    else beginset @j = @i;    set @outstr = 'customer exception 11111111111111111';    select * from CD_BILL_NO_RULE where CREATOR = '150' AND REC_VER =0;    select * from ES_USER where ESUS_ESCO_ID = 100;RAISERROR (66666, -- Message id.   16, -- Severity,   1 -- State,   ) ;end;  end try  begin catch    raiserror(66666,16,1);    set @outstr = 'customer exception 333333333333333333';  end catch;  end;commit tran;end trybegin catchif @@ERROR=66666 begin    set @outstr = @outstr  + '---------------- customer exception';end;rollback tran;return;end catch;go

使用Spring调用SQLServer存储过程与Oracle存储过程的差别:

1、调用一个返回结果集的存储过程

  SQL Server：

        super.declareParameter(new SqlReturnResultSet(name,ParameterizedBeanPropertyRowMapper.newInstance(entityTypeClass)));  

  Oracle：

       super.declareParameter(new SqlOutParameter(name,OracleTypes.CURSOR,ParameterizedBeanPropertyRowMapper.newInstance(entityTypeClass));

  注：(1). SQL Server定义返回结果集的存储过程，只需在存储过程中执行一个查询语句即可。但是这个查询语句必   须在transaction中。

         (2). SQL Server存储过程返回的结果集因为没有定义具体的变量名，所以我们在获取结果集的时候，直接写 “default”即可。

      sp.addResultSetParameter("default", CdBillNoRuleModel.class);

         Map<String,Object> result = sp.execute();

    List<CdBillNoRuleModel> results = (List<CdBillNoRuleModel>)result.get("default");  

        (3). 如果存储过程中有返回多个结果集，则不能直接用“default”来获取结果集，而是用“result-set-*”。

 sp.addResultSetParameter("result-set-1", CdBillNoRuleModel.class);

 sp.addResultSetParameter("result-set-2", EsUserModel.class);

 Map<String,Object> result = sp.execute();

 List<CdBillNoRuleModel> results = (List<CdBillNoRuleModel>)result.get("result-set-1");

 List<EsUserModel> users = (List<EsUserModel>)result.get("result-set-2");

相关资源下载地址： http://download.csdn.net/detail/chen_yu_ting/4233615