The JVM Fanboy Blog 

Apache Derby part 3: Stored Procedures

by vincent


Posted on Tuesday Aug 04, 2015 at 12:54AM in Relational Database


In the previous part, we created a small database to store movie titles and the media type. In this part we will create and load a Stored Procedure into that database to make it really easy to add movies using the "ij" command-line tool with one simple statement.

First a warning. Most dedicated DBA's will probably hate you when you ask them to enable Java stored procedures support in their database. I don't think it's really considered common practice, but many enterprise-grade database systems offer stored procedures written in Java. I can think of many reasons not to do this, but there are probably cases where it can be a good solution. Since Derby is a database management system implemented fully in Java anyway, it's make a bit more sense here. There's a nice article on the official Oracle Nashorn team blog about this subject.

We will create a simple stored procedure that can be used to easily add a movie using a single CALL AddMovie('Movie Title', 'Media Name') statement. What it will do is simply look up the media record by name, then create the specified movie record. So it saves you from looking up the media id manually or using a subquery to do it with one query. I will never recommend to use stored procedures like this in an application (in this case for performance reasons, applications should look up the id of the media once and cache that), but if I'd plan to add many records manually using SQL and a command-line tool like "ij", then I'd seriously consider adding a utility stored procedure like this for convenience. That is, if the table contained more fields :)

Background info

Stored Procedures are normal static public methods in an ordinary public Java class. The class does not have to implement any interface or extend any other class and can reside in any package. After registering those methods inside Derby, those methods can be called directly inside the database using specific SQL statements.

PROCEDURE or FUNCTION

Derby supports two kind of stored procedures: PROCEDURE and FUNCTION types. A FUNCTION can return many pre-defined types and can be used in SQL statements, like UPDATE and INSERT INTO. This is unlike PROCEDURES, which are usually triggered via a CALL statement and therefore can not be used inside other SQL statements. While a PROCEDURE can return one or more ResultSet objects, those are not accessible at all when using the CALL statement.

Arguments

Most, but not all, standard Derby datatypes are supported for stored procedure parameters (VARCHAR, INT, LONGINT, DECIMAL...), but some that are not include CLOB, BLOB and XML (booo! :( ). Varargs (infinite parameters of a single type) are supported.

Each argument must be defined as IN, OUT or INOUT parameter type. Parameters defined as IN are input only (like usual Java parameters), OUT can be written to only (so can not used to pass input values to the stored procedure) and INOUT parameters can be used to both read data from and pass data to the caller of the stored procedure.

Java of course does not really know parameter types that can be written to, unlike C or even many modern BASICs that often have BYREF types, but those are cleverly emulated by Derby by passing single element arrays, which the stored procedure can use to replace the value of the first element.

The CALL statement, that we are going to use for our stored procedure example, only supports IN parameters.

Transaction management

Stored procedures should usually use Derby's current database connection, so that the stored procedure runs in the same transaction as the caller. The method can simply pass the "jdbc:default:connection" connection string to the standard JDBC DriverManager's getConnection method to get it.

Issuing COMMIT or ROLLBACK commands on this connection should be avoided, unless you have a very good reason to do so (but be aware that this is not possible at all in a FUNCTION stored procedure). There are some other limitations as well, for example a stored procedure is not allowed to modify data on a table that it was called on.

By not issuing COMMIT and ROLLBACK commands, the stored procedure will work as expected when the caller executes those commands after running the stored procedure.

Exceptions

Exceptions that are thrown by stored procedure methods are always wrapped inside a SQLException with hard-coded SQLState value of "38000", unless the stored procedure throws a SQLException itself with SQLState in the so called user-range "38001" to "38999". In the latter case the thrown exception is thrown as-is.

Loading and defining stored procedures

Stored pocedures can be put into JAR files that can be loaded directly by Derby and are available to one database schema only. Other possibilities are manually adding .class files to the Derby's classpath by adding a commandline option when starting Derby. In this example we're going to create and load a JAR file directly into the database. After loading the class or JAR file, the stored procedure must be registered by calling either CREATE PROCEDURE or CREATE FUNCTION statement.

Coding the AddMovie stored procedure

Enough talking, let's write some code. As reported previously, I'll be using NetBeans (for now exclusively) on this blog. I will be using Java 7 features, so you'll need at least JDK 7 (default for current version of NetBeans) or JDK 8 to compile this code.

I assume you have downloaded the latest Derby version manually and will not be using the JavaDB version (although, at the time of this writing, the current version of Derby was the same one as used in JDK 8's JavaDB).

  • Start NetBeans. From the "File" menu, choose "New Project..."

    Choose the "Java" category and "Java Application" project type.

    New Project (NetBeans IDE screenshot)

  • I've chosen the project name "DerbySPMovieUtils" and class name "eu.vanderleun.vincent.blog.DerbySPMovieUtils". Click "Finish"

    New Java Application (NetBeans IDE screenshot)

    (Note: ignore the paths in my screenshots, I am running a Developer edition of NetBeans that stores files in temporary directories)

  • Replace the automatically generated code with the code shown below.

    NetBeans IDE screenshot

  • If you require other settings, you should consult the documentation for the KVLite command-line options. I did not have a need to try them.


Here's the full code. To keep it short, I put the complete logic in 1 method. In production-grade applications, I probably would have created multiple private helper methods.

package eu.vanderleun.vincent.blog;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DerbySPMovieUtils {
    public final static String SQLSTATE_UNKNOWN_MEDIA = "38001";
    public final static String SQLSTATE_TOO_MANY_MEDIA = "38002";
    
    /* This method is called from within Derby. It uses a connection provided by Derby */
    public static void addMovie(String movie, String media) throws SQLException {
        Connection conn = DriverManager.getConnection("jdbc:default:connection");
        lookupMediaAndAddMovie(conn, movie, media);
    }
    
    /* This is an internal method. It is package-access, so it can easily be unit-tested */
    static void lookupMediaAndAddMovie(Connection conn, String movie, String media) throws SQLException {
        int mediaId;
        String sql = "SELECT id FROM media WHERE UPPER(name)=?";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, media.toUpperCase());
            try (ResultSet rsMedia = stmt.executeQuery()) {
                if (!rsMedia.next())
                    throw new SQLException("Media '" + media + "' not found", SQLSTATE_UNKNOWN_MEDIA);
                mediaId = rsMedia.getInt(1);
                if (rsMedia.next()) {
                    throw new SQLException("Multiple media '" + media + "' records found", SQLSTATE_TOO_MANY_MEDIA);                    
                }
            }
        }
        
        sql = "INSERT INTO movie (media, name) VALUES (?, ?)";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, mediaId);
            stmt.setString(2, movie);
            stmt.executeUpdate();
        }
    }    
}

Unit-testing the logic

Of course, we'd like to unit-test the method.

We'll need a Derby database connection for this. To obtain that, we must load a Derby database JDBC driver, but we do not want this dependency in the final JAR file that we will load into Derby, because that would cause conflicts with Derby's own internal classes. Luckily NetBeans IDE offers a simple solution, we can specify libraries that are loaded for unit-tests only.

  • Right-click the project name, choose "New" - "Other..."

    NetBeans IDE screenshot

  • Choose the "Unit Tests" category and "JUnit Test" project type.

    NetBeans IDE screenshot

  • Change the "Class Name": AddMovieTests and change the "Package" into "eu.vanderleun.vincent.blog". Then click " Finish".

    NetBeans IDE screenshot

    (Sorry for the change of appearance, I upgraded from Windows 8.1 to Windows 10 during the writing of this article)

  • A dialog may appear asking for the version number of JUnit to use. If so, choose "JUnit 4.x"

  • Before replacing the automatically generated code, let's load the Derby driver necessary for running the unit tests. Right-click the project name "DerbySPMovieUtils" and choose "Properties"

    NetBeans IDE screenshot

  • In the Properties window, on the left tree select "Libraries", and on the tab control on the right side of the window select the "Run Tests" tab. Click the "Add JAR/Folder" option.

    NetBeans IDE screenshot

  • Navigate to the directory where you extracted the Derby binary ZIP file. In this directory, choose the Lib subdirectory and finally find and double-click the "derby.jar" file, finally choose "OK" to close the Project Window. This ensures that Derby.jar is only added to the classpath when running the unit-tests and is not included when building the final production JAR file.

    NetBeans IDE screenshot

  • Replace the automatically generated code with the code shown below.

    NetBeans IDE screenshot

package eu.vanderleun.vincent.blog;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import static org.junit.Assert.*;

public class AddMovieTests {
    private Connection conn;
        
    @Before
    public void setUp() throws SQLException {
        System.out.println("Creating temporary database");
        String url = "jdbc:derby:memory:movieTestDB;create=true";
        conn = DriverManager.getConnection(url);
        
        try (Statement stmt = conn.createStatement()) {
            System.out.println("Creating media table...");
            stmt.executeUpdate("CREATE TABLE media (id INT NOT NULL GENERATED ALWAYS AS IDENTITY, name VARCHAR(32), CONSTRAINT media_pk PRIMARY KEY (id))");
            System.out.println("Creating movie table...");
            stmt.executeUpdate("CREATE TABLE movie (ID INT NOT NULL GENERATED ALWAYS AS IDENTITY, name VARCHAR(255) NOT NULL, media INT, CONSTRAINT movie_pk PRIMARY KEY (id), CONSTRAINT movie_media_fk FOREIGN KEY (media) REFERENCES media ON DELETE SET NULL ON UPDATE RESTRICT)");
            System.out.println("Adding dvd media...");
            stmt.executeUpdate("INSERT INTO media (name) VALUES ('DVD')");
        }        
    }
    
    @After
    public void tearDown() throws SQLException {
        System.out.println("Closing database\n");        
        String url = "jdbc:derby:memory:movieTestDB;drop=true";
        try {
            conn = DriverManager.getConnection(url);
        } catch (java.sql.SQLNonTransientConnectionException e) {
            // This traceback is expected
        }
        conn.close();
    }
    
    @Test
    public void testNonExistingMedia() throws SQLException {
        try {
            DerbySPMovieUtils.lookupMediaAndAddMovie(conn, "Movie title", "ducktape");
        } catch (SQLException e) {
             assertEquals(DerbySPMovieUtils.SQLSTATE_UNKNOWN_MEDIA, e.getSQLState());
             return;
        }
        fail("Media should not have been found");
    }
    
    @Test
    public void testTooManyMedia() throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            // Add DVD again, but now in lowercase
            stmt.executeUpdate("INSERT INTO media (name) VALUES ('dvd')");
            DerbySPMovieUtils.lookupMediaAndAddMovie(conn, "Future Force", "DVD");
        } catch (SQLException e) {
             assertEquals(DerbySPMovieUtils.SQLSTATE_TOO_MANY_MEDIA, e.getSQLState());
             return;
        }
        fail("Multiple media records should not have been accepted");        
    }
    
    @Test
    public void testAdd() throws SQLException {
        String movieTitle = "Great Movie";
        DerbySPMovieUtils.lookupMediaAndAddMovie(conn, movieTitle, "dvd");
        String sql = "SELECT count(1) FROM movie WHERE name=?";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, movieTitle);
            try (ResultSet rsMovie = stmt.executeQuery()) {
                rsMovie.next();
                assertEquals(1, rsMovie.getInt(1));                
            }
        }
    }    
}

  • Now run the unit-tests by making sure the cursor is on the unit-test code and pressing shift-F6. Is everything went smoothly, the unit-tests should now run successfully.

    NetBeans IDE screenshot

  • Study the unit-test code carefully. Note the connection string jdbc:derby:memory:movieTestDB;create=true and later one ending with drop=true. This creates and drops a database that is kept in memory only and not written to disk. This is a very handy Derby feature for creating small temporary databases. Note that a SQLNonTransientConnectionException exception is thrown when dropping the database. This is expected behavior and is a design choice of the Derby developers.

  • Build the final JAR file that we are going to load in Derby by right-clicking the project name and choosing "Clean and Build"

    NetBeans IDE screenshot

  • After a short while, a message appears in the status-bar telling that the build has completed.

Loading the JAR file into Derby

  • Open a terminal (Command) window, and with the CD command navigate to the NetBeans project directory, containing the project you just created. In Windows this is by default the "NetBeansProjects" directory in your "Documents" directory. Then go to the "Dist" subdirectory where the generated JAR file should be.

    Windows commandline screenshot

  • Find the Derby database you created in the previous part. If you do not have it anymore, just follow the instructions in that article to re-create the "derby_test" database.

  • Start the "ij" command-line tool (again, see previous article of this series for instructions)

  • Enter the four commands displayed below one by one. Of course, substitute c:\java\derby_databases\derby_test with your chosen path and/or filename.

    ij screenshot (Windows)

CONNECT 'jdbc:derby:c:\java\derby_databases\derby_test;create=false'; 

CALL SQLJ.INSTALL_JAR('DerbySPMovieUtils.jar', 'APP.MovieUtils', 0);

CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath', 'APP.MovieUtils');

CREATE PROCEDURE APP.AddMovie(IN MOVIE VARCHAR(255), IN MEDIA VARCHAR(32))  PARAMETER STYLE JAVA MODIFIES SQL DATA LANGUAGE JAVA EXTERNAL NAME 'eu.vanderleun.vincent.blog.DerbySPMovieUtils.addMovie';

Here's a summary of each commands

  • The first command simply connects the ij command-line tool to the database

  • The second command calls the built-in "SQLJ.INSTALL_JAR" stored procedure, that loads the JAR file into the database. The first parameter is the jar file path. The second parameter is the name that is used when referring this jar file. You are free to choose this name, in this case I chose "MovieUtils". You are expected to add a schema name before it, in this case I chose the "APP" schema, that I also used to store the movie and media tables. The third parameter is not used and the manual advises to always use 0 for now.

  • The third command adds the loaded JAR file to Derby's own class path

  • The fourth command registers the AddMovie method. This method only takes two VarChar input parameters and returns not any data. Since the stored procedure modifies data (it inserts a record), "MODIFIED SQL DATA" is specified. Note that this command has more options then used here, look them up in the Reference manual.

The stored procedure is now loaded and registered. Since the JAR file is stored inside the database, you can safely delete the JAR file from your file-system. If you want to delete the JAR file in the database, or overwrite your JAR file, you can use the "SQLJ.REMOVE_JAR", and "SQLJ.REPLACE_JAR" commands. You'll need the internal name (second parameter of the second command) for this to work correctly.

Now add some movies!

ij screenshot

CALL AddMovie('Zardoz', 'DVD');
CALL AddMovie('Mega Shark vs Mecha Shark', 'dvd');
CALL AddMovie('The Booth At The End: season 1', 'dvd');
CALL AddMovie('Trancers II: The Return Of Jack Deth', 'bluray');

Unfortunately it always displays 0 rows were added/updated/deleted. I didn't find a way to fix this, CALL seems to completely ignore the ResultSet that you can optionally return, so I did not bother to do this in this example. If I find a fix later, I'll update this article, but I currently suspect this is not possible.

What's next?

I plan to take a short break from Derby and write something about Oracle NoSQL when used from Oracle Nashorn JVM javascript engine. Then I want later continue this series by creating a Derby Table Function that reads data from that Oracle NOSQL database.

Also while I am reasonably happy with Apache Roller, the web-application that powers this blog, I am not really blown away by the design of this blog, so I want to spend some time creating my own theme from scratch.



No one has commented yet.
Comments are closed for this entry.