PostgreSQL - Java

Modern PostgreSQL and Java Integration

Connecting a Java application to a PostgreSQL database is a common requirement. This tutorial will guide you through the modern best practices for doing so, moving away from outdated manual JAR management and unsafe coding practices.

Prerequisites

• Java Development Kit (JDK) 11 or newer.
• A modern build automation tool like Maven or Gradle. We will use Maven for our examples.
• A running PostgreSQL server instance.

Step 1: Project Setup with Maven

Modern Java projects should never manage dependencies by manually downloading JAR files. Instead, use a build tool like Maven. It automatically downloads the required libraries (called ‘dependencies’) and manages your project structure.

In your project’s pom.xml file, add the PostgreSQL JDBC driver dependency:

<!-- pom.xml -->
<project ...>
    ...
    <properties>
        <maven.compiler.source>11</maven.compiler.source>
        <maven.compiler.target>11</maven.compiler.target>
    </properties>

    <dependencies>
        <!-- PostgreSQL JDBC Driver -->
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.7.3</version> <!-- Check for the latest version on Maven Central -->
        </dependency>

        <!-- (Optional but Recommended) A connection pool like HikariCP -->
        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
            <version>5.1.0</version>
        </dependency>
    </dependencies>
    ...
</project>

Always check the Maven Central repository for the latest version of the PostgreSQL driver.

Step 2: Establishing a Connection Safely

Modern Java provides the try-with-resources statement, which automatically closes resources like database connections, statements, and result sets. This prevents resource leaks and makes code cleaner.

Best Practice: Avoid hardcoding credentials in your source code. Use environment variables or a configuration file.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DatabaseConnector {
    // Ideally, load these from a config file or environment variables
    private static final String URL = "jdbc:postgresql://localhost:5432/testdb";
    private static final String USER = "postgres";
    private static final String PASSWORD = "your_password";

    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
            if (conn != null) {
                System.out.println("Connected to the PostgreSQL server successfully.");
            } else {
                System.out.println("Failed to make connection.");
            }
        } catch (SQLException e) {
            System.err.println("Connection Failed! Check output console");
            e.printStackTrace();
        }
    }
}

Step 3: CRUD Operations with PreparedStatement

A critical security practice is to always use PreparedStatement instead of concatenating strings to build SQL queries. This prevents SQL Injection attacks, where a malicious user could manipulate your database.

Let’s create a full CRUD example class. First, the table setup SQL:

-- Run this in psql or a DB client first
DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    position TEXT NOT NULL,
    salary NUMERIC(10, 2)
);

Now, the complete Java code demonstrating safe CRUD operations:

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

// A simple model class for an Employee
class Employee {
    private int id;
    private String name;
    private String position;
    private double salary;
    // Constructors, Getters, Setters, and toString() omitted for brevity
}

public class EmployeeDAO {
    private static final String URL = "jdbc:postgresql://localhost:5432/testdb";
    private static final String USER = "postgres";
    private static final String PASSWORD = "your_password";

    // C: Create
    public void addEmployee(String name, String position, double salary) {
        String sql = "INSERT INTO employees(name, position, salary) VALUES(?, ?, ?)";
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, name);
            pstmt.setString(2, position);
            pstmt.setDouble(3, salary);
            pstmt.executeUpdate();
            System.out.println("Added employee: " + name);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // R: Read
    public Employee getEmployeeById(int id) {
        String sql = "SELECT * FROM employees WHERE id = ?";
        Employee employee = null;
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                // Create and populate the employee object (implementation omitted)
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return employee;
    }

    // U: Update
    public void updateEmployeeSalary(int id, double newSalary) {
        String sql = "UPDATE employees SET salary = ? WHERE id = ?";
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setDouble(1, newSalary);
            pstmt.setInt(2, id);
            int affectedRows = pstmt.executeUpdate();
            System.out.println("Updated salary for employee ID " + id + ". Rows affected: " + affectedRows);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // D: Delete
    public void deleteEmployee(int id) {
        String sql = "DELETE FROM employees WHERE id = ?";
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id);
            int affectedRows = pstmt.executeUpdate();
            System.out.println("Deleted employee ID " + id + ". Rows affected: " + affectedRows);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Step 4: Transaction Management

A transaction is a sequence of operations performed as a single logical unit of work. All operations must succeed, or none of them are applied. This ensures data integrity.

Example: Transferring a ‘bonus’ from one employee’s record to a special fund. Both updates must succeed.

public void performTransaction() {
    Connection conn = null;
    try {
        conn = DriverManager.getConnection(URL, USER, PASSWORD);
        // 1. Disable auto-commit to start a transaction
        conn.setAutoCommit(false);

        // 2. Perform multiple operations
        String sql1 = "UPDATE employees SET salary = salary - 5000 WHERE name = 'Alice'";
        String sql2 = "UPDATE company_funds SET balance = balance + 5000";

        try (Statement stmt = conn.createStatement()) {
            stmt.executeUpdate(sql1);
            stmt.executeUpdate(sql2);
        }

        // 3. If all operations succeed, commit the transaction
        conn.commit();
        System.out.println("Transaction committed successfully.");

    } catch (SQLException e) {
        System.err.println("Transaction failed. Rolling back...");
        if (conn != null) {
            try {
                // 4. If any error occurs, roll back the entire transaction
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        e.printStackTrace();
    } finally {
        if (conn != null) {
            try {
                conn.setAutoCommit(true); // Reset to default behavior
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Best Practice: Connection Pooling

Opening a new database connection for every request is slow and inefficient. In a real-world application, you should use a connection pool (like HikariCP, which we added to pom.xml). A connection pool maintains a set of open database connections that can be reused by your application, dramatically improving performance.

While a full implementation is beyond this basic tutorial, integrating a pool involves creating a single DataSource object at application startup and getting connections from it instead of from DriverManager.