java一个接口调用太多sql,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:

-- Create date:

-- 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

-- = ,

-- =

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> callProc(String procName,Map param) {

getConn();

ArrayList> resultList = new ArrayList>();

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{

result = cs.getResultSet();

if(result != null){

if("1".equals(param.get("6"))){//初始化方法

if( resultCount == 0 || resultCount == 1){

List dataList = new ArrayList();

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 dataList = new ArrayList();

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 dataList = new ArrayList();

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++;

}

cs.getMoreResults();

continue;

}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;

}