Java Tutorial/Database/StoredProcedure

Материал из Java эксперт
Перейти к: навигация, поиск

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>





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>





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 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_Tutorial/Database/StoredProcedure&oldid=4822»