Java Stored Procedures with Derby

Stored procedures provide a very powerful capability. They enable the execution of many SQL statements within the context of conditional programming logic, via a single database call. Stored procedures execute in the database, thus making them an excellent place for computationally intense db operations. Stored procedures do come at cost. Some database vendors offer extensions to SQL e.g. PL/SQL, T-SQL, PL/pgSQL . These extensions may be proprietary or may comform to different standards. If your application is required to support a multitude of database flavors, it is likely that you must create separate stored procedures for each supported database. One of these standards is SQL/JRT. SQL/JRT defines a means to write stored procedures using the Java programming language and JDBC. Today’s topic is focused in this area.

Here’s what’s on tap:

1. Craft stored procedure logic using the Java programming language
2. Package and deploy the Java logic to Derby
3. Create a stored procedure that calls the Java logic.
4. Call the stored procedure using JDBC.

For the purpose of this tutorial we will use the following:
– Apache Derby Database 10.10. Get it here
– Java7

Other databases that currently support SQL/JRT are Oracle and IBM DB2.

Before we get started, let’s first:

A. Install and Configure Derby
B. Create a database and import sample data

A. Install and Configure Derby

see my post, ‘Quick Start with Apache Derby’.

B. Create a database and import sample data

First, create a file e.g. C:\sample.sql and add the following content:

[code language=”sql”]CREATE TABLE CUSTOMER (
ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
FIRSTNAME VARCHAR(255), LASTNAME VARCHAR(255),
PRIMARY KEY (ID)
);

INSERT INTO CUSTOMER VALUES(default, ‘John’, ‘Doe’);
INSERT INTO CUSTOMER VALUES(default, ‘Joe’, ‘Jones’);
INSERT INTO CUSTOMER VALUES(default, ‘Sally’, ‘Smith’);
[/code]

Lauch Derby’s interactive SQL scripting tool, ij, and create the sample database.

$DERBYHOME\bin\ij
ij>CONNECT 'jdbc:derby://localhost:1527/sample;create=true;user=APP;password=APP';


The above command, will create a new database named ‘sample’ (if ‘sample’ does not exist). It will also use the database schema, APP.

Next, import data into the sample database using sample.sql.

ij>RUN 'C:\sample.sql';

Finally, to validate the import, ensure the following statement results in three records.

ij>SELECT * FROM CUSTOMER;

1. Craft stored procedure logic using the Java programming language

Crafting stored procedure logic using Java is very similar to creating a regular Java class.
(When reading the source code below, pay close attention to any source code comments flagged as)

[code language=”java”]// IMPORTANT![/code]

First, create a new Java class, <code>SampleStoredProcedure.java</code>:

[code language=”java”]
package sample.sp;

public class SampleStoredProcedures {
// class body here
}
[/code]

Secondly, declare the method to contain our stored procedure logic. For the purpose if this tutorial, our stored procedure will simply fetch a customer’s last name, given that customer’s id.

[code language=”java”]
public class SampleStoredProcedures {
// This method will be registered to and invoked by the database
// IMPORTANT! It must be declared public static! This is required by SQL/JRT.
// IMPORTANT! OUT parameters must be declared as arrays, even if the expected result will always contain 1 element
public static void getCustomerLastNameById(long customerId /* IN paramater */,
String[] customerLastName /* OUT paramater */) throws SQLException {
// method body here
}
}
[/code]

Lastly, implement the method’s body.

[code language=”java”]

public static void getCustomerLastNameById(long customerId, String[] customerLastName) throws SQLException {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet res = null;

try {
// re-use the existing JDBC connection
// IMPORTANT! "jdbc:default:connection" tells the DriverManager to use the existing connection.
conn = DriverManager.getConnection("jdbc:default:connection");

// prepare the query
String sql = "SELECT LASTNAME FROM CUSTOMER WHERE ID = ?";
stmt = conn.prepareStatement(sql);
// bind parameters
stmt.setLong(1, customerId);
res = stmt.executeQuery();

// set the result in OUT parameter
// IMPORTANT: Notice that we never instantiate the customerLastName array.
// The array is instead initialized and passed in by Derby, our SQL/JRT implementor
customerLastName[0] = (res.next()) ? res.getString(1) : "Customer not Found.";
} finally {
if (res != null) {
// close the result set
res.close();
}

if (stmt != null) {
// close the statement
stmt.close();
}

if (conn != null) {
// close the db connection
conn.close();
}
}
}
[/code]

2. Package and deploy the Java logic to Derby

Before we deploy, our new Java stored procedure method with Derby, we must first package our SampleStoredProcedures.class in a Jar file. Let’s call our Jar file,  SampleStoredProcedures.jar and save it to e.g. c:\myjars.
Note: If you are unfamiliar with Jar files, Oracle has a nice tutorial here.

Next, launch Derby’ ij tool e.g. $DERBY_HOME/bin/ij from the command line

First, install our Jar file into Derby.
ij>CALL sqlj.install_jar( 'c:/myjars/SampleStoredProcedures.jar', 'APP.SampleStoredProcedures',0);
Note: APP refers to, the Derby user, APP’s schema.

To replace the installed Jar with a new version:
ij>CALL sqlj.install_jar( 'c:/myjars/SampleStoredProcedures.jar', 'APP.SampleStoredProcedures',0);

Next, update Derby’s classpath to include our Jar file.
ij>ALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath','APP.SampleStoredProcedures');

3. Create the stored procedure to call our Java logic.

Continue using $DERBY_HOME/bin/ij. We will now create the actual stored procedure using the following SQL statement:

ij>CREATE PROCEDURE GETCUSTOMERLASTNAME(STREAM_ID BIGINT, OUT NAME VARCHAR(255)) LANGUAGE JAVA EXTERNAL PARAMETER STYLE JAVA ‘samplestoredprocedures.SampleStoredProcedures.getCustomerLastName’;

Below is the CREATE PROCEDURE SQL statement in a more sane format.

[code language=”sql”]
CREATE PROCEDURE GETCUSTOMERLASTNAME(STREAM_ID BIGINT,
OUT NAME VARCHAR(255))
LANGUAGE JAVA
EXTERNAL
PARAMETER STYLE JAVA
‘samplestoredprocedures.SampleStoredProcedures.getCustomerLastName’;
[/code]

4. Call the stored procedure via JDBC
Finally, we can call our stored procedure. Unfortunately, because our stored procedure has an OUT parameter, we cannot simply invoke it via ij, using the SQL CALL statement. e.g <code>ij>CALL GETCUSTOMERNAME(....</code> Instead, we will use the following simple JDBC client.

[code language=”java”]
public class TestDriver {

public static void main (String[] args) throws Exception {
Connection conn = null;
CallableStatement stmt = null;
try {
// IMPORTANT! The Derby driver e.g. derbyclient.jar must be on the classpath
Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();

// Connect to the database
conn = DriverManager
.getConnection("jdbc:derby://localhost:1527/gc;user=APP;password=APP");

// Create the CALL statement
stmt = conn.prepareCall("CALL GETCUSTOMERLASTNAME( ?, ? )");
// Bind the customer id to the first parameter
stmt.setLong(1, Long.parseLong(args[0]));
// Register the second parameter as an OUT parameter
stmt.registerOutParameter(2, Types.VARCHAR);

stmt.execute();

// Print result in the OUT parameter – e.g. the customer’s last name
System.out.println(stmt.getString(2));
} finally {
// Release db resources
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
}
}
}
[/code]

Thank you!