Java CRUD Operations with PostgreSQL

Author:
In this tutorial, we will learn how to write a Java program to connect to the PostgreSQL database and perform basic database operations (CRUD - Create, Retrieve, Update and Delete) using JDBC (Java Database Connectivity) API.

These CRUD operations are equivalent to the INSERT, SELECT, UPDATE and DELETE statements in SQL language.

What is JDBC?


Java Database Connectivity or JDBC API provides industry-standard and database-independent connectivity between the Java applications and relational database servers (relational databases, spreadsheets, and flat files).


To keep it simple, JDBC allows a Java application to connect to a relational database. The major databases are supported such as Oracle, Microsoft SQL Server, DB2 and many others.

Fundamental Steps in JDBC

The fundamental steps involved in the process of connecting to a database and executing a query consist of the following:
  1. Import JDBC packages 
  2. Open a connection to the database.
  3. Create a statement object to perform a query.
  4. Execute the statement object and return a query resultset.
  5. Process the resultset.
We are going to use the above steps in further Java JDBC programs.

What is PostgreSQL?

PostgreSQL is a general-purpose and object-relational database management system, the most advanced open-source database system. PostgreSQL was developed based on POSTGRES 4.2 at Berkeley Computer Science Department, University of California.
PostgreSQL was designed to run on UNIX-like platforms. However, PostgreSQL was then also designed to be portable so that it could run on various platforms such as Mac OS X, Solaris, and Windows.

Technologies used

We use below technologies in this tutorial:
  • JDK - 1.8 or later
  • PostgreSQL- 42.2.9
  • IDE - Eclipse Neon
  • JDBC - 4.2

Download PostgreSQL JDBC Driver

To connect to the PostgreSQL database server from a Java program, you need to have a PostgreSQL JDBC driver. You can download the latest version of the driver on the postgresql.org website via the download page.
Add the PostgreSQL JDBC driver jar file to the project classpath.
For maven users:
<!-- https://mvnrepository.com/artifact/org.postgresql/postgresql -->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.2.9</version>
</dependency>
For Gradle users:
// https://mvnrepository.com/artifact/org.postgresql/postgresql
compile group: 'org.postgresql', name: 'postgresql', version: '42.2.9'
Let's start with creating a users table and then we will perform  INSERT, UPDATE, SELECT, and DELETE operations.

1. JDBC PostgreSQL Create Table Example

The complete Java program for creating "users" table in the PostgreSQL database server is as follows:
package net.javaguides.postgresql.tutorial;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Create Table JDBC Example
 * @author Ramesh Fadatare
 *
 */
public class CreateTableExample {

    private final String url = "jdbc:postgresql://localhost/mydb";
    private final String user = "postgres";
    private final String password = "root";

    private static final String createTableSQL = "CREATE TABLE users " +
        "(ID INT PRIMARY KEY ," +
        " NAME TEXT, " +
        " EMAIL VARCHAR(50), " +
        " COUNTRY VARCHAR(50), " +
        " PASSWORD VARCHAR(50))";

    public static void main(String[] argv) throws SQLException {
        CreateTableExample createTableExample = new CreateTableExample();
        createTableExample.createTable();
    }

    public void createTable() throws SQLException {

        System.out.println(createTableSQL);
        // Step 1: Establishing a Connection
        try (Connection connection = DriverManager.getConnection(url, user, password);

            // Step 2:Create a statement using connection object
            Statement statement = connection.createStatement();) {

            // Step 3: Execute the query or update query
            statement.execute(createTableSQL);
        } catch (SQLException e) {

            // print SQL exception information
            printSQLException(e);
        }
    }

    public static void printSQLException(SQLException ex) {
        for (Throwable e: ex) {
            if (e instanceof SQLException) {
                e.printStackTrace(System.err);
                System.err.println("SQLState: " + ((SQLException) e).getSQLState());
                System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
                System.err.println("Message: " + e.getMessage());
                Throwable t = ex.getCause();
                while (t != null) {
                    System.out.println("Cause: " + t);
                    t = t.getCause();
                }
            }
        }
    }
}
Output:
CREATE TABLE users (ID INT PRIMARY KEY , NAME TEXT,  EMAIL VARCHAR(50),  COUNTRY VARCHAR(50),  PASSWORD VARCHAR(50))So we have created a "users" table PostgreSQL database server successfully.
Check out all Java PostgreSQL examples at Java PostgreSQL Tutorial.

2. Java JDBC PostgreSQL Insert Example

2.1 Inserting one row into a table

To insert a row into a table, you follow these steps:
  • Establish a database connection to get a Connection object.
  • Create a Statement object from the Connection object.
  • Execute the INSERT statement.
  • Close the database connection.
Here is the complete Java program to insert a single row into the "users" table in the PostgreSQL database:
package net.javaguides.postgresql.tutorial;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * Insert PrepareStatement JDBC Example
 * 
 * @author Ramesh Fadatare
 *
 */
public class InsertRecordExample {
    private final String url = "jdbc:postgresql://localhost/myDB";
    private final String user = "postgres";
    private final String password = "root";

    private static final String INSERT_USERS_SQL = "INSERT INTO users" +
        "  (id, name, email, country, password) VALUES " +
        " (?, ?, ?, ?, ?);";

    public static void main(String[] argv) throws SQLException {
        InsertRecordExample createTableExample = new InsertRecordExample();
        createTableExample.insertRecord();
    }

    public void insertRecord() throws SQLException {
        System.out.println(INSERT_USERS_SQL);
        // Step 1: Establishing a Connection
        try (Connection connection = DriverManager.getConnection(url, user, password);

            // Step 2:Create a statement using connection object
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_USERS_SQL)) {
            preparedStatement.setInt(1, 1);
            preparedStatement.setString(2, "Tony");
            preparedStatement.setString(3, "[email protected]");
            preparedStatement.setString(4, "US");
            preparedStatement.setString(5, "secret");

            System.out.println(preparedStatement);
            // Step 3: Execute the query or update query
            preparedStatement.executeUpdate();
        } catch (SQLException e) {

            // print SQL exception information
            printSQLException(e);
        }

        // Step 4: try-with-resource statement will auto close the connection.
    }

    public static void printSQLException(SQLException ex) {
        for (Throwable e: ex) {
            if (e instanceof SQLException) {
                e.printStackTrace(System.err);
                System.err.println("SQLState: " + ((SQLException) e).getSQLState());
                System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
                System.err.println("Message: " + e.getMessage());
                Throwable t = ex.getCause();
                while (t != null) {
                    System.out.println("Cause: " + t);
                    t = t.getCause();
                }
            }
        }
    }
}

2.2 Inserting multiple rows into a table

The steps of inserting multiple rows into a table are as follows:
  • Create a database connection.
  • Create a PreparedStatement object.
  • Call the addBatch() method of the PreparedStatement object.
  • Call the executeBatch() method to submit a batch of the INSERT statements to the PostgreSQL database server for execution.
  • Close the database connection.
Here is the complete Java program to insert multiple rows into the "users" table in the PostgreSQL database:
package net.javaguides.postgresql.tutorial;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;

public class InsertMultipleRecordsExample {

    private final String url = "jdbc:postgresql://localhost/myDB";
    private final String user = "postgres";
    private final String password = "root";

    private static final String INSERT_USERS_SQL = "INSERT INTO users" +
        "  (id, name, email, country, password) VALUES " +
        " (?, ?, ?, ?, ?);";

    /**
     * insert multiple users
     */
    public void insertUsers(List < User > list) {
        try (
            Connection conn = DriverManager.getConnection(url, user, password); PreparedStatement statement = conn.prepareStatement(INSERT_USERS_SQL);) {
            int count = 0;

            for (User user: list) {
                statement.setInt(1, user.getId());
                statement.setString(2, user.getName());
                statement.setString(3, user.getEmail());
                statement.setString(4, user.getCountry());
                statement.setString(5, user.getPassword());

                statement.addBatch();
                count++;
                // execute every 100 rows or less
                if (count % 100 == 0 || count == list.size()) {
                    statement.executeBatch();
                }
            }
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }

    public static void main(String[] args) {
        InsertMultipleRecordsExample example = new InsertMultipleRecordsExample();
        example.insertUsers(Arrays.asList(new User(2, "Ramesh", "[email protected]", "India", "password123"),
            new User(3, "John", "[email protected]", "US", "password123")));
    }
}
Check out all Java PostgreSQL examples at Java PostgreSQL Tutorial.

3. JDBC PostgreSQL Select Record Example

Here is the complete Java program to select single or multiple records from the "users" table:
package net.javaguides.postgresql.tutorial;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Select PreparedStatement JDBC Example
 * 
 * @author Ramesh Fadatare
 *
 */
public class RetrieveRecordsExample {

    private final static String url = "jdbc:postgresql://localhost/mydb";
    private final static String user = "postgres";
    private final static String password = "root";

    private static final String QUERY = "select id,name,email,country,password from Users where id =?";
    private static final String SELECT_ALL_QUERY = "select * from users";

    public void getUserById() {
        // using try-with-resources to avoid closing resources (boiler plate
        // code)

        // Step 1: Establishing a Connection
        try (Connection connection = DriverManager.getConnection(url, user, password);
            // Step 2:Create a statement using connection object
            PreparedStatement preparedStatement = connection.prepareStatement(QUERY);) {
            preparedStatement.setInt(1, 1);
            System.out.println(preparedStatement);
            // Step 3: Execute the query or update query
            ResultSet rs = preparedStatement.executeQuery();

            // Step 4: Process the ResultSet object.
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                String country = rs.getString("country");
                String password = rs.getString("password");
                System.out.println(id + "," + name + "," + email + "," + country + "," + password);
            }
        } catch (SQLException e) {
            printSQLException(e);
        }
    }

    public void getAllUsers() {
        // using try-with-resources to avoid closing resources (boiler plate
        // code)

        // Step 1: Establishing a Connection
        try (Connection connection = DriverManager.getConnection(url, user, password);
            // Step 2:Create a statement using connection object
            PreparedStatement preparedStatement = connection.prepareStatement(SELECT_ALL_QUERY);) {
            System.out.println(preparedStatement);
            // Step 3: Execute the query or update query
            ResultSet rs = preparedStatement.executeQuery();

            // Step 4: Process the ResultSet object.
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                String country = rs.getString("country");
                String password = rs.getString("password");
                System.out.println(id + "," + name + "," + email + "," + country + "," + password);
            }
        } catch (SQLException e) {
            printSQLException(e);
        }
    }

    public static void main(String[] args) {
        RetrieveRecordsExample example = new RetrieveRecordsExample();
        example.getUserById();
        example.getAllUsers();
    }

    public static void printSQLException(SQLException ex) {
        for (Throwable e: ex) {
            if (e instanceof SQLException) {
                e.printStackTrace(System.err);
                System.err.println("SQLState: " + ((SQLException) e).getSQLState());
                System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
                System.err.println("Message: " + e.getMessage());
                Throwable t = ex.getCause();
                while (t != null) {
                    System.out.println("Cause: " + t);
                    t = t.getCause();
                }
            }
        }
    }
}
Output:
select id,name,email,country,password from Users where id =1
select * from users
2,Ramesh,[email protected],India,password123
3,John,[email protected],US,password123
In this tutorial, we have shown you how to query data from the PostgreSQL database using the JDBC API.
Check out all Java PostgreSQL examples at Java PostgreSQL Tutorial.

4. JDBC PostgreSQL Update Example

Here is the complete Java program to update a row into the "users" table in the PostgreSQL database:
package net.javaguides.postgresql.tutorial;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * Update PreparedStatement JDBC Example
 * @author Ramesh Fadatare
 *
 */
public class UpdateRecordExample {

    private final String url = "jdbc:postgresql://localhost/mydb";
    private final String user = "postgres";
    private final String password = "root";

    private static final String UPDATE_USERS_SQL = "update users set name = ? where id = ?;";

    public static void main(String[] argv) throws SQLException {
        UpdateRecordExample updateStatementExample = new UpdateRecordExample();
        updateStatementExample.updateRecord();
    }

    public void updateRecord() throws SQLException {
        System.out.println(UPDATE_USERS_SQL);
        // Step 1: Establishing a Connection
        try (Connection connection = DriverManager.getConnection(url, user, password);

            // Step 2:Create a statement using connection object
            PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_USERS_SQL)) {
            preparedStatement.setString(1, "Ram");
            preparedStatement.setInt(2, 1);

            // Step 3: Execute the query or update query
            preparedStatement.executeUpdate();
        } catch (SQLException e) {

            // print SQL exception information
            printSQLException(e);
        }

        // Step 4: try-with-resource statement will auto close the connection.
    }

    public static void printSQLException(SQLException ex) {
        for (Throwable e: ex) {
            if (e instanceof SQLException) {
                e.printStackTrace(System.err);
                System.err.println("SQLState: " + ((SQLException) e).getSQLState());
                System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
                System.err.println("Message: " + e.getMessage());
                Throwable t = ex.getCause();
                while (t != null) {
                    System.out.println("Cause: " + t);
                    t = t.getCause();
                }
            }
        }
    }
}
Check out all Java PostgreSQL examples at Java PostgreSQL Tutorial.

5. JDBC PostgreSQL Delete Example

Here is the complete Java program to delete record from "users" table in the PostgreSQL database:
package net.javaguides.postgresql.tutorial;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * Delete JDBC Example
 * @author Ramesh Fadatare
 *
 */
public class DeleteRecordExample {

    private static final String DELETE_USERS_SQL = "delete from users where id = ?;";

    private final String url = "jdbc:postgresql://localhost/mydb";
    private final String user = "postgres";
    private final String password = "root";

    public static void main(String[] argv) throws SQLException {
        DeleteRecordExample deleteStatementExample = new DeleteRecordExample();
        deleteStatementExample.deleteRecord();
    }

    public void deleteRecord() throws SQLException {
        System.out.println(DELETE_USERS_SQL);

        // Step 1: Establishing a Connection
        try (Connection connection = DriverManager.getConnection(url, user, password);

            // Step 2:Create a statement using connection object
            PreparedStatement preparedStatement = connection.prepareStatement(DELETE_USERS_SQL);) {
            preparedStatement.setInt(1, 1);

            // Step 3: Execute the query or update query
            int result = preparedStatement.executeUpdate();
            System.out.println("Number of records affected :: " + result);
        } catch (SQLException e) {

            // print SQL exception information
            printSQLException(e);
        }

        // Step 4: try-with-resource statement will auto close the connection.
    }

    public static void printSQLException(SQLException ex) {
        for (Throwable e: ex) {
            if (e instanceof SQLException) {
                e.printStackTrace(System.err);
                System.err.println("SQLState: " + ((SQLException) e).getSQLState());
                System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
                System.err.println("Message: " + e.getMessage());
                Throwable t = ex.getCause();
                while (t != null) {
                    System.out.println("Cause: " + t);
                    t = t.getCause();
                }
            }
        }
    }
}
Check out all Java PostgreSQL examples at Java PostgreSQL Tutorial.

Conclusion

In this tutorial, we have seen how to write a Java program to connect to the PostgreSQL database and perform basic database operations (CRUD - Create, Retrieve, Update and Delete) using JDBC (Java Database Connectivity) API.
Check out all Java PostgreSQL examples at Java PostgreSQL Tutorial.

Comments

Post a Comment

Leave Comment