使用 JDBC 批量上传文件夹内多个文件到MySQL并批量下载

阳光穿透心脏的1/2处 2023-09-29 09:13 113阅读 0赞

在之前的几篇文章 中，[如何使用 JDBC 将文件存储在 MySQL 数据库中][JDBC _ MySQL]以及 [如何使用 JDBC 从 MySQL 数据库中读取文件/CLOB 数据，][JDBC _ MySQL _CLOB] 我们学习了如何在 MYSQL 数据库中保存文本文件或 CLOB（字符大对象）数据以及如何将其读回从数据库。在这篇文章中，我们将学习如何使用 JDBC 编写和读取 MySQL BLOB/二进制文件

在项目主目录中创建一个文件夹**InputFiles**并转储所有二进制文件**（** .doc、PDF、图像、音频、视频等**）**

**eclipse中的项目结构：**

![4493ea1fee3930026a33578b01f7686a.gif][]

**DB.sql**在 MySQL 数据库（**jdbcdb 模式）**中创建**storebinaryfile\_table 的 SQL 查询**

CREATE TABLE `storebinaryfile_table` (
      `file_id` int(11) NOT NULL auto_increment,
      `file_name` varchar(45) NOT NULL,
      `file_size_in_kb` bigint(20) NOT NULL,
      `file_extension` char(30) NOT NULL,
      `file_content` longblob NOT NULL,
      PRIMARY KEY  (`file_id`)
    ) ;storebinaryfile_table

**DBUtil.java** 类，负责连接 MySQL 数据库。

package com.infotech.util;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    
    public class DBUtil {
    
        private static final String DB_DRIVER_CLASS = "com.mysql.jdbc.Driver";
        private static final String DB_USERNAME = "root";
        private static final String DB_PASSWORD = "root";
        private static final String DB_URL = "jdbc:mysql://localhost:3306/jdbcdb?autoReconnect=true&useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
        private static Connection connection = null;
    
        static {
            try {
                Class.forName(DB_DRIVER_CLASS);
                connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
            } catch (ClassNotFoundException | SQLException e) {
                e.printStackTrace();
            }
        }
    
        public static Connection getConnection() {
            return connection;
        }
    }

**SaveBinaryFilesInDBClientTest.java**类用于在 MYSQL 数据库 中保存四个二进制文件（**GST.pdf、java 集合.jpg、JDBC Introduction.docx 和 JDBC Introduction.pptx** ） 。

package com.infotech.client;
 
 import java.io.File;
 import java.io.FileInputStream;
 import java.io.IOException;
 import java.nio.file.Files;
 import java.nio.file.Path;
 import java.nio.file.Paths;
 import java.sql.Connection;
 import java.sql.PreparedStatement;
 import java.sql.SQLException;
 import java.util.List;
 import java.util.stream.Collectors;
 import java.util.stream.Stream;
 
 import com.infotech.util.DBUtil;
 
 /**
 * @author KK JavaTutorials JDBC program to write or save binary data/BLOB data
 * in database
 */
 public class SaveBinaryFilesInDBClientTest {
 
 public static void main(String[] args) throws SQLException {
 saveBinaryFilesInDatabase();
 }
 
 private static void saveBinaryFilesInDatabase() throws SQLException {
 String SQL = "INSERT INTO storebinaryfile_table (file_name,file_size_in_kb,file_extension,file_content)VALUES(?,?,?,?)";
 Path dir = Paths.get("InputFiles");
 try ( Stream<Path> list = Files.list(dir); Connection connection = DBUtil.getConnection(); PreparedStatement ps = connection.prepareStatement(SQL)) {
 /*
 			 *Below line belongs to JDK 1.8 API so make sure you are running this code on JDK 1.8.
 			 *And you IDE pointing to compiler version 1.8
 */
 List<Path> pathList = list.collect(Collectors.toList());
 System.out.println("Following files are saved in database..");
 for (Path path : pathList) {
 System.out.println(path.getFileName());
 File file = path.toFile();
 String fileName = file.getName();
 long fileLength = file.length();
 long fileLengthInKb = fileLength / 1024;
 
 ps.setString(1, fileName);
 ps.setLong(2, fileLengthInKb);
 
 ps.setString(3, fileName.substring(fileName.lastIndexOf(".") + 1));
 
 FileInputStream fis = new FileInputStream(file);
 ps.setBinaryStream(4, fis, fileLength);
 
 ps.addBatch();
 }
 System.out.println("----------------------------------------");
 int[] executeBatch = ps.executeBatch();
 for (int i : executeBatch) {
 System.out.println(i);
 }
 } catch (IOException e) {
 e.printStackTrace();
 }
 }
 }

运行**SaveBinaryFilesInDBClientTest** **.java** 程序后，您将在 Eclipse 控制台上查看以下输出：

如果您 在 MYSQL 数据库的**jdbcdb模式中查询****storebinaryfile\_table** 。您会发现所有二进制文件都保存为如下屏幕截图。

![95d3f89bb62d549ab10b3bfa2486b49a.gif][]

现在让我们从 MySQL 数据库中读取这些二进制文件并写入 项目主目录中的**DownLoadFiles**文件夹。

**DownloadBinaryFilesFromDBClientTest.java** 类用于从 MYSQL 数据库中读取所有二进制文件，并写入 项目主目录的**DownLoadFiles文件夹中。**

**注意：**您必须 在项目主目录中手动创建**DownLoadFiles文件夹（您可以修改此程序以自动创建文件夹）。**

package com.infotech.client;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.nio.file.Files;
    import java.nio.file.Paths;
    import java.sql.Blob;
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    import com.infotech.util.DBUtil;
    
    /**
     * @author KK JavaTutorials JDBC program to read binary data/BLOB data from
     * database and write into local disk.
     */
    public class DownloadBinaryFilesFromDBClientTest {
    
        public static void main(String[] args) throws SQLException {
            downloadBinaryFilesFromDatabase();
        }
    
        private static void downloadBinaryFilesFromDatabase() throws SQLException {
    
            String SQL = "SELECT *FROM storebinaryfile_table";
            try ( Connection connection = DBUtil.getConnection();  PreparedStatement ps = connection.prepareStatement(SQL);  ResultSet rs = ps.executeQuery()) {
                System.out.println("Following flies are downloaded from database..");
                while (rs.next()) {
                    int fileId = rs.getInt("file_id");
                    String fileName = rs.getString("file_name");
                    long fileSizeInKb = rs.getLong("file_size_in_kb");
                    String fileExtension = rs.getString("file_extension");
                    System.out.println("File Id:" + fileId);
                    System.out.println("File Name:" + fileName);
                    System.out.println("File Size In KB:" + fileSizeInKb);
                    System.out.println("File Extension:" + fileExtension);
    
                    Blob blob = rs.getBlob("file_content");
                    InputStream inputStream = blob.getBinaryStream();
    
                    System.out.println("-----------------------------------");
                    Files.copy(inputStream, Paths.get("DownLoadFiles/" + fileName));
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

**运行上述客户端程序后，您将在 Eclipse 控制台上查看以下输出：**

<table style="margin-left:-30px;"> 
 <tbody> 
 <tr> 
 <td style="background-color:#eeeeee;text-align:left;vertical-align:top;"> 
 <div style="margin-left:0;text-align:left;"> 
 <div style="margin-left:0;text-align:center;"> 
 1 
 </div> 
 <div style="margin-left:0;text-align:center;"> 
 2 
 </div> 
 <div style="margin-left:0;text-align:center;"> 
 3 
 </div> 
 <div style="margin-left:0;text-align:center;"> 
 4 
 </div> 
 <div style="margin-left:0;text-align:center;"> 
 5 
 </div> 
 <div style="margin-left:0;text-align:center;"> 
 6 
 </div> 
 <div style="margin-left:0;text-align:center;"> 
 7 
 </div> 
 <div style="margin-left:0;text-align:center;"> 
 8 
 </div> 
 <div style="margin-left:0;text-align:center;"> 
 9 
 </div> 
 <div style="margin-left:0;text-align:center;"> 
 10 
 </div> 
 <div style="margin-left:0;text-align:center;"> 
 11 
 </div> 
 <div style="margin-left:0;text-align:center;"> 
 12 
 </div> 
 <div style="margin-left:0;text-align:center;"> 
 13 
 </div> 
 <div style="margin-left:0;text-align:center;"> 
 14 
 </div> 
 <div style="margin-left:0;text-align:center;"> 
 15 
 </div> 
 <div style="margin-left:0;text-align:center;"> 
 16 
 </div> 
 <div style="margin-left:0;text-align:center;"> 
 17 
 </div> 
 <div style="margin-left:0;text-align:center;"> 
 18 
 </div> 
 <div style="margin-left:0;text-align:center;"> 
 19 
 </div> 
 <div style="margin-left:0;text-align:center;"> 
 20 
 </div> 
 <div style="margin-left:0;text-align:center;"> 
 21 
 </div> 
 </div> </td> 
 <td style="text-align:left;vertical-align:top;width:653.812px;"> 
 <div style="margin-left:0;text-align:left;"> 
 <div style="margin-left:0;text-align:left;"> 
 Following flies are downloaded from database.. 
 </div> 
 <div style="margin-left:0;text-align:left;"> 
 File Id:1 
 </div> 
 <div style="margin-left:0;text-align:left;"> 
 File Name:GST.pdf 
 </div> 
 <div style="margin-left:0;text-align:left;"> 
 File Size In KB:760 
 </div> 
 <div style="margin-left:0;text-align:left;"> 
 File Extension:pdf 
 </div> 
 <div style="margin-left:0;text-align:left;"> 
 ----------------------------------- 
 </div> 
 <div style="margin-left:0;text-align:left;"> 
 File Id:2 
 </div> 
 <div style="margin-left:0;text-align:left;"> 
 File Name:java collection .jpg 
 </div> 
 <div style="margin-left:0;text-align:left;"> 
 File Size In KB:51 
 </div> 
 <div style="margin-left:0;text-align:left;"> 
 File Extension:jpg 
 </div> 
 <div style="margin-left:0;text-align:left;"> 
 ----------------------------------- 
 </div> 
 <div style="margin-left:0;text-align:left;"> 
 File Id:3 
 </div> 
 <div style="margin-left:0;text-align:left;"> 
 File Name:JDBC Introduction.docx 
 </div> 
 <div style="margin-left:0;text-align:left;"> 
 File Size In KB:69 
 </div> 
 <div style="margin-left:0;text-align:left;"> 
 File Extension:docx 
 </div> 
 <div style="margin-left:0;text-align:left;"> 
 ----------------------------------- 
 </div> 
 <div style="margin-left:0;text-align:left;"> 
 File Id:4 
 </div> 
 <div style="margin-left:0;text-align:left;"> 
 File Name:JDBC Introduction.pptx 
 </div> 
 <div style="margin-left:0;text-align:left;"> 
 File Size In KB:107 
 </div> 
 <div style="margin-left:0;text-align:left;"> 
 File Extension:pptx 
 </div> 
 <div style="margin-left:0;text-align:left;"> 
 ----------------------------------- 
 </div> 
 </div> </td> 
 </tr> 
 </tbody> 
</table>

现在只需刷新您的项目并展开**DownLoadFiles**文件夹，您将看到所有二进制文件都从数据库下载并写入此文件夹，如下面的屏幕截图所示。

![20a6b2144d45b4c19fda9cf3ac3df9d6.gif][]

这就是使用 JDBC 编写和读取 MySQL BLOB/二进制文件的全部内容

**你可能也会喜欢：**

[如何使用 JDBC 将文件存储在 MySQL 数据库中如何使用][JDBC _ MySQL][JDBC 示例中的][JDBC]  
[JDBC ResultSetMetaData 从 MySQL 数据库中读取文件/CLOB 数据][JDBC _ MySQL _CLOB][如何使用 JDBC 从插入的查询中获取主键值（自动生成的键）？][JDBC 1]

[JDBC _ MySQL]: https://kkjavatutorials.com/how-to-store-file-in-mysql-database-using-jdbc/
[JDBC _ MySQL _CLOB]: https://kkjavatutorials.com/how-to-read-file-from-mysql-database-using-jdbc/
[4493ea1fee3930026a33578b01f7686a.gif]: https://img-blog.csdnimg.cn/img_convert/4493ea1fee3930026a33578b01f7686a.gif
[95d3f89bb62d549ab10b3bfa2486b49a.gif]: https://img-blog.csdnimg.cn/img_convert/95d3f89bb62d549ab10b3bfa2486b49a.gif
[20a6b2144d45b4c19fda9cf3ac3df9d6.gif]: https://img-blog.csdnimg.cn/img_convert/20a6b2144d45b4c19fda9cf3ac3df9d6.gif
[JDBC]: https://kkjavatutorials.com/resultsetmetadata-in-jdbc-example/
[JDBC 1]: https://kkjavatutorials.com/how-to-get-primary-key-value-auto-generated-keys-from-inserted-queries-using-jdbc/