Connecting to PostgreSQL with Java

In this article, we will introduce how to connect to a PostgreSQL cluster using Java and some of the possible actions that you can perform on a PostgreSQL database. 

The easiest way to set up a Java client for PostgreSQL is using the JDBC interface. In order to use the interface, you will need to install the PostgreSQL JDBC driver and ensure that the jar archive is included in the class path

If you are using a build tool for your java application, you can use this to search for the latest jars and how to include them in your project. For example, for maven, you will need to add the following dependency:

<!-- https://mvnrepository.com/artifact/org.postgresql/postgresql -->
<dependency>
  <groupId>org.postgresql</groupId>
     <artifactId>postgresql</artifactId>
     <version>42.2.23</version>
</dependency>

Connecting to a cluster

import java.sql.*
import java.util.Arrays
import java.util.Properties

final String url = “jdbc:postgresql://<node ip address>:5432/postgres?targetServerType=primary”;
final Properties props = new Properties();
props.setProperty(“user”, “icpostgresql”);
props.setProperty(“password”, <password>);

try (Connection conn = DriverManager.getConnection(url, props)) {
System.out.println(connection.getMetaData().getDatabaseProductVersion();
} catch(SQLException e) {
System.out.println(“Error connecting to database “ + Arrays.toString(e.getStackTrace()));
}

where <node ip address> is the ip address of your master node, and <password> is the password for the icpostgresql user. You can find this information on the Connection Info page of your cluster.

This will connect you to the postgres database on your cluster, which is created when your cluster is initially provisioned. You can then process SQL statements using JDBC with the Statement interface.

Statement st = connection.createStatement();

Creating a table

st.execute("CREATE TABLE cities (name varchar(80), location point, population int);");

Note that by default the JDBC driver automatically commits database transactions, so a manual commit is not necessary.

Populating a table with rows

st.execute("INSERT INTO cities VALUES ('canberra', '(35.3, 149.1)', 395790)");

Querying a table

The data from a SQL query is stored in a ResultSet object, which can be accessed through  a cursor.

ResultSet rs = st.executeQuery(“SELECT * FROM cities”);
while (rs.next()) {
String name = rs.getString(“name”);
String location = rs.getString(“location”);
int population = rs.getInt(“population”);
System.out.println(name + “, “ + location + “, “ + population);
}

Releasing resources

Make sure to close your objects after you have finished to prevent any memory leaks. 

rs.close()
st.close()

Further Reading

The examples above only give basic use cases for JDBC.  For more information please see the official JDBC API documentation.

Need Support
Learn More

Already have an account?
Login to the Console

Experiencing difficulties on the website or console?
Status page for known incidents


Don’t have an account yet?
Sign up for a free trial

Why sign up?
To experience the ease of creating and managing clusters via the Instaclustr Console.