sql

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

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.

Java JDBC PostgreSQL Connection - Windows Services
Windows Services

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

Java JDBC PostgreSQL Connection - pgAdmin 4
pgAdmin 4

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.

Java JDBC PostgreSQL Connection - Create Table - Column Tab
Create Table – Column Tab

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

Java JDBC PostgreSQL Connection - INSERT Script
INSERT Script

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.

Java JDBC PostgreSQL Connection - View All Rows
View 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.

Java JDBC PostgreSQL Connection - Java Build Path
Java Build 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.

Download
You can download the source code of this example here: java-jdbc-postgresql-connection.zip.

Joel Patrick Llosa

I graduated from Silliman University in Dumaguete City with a degree in Bachelor of Science in Business Computer Application. I have contributed to many Java related projects at Neural Technologies Ltd., University of Southampton (iSolutions), Predictive Technologies, LLC., Confluence Service, North Concepts, Inc., NEC Telecom Software Philippines, Inc., and NEC Technologies Philippines, Inc. You can also find me in Upwork freelancing as a Java Developer.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

17 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Steven
Steven
5 years ago

Thanks for the post. Very helpful for me to get started!

Kristijan
Kristijan
5 years ago

Works like a charm, thank you!

prashant b jadhav
prashant b jadhav
5 years ago

java.lang.ClassNotFoundException: org.postgresql.Driver
java.lang.NullPointerException

Am getting this error even after configuring the build path

John
John
4 years ago

Something like what?

srinivasan
5 years ago

hi, i have done this already, when i try to connect with ip Addess of the machine in which i installed postgresql_db its not working properly. Can you explain how to configure for that?..

BingLi224
BingLi224
5 years ago

it’s unclear to prepare the postgresql jar file before importing into Eclipse.

Anyway, thanks for overall.

Jorabek
Jorabek
4 years ago

jar file idea intellj why

Santosh
4 years ago

PostgreSQL jdbc settings Works perfectly. Thanks for the clear explanation.

Sreenivasan Nagarajan
Sreenivasan Nagarajan
3 years ago

Very simple to follow and worked like a charm. Great job Joel. Thanks much.

Ravish Kumar Srivastava
Ravish Kumar Srivastava
2 years ago

Not working if the java code is deployed in Linux environment showing error table doesn’t exist, however same is working in the window machine.

Last edited 2 years ago by Ravish Kumar Srivastava
Back to top button