Java/Database SQL JDBC/Store Procedure

Материал из Java эксперт
Версия от 21:01, 31 мая 2010; (обсуждение)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

Содержание

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

   <source lang="java">

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
 }

} 


 </source>



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

   <source lang="java">

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

} 


 </source>



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

   <source lang="java">

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
 }

} 


 </source>



Call a procedure with one IN/OUT parameter

   <source lang="java">

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

} 


 </source>



Call a procedure with one IN parameter

   <source lang="java">

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

} 


 </source>



Call a procedure with one OUT parameter

   <source lang="java">

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

} 


 </source>



Call a stored procedure with no parameters and return value.

   <source lang="java">

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

} 


 </source>



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

   <source lang="java">

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

} 


 </source>



Calling a Stored Procedure in a Database with no parameters

   <source lang="java">

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

} 


 </source>



Call stored procedure

   <source lang="java">

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
 }

} 


 </source>



Call Stored Procedure In MySql

   <source lang="java">

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

} 


 </source>



Call Stored Procedure In Oracle And Pass In Out Parameters

   <source lang="java">

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

} 


 </source>



Connect to database and call stored procedure

   <source lang="java">

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

} 


 </source>



Create a function named myfuncinout that returns a VARCHAR value

   <source lang="java">

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

} 


 </source>



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

   <source lang="java">

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

} 


 </source>



Create a function named myfuncout which returns a VARCHAR value;

   <source lang="java">

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

} 


 </source>



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

   <source lang="java">

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

} 


 </source>



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

   <source lang="java">

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

} 


 </source>



Create procedure myprocin with an IN parameter named x.

   <source lang="java">

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

} 


 </source>



Create procedure myprocout with an OUT parameter named x

   <source lang="java">

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

} 


 </source>



Creating a Stored Procedure or Function in an Oracle Database

   <source lang="java">

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

} 


 </source>



Get Stored Procedure Name And Type

   <source lang="java">

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

} 



 </source>



Get Stored Procedure Signature

   <source lang="java">

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

} 



 </source>



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

   <source lang="java">

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

} 


 </source>



Stored procedure utilities

   <source lang="java">

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

} 



 </source>



Stored procedure with Input/Output parms and a ResultSet

   <source lang="java">

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

} 


 </source>
Источник — «http://jexp.ru/index.php?title=Java/Database_SQL_JDBC/Store_Procedure&oldid=6952»