How to Use the PL/Java Language in PostgreSQL

When I first heard that PostgreSQL could run Java code directly inside the database server, my initial reaction was skepticism — Java inside a database process sounds heavyweight. But after actually working with PL/Java on a project that needed to reuse an existing Java business-logic library from inside stored procedures, I came away genuinely impressed by how well-integrated it is. PL/Java lets me write PostgreSQL functions, triggers, and even custom aggregates using standard Java code, compiled to bytecode and executed inside an embedded Java Virtual Machine that runs alongside the PostgreSQL backend process.

In this guide, I’ll walk through what PL/Java is, how to install and configure it, how to write and deploy functions, how parameters and return types map between SQL and Java, common real-world use cases, and the troubleshooting steps and best practices I rely on when working with it.

What Is PL/Java?

PL/Java is a trusted procedural language extension for PostgreSQL that embeds a Java Virtual Machine inside the PostgreSQL server process. It allows me to write functions, triggers, and user-defined aggregates in Java, package them into a .jar file, and load that jar directly into the database. From that point on, I can call Java methods as if they were native SQL functions.

Unlike PL/pgSQL, which is designed purely for procedural SQL logic, PL/Java gives me the entire Java standard library and any additional libraries I bundle into my jar. This means access to Java’s networking stack, cryptography libraries, XML/JSON parsing, and any custom business logic already written in Java by my organization.

PL/Java is maintained as an open-source project and has a fairly active community relative to some of the other alternative PL languages, in large part because Java shops tend to have significant existing codebases they want to reuse rather than rewrite.

Why Choose PL/Java?

There are specific situations where PL/Java genuinely earns its complexity:

• Reusing existing Java business logic. If my organization already has domain logic — say, tax calculation rules or complex scoring algorithms — written and tested in Java, PL/Java lets me expose that same code as a database function without a rewrite.
• Access to the Java ecosystem. Libraries for cryptography, date/time handling, JSON processing, and numerical computation are often more mature and better tested in Java than what’s available natively in PL/pgSQL.
• Strong typing and tooling. Java’s static typing, combined with mature IDE support, makes large or complex stored procedure logic easier to maintain and refactor than long PL/pgSQL blocks.
• Custom aggregates with complex state. Java’s object-oriented model makes it comparatively easy to build aggregate functions that need to track complicated intermediate state.

Installing PL/Java

Getting PL/Java running requires a Java Development Kit (JDK) on the server, along with the PL/Java extension itself, which is typically distributed as a jar plus a small native binding library.

On a Debian/Ubuntu system:

# Install a JDK (PL/Java requires a compatible JDK version for your PostgreSQL version)
sudo apt-get install openjdk-17-jdk

# Install PostgreSQL server development headers
sudo apt-get install postgresql-server-dev-16

# Install PL/Java (some distros package it directly)
sudo apt-get install postgresql-16-pljava

If a prebuilt package isn’t available for my distribution, I download the PL/Java installer jar from the official project and run it with java -jar pljava-pg16-x86_64-Linux-gcc.jar, which walks through an interactive install process and writes the necessary configuration.

After installation, I need to tell PostgreSQL where the JVM library lives, usually in postgresql.conf:

pljava.libjvm_location = '/usr/lib/jvm/java-17-openjdk-amd64/lib/server/libjvm.so'

Then I restart PostgreSQL and create the extension inside my target database:

CREATE EXTENSION pljava;

I can verify it loaded correctly with:

SELECT pljava_version();

Writing a Basic PL/Java Function

Unlike PL/pgSQL, where I write function bodies inline in SQL, PL/Java functions are written as regular Java methods, compiled, packaged into a jar, and then mapped to SQL function signatures.

Step 1: Write the Java Class

package com.example.dbfunctions;

public class MathFunctions {
    public static int addNumbers(int a, int b) {
        return a + b;
    }
}

Step 2: Compile and Package

javac -d build com/example/dbfunctions/MathFunctions.java
jar cf mathfunctions.jar -C build .

Step 3: Deploy the Jar to PostgreSQL

SELECT sqlj.install_jar('file:///path/to/mathfunctions.jar', 'mathfunctions', true);
SELECT sqlj.set_classpath('public', 'mathfunctions');

Step 4: Create the SQL Function Mapping

CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER
AS 'com.example.dbfunctions.MathFunctions.addNumbers'
LANGUAGE java;

Now I can call it just like any built-in function:

SELECT add_numbers(5, 9);

Parameter and Return Type Mapping

PL/Java maps PostgreSQL types to Java types in a fairly intuitive way:

PostgreSQL Type	Java Type
integer	int / Integer
bigint	long / Long
text / varchar	String
boolean	boolean / Boolean
numeric	java.math.BigDecimal
timestamp	java.sql.Timestamp
bytea	byte[]
arrays	java.sql.Array

Example: Working with Text and Nulls

public static String formatGreeting(String name) {
    if (name == null) {
        return "Hello, stranger!";
    }
    return "Hello, " + name.trim() + "!";
}

CREATE OR REPLACE FUNCTION format_greeting(name TEXT)
RETURNS TEXT
AS 'com.example.dbfunctions.MathFunctions.formatGreeting'
LANGUAGE java;

Returning Sets

To return multiple rows, I implement java.sql.ResultSet-based iteration using ResultSetProvider or SETOF returning functions with an iterator pattern:

import org.postgresql.pljava.ResultSetProvider;
import java.sql.ResultSet;
import java.sql.SQLException;

public class EvenNumbers implements ResultSetProvider {
    private final int max;

    public EvenNumbers(int max) {
        this.max = max;
    }

    public static ResultSetProvider listEvenNumbers(int max) {
        return new EvenNumbers(max);
    }

    public boolean assignRowValues(ResultSet receiver, int currentRow) throws SQLException {
        int value = currentRow * 2;
        if (value > max) {
            return false;
        }
        receiver.updateInt(1, value);
        return true;
    }

    public void close() {}
}

CREATE OR REPLACE FUNCTION list_even_numbers(max_val INTEGER)
RETURNS SETOF INTEGER
AS 'com.example.dbfunctions.EvenNumbers.listEvenNumbers'
LANGUAGE java;

Writing Trigger Functions in PL/Java

Trigger functions in PL/Java implement the org.postgresql.pljava.TriggerData interface indirectly through special method signatures. Here’s an example that normalizes an email column before insert:

import java.sql.ResultSet;
import java.sql.SQLException;
import org.postgresql.pljava.TriggerData;

public class EmailTrigger {
    public static void normalizeEmail(TriggerData td) throws SQLException {
        ResultSet newRow = td.getNew();
        String email = newRow.getString("email");
        if (email != null) {
            newRow.updateString("email", email.trim().toLowerCase());
        }
    }
}

CREATE OR REPLACE FUNCTION normalize_email()
RETURNS trigger
AS 'com.example.dbfunctions.EmailTrigger.normalizeEmail'
LANGUAGE java;

CREATE TRIGGER trg_normalize_email
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION normalize_email();

Querying the Database from Java

PL/Java functions can run SQL queries using standard JDBC, connecting through a special in-process JDBC driver:

import java.sql.*;

public static java.math.BigDecimal getOrderTotal(int orderId) throws SQLException {
    Connection conn = DriverManager.getConnection("jdbc:default:connection");
    PreparedStatement stmt = conn.prepareStatement(
        "SELECT SUM(price * quantity) AS total FROM order_items WHERE order_id = ?"
    );
    stmt.setInt(1, orderId);
    ResultSet rs = stmt.executeQuery();
    java.math.BigDecimal total = java.math.BigDecimal.ZERO;
    if (rs.next()) {
        total = rs.getBigDecimal("total");
    }
    rs.close();
    stmt.close();
    return total;
}

Using PreparedStatement with bound parameters, exactly like standard JDBC code, keeps this safe from SQL injection — I never build queries with raw string concatenation here.

Common Use Cases

From what I’ve seen in real deployments, PL/Java tends to show up in:

1. Financial and scoring calculations where an organization already has a well-tested Java library implementing complex business rules.
2. Cryptographic operations using Java’s built-in javax.crypto package for hashing or encryption directly at the data layer.
3. Custom aggregates that need to accumulate complex intermediate state, such as running statistical calculations.
4. Data validation against external schemas, using Java XML or JSON libraries to validate structured data before it’s persisted.
5. Integration bridges in organizations migrating logic between a Java application tier and the database tier.

Troubleshooting Common Issues

“cannot find libjvm.so” errors on startup This means PostgreSQL can’t locate the JVM shared library. I double-check the pljava.libjvm_location setting matches the exact path for the JDK installed, which varies by distro and JDK vendor. Running find / -name "libjvm.so" helps track down the correct path quickly.

ClassNotFoundException when calling a function This usually means the classpath wasn’t set correctly, or the jar wasn’t installed into the right schema. I re-check sqlj.set_classpath() and confirm the exact fully qualified class name matches what I used in the CREATE FUNCTION statement, including case sensitivity.

High memory usage per connection Because each backend process that uses PL/Java loads its own JVM instance, memory usage per connection can be noticeably higher than PL/pgSQL. I mitigate this with connection pooling (via PgBouncer or similar), so I’m not spinning up a fresh JVM per short-lived connection.

Slow first call after server restart JVM startup and class loading add latency to the first PL/Java call after a restart or new connection. This is expected behavior, and I account for it in warm-up scripts if the workload is latency-sensitive.

Security exceptions in trusted mode PL/Java’s trusted language enforces a security policy that restricts filesystem and network access by default. If a function needs broader permissions, I either grant explicit permissions through the Java security policy file, or use the untrusted javau language variant, restricted to superusers.

Best Practices

• Reuse, don’t reinvent. PL/Java shines brightest when I’m reusing existing, well-tested Java code rather than writing brand-new logic from scratch inside the database.
• Keep jars lean. Bundling unnecessary dependencies into the deployed jar increases classloading time and memory footprint per connection.
• Always use PreparedStatement. Never concatenate SQL strings when querying from Java functions.
• Pool connections aggressively. Given the JVM startup cost per backend, connection pooling makes a much bigger performance difference with PL/Java than it does with lighter procedural languages.
• Version and test jars like any other software artifact. I keep jar builds in CI/CD, with unit tests run against the Java classes independently of PostgreSQL before deployment.
• Monitor JVM memory settings. I tune pljava.vmoptions (e.g., -Xmx and -Xms) explicitly rather than relying on JVM defaults, since default heap sizing can be poorly suited to a database server context.
• Separate business logic from data access. I keep pure business logic in plain Java classes, and isolate JDBC/database access into a thin separate layer, which keeps the code testable outside PostgreSQL entirely.

Comparing PL/Java to Other Procedural Languages

Whenever I’m deciding between PL/Java and the alternatives, a few tradeoffs consistently come up.

Against PL/pgSQL, PL/Java loses on startup latency and memory footprint per connection, since spinning up a JVM is heavier than PL/pgSQL’s native, always-warm execution model. But PL/Java wins decisively when the logic genuinely needs Java’s type safety, tooling, or an existing Java library — trying to replicate a mature Java cryptography or statistics library in hand-written PL/pgSQL would be a significant, error-prone undertaking.

Against PL/Python, the comparison usually comes down to what ecosystem the organization already has investment in. Python’s data science and general-purpose scripting libraries are often more accessible to a broader range of developers, while Java’s strengths lie in large, well-tested enterprise codebases, strict typing, and performance-critical numeric code that benefits from JIT compilation once warmed up.

Against PL/Perl, there’s very little overlap in practice — PL/Perl is chosen for lightweight text processing, while PL/Java is chosen for substantial, structured business logic reuse. I rarely see a project seriously weighing one against the other.

Security Considerations

PL/Java’s trusted language variant (java, as opposed to the untrusted javau) enforces a Java security policy that restricts what compiled code can do — no arbitrary filesystem access, no arbitrary network sockets, unless explicitly permitted. This is a meaningfully different security model from PL/Python, which is untrusted-only in PostgreSQL, and it’s one of PL/Java’s genuine advantages for multi-tenant or shared database environments where I want to let more roles create functions without granting broad system access.

That said, a few things I always check:

Security policy configuration. The trusted sandbox is only as strong as the Java security policy file backing it. I review pljava.policy (and any custom policy grants) carefully before assuming a function is safely sandboxed, especially after any PL/Java version upgrade, since default policies can change between releases.

Untrusted mode (javau) is superuser-only for good reason. Just like plpython3u or plperlu, granting access to the untrusted Java language variant is equivalent to granting broad code-execution capability on the server host. I never extend this beyond a small, trusted set of database administrators.

JDBC connection scope. The special jdbc:default:connection URL used inside PL/Java functions ties database access to the same session and transaction as the calling context, which is generally safe, but I still apply the same parameterized-query discipline as I would with any other JDBC code, since building SQL strings via Java string concatenation is just as risky here as anywhere else.

Performance Tuning Tips

• Tune JVM heap size deliberately. The pljava.vmoptions setting controls JVM startup flags like -Xmx and -Xms. Under-provisioning causes excessive garbage collection pauses under load; over-provisioning wastes memory across many concurrent backend processes, since each one gets its own JVM instance.
• Pool connections aggressively. Because each new database connection using PL/Java pays a JVM initialization cost, connection pooling (through PgBouncer or a similar tool) has an outsized impact on PL/Java performance compared to lighter-weight procedural languages.
• Avoid excessive classloading. Keeping jars lean and minimizing the number of distinct classes loaded per function call reduces both memory footprint and classloading latency, which matters most on the first call in a fresh backend process.
• Benchmark JIT warm-up behavior. Java’s just-in-time compilation means code often runs faster after being called repeatedly within the same JVM instance. For short-lived connections, this benefit is mostly lost, which is another argument in favor of connection pooling and longer-lived backend processes for PL/Java-heavy workloads.

Frequently Asked Questions

Can I use Maven or Gradle to manage PL/Java project dependencies? Yes — I build the jar using whatever standard Java build tooling I already use, including bundling third-party dependencies into a “fat jar” if needed, then deploy the final compiled jar to PostgreSQL using sqlj.install_jar(). The build process itself is entirely independent of PostgreSQL.

Does PL/Java support annotations for simplified deployment? Yes — PL/Java includes an annotation-based deployment mechanism (@Function, @Trigger, and related annotations) that can generate the SQL deployment descriptor automatically at build time, which I find considerably more maintainable than hand-writing CREATE FUNCTION mappings for large projects with many Java-backed functions.

How does PL/Java handle exceptions? Java exceptions thrown inside a PL/Java function propagate back to PostgreSQL as SQL errors, and I can catch and translate them explicitly within my Java code if I want more control over the SQLSTATE or error message returned to the calling SQL client.

Final Thoughts

PL/Java is one of the more powerful procedural language options available for PostgreSQL, specifically because it opens the door to the entire Java ecosystem from inside the database. I’ve found it most valuable in organizations that already have significant Java infrastructure and want to avoid duplicating business logic between the application and database layers. The tradeoff is added operational complexity — JVM configuration, memory tuning, and classpath management aren’t things I have to think about with PL/pgSQL.

If I’m working somewhere with a strong Java presence and a genuine need to run that logic close to the data, PL/Java is a mature, well-supported choice. If I’m starting fresh without that constraint, I’d still default to PL/pgSQL for most stored procedure work and reserve PL/Java for the specific cases where reusing existing Java code delivers real value.