View RSS Feed

My Java Tips

Using PostgreSQL JDBC driver

Rate this Entry
by , 11-12-2011 at 05:45 PM (1407 Views)
PostgreSQL is a popular open source object-relational database management system that is being used for small and medium sized applications. We have two options to connect to PostgreSQL. One is to use odbc/jdbc connection and the other is to use PostgreSQL JDBC driver. In this post, I will write about how to use PostgreSQL JDBC driver to connect to PostgreSQL database.


First of all, you will need PostgreSQL database driver. It can be downloaded from
http://jdbc.postgresql.org/download.html

The driver provides are reasonably complete implementation of the JDBC 3 specification in addition to some PostgreSQL specific extensions.

Once you have the jar file, include it into class path.

For demonstrating an example, I created a database in PostgreSQL called testdb. A table named “languages” is also created with following schema:

id (integer)
name (text)
comments (text)

I want to connect to the database using Postgres jdbc driver and want to display the contents of the languages table. Lets see how to do that:

Java Code:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Db {

	static String dbname = "testdb";
	static String dbuser = "postgres";
	static String dbpass = "postgres";
	static String dbhost = "localhost";
	static String dbport = "5432";
	static String dbtable = "languages";

	public static void main(String[] args) throws Exception {
		Connection conn;

		Class.forName("org.postgresql.Driver").newInstance();
		String url = "jdbc:postgresql://" + dbhost + ":" + dbport + "/" + dbname;
		System.out.println("getConnection: url="+url);
		conn = DriverManager.getConnection(url, dbuser, dbpass);
		Statement s = conn.createStatement();
		String query = "select * from programming";
		ResultSet rs;

		rs = s.executeQuery(query);
		while(rs.next())
		{
			System.out.print(rs.getString("name"));
			System.out.println(" - " + rs.getString("comments"));
		}
		}

}
Output:


getConnection: url=jdbc:postgresql://localhost:5432/testdb
Java - version 6
C++ - Borland
VB – Microsoft

So desired output is displayed. Driver is loaded using Class.forName statement and then connection is established using DriverManager.getConnection(…) which takes connection parameters. After that we created the Statement and executed the query. ResultSet is used to move through and the outcome of the executed query.

If Postgres jdbc driver is not included in the class path, you will get following exception:


Exception in thread "main" java.lang.ClassNotFoundException: org.postgresql.Driver

I hope this helps.

Submit "Using PostgreSQL JDBC driver" to Facebook Submit "Using PostgreSQL JDBC driver" to Digg Submit "Using PostgreSQL JDBC driver" to del.icio.us Submit "Using PostgreSQL JDBC driver" to StumbleUpon Submit "Using PostgreSQL JDBC driver" to Google

Categories
JDBC

Comments