JAVA调用sqlserver存储过程的实现（返回多个结果集的实现）

Sqlserver使用的为2008R2

1、存储过程

USE [YZC_BI]
GO
/****** Object:  StoredProcedure [dbo].[CalYlfwColumnData]    Script Date: 08/20/2014 09:24:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[CalYlfwColumnData] @xmbm  varchar(max),@tjsp varchar(max),@startdate varchar(10),@enddate varchar(10)
	-- Add the parameters for the stored procedure here
	--<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
	--<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	select * from table
    
END

slqserver存储过程里面返回的结果集只需要在最后select，然后在java中获取即可，Oracle则需要另外处理。

2、java程序实现，首先导入对应的驱动包

①返回结果集为一个的情况

package me.hua.main.database;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class CallProcedure {

	String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
	String url = "jdbc:sqlserver://127.0.0.1:1433;DatabaseName=dbname";
	Connection conn = null;
	CallableStatement cs = null;// PreparedStatement,Statement
	ResultSet rs;

	public void getConn() {
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, "sa", "sasa");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void callProc() {
		try {
			cs = conn.prepareCall("{call CalYlfwColumnData(?,?,?,?)}");
			
			cs.setString(1, "123");
			cs.setString(2,"123");
			cs.setString(3,"201301");
			cs.setString(4,"201312");
			ResultSet rs = cs.executeQuery();
			
			while(rs != null && rs.next()){
				System.out.println("ID:"+rs.getString(1));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (cs != null)
					cs.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static void main(String[] args) {
		CallProcedure dao = new CallProcedure();
		dao.getConn();
		dao.callProc();
	}
}

②返回结果集为多个的情况

着重看红色部分

/**
	 * 返回结果为多个结果集的存储过程调用
	 * @param procName
	 * @param param
	 * @return 返回多个结果集
	 */
	public ArrayList<List<ChartDateModel>> callProc(String procName,Map<String,String> param) {
		getConn();
		ArrayList<List<ChartDateModel>> resultList = new ArrayList<List<ChartDateModel>>();

		try {
			//拼接调用存储过程的sql
			String sql = "{call ";
			sql += procName;
			sql += "(";
			for(int i = 0; i < param.size();i++){
				if(i != 0){
					sql += ",?";
				}else{
					sql += "?";
				}
				
			}
			sql += ")}";
			
			cs = conn.prepareCall(sql);
			//设置参数
			for(int i = 1; i <= param.size();i++){
				cs.setString(i, param.get(i+""));
			}
			boolean flag = cs.execute();
			ResultSet result;

			if("CalYlfwColumnData".equals(procName)){
				//医疗收费柱状图
				int resultCount = 0;
				do{
					<span style="color:#FF0000;">result = cs.getResultSet();</span>
					if(result != null){
						if("1".equals(param.get("6"))){//初始化方法
							if( resultCount == 0 || resultCount == 1){
								
								List<ChartDateModel> dataList = new ArrayList<ChartDateModel>();
								while(result.next()){
									ChartDateModel chartDataModel = new ChartDateModel();
									chartDataModel.setId(result.getString(1));
									chartDataModel.setName(result.getString(2));
									chartDataModel.setLineValue(result.getString(3));//调价后总收入
									chartDataModel.setValue(result.getString(4));//调价前总收入
									chartDataModel.setValue1(result.getString(5));//药品加成
									dataList.add(chartDataModel);
								}
								resultList.add(dataList);
							}else if(resultCount == 2){
								List<ChartDateModel> dataList = new ArrayList<ChartDateModel>();
								while(result.next()){
									ChartDateModel chartDataModel = new ChartDateModel();
									chartDataModel.setXmbm(result.getString(1));
									chartDataModel.setXmmc(result.getString(2));
									chartDataModel.setJjdw(result.getString(3));//
									chartDataModel.setSfjg(result.getString(4)==null?0f:Float.parseFloat(result.getString(4)));//
									chartDataModel.setYwl(result.getString(5)==null?0f:Float.parseFloat(result.getString(5)));//
									chartDataModel.setTjsp(result.getString(6)==null?0f:Float.parseFloat(result.getString(6)));
									chartDataModel.setTjzs(result.getString(7)==null?0f:Float.parseFloat(result.getString(7)));
									chartDataModel.setXmlb(result.getString(8));
									dataList.add(chartDataModel);
								}
								resultList.add(dataList);
							}
						}else if("2".equals(param.get("6"))){
							if( resultCount == 0){
								
								List<ChartDateModel> dataList = new ArrayList<ChartDateModel>();
								while(result.next()){
									ChartDateModel chartDataModel = new ChartDateModel();
									chartDataModel.setXmbm(result.getString(1));
									chartDataModel.setXmmc(result.getString(2));
									chartDataModel.setJjdw(result.getString(3));//
									chartDataModel.setSfjg(result.getString(4)==null?0f:Float.parseFloat(result.getString(4)));//
									chartDataModel.setYwl(result.getString(5)==null?0f:Float.parseFloat(result.getString(5)));//
									chartDataModel.setTjsp(result.getString(6)==null?0f:Float.parseFloat(result.getString(6)));
									chartDataModel.setTjzs(result.getString(7)==null?0f:Float.parseFloat(result.getString(7)));
									chartDataModel.setXmlb(result.getString(8));
									dataList.add(chartDataModel);
								}
								resultList.add(dataList);
							}
						}
						
						resultCount++;
					}
					
					<span style="color:#FF0000;">cs.getMoreResults();
					continue;</span>
				}while(result != null);
				System.out.println("count:"+resultCount);
			}
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (cs != null)
					cs.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return resultList;
	}