How to insert pictures in mysql

How to insert pictures in mysql: first create a table in the database; then load the JDBC driver and establish a connection; finally create a Statement interface class to execute SQL statements.

How to insert pictures in mysql:

1. First, create a table in the database. I created a table called pic under the database named 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 to create 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.) , execution, table creation is successful.

2. Implement the 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 the connection between Java and database is achieved through JDBC driver. I use the MySQL Connector/J provided on the MySQL website. If you use other types of drivers, there may be some differences in the following implementation process.

2.1. Load JDBC driver and establish 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 be connected. The simplest method is to use Class.forName() to register the interface class that implements java.sql.Driver with DriverManager. For MySQL Connector/J, the name of this class is com.mysql.jdbc.Driver.

The following simple example illustrates 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 obtain the connection to the database by calling the DriverManager.getConnection() method. In fact, this statement exists in the above example, but it has been commented out. There will be a complete example in the later implementation.

2.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. I won’t go into details here.

The following code snippet inserts a record into the pic table. Among them, the object con of the Connection interface at (1) gets the precompiled SQL statement (precompiled SQL statement) by calling the prepareStatement method; (2) is the assignment of the first question mark of the insert statement, and (3) is the second assignment. , (4) is the third one. This step is also the most important to mention. The method used is setBinaryStream(). The first parameter 3 refers to the third question mark. fis is a binary file stream. The third parameter is The length of this 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();
…

2.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.");
    }
  }
}

More related free learning recommendations: mysql tutorial(Video)

The above is the detailed content of How to insert pictures in mysql. For more information, please follow other related articles on the PHP Chinese website!