Java and Postgres Connection
Introduction
Java Database Connectivity, or JDBC, is an API, or application programming interface, written for JAVA programming language. In order to enable a JAVA and Postgres connection, the JDBC driver must be used for a JAVA application to interact with the Postgres database. The JDBC driver both establishes a connection to the database and also implements the protocol for transferring data. This tutorial will explain the JAVA and Postgres connection, how to set up the Java environment and download the Postgres JDBC driver.
Prerequisite
PostgreSQL must be properly installed and configured on the local system.
Internet access for downloading the necessary files.
ECLIPSE IDE must be properly installed and configured on the local system. The ECLIPSE IDE can be downloaded here.
JAVA JDK version 1.8 must be installed on the local system. Execute the following command to confirm the currently installed version:
1 2 3 4 | C:\user>java -version java version "1.8.0_221" Java(TM) SE Runtime Environment (build 1.8.0_221-b11) Java HotSpot(TM) 64-Bit Server VM (build 25.221-b11, mixed mode) |
Download Postgres JDBC driver
To enable a connection between the PostgreSQL database and the JAVA application, an intermediary driver must be used to link the two technologies. The latest version of the JDBC driver can be downloaded from the postgresql.org website.
Prepare Postgres Database
This section will cover setting up the Postgres database that will be used in the examples in this tutorial.
Setting up database via command line
The Postgres database will be set up via the command line.
First, log in to the Postgres shell and execute the following command to create a database named “Flowers:”
1 | CREATE DATABASE flowers |
Next, create a table named “Flowers” with the following structure in the new database:
1 | CREATE TABLE flower (id INT PRIMARY KEY, name VARCHAR(100), price INT); |
Now execute the following command to insert some sample records into the Flower table:
1 2 3 4 5 6 7 | INSERT INTO flower (id, name, price) VALUES (1,'rose',250), (2,'tulip',150), (3,'snowdrop',300), (4,'Poppy',250), (5,'Bluebell',100); |
The results should produce a table with a following data:
1 2 3 4 5 6 7 8 | id | name | price ----+----------+------- 1 | rose | 250 2 | tulip | 150 3 | snowdrop | 300 4 | Poppy | 250 5 | Bluebell | 100 (5 rows) |
JAVA JDBC Postgres Connection
With the Postgres database setup, this section will explain how to connect the JAVA application to Postgres.
Create JAVA application
Following is an explanation of how to create a JAVA application with ECLIPSE IDE:
- First, open ECLIPSE, create a new JAVA project and name it ‘postgresjava’. This should resemble the following image:
-img src=”https://t.gyazo.com/teams/500rockets/efd9ab36d385fff0fa70ad6a08bbec00.png” width = “632” height = “456”-
- Next, import the JAR file, downloaded in the previous section, into the ECLIPSE libraries. This should resemble the following screenshot:
-img src=”https://t.gyazo.com/teams/500rockets/8093d7dbbb4252924835257aaf352a34.png” width = “632” height = “456”-
NOTE: Importing the JAR files is essential as it allows for referencing the library in the application.
- Now create a new java class named _PostgresJava.java_and append the following JAVA code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | package postgresjava; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class PostgresJava { // we define the attribute of the PostgresJava class using the following details. private final String dburl = "jdbc:postgresql://localhost/flowers"; private final String dbuser = "postgres"; private final String dbpassword = "1234"; // This method will connect to PostgreSQL database and will return a connection object public Connection connect() { Connection conn = null; try { conn = DriverManager.getConnection(dburl, dbuser, dbpassword); System.out.println("Successfull Postgres and Java connection"); // this prepares a connection statement Statement statement = conn.createStatement(); // we assign a query to the statement ResultSet rs = statement.executeQuery("SELECT * FROM flower"); // this will iterate through the result and print the result in the console while (rs.next()){ System.out.printf("%-30.30s %-30.30s%n", rs.getString("name"), rs.getString("price")); } } catch (SQLException e) { System.out.println(e.getMessage()); } return conn; } // this is the entry point of our application and will be the one to execute our method public static void main(String[] args) { PostgresJava app = new PostgresJava(); app.connect(); } } |
The results should resemble the following:
1 2 3 4 5 6 | Successful Postgres and Java connection rose 250 tulip 150 snowdrop 300 Poppy 250 Bluebell 100 |
Conclusion
This tutorial explained the JAVA and Postgres connection and provided instructions on how to set up the Java environment and download the Postgres JDBC driver. The article provided links for downloading the JDBC driver and ECLIPSE IDE and specifically provided detailed instructions for creating a JAVA application with ECLIPSE IDE. The tutorial also covered how to prepare the Postgres database and connect the JAVA application to Postgres via the command line. The tutorial provided examples on how to insert data in tables in the Postgres database. Remember that importing the JAR files is critical for referencing the library in the JAVA application.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started