写一个java中调用存储过程的方法

例子1:调用有返回值的存储过程

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class TestProc {

private static Connection connection = null;
public static Connection getConnection(){
try {
Class.forName(“com.microsoft.jdbc.sqlserver.SQLServerDriver”);
connection = DriverManager.getConnection(“jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=mydb”,”sa”,”sa”);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}

public static void main(String[] args) {

Connection connection = TestProc.getConnection();
try{
String poetName = “silas”;
int id = 0;
// 设置调用的存储过程名及参数情况
CallableStatement proc = connection.prepareCall(“{ call test_proc(?, ?) }”);
// 设置输入参数值1的值
proc.setString(1, poetName);
// 设置输出参数及返回类型
proc.registerOutParameter(2,java.sql.Types.INTEGER);
proc.execute();
// 取出存储过程的返回值
id = proc.getInt(2);
System.out.println(“人员ID为:”+id);
connection.close();
}catch (SQLException e){
e.printStackTrace();
}

// 以下为存储过程的定义
// create proc test_proc
// @username varchar(20),@pid int output
// as
// declare @uid int
// set @uid = 0
// select @uid=userid from users where username=@username
// if @uid<>0
// set @pid = @uid
// else
// set @pid = 0

}
}
当前以上存储过程中调用了users表,此处建表省略.

例子2:调用返回结果集的存储过程

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestProcResultSet {

private static Connection connection = null;
public static Connection getConnection(){
try {
Class.forName(“com.microsoft.jdbc.sqlserver.SQLServerDriver”);
connection = DriverManager.getConnection(“jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=mydb”,”sa”,”sa”);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}

public static void main(String[] args) {

Connection connection = TestProc.getConnection();
try{
ResultSet rs = null;
// 设置调用的存储过程名及参数情况,注意的是存储过程不可以有输出参数
CallableStatement proc = connection.prepareCall(“{ call test_proc_resultset(?)}”);
// 设置输入参数
proc.setInt(1, 11);
// 调入存储过程
proc.execute();
// 取出存储过程的结果集
rs = proc.getResultSet();
for(int i=0;rs.next();i++)
System.out.println(“Result的大小为:”+rs.getString(2));
connection.close();
}catch (SQLException e){
e.printStackTrace();
}

// create proc test_proc_resultset
// @id int
// as
// select * from users where userid=@id

}
}

例子3:调用有默认值的存储过程

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class TestDefaultProc {

private static Connection connection = null;
public static Connection getConnection(){
try {
Class.forName(“com.microsoft.jdbc.sqlserver.SQLServerDriver”);
connection = DriverManager.getConnection(“jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=mydb”,”sa”,”sa”);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}

public static void main(String[] args) {

Connection connection = TestDefaultProc.getConnection();
try{
int id = 0;
// 设置调用的存储过程名及默认参数的情况
CallableStatement proc = connection.prepareCall(“{ call test_default_proc(default, ?) }”);
// 设置输出参数及返回类型
proc.registerOutParameter(1,java.sql.Types.INTEGER);
proc.execute();
// 取出存储过程的返回值
id = proc.getInt(1);
System.out.println(“人员ID为:”+id);
connection.close();
}catch (SQLException e){
e.printStackTrace();
}

// 以下为存储过程的定义
// create proc test_default_proc
// @username varchar(20)=’silas’,@pid int output
// as
// declare @uid int
// set @uid = 0
// select @uid=userid from users where username=@username
// if @uid<>0
// set @pid = @uid
// else
// set @pid = 0

}

例子4:调用返回两个以上结果集的存储过程（重点）
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestProcMulResultSet {

private static Connection connection = null;
public static Connection getConnection(){
try {
Class.forName(“com.microsoft.jdbc.sqlserver.SQLServerDriver”);
connection = DriverManager.getConnection(“jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=mydb”,”sa”,”sa”);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}

public static void main(String[] args) {

Connection connection = TestProc.getConnection();
try{
ResultSet rs = null;
// 设置调用的存储过程名及参数情况,注意的是存储过程不可以有输出参数
CallableStatement proc = connection.prepareCall(“{ call test_proc_mulresultset(?)}”);
// 设置输入参数
proc.setInt(1, 11);
// 调入存储过程
proc.execute();
// 取出存储过程的结果集
boolean hasResult = true;
while (hasResult) {
rs = proc.getResultSet();
while(rs.next()) {
System.out.println(“第一条记录第二个字段值为:”+rs.getString(2));
break;
}
hasResult = proc.getMoreResults();
}
connection.close();
}catch (SQLException e){
e.printStackTrace();
}

// create proc test_proc_mulresultset
// @id int
// as
// select * from users where userid=@id
// select * from users order by userid desc
}
}