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:
- 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¶m2=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¶m2=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);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. * @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(); } } }}
Pingback: JDBC’s database connection URLs for common databases - Tech Blicks