Java Stored Procedures in Java DB

1 Java Stored Procedure

This post is about Java stored procedures in Java DB.

Java DB is a relational database management system that is based on the Java programming language and SQL. This is the Oracle release of the Apache Software Foundation’s open source Derby project. Java DB is included in the Java SE 7 SDK.

Java code invoked within the database is a stored procedure (or procedure). Java stored procedures are database side JDBC (Java Database Connectivity) routines.

The procedure code is defined in a Java class method and stored in the database. This is executed using SQL. The procedure code can be with or without any database related code.

Other database side (or server side) programs are triggers and table functions.

1.1 Java Procedure Types

There are two types of stored procedures, based on the transaction in which they are invoked in: nested connections and non-nested connections.

Nested Connections

This type of procedure uses the same transaction as that of the SQL statement that called it. The procedure code uses the same connection as that of the parent SQL, using the connection URL syntax jdbc:default:connection. The following is an example code bit to get a connection:

Connection c = DriverManager.getConnection("jdbc:default:connection");

Note that the connection URL attributes are not supported for this type.

Example code at: 2.1 Creating.

Non-nested Connections

This type of procedure uses a new database connection. The procedure is executed in a different transaction than that of the calling SQL.

The stored procedure code can also connect to a different database.

Example code at: 3.1 Using a Non-nested Connection.

1.2 SQL Exceptions in Procedures

SQL exceptions in procedures can be caught and handled within the procedure code, or propagated (and caught) in the calling program.

2 Create and Use a Java Stored Procedure

This describes the creating a Java stored procedure in Java DB database and using it interactively in SQL as also in Java code. The stored procedure code is created using the Java programming language. The procedure is Java code in a method with signature public static void procedureMethod. The stored procedure is created and stored in the Java DB database as a database object.

The procedure is invoked (or called) using a SQL command, or from a Java program using JDBC API.

2.1 Creating

Create a Java method, compile it, and store the procedure in database.

2.1.1 Create a Java Method

The following is an example method.

public static void testProc(int iParam1, String iParam2, int [] oParam)
        throws SQLException {
    String connectionURL = "jdbc:default:connection";
    Connection conn = DriverManager.getConnection(connectionURL);
    String DML = "UPDATE TEST_TABLE SET NAME = ? WHERE ID = ?";
    PreparedStatement pstmnt = conn.prepareStatement(DML);
    pstmnt.setString(1, iParam2);
    pstmnt.setInt(2, iParam1);
    int updateRowcount = pstmnt.executeUpdate();
    oParam [0] = updateRowcount;
} // testProc()

The code is created in a Java class, for example JavaStoredProcs.java, and compiled. Any number of procedure methods can be created within a class.

In the example code:

• The procedure method has three parameters. The first two (iParam1 and iParam2) are of IN and the third is an OUT parameter modes respectively. Note that the OUT parameter is specified as an array; each OUT and INOUT parameter is required to be specified in the procedure method as an array, and only the first element of the array is used (i.e., mapped) as the procedure parameter variable.
• The procedure uses a nested connection.
• Any SQL exception thrown can be handled in the calling program, or within the procedure method; in this case the exception is handled in the calling code.

2.1.2 Create a Procedure in Database

The procedure is created in the database using the CREATE PROCEDURE statement. This command is run interactively using ij, or from a Java program using JDBC API’s java.sql.Statement interface.

The command syntax and details is as follows:

CREATE PROCEDURE procedure-Name(ProcedureParameters)ProcedureElements

procedure-Name: is the procedure name as stored in the database; is created in the default schema, if not specified.

ProcedureParameters: specifies the parameter mode (IN, INOUT or OUT), an optional name and the data type. The data type is of the database data type. Java DB does not support long column types (for example Long Varchar, BLOB, …) in procedures. Parameters are optional.

ProcedureElements: This must contain the following three elements, and can have additional optional ones.

• LANGUAGE JAVA. This is the only value.
• PARAMETER STYLE JAVA. This is the only value.
• EXTERNAL NAME. This specifies the Java method to be called when the procedure is executed, and takes the form ClassName.methodName Optional, procedure elements:
• DYNAMIC RESULT SETS integer
• DeterministicCharacteristic
• EXTERNAL SECURITY
• MODIFIES SQL DATA (the default), CONTAINS SQL, READS SQL DATA, NO SQL (a procedure without any database related code)

2.1.2.1 Create Procedure in Database Interactively Using ij

ij is a command line tool included with Java DB. ij is a JDBC tool used to run interactive queries on a Java DB database.

ij> CONNECT 'jdbc:derby:testDB';
ij> CREATE PROCEDURE PROC_NAME(IN id1 INTEGER, IN name2 VARCHAR(50), OUT count3 INTEGER) LANGUAGE JAVA EXTERNAL NAME 'JavaStoredProcs.testProc' PARAMETER STYLE JAVA;

In the example, the procedure PROC_NAME is created in the testDB database. The Java method created earlier (2.1.1 Create a Java Method), is specified as the EXTERNAL NAME.

To list the procedures in the database, use the command SHOW PROCEDURES.

2.2.2 Change or Delete a Procedure

To change a procedure, delete the procedure from the database and create again.

Example for deleting a procedure using ij:

ij> DROP PROCEDURE procedureName;

2.2 Using (Invoking)

A procedure is run interactively using the SQL CALL command or from a client program using JDBC API.

The CALL SQL command supports only IN parameters. The JDBC API’s CallableStatement interface is used to invoke a procedure with IN, INOUT or OUT parameters.

2.2.1 CALL SQL Statement

The CALL statement is used to invoke a procedure. This does not return a value. Only procedures with IN parameters are supported when invoked using the CALL.

The following example shows a CALL command run from ij to invoke the procedure MyProc. MyProc is the name of a procedure as defined in the database using CREATE PROCEDURE.

ij> CALL MyProc();

2.2.2 Invoke Procedure from a Java Program

This example code invokes the procedure (PROC_NAME) created earlier (2.1 Creating).

The code uses JDBC API’s CallableStatement interface (more details at 2.2.3 Notes on CallableStatement). The input parameters to the procedure are set and the out parameter value is printed at the end of this example method. Note that this Java class is different than that of the class in which the procedure is created.

private static void runStoredProc(Connection conn)
        throws SQLException {
    int iParam1 = 1;
    String iParam2 = "updated name data";
    String proc = "{call PROC_NAME(?, ?, ?)}";
    CallableStatement cs = conn.prepareCall(proc);
    cs.setInt(1, iParam1);
    cs.setString(2, iParam2);
    cs.registerOutParameter(3, java.sql.Types.INTEGER);
    cs.executeUpdate();
    String oParam = cs.getInt(3);
    System.out.println("Updated row count from the proc: " + oParam);
} // runStoredProc()

2.2.3 Notes on CallableStatement

Java JDBC API’s CallableStatement interface extends PreparedStatement and is defined in java.sql package. This is used to execute SQL stored procedures.

The API provides a stored procedure SQL escape syntax that allows procedures to be called in a standard way for all RDBMSs. Syntax has one form that includes a result parameter and one that does not. If used, the result parameter must be registered as an OUT parameter. The other parameters (arg1, arg2, …) can be used for input, output or both.

The following are the syntax (with and without return value, respectively):

{? = call <procedure-name> [(arg1, arg2, ...)]}
{call <procedure-name> [(arg1, arg2, ...)]}

IN parameter values are set using the setter methods inherited from the PreparedStatement. The type of all OUT parameters must be  registered prior to executing the stored procedure using registerOutParameter(); their values are retrieved after execution, via the getXxx(int parameterIndex / StringparameterName) methods (getBoolean(), getArray(),  …).

Parameter Modes

The parameter attributes IN (the default), OUT, and INOUT are parameter modes.

Calling a Stored Procedure

String procName = "{call STORED_PRODURE_NAME(}";
CallableStatement cs = conn.prepareCall(procName);
ResultSet rs = cs.executeQuery();

To call a stored procedure, use execute(), executeQuery(), or executeUpdate() methods depending on how many ResultSet objects  the procedure returns. If not sure how many ResultSet objects the procedure returns, use the execute() method.

cs = conn.prepareCall("{call INCR_PRICE(?, ?)}");
cs.setString(1, itemNameArg);				// (1)
cs.setFloat(2, newPriceArg);				// (2a)
cs.registerOutParameter(2, Types.NUMERIC); 	// (2b)
cs.execute();
float newPrice = cs.getFloat(2);			// (2c)

The first parameter 1 is an IN parameter.

The second parameter has the parameter mode INOUT. It’s IN value is specified by calling the setter method 2a and register the OUT type with the registerOutParameter() method 2b. The output value is retrieved by the getter method 2c.

3 Examples

There are two examples: the first shows code to create and use a non-nested type procedure, and the next is an example usage of a Java DB’s pre-defined procedure.

3.1 Using a Non-nested Connection

In this example, the Java procedure accesses a different database and connection, than the one used in the calling program. The procedure returns an OUT integer parameter value.

• Create and compile the procedure code.

  public static void testProc4(int [] retval)
          throws SQLException {
      String connectionURL = "jdbc:derby:testDB2";
      Connection conn = DriverManager.getConnection(connectionURL);
      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery("SELECT * FROM ID_TABLE");
      int nextid = 0;
      while(rs.next()) {
          nextid = rs.getInt("ID");
      }
      retval[0] = nextid;
      conn.close(); // alternative: shutdown the database.
  } // testProc4()
  

• Create the procedure in the database.

  CREATE PROCEDURE PROC_NAME_4(OUT paramname INTEGER)
      LANGUAGE JAVA
      EXTERNAL NAME 'JavaStoredProcs.testProc4'
      PARAMETER STYLE JAVA
      READS SQL DATA;
  

  The procedure element READS SQL DATA specifies that the SQL in procedure method can only use SELECT statements.

• Invoke the procedure in client program.

  private static void runStoredProc4(Connection conn)
          throws SQLException {
      String proc = "{call PROC_NAME_4(?)}";
      CallableStatement cs = conn.prepareCall(proc);
      cs.registerOutParameter(1, java.sql.Types.INTEGER);
      cs.execute();
      int oParamData = cs.getInt(1); // proc output value
  } // runStoredProc4()
  

3.2 A Java DB Built-in System Procedure

SYSCS_UTIL.SYSCS_BACKUP_DATABASE is a pre-defined and Java DB specific system procedure. This backs up the database to a specified directory. The syntax is SYSCS_BACKUP_DATABASE(IN backupDirPath VARCHAR). The procedure does not return a value.

The following example SQL command invokes the procedure:

CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE('c:/backupdir');

Note: A detailed example Java code can be found in the blog post titled Backing up a Java DB Database at http://www.javaquizplayer.com/blogposts/blogpost4.html

4 NOTES

4.1 Some Advantages of Procedures over Client Code

• The routine allows the code to be stored once on the database server and can be accessed from multiple applications. Also, the code can be complex as compared to that of SQL.
• The code is executed on the server hence there is reduced network traffic in aclient-server application. This improves an application’s performance.

4.2 Other RDBMSs

Oracle’s 10g and HyperSQL DataBase (HSQLDB) are some of the other databases that support Java stored procedures.

5 References

• Apache Derby > Documentation (10.8 Manuals)
• Java SE 7 API > java.sql package

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

I agree to the Terms and Privacy Policy

Sign up

Thank you!

We will contact you soon.