Home » Core Java » sql » Java JDBC PostgreSQL Connection Example

About Joel Patrick Llosa

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 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.

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.
(+1 rating, 1 votes)
13 Comments Views Tweet it!

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

 

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

 

and many more ....

 

Receive Java & Developer job alerts in your Area

 

13
Leave a Reply

avatar
5 Comment threads
8 Thread replies
1 Followers
 
Most reacted comment
Hottest comment thread
8 Comment authors
JorabekJohnBingLi224srinivasanprashant b jadhav Recent comment authors
  Subscribe  
newest oldest most voted
Notify of
Steven
Guest
Steven

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

Kristijan
Guest
Kristijan

Works like a charm, thank you!

prashant b jadhav
Guest
prashant b jadhav

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

Am getting this error even after configuring the build path

srinivasan
Guest

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
Guest
BingLi224

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

Anyway, thanks for overall.