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:
particledb start --data-dir ~/.particledb/data --auth-method scram-sha-256 --pg-password secretParticleDB JDBC driver
Section titled “ParticleDB JDBC driver”At a glance
Section titled “At a glance”| 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.
Install
Section titled “Install”<dependency> <groupId>ai.particledb</groupId> <artifactId>particledb-jdbc</artifactId> <version>1.0.12</version></dependency>dependencies { implementation("ai.particledb:particledb-jdbc:1.0.12")}Let Maven collect the driver and its dependencies into lib/:
cat > pom.xml <<'POM'<project><modelVersion>4.0.0</modelVersion> <groupId>x</groupId><artifactId>x</artifactId><version>0</version> <dependencies><dependency> <groupId>ai.particledb</groupId><artifactId>particledb-jdbc</artifactId> <version>1.0.12</version> </dependency></dependencies></project>POMmvn dependency:copy-dependencies -DoutputDirectory=libThen run with -cp "lib/*:your-app.jar". No JVM flags are needed.
JVM flags
Section titled “JVM flags”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.
Hello world
Section titled “Hello world”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)); } } }}CRUD round trip
Section titled “CRUD round trip”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")); } }}Connection options
Section titled “Connection options”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 |
Authentication
Section titled “Authentication”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.
DataSource
Section titled “DataSource”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()) { // ...}Connection pooling (HikariCP)
Section titled “Connection pooling (HikariCP)”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.
Transactions
Section titled “Transactions”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, andsetTransactionIsolation()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; setraiseExceptionOnSilentRollback=trueto get an exception instead. executeBatch()stops at the first failing statement and throwsBatchUpdateException;getUpdateCounts()holds the counts of the statements that ran before it.setReadOnly(true)is enforced: a write fails with SQLSTATE25006.- Savepoints are supported.
CallableStatementis not (SQLFeatureNotSupportedException); runCALL ...through aStatement.
Verifying the downloaded JAR
Section titled “Verifying the downloaded JAR”Maven Central publishes checksums and a GPG signature next to the JAR:
BASE=https://repo1.maven.org/maven2/ai/particledb/particledb-jdbc/1.0.12curl -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 MISMATCHSQL client interface with pgjdbc
Section titled “SQL client interface with pgjdbc”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.
Next steps
Section titled “Next steps”- Kotlin and Clojure. the same drivers from other JVM languages
- SQL Reference. SQL syntax and examples
- SQL client connections. connection settings for pgjdbc
This site uses Google Analytics cookies to count visits. Privacy · License