Calling a Function in a Database - Java JDBC

Java examples for JDBC:Stored Procedure

Description

Calling a Function in a Database

Demo Code

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;

public class Main {

  public void main(String[] argv) {
    CallableStatement cs;/*from  w  ww. j  a v a2  s .  co  m*/
    Connection connection = null;
    try {
      // Call a function with no parameters; 
      cs = connection.prepareCall("{? = call myfunc}");

      // Register the type of the return value
      cs.registerOutParameter(1, 123);

      // Execute and retrieve the returned value
      cs.execute();
      String retValue = cs.getString(1);

      // Call a function with one IN parameter; the function returns a VARCHAR
      cs = connection.prepareCall("{? = call myfuncin(?)}");

      // Register the type of the return value
      cs.registerOutParameter(1, Types.VARCHAR);

      // Set the value for the IN parameter
      cs.setString(2, "a string");

      // Execute and retrieve the returned value
      cs.execute();
      retValue = cs.getString(1);

      // Call a function with one OUT parameter; the function returns a VARCHAR
      cs = connection.prepareCall("{? = call myfuncout(?)}");

      // Register the types of the return value and OUT parameter
      cs.registerOutParameter(1, Types.VARCHAR);
      cs.registerOutParameter(2, Types.VARCHAR);

      // Execute and retrieve the returned values
      cs.execute();
      retValue = cs.getString(1); // return value
      String outParam = cs.getString(2); // OUT parameter

      // Call a function with one IN/OUT parameter; 
      cs = connection.prepareCall("{? = call myfuncinout(?)}");

      // Register the types of the return value and OUT parameter
      cs.registerOutParameter(1, Types.VARCHAR);
      cs.registerOutParameter(2, Types.VARCHAR);

      // Set the value for the IN/OUT parameter
      cs.setString(2, "a string");

      // Execute and retrieve the returned values
      cs.execute();
      retValue = cs.getString(1); // return value
      outParam = cs.getString(2); // IN/OUT parameter
    } catch (SQLException e) {
    }
  }
}

Related Tutorials