Java/Database SQL JDBC/Store Procedure

Call a function with one IN/OUT parameter; the function returns a VARCHAR

  
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    CallableStatement 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();
    String retValue = cs.getString(1);           // return value
    String outParam = cs.getString(2);           // IN/OUT parameter
  }
}

Call a function with one IN parameter; the function returns a VARCHAR

  
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    CallableStatement cs = connection.prepareCall("{? = call myfuncin(?)}");
    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();
    String retValue = cs.getString(1);
  }
}

Call a function with one OUT parameter; the function returns a VARCHAR

  
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    CallableStatement 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();
    String retValue = cs.getString(1); // return value
    String outParam = cs.getString(2); // OUT parameter
  }
}

Call a procedure with one IN/OUT parameter

  

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    CallableStatement cs = connection.prepareCall("{call myprocinout(?)}");
    
    // Register the type of the IN/OUT parameter
    cs.registerOutParameter(1, Types.VARCHAR);
    // Set the value for the IN/OUT parameter
    cs.setString(1, "a string");
    // Execute the stored procedure and retrieve the IN/OUT value
    cs.execute();
    String outParam = cs.getString(1);            // OUT parameter
    System.out.println(outParam);
  }
}

Call a procedure with one IN parameter

  
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    // Call a procedure with no parameters
    CallableStatement cs = connection.prepareCall("{call myprocin(?)}");
   
    // Set the value for the IN parameter
    cs.setString(1, "a string");
    cs.execute();
  }
}

Call a procedure with one OUT parameter

  
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    CallableStatement cs = connection.prepareCall("{call myprocout(?)}");
    
    // Register the type of the OUT parameter
    cs.registerOutParameter(1, Types.VARCHAR);
    cs.execute();
    String outParam = cs.getString(1);     // OUT parameter
    System.out.println(outParam);
  }
}

Call a stored procedure with no parameters and return value.

  

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
public class Main {
  public static void main(String[] argv) throws Exception {
    Connection con = null;
    CallableStatement proc_stmt = null;
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    con = DriverManager.getConnection("jdbc:sqlserver://MYSERVER;databaseName=MYDATABASE",
        "USERID", "PASSWORD");
    proc_stmt = con.prepareCall("{ call someStoredProc() }");
    proc_stmt.executeQuery();
  }
}

Calling a Function in a Database: call functions with IN, OUT, and IN/OUT parameters.

  
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    CallableStatement cs = connection.prepareCall("{? = call myfunc}");
    // Register the type of the return value
    int intValue = 0;
    cs.registerOutParameter(1, intValue);
    // Execute and retrieve the returned value
    cs.execute();
    String retValue = cs.getString(1);
  }
}

Calling a Stored Procedure in a Database with no parameters

  
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    CallableStatement cs = connection.prepareCall("{call myproc}");
    cs.execute();
  }
}

Call stored procedure

  
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Types;
public class Main {
  public static void main(String[] argv) throws Exception {
    Connection conn = null;
    String query = "begin proc(?,?,?); end;";
    CallableStatement cs = conn.prepareCall(query);
    cs.setString(1, "string parameter");
    cs.setInt(2, 1);
    cs.registerOutParameter(2, Types.INTEGER);
    cs.registerOutParameter(3, Types.INTEGER);
    cs.execute();
    int parm2 = cs.getInt(2); // get the result from OUTPUT #2
    int parm3 = cs.getInt(3); // get the result from OUTPUT #3
  }
}

Call Stored Procedure In MySql

  
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ParameterMetaData;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getMySqlConnection();
    // Step-2: identify the stored procedure
    String simpleProc = "{ call simpleproc(?) }";
    // Step-3: prepare the callable statement
    CallableStatement cs = conn.prepareCall(simpleProc);
    // Step-4: register output parameters ...
    cs.registerOutParameter(1, java.sql.Types.INTEGER);
    // Step-5: execute the stored procedures: proc3
    cs.execute();
    // Step-6: extract the output parameters
    int param1 = cs.getInt(1);
    System.out.println("param1=" + param1);
    // Step-7: get ParameterMetaData
    ParameterMetaData pmeta = cs.getParameterMetaData();
    if (pmeta == null) {
      System.out.println("Vendor does not support ParameterMetaData");
    } else {
      System.out.println(pmeta.getParameterType(1));
    }
    conn.close();
  }
  private static Connection getHSQLConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    System.out.println("Driver Loaded.");
    String url = "jdbc:hsqldb:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
  public static Connection getMySqlConnection() throws Exception {
    String driver = "org.gjt.mm.mysql.Driver";
    String url = "jdbc:mysql://localhost/demo2s";
    String username = "oost";
    String password = "oost";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
  public static Connection getOracleConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:caspian";
    String username = "mp";
    String password = "mp2";
    Class.forName(driver); // load Oracle driver
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
}

Call Stored Procedure In Oracle And Pass In Out Parameters

  
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getOracleConnection();
    // Step-2: identify the stored procedure
    String proc3StoredProcedure = "{ call proc3(?, ?, ?) }";
    // Step-3: prepare the callable statement
    CallableStatement cs = conn.prepareCall(proc3StoredProcedure);
    // Step-4: set input parameters ...
    // first input argument
    cs.setString(1, "abcd");
    // third input argument
    cs.setInt(3, 10);
    // Step-5: register output parameters ...
    cs.registerOutParameter(2, java.sql.Types.VARCHAR);
    cs.registerOutParameter(3, java.sql.Types.INTEGER);
    // Step-6: execute the stored procedures: proc3
    cs.execute();
    // Step-7: extract the output parameters
    // get parameter 2 as output
    String param2 = cs.getString(2);
    // get parameter 3 as output
    int param3 = cs.getInt(3);
    System.out.println("param2=" + param2);
    System.out.println("param3=" + param3);
    conn.close();
  }
  private static Connection getHSQLConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    System.out.println("Driver Loaded.");
    String url = "jdbc:hsqldb:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
  public static Connection getMySqlConnection() throws Exception {
    String driver = "org.gjt.mm.mysql.Driver";
    String url = "jdbc:mysql://localhost/demo2s";
    String username = "oost";
    String password = "oost";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
  public static Connection getOracleConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:caspian";
    String username = "mp";
    String password = "mp2";
    Class.forName(driver); // load Oracle driver
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
}

Connect to database and call stored procedure

  
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
public class Main {
  public static void main(String[] argv) throws Exception {
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    Connection con = DriverManager.getConnection("jdbc:sqlserver://MYSERVER;databaseName=MYDATABASE",
        "USERID", "PASSWORD");
    CallableStatement proc_stmt = con.prepareCall("{ call generateID(?) }");
    proc_stmt.setString(1, "employee");
    ResultSet rs = proc_stmt.executeQuery();
    if (rs.next()) {
      int employeeId = rs.getInt(1);
      System.out.println("Generated employeeId: " + employeeId);
    } else {
      System.out.println("Stored procedure couldn"t generate new Id");
    }
  }
}

Create a function named myfuncinout that returns a VARCHAR value

  
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "oracle.jdbc.driver.OracleDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1521";
    String sid = "mydatabase";
    String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    Statement stmt = connection.createStatement();
    String function = "CREATE OR REPLACE FUNCTION myfuncinout(x IN OUT VARCHAR) RETURN VARCHAR IS "
        + "BEGIN x:= x||"outvalue"; RETURN "a returned string"; END;";
    stmt.executeUpdate(function);
  }
}

Create a function named myfuncin which returns a VARCHAR value; the function has an IN parameter named x

  
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "oracle.jdbc.driver.OracleDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1521";
    String sid = "mydatabase";
    String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    Statement stmt = connection.createStatement();
    String function = "CREATE OR REPLACE FUNCTION myfuncin(x VARCHAR) RETURN VARCHAR IS "
        + "BEGIN RETURN "a return string"||x; END;";
    stmt.executeUpdate(function);
  }
}

Create a function named myfuncout which returns a VARCHAR value;

  
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "oracle.jdbc.driver.OracleDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1521";
    String sid = "mydatabase";
    String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    Statement stmt = connection.createStatement();
    String function = "CREATE OR REPLACE FUNCTION myfuncout(x OUT VARCHAR) RETURN VARCHAR IS "
        + "BEGIN " + "x:= "outvalue"; " + "RETURN "a returned string"; " + "END;";
    stmt.executeUpdate(function);
  }
}

Create a function named myfunc which returns a VARCHAR value; the function has no parameter

  
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "oracle.jdbc.driver.OracleDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1521";
    String sid = "mydatabase";
    String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    Statement stmt = connection.createStatement();
    String function = "CREATE OR REPLACE FUNCTION myfunc RETURN VARCHAR IS BEGIN RETURN "a returned string"; END;";
    stmt.executeUpdate(function);
  }
}

Create procedure myprocinout with an IN/OUT parameter named x; x is an IN parameter and an OUT parameter

  
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "oracle.jdbc.driver.OracleDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1521";
    String sid = "mydatabase";
    String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    String procedure = "CREATE OR REPLACE PROCEDURE myprocinout(x IN OUT VARCHAR) IS BEGIN "
        + "INSERT INTO oracle_table VALUES(x); " // Use x as IN parameter
        + "x := "outvalue"; " // Use x as OUT parameter
        + "END;";
    Statement stmt = connection.createStatement();
    stmt.executeUpdate(procedure);
  }
}

Create procedure myprocin with an IN parameter named x.

  
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "oracle.jdbc.driver.OracleDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1521";
    String sid = "mydatabase";
    String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    Statement stmt = connection.createStatement();
    // IN is the default mode for parameter, so both "x VARCHAR" and "x IN VARCHAR" are valid
    String procedure = "CREATE OR REPLACE PROCEDURE myprocin(x VARCHAR) IS " + "BEGIN "
        + "INSERT INTO oracle_table VALUES(x); " + "END;";
    stmt.executeUpdate(procedure);
  }
}

Create procedure myprocout with an OUT parameter named x

  
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "oracle.jdbc.driver.OracleDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1521";
    String sid = "mydatabase";
    String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    Statement stmt = connection.createStatement();
    String procedure = "CREATE OR REPLACE PROCEDURE myprocout(x OUT VARCHAR) IS BEGIN "
        + "INSERT INTO oracle_table VALUES("string 2"); x := "outvalue"; END;";
    stmt.executeUpdate(procedure);
  }
}

Creating a Stored Procedure or Function in an Oracle Database

  
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "oracle.jdbc.driver.OracleDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1521";
    String sid = "mydatabase";
    String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    CallableStatement cs = connection.prepareCall("{? = call myfuncinout(?)}");
    
    Statement stmt = connection.createStatement();
    
    // Create procedure myproc with no parameters
    String procedure = "CREATE OR REPLACE PROCEDURE myproc IS "
        + "BEGIN "
        + "INSERT INTO oracle_table VALUES("string 1"); "
        + "END;";
    stmt.executeUpdate(procedure);
  }
}

Get Stored Procedure Name And Type

  
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getMySqlConnection();
    System.out.println("Got Connection.");
    Statement st = conn.createStatement();
    st.executeUpdate("drop table survey;");
    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
    ResultSet rs = null;
    DatabaseMetaData meta = conn.getMetaData();
    rs = meta.getProcedures(null, null, "%");
    while (rs.next()) {
      String spName = rs.getString("PROCEDURE_NAME");
      int spType = rs.getInt("PROCEDURE_TYPE");
      System.out.println("Stored Procedure Name: " + spName);
      if (spType == DatabaseMetaData.procedureReturnsResult) {
        System.out.println("procedure Returns Result");
      } else if (spType == DatabaseMetaData.procedureNoResult) {
        System.out.println("procedure No Result");
      } else {
        System.out.println("procedure Result unknown");
      }
    }
    st.close();
    conn.close();
  }
  private static Connection getHSQLConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    System.out.println("Driver Loaded.");
    String url = "jdbc:hsqldb:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
  public static Connection getMySqlConnection() throws Exception {
    String driver = "org.gjt.mm.mysql.Driver";
    String url = "jdbc:mysql://localhost/demo2s";
    String username = "oost";
    String password = "oost";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
  public static Connection getOracleConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:caspian";
    String username = "mp";
    String password = "mp2";
    Class.forName(driver); // load Oracle driver
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
}

Get Stored Procedure Signature

  
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getMySqlConnection();
    System.out.println("Got Connection.");
    Statement st = conn.createStatement();
    st.executeUpdate("drop table survey;");
    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
    DatabaseMetaData dbMetaData = conn.getMetaData();
    ResultSet rs = dbMetaData.getProcedureColumns(conn.getCatalog(),
                          null,
                          "procedureNamePattern",
                          "columnNamePattern");
    while(rs.next()) {
      // get stored procedure metadata
      String procedureCatalog     = rs.getString(1);
      String procedureSchema      = rs.getString(2);
      String procedureName        = rs.getString(3);
      String columnName           = rs.getString(4);
      short  columnReturn         = rs.getShort(5);
      int    columnDataType       = rs.getInt(6);
      String columnReturnTypeName = rs.getString(7);
      int    columnPrecision      = rs.getInt(8);
      int    columnByteLength     = rs.getInt(9);
      short  columnScale          = rs.getShort(10);
      short  columnRadix          = rs.getShort(11);
      short  columnNullable       = rs.getShort(12);
      String columnRemarks        = rs.getString(13);
      System.out.println("stored Procedure name="+procedureName);
      System.out.println("procedureCatalog=" + procedureCatalog);
      System.out.println("procedureSchema=" + procedureSchema);
      System.out.println("procedureName=" + procedureName);
      System.out.println("columnName=" + columnName);
      System.out.println("columnReturn=" + columnReturn);
      System.out.println("columnDataType=" + columnDataType);
      System.out.println("columnReturnTypeName=" + columnReturnTypeName);
      System.out.println("columnPrecision=" + columnPrecision);
      System.out.println("columnByteLength=" + columnByteLength);
      System.out.println("columnScale=" + columnScale);
      System.out.println("columnRadix=" + columnRadix);
      System.out.println("columnNullable=" + columnNullable);
      System.out.println("columnRemarks=" + columnRemarks);
    }

    st.close();
    conn.close();
  }
  private static Connection getHSQLConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    System.out.println("Driver Loaded.");
    String url = "jdbc:hsqldb:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
  public static Connection getMySqlConnection() throws Exception {
    String driver = "org.gjt.mm.mysql.Driver";
    String url = "jdbc:mysql://localhost/demo2s";
    String username = "oost";
    String password = "oost";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
  public static Connection getOracleConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:caspian";
    String username = "mp";
    String password = "mp2";
    Class.forName(driver); // load Oracle driver
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
}

Getting the Stored Procedure Names in a Database: retrieves the names of all stored procedures in a database.

  
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    DatabaseMetaData dbmd = connection.getMetaData();
    // Get all stored procedures in any schema and catalog
    ResultSet resultSet = dbmd.getProcedures(null, null, "%");
    // Get stored procedure names from the result set
    while (resultSet.next()) {
        String procName = resultSet.getString(3);
        System.out.println(procName);
    }
  }
}

Stored procedure utilities

  
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
public class StoredProcUtil {
  private static DataSource pool;
  private static Context env;
  static {
    try {
      env = (Context) new InitialContext().lookup("java:comp/env");
      pool = (DataSource) env.lookup("jdbc/oracle-8i-athletes");
      if (pool == null)
        throw new Exception(
            ""oracle-8i-athletes" is an unknown DataSource");
    } catch (Exception e) {
      System.out.println(e);
    }
  }//static
  public static void addRaceEvent(String name, String location, String date) {
    if ((!check(name)) || (!check(location)) || (!check(date)))
      throw new IllegalArgumentException(
          "Invalid param values passed to addRaceEvent()");
    Connection conn = null;
    try {
      conn = pool.getConnection();
      if (conn == null)
        throw new SQLException(
            "Invalid Connection in addRaceEvent method");
      CallableStatement cs = null;
      //Create an instance of the CallableStatement
      cs = conn.prepareCall("{call addEvent (?,?,?)}");
      cs.setString(1, name);
      cs.setString(2, location);
      cs.setString(3, date);
      //Call the inherited PreparedStatement.executeUpdate() method
      cs.executeUpdate();
      // return the connection to the pool
      conn.close();
    } catch (SQLException sqle) {
    }
  }//addRaceEvent
  private static boolean check(String value) {
    if (value == null || value.equals(""))
      return false;
    return true;
  }
}

Stored procedure with Input/Output parms and a ResultSet

  
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
public class Main {
  public static void main(String[] argv) throws Exception {
  }
  public static int storedProcWithResultSet() throws Exception {
    Connection conn = null;
    CallableStatement cs = conn.prepareCall("{? = call proc (?,?,?,?,?,?,?)}");
    // register input parameters
    cs.setString(2, "");
    cs.setString(3, "");
    cs.setString(4, "123");
    // regsiter ouput parameters
    cs.registerOutParameter(5, java.sql.Types.CHAR);
    cs.registerOutParameter(6, java.sql.Types.CHAR);
    cs.registerOutParameter(7, java.sql.Types.CHAR);
    // Procedure execution
    ResultSet rs = cs.executeQuery();
    ResultSetMetaData rsmd = rs.getMetaData();
    int nbCol = rsmd.getColumnCount();
    while (rs.next()) {
      for (int i = 1; i <= nbCol; i++) {
        System.out.println(rs.getString(i));
        System.out.println(rs.getString(i));
      }
    }
    // OUTPUT parameters
    System.out.println("return code of Stored procedure = : " + cs.getInt(1));
    for (int i = 5; i <= 7; i++)
      System.out.println("parameter " + i + " : " + cs.getString(i));
    return cs.getInt(1);
  }
}