Working with PostgreSQL in Java - Using the JDBC Library

In this tutorial, we’ll learn how to connect to a Postgres database using JDBC, and run basic SQL queries in our Java application.

For our Java application, we’ll be using a maven project structure. You can see the complete source code for this example on Github

What Is JDBC?

JDBC stands for Java Database Connectivity. It is a set of APIs that we can use to connect and execute queries on any compatible database, like PostgreSQL.

Most of the classes and methods that we’ll be using are a part of the java.sql and javax.sql packages, which come included as a part of Java SE.

Let’s look at how data is retrieved from a database from most applications:

1. We create a connection to the database
2. We send a query statement (something like SELECT * FROM mytable)
3. The database returns the results
4. We use the results in our application

The JDBC API contains classes that describe each of these steps:

1. The Connection class represents the connection that we make with our database.
2. PreparedStatement represents the query statement that will be executed.
3. The results are returned by the database as a ResultSet instance that contains our queried data.

In the next sections, we’ll look at some examples of how we can do this in practice.

Creating a Test Database

Once you’ve installed Postgres you should have a Postgres instance running on our local machine (localhost) on port 5432 (which is the default for Postgres).

Now we can create the database and tables in the psql shell:

CREATE DATABASE bird_encyclopedia;
\c bird_encyclopedia

CREATE TABLE birds (
  id SERIAL PRIMARY KEY,
  bird VARCHAR(256),
  description VARCHAR(1024)
);

We can also populate the birds table with some test data:

INSERT INTO birds (bird , description) VALUES 
('pigeon', 'common in cities'),
('eagle', 'bird of prey');

Installing the Database Driver

When using JDBC, we need to install the driver of the database we’re using. In this case, that would be the PostgreSQL JDBC driver, which we can add as a dependency to our pom.xml file:

<dependency>
	<groupId>org.postgresql</groupId>
	<artifactId>postgresql</artifactId>
	<version>42.4.1</version>
</dependency>

You can see the entire pom.xml file for more details

The database driver is an implementation of the JDBC API interfaces, and allows our application to use JDBC classes to interact with the database:

Connecting to the Database - Creating a Data Source

To execute queries, we need to create a connection to the database. However creating a connection is resource-intensive, and we normally use a DataSource interface, which acts as a factory that provides Connection instances.

We can create a new PGSimpleDataSource instance provided by the postgres driver library, which implements the DataSource interface:

import javax.sql.DataSource;
import org.postgresql.ds.PGSimpleDataSource;

public class App {
		// We can create a method to create and return a DataSource for our Postgres DB
    private static DataSource createDataSource() {
				// The url specifies the address of our database along with username and password credentials
				// you should replace these with your own username and password
        final String url =
                "jdbc:postgresql://localhost:5432/bird_encyclopedia?user=dbadmin&password=my-secret-password";
        final PGSimpleDataSource dataSource = new PGSimpleDataSource();
        dataSource.setUrl(url);
        return dataSource;
    }
}

Now we can create Connection instances from our DataSource that we can use to run queries.

Reading Data

Let’s look at some code that will execute a query to read all the entries present in the birds table:

// Use the method we defined earlier to create a datasource
DataSource dataSource = createDataSource();

// get a connection from the datasource
Connection conn = dataSource.getConnection();

// Create a new statement on the connection
PreparedStatement stmt = conn.prepareStatement("SELECT * FROM birds");

// Execute the query, and store the results in the ResultSet instance
ResultSet rs = stmt.executeQuery();

// We run a loop to process the results.
// The rs.next() method moves the result pointer to the next result row, and returns
// true if a row is present, and false otherwise
// Note that initially the result pointer points before the first row, so we have to call
// rs.next() the first time
while (rs.next()) {
	// Now that `rs` points to a valid row (rs.next() is true), we can use the `getString`
	// and `getLong` methods to return each column value of the row as a string and long
	// respectively, and print it to the console
	System.out.printf("id:%d bird:%s description:%s%n", rs.getLong("id"),
			rs.getString("bird"), rs.getString("description"));
}

This will give the output:

id:1 bird:pigeon description:common in cities
id:2 bird:eagle description:bird of prey

When reading results, the ResultSet acts as a row-wise scanner. Initially, it points just before the first row, and returns false if no more rows exist.

Adding Query Params

In many cases, we need to pass in variables to our query. For example, if we want to filter a particular bird from our birds table, we can use the query:

SELECT * FROM birds WHERE bird = ?

Here, we want to pass in the value of the bird filter from our Java code.

We can do this by setting parameters on our PreparedStatement instance:

// ...
// same as before
// ...

// Create a prepared statement with a query param denoted by "?"
PreparedStatement stmt = conn.prepareStatement("select * from birds where bird=?");
// Set the value of the param. Param indexes begin from 1
// Since we want to set the value as a string, we use `setString`
// with the param index and param value as arguments
stmt.setString(1, "eagle");

// Execute the query and read the results same as before
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
	System.out.printf("id:%d bird:%s description:%s%n", rs.getLong("id"),
			rs.getString("bird"), rs.getString("description"));
}

This will give us the output:

id:2 bird:eagle description:bird of prey

Inserting Data

Write operations, like inserting data, work a bit differently than SELECT queries. In this case, we don’t get any results back. Instead, Postgres returns the number of rows that are modified by the write operation.

Instead of using stmt.executeQuery() like before, we can use the stmt.executeUpdate() method to insert data. This method returns an int that denotes the number of rows that were modified.

Let’s look at an example where we insert a new bird into our table:

// Create a new insert statement with the bird and description values as query params
PreparedStatement insertStmt =
        conn.prepareStatement("INSERT INTO birds(bird, description) VALUES (?, ?)");

// Set the query params
insertStmt.setString(1, "rooster");
insertStmt.setString(2, "wakes you up in the morning");

// Run the insert query using the `executeUpdate` method.
// This returns the number of inserted rows
int insertedRows = insertStmt.executeUpdate();
// Print out the number of inserted rows
System.out.printf("inserted %s bird(s)%n", insertedRows);

Now we will get the output:

inserted 1 bird(s)

And, if we check the table again, we can see that a new row was inserted:

> psql
dbadmin=# \c bird_encyclopedia
You are now connected to database "bird_encyclopedia" as user "dbadmin".

bird_encyclopedia=# select * from birds;
 id |  bird   |         description
----+---------+-----------------------------
  1 | pigeon  | common in cities
  2 | eagle   | bird of prey
  3 | rooster | wakes you up in the morning

Updating Data

We can use the executeUpdate method along with query params to execute an UPDATE query statement:

// Similar to the previous example, we can use query params to fill in the condition
// as well as the value to update
PreparedStatement updateStmt =
        conn.prepareStatement("UPDATE birds SET description = ? WHERE bird = ?");
updateStmt.setString(1, "has a red crown");
updateStmt.setString(2, "rooster");
int updatedRows = updateStmt.executeUpdate();
System.out.printf("updated %s bird(s)%n", updatedRows);

In this case, we will get the number of updated rows as a return value from executeUpdate.

Now we will get the output:

updated 1 bird(s)

Now if we check the table again, we can see the updated data for the rooster entry:

bird_encyclopedia=# select * from birds;
 id |  bird   |   description
----+---------+------------------
  1 | pigeon  | common in cities
  2 | eagle   | bird of prey
  3 | rooster | has a red crown

Deleting Data

Since DELETE queries also modify data, we should use executeUpdate for this as well:

// Similar to the previous example, we can use query params to fill in the delete condition
PreparedStatement deleteStmt = conn.prepareStatement("DELETE FROM birds WHERE bird = ?");
deleteStmt.setString(1, "rooster");
int deletedRows = deleteStmt.executeUpdate();
System.out.printf("deleted %s bird(s)%n", deletedRows);

Now we will get the output:

deleted 1 bird(s)

Our table will now only have the original two entries with the rooster entry removed:

bird_encyclopedia=# select * from birds;
 id |  bird   |   description
----+---------+------------------
  1 | pigeon  | common in cities
  2 | eagle   | bird of prey

Handling Sql Exceptions

The java.sql package specifies a SQLException class that is thrown if the database faces an error.

This error could be anything from a syntax error to a connection failure. We can determine the type of error in our Java program by handling the SQLException.

In the case of Postgres, the documentation specifies a list of error codes that could be returned, for example:

We can use the getSQLState method to determine the error code. We can decide what action to perform based on the error code. For example, you may want to retry the query if there is a temporary connection error, but report the exception if there is a syntax error:

public class App {
    public static void main(String[] args) throws Exception {

        DataSource dataSource = createDataSource();
        Connection conn = dataSource.getConnection();

        try {
            insertBird(conn);
        } catch (SQLException e) {
            String errorCode = e.getSQLState();
            // 08000 - connection_exception
            if (errorCode == "08000") {
                // retry query after re-establishing connection
            }
            // 42601 - syntax error
            else if (errorCode == "42601") {
                // throw error so that we can see the failure
                throw e;
            } else {
                // log a warning, or do some other action based on the error code
                System.out.printf("SQL failed with error code: %s%n", errorCode);
            }

        }

    }

    private static DataSource createDataSource() {
        final String url =
                "jdbc:postgresql://localhost:5432/bird_encyclopedia?user=dbadmin&password=my-secret-password";
        final PGSimpleDataSource dataSource = new PGSimpleDataSource();
        dataSource.setUrl(url);
        return dataSource;
    }

    // add a new bird to the table - this should throw an SQLException because there is a 
    // spelling error for the "description" column
    private static void insertBird(Connection conn) throws SQLException {
        PreparedStatement insertStmt =
        //                                                     ⬇ typo 
                conn.prepareStatement("INSERT INTO birds(bird, descsription) VALUES (?, ?)");
        insertStmt.setString(1, "rooster");
        insertStmt.setString(2, "wakes you up in the morning");
        int insertedRows = insertStmt.executeUpdate();
        System.out.printf("inserted %s bird(s)%n", insertedRows);
    }

}

Conclusion

In this tutorial, we covered the basics on how to connect and execute queries on a PostgreSQL database. In practical applications, we’d probably integrate our database connection with a web application or data processing job.

You can view and run the complete example code on Github.

If you want to know more about the different classes available within JDBC, you can look at the java.sql module documentation.

You can also view the documentation for the PostgreSQL JDBC driver to see postgres-specific classes.