Managing Database Connections

Querying a PostgreSQL Database in Java

Miller Trujillo

Staff Software Engineer

Why connection management matters?

Each connection opens a network socket
Requires authentication and resource allocation
Unclosed connections cause resource leaks
New connections might fail

A diagram showing multiple database connections piling up, with some marked as "zombie" connections that are open but unused

try-with-resources

String sql = "SELECT COUNT(*) FROM books";


try (Connection conn = DriverManager.getConnection(url, user, password);
     Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery(sql)) {

  if (rs.next()) {
    System.out.println("Total books: " + rs.getInt(1));
  }
}

Connections are closed automatically

Connection pooling

Fresh connections repeat expensive setup
Pooling reuses existing connections
Connections return to pool when done

A taxi stand analogy

A diagram showing a connection pool with multiple pre-established connections, with arrows showing connections being borrowed and returned

HikariCP example

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;


HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost:5432/library");
config.setUsername("postgres");
config.setPassword("secret");


HikariDataSource ds = new HikariDataSource(config);


try (Connection conn = ds.getConnection()) {
  System.out.println("Connected with HikariCP!");
}

HikariCP Console Logging

[main] INFO com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Starting...
[main] INFO com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - 
    Added connection org.postgresql.jdbc.PgConnection@ID
[main] INFO com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Start completed.

Let's practice!

Querying a PostgreSQL Database in Java