在之前的几篇文章 中,如何使用 JDBC 将文件存储在 MySQL 数据库中以及 如何使用 JDBC 从 MySQL 数据库中读取文件/CLOB 数据, 我们学习了如何在 MYSQL 数据库中保存文本文件或 CLOB(字符大对象)数据以及如何将其读回从数据库。在这篇文章中,我们将学习如何使用 JDBC 编写和读取 MySQL BLOB/二进制文件
在项目主目录中创建一个文件夹InputFiles并转储所有二进制文件( .doc、PDF、图像、音频、视频等)
eclipse中的项目结构:
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
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 控制台上查看以下输出:
1
2
3
4
5
6
7
8
9
10
|
Following files are saved in database..
GST.pdf
java collection .jpg
JDBC Introduction.docx
JDBC Introduction.pptx
----------------------------------------
1
1
1
1
|
如果您 在 MYSQL 数据库的jdbcdb模式中查询storebinaryfile_table 。您会发现所有二进制文件都保存为如下屏幕截图。
现在让我们从 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 控制台上查看以下输出:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
Following flies are downloaded from database..
File Id:1
File Name:GST.pdf
File Size In KB:760
File Extension:pdf
-----------------------------------
File Id:2
File Name:java collection .jpg
File Size In KB:51
File Extension:jpg
-----------------------------------
File Id:3
File Name:JDBC Introduction.docx
File Size In KB:69
File Extension:docx
-----------------------------------
File Id:4
File Name:JDBC Introduction.pptx
File Size In KB:107
File Extension:pptx
-----------------------------------
|
现在只需刷新您的项目并展开DownLoadFiles文件夹,您将看到所有二进制文件都从数据库下载并写入此文件夹,如下面的屏幕截图所示。
这就是使用 JDBC 编写和读取 MySQL BLOB/二进制文件的全部内容
你可能也会喜欢:
如何使用 JDBC 将文件存储在 MySQL 数据库中如何使用JDBC 示例中的
JDBC ResultSetMetaData 从 MySQL 数据库中读取文件/CLOB 数据如何使用 JDBC 从插入的查询中获取主键值(自动生成的键)?