Insert an Image using JDBC

we are going to learn about how to insert an image using JDBC in MySQL database. As we already discussed in the previous example on PreparedStatement in JDBC, one of the main advantages of PreparedStatement is, it can handle the large objects (images, videos and etc..).

For this example, we are going to use MySql database for inserting an image. In Mysql, if we want to store the binary data, we have to define the column with BLOB (Binary Large Object) type.

Insert an Image using JDBC :

Some important points about to handle the large objects in JDBC.

  1. ◈ If want to insert an image using JDBC into database, or read an image from database then we need to use PreparedStatement of JDBC.
  2. ◈ In database, the image will not be stored directly. The bytes of an image (binary data) will be stored.
  3. ◈ To store the image in data base, we should declare the column type as BLOB.
  4. ◈ BLOB type of column can store the data up-to a maximum of 4 GB.
  5. ◈ To set the binary data (bytes) of an image to sql command, we need to use the setBinaryStream() method on PrepareStatement object.

>Here is the complete example to insert an image using JDBC into database.

Insert an Image using JDBC Example :
Create a table like below :

                        CREATE TABLE employee (
                        id int(10) DEFAULT NULL,
                        name varchar(100) DEFAULT NULL,
                        salary varchar(100) DEFAULT NULL,
                        photo blob
                        );
Example:

                        import java.io.File;
                        import java.io.FileInputStream;
                        import java.io.InputStream;
                        import java.sql.Connection;
                        import java.sql.DriverManager;
                        import java.sql.PreparedStatement;
                        
                        public class JDBCLargeObjectsExample {

                            public static void main(String[] args) throws Exception {
                                // TODO Auto-generated method stub

                                Connection connection = null;
                                PreparedStatement pstatement = null;
                                Scanner scanner = null;
                                String img = "C:/Users/cgoka/Desktop/sample.jpg";

                                try {
                                    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                                    connection = DriverManager.getConnection(
                                            "jdbc:mysql://localhost:3306/jdbc", "root", "123456");
                                    pstatement = connection
                                            .prepareStatement("insert into employee values(?,?,?,?)");
                                    pstatement.setInt(1, 101);
                                    pstatement.setString(2, "Chandra Shekhar");
                                    pstatement.setString(3, "3000");
                                    File file = new File(img);
                                    FileInputStream stream = new FileInputStream(file);
                                    pstatement.setBinaryStream(4, stream);

                                    pstatement.executeUpdate();
                                    System.out.println("Image Inserted");

                                } catch (Exception e) {
                                    e.printStackTrace();
                                }
                            }
                        }

On the above example, we are using the FileInputStream to read the image from the local computer. And passing the FileInputStream to PrepareStatement object using setBinaryStream() method.

To run the above code in your system, you need to give your local image path to img variable.