[JDBC]Stored Procedures

Stored Procedures

A - takes no parameters and returns nothing, a ResultSet or a row count:

  CallableStatement cstmt =
     con.prepareCall( "{ call sp_A }" );

  For no return:
  cstmt.execute(); // could use executeUpdate()

  For returned ResultSet:
  ResultSet rs = cstmt.executeQuery();

  For returned update count:
  int iUC = cstmt.executeUpdate();

 

B - single parameter and returns a result parameter. Assumes int result parameter and a String IN parameter:

  CallableStatement cstmt =
     con.prepareCall( "{ ? = call sp_B( ? ) }" );

  // int result parameter
  cstmt.registerOutParameter( 1, Types.INTEGER );
  // String IN parameter
  cstmt.setString( 2, "M-O-O-N" );

  cstmt.execute(); // could use executeUpdate()

  int iRP = cstmt.getInt( 1 );

 

C - multiple parameters and returns nothing, a ResultSet or a row count. Assumes int IN, OUT, and INOUT parameters:

  CallableStatement cstmt =
     con.prepareCall( "{ call sp_C( ? ? ? ) }" );

  Setup:
  // set int IN parameter
  cstmt.setInt( 1, 333 );

  // register int OUT parameter
  cstmt.registerOutParameter( 2, Types.INTEGER );

  // set int INOUT parameter
  cstmt.setInt( 3, 666 );
  // register int INOUT parameter
  cstmt.registerOutParameter( 3, Types.INTEGER );

  For no return ( other than OUT and INOUT: )
    cstmt.execute(); // could use executeUpdate()

    // get int OUT and INOUT
    int iOUT = cstmt.getInt( 2 );
    int iINOUT = cstmt.getInt( 3 );

  For returned ResultSet:
    ResultSet rs = cstmt.executeQuery();

    // get int OUT and INOUT
    int iOUT = cstmt.getInt( 2 );
    int iINOUT = cstmt.getInt( 3 );

  For returned update count:
    int iUC = cstmt.executeUpdate();

    // get int OUT and INOUT
    int iOUT = cstmt.getInt( 2 );
    int iINOUT = cstmt.getInt( 3 );