How to connect to remote Postgresql in Java

In the previous post I explained about connecting to remote PostgreSQL database with SSH command in Linux. This post is dedicated to do make remote connection with do some programming in Java which is quite simple.

In order to connect to PostgreSQL in Java despite the fact of connection type you need JDBC driver which you can download it from this link. You should keep note that the version of JDBC is related to your PostgreSQL version. For instance, for version 8.X you probably need JDBC version 3, however, for upper version example, 9.X you need JDBC version 4.

So what is JDBC?

JDBC is a package/library or you also can say driver that makes connecting to the database and many other tasks easy for you with just few lines of command. Read more here.

For better demonstration just take a look at following example,

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

	public class Test {

	public static void main(String args[]) {
	Connection conn = null;
	String dbuser = "DBUser";
	String dbpass = "DBPassword";
	String dburl = "jdbc:postgresql://localhost:5432/TestDB";
	try {
	conn = getConnection(conn, dburl, dbuser, dbpass);
	// Put SQL statement here
	String selectStmt = "SELECT * FROM tbl_test";
	PreparedStatement prepStmt = null;
	prepStmt = conn.prepareStatement(selectStmt);
	ResultSet rs = prepStmt.executeQuery();
	while (rs.next()) {
	String pkid = rs.getString("pkid");
	System.out.println("PKID ==>" + pkid);
	String name = rs.getString("name");
	System.out.println("NAME ==>" + name);
	}
	} catch (Exception e) {
	e.printStackTrace();
	System.err.println(e.getClass().getName() + ": " + e.getMessage());
	System.exit(0);
	}
	System.out.println("Opened database successfully");
	}

	private static Connection getConnection(Connection conn, String dburl, String dbuser, String dbpass) throws InstantiationException, IllegalAccessException {
	if (conn == null) {
	try {
	Class.forName("org.postgresql.Driver").newInstance();
	} catch (ClassNotFoundException ex) {
	ex.printStackTrace();
	System.exit(0);
	}
	try {
	conn = DriverManager.getConnection(dburl, dbuser, dbpass);
	} catch (SQLException ex) {
	ex.printStackTrace();
	}
	}
	return conn;
	}
	}

view raw Test.java hosted with ❤ by GitHub

The is very clear and understandable and basically there is nothing to mention. You just only note about database name, username, and password of your database which in this example db name is TestDB, username is DBUser, and password is DBPassword. The rest is very clear.

Now if you attempt to compile the following code, most probably you will get similar error like this,

java.lang.ClassNotFoundException: org.postgresql.Driver

The root of the problem is because of lack of adding/introducing the jar file on compilation and runtime. To overcome with such a problem compile and run your application like below

javac -cp .:[Path to your JDBC driver] [Your java filename]

Example,

javac cp .:/usr/java/jboss/server/default/lib/postgresql-9.2-1002.jdbc4.jar Example.java

For runtime,

java -cp .:[Path to your JDBC driver] [You class name without '.class']
java -cp .:/usr/java/jboss/server/default/lib/postgresql-9.2-1002.jdbc4.jar Static

For those that do not want to write this command every time on compile or runtime they either can utilize Maven, Ant, or simply shell script if your project is not very big. Of course my recommendation is to use Ant or Maven.

You also can download the source code and shell script from this link.