在之前的两个工具类的基础上又封装了一个通用文件上传并入库工具类，完美解决了附件上传的问题。注意此工具类操作有点危险，原因是泛微OA的一些底层机制影响的。具体看代码吧：
package com.api.zp.interfaces.util;
import com.alibaba.fastjson.JSONObject;
import com.api.doc.detail.service.DocAccService;
import com.api.doc.detail.service.DocSaveService;
import weaver.conn.RecordSet;
import weaver.general.BaseBean;
import weaver.hrm.User;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.net.HttpURLConnection;
import java.net.URL;
import java.util.*;
/**
* 支持：
* 1. 附件上传到文档中心，返回docId
* 2. 业务表数据插入/更新（唯一字段判断）
* 3. 字段映射+自定义字段优先合并
* 4. 自动删除旧附件记录和文件
*
* @Author zF.
* @ClassName FileUploadProcessorUtil.java
* @ProjectName 通用文件上传并入库工具类
*/
public class FileUploadProcessorUtil {
private static final BaseBean baseBean = new BaseBean();
/**
* 之前的那些人写文件上传并入库都要重新了解一遍泛微OA的底层逻辑，还需要单独更新对应的两张文件表中的数据。
* 需要编写大量代码来实现这些操作，且极容易出错。在写附件上传的时候，由于没有文档，我阅读了本项目中大量前人留下的坑。
* 例如：docCategoryId这个字段原有的位置，因为泛微命名其为var1，所以不明其意。最后连线泛微的官方工作人员才知道那个位置是什么东西。
* 我在仔细了解了当前项目的交互逻辑之后，遂封装了这个工具类。从此以后，继任的开发者再也不需要关心泛微OA的底层逻辑。
* 只需要按照我要求的传参，即可直接实现附件上传功能。实在是太优雅啦，当然，如有兴趣你也可以直接阅读本工具类代码，注释非常详细。
*/
/**
* 通用文件上传+入库（支持自动上传文件并入库）
*
* @param body json数据体
* @param updateFields 字段映射
* @param uniqueFields 唯一字段
* @param tableName 业务表
* @param attachmentFieldName 附件字段名
* @param fileList 附件列表（每个map含fileUrl、fileName）
* @param docCategoryId 文档目录ID
* @param userId 用户ID
* @param customFields 自定义字段
*/
public static Map<String, Object> processDbDataAndUpsert(JSONObject body, Map<String, String> updateFields, List<String> uniqueFields,
String tableName, String attachmentFieldName, List<Map<String, String>> fileList,
int docCategoryId, int userId, Map<String, Object> customFields) {
// 环境判断：只允许在生产环境（Linux）运行
String osName = System.getProperty("os.name").toLowerCase();
if (osName.contains("windows")) {
// 原因是本地运行该工具类后，由于共用数据库的缘故。返回的imageFileId和docId并不是你上传的，而是线上生产环境其他业务真实上传的附件信息。
// 这是泛微OA的特性（获取线上最后一个文件的ID），无解。执行本方法之后，就会把线上其他业务的附件给删除掉。
// 如果非要运行，那就把这个判断注释掉。但一定不能在本地运行deleteOldAttachments方法，这样就不会删掉线上的文件数据及记录。
// 测试完方法，记得手动删掉其对应的IMAGEFILEID表和DOCIMAGEFILE表的数据库记录。
// 另外其他代码中使用的删除附件的方法是错的，需要使用：docAccService.deleteDocAcc，至于原因，有兴趣可以读一下这个方法的代码。
throw new RuntimeException("严禁在开发环境运行本工具类，只能在生产环境（Linux）环境下运行！当前操作系统：" + osName);
}
// 1. 上传文件到文档中心，收集docId
List<Integer> docIds = new ArrayList<>();
if (fileList != null) {
for (Map<String, String> file : fileList) {
String fileUrl = file.get("fileUrl");
String fileName = file.get("fileName");
if (fileUrl != null && fileName != null) {
int docId = uploadFileToDocCenter(fileUrl, fileName, docCategoryId, userId);
if (docId > 0) {
docIds.add(docId);
}
}
}
}
// 2. 字段映射
Map<String, Object> dbData = mapJsonToDbFields(body, updateFields);
// 3. 合并自定义字段（优先级高），并保证所有key为大写
if (customFields != null) {
for (Map.Entry<String, Object> entry : customFields.entrySet()) {
dbData.put(entry.getKey(), entry.getValue());
}
}
// 4. 删除旧附件（如果存在）——用合并后的 dbData
if (attachmentFieldName != null && !attachmentFieldName.isEmpty()) {
Map<String, Object> whereMap = buildWhereClause(uniqueFields, dbData);
String whereClause = (String) whereMap.get("whereClause");
List<Object> whereParams = (List<Object>) whereMap.get("whereParams");
if (whereClause.length() > 0) {
deleteOldAttachments(tableName, whereClause, whereParams, attachmentFieldName, new User(userId));
}
}
// 5. 组装附件字段
putAttachmentField(dbData, attachmentFieldName, docIds);
// 6. 保证所有key为大写（包括customFields）
Map<String, Object> upperDbData = new HashMap<>();
for (Map.Entry<String, Object> entry : dbData.entrySet()) {
upperDbData.put(entry.getKey().toUpperCase(), entry.getValue());
}
// 7. 唯一字段大写
List<String> upperUniqueFields = new ArrayList<>();
for (String f : uniqueFields) upperUniqueFields.add(f.toUpperCase());
// 8. 数据库操作，始终用同一个RecordSet
RecordSet rs = new RecordSet();
upsertByDbFieldOrder(rs, tableName, upperDbData, upperUniqueFields);
// 返回最终数据
Map<String, Object> result = new HashMap<>();
result.put("upperDbData", upperDbData);
result.put("upperUniqueFields", upperUniqueFields);
result.put("docIds", docIds);
return result;
}
/**
* 通用字段映射：将 body 的 json 字段按映射关系转为 db 字段
*/
public static Map<String, Object> mapJsonToDbFields(JSONObject body, Map<String, String> updateFields) {
Map<String, Object> dbData = new HashMap<>();
for (Map.Entry<String, String> entry : updateFields.entrySet()) {
String jsonField = entry.getKey();
String dbField = entry.getValue();
if (body.containsKey(jsonField)) {
dbData.put(dbField, body.get(jsonField));
}
}
return dbData;
}
/**
* 构建 where 条件和参数（唯一字段大写，值从 dataMap 取）
*/
public static Map<String, Object> buildWhereClause(List<String> uniqueFields, Map<String, Object> dataMap) {
StringBuilder whereClause = new StringBuilder();
List<Object> whereParams = new ArrayList<>();
for (String field : uniqueFields) {
if (whereClause.length() > 0) whereClause.append(" AND ");
whereClause.append(field.toUpperCase()).append(" = ?");
whereParams.add(dataMap.get(field.toUpperCase()));
}
Map<String, Object> result = new HashMap<>();
result.put("whereClause", whereClause.toString());
result.put("whereParams", whereParams);
return result;
}
/**
* 删除业务表中的旧附件记录和文件
*/
public static void deleteOldAttachments(String tableName, String whereClause, List<Object> whereParams, String attachmentFieldName, User user) {
// 环境判断：只允许在生产环境（Linux）运行删除逻辑
String osName = System.getProperty("os.name").toLowerCase();
if (osName.contains("windows")) {
// 原因是本地运行该工具类后，由于共用数据库的缘故。返回的imageFileId和docId并不是你上传的，而是线上生产环境其他业务真实上传的附件信息。
// 这是泛微OA的特性（获取线上最后一个文件的ID），无解。执行本方法之后，就会把线上其他业务的附件给删除掉。
baseBean.writeLog("开发环境下不执行 deleteOldAttachments 方法，当前操作系统：" + osName);
return;
}
RecordSet rs = new RecordSet();
DocAccService docAccService = new DocAccService();
try {
// 查询业务表中现有的附件docId
String querySql = "SELECT " + attachmentFieldName.toUpperCase() + " FROM " + tableName + " WHERE " + whereClause;
baseBean.writeLog("查询旧附件SQL: " + querySql + " 参数: " + whereParams);
rs.executeQuery(querySql, whereParams.toArray());
if (rs.next()) {
String attachmentValue = rs.getString(attachmentFieldName.toUpperCase());
if (attachmentValue != null && !attachmentValue.trim().isEmpty()) {
baseBean.writeLog("发现旧附件记录: " + attachmentValue);
// 处理多个附件的情况（逗号分隔）
String[] docIds = attachmentValue.split(",");
for (String docIdStr : docIds) {
String docId = docIdStr.trim();
if (!docId.isEmpty()) {
try {
int docIdInt = Integer.parseInt(docId);
deleteSingleAttachment(rs, docAccService, docIdInt, user);
} catch (NumberFormatException e) {
baseBean.writeLog("无效的docId格式: " + docId);
}
}
}
}
}
} catch (Exception e) {
baseBean.writeLog("删除旧附件异常: " + e.getMessage());
}
}
/**
* 删除单个附件记录和文件
*
* @param rs 数据库连接
* @param docAccService 附件服务
* @param docId 文档ID
* @param user 用户对象
*/
private static void deleteSingleAttachment(RecordSet rs, DocAccService docAccService, int docId, User user) {
try {
// 查询DOCIMAGEFILE表获取IMAGEFILEID
String imageFileQuery = "SELECT IMAGEFILEID FROM DOCIMAGEFILE WHERE DOCID = ?";
baseBean.writeLog("查询IMAGEFILEID SQL: " + imageFileQuery + " 参数: [" + docId + "]");
rs.executeQuery(imageFileQuery, docId);
if (rs.next()) {
int imageFileId = rs.getInt("IMAGEFILEID");
baseBean.writeLog("找到IMAGEFILEID: " + imageFileId + " 对应DOCID: " + docId);
// 调用泛微底层方法删除文档附件（包含权限检查和引用计数）
baseBean.writeLog("开始删除文档附件，IMAGEFILEID: " + imageFileId + ", DOCID: " + docId);
boolean deleteResult = docAccService.deleteDocAcc(imageFileId, docId, user);
if (deleteResult) {
baseBean.writeLog("成功删除文档附件，DOCID: " + docId + ", IMAGEFILEID: " + imageFileId);
} else {
baseBean.writeLog("删除文档附件失败，可能是权限不足，DOCID: " + docId + ", IMAGEFILEID: " + imageFileId);
}
} else {
baseBean.writeLog("未找到DOCID对应的IMAGEFILEID: " + docId);
}
} catch (Exception e) {
baseBean.writeLog("删除单个附件异常，DOCID: " + docId + ", 错误: " + e.getMessage());
}
}
/**
* 上传文件到文档中心，返回docId
*/
public static int uploadFileToDocCenter(String fileUrl, String fileName, int docCategoryId, int userId) {
try {
baseBean.writeLog("开始上传文件: " + fileName + ", URL: " + fileUrl);
String base64Content = getFileBase64FromUrl(fileUrl);
if (base64Content == null) {
baseBean.writeLog("文件下载失败: " + fileName);
return 0;
}
if (base64Content != null) {
byte[] fileBytes = Base64.getDecoder().decode(base64Content);
baseBean.writeLog("下载的文件大小: " + fileBytes.length + " 字节");
}
DocAccService docAccService = new DocAccService();
DocSaveService docSaveService = new DocSaveService();
User user = new User(userId);
// 备用方案，直接url入参
// int annexId = docAccService.getFileByUrl(fileUrl, fileName);
// 获取附件ID
int annexId = docAccService.getFileByBase64(base64Content, fileName);
// 获取文档ID
int docId = docSaveService.accForDoc(docCategoryId, annexId, user);
baseBean.writeLog("文件上传成功，文档ID: " + docId + ", 文件名: " + fileName);
baseBean.writeLog("文件URL: " + fileUrl);
baseBean.writeLog("文件名: " + fileName);
baseBean.writeLog("Base64内容长度: " + (base64Content != null ? base64Content.length() : 0));
baseBean.writeLog("获取到的annexId: " + annexId);
baseBean.writeLog("获取到的docId: " + docId);
return docId;
} catch (Exception e) {
baseBean.writeLog("文件上传异常: " + e.getMessage());
return 0;
}
}
/**
* 下载文件并转为Base64
*/
public static String getFileBase64FromUrl(String fileUrl) {
InputStream is = null;
ByteArrayOutputStream outStream = null;
HttpURLConnection httpUrl = null;
try {
URL url = new URL(fileUrl);
httpUrl = (HttpURLConnection) url.openConnection();
httpUrl.setRequestMethod("GET");
httpUrl.connect();
is = httpUrl.getInputStream();
outStream = new ByteArrayOutputStream();
byte[] buffer = new byte[1024];
int len;
while ((len = is.read(buffer)) != -1) {
outStream.write(buffer, 0, len);
}
return Base64.getEncoder().encodeToString(outStream.toByteArray());
} catch (Exception e) {
baseBean.writeLog("文件下载异常: " + e.getMessage());
return null;
} finally {
try {
if (is != null) is.close();
if (outStream != null) outStream.close();
if (httpUrl != null) httpUrl.disconnect();
} catch (Exception e) {
baseBean.writeLog("流关闭异常: " + e.getMessage());
}
}
}
/**
* 按数据库字段顺序将数据写入或更新指定表
*
* @param rs 数据库连接
* @param tableName 目标表
* @param data 数据Map
* @param uniqueFields 唯一字段（大写）
*/
public static void upsertByDbFieldOrder(RecordSet rs, String tableName, Map<String, Object> data, List<String> uniqueFields) {
// 1. 构建where条件
StringBuilder whereClause = new StringBuilder();
List<Object> whereParams = new ArrayList<>();
for (String field : uniqueFields) {
if (whereClause.length() > 0) whereClause.append(" AND ");
whereClause.append(field).append(" = ?");
whereParams.add(data.get(field));
}
// 2. 检查记录是否存在
String checkSql = "SELECT COUNT(1) AS CNT FROM " + tableName + " WHERE " + whereClause;
boolean exists = false;
try {
rs.executeQuery(checkSql, whereParams.toArray());
if (rs.next()) exists = rs.getInt("CNT") > 0;
} catch (Exception e) {
baseBean.writeLog("检查记录存在性失败！表[" + tableName + "]，错误：" + e.getMessage());
throw new RuntimeException("记录检查异常！", e);
}
// 3. 获取表字段
List<String> dbFields = getTableFields(rs, tableName, data);
if (dbFields.isEmpty()) {
baseBean.writeLog("警告：表 [" + tableName + "] 无有效字段信息，操作终止！");
return;
}
if (exists) {
// 更新
executeUpdate(rs, tableName, data, dbFields, whereClause.toString(), whereParams);
} else {
// 插入
executeInsert(rs, tableName, data, dbFields);
}
}
/**
* 更新操作
*/
private static void executeUpdate(RecordSet rs, String tableName, Map<String, Object> data, List<String> dbFields, String whereClause, List<Object> whereParams) {
StringBuilder setClause = new StringBuilder();
List<Object> setParams = new ArrayList<>();
for (String field : dbFields) {
// 跳过唯一字段，它们用于WHERE条件
if (whereClause.contains(field + " = ?")) {
continue;
}
if (setClause.length() > 0) {
setClause.append(", ");
}
setClause.append(field).append(" = ?");
setParams.add(data.get(field));
}
String sql = "UPDATE " + tableName + " SET " + setClause + " WHERE " + whereClause;
List<Object> allParams = new ArrayList<>();
allParams.addAll(setParams);
allParams.addAll(whereParams);
try {
baseBean.writeLog("执行SQL(更新)：" + sql + " 参数：" + allParams);
baseBean.writeLog("可直接执行SQL(更新)：" + buildFullSql(sql, allParams));
rs.executeUpdate(sql, allParams.toArray());
baseBean.writeLog("成功更新表 [" + tableName + "]，数据：" + data);
} catch (Exception e) {
baseBean.writeLog("更新失败！表 [" + tableName + "]，错误：" + e.getMessage());
throw new RuntimeException("数据库更新异常", e);
}
}
/**
* 插入操作
*/
private static void executeInsert(RecordSet rs, String tableName, Map<String, Object> data, List<String> dbFields) {
List<Object> params = new ArrayList<>();
for (String field : dbFields) {
params.add(data.get(field));
}
String fieldsClause = String.join(", ", dbFields);
String placeholders = String.join(", ", Collections.nCopies(dbFields.size(), "?"));
String sql = "INSERT INTO " + tableName + "(" + fieldsClause + ") VALUES(" + placeholders + ")";
try {
baseBean.writeLog("执行SQL(插入)：" + sql + " 参数：" + params);
baseBean.writeLog("可直接执行SQL(插入)：" + buildFullSql(sql, params));
rs.executeUpdate(sql, params.toArray());
baseBean.writeLog("成功插入表 [" + tableName + "]，数据：" + data);
} catch (Exception e) {
baseBean.writeLog("插入失败！表 [" + tableName + "]，错误：" + e.getMessage());
throw new RuntimeException("数据库插入异常", e);
}
}
// 构造可直接执行的SQL（将?替换为实际参数值，字符串加单引号）
private static String buildFullSql(String sql, List<Object> params) {
StringBuilder sb = new StringBuilder();
int paramIdx = 0;
for (int i = 0; i < sql.length(); i++) {
char c = sql.charAt(i);
if (c == '?' && paramIdx < params.size()) {
Object val = params.get(paramIdx++);
if (val == null) {
sb.append("NULL");
} else if (val instanceof Number) {
sb.append(val.toString());
} else {
sb.append("'").append(val.toString().replace("'", "''")).append("'");
}
} else {
sb.append(c);
}
}
return sb.toString();
}
/**
* 获取数据库表字段
*/
private static List<String> getTableFields(RecordSet metaRs, String tableName, Map<String, Object> data) {
List<String> fields = new ArrayList<>();
String metaSql = "SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = '" + tableName + "' AND OWNER = USER ORDER BY COLUMN_ID";
try {
metaRs.execute(metaSql);
while (metaRs.next()) {
String field = metaRs.getString("COLUMN_NAME");
if (data.containsKey(field)) {
fields.add(field);
}
}
if (fields.isEmpty()) {
baseBean.writeLog("警告：表 [" + tableName + "] 无数据中存在的有效字段，操作终止！");
}
} catch (Exception e) {
baseBean.writeLog("获取表字段失败！表 [" + tableName + "]，错误：" + e.getMessage());
throw new RuntimeException("元数据查询异常！", e);
}
return fields;
}
/**
* 组装附件字段（单/多附件）
*/
public static void putAttachmentField(Map<String, Object> dbData, String attachmentFieldName, List<Integer> docIds) {
if (!docIds.isEmpty() && attachmentFieldName != null && !attachmentFieldName.isEmpty()) {
if (docIds.size() == 1) {
dbData.put(attachmentFieldName, docIds.get(0));
} else {
String attachmentIdsStr = String.join(",", docIds.stream().map(String::valueOf).toArray(String[]::new));
dbData.put(attachmentFieldName, attachmentIdsStr);
}
}
}
}当然，使用方式也非常的优雅：
/**
* @Description 保存银行交易回单附件到数据库
*/
public static List<JSONObject> bankTransactionFileUpload() {
RecordSet rs = new RecordSet();
List<JSONObject> resultList = new ArrayList<>();
String today = DateUtil.today();
int docCategoryId = 100000;
String randomUUID = IdUtil.randomUUID();
int userId = 1;
String tableName = "UF_ZJJSYQ"; // 业务表名
String attachmentFieldName = "ANNEX"; // 业务表中存放docId的字段名
List<String> serialIds = queryTodaySerialIds(rs, today);
baseBean.writeLog(randomUUID + "-当前xx银行待请求回单数据：" + JSON.toJSONString(serialIds));
for (String serialId : serialIds) {
String downloadNo = getDownloadNoBySerialId(randomUUID, serialId);
if (StrUtil.isEmpty(downloadNo)) {
continue;
}
List<String> downloadUrls = getDownloadUrlsByDownloadNo(randomUUID, downloadNo);
baseBean.writeLog(randomUUID + "-当前xx银行回单下载编号列表：" + JSON.toJSONString(downloadUrls));
for (String downloadUrl : downloadUrls) {
// 组装 fileList
List<Map<String, String>> fileList = new ArrayList<>();
Map<String, String> fileMap = new HashMap<>();
String fileName = "NB" + serialId + ".pdf";
fileMap.put("fileUrl", downloadUrl);
fileMap.put("fileName", fileName);
fileList.add(fileMap);
// 组装 body
JSONObject body = new JSONObject();
// SERIALID 在上面定义为唯一字段，数据库不会更新它。
body.put("SERIALID", serialId);
// 请求入参和数据库字段关系映射
Map<String, String> updateFields = new HashMap<>();
updateFields.put("SERIALID", "SERIALID");
// 自定义字段（优先级高于入参）
Map<String, Object> customFields = new HashMap<>();
customFields.put("SERIALID", serialId);
// 唯一字段（数据库字段名，需大写）
List<String> uniqueFields = new ArrayList<>();
uniqueFields.add("SERIALID");
baseBean.writeLog(randomUUID + "-当前xx银行回单附件入库：" + JSON.toJSONString(fileList));
// 调用工具类
Map<String, Object> utilResult = FileUploadProcessorUtil.processDbDataAndUpsert(
body, updateFields, uniqueFields, tableName, attachmentFieldName,
fileList, docCategoryId, userId, customFields
);
baseBean.writeLog(randomUUID + "-当前xx银行回单附件入库结果：" + JSON.toJSONString(utilResult));
List<Integer> docIds = (List<Integer>) utilResult.get("docIds");
JSONObject result = new JSONObject();
result.put("serialId", serialId);
result.put("fileName", fileName);
result.put("downloadUrl", downloadUrl);
result.put("docIds", docIds);
resultList.add(result);
}
}
return resultList;
}