Jdbc Connect To PostgreSQL

Here we show an example of how to connect to A PostgreSQL database using JDBC. To work along with this example you will need to download the PostgreSQL driver.

Alternatively you can use Maven to include the PostgreSQL driver.

1
2
3
4
5
<dependency>
   <groupId>postgresql</groupId>
   <artifactId>postgresql</artifactId>
   <version>9.1-901-1.jdbc4</version>
</dependency>

Load PostgreSQL JDBC Driver

The first step is to load the PostgreSQL JDBC driver as shown in the below example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
public void loadDriver() {
        System.out.println("Looking for PostgreSQL Driver on classpath.");

        try {
            Class.forName("org.postgresql.Driver");
            System.out.println("PostgreSQL JDBC Driver Loaded");
        } catch (ClassNotFoundException e) {
            System.out.println("No PostgreSQL driver found on classpath.  Please add PostgreSQL Driver to classpath.");
            throw new RuntimeException(e);
        }
 }

Connect To PostgreSQL

Once the driver is loaded the next step is to connect to PostgreSQL via the loaded driver.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
public void connect(final String userName, final String password) {

        try {
            System.out.println(String.format("Attempting to connect to PostgreSQL Database with user: %s and password: %s ",
                    userName, password));

            connection = DriverManager
                    .getConnection("jdbc:postgresql://127.0.0.1:5432/javaCore",
                            userName, password);

        } catch (SQLException e) {
            System.out.println("Error unable to connect. Check console stack trace.");
            throw new RuntimeException(e);
        }
}

JDBC Connection Cleanup

Once finished with the connection it is important to close it to free the allocated resources.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
public static void main(String[] args) {
        PostgreSqlConnectionExample example = new PostgreSqlConnectionExample();

        try {
            example.loadDriver();
            example.connect("root", "root");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            example.closeConnection();
        }
}

JDBC PostgreSQL Connection Example Code

 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
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
package com.stephenenright.tutorials.javajdbc.connection;

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

public class PostgreSqlConnectionExample {

    private Connection connection = null;

    public static void main(String[] args) {
        PostgreSqlConnectionExample example = new PostgreSqlConnectionExample();

        try {
            example.loadDriver();
            example.connect("root", "root");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            example.closeConnection();
        }
    }

    public void loadDriver() {
        System.out.println("Looking for PostgreSQL Driver on classpath.");

        try {
            Class.forName("org.postgresql.Driver");
            System.out.println("PostgreSQL JDBC Driver Loaded");
        } catch (ClassNotFoundException e) {
            System.out.println("No PostgreSQL driver found on classpath.  Please add PostgreSQL Driver to classpath.");
            throw new RuntimeException(e);
        }
    }

    public void connect(final String userName, final String password) {

        try {
            System.out.println(String.format("Attempting to connect to PostgreSQL Database with user: %s and password: %s ",
                    userName, password));

            connection = DriverManager
                    .getConnection("jdbc:postgresql://127.0.0.1:5432/javaCore",
                            userName, password);

        } catch (SQLException e) {
            System.out.println("Error unable to connect. Check console stack trace.");
            throw new RuntimeException(e);
        }
    }

    public void closeConnection() {
        if (connection != null) {
            System.out.println("Connection to database successful.");

        } else {
            System.out.println("Failed to connect to database");
        }


        try {
            connection.close();
        } catch (Exception e) {
            System.err.println("Warning connection not closed");
        }
    }

    public Connection getConnection() {
        return connection;
    }
}

Run The Example

The example can be executed from the IDE of your choosing. To run from the command line you can use a similar command to that shown below:

1
java -cp ${PATH_TO_JAR}/Oracle-connector-java-5.1.8-bin.jar;${PATH_TO_CLASSES_DIR}  com.github.senrightlearn.java.jdbc.connection.PostgreSqlConnectionExample

Code Output

1
2
3
4
Looking for PostgreSQL Driver on classpath.
PostgreSQL JDBC Driver Loaded
Attempting to connect to PostgreSQL Database with user: root and password: password
Connection to database successful.

Conclusion

In this tutorial we looked at how to connect to Postgresql using JDBC, we also looked at the main steps when working with Postgresql with JDBC, such as, loading the driver, connecting to Postgresql, and cleaning up the connection when we are finished.