Connect to PostgreSQL with JDBC driver

In this post, I am going to explain how to create, insert and query the table using JDBC.

 

Prerequisite

Setup Postgresql.

 

Dependencies used

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.4.1</version>
</dependency>

 

Create a test database

Open a terminal and execute below command to create ‘test’ database.

createdb -U postgres -h localhost test

 

Refer this link (https://self-learning-java-tutorial.blogspot.com/2019/05/postgres-create-database.html)  for more details.

 

JDBC to connect to test database and interact with a table

Step 1: Get an instance of database connection object.

final String url = "jdbc:postgresql://127.0.0.1:5432/test";
final String userName = "postgres";
final String pasword = "postgres";

final Connection conn = DriverManager.getConnection(url, userName, pasword);

Step 2: Get a Statement object.

final Statement stmt = conn.createStatement();

Step 3: Create a table by executing the query,

String query = "CREATE TABLE employee (id int, name varchar(30), PRIMARY KEY(id))";
stmt.execute(query);

Step 4: Insert some records into employee table.

query = "INSERT INTO employee values(1, 'Krishna')";
stmt.execute(query);
query = "INSERT INTO employee values(2, 'Arjun')";
stmt.execute(query);

Step 5: Query employee table and print the information.

query = "SELECT * FROM employee";

try (final ResultSet rs = stmt.executeQuery(query)) {
    while (rs.next()) {
        int id = rs.getInt("id");
        String name = rs.getString("name");
        System.out.println(id + " " + name);
    }
}

Find the below working application.

 

HelloWorld.java

package com.sample.app;

/*Import sql package entities */
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class HelloWorld {
    public static void main(String args[]) throws SQLException, ClassNotFoundException {

        /* Open connection to database */
        System.out.println("Connecting to database");
        final String url = "jdbc:postgresql://127.0.0.1:5432/test";
        final String userName = "postgres";
        final String pasword = "postgres";

        try (final Connection conn = DriverManager.getConnection(url, userName, pasword);
                final Statement stmt = conn.createStatement();) {

            /* Query to create employee table */
            String query = "CREATE TABLE employee (id int, name varchar(30), PRIMARY KEY(id))";
            stmt.execute(query);

            /* Insert data to employee table */
            query = "INSERT INTO employee values(1, 'Krishna')";
            stmt.execute(query);
            query = "INSERT INTO employee values(2, 'Arjun')";
            stmt.execute(query);

            query = "SELECT * FROM employee";

            try (final ResultSet rs = stmt.executeQuery(query)) {
                while (rs.next()) {
                    int id = rs.getInt("id");
                    String name = rs.getString("name");
                    System.out.println(id + " " + name);
                }
            }
        }

    }
}

Run the application, you will see below messages in the console.

Connecting to database
1 Krishna
2 Arjun

Query the table from psql console and confirm the same.

$psql -U postgres -h localhost --dbname=test
Password for user postgres: 
psql (14.4)
Type "help" for help.

test-# \d
          List of relations
 Schema |   Name   | Type  |  Owner   
--------+----------+-------+----------
 public | employee | table | postgres
(1 row)

test-# 
test=# SELECT * FROM employee;
 id |  name   
----+---------
  1 | Krishna
  2 | Arjun
(2 rows)

Complete maven project structure looks like below.

 

 

You can download complete working application from this link.

Previous                                                 Next                                                 Home