java调用存储过程

 

创建存储过程的脚本,

使用sqlserver2000 中的pubs 数据库中的 jobs表为例.

create procedure  showAll
as 
select * from jobs



create procedure obtainJob_desc
@outputParam varchar(20) output,
@id          int
as
select @outputParam = job_desc from jobs where job_id = @id 
 

create procedure obtainReturn
as
declare @ret int
select @ret = count(*) from jobs
return @ret


declare @ret int
exec  @ret = obtainReturn 
print @ret

 

用来获得连接的函数

public  Connection getConnection()...{
  Connection con = null;
  try ...{
   Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
   con = DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;databasename=pubs","sa","");
  } catch (Exception e) ...{
   e.printStackTrace();
  }
  return con ;
 }

 

1,调用得到结果集的存储过程

 

public void getResultSet()...{
        //获得连接
        Connection con = this.getConnection();
        try ...{
            //showAll为存储过程名
            java.sql.CallableStatement cstm = con.prepareCall("{call showAll }");
            ResultSet rs = cstm.executeQuery();
            while(rs.next())...{
                //这里写逻辑代码。
                System.out.println(rs.getString(1));
            }
            rs.close();
            con.close();
        } catch (SQLException e) ...{
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
    }

2,调用带有 输入 ,输出 参数的存储过程。

 

public void getOutParameter(int inParam)...{
        String outParam;
        Connection con = this.getConnection();
        
        try ...{
            CallableStatement cstm = con.prepareCall("{call obtainJob_desc (?,?)}");
            cstm.registerOutParameter(1, Types.VARCHAR);
            cstm.setInt(2, inParam);
            cstm.execute();;
            
            //得到输出参数。
            String outParma = cstm.getString(2);
            System.out.println(outParma);
            cstm.close();
            con.close();
            
        } catch (SQLException e) ...{
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
    }

 

3,调用带返回值的存储过程。

 

public void getReturn()...{
        int ret;
        Connection con = this.getConnection();
        try ...{
            CallableStatement cstm = con.prepareCall("{?=call obtainReturn()}");
            cstm.registerOutParameter(1, Types.INTEGER);
            
            cstm.execute();
            //得到返回值
             ret = cstm.getInt(1);
            
            System.out.println(ret);
            cstm.close();
            con.close();
            
        } catch (SQLException e) ...{
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
    }