Java JDBC PostgreSQL Connection Example

This article is a Java JDBC PostgreSQL Connection Example. PostgreSQL is an object-relational database management system. It’s one of the most popular databases used in the world. This article will show you how Java connects to it using JDBC.

1. Tools and Requirements

1. PostgreSQL download
2. Eclipse Oxygen
3. PostgreSQL JDBC driver download

Want to be a JDBC Master ?

Subscribe to our newsletter and download the JDBC Ultimate Guide right now!

In order to help you master database programming with JDBC, we have compiled a kick-ass guide with all the major JDBC features and use cases! Besides studying them online you may download the eBook in PDF format!

Download NOW!

Thank you!

We will contact you soon.

2. Development Environment Setup

This example is set up on a Windows 10 machine with JDK 1.8. PostgreSQL v10 and PostgreSQL JDBC driver v42.2.2 are used. Follow the download and installation instructions from the PostgreSQL v10 website.
This example installed all the PostgreSQL components:

• PostgreSQL Server
• pgAdmin 4
• Stack Builder
• Command Line tools

The PostgreSQL server is configured with the following attributes:

• superuser: postgres
• password: postgres
• default port: 5432

3. PostgreSQL Server Setup

Open up Windows Services and check if the PostgreSQL server is running. The service is called postgresql-x64-10. If it is running, open up pgAdmin 4 — there should be a shorcut under the PostgreSQL 10 menu.

Once you have signed in to pgAdmin 4, you should see something like below:

Now, go to Object -> Create -> Database and name it example. Under example, go to Schemas -> public -> Tables, right click, Create -> Table and name it cars.

Right click cars -> Scripts -> INSERT Script and replace the questions marks with the data you want and then execute by clicking the lightning button.

This Java JDBC PostgreSQL connection example used the insert script below:

INSERT SQL

INSERT INTO public.cars(model, price) VALUES ('mondeo', '20,000.00');

To check your records, Right click cars -> View/Edit Data -> All Rows.

4. Java JDBC PostgreSQL Connection Implementation

Now that you have your database server ready, it’s time to connect to it. Fire up Eclipse and create a new Java project, File -> New -> Java Project and name it java-jdbc-postgresql-connection. Create a lib directory and place postgresql-42.2.2.jar there and add it to the build path — right click on the project, Build Path -> Configure Buid Path.

Next, create the source below:

PostgreSqlExample.java

package com.javacodegeeks.example;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class PostgreSqlExample {
	public static void main(String[] args) {
		try (Connection connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/example", "postgres", "postgres")) {
			System.out.println("Java JDBC PostgreSQL Example");
			// When this class first attempts to establish a connection, it automatically loads any JDBC 4.0 drivers found within 
			// the class path. Note that your application must manually load any JDBC drivers prior to version 4.0.
//			Class.forName("org.postgresql.Driver"); 
			System.out.println("Connected to PostgreSQL database!");
			Statement statement = connection.createStatement();
			System.out.println("Reading car records...");
			System.out.printf("%-30.30s  %-30.30s%n", "Model", "Price");
			ResultSet resultSet = statement.executeQuery("SELECT * FROM public.cars");
			while (resultSet.next()) {
				System.out.printf("%-30.30s  %-30.30s%n", resultSet.getString("model"), resultSet.getString("price"));
			}
		} /*catch (ClassNotFoundException e) {
			System.out.println("PostgreSQL JDBC driver not found.");
			e.printStackTrace();
		}*/ catch (SQLException e) {
			System.out.println("Connection failure.");
			e.printStackTrace();
		}
	}
}

To connect to the PostgreSQL database, you use the DriverManager.getConnection method and provide the connection string, username and password as shown in line 11. You provide the hostname, port number and the database name in the connection string. You use the try-with-resources statement to ensure that the resource is closed after it is used. In line 16, the PostgreSQL driver is automatically loaded, so we commented this line. But if your JDBC driver is prior to version 4 then you’ll need to uncomment this line. The catch on lines 27 to 28 is needed only when you use Class.forName("org.postgresql.Driver");

4. Java JDBC PostgreSQL Connection Output

You should see the output below when you run the program.

Console Output

Java JDBC PostgreSQL Example
Connected to PostgreSQL database!
Reading car records...
Model                           Price                         
mondeo                          £20,000.00

7. Java JDBC PostgreSQL Connection Summary

To summarize how you can connect to a PostgreSQL database server, you add the PostgreSQL driver in your classpath. Use DriverManager and provide the connection string, username, and password to connect to the server. You then execute queries using the established connection. After using the connection, ensure that it is closed by closing it or using the try-with-resources statement.

8. Download the Source Code

This is an example about Java JDBC PostgreSQL Connection.