Writing PostgreSQL functions with Java using PL/Java

Last Updated:  February 26, 2020 | Published: March 9, 2019

The PostgreSQL RDBMS offers a great variety for writing server-side functions and with PostgreSQL 11 now also stored procedures. PostgreSQL comes with a language named PL/pqSQL which enhances plain SQL with control flow predicates (similar to Oracle's PL/SQL). Besides this SQL-like language, PostgreSQL also offers built-in support for using Perl 5, Python and TCL as possible languages to implement functions and stored procedures. As a Java developer, I was always curious if there is a way to use Java within PostgreSQL.  While searching for a solution, I found PL/Java which is an open-source extension for PostgreSQL that allows stored procedures, triggers and functions to be written in Java.

In this blog, I'll show you how to install the third-party extension and how to write and deploy Java code as a PostgreSQL function. I'll be using PL/Java with version 1.5.2, OpenJDK 8, Debian 9PostgreSQL 9.6 and Maven 3.

Installing PL/Java via apt-get (for Debian &Ubtunu)

There are pre-built packages for Debian and Ubuntu once you enable the PostgreSQL Debian repository (follow the Quickstart section in the provided link). If you are running on Debian or Ubuntu and have the PostgreSQL repository enabled, you can simply install the PL/Java extension alongside the PostgreSQL server and skip the following example to build the extension manually:

As the PL/Java extension is not available as a prebuilt package for every operating system,  I'll demonstrate how to build it for your platform. If you are running on Debian or Ubuntu and installed the package via apt-get, you can jump to the Enabling the pljava extension.

Building PL/Java from source

The PL/Java source code is available on GitHub as well as the official documentation site. To build the extension, there are some prerequisites you have to meet on your database server. First of all, you need a JDK installed and Maven as the build tool. In addition, you need the C compiling and linking tools for your platform (in most cases gcc and g++). Furthermore, you have to provide some development packages for your PostgreSQL server version and for openssl. In the following you'll see all additional packages required for the Debian 9 slim docker image:

After installing all the required software packages, you can clone the Git repository, check out the PL/Java version of your choice and build the Maven project:

This step may take some time and will fail if you are missing any additional package. After Maven is finished, you'll find the required .jar file for your platform in the pljava-packaging/target folder. For my setup, this file is called: pljava -packaging/target/pljava-pg9.6-amd64-Linux-gpp.jar. You have to run this .jar file once with sufficient privilege to write in the PostgreSQL installation directories:

With these steps, you are now ready to create the pljava extension within PostgreSQL. 

Enabling the pljava extension

Either connect to your database with PgAdmin or the psql CLI tool and run the following SQL with the postgres superuser:

The first line is essential as this will tell PostgreSQL where to find the JVM on your machine. The path might differ for your operating system or JDK version/vendor. If you use the pljava extension from the Debian repository, this parameter already points to your default-java installation.

Furthermore, the GRANT USAGE statement is required for your database application user to have access to pljava.

Creating a custom Java based function

Now you are ready to write your functions with Java. Therefore create a simple Maven project with the following pom.xml:

The Maven project just needs the  pljava-apidependency which is part of building PL/Java in the previous step. Make sure you read the comment in the pom.xml above if you develop on another machine then the machine running your PostgreSQL server. Furthermore, I configured the maven-jar-plugin for a custom manifest configuration, which will be responsible for creating a  pljava.ddr  file during the Maven build step. I'll provide the use of this file later on.

With this setup, you can now create public Java classes which contain your PostgreSQL functions as static Java methods:

With the @Function annotation of the pljava-api dependency, we can configure the PostgreSQL function's metadata and mark the Java function to be deployed as a PostgreSQL function later on. For writing functions, you can use the standard Java data types as method arguments and return values (for more detailed information about passing data types between PostgreSQL and Java, read the official documentation for this topic).

For returning a list of values, you can use the Iterator<E>  interface:

Writing only functions like the two above would be boring if you can't access data within your database. Therefore you can create a JDBC connection within your function and connect to a default JDBC URLjdbc:default:connection. This is not a conventional JDBC connection, but rather direct access into the current PostgreSQL backend.

When you finished writing your Java methods, you can now package your Maven project with:

The result will contain the following files:

Applying the PL/Java function to the PostgreSQL database

The already mentioned pljava.ddr  file will now contain SQL instructions to automatically create your PostgreSQL functions when your .jar  is deployed to your database server:

The pljava extension comes with few utility functions that are installed into the sqlj schema: install_jar(), remove_jar(), replace_jar()Our Java functions can now be deployed with the install_jar() function:

The function takes the path of the .jar file, a mnemonic name to reference the .jar within the database and a boolean indicating whether or not the functions should be automatically deployed (based on the pljava.ddr  file). The path can be either absolute or relative to the server's current datadir directory.

You can now list all available functions within your database using psql:

and use the functions like any other PostgreSQL function:

You are now ready and deploy new, replace or remove old Java functions with PL/Java.

Please note: This is just a quick example to show you what's possible with PL/Java. For use in production make sure you read the documentation for potential adjustments. You can follow the official Hello World example in addition and read through the recommendation section for setting the appropriate VM options.

For a more detailed deep dive into PostgreSQL server-side programming, have a look at this excellent book: PostgreSQL 11 Server Side Programming Quick Start Guide: Effective database programming and interaction 

I've created a simple Dockerfile for you with everything already set up to try it on your machine. You can find in my GitHub repository with further instructions. Big thanks to jcflack for the feedback and adjustments for this blog post.

Have fun writing PL/Java functions,

Phil

>