JDBC PostgreSQL Connection

In this tutorial we will connect a Java application to a PostgreSQL database using JDBC.

1. Prerequisites

1. Eclipse
2. JDBC PostgreSQL driver (https://jdbc.postgresql.org/download.html)
3. Running PostgreSQL database

2. Create standard Java project

1. In Eclipse, create a new Java project
2. Create a folder called “lib” in the root folder of the project
3. Copy the JDBC driver into that “lib” folder
4. Right click the JDBC jar file and select Build Path ⇒ Add to Build Path

3. JDBC PostgreSQL connection example

At first we need a wrapper class to connect to a selected database. Do not forget to adapt your package location if you use another location. You can use the follwing code:

package com.tutorialacademy.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class DBConnector 
{
	private Connection conn = null;
	
	public DBConnector( String dbUrl, String user, String pw, boolean ssl ) 
	{
		Properties properties = new Properties();
		properties.setProperty( "user", user );
		properties.setProperty( "password", pw );
		if( ssl ) properties.setProperty( "ssl", "true" );
		
		try 
		{
			conn = DriverManager.getConnection( dbUrl, properties );
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
	}
	
	public Connection getConnection()
	{
		return conn;
	}
	
	public void close() 
	{	
		try
		{
			if( conn != null )
			{
				conn.close();
			}
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
	}
}

In the constructor you pass the database URL, username, password and a boolean whether you are using SSL (encryption). Afterwards you can use the getConnection() to work with the established connection. Do not forget to call the close() function to free resources.

4. PostgreSQL CRUD operations

Now we can connect to the database and create tables, insert, update, delete and select data:

package com.tutorialacademy.jdbc;

import java.sql.ResultSet;
import java.sql.Statement;

public class PostgreSQLCRUD 
{
	DBConnector db = null;
	
	public PostgreSQLCRUD( String url, String user, String pw )
	{
		// no SSL
		db = new DBConnector( url, user, pw, false );
		System.out.println( "Opened database successfully!" );
	}
	
	public void createTable()
	{
		Statement stmt = null;
		try
		{
			stmt = db.getConnection().createStatement();
		
			String sql = "CREATE TABLE student " +
						 "(ID INT PRIMARY KEY     NOT NULL, " +
						 " NAME           TEXT    NOT NULL, " +
						 " AGE            INT     NOT NULL) "; 
		
			stmt.executeUpdate( sql );
			stmt.close();
		}
		catch ( Exception e ) 
		{
			System.err.println( e.getClass().getName()+": "+ e.getMessage() );
			System.exit( 0 );
		}
		
		System.out.println( "Table created successfully!" );
	}
	
	public void insertData()
	{
		Statement stmt = null;
		
		try 
		{
			String sql = null;
			stmt = db.getConnection().createStatement();
			
			sql = "INSERT INTO student ( ID, NAME, AGE ) " +
				  "VALUES (1, 'Mike', 20 );";
			stmt.executeUpdate( sql );
			
			sql = "INSERT INTO student ( ID, NAME, AGE ) " +
				  "VALUES (2, 'Bill', 22 );";
			stmt.executeUpdate( sql );

			sql = "INSERT INTO student ( ID, NAME, AGE ) " +
				  "VALUES (3, 'James', 25 );";
			stmt.executeUpdate( sql );
			
			stmt.close();
		}
		catch ( Exception e )
		{
			System.err.println( e.getClass().getName()+": "+ e.getMessage() );
			System.exit( 0 );
		}
		
		System.out.println( "Insert successfully!" );
	}
	
	public void updateData()
	{
		Statement stmt = null;
		try 
		{
			stmt = db.getConnection().createStatement();
			
			String sql = "UPDATE student set age = 33 where ID = 1;";
			stmt.executeUpdate( sql );
			stmt.close();
		}
		catch ( Exception e ) 
		{
			System.err.println( e.getClass().getName()+": "+ e.getMessage() );
			System.exit( 0 );
		}
		
		System.out.println( "Update successfully!" );
	}
	
	public void deleteData()
	{
		Statement stmt = null;
		try 
		{
			stmt = db.getConnection().createStatement();
			
			String sql = "DELETE FROM student WHERE ID=2;";
			stmt.executeUpdate( sql );
			stmt.close();
		}
		catch ( Exception e ) 
		{
			System.err.println( e.getClass().getName()+": "+ e.getMessage() );
			System.exit( 0 );
		}
		
		System.out.println( "Delete successfully!" );
	}
	
	public void selectData()
	{
		Statement stmt = null;
		int rows = 0;

		try 
		{
			stmt = db.getConnection().createStatement();
			
			ResultSet rs = stmt.executeQuery( "SELECT * FROM student;" );
			
			while ( rs.next() ) 
			{
				int id = rs.getInt( "id" );
				String name = rs.getString( "name" );
				int age = rs.getInt("age");
				System.out.println( "ID = " + id );
				System.out.println( "NAME = " + name );
				System.out.println( "AGE = " + age );
				System.out.println();
				rows++;
			}
			
			rs.close();
			stmt.close();
		}
		catch ( Exception e ) 
		{
			System.err.println( e.getClass().getName()+": "+ e.getMessage() );
			System.exit( 0 );
		}
		
		System.out.println( "Selected " + rows + " rows successfully!" );
	}
	
	public void close()
	{
		db.close();
		System.out.println( "Closed DBConnector!" );
	}
}

5. Execute the code

Here is a Main function to test and manipulate the JDBC PostgreSQL Connection. Do not forget to replace the url, user and password with your own credentials.

package com.tutorialacademy.jdbc;

public class RunPostgreSQLCRUD 
{
	public static void main(String[] args) 
	{
		String url = "jdbc:postgresql://localhost/myDBName";
		String user = "john";
		String pw = "secret";
		
		PostgreSQLCRUD crud = new PostgreSQLCRUD( url, user, pw );
		
		crud.createTable();
		
		crud.insertData();
		
		crud.updateData();
		
		crud.deleteData();
		
		crud.selectData();
		
		crud.close();
	}

}

You should get the following output:

Opened database successfully!
Table created successfully!
Insert successfully!
Update successfully!
Delete successfully!
ID = 1
NAME = Mike
AGE = 33

ID = 3
NAME = James
AGE = 25

Selected 2 rows successfully!
Closed DBConnector!

If you have problems or questions feel free to comment and ask.