Skip to content
ParticleDB
Release candidateParticleDB 1.1.19-rc.17 is not yet GA or ready for production data.Release status →

Java and JDBC

Use the configured address for your client. See connection addresses and security. The examples use the SQL client address unless they explicitly select the HTTP API or ParticleDB JDBC driver.

JVM applications can connect to ParticleDB in two ways:

ParticleDB JDBC driver pgjdbc
Coordinates ai.particledb:particledb-jdbc:1.0.12 (Maven Central) org.postgresql:postgresql
Protocol / port ParticleDB native client, 7173 SQL client interface, 5432
URL jdbc:particledb://host:7173/particledb jdbc:postgresql://host:5432/particledb
TLS supported supported
Savepoints supported supported

The ParticleDB Java SDK, ai.particledb:particledb-java-sdk:1.1.3 on Maven Central (MIT), is built on this driver and depends on particledb-jdbc. Java SDK 1.0.0 was published under Apache-2.0 and is unchanged.

The examples on this page connect as postgres with the password secret, for a server started like this:

Terminal window
particledb start --data-dir ~/.particledb/data --auth-method scram-sha-256 --pg-password secret
Driver class org.particledb.Driver (registered automatically)
URL jdbc:particledb://host[:port][/database][?option=value&...]
Server port 7173, the database port; it is the default when the URL has no port
Java 11+, no JVM flags
License MIT

Versions 1.0.0 to 1.0.8 were published under Apache-2.0 and are unchanged on Maven Central; 1.0.10 and later are MIT. Use them with server 1.1.19-rc.13 or later. See release and client availability for version-specific status.

<dependency>
<groupId>ai.particledb</groupId>
<artifactId>particledb-jdbc</artifactId>
<version>1.0.12</version>
</dependency>

None, on any JDK from 11 on. The driver decodes every result type itself, including numeric, date, time, timestamp, interval and arrays; Apache Arrow Java is not a dependency. If your application still passes --add-opens=java.base/java.nio=ALL-UNNAMED from an earlier release, you can remove it.

The optional resultDecoder setting (URL option, Properties entry or PdbDataSource.setResultDecoder) selects the decoder. The default, auto, and builtin use the driver’s own reader. arrow uses Apache Arrow Java, which you then add to your application yourself, together with the JVM option above on JDK 16 and later.

import java.sql.*;
import java.util.Properties;
public class Hello {
public static void main(String[] args) throws Exception {
Properties props = new Properties();
props.setProperty("user", "postgres");
props.setProperty("password", "secret");
try (Connection c = DriverManager.getConnection(
"jdbc:particledb://127.0.0.1:7173/particledb", props);
Statement s = c.createStatement();
ResultSet rs = s.executeQuery("SELECT 1")) {
while (rs.next()) {
System.out.println(rs.getInt(1));
}
}
}
}
try (Statement s = c.createStatement()) {
s.execute("CREATE TABLE users (id INT PRIMARY KEY, name TEXT, score DOUBLE PRECISION)");
}
try (PreparedStatement ins = c.prepareStatement(
"INSERT INTO users (id, name, score) VALUES (?, ?, ?)")) {
ins.setInt(1, 1); ins.setString(2, "alice"); ins.setDouble(3, 92.5);
ins.executeUpdate(); // 1
}
try (PreparedStatement sel = c.prepareStatement(
"SELECT name, score FROM users WHERE id = ?")) {
sel.setInt(1, 1);
try (ResultSet rs = sel.executeQuery()) {
while (rs.next()) {
System.out.printf("name=%s score=%.2f%n", // name=alice score=92.50
rs.getString("name"), rs.getDouble("score"));
}
}
}

Pass options as JDBC Properties, as URL options (?user=app&password=...), or through PdbDataSource setters. user:password@host in the URL is not supported. URL option values are used verbatim (no percent-decoding), so a password containing &, =, or ? must go through Properties.

Option Default Description
user not set Login role
password not set Password for user
connectTimeout 30000 TCP + handshake timeout in milliseconds
maxStreamsPerConn 64 Concurrent requests multiplexed on one TCP connection
prepareThreshold 5 Executions before a statement is prepared on the server
sslMode disable disable, require, verify-ca or verify-full; see TLS
sslRootCert not set PEM CA certificate for verify-ca and verify-full
resultDecoder auto auto or builtin (the driver’s reader), or arrow; see JVM flags
raiseExceptionOnSilentRollback false When true, commit() of a transaction the server rolled back (because a statement in it failed) throws SQLException with SQLSTATE 25P02 instead of returning normally

The driver authenticates during getConnection():

Server Credentials set Result
requires auth yes connects; a wrong password throws SQLException with SQLSTATE 28P01
requires auth no SQLException with SQLSTATE 28000
started with --no-auth either connects

Set sslMode to require (encryption), verify-ca (certificate chain) or verify-full (chain and hostname), and sslRootCert to the CA certificate. Use verify-full for remote connections. sslCert and sslKey accept a PEM client certificate and PKCS#8 private key. A TLS request never falls back to plaintext.

Properties props = new Properties();
props.setProperty("user", "postgres");
props.setProperty("password", "secret");
props.setProperty("sslMode", "verify-full");
props.setProperty("sslRootCert", "/path/to/ca.pem");
try (Connection c = DriverManager.getConnection(
"jdbc:particledb://db.example.com:7173/particledb", props)) {
// ...
}

The server enables TLS with --tls-cert and --tls-key. See TLS.

import org.particledb.ds.PdbDataSource;
PdbDataSource ds = new PdbDataSource();
ds.setHost("127.0.0.1");
ds.setPort(7173);
ds.setDatabase("particledb");
ds.setUser("postgres");
ds.setPassword("secret");
try (Connection c = ds.getConnection()) {
// ...
}

The driver has no built-in pool; use HikariCP:

HikariConfig cfg = new HikariConfig();
cfg.setJdbcUrl("jdbc:particledb://127.0.0.1:7173/particledb");
cfg.setUsername("postgres");
cfg.setPassword("secret");
cfg.setMaximumPoolSize(10);
try (HikariDataSource ds = new HikariDataSource(cfg);
Connection c = ds.getConnection()) {
// ...
}

Choose the pool size from your application concurrency and the server connection limit. Measure connection wait time before increasing it.

c.setAutoCommit(false);
try (Statement s = c.createStatement()) {
s.executeUpdate("INSERT INTO users VALUES (2, 'bob', 80.0)");
c.commit(); // or c.rollback() to discard
} finally {
c.setAutoCommit(true);
}
  • New connections run at the server’s default isolation level, READ COMMITTED. getTransactionIsolation() reports it, and setTransactionIsolation() changes it.
  • Run schema changes outside application transactions. See schema changes for the supported boundary.
  • A failed statement aborts the transaction, as in PostgreSQL. commit() then rolls it back and returns normally (nothing in it is committed), as pgjdbc does; set raiseExceptionOnSilentRollback=true to get an exception instead.
  • executeBatch() stops at the first failing statement and throws BatchUpdateException; getUpdateCounts() holds the counts of the statements that ran before it.
  • setReadOnly(true) is enforced: a write fails with SQLSTATE 25006.
  • Savepoints are supported. CallableStatement is not (SQLFeatureNotSupportedException); run CALL ... through a Statement.

Maven Central publishes checksums and a GPG signature next to the JAR:

Terminal window
BASE=https://repo1.maven.org/maven2/ai/particledb/particledb-jdbc/1.0.12
curl -sL -o particledb-jdbc-1.0.12.jar "$BASE/particledb-jdbc-1.0.12.jar"
EXPECTED=$(curl -sL "$BASE/particledb-jdbc-1.0.12.jar.sha1")
ACTUAL=$(shasum -a 1 particledb-jdbc-1.0.12.jar | awk '{print $1}')
[ "$EXPECTED" = "$ACTUAL" ] && echo OK || echo MISMATCH

The standard PostgreSQL JDBC driver works over the SQL client interface, including TLS and savepoints:

<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.7.11</version>
</dependency>
try (Connection c = DriverManager.getConnection(
"jdbc:postgresql://127.0.0.1:5432/particledb", "postgres", "secret")) {
c.setAutoCommit(false);
try (Statement s = c.createStatement()) {
s.executeUpdate("INSERT INTO users VALUES (30, 'pg', 1.0)");
Savepoint sp = c.setSavepoint();
s.executeUpdate("INSERT INTO users VALUES (31, 'pg2', 1.0)");
c.rollback(sp); // undoes only the second insert
c.commit();
}
}

With the server’s default --auth-method password, a SQL client login must use TLS (add ?sslmode=require to the URL); with --auth-method scram-sha-256 it works with or without TLS. See TLS and authentication.