MySQL-insert/out image

 显示数据库的文件夹路径 

SELECT @@datadir;

 建表

CREATE TABLE images (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  data BLOB,
  PRIMARY KEY (id)
);

 插入

INSERT INTO images (name, data) VALUES ('example.jpg', LOAD_FILE('/path/to/example.jpg'));

 

mysql> INSERT INTO images (name, data) VALUES ('example.jpg', LOAD_FILE('e:/pict
ures/image0.jpg'));
ERROR 1406 (22001): Data too long for column 'data' at row 1 

修改列类型：
ALTER TABLE images MODIFY COLUMN data LONGBLOB;

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 InsertImage {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/mydatabase";
    String username = "root";
    String password = "mypassword";
    String filePath = "C:\\Users\\MI\\Desktop\\pictures\\image0.jpg";
    String query = "INSERT INTO images (name, data) VALUES (?, ?)";

    try (Connection conn = DriverManager.getConnection(url, username, password);
         PreparedStatement pstmt = conn.prepareStatement(query)) {
      File file = new File(filePath);
      FileInputStream fis = new FileInputStream(file);
      pstmt.setString(1, file.getName());
      pstmt.setBinaryStream(2, fis, file.length());
      pstmt.executeUpdate();
      System.out.println("Image inserted successfully.");
    } catch (SQLException | IOException e) {
      System.out.println(e.getMessage());
    }
  }
}

请注意，在 SQL 中使用反斜杠时，需要用另一个反斜杠进行转义。例如，如果您想使用文件路径 C:\Users\MI\Desktop\pictures\image0.jpg，则需要在 SQL 语句中将其写为 C:\\Users\\MI\\Desktop\\pictures\\image0.jpg。 

 

使用jdbc

import java.sql.*;

public class InsertImage {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/mydatabase";
    String username = "root";
    String password = "mypassword";
    String filePath = "C:\\Users\\MI\\Desktop\\pictures\\image0.jpg";
    String query = "INSERT INTO images (name, data) VALUES (?, LOAD_FILE(?))";

    try (Connection conn = DriverManager.getConnection(url, username, password);
         PreparedStatement pstmt = conn.prepareStatement(query)) {
      pstmt.setString(1, "example.jpg");
      pstmt.setString(2, filePath);
      pstmt.executeUpdate();
      System.out.println("Image inserted successfully.");
    } catch (SQLException e) {
      System.out.println(e.getMessage());
    }
  }
}

若一次性插入多张图片...

1.使用for

import java.sql.*;
import java.io.File;
import java.util.ArrayList;
import java.util.List;

public class InsertImages {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/mydatabase";
    String username = "root";
    String password = "mypassword";
    List<String> filePaths = new ArrayList<>();
    filePaths.add("C:\\Users\\MI\\Desktop\\pictures\\image0.jpg");
    filePaths.add("C:\\Users\\MI\\Desktop\\pictures\\image1.jpg");
    filePaths.add("C:\\Users\\MI\\Desktop\\pictures\\image2.jpg");
    String query = "INSERT INTO images (name, data) VALUES (?, LOAD_FILE(?))";

    try (Connection conn = DriverManager.getConnection(url, username, password);
         PreparedStatement pstmt = conn.prepareStatement(query)) {
      for (String filePath : filePaths) {
        File file = new File(filePath);
        pstmt.setString(1, file.getName());
        pstmt.setString(2, filePath);
        pstmt.executeUpdate();
        System.out.println(file.getName() + " inserted successfully.");
      }
    } catch (SQLException e) {
      System.out.println(e.getMessage());
    }
  }
}

2.使用递归

import java.sql.*;
import java.io.File;
import java.util.ArrayList;
import java.util.List;

public class InsertImages {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/mydatabase";
    String username = "root";
    String password = "mypassword";
    List<String> filePaths = new ArrayList<>();
    filePaths.add("C:\\Users\\MI\\Desktop\\pictures\\image0.jpg");
    filePaths.add("C:\\Users\\MI\\Desktop\\pictures\\image1.jpg");
    filePaths.add("C:\\Users\\MI\\Desktop\\pictures\\image2.jpg");
    String query = "INSERT INTO images (name, data) VALUES (?, LOAD_FILE(?))";

    try (Connection conn = DriverManager.getConnection(url, username, password);
         PreparedStatement pstmt = conn.prepareStatement(query)) {
      insertImage(filePaths, pstmt);
    } catch (SQLException e) {
      System.out.println(e.getMessage());
    }
  }

  private static void insertImage(List<String> filePaths, PreparedStatement pstmt) throws SQLException {
    if (filePaths.isEmpty()) {
      return;
    }

    String filePath = filePaths.remove(0);
    File file = new File(filePath);
    pstmt.setString(1, file.getName());
    pstmt.setString(2, filePath);
    pstmt.executeUpdate();
    System.out.println(file.getName() + " inserted successfully.");

    insertImage(filePaths, pstmt);
  }
}

 PreparedStatement 可以防止 SQL 注入攻击。当您使用字符串拼接来构建 SQL 语句时，攻击者可以通过在字符串中插入恶意代码来执行任意 SQL 命令。例如，如果您的代码接受用户输入并将其用作 SQL 查询的一部分，攻击者可以通过输入 '; DROP TABLE users; -- 来删除您的用户表。使用 PreparedStatement 可以避免这种情况，因为它会将输入参数作为参数化查询的一部分，而不是将其直接插入 SQL 语句中。

 sql注入示例：

String username = request.getParameter("username");
String password = request.getParameter("password");
String query = "SELECT * FROM users WHERE username='" + username + "' AND password='" + password + "'";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query);

username = ' OR 1=1 -- 

SELECT * FROM users WHERE username='' OR 1=1 --' AND password='...'

 

图片输出：

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package NewCoder;

/**
 *
 * @author MI
 */
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;

import java.awt.image.BufferedImage;
import java.io.ByteArrayInputStream;

import java.io.InputStream;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.imageio.ImageIO;
public class SQL_InsertAndOutput {
    public void insertImage() {
        String url = "jdbc:mysql://localhost:3306/test";
        String username = "root";
        String password = "chenzhao";
        String filePath = "C:\\Users\\MI\\Desktop\\pictures\\image0.jpg";
        String query = "INSERT INTO images (name, data) VALUES (?, ?)";

        try (Connection conn = DriverManager.getConnection(url, username, password);
             PreparedStatement pstmt = conn.prepareStatement(query)) {
          File file = new File(filePath);
          FileInputStream fis = new FileInputStream(file);
          pstmt.setString(1, file.getName());
          pstmt.setBinaryStream(2, fis, file.length());
          pstmt.executeUpdate();
          System.out.println("Image inserted successfully.");


        } catch (SQLException | IOException e) {
          System.out.println(e.getMessage());
        }
    }
    public void outputImage() {
        String url = "jdbc:mysql://localhost:3306/test";
        String username = "root";
        String password = "chenzhao";
        try(Connection conn = DriverManager.getConnection(url, username, password);
            Statement stmt = conn.createStatement();) {
          // Retrieve binary data from database
            ResultSet rs = stmt.executeQuery("SELECT data FROM images WHERE id = 6");
            if (rs.next()) {
              byte[] imageData = rs.getBytes("data");
              // Convert binary data to image format
              InputStream in = new ByteArrayInputStream(imageData);
              BufferedImage image = ImageIO.read(in);
              // Use the 'image' object to display the image in your application
              // Save image to file
              String path = "e:/picture/example.jpg";
              File outputfile = new File(path);
              ImageIO.write(image, "jpg", outputfile);
              System.out.println("successfully! picture at " + "e:/picture/example.jpg");
            }  
        } catch (SQLException | IOException e) {
          System.out.println(e.getMessage());
        }
    }
  public static void main(String[] args) {
//      new SQL_InsertAndOutput().insertImage();
     new SQL_InsertAndOutput().outputImage();
    
  }
}

SHOW CREATE TABLE test.images;