Java - JDBC PostgreSQL Insert Image Example

In this tutorial, we will discuss how to insert an image into a PostgreSQL database with an example.
Some people prefer to put their images into the database, some prefer to keep them on the file system for their applications. Technical difficulties arise when we work with lots of images. Images are binary data. PostgreSQL database has a special data type to store binary data called bytea. This is a non-standard data type. The standard data type in databases is BLOB.
PostgreSQL is a powerful, open-source object-relational database system. It is a multi-user database management system. It runs on multiple platforms including Linux, FreeBSD, Solaris, Microsoft Windows, and Mac OS X. PostgreSQL is developed by the PostgreSQL Global Development Group.
Check out all Java PostgreSQL examples at Java PostgreSQL Tutorial.

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'

PostgreSQL Database Setup

Let's create "images" table in "mydb" database with the following statement:
CREATE TABLE IF NOT EXISTS images(id serial, data bytea);

JDBC PostgreSQL Insert Image Example

Let's see how to insert an image into a PostgreSQL database with an example.
package net.javaguides.postgresql.tutorial;

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) {

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

        String query = "INSERT INTO images(data) VALUES(?)";

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

            File img = new File("java-logo.jpg");

            try (FileInputStream fin = new FileInputStream(img)) {

                pst.setBinaryStream(1, fin, (int) img.length());
                pst.executeUpdate();
            } 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);
        }
    }
}
The FileOutputStream object is created to write to a file. It is meant for writing streams of raw bytes such as image data.
Check out all Java PostgreSQL examples at Java PostgreSQL Tutorial.

Comments