Connecting Java JDBC to PostgreSQL
Introduction
In this article we will learn how to connect the JDBC to a PostgreSQL database from a java application.
Prerequisites
- Ensure that PostgreSQL server is properly installed, configured and running on the background.
For Linux and Windows systems you can download PostgreSQL here
- Ensure that ECLIPSE IDE is properly installed and configured in your system.
For Linux and Windows system you can download Eclipse IDE here
- Basic Java syntax knowledge.
Creating a Sample Database
Before we proceed with our tutorial, let us create a sample data that we can use later as we progress.
First, we login to our PostgreSQL server and create a database.
After creating the database then we can connect to it using the PostgreSQL meta-command \c followed by the database name, like so: \c testdatabase;.
Then we create a table named tbluser using the following command.
id INT PRIMARY KEY,
username TEXT NOT NULL,
password TEXT NOT NULL
);
We can now perform an INSERT operation to put some records in there:
VALUES (1, 'rommel2019', '123456');
Output:
----+------------+----------
1 | rommel2019 | 123456
(1 ROW)
Downloading the JDBC Driver
In order for us to connect our java application to the target PostgreSQL database, we need to have the JDBC driver.
- We can download the latest version on the official website of PostgreSQL under the download section.
- Or we can reference the driver using the MAVEN tool of ECLIPSE IDE.
Connecting to the PostgreSQL database server
In this section, we will now connect a java application to PostgreSQL database.
Creating a Java Project
First, we need to create a new project in ECLIPSE IDE using the following steps in sequence:
Click File, then select Project... on the sub-menu.
Then select Java Project in the available options under the Select a wizard window. Then click the Next> button to proceed on the next step.
We then provide a meaningful name that defines our java project.
Then click Finish
Manual Import of PostgreSQL JDBC driver
Now that we have a java project let us now import the necessary driver manually. To do this we follow these steps in sequence.
First, select the java project that we created earlier then press ALT + ENTER on your keyboard to open up the properties window.
Then click Java Build Path on the left side pane of the properties window.
Then click the Add External JARS... option, to open up the JAR Selection window then look for the PostgreSQL JDBC driver, as of this writing the latest version of the driver is “postgresql-42.2.8.jar”.
Click Open to select the driver.
Click the Apply and Close button to close the properties window.
Creating a Java Class.
Now that we have downloaded the PostgreSQL JDBC driver, we are now ready to connect to the PostgreSQL database via java application.
First, we make a new class inside our java application. To do this follow the steps in sequence:
Then provide the following details as shown in the image:
After providing the details, we click the Finish button.
Connecting to PostgreSQL server via Java Application.
We are now ready, coding our connection using the Java Class that we have created in the previous section.
First, we declare variables that will hold the values of the connection string, the name of the user and the corresponding password. To do this we use the following code:
After we determine these three important details, we can now use them to connect to the server with the help of the getConnection() method of the class DriverManager.
Below java code will create a conn() function that will call for the details that we created earlier and use them to connect to PostgreSQL database server.
Connection conn = null;
try {
conn = DriverManager.getConnection(conUrl, username, password);
System.out.println("You are now connected to the server");
}catch(SQLException e){
System.err.println(e.getMessage());
}
return conn;
}
Now to test our code we need to call the function above within the entry point of our java application, the function main().
We use the following code within the function main() code block.
Then we press CTRL + F11 our keyboard.
You should see something like this in your console.
Basic Java JDBC PostgreSQL query
The following code will perform a basic SELECT operation to verify that our application is properly connecting to PostgreSQL database server.
String SQL = "SELECT * FROM tbluser";
try(Connection conn = conn();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(SQL)){
displayUser(rs);
}catch (SQLException e) {
System.out.println(e.getMessage());
}
}
public void displayUser (ResultSet rs) throws SQLException {
while(rs.next()) {
System.out.println(rs.getString("username") + "\t"
+ rs.getString("password"));
}
}
Conclusion
In this tutorial we show you show to connect Java application to PostgreSQL database.
The Code
Below is the entire working code that we used in this article.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JdbcDemo {
private final String conUrl = "jdbc:postgresql://localhost/testdatabase";
private final String username = "postgres";
private final String password = "1234";
public Connection conn() {
Connection conn = null;
try {
conn = DriverManager.getConnection(conUrl, username, password);
System.out.println("You are now connected to the server");
}catch(SQLException e){
System.err.println(e.getMessage());
}
return conn;
}
public static void main(String[] args) {
JdbcDemo app = new JdbcDemo();
app.getUser();
}
public void getUser() {
String SQL = "SELECT * FROM tbluser";
try(Connection conn = conn();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(SQL)){
displayUser(rs);
}catch (SQLException e) {
System.out.println(e.getMessage());
}
}
public void displayUser (ResultSet rs) throws SQLException {
while(rs.next()) {
System.out.println(rs.getString("username") + "\t"
+ rs.getString("password"));
}
}
}
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started