From 625c4f2816d171bbe8cf264e23d3bec0c11e3b86 Mon Sep 17 00:00:00 2001
From: zouyu <2723363702@qq.com>
Date: 星期五, 03 四月 2026 17:25:13 +0800
Subject: [PATCH] 采集器调整:mdb数据库处理，排除特殊字段类型
---
src/main/java/com/chinaztt/mes/docx/util/TakeWords.java | 112 +++++++++++++++++++++++++++++++++++++++++++++++++++----
1 files changed, 103 insertions(+), 9 deletions(-)
diff --git a/src/main/java/com/chinaztt/mes/docx/util/TakeWords.java b/src/main/java/com/chinaztt/mes/docx/util/TakeWords.java
index 861d5ad..4987848 100644
--- a/src/main/java/com/chinaztt/mes/docx/util/TakeWords.java
+++ b/src/main/java/com/chinaztt/mes/docx/util/TakeWords.java
@@ -7,6 +7,7 @@
import com.opencsv.CSVReader;
import com.opencsv.CSVReaderBuilder;
import com.opencsv.exceptions.CsvValidationException;
+import lombok.extern.slf4j.Slf4j;
import net.sourceforge.tess4j.Tesseract;
import net.sourceforge.tess4j.TesseractException;
import org.apache.commons.lang3.ObjectUtils;
@@ -17,7 +18,6 @@
import org.apache.poi.ooxml.POIXMLDocument;
import org.apache.poi.ooxml.extractor.POIXMLTextExtractor;
import org.apache.poi.openxml4j.opc.OPCPackage;
-import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
@@ -28,6 +28,7 @@
import java.util.*;
import java.util.regex.Pattern;
+@Slf4j
public class TakeWords {
private static final String splitIdentifier = "@-@"; // 鑷畾涔夊敮涓�鏍囪瘑绗�
@@ -36,6 +37,11 @@
private static final Pattern SCIENTIFIC_PATTERN = Pattern.compile(
"^[+-]?\\d+(\\.\\d+)?[eE][+-]?\\d+$"
);
+
+ /**
+ * 澶勭悊mdb鏁版嵁搴撴帓闄ゅ瓧娈电被鍨�
+ */
+ private final static List<String> MDB_EXCLUDE_TYPES = Arrays.asList("java.sql.Blob");
public static Object readWordFile(File file) {
String result = "";
@@ -236,7 +242,7 @@
if(StringUtils.isNotBlank(getFileDto.getMdbSampleCode())){
s+=" and " + getFileDto.getMdbSampleCode() + " = '" + getFileDto.getSampleCode() + "'";
}
- if(StringUtils.isNotBlank(getFileDto.getBushingColorField())){
+ if(ObjectUtils.allNotNull(getFileDto.getBushingColorField(), getFileDto.getBushingColor()) && !getFileDto.getBushingColorField().equals("null")){
s+=" and " + getFileDto.getBushingColorField() + " = '" + getFileDto.getBushingColor() + "'";
}
@@ -244,11 +250,17 @@
rs = preparedStatement.executeQuery();
ResultSetMetaData data = rs.getMetaData();
while (rs.next()) {
- Map<String, String> map = new HashMap<>();
+ Map<String, Object> map = new HashMap<>();
for (int i = 1; i <= data.getColumnCount(); i++) {
//鍒楀悕
String columnName = data.getColumnName(i);
- map.put(columnName, rs.getString(i));
+ //鍒楀瓧娈电被鍨�
+ String columnClassName = data.getColumnClassName(i);
+ Object columnValue = null;
+ if(!MDB_EXCLUDE_TYPES.contains(columnClassName)){
+ columnValue = rs.getObject(i);
+ }
+ map.put(columnName, columnValue);
}
list.add(map);
}
@@ -286,7 +298,7 @@
String table = getFileDto.getDbTable();
// 妫�鏌ユ暟鎹簱鍚嶅拰琛ㄥ悕鏄惁涓虹┖
if (dbName == null || dbName.isEmpty() || table == null || table.isEmpty()) {
- return R.failed("鏁版嵁搴撳悕鎴栬〃鍚嶄笉鑳戒负绌�");
+ return Result.failed("鏁版嵁搴撳悕鎴栬〃鍚嶄笉鑳戒负绌�");
}
// 鏁版嵁搴撹繛鎺ヤ俊鎭�
String url = "jdbc:mysql://localhost:3306/"+dbName+"?useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true&characterEncoding=utf8";
@@ -332,7 +344,7 @@
} catch (Exception e) {
e.printStackTrace();
// 鍋囪 R 绫绘湁 failed 鏂规硶锛岃嫢娌℃湁闇�琛ュ厖瀹炵幇
- return R.failed("鏁版嵁搴撴煡璇㈠嚭閿�: " + e.getMessage());
+ return Result.failed("鏁版嵁搴撴煡璇㈠嚭閿�: " + e.getMessage());
} finally {
try {
if (resultSet != null) {
@@ -371,9 +383,13 @@
List<Object> list = new ArrayList<>();
//杞箟涓枃
String sampleCode = changeCharset(getFileDto.getSampleCode(), "GBK");
- String sql = "select * from " + getFileDto.getDbFileName() +
- " where " + getFileDto.getMdbEntrustCode() + " = '" + getFileDto.getEntrustCode() +
- "' and " + getFileDto.getMdbSampleCode() + " = '" + sampleCode + "'";
+ String sql = "select * from " + getFileDto.getDbFileName() + " where 1=1";
+ if(StringUtils.isNotBlank(getFileDto.getMdbEntrustCode())){
+ sql+=" and " + getFileDto.getMdbEntrustCode() + " = '" + getFileDto.getEntrustCode() +"'";
+ }
+ if(StringUtils.isNotBlank(getFileDto.getMdbSampleCode())){
+ sql+=" and " + getFileDto.getMdbSampleCode() + " = '" + sampleCode + "'";
+ }
if (ObjectUtils.allNotNull(getFileDto.getFiberOpticRibbonCode(), getFileDto.getFiberOpticRibbon()) && !getFileDto.getFiberOpticRibbonCode().equals("null")) {
sql = sql + " and " + getFileDto.getFiberOpticRibbonCode() + " = '" + getFileDto.getFiberOpticRibbon() + "'";
}
@@ -456,4 +472,82 @@
}
}
+ /**
+ * 璇诲彇sqlserver鏁版嵁
+ * @param getFileDto 璇锋眰鍙傛暟瀵硅薄
+ * @return
+ */
+ public static Object readSqlServerDataBase(GetFileDto getFileDto) {
+ Map<String, Object> tableMap = new HashMap<>(16);
+ // 浠� GetFileDto 鑾峰彇鏁版嵁搴撳悕锛屽搴斻�愭枃浠跺悕绉般�戝瓧娈�
+ String dbName = getFileDto.getDbFileName();
+ String user = getFileDto.getDbUserName();
+ String password = getFileDto.getDbPassword();
+ // 浠� GetFileDto 鑾峰彇鏁版嵁琛ㄥ悕锛屽搴斻�愭暟鎹簱琛ㄥ悕銆戝瓧娈�
+ String table = getFileDto.getDbTable();
+ // 妫�鏌ユ暟鎹簱鍚嶅拰琛ㄥ悕鏄惁涓虹┖
+ if (dbName == null || dbName.isEmpty() || table == null || table.isEmpty()) {
+ return Result.failed("鏁版嵁搴撳悕鎴栬〃鍚嶄笉鑳戒负绌�");
+ }
+ // 鏁版嵁搴撹繛鎺ヤ俊鎭�
+ String url = "jdbc:sqlserver://localhost:1433;DatabaseName="+dbName+";encrypt=true;trustServerCertificate=true";
+ Connection connection = null;
+ PreparedStatement preparedStatement = null;
+ ResultSet resultSet = null;
+ List<Map<String, Object>> dataList = new ArrayList<>();
+ try {
+ // 寤虹珛杩炴帴
+ connection = DriverManager.getConnection(url, user, password);
+ // 鏋勫缓鍩虹 SQL
+
+ String sql = "SELECT * FROM "+table+" WHERE 1=1";
+ if(StringUtils.isNotBlank(getFileDto.getMdbEntrustCode()) ){
+ sql+=" AND (" + getFileDto.getMdbEntrustCode() + " = TRIM('" + getFileDto.getEntrustCode()+ "')";
+ if(StringUtils.isNotBlank(getFileDto.getLotBatchNo())){
+ sql+=" OR "+ getFileDto.getMdbEntrustCode() + " = TRIM('" + getFileDto.getLotBatchNo()+ "')";
+ }
+ sql+=")";
+ }
+ if(StringUtils.isNotBlank(getFileDto.getMdbSampleCode())){
+ sql+=" AND " + getFileDto.getMdbSampleCode() + " = '" + getFileDto.getSampleCode() + "'";
+ }
+ if(StringUtils.isNotBlank(getFileDto.getCableTag())){
+ sql+=" AND Color = '" + getFileDto.getCableTag() + "'";
+ }
+ // 鍒涘缓 PreparedStatement 瀵硅薄鎵ц SQL
+ preparedStatement = connection.prepareStatement(sql);
+ resultSet = preparedStatement.executeQuery();
+ ResultSetMetaData metaData = resultSet.getMetaData();
+ int columnCount = metaData.getColumnCount();
+ // 閬嶅巻缁撴灉闆嗚幏鍙栨暟鎹�
+ while (resultSet.next()) {
+ Map<String, Object> rowData = new HashMap<>();
+ for (int i = 1; i <= columnCount; i++) {
+ String columnName = metaData.getColumnName(i);
+ rowData.put(columnName, resultSet.getObject(i));
+ }
+ dataList.add(rowData);
+ }
+ tableMap.put("data", dataList);
+ } catch (Exception e) {
+ e.printStackTrace();
+ // 鍋囪 R 绫绘湁 failed 鏂规硶锛岃嫢娌℃湁闇�琛ュ厖瀹炵幇
+ return Result.failed("鏁版嵁搴撴煡璇㈠嚭閿�: " + e.getMessage());
+ } finally {
+ try {
+ if (resultSet != null) {
+ resultSet.close();
+ }
+ if (preparedStatement != null) {
+ preparedStatement.close();
+ }
+ if (connection != null) {
+ connection.close();
+ }
+ } catch (SQLException e) {
+ e.printStackTrace();
+ }
+ }
+ return tableMap;
+ }
}
--
Gitblit v1.9.3