如何将MySQL中存储的BLOB类型转换为MultipartFile？

我在项目中将附件以BLOB类型存储在MySQL数据库中，现在需要把它检索出来转换为MultipartFile类型，同时补充完善代码逻辑以实现SMTP邮件发送功能。以下是现有代码示例：

try {
    Connection conn = this.getConnection();
    try(PreparedStatement stmt = conn.prepareStatement(sql)) {
        stmt.setInt(1,mail_id);
        try(ResultSet rs = stmt.executeQuery()) {
            while (rs.next()){

                mailModel.setEmailId(rs.getInt("email_id"));
                mailModel.setEmailAddress(rs.getString("email_address"));
                mailModel.setMsgTemplate(rs.getString("msg_template"));
                mailModel.setPriority(rs.getInt("priority"));
                String jsonData = rs.getString("parameters");
                List<MailModel.Parameter> parameters = parseJsonParameters(jsonData);
                mailModel.setParameters(parameters);

                Blob blob = rs.getBlob("attachment");
                
            }
            return mailModel;
        }
    }
} 

1. Blob转MultipartFile实现 #

需要将Blob的二进制数据包装为MultipartFile实例，建议在数据库中额外存储附件文件名（比如新增attachment_name字段），方便后续邮件附件命名。可以用Spring提供的MockMultipartFile，或自定义实现MultipartFile接口避免依赖测试包。

2. 完善SMTP邮件发送逻辑 #

借助JavaMailSender构建带附件的MIME邮件，完成发送流程。

以下是补充完整后的代码：

import org.springframework.mail.javamail.JavaMailSender;
import org.springframework.mail.javamail.MimeMessageHelper;
import org.springframework.web.multipart.MultipartFile;
import jakarta.mail.internet.MimeMessage;
import java.io.InputStream;
import java.util.List;

// 注入JavaMailSender实例
private JavaMailSender mailSender;

public MailModel retrieveAndSendMail(int mail_id) throws Exception {
    MailModel mailModel = new MailModel();
    // 注意SQL需包含attachment_name字段
    String sql = "SELECT email_id, email_address, msg_template, priority, parameters, attachment, attachment_name FROM your_mail_table WHERE mail_id = ?";
    
    try {
        Connection conn = this.getConnection();
        try(PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, mail_id);
            try(ResultSet rs = stmt.executeQuery()) {
                while (rs.next()){
                    // 填充邮件基础信息
                    mailModel.setEmailId(rs.getInt("email_id"));
                    String recipient = rs.getString("email_address");
                    mailModel.setEmailAddress(recipient);
                    String msgContent = rs.getString("msg_template");
                    mailModel.setMsgTemplate(msgContent);
                    mailModel.setPriority(rs.getInt("priority"));
                    String jsonData = rs.getString("parameters");
                    List<MailModel.Parameter> parameters = parseJsonParameters(jsonData);
                    mailModel.setParameters(parameters);

                    // 处理Blob转MultipartFile
                    Blob blob = rs.getBlob("attachment");
                    MultipartFile attachment = null;
                    if (blob != null) {
                        String fileName = rs.getString("attachment_name");
                        try(InputStream inputStream = blob.getBinaryStream()) {
                            // 使用MockMultipartFile包装
                            attachment = new org.springframework.mock.web.MockMultipartFile(
                                fileName,
                                fileName,
                                "application/octet-stream", // 根据实际文件类型调整MIME
                                inputStream
                            );
                        }
                    }

                    // 执行SMTP邮件发送
                    sendMail(recipient, msgContent, attachment);
                }
                return mailModel;
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
        throw e;
    }
}

// 邮件发送核心方法
private void sendMail(String recipient, String content, MultipartFile attachment) throws Exception {
    MimeMessage mimeMessage = mailSender.createMimeMessage();
    // 第二个参数true表示支持多部分邮件（带附件）
    MimeMessageHelper helper = new MimeMessageHelper(mimeMessage, true, "UTF-8");
    
    helper.setTo(recipient);
    helper.setSubject("邮件主题"); // 根据业务需求动态设置
    helper.setText(content, true); // true表示支持HTML格式内容
    
    // 添加附件（如果存在）
    if (attachment != null && !attachment.isEmpty()) {
        helper.addAttachment(attachment.getOriginalFilename(), attachment);
    }
    
    mailSender.send(mimeMessage);
}

可选：自定义MultipartFile实现（避免依赖测试包） #

如果生产环境不想引入Spring测试包，可自定义实现MultipartFile接口：

import org.springframework.web.multipart.MultipartFile;
import java.io.*;

public class BlobMultipartFile implements MultipartFile {
    private final String fileName;
    private final byte[] content;
    private final String contentType;

    public BlobMultipartFile(String fileName, byte[] content, String contentType) {
        this.fileName = fileName;
        this.content = content;
        this.contentType = contentType;
    }

    @Override
    public String getName() {
        return fileName;
    }

    @Override
    public String getOriginalFilename() {
        return fileName;
    }

    @Override
    public String getContentType() {
        return contentType;
    }

    @Override
    public boolean isEmpty() {
        return content == null || content.length == 0;
    }

    @Override
    public long getSize() {
        return content.length;
    }

    @Override
    public byte[] getBytes() throws IOException {
        return content;
    }

    @Override
    public InputStream getInputStream() throws IOException {
        return new ByteArrayInputStream(content);
    }

    @Override
    public void transferTo(File dest) throws IOException, IllegalStateException {
        try(FileOutputStream fos = new FileOutputStream(dest)) {
            fos.write(content);
        }
    }
}

替换使用方式：

byte[] blobBytes = blob.getBytes(1, (int) blob.length());
attachment = new BlobMultipartFile(fileName, blobBytes, "application/pdf"); // 示例为PDF类型

注意事项 #

• 确保数据库表中存储附件文件名，否则无法正确设置邮件附件的显示名称。
• 根据附件实际类型调整MIME类型（如图片用image/jpeg，Excel用application/vnd.openxmlformats-officedocument.spreadsheetml.sheet）。
• 需在Spring配置中正确配置JavaMailSender的SMTP参数（主机、端口、账号、授权码等）。

内容的提问来源于stack exchange，提问作者Chandra Mayou