Connecting Java JDBC to PostgreSQL

Written by Data Pilot

October 07, 2019

PostgreSQL

Introduction

In this article we will learn how to connect the JDBC to a PostgreSQL database from a java application.

Prerequisites

Ensure that PostgreSQL server is properly installed, configured and running on the background.

For Linux and Windows systems you can download PostgreSQL here

Ensure that ECLIPSE IDE is properly installed and configured in your system.

For Linux and Windows system you can download Eclipse IDE here

Basic Java syntax knowledge.

Creating a Sample Database

Before we proceed with our tutorial, let us create a sample data that we can use later as we progress.

First, we login to our PostgreSQL server and create a database.

1	CREATE DATABASE testdatabase;

After creating the database then we can connect to it using the PostgreSQL meta-command \c followed by the database name, like so: \c testdatabase;.

Then we create a table named tbluser using the following command.

1
2
3
4
5

CREATE TABLE tbluser (
id INT PRIMARY KEY,
username TEXT NOT NULL,
password TEXT NOT NULL
);

We can now perform an INSERT operation to put some records in there:

1 2	INSERT INTO tbluser(id,username,password) VALUES (1, 'rommel2019', '123456');

Output:

1
2
3
4

id | username | password
----+------------+----------
1 | rommel2019 | 123456
(1 ROW)

Downloading the JDBC Driver

In order for us to connect our java application to the target PostgreSQL database, we need to have the JDBC driver.

We can download the latest version on the official website of PostgreSQL under the download section.
Or we can reference the driver using the MAVEN tool of ECLIPSE IDE.

Connecting to the PostgreSQL database server

In this section, we will now connect a java application to PostgreSQL database.

Creating a Java Project

First, we need to create a new project in ECLIPSE IDE using the following steps in sequence:

Click File, then select Project... on the sub-menu.

image show how to create new java project

Then select Java Project in the available options under the Select a wizard window. Then click the Next> button to proceed on the next step.

Image shows selecting java project

We then provide a meaningful name that defines our java project.

Then click Finish

Manual Import of PostgreSQL JDBC driver

Now that we have a java project let us now import the necessary driver manually. To do this we follow these steps in sequence.

First, select the java project that we created earlier then press ALT + ENTER on your keyboard to open up the properties window.
Then click Java Build Path on the left side pane of the properties window.
Then click the Add External JARS... option, to open up the JAR Selection window then look for the PostgreSQL JDBC driver, as of this writing the latest version of the driver is “postgresql-42.2.8.jar”.
Click Open to select the driver.
Click the Apply and Close button to close the properties window.

Creating a Java Class.

Now that we have downloaded the PostgreSQL JDBC driver, we are now ready to connect to the PostgreSQL database via java application.

First, we make a new class inside our java application. To do this follow the steps in sequence:

image shows creating a new java class

Then provide the following details as shown in the image:

image showing details for the new java class

After providing the details, we click the Finish button.

Connecting to PostgreSQL server via Java Application.

We are now ready, coding our connection using the Java Class that we have created in the previous section.

First, we declare variables that will hold the values of the connection string, the name of the user and the corresponding password. To do this we use the following code:

1
2
3
4
5
6
7
8

// connection string
private final String conUrl = "jdbc:postgresql://localhost/testdatabase";

// the name of the user
private final String username = "postgres";

// the password of the user
private final String password = "1234";

After we determine these three important details, we can now use them to connect to the server with the help of the getConnection() method of the class DriverManager.

Below java code will create a conn() function that will call for the details that we created earlier and use them to connect to PostgreSQL database server.

1
2
3
4
5
6
7
8
9
10
11
12

public Connection conn() {
Connection conn = null;

try {
conn = DriverManager.getConnection(conUrl, username, password);
System.out.println("You are now connected to the server");
}catch(SQLException e){
System.err.println(e.getMessage());
}

return conn;
}

Now to test our code we need to call the function above within the entry point of our java application, the function main().

We use the following code within the function main() code block.

1
2
3
4
5

public static void main(String[] args) {

JdbcDemo app = new JdbcDemo();
app.conn();
}

Then we press CTRL + F11 our keyboard.

You should see something like this in your console.

image shows the result of the java application

Basic Java JDBC PostgreSQL query

The following code will perform a basic SELECT operation to verify that our application is properly connecting to PostgreSQL database server.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

public void getUser() {
String SQL = "SELECT * FROM tbluser";

try(Connection conn = conn();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(SQL)){
displayUser(rs);
}catch (SQLException e) {
System.out.println(e.getMessage());
}
}

public void displayUser (ResultSet rs) throws SQLException {
while(rs.next()) {
System.out.println(rs.getString("username") + "\t"
+ rs.getString("password"));
}
}

Conclusion

In this tutorial we show you show to connect Java application to PostgreSQL database.

The Code

Below is the entire working code that we used in this article.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

package com.pgjdbc.demo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JdbcDemo {

private final String conUrl = "jdbc:postgresql://localhost/testdatabase";
private final String username = "postgres";
private final String password = "1234";

public Connection conn() {
Connection conn = null;

try {
conn = DriverManager.getConnection(conUrl, username, password);
System.out.println("You are now connected to the server");
}catch(SQLException e){
System.err.println(e.getMessage());
}

return conn;
}

public static void main(String[] args) {

JdbcDemo app = new JdbcDemo();
app.getUser();
}

public void getUser() {
String SQL = "SELECT * FROM tbluser";
try(Connection conn = conn();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(SQL)){
displayUser(rs);
}catch (SQLException e) {
System.out.println(e.getMessage());
}
}

public void displayUser (ResultSet rs) throws SQLException {
while(rs.next()) {
System.out.println(rs.getString("username") + "\t"
+ rs.getString("password"));
}
}

}

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started