Core Java

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);
    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, 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:
    • DeterministicCharacteristic
    • MODIFIES SQL DATA (the default), CONTAINS SQL, READS SQL DATA, NO SQL (a procedure without any database related code) 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';

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);
    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)
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( {
            nextid = rs.getInt("ID");
        retval[0] = nextid;
        conn.close(); // alternative: shutdown the database.
    } // testProc4()
  • Create the procedure in the database.
        EXTERNAL NAME 'JavaStoredProcs.testProc4'

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


Note: A detailed example Java code can be found in the blog post titled Backing up a Java DB Database at


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

Prasad Saya

Prasad Saya is a software engineer with over ten years’ experience in application development, maintenance, testing and consulting on various platforms. He is also a certified Java and Java EE developer. At present his interest is in developing Java applications.
Notify of

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Newest Most Voted
Inline Feedbacks
View all comments
9 years ago

I need a step by step assistance on the technical aspects of the above. Assuming I have written my Java stored procedure and compiled it, WHERE do I place the class file??? I will use either ij or my java program to create the stored procedure in the database but my question is: where will the class file be? I am looking at this: CREATE PROCEDURE procedure-Name(ProcedureParameters)ProcedureElements Assuming I am executing the above from the ij command line tool, where will the class file with my stored procedures be? Please provide me with a step by step technical assistance on… Read more »

Prasad Saya
9 years ago
Reply to  Munashe

Thanks for reading the article.

Place the class file in the same directory as that of the Java DB database. Start ij, connect to the database and run the create procedure command. Verify if the procedure is created in the database. Invoke the procedure and verify results.

8 years ago

The article is helping & elaborated in a good manner. Can I get the implementation for getting alerts from DB if any table is altered/updated. A TRIGGER kindof thing.
Thanks in advance.

8 years ago
Reply to  Prasoon

Thanks for reading the article.

There are links to documentation on the 5 References section, and they are quite well detailed. I hope you will be able to find something useful there.

Edson Alves Pereira
Edson Alves Pereira
6 years ago

Hello, i´d like to create a stored procedure with a OUT Cursor parameter, is there any way to do that?

Prasad Saya
6 years ago

Hello Edson, thanks for reading my article. Your query is not clear to me. Java stored procedure has a parameter with OUT mode. The section “2.1.1 Create a Java Method” has some notes about the different modes and how they function. There are also detailed examples demonstrating the usage in the section “2.2 Using (Invoking)” – it has examples about invoking stored procedures which are created in the earlier mentioned section 2.1.1.

The section “3.1 Using a Non-nested Connection” has example code. The code includes creating the Java stored procedure and invoking it.

Hope this is helpful.


Back to top button