How to store images in MySQL

1 Introduction

When designing a database, it is inevitable to insert images or audio files into the database. Generally speaking, we can avoid the trouble of inserting directly into the database by inserting the corresponding storage location of the image file instead of the file itself. But sometimes, inserting images into MySQL is more manageable. So how to store it in MySQL?

Reference [1] contains a fairly clear example, but it is based on the MySQL graphical query tool Query Brower. If you don’t have it installed on your machine, you may not be able to understand it well. I won't go into details here, please see the link provided for more detailed information.

In addition, the example in [1] only illustrates the ease of use and power of Query Brower, but it does not have much practical application in our development. So let's use JAVA to write a simple example of storing data in MySQL.

2 Create a table

First, you need to create a table in the database. I created a table called pic in a database called test. The table includes 3 columns, idpic, caption and img. Among them, idpic is the primary key, caption is the description of the picture, and img is the image file itself. The SQL statement for creating the table is as follows:

DROP TABLE IF EXISTS `test`.`pic`;
CREATE TABLE `test`.`pic` (
 `idpic` int(11) NOT NULL auto_increment,
 `caption` varchar(45) NOT NULL default '',
 `img` longblob NOT NULL,
 PRIMARY KEY (`idpic`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Enter the above statement into the command line (if Query Brower is installed, you can follow the instructions in reference [1] to create the table, which will be more convenient.), execute it, and the table will be created successfully.

3 Implementing image storage class

After the table is completed, we start writing a Java class to complete the operation of inserting pictures into the database. We know that Java and database connections are achieved through JDBC driver. I use MySQL Connector/J provided on the MySQL website. If you use another type of driver, there may be slight differences in the implementation below.

3.1 Load the JDBC driver and establish a connection

The DriverManager interface provided in JDK is used to manage the connection between Java Application and JDBC Driver. Before using this interface, DriverManager needs to know the JDBC driver to connect to. The simplest way is to use Class.forName() to register the interface class that implements java.sql.Driver with DriverManager. For MySQL Connector/J, the class name is com.mysql.jdbc.Driver.

The following simple example shows how to register the Connector/J Driver.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
 
public class LoadDriver {
  public static void main(String[] args) {
    try {
      // The newInstance() call is a work around for some
      // broken Java implementations
      Class.forName("com.mysql.jdbc.Driver").newInstance();
      
      // Connection con = DriverManager.getConnection(……)
      // ...
    } catch (Exception ex) {
      // handle the error
    }
}

After registering the driver with DriverManager, we can get the connection to the database by calling DriverManager.getConnection() method. In fact, this statement exists in the example above, but it is commented out. There will be a complete example in the following implementation.

3.2 PreparedStatement

After completing the above steps, we can create a Statement interface class through the established connection to execute some SQL statements. In the following example, I use PreparedStatement and CallableStatement, which can execute some stored procedures and functions, but I won’t talk about them here. The following code snippet inserts a record into the pic table. Where (1) the object con of the Connection interface obtains the precompiled SQL statement by calling the prepareStatement method; (2) assigns a value to the first question mark of the insert statement, (3) assigns a value to the second, and (4) assigns a value to the third. This step is also the most noteworthy. The method used is setBinaryStream(). The first parameter 3 refers to the third question mark, fis is a binary file stream, and the third parameter is the length of the file stream.

PreparedStatement ps;
…
ps = con.prepareStatement("insert into PIC values ​​(?,?,?)"); // (1)
ps.setInt(1, id); //(2)
ps.setString(2, file.getName()); (3)
ps.setBinaryStream(3, fis, (int)file.length()); (4)
ps.executeUpdate();
…

3.3 Complete code

The complete code is listed above.

package com.forrest.storepic;
 import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
 
/**
 * This class describes how to store picture file into MySQL.
 * @author Yanjiang Qian
 * @version 1.0 Jan-02-2006
 */
public class StorePictures {
  
  private String dbDriver;
  private String dbURL;
  private String dbUser;
  private String dbPassword;
  private Connection con;
  private PreparedStatement ps; 
 
  public StorePictures() {
    dbDriver = "com.mysql.jdbc.Driver";
    dbURL = "jdbc:mysql://localhost:3306/test";
    dbUser = "root";
    dbPassword = "admin";
    initDB();
  }
  
  public StorePictures(String strDriver, String strURL,
      String strUser, String strPwd) {
    dbDriver = strDriver;
    dbURL = strURL;
    dbUser = strUser;
    dbPassword = strPwd;
    initDB();
  }
 
  public void initDB() {
    try {
      // Load Driver
      Class.forName(dbDriver).newInstance();
      // Get connection
      con = DriverManager.getConnection(dbURL,
          dbUser, dbPassword);      
    } catch(ClassNotFoundException e) {
      System.out.println(e.getMessage());
    } catch(SQLException ex) {
      // handle any errors
      System.out.println("SQLException: " + ex.getMessage());
      System.out.println("SQLState: " + ex.getSQLState());
      System.out.println("VendorError: " + ex.getErrorCode());
 
    } catch (Exception e) {
      System.out.println(e.getMessage());
    }
  }
 
  public boolean storeImg(String strFile) throws Exception {
    boolean written = false;
    if (con == null)
      written = false;
    else {
      int id = 0;
      File file = new File(strFile);
      FileInputStream fis = new FileInputStream(file);
      
      try {       
        ps = con.prepareStatement("SELECT MAX(idpic) FROM PIC");
        ResultSet rs = ps.executeQuery();
        
        if(rs != null) {
          while(rs.next()) {
            id = rs.getInt(1)+1;
          }
        } else {    
          return written;
        }
        
        ps = con.prepareStatement("insert "
            + "into PIC values ​​(?,?,?)");
        ps.setInt(1, id);
        ps.setString(2, file.getName());
        ps.setBinaryStream(3, fis, (int) file.length());
        ps.executeUpdate();
        
        written = true;
      } catch (SQLException e) {
        written = false;
        System.out.println("SQLException: "
            + e.getMessage());
        System.out.println("SQLState: "
            + e.getSQLState());
        System.out.println("VendorError: "
            + e.getErrorCode());
        e.printStackTrace();
      finally       
        ps.close();
        fis.close();
        // close db con
        con.close();
      }
    }
    return written;
  }
  
  /**
   * Start point of the program
   * @param args CMD line
   */
  public static void main(String[] args) {
    if(args.length != 1) {
      System.err.println("java StorePictures filename");
      System.exit(1);
    }
    boolean flag = false;
    StorePictures sp = new StorePictures();
    try {
      flag = sp.storeImg(args[0]);
    } catch (Exception e) {
      e.printStackTrace();
    }
    if(flag) {
      System.out.println("Picture uploading is successful.");
    } else {
      System.out.println("Picture uploading is failed.");
    }
  }
}

4 Conclusion

At this point, we have introduced the whole process of saving pictures in MySQL. This example is the simplest one. Readers can add other functions according to their actual needs, such as reading files, deleting, etc., to make the whole program more perfect. When writing this article, I mainly referred to reference [2], and I would like to thank it here. Reference [3] is a very beautiful example, which not only allows saving but also reading, and has a very intuitive graphical interface. Those who are interested can study it in depth.

The above is the method of storing pictures in MySQL that I introduced to you. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!
If you find this article helpful, please feel free to reprint it and please indicate the source. Thank you!