JDBC:Using Stored Procedures

Using Stored Procedures

A stored procedure is a group of SQL statements that form a logical unit and perform a particular task, and they are used to encapsulate a set of operations or queries to execute on a database server. For example, operations on an employee database (hire, fire, promote, lookup) could be coded as stored procedures executed by application code. Stored procedures can be compiled and executed with different parameters and results, and they can have any combination of input, output, and input/output parameters.
Note that stored procedures are supported by most DBMSs, but there is a fair amount of variation in their syntax and capabilities. Consequently, the tutorial contains two classes, StoredProcedureJavaDBSample and StoredProcedureMySQLSample to demonstrate how to create stored procedures in Java DB and MySQL, respectively.

Overview of Creating Stored Procedures in Java DB

Java DB uses the Java programming language for its stored procedures. Consequently, when you define a stored procedure, you specify which Java class to call and where Java DB can find it.
The following statement creates a stored procedure named SHOW_SUPPLIERS:

CREATE PROCEDURE SHOW_SUPPLIERS()
  PARAMETER STYLE JAVA
  LANGUAGE JAVA
  DYNAMIC RESULT SETS 1
  EXTERNAL NAME 'com.oracle.tutorial.jdbc.StoredProcedureJavaDBSample.showSuppliers'

The following list describes the procedure elements you can specify in the CREATE PROCEDURE statement:

• PARAMETER STYLE JAVA: Specifies that the stored procedure uses a parameter-passing convention that conforms to the Java language and the SQL routines specification (currently, JAVA is the only option
• LANGUAGE JAVA: Specifies the programming language of the stored procedure (currently, JAVA is the only option)
• DYNAMIC RESULT SETS 1: Specifies the maximum number of result sets retrieved; in this case, it is 1
• EXTERNAL NAME 'com.oracle.tutorial.jdbc.StoredProcedureJavaDBSample.showSuppliers' specifies the fully qualified Java method that this stored procedure calls

The stored procedure SHOW_SUPPLIERS does not have any parameters; see the MySQL documentation for information about how to define procedures with parameters (within the parentheses after the procedure's name).
To add the JAR file that contains the class that the stored procedure calls to the database, call the following built-in procedures:

• sqlj.install_jar: Adds a JAR file to a database.
• sqlj.replace_jar: Replaces a JAR file that already exists in a database.
• syscs_util.syscs_set_database_property: Sets a database property; use this procedure to set the property derby.database.classpath to add the JAR file (added to the database by sqlj.install_jar) to the database's class path.

To call a stored procedure, use the CALL statement:

CALL SHOW_SUPPLIERS;

Overview of Creating Stored Procedures in MySQL

MySQL uses a SQL-based syntax for its stored procedures. The following SQL script creates a stored procedure named SHOW_SUPPLIERS that returns the name of the supplier for each type of coffee in the table COFFEES:

SELECT 'Dropping procedure SHOW_SUPPLIERS' AS ' ';

drop procedure if exists SHOW_SUPPLIERS;

SELECT 'Changing delimiter to pipe' AS ' ';

delimiter |

SELECT 'Creating procedure SHOW_SUPPLIERS' AS ' '|

create procedure SHOW_SUPPLIERS()
  begin
    select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME
    from SUPPLIERS, COFFEES
    where SUPPLIERS.SUP_ID = COFFEES.SUP_ID
    order by SUP_NAME;
  end|

delimiter ;

The drop procedure statement deletes that procedure SHOW_SUPPLIERS if it exists. The statement delimiter | changes the character that separates statements from the semicolon (;) to the pipe (|). In MySQL, statements in a stored procedure are separated by semicolons. However, a different delimiter is required to end the create procedure statement. This example uses the pipe (|) character; you can use an other characters (or more than one character).
The create procedure statement consists of the name of the procedure, a comma-separated list of parameters in parentheses, and SQL statements within the begin and end keywords. The stored procedure SHOW_SUPPLIERS does not have any parameters; see the MySQL documentation for information about how to define procedures with parameters.
To call a stored procedure, use the CALL statement:

CALL SHOW_SUPPLIERS();

Creating Stored Procedures in Java DB

Creating and using a stored procedure in Java DB involves the following steps:

1. Create a public static Java method in a Java class; this method performs the required task of the stored procedure.
2. Package the Java class in a jar file.
3. Create the stored procedure; this stored procedure calls the Java method you created.
4. Add the JAR file to the database.
5. Call the stored procedure with the CALL SQL statement.

The class StoredProcedureJavaDBSample creates a stored procedure named SHOW_SUPPLIERS that selects the names of all the suppliers from the table SUPPLIERS that supply coffee listed in the COFFEES table. When this class calls SHOW_SUPPLIERS, it outputs the following:

Acme, Inc.: Colombian_Decaf
Acme, Inc.: Colombian
Superior Coffee: French_Roast_Decaf
Superior Coffee: French_Roast
The High Ground: Espresso

Creating Public Static Java Method

The following method, StoredProcedureJavaDBSample.showSuppliers, contains the SQL statements that the stored procedure SHOW_SUPPLIERS calls:

  public static void showSuppliers(ResultSet[] rs) throws SQLException {
    Connection con = DriverManager.getConnection("jdbc:default:connection");
    Statement stmt = null;

    String query =
      "select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME " + "from SUPPLIERS, COFFEES " +
      "where SUPPLIERS.SUP_ID = COFFEES.SUP_ID " + "order by SUP_NAME";

    stmt = con.createStatement();
    rs[0] = stmt.executeQuery(query);

  }

The SHOW_SUPPLIERS stored procedure takes no arguments. You can specify arguments in a stored procedure by defining them in the method signature of your public static Java method. Note that the method showSuppliers contains a parameter of type ResultSet[]. If your stored procedure returns any number of ResultSet objects, specify one parameter of type ResultSet[] in your Java method. In addition, ensure that this Java method is public and static.
Retrieve the Connection object from the URL jdbc:default:connection. This is a convention in Java DB to indicate that the stored procedure will use the currently existing Connection object.
Note that the Statement object is not closed in this method. Do not close any Statement objects in the Java method of your stored procedure; if you do so, the ResultSet object will not exist when you issue the CALL statement when you call your stored procedure.

Creating Stored Procedure and Specify Commands to Add JAR File to Database

Because StoredProcedureJavaDBSample contains all the steps to create and call a stored procedure, these steps are performed before packaging the Java class file in a JAR file.
The following method, StoredProcedureJavaDBSample.showSuppliers, creates the stored procedure and specifies the commands to add the jar file to the database:

  public void createProcedure(Connection con, String jarPathName) throws SQLException {

    Statement stmt = null;
    Statement stmtDrop = null;
    CallableStatement cs2 = null;
    CallableStatement cs2a = null;
    CallableStatement cs3 = null;

    String queryDrop = "DROP PROCEDURE SHOW_SUPPLIERS";
    String query = "CREATE PROCEDURE SHOW_SUPPLIERS(DBNAME VARCHAR(128)) " +
      "PARAMETER STYLE JAVA " +
      "LANGUAGE JAVA " +
      "DYNAMIC RESULT SETS 1 " +
      "EXTERNAL NAME 'com.oracle.tutorial.jdbc.StoredProcedureJavaDBSample.showSuppliers'";

    String query2 = "CALL sqlj.install_jar('" + jarPathName + "','" + dbName + ".JDBCTutorial',0)";
    String query2a = "CALL sqlj.replace_jar('" + jarPathName + "','" + dbName + ".JDBCTutorial')";
    String query3 = "CALL syscs_util.syscs_set_database_property('derby.database.classpath','" + dbName + ".JDBCTutorial')";

    try {
      stmtDrop = con.createStatement();
      stmtDrop.execute(queryDrop);
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      stmtDrop.close();
    }
    try {
      stmt = con.createStatement();
      stmt.execute(query);
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      stmt.close();
    }
    try {
      cs2 = con.prepareCall(query2);
      cs2.execute();
    } catch (SQLException e2) {
      JDBCTutorialUtilities.printSQLException(e2);
    } finally {
      cs2.close();
      try {
        cs2a = con.prepareCall(query2a);
        cs2a.execute();
      } catch (SQLException e2a) {
        JDBCTutorialUtilities.printSQLException(e2a);
      } finally {
        cs2a.close();
      }
    }
    try {
      cs3 = con.prepareCall(query3);
      cs3.execute();
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      cs3.close();
    }
  }

This CREATE PROCEDURE statement creates the stored procedure SHOW_SUPPLIERS:

CREATE PROCEDURE SHOW_SUPPLIERS()
  PARAMETER STYLE JAVA
  LANGUAGE JAVA
  DYNAMIC RESULT SETS 1
  EXTERNAL NAME 'com.oracle.tutorial.jdbc.StoredProcedureJavaDBSample.showSuppliers'

The install_jar procedure in the SQL schema adds a JAR file to the database. The first argument of this procedure is the full path name of the JAR file on the computer from which this procedure is run. The second argument is an identifier that Java DB uses to refer to the JAR file. The replace_jar procedure replaces a JAR file already in the database.
The system procedure SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY sets or deletes the value of a property of the database on the current connection. This method sets the property derby.database.classpath to the identifier specified in the install_jar file. Java DB first looks in your Java class path for a class, then it looks in derby.database.classpath.
Note: Alternatively, you can specify the location of your JAR file in your CLASSPATH environment variable. Java DB first looks in your CLASSPATH for any required classes before looking in the database.

Specify Commands to Call Stored Procedure

The following method, StoredProcedureJavaDBSample.runStoredProcedure, calls the stored procedure:

  public void runStoredProcedure(Connection con) throws SQLException {
    CallableStatement cs = null;

    try {
      cs = con.prepareCall("{call SHOW_SUPPLIERS( '" + this.dbName + "')}");
      cs.execute();
      ResultSet rs = cs.getResultSet();
      while (rs.next()) {
        String supplier = rs.getString("SUP_NAME");
        String coffee = rs.getString("COF_NAME");
        System.out.println(supplier + ": " + coffee);
      }
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      cs.close();
    }
  }

The interface CallableStatement extends PreparedStatement. It is used to call stored procedures.
Note: As with Statement objects, to call the stored procedure, you can call execute, executeQuery, or executeUpdate depending on how many ResultSet objects the procedure returns. However, if you are not sure how many ResultSet objects the procedure returns, call execute.

Package Java Class in JAR File

The Ant build script build.xml contains targets to compile and package the tutorial in a JAR file. At a command prompt, change the current directory to <JDBC tutorial directory>. From this directory, run the following command to compile and package the tutorial in a JAR file:
ant jar
The name of the JAR file is <JDBC tutorial directory>/lib/JDBCTutorial.jar.
Note: Ensure that you have modified sampleproperties.xml so that the value of the property jar_file is set to the full path name of JDBCTutorial.jar.

Creating Stored Procedure in MySQL

Creating and using a stored procedure in Java DB involves the following steps:

1. Create the stored procedure with JDBC API
2. Call the stored procedure with the CALL SQL statement

The class StoredProcedureMySQLSample creates a stored procedure named SHOW_SUPPLIERS that selects the names of all the suppliers from the table SUPPLIERS that supply coffee listed in the COFFEES table. When this class calls SHOW_SUPPLIERS, it outputs the following:

Acme, Inc.: Colombian_Decaf
Acme, Inc.: Colombian
Superior Coffee: French_Roast_Decaf
Superior Coffee: French_Roast
The High Ground: Espresso

Creating Stored Procedure with JDBC API

The following method, StoredProcedureMySQLSample, creates a stored procedure named SHOW_SUPPLIERS:

  public void createProcedureShowSuppliers() throws SQLException {
    String createProcedure = null;
    createProcedure = "create procedure SHOW_SUPPLIERS() " +
           "begin " +
           "select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME " +
           "from SUPPLIERS, COFFEES " +
           "where SUPPLIERS.SUP_ID = COFFEES.SUP_ID " +
           "order by SUP_NAME; " +
           "end";
    Statement stmt = null;

    try {
      stmt = con.createStatement();
      stmt.executeUpdate(createProcedure);
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      stmt.close();
    }
  }

This CREATE PROCEDURE statement creates a stored procedure:

create procedure SHOW_SUPPLIERS()
  begin
  select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME
  from SUPPLIERS, COFFEES
  where SUPPLIERS.SUP_ID = COFFEES.SUP_ID
  order by SUP_NAME;
end

MySQL uses a SQL-based syntax for its stored procedures. Note that the Connection object con must be connected to a specific database.

Calling Stored Procedures

The following method, StoredProcedureMySQLample.runStoredProcedure, calls the stored procedure:

  public void runStoredProcedure() throws SQLException {
    CallableStatement cs = null;

    try {
      cs = con.prepareCall("{call SHOW_SUPPLIERS}");
      ResultSet rs = cs.executeQuery();

      while (rs.next()) {
        String supplier = rs.getString("SUP_NAME");
        String coffee = rs.getString("COF_NAME");
        System.out.println(supplier + ": " + coffee);
      }

    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      cs.close();
    }
  }

The interface CallableStatement extends PreparedStatement. It is used to call stored procedures.
Note: As with Statement objects, to call the stored procedure, you can call execute, executeQuery, or executeUpdate depending on how many ResultSet objects the procedure returns. However, if you are not sure how many ResultSet objects the procedure returns, call execute.