把图片批量导入到MySQL数据库

1、需求

将.bmp图片文件批量插入MySQL数据库，并插入文件名称，图片文件如下所示

2、代码

package com.zaagtech.java;

import java.io.FileInputStream;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.io.File;
import java.sql.Connection;

/**
 * 作者：杨鹏举，2021.7.1
 */

public class InsertPic {

    /**
     * 插入图片到数据库(可以批量)
     */
    public static void InsrtPicToDB(){
        String sql = "insert into photo(name,photo) values (?,?)";
//        String filepath = "C:/Users/ypj/Desktop/file";
        String filepath = "D:/ZaagTech/Total/0618/hfr/01";
        File file = new File(filepath);
        String [] filelist = file.list();

        Connection conn = null;
        PreparedStatement pst = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("开始连接数据库");
            String url = "jdbc:mysql://localhost:3306/test?serverTimezone=UTC";
            String user = "root";
            String password = "203032ypj";
            conn = DriverManager.getConnection(url, user, password);
            pst = conn.prepareStatement(sql);

            int m = 0;
            for(int i=0;i<filelist.length;i++){
                File reaFile = new File(filepath + "\\" + filelist[i]);
                FileInputStream fis = new FileInputStream(reaFile);
                String photoName = "";
//              if(filelist[i].length() >= 6){
                photoName = filelist[i].substring(0, 6); //保存文件名当做一个字段的值
                photoName = filelist[i];
                pst.setString(1, photoName);
                pst.setBinaryStream(2, fis, (int) reaFile.length());
                m++;
                int n = pst.executeUpdate();
                System.out.println(n + "条记录已经插入");
                }
//            }
            System.out.println("本次一共导入" + m + "条");
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            try {
                if(pst != null){
                    pst.close();
                }
                if(conn != null){
                    conn.close();
                }
                System.out.println("数据库关闭");
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    public static void main(String[] args) {
        InsrtPicToDB();
    }

}

3、运行结果

4、数据库查证

5、结论

由上所述可知，通过Java+MySQL可高效地将图片数据信息批量插入数据库。