例子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 }}