Of Stack

Method of storing pictures in MySQL

  • 2021-12-12 10:05:24
  • OfStack

1 Introduction

In the design to database development, it is inevitable to insert pictures or audio files into the database. 1 Generally speaking, we can insert the corresponding storage location of the picture file instead of the file itself, so as to avoid the trouble of inserting it directly into the database. But sometimes, inserting pictures into MySQL is easier to manage. So how to store it in MySQL?

There is a fairly clear example in Resources [1], but the query tool Query Brower based on MySQL graphical interface may not be well understood if it is not installed on your machine. I won't go into details here. Please see the links given for more detailed information.

Also, the example in [1] only shows us the ease of use and power of Query and Brower, which is not very big for our practical application in development. So let's use JAVA to write a simple instance stored in MySQL.

2 Building tables

First of all, you should build tables in the database. I set up a table called pic under the database named test. The table consists of three columns, idpic, caption, and img. Among them, idpic is the primary key, caption is the expression of pictures, and img is the image file itself. The SQL statement for building 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 build the table according to the instructions in reference [1], which is more convenient.) The table was successfully established.

3 to implement image storage class

After the table is complete, we begin to write an Java class to complete the operation of inserting pictures into the database. As we know, the connection between Java and database is realized through JDBC driver. I'm using MySQL Connector/J, which is available on the MySQL website. If you're using other types of driver, there may be some differences in the implementation below.

3.1 Load JDBC driver and establish connection

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

The following simple example shows how to register 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 call DriverManager.getConnection() Method to get a connection to the database. In fact, there is this statement in the above example, but it has been commented out. There will be a complete example in the later implementation.

3.2 PreparedStatement

After completing the above steps, we can create an Statement interface class with the established connection to execute some SQL statements. In the following example, I use PreparedStatement, and CallableStatement, which can execute 1 stored procedures and functions, which will not be discussed here. The following code snippet inserts a record into the pic table. The object con of Connection interface at (1) obtains a precompiled SQL statement (precompiled SQL statement) by calling prepareStatement method; At (2), the first question mark of the insert statement is assigned, (3) is assigned to the second, and (4) is assigned to the third. This step is also mentioned in this first step. 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 Summary

At this point, we have introduced the whole process of saving pictures to MySQL. This example is one of the simplest examples, readers can according to their actual needs to add other 1 some functions, such as reading files, deleting, etc., so that the whole program is more perfect. When writing this article, I mainly refer to reference [2], and thank you here. Reference [3] is a pretty example, not only save and read, but also has a very intuitive graphical interface, interested in in-depth study.

Related articles: