JAVA EE: JDBC : Store Image

Saturday, 23 August 2014

JDBC : Store Image

Enjoyed this tutorial?

Subscribe to Ram N Java for regular, beginner-friendly Java & database tutorials!

Introduction

Storing multimedia files like images, audio, or PDFs inside a relational database is a standard requirement for many Java applications. In relational databases like MySQL, binary files are stored using the BLOB (Binary Large Object) data type.

With Java Database Connectivity (JDBC), you can read an image file from your local storage, convert it into an input stream, and save it directly into a MySQL table using a PreparedStatement.

Database Table Structure

Before executing the Java code, create a dedicated table named city_image with a BLOB column to hold the image bytes:

CREATE TABLE city_image (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    photo BLOB
);

Step-by-Step Implementation in Java

Load the JDBC Driver: Register the MySQL JDBC driver class using Class.forName("com.mysql.cj.jdbc.Driver").
Establish Connection: Connect to MySQL using DriverManager.getConnection() with your database URL, username, and password.
Prepare the SQL Query: Create a parameterized query such as INSERT INTO city_image (id, name, photo) VALUES (?, ?, ?).
Read the File into a Stream: Wrap your local image file using FileInputStream.
Set Binary Stream: Use the setBinaryStream() method of PreparedStatement to pass the stream to the BLOB column.
Execute and Clean Up: Call executeUpdate() to save the record, and close all resources in a finally block.

Understanding setBinaryStream()

The PreparedStatement interface provides overloaded versions of the setBinaryStream() method:

setBinaryStream(int parameterIndex, InputStream x)
setBinaryStream(int parameterIndex, InputStream x, long length)

This method streams the raw binary contents directly from your disk to the database server without loading the entire file into memory as a giant byte array.

Recommended Related Tutorials

Continue mastering Java Database Connectivity with these practical guides:

Click here to watch in Youtube : https://www.youtube.com/watch?v=GCzdfiia_LU

Click the below Image to Enlarge

JDBC : Store Image

JDBCStoreImageDemo.java

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;

public class JDBCStoreImageDemo
{
    // JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/world";

    // Database credentials
    static final String USERNAME = "root";
    static final String PASSWORD = "root";

    public static void main(String[] args)
    {
        JDBCStoreImageDemo jdbcStoreImageDemo = new JDBCStoreImageDemo();
        jdbcStoreImageDemo.storeImage();
    }

    private void storeImage()
    {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        FileInputStream fileInputStream = null;
        try
        {
            /*
             * Register the JDBC driver in DriverManager
             */

            Class.forName(JDBC_DRIVER);

            /*
             * Establish connection to the Database using DriverManager
             */

            connection = DriverManager
                    .getConnection(DB_URL, USERNAME, PASSWORD);

            String sql = "insert into CITY_IMAGE values(?,?,?);";

            /*
             * Execute the query
             */
            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setInt(1, 1);
            
            File file= new File("D:/Downloads/New york.jpg");
            preparedStatement.setString(2, file.getName());
            
            fileInputStream = new FileInputStream(file);
            preparedStatement.setBinaryStream(3, fileInputStream,
                    fileInputStream.available());

            int numberOfRowsInserted = preparedStatement.executeUpdate();

            System.out
                    .println("numberOfRowsInserted : " + numberOfRowsInserted);

        }
        catch (SQLException se)
        {

            se.printStackTrace();
        }
        catch (ClassNotFoundException e)
        {
            e.printStackTrace();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            /*
             * finally block used to close resources
             */

            if (preparedStatement != null)
            {
                try
                {
                    preparedStatement.close();
                }
                catch (SQLException e)
                {
                    e.printStackTrace();
                }
            }
            if (connection != null)
            {
                try
                {
                    connection.close();
                }
                catch (SQLException e)
                {
                    e.printStackTrace();
                }
            }

            if (fileInputStream != null)
            {
                try
                {
                    fileInputStream.close();
                }
                catch (IOException e)
                {
                    e.printStackTrace();
                }
            }
        }

    }
}

Output

numberOfRowsInserted : 1

Sql

CREATE TABLE `CITY_IMAGE` 
(  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '', 
 `Photo` BLOB  NULL ,   PRIMARY KEY (`ID`))
 ENGINE=InnoDB;

Environment Used

JDK version : 1.7.0_51
Mysql Server version : 5.6.19

To Download JDBCStoreImageDemoApp Project Click the below link

https://sites.google.com/site/javaee4321/jdbc/JDBCStoreImageDemoApp.zip?attredirects=0&d=1

See also:

Servlets Tutorial

All Design Patterns Links

JDBC Tutorial

Saturday, 23 August 2014

JDBC : Store Image

Introduction

Database Table Structure

Step-by-Step Implementation in Java

Understanding setBinaryStream()

Recommended Related Tutorials

No comments:

Post a Comment

Tutorials