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

    }