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

    }