Connecting Java with PostgreSQL Using JDBC

JDBC (Java Database Connectivity) is an API that enables Java applications to communicate with databases. It provides methods to establish a connection, execute SQL queries, and retrieve data from the database. In this project, PostgreSQL is used as the database management system, and Maven is used to manage project dependencies.

Creating a Maven Project in Eclipse
To begin, open Eclipse and create a new Maven project by following these steps:

Go to File → New → Project.
Select Maven Project and click Next.
Choose the default workspace location and continue.
Enter the project details:
Group ID: jdbc (package name)
Artifact ID: sampleProject
Version: 0.0.1-SNAPSHOT
Click Finish to create the project.

After the project is created, create a Java class inside the src/main/java folder by right-clicking the package and selecting:

New → Class

Adding PostgreSQL JDBC Dependency

When a Maven project is created, Eclipse automatically generates a pom.xml file. This file is used to manage external libraries and dependencies required by the project.

To connect Java with PostgreSQL, the PostgreSQL JDBC driver dependency must be added to the pom.xml file.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0
         https://maven.apache.org/xsd/maven-4.0.0.xsd">

    <modelVersion>4.0.0</modelVersion>

    <groupId>jdbc</groupId>
    <artifactId>sampleProject</artifactId>
    <version>0.0.1-SNAPSHOT</version>

    <dependencies>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.7.7</version>
        </dependency>
    </dependencies>

</project>

After saving the pom.xml file, Maven automatically downloads the PostgreSQL JDBC driver and adds it to the project.

Task Description
Create a Java application that connects to a PostgreSQL database using JDBC. Assume that the database contains two tables: India Players and Afghanistan Players. Each table should store the player's name and score.

Insert at least three records into each table. Using Java and JDBC, retrieve the data from both tables and display the player names along with their scores on the console.

Requirements

• Connect Java with PostgreSQL using JDBC.

• Create two tables:

  • india_players
  • afghanistan_players

• Store the following details in each table:

  • Player Name
  • Score

• Insert a minimum of three records into each table.

• Fetch the records from both tables using Java.

• Display the retrieved data in the output.

Creating Tables in PostgreSQL Using the Linux Terminal

After setting up the Java Maven project, the next step is to create the database and tables in PostgreSQL using the Linux terminal.

Step 1: Open the Terminal
Open the Linux terminal and log in to PostgreSQL using:

sudo -u postgres psql

Once connected, the PostgreSQL prompt appears:

postgres=#

Step 2: Create a Database
Create a database named cricketdb:

CREATE DATABASE cricketdb;

Switch to the newly created database:

\c cricketdb

The prompt changes to:

cricketdb=#

Step 3: Create the Afghanistan Players Table

Create the table:

CREATE TABLE afghanistan_players (
    player_name VARCHAR(50),
    score INT
);

Insert sample records:

INSERT INTO afghanistan_players VALUES
('Rahmanullah Gurbaz', 78),
('Rashid Khan', 45),
('Mohammad Nabi', 60);

Step 4: Create the India Players Table
Create the table:

CREATE TABLE india_players (
    player_name VARCHAR(50),
    score INT
);

Insert sample records:

INSERT INTO india_players VALUES
('Virat Kohli', 92),
('Rohit Sharma', 75),
('Shubman Gill', 64);

Step 5: View the Data
To display the records stored in the tables, execute:

SELECT * FROM india_players;

SELECT * FROM afghanistan_players;

Step 6: Exit PostgreSQL
To leave the PostgreSQL shell, type:

\q

Using these commands, the database and tables are created successfully in PostgreSQL through the Linux terminal. The data stored in these tables can then be accessed from Java using JDBC.

Project Structure

In Eclipse:

sampleProject
│
├── src/main/java
│     └── sampleproject (package)
│            └── cricketdb.java (class)
│
└── pom.xml

sample project(package)-> src -> cricketdb(new class)

Java Program

package sampleProject;

import java.sql.*;

public class CricketData {
    public static void main(String[] args) {

        String url = "jdbc:postgresql://localhost:5432/cricketdb";
        String user = "postgres";
        String password = "Welcome@123";

        try {
            Connection con = DriverManager.getConnection(url, user, password);

            // India Players
            System.out.println("INDIA PLAYERS");
            Statement st1 = con.createStatement();
            ResultSet rs1 = st1.executeQuery("SELECT * FROM india_players");

            while(rs1.next()) {
                System.out.println(
                    rs1.getString("player_name") +
                    " - " +
                    rs1.getInt("score")
                );
            }

            System.out.println("\nAFGHANISTAN PLAYERS");

            // Afghanistan Players
            Statement st2 = con.createStatement();
            ResultSet rs2 = st2.executeQuery("SELECT * FROM afghanistan_players");

            while(rs2.next()) {
                System.out.println(
                    rs2.getString("player_name") +
                    " - " +
                    rs2.getInt("score")
                );
            }

            con.close();

        } catch(Exception e) {
            System.out.println(e);
        }
    }
}

Output

Need to Know
Connecting Java with PostgreSQL

The database connection was established using JDBC.

String url = "jdbc:postgresql://localhost:5432/cricketdb";
String user = "postgres";
String password = "your_password";

Connection con = DriverManager.getConnection(url, user, password);

Password Authentication Error

During execution, the following exception occurred:

org.postgresql.util.PSQLException:
FATAL: password authentication failed for user "postgres"

This error occurred because the password provided in the Java program did not match the PostgreSQL user's password.

The issue was resolved by resetting the PostgreSQL password:

ALTER USER postgres PASSWORD 'admin123';

The same password was then used inside the Java program.

Testing the Database Connection

The PostgreSQL connection was verified using:

psql -U postgres -d cricketdb -W

Successful authentication confirmed that the database credentials were correct.

Updating the Maven Project
If the dependency is not downloaded automatically, right-click the project and select:

Maven → Update Project → Finish

This refreshes the project and downloads all required libraries.

XML Validation Issue

While editing the pom.xml file, the following warning appeared:

Cannot find the declaration of element 'project'

This is usually an XML validation issue and does not affect the execution of the Maven project. Updating the Maven project or running mvn clean install resolves the issue in most cases.

Conclusion

JDBC provides a simple and efficient way to connect Java applications with PostgreSQL databases. Maven simplifies dependency management, while PostgreSQL offers reliable data storage. Proper configuration of the JDBC driver and database credentials ensures successful communication between Java and PostgreSQL.