Boost Your PostgreSQL Database with Java: Step-by-Step Installation and Usage

A Complete PL/Java Installation Guide

PL/Java is one of PostgreSQL’s programming language extensions. Compared to PL/pgSQL (PostgreSQL’s native procedural language), it offers object-oriented features specific to Java and supports both Java’s standard libraries and third-party libraries. Since Java is a cross-platform language, using PL/Java allows developers to easily leverage Java’s existing ecosystem, such as Apache Commons, Spring Framework, or other tools for processing data within the database.

PL/Java is a PostgreSQL extension that enables user-defined functions (UDFs) to be written in Java. Through PL/Java, users can execute Java code within PostgreSQL, providing more flexibility and extensibility in database operations.

1. Installation

You can use PostgreSQL version 16.

The latest tag of PL/Java is v1.6.7:

https://github.com/tada/pljava/tree/V1_6_7

Java 9 or higher is required. If you’re using Java 8, there may be compilation issues.

Once mvn and java are installed, you can start using PL/Java.

1.1 Installing PostgreSQL 16.3 from source

./configure --prefix=/home/admin/install/postgres --without-icu 
make -j8 install

Then, add the postgres/bin directory to the system path.

Now, initialize the database and start it:

initdb -D /usr/local/pgsql/data

Then:

psql -U postgres

File Browser

1.2 Installing PL/Java

mvn clean package

If you see all SUCCESS, the compilation was successful. Now, install the PostgreSQL extension:

cd pljava-packaging/target/
java -jar pljava-pg16.jar

If you see the following files installed in the PostgreSQL directory, the installation is complete:

[admin@i-7dwsk0ty target]$ java -jar pljava-pg16.jar
/home/admin/install/postgres/lib/libpljava-so-1.6.7.so as bytes
/home/admin/install/postgres/share/pljava/pljava-1.6.7.jar as bytes
/home/admin/install/postgres/share/pljava/pljava-api-1.6.7.jar as bytes
/home/admin/install/postgres/share/pljava/pljava-examples-1.6.7.jar as bytes
/home/admin/install/postgres/share/extension/pljava.control as lines (ASCII)
... too many lines omitted

1.3 Configuring the JVM

In the postgresql.conf file in the data directory, add the following line, then restart the database:

pljava.libjvm_location='/code/jdk-9.0.4/lib/server/libjvm.so'

2. Usage

Create the extension. If you see the following, it was successful:

postgres=# CREATE EXTENSION pljava;
CREATE EXTENSION

Now you can write a simple “Hello, world” example, such as:

package com.postgresql.pljava;

public class Hello {
    public static int test(Object data) throws Throwable {
        return 0;
    }
}

You can use the jar command to create a JAR file:

javac Hello.java
jar -cvf Hello.jar com/postgresql/pljava/Hello.class

Now, load and use the test function in psql:

postgres=# select sqlj.install_jar('file:///code/hashdata-lightning/Hello.jar', 'test', true);
 install_jar 
-------------
 
(1 row)

postgres=# select sqlj.set_classpath('public', 'test');
 set_classpath 
---------------
 
(1 row)
postgres=# CREATE OR REPLACE FUNCTION test(data anyelement)
RETURNS int
AS 'com.postgresql.pljava.Hello.test'
LANGUAGE java;
CREATE FUNCTION

Now, test it:

gpadmin=# select test(1);
 test 
-------
     0
(1 row)