Java JDBC PostgreSQL Read Image Example

In this tutorial, we will show you how to retrieve an image from a PostgreSQL database using Java and the JDBC API. In the previous tutorial, we inserted an image into a PostgreSQL table. Now, we will read that image back and save it to a file.

What You’ll Learn:

• How to retrieve binary data (images) from a PostgreSQL database using JDBC.
• How to write binary data (image) to a file using FileOutputStream.
• How to handle PostgreSQL's bytea data type 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

Ensure that you have already inserted an image into the PostgreSQL database. Here’s a reminder of the table structure used to store the image:

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

In the previous tutorial, we inserted an image into this images table. Now, we will read the image back from the table.

Step 3: Reading an Image from PostgreSQL Using JDBC

To read an image from PostgreSQL using JDBC, you’ll need to:

1. Establish a connection to the PostgreSQL database.
2. Execute a SELECT query to retrieve the image from the database.
3. Use FileOutputStream to save the binary image data as a file.

Java Program: Read Image from PostgreSQL

Here’s the complete Java program to read an image from the PostgreSQL database and save it to a file:

package com.example.postgresql;

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

public class JavaPostgreSqlReadImage {

    public static void main(String[] args) {

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

        // SQL query to retrieve image data
        String query = "SELECT data, LENGTH(data) FROM images WHERE id = 1";

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

            // Move to the first result in the ResultSet
            if (rs.next()) {

                // File to save the retrieved image
                File myFile = new File("java-logo1.jpg");

                try (FileOutputStream fos = new FileOutputStream(myFile)) {

                    // Get the length and binary data of the image
                    int len = rs.getInt(2);
                    byte[] buf = rs.getBytes("data");

                    // Write the binary data to the file
                    fos.write(buf, 0, len);
                    System.out.println("Image read successfully and saved as java-logo1.jpg.");
                }
            }

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

Explanation:

• FileOutputStream: This is used to write the binary image data to a new file.
• rs.getBytes("data"): Retrieves the binary data (image) from the bytea column.
• LENGTH(data): Retrieves the length of the binary data.
• File: A new file is created where the image will be saved after being read from the database.

Step 4: Running the Program

To run the program:

1. Compile and run the JavaPostgreSqlReadImage class.
2. Ensure the id of the image you are retrieving matches the one you inserted earlier (in this case, id = 1).
3. The program will read the image from the database and save it as java-logo1.jpg in the current directory.

Sample Output:

Image read successfully and saved as java-logo1.jpg.

This output confirms that the image has been successfully retrieved from the PostgreSQL database and saved to a file.

Step 5: Verifying the Image

After running the program, you will find the image file java-logo1.jpg in your project directory. You can open it to verify that the image was successfully retrieved and saved.

Conclusion

In this tutorial, we demonstrated how to retrieve an image from a PostgreSQL database using Java JDBC. This process is useful for applications that store binary data, such as images, in a database and need to retrieve and display them.

Key Takeaways:

• Use the bytea data type in PostgreSQL to store binary data like images.
• Use ResultSet.getBytes() to retrieve binary data from a database.
• FileOutputStream is used to write the binary data to a file.

By following this guide, you can now retrieve and save images stored in PostgreSQL databases using Java and JDBC.