Java JDBC PostgreSQL Insert Example

Author:

In this tutorial, we will show you how to insert data into a PostgreSQL database using Java and the JDBC API. We'll cover how to insert both single and multiple rows into a PostgreSQL table using prepared statements, ensuring safe and efficient database interactions.

What You’ll Learn:

  • How to insert a single row into a PostgreSQL database.
  • How to insert multiple rows efficiently using batch processing.
  • Handling SQL exceptions in Java.
  • How to connect to a PostgreSQL database using JDBC.

Technologies Used:

In this tutorial, we will be using the following technologies:

  • JDK: Version 21 or later
  • PostgreSQL JDBC Driver: Version 42.7.4
  • IDE: Eclipse, IntelliJ IDEA, or any other IDE
  • JDBC: Version 4.2

Step 1: Download PostgreSQL JDBC Driver

To connect your Java program to the PostgreSQL database, you need to have the PostgreSQL JDBC driver in your project. You can download it or use Maven or Gradle to include it automatically.

For Maven users:

Add the following dependency to your pom.xml file:

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

For Gradle users:

Add the following line to your build.gradle file:

implementation 'org.postgresql:postgresql:42.7.4'

This will automatically add the PostgreSQL JDBC driver to your project.

Step 2: PostgreSQL Database Setup

Before inserting data, ensure that you have a PostgreSQL database set up and ready. If you followed the previous tutorial, you should have a users table created.

Here’s the basic structure of the users table:

CREATE TABLE users (
    ID INT PRIMARY KEY,
    NAME TEXT,
    EMAIL VARCHAR(50),
    COUNTRY VARCHAR(50),
    PASSWORD VARCHAR(50)
);

Step 3: Inserting a Single Row into the PostgreSQL Table

Steps to Insert a Single Row:

  1. Establish a database connection to get a Connection object.
  2. Create a PreparedStatement using the connection.
  3. Execute the INSERT query to add the row.
  4. Close the database connection.

Java Program: Inserting a Single Row

package com.example.postgresql;

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

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[] args) {
        InsertRecordExample example = new InsertRecordExample();
        try {
            example.insertRecord();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    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, "tony@gmail.com");
            preparedStatement.setString(4, "US");
            preparedStatement.setString(5, "secret");

            System.out.println(preparedStatement);
            // Step 3: Execute the query
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            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();
                }
            }
        }
    }
}

Explanation:

  • PreparedStatement: We use PreparedStatement to insert data securely.
  • setInt(), setString(): These methods set the parameters for the SQL query.
  • executeUpdate(): Executes the SQL query to insert the record into the database.

Step 4: Inserting Multiple Rows into the PostgreSQL Table

Inserting multiple rows can be done efficiently using batch processing. This way, you can insert many rows at once, reducing the overhead of multiple database connections.

Steps to Insert Multiple Rows:

  1. Establish a database connection.
  2. Create a PreparedStatement.
  3. Use addBatch() to add multiple insert statements.
  4. Call executeBatch() to submit the batch to the database.
  5. Close the connection.

Java Program: Inserting Multiple Rows

package com.example.postgresql;

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 (?, ?, ?, ?, ?);";

    public void insertUsers(List<User> users) {
        try (Connection conn = DriverManager.getConnection(url, user, password);
             PreparedStatement preparedStatement = conn.prepareStatement(INSERT_USERS_SQL)) {
             
            int count = 0;
            for (User user : users) {
                preparedStatement.setInt(1, user.getId());
                preparedStatement.setString(2, user.getName());
                preparedStatement.setString(3, user.getEmail());
                preparedStatement.setString(4, user.getCountry());
                preparedStatement.setString(5, user.getPassword());
                preparedStatement.addBatch();
                count++;

                // Execute every 100 rows or when the batch is complete
                if (count % 100 == 0 || count == users.size()) {
                    preparedStatement.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", "ramesh@gmail.com", "India", "password123"),
            new User(3, "John", "john@gmail.com", "US", "password123")
        ));
    }
}

Explanation:

  • Batch Processing: We use the addBatch() method to add multiple SQL statements to a batch and executeBatch() to execute them all at once.
  • Improved Performance: This method improves performance when inserting many rows by reducing the number of database calls.

Conclusion

In this tutorial, we showed you how to insert both single and multiple rows into a PostgreSQL database using the JDBC API in Java. By using PreparedStatement and batch processing, you can securely and efficiently insert data into your PostgreSQL tables.

Key Takeaways:

  • Use PreparedStatement for secure and efficient SQL query execution.
  • Use batch processing to improve performance when inserting multiple rows.
  • Handle SQL exceptions carefully to debug potential database issues.

Comments

Post a Comment

Leave Comment