# Java and JDBC

Connect to ParticleDB from Java and JDBC, run parameterized queries, handle transactions and errors.

Source: https://particledb.ai/sdks/java/

Use the configured address for your client. See [connection addresses](/installation/ports/) and [security](/guides/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:

```bash
particledb start --data-dir ~/.particledb/data --auth-method scram-sha-256 --pg-password secret
```

## ParticleDB JDBC driver

### 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](/installation/releases/) for version-specific status.

### Install

  
### Maven

  ```xml
  <dependency>
      <groupId>ai.particledb</groupId>
      <artifactId>particledb-jdbc</artifactId>
      <version>1.0.12</version>
  </dependency>
  ```
  
  
### Gradle

  ```kotlin
  dependencies {
      implementation("ai.particledb:particledb-jdbc:1.0.12")
  }
  ```
  
  
### Plain classpath

  Let Maven collect the driver and its dependencies into `lib/`:

  ```bash
  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>
  POM
  mvn dependency:copy-dependencies -DoutputDirectory=lib
  ```

  Then run with `-cp "lib/*:your-app.jar"`. No JVM flags are needed.
  

### 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

```java
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

```java
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

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](#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](#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

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 |

### TLS

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.

```java
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](/connectivity/tls/).

### DataSource

```java
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)

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

```java
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

```java
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](/sql/transactional-ddl/) 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`.

### Verifying the downloaded JAR

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

```bash
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
```

## SQL client interface with pgjdbc

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

```xml
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.11</version>
</dependency>
```

```java
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](/connectivity/tls/#authentication).

## Next steps

- [Kotlin](/sdks/kotlin/) and [Clojure](/sdks/clojure/). the same drivers from other JVM languages
- [SQL Reference](/sql/overview/). SQL syntax and examples
- [SQL client connections](/connectivity/pg-wire/). connection settings for pgjdbc
