The PL/Java package provides access to Java stored procedures, triggers, and functions via the JDBC interface. Unless otherwise noted, the commands and paths noted in the following section assume that you have performed an installation with the edb-asxx-pljava RPM package where xx is the Advanced Server version number.

Before installing PL/Java for use with a standard Java virtual machine (JVM) on a Linux system, you must first confirm that a Java runtime environment (version 1.8) is installed on your system. Installation of a Java development kit also provides a Java runtime environment.

Installing PL/Java on Linux

The following steps outline the process of installing PL/Java on a Linux system:

Step 1: Edit the postgresql.conf file located under the data directory of your Advanced Server installation and add (or modify) the following settings:

pljava.classpath = 'path_to_pljava.jar'
pljava.libjvm_location = 'path_to_libjvm.so'

Where path_to_pljava.jar specifies the location of the pljava.jar file and path_to_libjvm.so specifies the location of the libjvm.so file.

For example, the following lists the paths for a default installation with Java version 1.8:

pljava.classpath = '/usr/edb/as11/share/pljava/pljava-1.5.0.jar'
pljava.libjvm_location = '/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.91-1.b14.el6.x86_64/jre/lib/amd64/server/libjvm.so'

Step 2: Restart the database server.

Step 3: You can use the CREATE EXTENSION command to install PL/Java. To install the PL/Java extension, login to the database in which you want to install PL/Java with the psql or pgAdmin client, and invoke the following command:

CREATE EXTENSION pljava;

Step 4: To confirm that PL/Java is installed, invoke the following command:

SELECT * FROM pg_language WHERE lanname LIKE 'java%';

The edb-psql client displays two rows indicating that java and javau (Java Untrusted) have been installed in the database.

edb=# SELECT * FROM pg_language WHERE lanname LIKE 'java%';
lanname  | lanowner | lanispl | lanpltrusted | lanplcallfoid | laninline | lanvalidator | lanacl
---------+----------+---------+--------------+---------------+-----------+--------------+-------------------------------
java     | 10       | t       | t            | 16462         | 0         | 0            | {enterprisedb=U/enterprisedb}
javau    | 10       | t       | f            | 16463         | 0         | 0            |
(2 rows)

Installing PL/Java on Windows

The following steps outline the process of installing PL/Java on a Windows system.

Step 1: Edit the postgresql.conf file and add (or modify) the following settings:

pljava.classpath = 'POSTGRES_INSTALL_HOME\lib\pljava.jar'
pljava.libjvm_location = 'path_to_libjvm.so'

Where POSTGRES_INSTALL_HOME specifies the location of the Advanced Server installation. For example, the following is the configuration setting for a default installation:

pljava.classpath = 'C:\Program Files\edb\as11\lib\pljava.jar'

Step 2: Restart the database server.

Step 3: Modify the PATH setting used by the server, adding the following two entries:

%JRE_HOME%\bin;%JRE_HOME%\bin\client

Where JRE_HOME specifies the installation directory of your Java runtime environment. If you have a Java development kit, substitute the location of $JDK_HOME/jre for JRE_HOME.

Step 4: Use the Postgres CREATE EXTENSION command to install PL/Java. To run the installation script, use the psql or pgAdmin client to connect to the database in which you wish to install PL/Java and invoke the following command:

CREATE EXTENSION pljava;

Step 5: To confirm that PL/Java is installed, invoke the following command:

SELECT * FROM pg_language WHERE lanname LIKE 'java%';

The client will return a result set that includes java and javau (Java Untrusted).

Using PL/Java

To create a PL/Java program, you must first create a Java class that contains at least one static method, and then you must compile that class into a .class or .jar file. Next, you declare the Java function within SQL using the CREATE FUNCTION command. The CREATE FUNCTION command gives a SQL name to the function and associates the compiled class (and method name) with that function name.

For example, the following CREATE FUNCTION statement creates a function named getsysprop:

CREATE FUNCTION getsysprop(VARCHAR)
RETURNS VARCHAR
AS 'java.lang.System.getProperty'
LANGUAGE java;

When invoked, getsysprop will execute the getProperty (static) method defined within the java.lang.System class.

SELECT getsysprop('user.home');

 getsysprop
---------------
 /usr/edb/as11
(1 row)

The example that follows demonstrates the procedures used to create and install a simple HelloWorld program:

Step 1: Save the following code sample to a file named HelloWorld.java:

package com.mycompany.helloworld; 
public class HelloWorld 
{ 
    public static String helloWorld() 
    { 
      return "Hello World"; 
    } 
}

Step 2: Compile the file.

$ javac HelloWorld.java

And save it in a folder hierarchy as:

com/mycompany/helloworld/HelloWorld.class

Step 3: Create an archive file (a JAR file) named helloworld.jar:

jar cf helloworld.jar com/mycompany/helloworld/HelloWorld.class

Step 4: Open the edb-psql client, and install the jar file with the following command:

SELECT sqlj.install_jar('file:///file_path/helloworld.jar', 'helloworld', true);

Where file_path is the directory containing the helloworld.jar file. For example, if the /tmp directory is the file_path:

SELECT sqlj.install_jar('file:///tmp/helloworld.jar', 'helloworld', true);

To confirm that the jar file has been loaded correctly, perform a SELECT statement on the sqlj.jar_entry and sqlj.jar_repository tables.

edb=# SELECT entryid, entryname FROM sqlj.jar_entry; 
 entryid | entryname 
---------+------------------------------------------- 
     1   | com/mycompany/helloworld/HelloWorld.class 
(1 row) 
   
edb=# SELECT jarid, jarname, jarorigin, jarowner FROM sqlj.jar_repository; 
 jarid | jarname    | jarorigin                  | jarowner 
-------+------------+----------------------------+--------------
    4  | helloworld | file:///tmp/helloworld.jar | enterprisedb 
(1 row)

Step 5: Set the classpath as:

edb=# SELECT sqlj.set_classpath('public', 'helloworld');

The sqlj.classpath_entry table will now include an entry for the helloworld class file.

edb=# SELECT * FROM sqlj.classpath_entry; 
 schemaname | ordinal | jarid 
------------+---------+------- 
 public     | 1       | 4 
(1 row)

Step 6: Create a function that uses Java to call the static function declared in the jar file:

CREATE OR REPLACE FUNCTION helloworld() 
RETURNS "varchar" 
AS 
     'com.mycompany.helloworld.HelloWorld.helloWorld' 
LANGUAGE 'java' VOLATILE;

Step 7: Execute the function:

edb=# SELECT * FROM helloworld();

You should see the output:

helloworld 
-------------
 Hello World 
 (1 row)

The official PL/Java distribution is distributed with examples and documentation. For more information about using PL/Java, see the project page at:

https://github.com/tada/pljava/wiki

• On this page
• Installing PL/Java on Linux
• Installing PL/Java on Windows
• Using PL/Java