Java Connect to PostgreSQL database server with JDBC

This JDBC tutorial walks you 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.

Table of content:

1. 1. Download PostgreSQL JDBC driver
   2. JDBC database URL for PostgreSQL
   3. Register JDBC driver for PostgreSQL Server and create connection
   4. Example program

1.    Download PostgreSQL JDBC driver

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

• • JDBC3 Postgresql Driver: for JDK 1.4 and JDK 1.5
  • JDBC4 Postgresql Driver: for JDK 1.6 and JDK 1.7

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/ProductDB";
String user = "root";
String pass = "secret";
Connection conn = DriverManager.getConnection(dbURL, user, pass);

-          Version #3: getConnection(String url, Properties parameters). For example:

String dbURL = "jdbc:postgresql://localhost:5432/ProductDB";
Properties parameters = new Properties();
parameters.put("user", "root");
parameters.put("password", "secret");
Connection conn = DriverManager.getConnection(dbURL, parameters);

4.    Java Code Example to connect to PostgreSQL database

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.
 * @author www.codejava.net
 *
 */
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();
			}
		}
	}
}

That's how to connect a  Java program to PostgreSQL database. To see the code in action, I recommend you to watch the video below:

JDBC API References:

• DriverManager Class Javadoc
• Connection Interface Javadoc

Related JDBC Tutorials:

• JDBC CRUD Tutorial
• JDBC Transaction Tutorial
• How to Read Database Meta Data in JDBC
• How to call stored procedure with JDBC

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He began programming with Java back in the days of Java 1.4 and has been passionate about it ever since. You can connect with him on Facebook and watch his Java videos on YouTube.

Attachments:

JdbcPostgresqlConnection.java

[Example program]

1 kB