Java JDBC PostgreSQL Insert Image Example

In this tutorial, we will show you how to insert an image into a PostgreSQL database using Java and the JDBC API. 

What You’ll Learn:

• How to store binary data (images) in a PostgreSQL database using JDBC.
• How to use the bytea data type in PostgreSQL to store image files.
• How to handle file streams and manage binary data in Java.

Technologies Used:

In this tutorial, we will use the following technologies:

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

Step 1: Download PostgreSQL JDBC Driver

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

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'

Step 2: PostgreSQL Database Setup

Before inserting images, you need a table in PostgreSQL that can store binary data. PostgreSQL provides the bytea data type for storing binary data such as images.

Create a Table for Storing Images:

Run the following SQL command to create a table called images that stores image data:

CREATE TABLE IF NOT EXISTS images (
    id serial PRIMARY KEY,
    data bytea
);

This table has an id column (auto-incremented) and a data column to store binary image data.

Step 3: Inserting an Image into PostgreSQL Using JDBC

To insert an image into the PostgreSQL database using JDBC, you’ll need to:

1. Establish a connection to the PostgreSQL database.
2. Use the PreparedStatement to insert binary data (image) into the database.
3. Use FileInputStream to read the image file into the Java program.

Java Program: Insert Image into PostgreSQL

Here’s the complete Java program to insert an image into the PostgreSQL database:

package com.example.postgresql;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

public class JavaPostgreSqlWriteImage {

    public static void main(String[] args) {

        // PostgreSQL connection details
        String url = "jdbc:postgresql://localhost/mydb";
        String user = "postgres";
        String password = "root";

        // SQL query to insert image data
        String query = "INSERT INTO images (data) VALUES (?)";

        try (Connection con = DriverManager.getConnection(url, user, password);
             PreparedStatement pst = con.prepareStatement(query)) {

            // Specify the image file path
            File img = new File("java-logo.jpg");

            // Read the image as a stream of bytes
            try (FileInputStream fin = new FileInputStream(img)) {

                // Set the image data in the PreparedStatement
                pst.setBinaryStream(1, fin, (int) img.length());

                // Execute the update (insert the image)
                pst.executeUpdate();
                System.out.println("Image inserted successfully into the database.");

            } catch (IOException ex) {
                Logger.getLogger(JavaPostgreSqlWriteImage.class.getName()).log(
                        Level.SEVERE, ex.getMessage(), ex);
            }

        } catch (SQLException ex) {
            Logger lgr = Logger.getLogger(JavaPostgreSqlWriteImage.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
        }
    }
}

Explanation:

• FileInputStream: This is used to read the image file as a stream of bytes.
• PreparedStatement.setBinaryStream(): Sets the image data to be inserted into the bytea column in PostgreSQL.
• pst.executeUpdate(): Executes the SQL query to insert the image data into the images table.

Step 4: Running the Program

To run the program:

1. Compile and run the JavaPostgreSqlWriteImage class.
2. Ensure the java-logo.jpg file is located in the correct directory.
3. The program will insert the image into the images table.

Sample Output:

Image inserted successfully into the database.

Step 5: Verifying the Image Insertion

To verify that the image was successfully inserted, you can run the following query in PostgreSQL to see the binary data stored:

SELECT id, data FROM images;

You will see the id and the bytea data representing the inserted image.

Conclusion

In this tutorial, we demonstrated how to insert an image into a PostgreSQL database using Java JDBC. Storing binary data, such as images, in a database is useful in certain applications where the images are tightly coupled with the database records.

Key Takeaways:

• bytea is the data type used in PostgreSQL to store binary data like images.
• Use PreparedStatement and FileInputStream in Java to handle binary data.
• Storing images in a database provides centralized control but may require careful management for large-scale applications.

By following this guide, you can now store image files in your PostgreSQL database using Java and JDBC.