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
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.
You can download the source code of this example here: java-jdbc-postgresql-connection.zip.