PostgreSQL
suggest changeJava programs talk to PostgreSQL through JDBC, the standard java.sql API that ships with the JDK. The database-specific part is the driver, pgjdbc (org.postgresql:postgresql), a pure-Java implementation of the PostgreSQL wire protocol. Libraries such as Spring's JdbcTemplate, jOOQ, MyBatis and Hibernate all run on top of JDBC, so the basics in this article apply to them too.
Add the driver to pom.xml:
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.7.14</version>
</dependency>
With Gradle: implementation("org.postgresql:postgresql:42.7.14").
The examples below were run with JDK 25 against PostgreSQL 16, using a database named shop (CREATE DATABASE shop;). Each one is a standalone program you can run with java -cp postgresql-42.7.14.jar Example.java.
Connecting
A JDBC URL for PostgreSQL has the form jdbc:postgresql://host:port/database?param=value&.... Since JDBC 4 the driver registers itself automatically, so you don't need Class.forName("org.postgresql.Driver") anymore.
Connection, Statement and ResultSet all hold resources (a socket, a server-side portal), so always open them in try-with-resources:
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Connect {
static final String URL = "jdbc:postgresql://localhost:5432/shop";
public static void main(String[] args) throws SQLException {
try (Connection conn = DriverManager.getConnection(URL, "postgres", "secret")) {
DatabaseMetaData meta = conn.getMetaData();
System.out.println("Driver: " + meta.getDriverName() + " " + meta.getDriverVersion());
System.out.println("Server: " + meta.getDatabaseProductVersion());
try (Statement st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT current_database(), current_user")) {
rs.next();
System.out.println("DB: " + rs.getString(1));
System.out.println("User: " + rs.getString(2));
}
}
}
}
Output:
Driver: PostgreSQL JDBC Driver 42.7.14
Server: 16.2
DB: shop
User: postgres
Useful URL parameters:
| Parameter | Purpose |
|---|---|
ssl=true, sslmode=verify-full |
Encrypt the connection and verify the server certificate |
ApplicationName=my-app |
Shows up in pg_stat_activity, handy for debugging |
connectTimeout=10 |
Seconds to wait for the TCP connection |
socketTimeout=30 |
Seconds to wait on a read before giving up |
currentSchema=app |
Sets the search_path |
reWriteBatchedInserts=true |
Rewrites batched INSERTs into multi-row inserts (see below) |
Don't hard-code credentials. Read them from environment variables (System.getenv("PGPASSWORD")) or a secrets manager.
Creating a table
Statement.execute() runs DDL. Text blocks keep multi-line SQL readable:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class Schema {
static final String URL = "jdbc:postgresql://localhost:5432/shop";
public static void main(String[] args) throws SQLException {
try (Connection conn = DriverManager.getConnection(URL, "postgres", "secret");
Statement st = conn.createStatement()) {
st.execute("DROP TABLE IF EXISTS products");
st.execute("""
CREATE TABLE products (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
sku TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
price NUMERIC(10, 2) NOT NULL,
tags TEXT[] NOT NULL DEFAULT '{}',
attrs JSONB,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
)
""");
System.out.println("Table created");
}
}
}
GENERATED ALWAYS AS IDENTITY is the modern replacement for SERIAL. NUMERIC maps to BigDecimal. Never use double for money.
Inserting rows with PreparedStatement
Always pass values through PreparedStatement placeholders (?) rather than gluing strings together. It prevents SQL injection and lets the server reuse the query plan. PostgreSQL's RETURNING clause hands back generated values, so you read them with executeQuery():
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Insert {
static final String URL = "jdbc:postgresql://localhost:5432/shop";
static long insertProduct(Connection conn, String sku, String name, BigDecimal price)
throws SQLException {
String sql = "INSERT INTO products (sku, name, price) VALUES (?, ?, ?) RETURNING id";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, sku);
ps.setString(2, name);
ps.setBigDecimal(3, price);
try (ResultSet rs = ps.executeQuery()) {
rs.next();
return rs.getLong("id");
}
}
}
public static void main(String[] args) throws SQLException {
try (Connection conn = DriverManager.getConnection(URL, "postgres", "secret")) {
long id1 = insertProduct(conn, "KB-01", "Mechanical keyboard", new BigDecimal("89.99"));
long id2 = insertProduct(conn, "MS-02", "Wireless mouse", new BigDecimal("24.50"));
System.out.println("Inserted ids: " + id1 + ", " + id2);
// Alternative: let the driver append RETURNING for you
String sql = "INSERT INTO products (sku, name, price) VALUES (?, ?, ?)";
try (PreparedStatement ps = conn.prepareStatement(sql, new String[] {"id"})) {
ps.setString(1, "MN-03");
ps.setString(2, "27\" monitor");
ps.setBigDecimal(3, new BigDecimal("279.00"));
int rows = ps.executeUpdate();
try (ResultSet keys = ps.getGeneratedKeys()) {
keys.next();
System.out.println("Rows: " + rows + ", generated id: " + keys.getLong(1));
}
}
}
}
}
Output:
Inserted ids: 1, 2
Rows: 1, generated id: 3
Passing column names to prepareStatement(sql, new String[] {"id"}) makes the driver add RETURNING id for you, and getGeneratedKeys() reads it. Avoid Statement.RETURN_GENERATED_KEYS with PostgreSQL: the driver then appends RETURNING * and sends every column back.
Querying and mapping to records
Mapping each row to a Java record keeps JDBC code out of the rest of the application. Use rs.getObject(column, Type.class) to get java.time types directly:
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.OffsetDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
public class Query {
static final String URL = "jdbc:postgresql://localhost:5432/shop";
record Product(long id, String sku, String name, BigDecimal price, OffsetDateTime createdAt) {}
static Product map(ResultSet rs) throws SQLException {
return new Product(
rs.getLong("id"),
rs.getString("sku"),
rs.getString("name"),
rs.getBigDecimal("price"),
rs.getObject("created_at", OffsetDateTime.class));
}
static List<Product> findCheaperThan(Connection conn, BigDecimal max) throws SQLException {
String sql = "SELECT id, sku, name, price, created_at FROM products WHERE price < ? ORDER BY price";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setBigDecimal(1, max);
try (ResultSet rs = ps.executeQuery()) {
List<Product> result = new ArrayList<>();
while (rs.next()) {
result.add(map(rs));
}
return result;
}
}
}
static Optional<Product> findBySku(Connection conn, String sku) throws SQLException {
String sql = "SELECT id, sku, name, price, created_at FROM products WHERE sku = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, sku);
try (ResultSet rs = ps.executeQuery()) {
return rs.next() ? Optional.of(map(rs)) : Optional.empty();
}
}
}
public static void main(String[] args) throws SQLException {
try (Connection conn = DriverManager.getConnection(URL, "postgres", "secret")) {
for (Product p : findCheaperThan(conn, new BigDecimal("100"))) {
System.out.printf("%d %-6s %-20s %8s%n", p.id(), p.sku(), p.name(), p.price());
}
System.out.println(findBySku(conn, "MN-03").map(Product::name).orElse("not found"));
System.out.println(findBySku(conn, "XX-99").map(Product::name).orElse("not found"));
Product p = findBySku(conn, "KB-01").orElseThrow();
System.out.println("created_at offset: " + p.createdAt().getOffset());
}
}
}
Output:
2 MS-02 Wireless mouse 24.50
1 KB-01 Mechanical keyboard 89.99
27" monitor
not found
created_at offset: Z
Things to know:
- Column indexes in JDBC start at 1, not 0.
getInt/getLong/getDoublereturn0for SQLNULL. Callrs.wasNull()afterwards, or users.getObject("col", Integer.class), which returnsnull.TIMESTAMPTZis stored in UTC. pgjdbc returns anOffsetDateTimewith offsetZ, so convert it withatZoneSameInstant(...)for display. UseLocalDateTimeonly forTIMESTAMP(without time zone) columns.- Avoid the legacy
java.sql.Timestamp/java.sql.Datetypes in new code.
Transactions
A new connection is in auto-commit mode: each statement commits on its own. To group statements, turn auto-commit off, then commit() or rollback():
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Tx {
static final String URL = "jdbc:postgresql://localhost:5432/shop";
static void transfer(Connection conn, long from, long to, BigDecimal amount) throws SQLException {
conn.setAutoCommit(false);
try (PreparedStatement debit = conn.prepareStatement(
"UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?");
PreparedStatement credit = conn.prepareStatement(
"UPDATE accounts SET balance = balance + ? WHERE id = ?")) {
debit.setBigDecimal(1, amount);
debit.setLong(2, from);
debit.setBigDecimal(3, amount);
if (debit.executeUpdate() != 1) {
throw new SQLException("insufficient funds in account " + from);
}
credit.setBigDecimal(1, amount);
credit.setLong(2, to);
credit.executeUpdate();
conn.commit();
} catch (SQLException e) {
conn.rollback();
throw e;
} finally {
conn.setAutoCommit(true);
}
}
static void printBalances(Connection conn) throws SQLException {
try (Statement st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT id, balance FROM accounts ORDER BY id")) {
while (rs.next()) {
System.out.println(" account " + rs.getLong(1) + ": " + rs.getBigDecimal(2));
}
}
}
public static void main(String[] args) throws SQLException {
try (Connection conn = DriverManager.getConnection(URL, "postgres", "secret")) {
try (Statement st = conn.createStatement()) {
st.execute("DROP TABLE IF EXISTS accounts");
st.execute("CREATE TABLE accounts (id BIGINT PRIMARY KEY, balance NUMERIC(12,2) NOT NULL)");
st.execute("INSERT INTO accounts VALUES (1, 100.00), (2, 50.00)");
}
transfer(conn, 1, 2, new BigDecimal("30.00"));
System.out.println("After first transfer:");
printBalances(conn);
try {
transfer(conn, 2, 1, new BigDecimal("500.00"));
} catch (SQLException e) {
System.out.println("Rolled back: " + e.getMessage());
}
printBalances(conn);
}
}
}
Output:
After first transfer:
account 1: 70.00
account 2: 80.00
Rolled back: insufficient funds in account 2
account 1: 70.00
account 2: 80.00
The balance >= ? condition in the UPDATE makes the check and the change one atomic step, so two concurrent transfers can't both pass the check. For stricter guarantees, call conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE) and retry the transaction when it fails with SQLState 40001 (serialization failure).
Batch inserts
Sending rows one at a time costs one network round trip per row. With addBatch()/executeBatch() the driver sends many rows at once. Adding reWriteBatchedInserts=true to the URL goes further: pgjdbc rewrites the batch into multi-row INSERT ... VALUES (...), (...), ... statements, which is often several times faster:
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Batch {
// reWriteBatchedInserts turns N single-row INSERTs into multi-row INSERTs
static final String URL = "jdbc:postgresql://localhost:5432/shop?reWriteBatchedInserts=true";
public static void main(String[] args) throws SQLException {
try (Connection conn = DriverManager.getConnection(URL, "postgres", "secret")) {
conn.setAutoCommit(false);
String sql = "INSERT INTO products (sku, name, price) VALUES (?, ?, ?)";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
for (int i = 1; i <= 1000; i++) {
ps.setString(1, "CBL-%04d".formatted(i));
ps.setString(2, "Cable #" + i);
ps.setBigDecimal(3, new BigDecimal("4.99"));
ps.addBatch();
if (i % 250 == 0) {
int[] counts = ps.executeBatch();
// With rewriting, each entry is Statement.SUCCESS_NO_INFO (-2)
System.out.println("flushed " + counts.length + " rows, counts[0] = " + counts[0]);
}
}
}
conn.commit();
try (Statement st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT count(*) FROM products WHERE sku LIKE 'CBL-%'")) {
rs.next();
System.out.println("cables in table: " + rs.getLong(1));
}
}
}
}
Output:
flushed 250 rows, counts[0] = -2
flushed 250 rows, counts[0] = -2
flushed 250 rows, counts[0] = -2
flushed 250 rows, counts[0] = -2
cables in table: 1000
When inserts are rewritten, the driver can't report a per-row count, so each array entry is Statement.SUCCESS_NO_INFO (-2). Flushing every few hundred or thousand rows keeps memory bounded. For really large loads, use COPY (below).
PostgreSQL types: UUID, dates, arrays and JSONB
pgjdbc maps most PostgreSQL types to natural Java types:
| PostgreSQL | Java |
|---|---|
uuid |
java.util.UUID (setObject / getObject(col, UUID.class)) |
date |
LocalDate |
timestamp |
LocalDateTime |
timestamptz |
OffsetDateTime |
numeric |
BigDecimal |
text[], int[] |
java.sql.Array (createArrayOf, getArray) |
json, jsonb |
String or org.postgresql.util.PGobject |
import java.sql.Array;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDate;
import java.time.OffsetDateTime;
import java.time.ZoneOffset;
import java.util.Arrays;
import java.util.UUID;
import org.postgresql.util.PGobject;
public class Types {
static final String URL = "jdbc:postgresql://localhost:5432/shop";
public static void main(String[] args) throws SQLException {
try (Connection conn = DriverManager.getConnection(URL, "postgres", "secret")) {
try (Statement st = conn.createStatement()) {
st.execute("DROP TABLE IF EXISTS events");
st.execute("""
CREATE TABLE events (
id UUID PRIMARY KEY,
day DATE NOT NULL,
at TIMESTAMPTZ NOT NULL,
labels TEXT[] NOT NULL,
scores INTEGER[] NOT NULL,
payload JSONB NOT NULL
)
""");
}
UUID id = UUID.randomUUID();
String sql = "INSERT INTO events VALUES (?, ?, ?, ?, ?, ?)";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setObject(1, id); // uuid
ps.setObject(2, LocalDate.of(2026, 10, 17)); // date
ps.setObject(3, OffsetDateTime.of(2026, 10, 17, 9, 30, 0, 0,
ZoneOffset.ofHours(2))); // timestamptz
ps.setArray(4, conn.createArrayOf("text", new String[] {"login", "web"}));
ps.setArray(5, conn.createArrayOf("integer", new Integer[] {7, 9, 10}));
PGobject json = new PGobject();
json.setType("jsonb");
json.setValue("{\"user\": \"alice\", \"ip\": \"10.0.0.5\"}");
ps.setObject(6, json);
ps.executeUpdate();
}
String q = """
SELECT id, day, at, labels, scores, payload, payload->>'user' AS user_name
FROM events WHERE id = ?
""";
try (PreparedStatement ps = conn.prepareStatement(q)) {
ps.setObject(1, id);
try (ResultSet rs = ps.executeQuery()) {
rs.next();
UUID readId = rs.getObject("id", UUID.class);
LocalDate day = rs.getObject("day", LocalDate.class);
OffsetDateTime at = rs.getObject("at", OffsetDateTime.class);
Array labels = rs.getArray("labels");
String[] labelValues = (String[]) labels.getArray();
Integer[] scores = (Integer[]) rs.getArray("scores").getArray();
String payload = rs.getString("payload");
System.out.println("same uuid: " + readId.equals(id));
System.out.println("day: " + day);
System.out.println("at (UTC): " + at);
System.out.println("labels: " + Arrays.toString(labelValues));
System.out.println("scores: " + Arrays.toString(scores));
System.out.println("payload: " + payload);
System.out.println("user: " + rs.getString("user_name"));
}
}
// Pass a Java array to "= ANY(?)" instead of building an IN (...) list
try (PreparedStatement ps = conn.prepareStatement(
"SELECT sku FROM products WHERE sku = ANY(?) ORDER BY sku")) {
ps.setArray(1, conn.createArrayOf("text", new String[] {"KB-01", "MN-03", "nope"}));
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
System.out.println("found " + rs.getString(1));
}
}
}
// JSON as a plain string parameter with an explicit cast
try (PreparedStatement ps = conn.prepareStatement(
"UPDATE products SET attrs = ?::jsonb WHERE sku = ?")) {
ps.setString(1, "{\"color\": \"black\", \"wireless\": true}");
ps.setString(2, "MS-02");
ps.executeUpdate();
}
try (PreparedStatement ps = conn.prepareStatement(
"SELECT name, attrs->>'color' FROM products WHERE attrs @> ?::jsonb")) {
ps.setString(1, "{\"wireless\": true}");
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
System.out.println("wireless: " + rs.getString(1) + " (" + rs.getString(2) + ")");
}
}
}
}
}
}
Output:
same uuid: true
day: 2026-10-17
at (UTC): 2026-10-17T07:30Z
labels: [login, web]
scores: [7, 9, 10]
payload: {"ip": "10.0.0.5", "user": "alice"}
user: alice
found KB-01
found MN-03
wireless: Wireless mouse (black)
Notes:
sku = ANY(?)with one array parameter replacesIN (?, ?, ?, ...). You get a single prepared statement no matter how many values there are.- PostgreSQL won't implicitly convert a
varcharparameter tojsonb. Either wrap it in aPGobjectwith typejsonb, or keepsetStringand cast in SQL with?::jsonb. Alternatively, addstringtype=unspecifiedto the URL to let the server infer the types of all string parameters. JSONBnormalizes documents: keys are reordered and whitespace is removed, as thepayloadoutput shows. Use a library like Jackson to turn the string into objects.@>(contains) queries onjsonbcan use a GIN index:CREATE INDEX ON products USING gin (attrs);.
Upserts and handling errors
INSERT ... ON CONFLICT inserts a row or updates the existing one in a single atomic statement. When a statement fails, pgjdbc throws PSQLException. Its SQLState (a 5-character code defined by the SQL standard and PostgreSQL) tells you what went wrong, and getServerErrorMessage() gives the structured details the server sent:
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.postgresql.util.PSQLException;
import org.postgresql.util.PSQLState;
import org.postgresql.util.ServerErrorMessage;
public class Upsert {
static final String URL = "jdbc:postgresql://localhost:5432/shop";
public static void main(String[] args) throws SQLException {
try (Connection conn = DriverManager.getConnection(URL, "postgres", "secret")) {
String upsert = """
INSERT INTO products (sku, name, price) VALUES (?, ?, ?)
ON CONFLICT (sku) DO UPDATE
SET name = EXCLUDED.name, price = EXCLUDED.price
RETURNING id, price, (xmax = 0) AS inserted
""";
try (PreparedStatement ps = conn.prepareStatement(upsert)) {
String[][] rows = {
{"KB-01", "Mechanical keyboard", "79.99"},
{"HS-04", "Headset", "59.00"}};
for (String[] row : rows) {
ps.setString(1, row[0]);
ps.setString(2, row[1]);
ps.setBigDecimal(3, new BigDecimal(row[2]));
try (ResultSet rs = ps.executeQuery()) {
rs.next();
System.out.printf("%s -> id=%d price=%s %s%n", row[0], rs.getLong("id"),
rs.getBigDecimal("price"),
rs.getBoolean("inserted") ? "inserted" : "updated");
}
}
}
// A plain INSERT of a duplicate SKU fails with SQLState 23505
try (PreparedStatement ps = conn.prepareStatement(
"INSERT INTO products (sku, name, price) VALUES (?, ?, ?)")) {
ps.setString(1, "KB-01");
ps.setString(2, "Duplicate");
ps.setBigDecimal(3, BigDecimal.ONE);
ps.executeUpdate();
} catch (PSQLException e) {
if (PSQLState.UNIQUE_VIOLATION.getState().equals(e.getSQLState())) {
ServerErrorMessage err = e.getServerErrorMessage();
System.out.println("SQLState: " + e.getSQLState());
System.out.println("constraint: " + err.getConstraint());
System.out.println("detail: " + err.getDetail());
} else {
throw e;
}
}
}
}
}
Output:
KB-01 -> id=1 price=79.99 updated
HS-04 -> id=1005 price=59.00 inserted
SQLState: 23505
constraint: products_sku_key
detail: Key (sku)=(KB-01) already exists.
(xmax = 0) AS inserted is a common PostgreSQL trick for telling inserted rows from updated ones. Note that the new row got id 1005: the 1000 cables used ids 4-1003, and the conflicting KB-01 upsert used up 1004 even though it updated an existing row. Identity values come from a sequence, and a value taken by a failed or conflicting insert is never handed out again. Expect gaps in generated ids.
Common SQLStates:
| SQLState | Meaning |
|---|---|
23505 |
unique_violation |
23503 |
foreign_key_violation |
23502 |
not_null_violation |
40001 |
serialization_failure (retry the transaction) |
40P01 |
deadlock_detected (retry the transaction) |
57014 |
query_canceled (e.g. statement_timeout) |
Inside a transaction, any error puts the transaction in an aborted state: every later statement fails with current transaction is aborted until you call rollback(). To recover from an expected error and continue the transaction, set a Savepoint before the risky statement and call conn.rollback(savepoint).
Connection pooling with HikariCP
Opening a PostgreSQL connection is expensive: a TCP handshake, maybe TLS, authentication, and a new backend process on the server. Servers and web applications should reuse connections from a pool. HikariCP is the standard choice, and Spring Boot uses it by default:
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
<version>7.1.0</version>
</dependency>
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
import java.util.ArrayList;
import java.util.List;
public class Pool {
public static void main(String[] args) throws Exception {
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost:5432/shop");
config.setUsername("postgres");
config.setPassword("secret");
config.setMaximumPoolSize(4);
config.setPoolName("shop-pool");
config.addDataSourceProperty("reWriteBatchedInserts", "true");
config.addDataSourceProperty("ApplicationName", "inventory-service");
try (HikariDataSource ds = new HikariDataSource(config);
ExecutorService pool = Executors.newVirtualThreadPerTaskExecutor()) {
List<Future<String>> results = new ArrayList<>();
for (String sku : List.of("KB-01", "MS-02", "MN-03", "HS-04", "CBL-0007")) {
results.add(pool.submit(() -> priceOf(ds, sku)));
}
for (Future<String> f : results) {
System.out.println(f.get());
}
}
}
static String priceOf(HikariDataSource ds, String sku) throws SQLException {
// close() returns the connection to the pool instead of closing the socket
try (Connection conn = ds.getConnection();
PreparedStatement ps = conn.prepareStatement(
"SELECT price, current_setting('application_name') FROM products WHERE sku = ?")) {
ps.setString(1, sku);
try (ResultSet rs = ps.executeQuery()) {
return rs.next() ? sku + " = " + rs.getBigDecimal(1) + " [" + rs.getString(2) + "]"
: sku + " missing";
}
}
}
}
Output:
KB-01 = 79.99 [inventory-service]
MS-02 = 24.50 [inventory-service]
MN-03 = 279.00 [inventory-service]
HS-04 = 59.00 [inventory-service]
CBL-0007 = 4.99 [inventory-service]
Calling close() on a pooled connection returns it to the pool. Keep pools small: PostgreSQL works best with few active connections (a common starting point is about twice the number of CPU cores on the database server), and max_connections defaults to 100 for all clients combined. Create a single HikariDataSource per database for the application's lifetime and pass it around as a javax.sql.DataSource.
Bulk loading with COPY
COPY is PostgreSQL's fastest way to move data in and out of a table. pgjdbc exposes it through CopyManager, which you get by unwrapping the connection. This works with pooled connections too:
import java.io.IOException;
import java.io.StringReader;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import org.postgresql.PGConnection;
import org.postgresql.copy.CopyManager;
public class Copy {
static final String URL = "jdbc:postgresql://localhost:5432/shop";
public static void main(String[] args) throws SQLException, IOException {
try (Connection conn = DriverManager.getConnection(URL, "postgres", "secret")) {
CopyManager copy = conn.unwrap(PGConnection.class).getCopyAPI();
String csv = """
sku,name,price
USB-01,USB-C hub,34.90
USB-02,"USB stick, 128 GB",12.00
USB-03,USB charger,19.95
""";
long loaded = copy.copyIn(
"COPY products (sku, name, price) FROM STDIN WITH (FORMAT csv, HEADER true)",
new StringReader(csv));
System.out.println("loaded rows: " + loaded);
StringWriter out = new StringWriter();
long exported = copy.copyOut(
"COPY (SELECT sku, name, price FROM products WHERE sku LIKE 'USB-%' ORDER BY sku) "
+ "TO STDOUT WITH (FORMAT csv, HEADER true)",
out);
System.out.println("exported rows: " + exported);
System.out.print(out);
}
}
}
Output:
loaded rows: 3
exported rows: 3
sku,name,price
USB-01,USB-C hub,34.90
USB-02,"USB stick, 128 GB",12.00
USB-03,USB charger,19.95
copyIn and copyOut also accept InputStream/OutputStream, so you can stream a multi-gigabyte file straight from disk without loading it into memory.
LISTEN and NOTIFY
PostgreSQL has a built-in publish/subscribe mechanism. One session runs LISTEN channel, and any session can send NOTIFY channel, 'payload' (or SELECT pg_notify(channel, payload), which accepts parameters). Notifications are delivered only after the sending transaction commits:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import org.postgresql.PGConnection;
import org.postgresql.PGNotification;
public class Notify {
static final String URL = "jdbc:postgresql://localhost:5432/shop";
public static void main(String[] args) throws Exception {
try (Connection listener = DriverManager.getConnection(URL, "postgres", "secret");
Connection sender = DriverManager.getConnection(URL, "postgres", "secret")) {
try (Statement st = listener.createStatement()) {
st.execute("LISTEN price_changes");
}
Thread.ofVirtual().start(() -> {
try (PreparedStatement ps = sender.prepareStatement("SELECT pg_notify(?, ?)")) {
for (String msg : new String[] {"KB-01:74.99", "MS-02:19.99"}) {
ps.setString(1, "price_changes");
ps.setString(2, msg);
ps.execute();
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
});
PGConnection pg = listener.unwrap(PGConnection.class);
int received = 0;
while (received < 2) {
// Blocks for up to 5 seconds waiting for notifications
PGNotification[] batch = pg.getNotifications(5000);
if (batch == null) {
continue;
}
for (PGNotification n : batch) {
System.out.println(n.getName() + ": " + n.getParameter());
received++;
}
}
}
}
}
Output:
price_changes: KB-01:74.99
price_changes: MS-02:19.99
getNotifications(timeoutMillis) blocks until something arrives or the timeout expires. A listening connection must stay open and dedicated to listening, so don't borrow it from a pool and return it. Payloads are limited to 8000 bytes. A common pattern is to send just an id and let the listener query for the details.
Best practices
- Always use
PreparedStatementwith?placeholders. Never concatenate user input into SQL. - Close every
Connection,StatementandResultSetwith try-with-resources. - Use a connection pool (HikariCP) in any long-running application.
- Use
BigDecimalforNUMERIC,OffsetDateTimeforTIMESTAMPTZ, andUUIDforuuid. - Set a
statement_timeout(for exampleoptions=-c%20statement_timeout=5000in the URL) so that runaway queries don't hang threads. - For queries that return millions of rows, turn off auto-commit and call
ps.setFetchSize(1000). Otherwise pgjdbc reads the whole result into memory. - Manage schema changes with a migration tool like Flyway or Liquibase rather than
CREATE TABLEcalls in application code. - For integration tests, run a real PostgreSQL in Docker with Testcontainers (
org.testcontainers:postgresql) instead of substituting H2. SQL dialects differ, and features likejsonb, arrays andON CONFLICTdon't behave the same.