Database, JAVA, Programmimg Languages, Softwares, Tips

Connect to PostgreSQL database server via JDBC

This tutorial walks through process of connecting a Java application to a PostgreSQL database server from downloading JDBC driver to write code that makes the connection. Before begin, make sure you have a version of PostgreSQL database server installed either on your development computer or on a dedicated server. Code in this tutorial is tested with PostgreSQL 9.1.

1.    Download PostgreSQL JDBC driver

You can obtain the latest version of JDBC driver for PostgreSQL here. Currently there are two versions:

So let choose the one matches with your JDK version. The download is actually a jar file so you can put it directly into your application’s classpath. Name of jar files are postgresql-VERSION.jdbc3.jar and postgresql-VERSION.jdbc4.jar.

2.    JDBC database URL for PostgreSQL

The syntax of database URL for PostgreSQL looks like the following forms:

jdbc:postgresql:database

jdbc:postgresql://host/database

jdbc:postgresql://host:port/database

jdbc:postgresql://host:port/database?param1=value1&param2=value2&…

 

Where:

    • host: host name or IP address of the machine on which PostgreSQL server is running. If omitted, default is localhost.
    • port: port number on which the server is listening. If omitted, default is 5432.
    • database: name of the database to connect to.
    • param1=value1&param2=value2&…: specify additional connection parameters in pairs of key=value form. Most common parameters are user and password.

Here are some examples:

–          Connect to the database ProductDB on localhost:

          jdbc:postgresql:ProductDB

–          Connect to a remote PostgreSQL server on the host dbserver:

          jdbc:postgresql:dbserver:ProductDB

–          Using host name and port number explicitly:

          jdbc:postgresql:dbserver:5432:ProductDB

–          Specify user name and password for the connection:

          jdbc:postgresql:ProductDB&user=root&password=secret

See all connection parameters specific to PosgreSQL.

 

 

3.    Register JDBC driver for PostgreSQL Server and create connection

With JDBC 3.0 (JDK 5.0 and before) we need to register the driver as follows:

Class.forName("org.postgresql.Driver");

or

DriverManager.registerDriver(new org.postgresql.Driver());

However, since JDBC 4.0 (JDK 6.0 and later), the registration is not required. The JDBC driver manager can detect and load the appropriate driver when it is parsing the database URL.To establish a connection, call the method getConnection() of the DriverManager class and supply database URL and connection parameters. We can choose one in three versions of this method:

–          Version #1: getConnection(String url). For example:

String dbURL = "jdbc:postgresql:ProductDB?user=root&password=secret";
Connection conn = DriverManager.getConnection(dbURL);
–          Version #2: getConnection(String url, String user, String pass). For example:
String dbURL = "jdbc:postgresql://localhost:5432/ProductDB";
Properties parameters = newProperties();
parameters.put("user", "root");
parameters.put("password", "secret");
Connection conn = DriverManager.getConnection(dbURL, parameters);

4.    Example program

Following is a demo program which illustrates three different ways to make connections to a PostgreSQL server:

package net.codejava.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
/**
 * This program demonstrates how to make database connection to PostgreSQL
 * server using JDBC.
 *
 */
public class JdbcPostgresqlConnection {
    public static void main(String[] args) {
        // create three connections to three different databases on localhost
        Connection conn1 = null;
        Connection conn2 = null;
        Connection conn3 = null;
        try {
            // Connect method #1
            String dbURL1 = "jdbc:postgresql:ProductDB1?user=root&password=secret";
            conn1 = DriverManager.getConnection(dbURL1);
            if (conn1 != null) {
                System.out.println("Connected to database #1");
            }
            // Connect method #2
            String dbURL2 = "jdbc:postgresql://localhost/ProductDB2";
            String user = "root";
            String pass = "secret";
            conn2 = DriverManager.getConnection(dbURL2, user, pass);
            if (conn2 != null) {
                System.out.println("Connected to database #2");
            }
            // Connect method #3
            String dbURL3 = "jdbc:postgresql://localhost:5432/ProductDB3";
            Properties parameters = new Properties();
            parameters.put("user", "root");
            parameters.put("password", "secret");
            conn3 = DriverManager.getConnection(dbURL3, parameters);
            if (conn3 != null) {
                System.out.println("Connected to database #3");
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (conn1 != null && !conn1.isClosed()) {
                    conn1.close();
                }
                if (conn2 != null && !conn2.isClosed()) {
                    conn2.close();
                }
                if (conn3 != null && !conn3.isClosed()) {
                    conn3.close();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }
}
Advertisements

1 thought on “Connect to PostgreSQL database server via JDBC”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s