Home » Enterprise Java » spring » JDBC » Amazon Redshift JDBC Connection Example

About Anmol Deep

Anmol Deep is a senior engineer currently working with a leading identity security company as a Web Developer. He has 8 years of programming experience in Java and related technologies (including functional programming and lambdas) , Python, SpringBoot, Restful architectures, shell scripts, and databases relational(MySQL, H2) and nosql solutions (OrientDB and MongoDB). He is passionate about researching all aspects of software development including technology, design patterns, automation, best practices, methodologies and tools, and love traveling and photography when not coding.

Amazon Redshift JDBC Connection Example

In this tutorial, we will learn about how to set up a JDBC connection to an Amazon Redshift cluster. We will also discuss and analyze a coding example where we connect to an Amazon Redshift Cluster via JDBC and perform CRUD operations on a table.

1. What is Amazon Redshift?

Amazon Redshift is a fully managed data warehouse service in the cloud. It is based on PostgreSQL 8.0.2. However, there a number of important differences between the two.

Amazon Redshift comes with the following set of rich features:

  • Faster performance
  • Easy to set up, deploy, & manage
  • Cluster management
  • Cost-effective
  • Quickly scalable (Petabyte-scale data warehousing)
  • Amazon S3 Data Lake Query Support

First-time users of Redshift are recommended to go through the sections on the Home Page of Amazon Redshift.

1.1. Databases and Users

When we provision a Redshift cluster, a database is also created which can be utilised to load data and run queries on the loaded data. We can also create additional databases by following the Create Database section of the Amazon Redshift Database developers guide.

At the time of provisioning a Redshift cluster, we need to define a master user. This user is a superuser and has access to all the databases created within the cluster and also has the privileges to create additional Superusers and Users.

2. JDBC Connection to Redshift

Since Redshift is a PostgreSQL variant, Amazon previously recommended using the JDBC4 Postgresql driver. Amazon also provides a Redshift specific JDBC driver and now recommends to use the same going forward.

In this tutorial, we will use the Amazon Redshift JDBC driver instead of the PostgreSQL driver.

2.1. Redshift Cluster JDBC URL

To establish a JDBC connection to a redshift cluster, we need the cluster JDBC URL that can be found on the Amazon Redshift console, on the cluster’s details page under the Properties tab.

The red outlined boxes in the below image will help you locate the JDBC URL on the Amazon Redshift Console.

Redshift Cluster JDBC URL

The format of the JDBC URL for Amazon Redshift looks like this:

jdbc:redshift://endpoint:port/database-name

The below table specifies the fields used above with their meaning.

FieldInformation
jdbcThe connection protocol.
redshiftThe subprotocol that specifies the use of redshift driver to connect to the database.
endpointThe Amazon Redshift cluster’s endpoint.
portThe port number which is LISTENING for an incoming connection.
databaseThe database created on the cluster, to which we are going to connect to.
JDBC URL Fields

The following is a sample JDBC URL of a database in Amazon Redshift Cluster

jdbc:redshift://adee-cluster.c9ld7lgff4o4.us-east-2.redshift.amazonaws.com:5555/dev

2.2. JDBC Driver for Amazon Redshift

First things first. Download the ZIP of the latest JDBC 4.2-compatible driver (version 1.2.45) and the dependent libraries.

Next, extract the contents of the ZIP file to a directory. This directory should contain the JDBC driver as well as the dependent jar files.

The class required to load this driver in the Java Runtime is com.amazon.redshift.jdbc42.Driver.

Some points to be noted here are:

  • Only the JDBC driver needs to be in the CLASSPATH of our Java application and the driver Manifest file will take care of all the other dependent libraries.
  • Java Runtime Environment (JRE 8.0 and above) is the minimum requirement to use the Amazon Redshift JDBC driver within an application to connect to the Redshift cluster.

2.3. Setting up the Connection

In this section, we will use the JDBC URL and the Amazon Redshift JDBC 4.2 Driver to get a connection to our Redshift cluster.ConnectionManager.java

public class ConnectionManager {

	private static final String JDBC_URL = 
		"jdbc:redshift://adee-cluster.c9ld7lgff4o4.us-east-2."
		+ "redshift.amazonaws.com:5555/dev";
	private static final String USERNAME = "awsuser";
	private static final String PASSWORD = "Qwerty1234";
	private static final String CLASS_NAME = "com.amazon.redshift.jdbc42.Driver";

	private Connection connection = null;

	public void connect() {
		// Dynamically load driver at runtime.
		try {
			Class.forName(CLASS_NAME);
		} catch (ClassNotFoundException e) {
			System.out.println("JDBC Driver class could not loaded");
			System.out.println(e.getMessage());
		}
		Properties properties = getPropertiesForDriverManager();
		try {
			System.out.println("Connecting to the database...");
			this.connection = DriverManager.getConnection(JDBC_URL, properties);
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
	}

	private Properties getPropertiesForDriverManager() {
		Properties props = new Properties();
		props.setProperty("user", USERNAME);
		props.setProperty("password", PASSWORD);
		return props;
	}
}

In the above code snippet, we have dynamically loaded the Redshift JDBC driver at runtime and used the standard DriverManager to get a connection to our Redshift cluster.

3. CRUD Operations

Next, we will perform basic CRUD operations using the Connection instance obtained in the previous section. We will write code for the following operations on the Amazon Redshift database:

  • Create a new table
  • Insert rows into the newly created table
  • Query the table for data
  • Delete the rows from the table and delete the table
  • Query system tables in the database

3.1. CRUD Operations Example

Now let’s see the coding examples to execute CRUD operations on our Redshift database.

We will use the standard java.sql PreparedStatement and ResultSet throughout this coding example.RedShiftCRUDOps.java

public class RedShiftCRUDOps {

	private Connection connection = null;
	private Statement statement = null;
	private PreparedStatement pStatement = null;
	private ResultSet rs = null;

	private static final String SQL_SELECT =
		"select * from public.employee;";
	private static final String SQL_INSERT =
		"INSERT INTO EMPLOYEE (ID, NAME, AGE) VALUES (?,?,?)";
	private static final String SQL_CREATE = 
		"create table employee(id integer, name varchar(25), age integer);";
	private static final String SQL_DELETE =
		"delete from employee;";
	private static final String SQL_DROP =
		"drop table employee;";

	private static final String SYS_TABLES_SELECT = 
		"select * from information_schema.tables;";

	public RedShiftCRUDOps(Connection connection) {
		this.connection = connection;
	}

	public void createTable() {
		System.out.println("Creating Table Employee..");
		try {
			statement = connection.createStatement();
			statement.executeUpdate(SQL_CREATE);
			System.out.println("Employee Table Created.");
		} catch (SQLException ex) {
			System.out.println(ex.getMessage());
		} finally {
			closeStatement();
		}
	}

	public void insertRows() {
		System.out.println("Inserting Rows in Table Employee..");
		try {
			pStatement = connection.prepareStatement(SQL_INSERT);
			pStatement.setInt(1, 1);
			pStatement.setString(2, "Alex");
			pStatement.setInt(3, 25);
			pStatement.executeUpdate();

			pStatement.setInt(1, 2);
			pStatement.setString(2, "Andy");
			pStatement.setInt(3, 30);
			pStatement.executeUpdate();

			pStatement.setInt(1, 3);
			pStatement.setString(2, "Robert");
			pStatement.setInt(3, 15);
			pStatement.executeUpdate();
			System.out.println("Rows Inserted in Table Employee");
		} catch (SQLException ex) {
			System.out.println(ex.getMessage());
		} finally {
			closeStatement();
		}
	}

	public void queryTable() {
		System.out.println("Querying Table Employee..");
		try {
			statement = connection.createStatement();
			rs = statement.executeQuery(SQL_SELECT);
			if (rs != null) {
				while (rs.next()) {
					System.out.print("Employee ID: " + 
						rs.getInt("id"));
					System.out.print(",Employee Name: " +
						rs.getString("name"));
					System.out.print(", Employee Age: " + 
						rs.getInt("age"));
					System.out.println();
				}
			}
		} catch (SQLException ex) {
			System.out.println(ex.getMessage());
		} finally {
			closeResultSet();
			closeStatement();
		}
	}

	public void deleteDataAndTable() {
		System.out.println("Deleting Data in Table Employee..");
		try {
			statement = connection.createStatement();
			int rows = statement.executeUpdate(SQL_DELETE);
			System.out.println(rows);
			System.out.println("Deleted Rows in Table Employee " 
				+ rows);
			System.out.println("Dropping Table Employee..");
			statement.executeUpdate(SQL_DROP);
			System.out.println("Dropped Table Employee..");
		} catch (SQLException ex) {
			System.out.println(ex.getMessage());
		} finally {
			closeStatement();
		}
	}

	public void querySystemTables() {
		try {
			System.out.println("Listing system tables...");
			statement = connection.createStatement();
			rs = statement.executeQuery(SYS_TABLES_SELECT);
			if (rs != null) {
				while (rs.next()) {
					System.out.print("Table_Catalog: " +
						rs.getString("table_catalog"));
					System.out.print(", Schema: " +
						rs.getString("table_schema"));
					System.out.print(", Table_Name: " +
						rs.getString("table_name"));
					System.out.println();

				}
			}
		} catch (SQLException ex) {
			System.out.println(ex.getMessage());
		} finally {
			closeResultSet();
			closeStatement();
		}
	}
	
	private void closeResultSet() {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	private void closeStatement() {
		if (pStatement != null) {
			try {
				pStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

Lastly, we have the Launcher class with the main method to start and run the application.It connects to the database and uses that connection to perform the above described operations.Launcher.java

public class Launcher {

	public static void main(String[] args) {
		ConnectionManager connMgr = new ConnectionManager();
		connMgr.connect();
		RedShiftCRUDOps ops = new RedShiftCRUDOps(connMgr.getConnection());
		ops.createTable();
		ops.insertRows();
		ops.queryTable();
		ops.deleteDataAndTable();
		ops.querySystemTables();
	}
}
Output
Connecting to the database...
Creating Table Employee..
Employee Table Created.
Inserting Rows in Table Employee..
Rows Inserted in Table Employee
Querying Table Employee..
Employee ID: 1,Employee Name: Alex, Employee Age: 25
Employee ID: 2,Employee Name: Andy, Employee Age: 30
Employee ID: 3,Employee Name: Robert, Employee Age: 15
Deleting Data in Table Employee..
Deleted Rows in Table Employee 3
Dropping Table Employee..
Dropped Table Employee..
Listing system tables...
Table_Catalog: dev, Schema: pg_internal, 
	Table_Name: redshift_auto_health_check_330039
Table_Catalog: dev, Schema: pg_catalog,
	Table_Name: pg_default_acl
Table_Catalog: dev, Schema: pg_catalog,
	 Table_Name: pg_conf
Table_Catalog: dev, Schema: pg_catalog,
	Table_Name: pg_library

4. Summary

In this tutorial, we learned how to set-up a JDBC connection to the Amazon Redshift Cluster.

Firstly, we introduced the Amazon Redshift cluster. Next, we discussed the Database and the Users available when we provision a cluster.

After that, we saw how to obtain the database connection string or the JDBC URL for our database and where to download the JDBC driver from, to connect to our database.

Lastly, we set-up a connection and saw a coding example to perform some basic operations on our database.

5. Download the source code

As always, all the code examples provided in this tutorial are available in a Java project and should be easy to download, import and run.

Download
You can download the full source code of this example here: Amazon Redshift JDBC Connection Example

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

 

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

 

and many more ....

 

Receive Java & Developer job alerts in your Area

I have read and agree to the terms & conditions

 

Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments