Connect 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:
1 2 3 4 5 6 |
<!-- 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
1 2 3 |
import java.sql.* import java.util.Arrays import java.util.Properties |
1 2 3 4 |
final String url = "jdbc:postgresql://<node ip address>:5432,<node ip address>:5432/postgres?targetServerType=primary"; final Properties props = new Properties(); props.setProperty("user", "icpostgresql"); props.setProperty("password", <password>); |
1 2 3 4 5 |
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> are the ip addresses of your nodes, 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.
1 |
Statement st = connection.createStatement(); |
Creating a table
1 |
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
1 |
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.
1 2 3 4 5 6 7 |
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 preventing any memory leaks.
1 2 |
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.