Working with PostgreSQL in Java

Introduction

PostgreSQL (which goes by the moniker Postgres) is famous for its object-relational nature. In contrast, other database systems are usually relational. Due to its nature, it's a great pairing with Java, which is heavily object-oriented.

Accessing a Postgres database using Java requires you to rely on the JDBC API, as you might've suspected. Because of this, Postgres routines and those of other database systems are alike. Still, that does not hide the fact that Postgres offers extra capabilities - such as an extended support for custom data types and large data sets.

What is PostgreSQL?

PostgreSQL is a derivative of the now defunct POSTGRES project. POSTGRES aimed to achieve not only object-orientation, but also extensibility. Nonetheless, the University of California ceased POSTGRES' development in 1994.

The early Postgres releases targeted UNIX computers. Yet, over the years, the database has become portable. Thus, you can find it on MacOS, Linux, and Windows systems.

Its open-source and free licensing has also added to its widespread adoption. Developers love it, in part, because they can dig into sources to find out how it works exactly.

Demo Application

A Postgres guide is incomplete without an accompanying CRUD implementation. We'll be writing a simple Java application that can create, read, update, and delete customer information from a Postgres database.

Of course, we'll start off by defining the entities and then using them to generate the database schema to make sure the tables are mapped correctly.

And as proper API demands, the business logic layer should not have an idea of what goes on in the database layer - a practice known as layered architecture. We will thus opt for the Data Access Object (DAO) pattern to meet this need.

Maven Dependency

We'll start off with a maven-archetype-quickstart for a simple skeleton Maven project via your terminal:

$ mvn archetype:generate -DgroupId=com.stackabuse.postgresql -DartifactId=java-postgresql-sample -DarchetypeArtifactId=maven-archetype-quickstart -DinteractiveMode=false

After running the command, you should end up with a structure like this:

java-postgresql-sample
├── src
|   ├── main
|      ├── java
|         ├── com
|            ├── stackabuse
|               ├── postgresql
└── test

Then, in your pom.xml file, add the Postgres dependency:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>{version}</version>
</dependency>

Domain Model

Let's make a directory called api in our src directory in which we'll define a model/entity - Customer:

public class Customer {
    private Integer id;
    private String firstName;
    private String lastName;
    private String email;

    // Constructor, getters and setters...

    @Override
    public String toString() {
        return "Customer["
                + "id=" + id
                + ", firstName=" + firstName
                + ", lastName=" + lastName
                + ", email=" + email
                + ']';
    }
}

This entity will be mapped into our Postgres database with its respective fields a bit later on.

CRUD Functionality

Since we're working according to the DAO pattern, let's start implementing our CRUD functionality through a Dao interface in the spi directory, which will house all of our interfaces and service classes:

public interface Dao<T, I> {
    Optional<T> get(int id);
    Collection<T> getAll();
    Optional<I> save(T t);
    void update(T t);
    void delete(T t);
}

Note the two class-level generics: T and I. T represents the actual class object to pass to and from the database, whereas I is the class of the entity's primary key.

We now have the CRUD skeleton and the domain object in place. With those two done, we can actually go ahead and create our database.

Creating a PosgreSQL Database

Follow the PostgreSQL installation guide for the platform you are using - the installation is pretty straightforward. With Postgres in place, we'll use pgAdmin to manage the installation.

On our localhost system, we'll create a database named sampledb and create a table for our Customers:

To do this, in pgAdmin we'll run the input in the query editor:

CREATE TABLE public.customer
(
    customer_id integer NOT NULL GENERATED ALWAYS AS IDENTITY (START 1 INCREMENT 1 ),
    first_name character varying(45) NOT NULL,
    last_name character varying(45) NOT NULL,
    email character varying(50),
    CONSTRAINT customer_pkey PRIMARY KEY (customer_id)
)

And thus, we've generated the table for Customers.

Connecting to the Database

Before we can execute any statements in the database from our code, we'll first need to set up a database connection. We'll do this through a JdcbConnection class:

public class JdbcConnection {

    private static final Logger LOGGER =
        Logger.getLogger(JdbcConnection.class.getName());
    private static Optional<Connection> connection = Optional.empty();

    public static Optional<Connection> getConnection() {
        if (connection.isEmpty()) {
            String url = "jdbc:postgresql://localhost:5432/sampledb";
            String user = "postgres";
            String password = "postgres";

            try {
                connection = Optional.ofNullable(
                    DriverManager.getConnection(url, user, password));
            } catch (SQLException ex) {
                LOGGER.log(Level.SEVERE, null, ex);
            }
        }

        return connection;
    }
}

The primary task of the class above is to retrieve a database connection. As it may not always return a non-null Connection object, the connection is wrapped in an Optional.

The other notable thing is that the connection is a static variable. Hence, the class returns the first, non-null connection instance it got on its initial run.

Adding Entities

Since we can now actually connect to the database, let's go ahead and try to create an entity in the database. To do so, we'll define a PostgreSqlDao class which implements the aforementioned Dao interface:

public class PostgreSqlDao implements Dao<Customer, Integer> {

    private static final Logger LOGGER =
        Logger.getLogger(PostgreSqlDao.class.getName());
    private final Optional<Connection> connection;

    public PostgreSqlDao() {
        this.connection = JdbcConnection.getConnection();
    }

    @Override
    public Optional<Integer> save(Customer customer) {
        String message = "The customer to be added should not be null";
        Customer nonNullCustomer = Objects.requireNonNull(customer, message);
        String sql = "INSERT INTO "
                + "customer(first_name, last_name, email) "
                + "VALUES(?, ?, ?)";

        return connection.flatMap(conn -> {
            Optional<Integer> generatedId = Optional.empty();

            try (PreparedStatement statement =
                 conn.prepareStatement(
                    sql,
                    Statement.RETURN_GENERATED_KEYS)) {

                statement.setString(1, nonNullCustomer.getFirstName());
                statement.setString(2, nonNullCustomer.getLastName());
                statement.setString(3, nonNullCustomer.getEmail());

                int numberOfInsertedRows = statement.executeUpdate();

                // Retrieve the auto-generated id
                if (numberOfInsertedRows > 0) {
                    try (ResultSet resultSet = statement.getGeneratedKeys()) {
                        if (resultSet.next()) {
                            generatedId = Optional.of(resultSet.getInt(1));
                        }
                    }
                }

                LOGGER.log(
                    Level.INFO,
                    "{0} created successfully? {1}",
                     new Object[]{nonNullCustomer,
                            (numberOfInsertedRows > 0)});
            } catch (SQLException ex) {
                LOGGER.log(Level.SEVERE, null, ex);
            }

            return generatedId;
        });
    }

    // Other methods of the interface which currently aren't implemented yet
}

After creating a Customer object, you may pass it to the save method of PostgreSqlDao to add it to the database.

The save method uses an SQL string for operating:

INSERT INTO customer(first_name, last_name, email) VALUES(?, ?, ?)

Using the database connection, the DAO then prepares the statement:

PreparedStatement statement = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)

Of interest is that the statement contains the flag Statement.RETURN_GENERATED_KEYS. This ensures that the database reports the primary key it created for the new row also.

Worth noting too is that the save method uses Java's mapping facility. It transforms the database connection into the return type the method requires. And more so, it uses a flatMap function to ensure that the value it returns does not have an Optional wrapping.

The remaining CRUD methods of PostgreSqlDao should follow the same premise. They should map the connection into a return, where required, and check if the connection exists first before operating with it otherwise.

Reading Entities

In our implementation, we've decided to have a method that returns a single Customer based on their id, and a method that returns all persisted customers from the database.

Let's start off with the simple .get() method that returns a single Customer with the corresponding id:

public Optional<Customer> get(int id) {
    return connection.flatMap(conn -> {
        Optional<Customer> customer = Optional.empty();
        String sql = "SELECT * FROM customer WHERE customer_id = " + id;

        try (Statement statement = conn.createStatement();
                ResultSet resultSet = statement.executeQuery(sql)) {

            if (resultSet.next()) {
                String firstName = resultSet.getString("first_name");
                String lastName = resultSet.getString("last_name");
                String email = resultSet.getString("email");

                customer = Optional.of(
                    new Customer(id, firstName, lastName, email));

                LOGGER.log(Level.INFO, "Found {0} in database", customer.get());
            }
        } catch (SQLException ex) {
            LOGGER.log(Level.SEVERE, null, ex);
        }

        return customer;
    });
}

The code is fairly straightforward. We execute the query through our Statement object and pack the results into a ResultSet. Then, we extract the information from the ResultSet and pack it into a constructor for a Customer, which is returned.

Now, let's implement the .getAll() method:

public Collection<Customer> getAll() {
    Collection<Customer> customers = new ArrayList<>();
    String sql = "SELECT * FROM customer";

    connection.ifPresent(conn -> {
        try (Statement statement = conn.createStatement();
                ResultSet resultSet = statement.executeQuery(sql)) {

            while (resultSet.next()) {
                int id = resultSet.getInt("customer_id");
                String firstName = resultSet.getString("first_name");
                String lastName = resultSet.getString("last_name");
                String email = resultSet.getString("email");

                Customer customer = new Customer(id, firstName, lastName, email);

                customers.add(customer);

                LOGGER.log(Level.INFO, "Found {0} in database", customer);
            }

        } catch (SQLException ex) {
            LOGGER.log(Level.SEVERE, null, ex);
        }
    });

    return customers;
}

Again, fairly straightforward - we execute the adequate SQL query, extract the information, instantiate Customer objects, and pack them into an ArrayList.

Updating Entities

Next, if we ever wish to update an entity after creating it, we need to have an .update() method:

public void update(Customer customer) {
    String message = "The customer to be updated should not be null";
    Customer nonNullCustomer = Objects.requireNonNull(customer, message);
    String sql = "UPDATE customer "
            + "SET "
            + "first_name = ?, "
            + "last_name = ?, "
            + "email = ? "
            + "WHERE "
            + "customer_id = ?";

    connection.ifPresent(conn -> {
        try (PreparedStatement statement = conn.prepareStatement(sql)) {

            statement.setString(1, nonNullCustomer.getFirstName());
            statement.setString(2, nonNullCustomer.getLastName());
            statement.setString(3, nonNullCustomer.getEmail());
            statement.setInt(4, nonNullCustomer.getId());

            int numberOfUpdatedRows = statement.executeUpdate();

            LOGGER.log(Level.INFO, "Was the customer updated successfully? {0}",
                    numberOfUpdatedRows > 0);

        } catch (SQLException ex) {
            LOGGER.log(Level.SEVERE, null, ex);
        }
    });
}

Again, we've prepared a statement and executed the update query based on the fields and id of the Customer passed to the update method.

Deleting Entities

And finally, sometimes we might wish to delete an entity, and for that purpose, the .delete() method is used:

public void delete(Customer customer) {
    String message = "The customer to be deleted should not be null";
    Customer nonNullCustomer = Objects.requireNonNull(customer, message);
    String sql = "DELETE FROM customer WHERE customer_id = ?";

    connection.ifPresent(conn -> {
        try (PreparedStatement statement = conn.prepareStatement(sql)) {

            statement.setInt(1, nonNullCustomer.getId());

            int numberOfDeletedRows = statement.executeUpdate();

            LOGGER.log(Level.INFO, "Was the customer deleted successfully? {0}",
                    numberOfDeletedRows > 0);

        } catch (SQLException ex) {
            LOGGER.log(Level.SEVERE, null, ex);
        }
    });
}

Again, based on the Customer's id, the delete query is executed to delete the entity.

Running the Application

After fleshing out the DAO implementation, the project now needs an entry point. The best place for this would be in the main static method:

public class CustomerApplication {

    private static final Logger LOGGER =
        Logger.getLogger(CustomerApplication.class.getName());
    private static final Dao<Customer, Integer> CUSTOMER_DAO = new PostgreSqlDao();

    public static void main(String[] args) {
        // Test whether an exception is thrown when
        // the database is queried for a non-existent customer.
        // But, if the customer does exist, the details will be printed
        // on the console
        try {
            Customer customer = getCustomer(1);
        } catch (NonExistentEntityException ex) {
            LOGGER.log(Level.WARNING, ex.getMessage());
        }

        // Test whether a customer can be added to the database
        Customer firstCustomer =
            new Customer("Manuel", "Kelley", "[email protected]");
        Customer secondCustomer =
            new Customer("Joshua", "Daulton", "[email protected]");
        Customer thirdCustomer =
            new Customer("April", "Ellis", "[email protected]");
        addCustomer(firstCustomer).ifPresent(firstCustomer::setId);
        addCustomer(secondCustomer).ifPresent(secondCustomer::setId);
        addCustomer(thirdCustomer).ifPresent(thirdCustomer::setId);

        // Test whether the new customer's details can be edited
        firstCustomer.setFirstName("Franklin");
        firstCustomer.setLastName("Hudson");
        firstCustomer.setEmail("[email protected]");
        updateCustomer(firstCustomer);

        // Test whether all customers can be read from database
        getAllCustomers().forEach(System.out::println);

        // Test whether a customer can be deleted
        deleteCustomer(secondCustomer);
    }

    // Static helper methods referenced above
    public static Customer getCustomer(int id) throws NonExistentEntityException {
        Optional<Customer> customer = CUSTOMER_DAO.get(id);
        return customer.orElseThrow(NonExistentCustomerException::new);
    }

    public static Collection<Customer> getAllCustomers() {
        return CUSTOMER_DAO.getAll();
    }

    public static void updateCustomer(Customer customer) {
        CUSTOMER_DAO.update(customer);
    }

    public static Optional<Integer> addCustomer(Customer customer) {
        return CUSTOMER_DAO.save(customer);
    }

    public static void deleteCustomer(Customer customer) {
        CUSTOMER_DAO.delete(customer);
    }
}

Since the CRUD methods from PostgreSqlDao are public, we'll wrap it to prevent exposure of the database layer to the rest of the code when not needed.

With that done, there are two other custom exception classes that need to be put in place. These are NonExistentEntityException:

public class NonExistentEntityException extends Throwable {

    private static final long serialVersionUID = -3760558819369784286L;

    public NonExistentEntityException(String message) {
        super(message);
    }
}

And its inheritor, NonExistentCustomerException:

public class NonExistentCustomerException extends NonExistentEntityException {

    private static final long serialVersionUID = 8633588908169766368L;

    public NonExistentCustomerException() {
        super("Customer does not exist");
    }
}

These two classes handle exceptions the DAO throws when a Customer does not exist to make exception handling a bit more friendly.

Conclusion

We have seen how to create a Postgres-based CRUD application. The steps show that it is actually a trivial affair to set up the Postgres back-end. Tying a Java domain model to a Postgres database connection takes a bit more work. That is because best practice demands for layer separation and information hiding.

You can find the entire project code on GitHub.