java调用sqlserver存储过程

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

}