From 0120a42fc52d9e55bc22a450e35a24b2d844b8ad Mon Sep 17 00:00:00 2001
From: 邹裕 <2723363702@qq.com>
Date: 星期二, 22 九月 2026 17:32:55 +0800
Subject: [PATCH] 修复ucanaccess查询中文排序mdb数据库导致索引失效查询缓慢问题
---
src/main/java/com/hwtd/mes/collect/util/JackcessTableReader.java | 286 +++++++++++++++++++++++++++++++++++
src/main/java/com/hwtd/mes/collect/service/impl/DataCollectionServiceImpl.java | 18 ++
pom.xml | 20 ++
README.md | 150 +++++++++++++++---
4 files changed, 448 insertions(+), 26 deletions(-)
diff --git a/README.md b/README.md
index c602ddf..8a98dda 100644
--- a/README.md
+++ b/README.md
@@ -1,47 +1,145 @@
## data-acquisition
-浜ㄦ椇鐗瑰MES鏁版嵁閲囬泦鍣�
+浜ㄦ椇鐗瑰 MES 鏁版嵁閲囬泦鍣紙Spring Boot 2.6.3 / Java 8锛�
-### 涓插彛閲囬泦锛堟墜鍔ㄧ洃鍚級
+- 閲囬泦鏂瑰紡锛氫覆鍙ｏ紙鎵嬪姩鐩戝惉锛夈�丄ccess 鏁版嵁搴撱�丳ostgreSQL
+- 鏈嶅姟绔彛锛�9527锛坄server.port`锛�
+- 鏃ュ織鐩綍锛歚logging.file-location`锛堥粯璁� `D:\mes-acquistion\logs`锛�
+- 缁熶竴杩斿洖鏍煎紡锛歚{ "code": 0, "msg": null, "data": ... }`锛宍code=0` 鎴愬姛锛宍code=1` 澶辫触锛堝師鍥犲湪 `msg` 閲岋級
-涓插彛涓嶅啀闅忛」鐩惎鍔ㄨ嚜鍔ㄧ洃鍚紝鏀逛负鎵嬪姩鐩戝惉锛氳皟鐢ㄩ噰闆嗘帴鍙ｆ椂寮�鍚洃鍚紝閲囬泦缁撴潫鍚庤皟鐢ㄥ叧闂帴鍙ｃ��
+### 涓�銆佷覆鍙ｉ噰闆嗭紙鎵嬪姩鐩戝惉锛�
+
+涓插彛涓嶉殢椤圭洰鍚姩鑷姩鐩戝惉锛氳皟鐢ㄥ紑鍚帴鍙ｆ墦寮�涓插彛骞跺彇鏁帮紝閲囬泦缁撴潫鍚庤皟鐢ㄥ叧闂帴鍙ｉ噴鏀句覆鍙ｃ��
| 鎺ュ彛 | 璇存槑 |
| --- | --- |
-| `GET /lims/getFile?fileExtension=.serialPort` | 閲囬泦鎺ュ彛锛氳皟鐢ㄦ椂鑷姩寮�鍚洃鍚紝骞惰繑鍥炴湰娆￠噰闆嗗埌鐨勬暟鎹紱娌℃湁鏁版嵁鏃惰繑鍥炵┖闆嗗悎锛岀敱鍓嶇杞鍙栨暟 |
-| `GET /lims/openSerialPort` | 鎵嬪姩寮�鍚洃鍚紝骞惰繑鍥炲綋鍓嶆帴鏀跺埌鐨勬暟鎹垪琛紙骞傜瓑锛屽弬鏁颁竴鑷存椂澶嶇敤宸叉墦寮�鐨勪覆鍙ｏ級 |
-| `GET /lims/closeSerialPort` | 鎵嬪姩鍏抽棴鐩戝惉锛堝箓绛夛紝閲囬泦缁撴潫鍚庤皟鐢紝閬垮厤涓插彛涓�鐩磋鍗犵敤锛� |
-| `GET /lims/serialPortStatus` | 鏌ヨ鐩戝惉鐘舵�侊細`listenName` / `listening` / `portOpen` / `dataSize` |
+| `GET /collection/openSerialPort` | 寮�鍚覆鍙ｇ洃鍚苟杩斿洖褰撳墠鎺ユ敹鍒扮殑鏁版嵁鍒楄〃锛堟病鏈夋暟鎹椂杩斿洖绌洪泦鍚堬紝鐢卞墠绔疆璇㈠彇鏁帮級銆傚箓绛夛細鍙傛暟涓�鑷存椂澶嶇敤宸叉墦寮�鐨勪覆鍙ｏ紝鍙傛暟鍙樺寲鏃舵寜鏂板弬鏁伴噸鏂版墦寮�锛屽绾跨▼骞跺彂璋冪敤涔熷彧浼氭墦寮�涓�娆� |
+| `GET /collection/closeSerialPort` | 鍏抽棴涓插彛鐩戝惉锛堝箓绛夛紝鏈紑鍚椂璋冪敤涔熻繑鍥炴垚鍔燂級 |
+| `GET /collection/serialPortStatus` | 鏌ヨ鐩戝惉鐘舵�侊紝杩斿洖 `listenName` / `listening` / `portOpen` / `dataSize` |
-涓插彛鍙傛暟鍏ㄩ儴鐢辨帴鍙ｄ紶鍏ワ紝`/lims/getFile`锛坄fileExtension=.serialPort` 鏃讹級鍜� `/lims/openSerialPort` 閮芥敮鎸侊紝
-涓嶄紶鐨勫瓧娈典娇鐢ㄩ粯璁ゅ�硷紙`listenName` 蹇呬紶锛夈�俛pplication.yml 涓殑 `serialPort` 閰嶇疆宸蹭笉鍐嶇敓鏁堬紝鍙互鍒犻櫎銆�
+#### 涓插彛鍙傛暟锛堝叏閮ㄧ敱鎺ュ彛浼犲叆锛屼笉浼犵殑瀛楁鐢ㄩ粯璁ゅ�硷級
| 鍙傛暟 | 榛樿鍊� | 璇存槑 |
| --- | --- | --- |
-| `listenName` | 蹇呬紶 | 涓插彛鍚嶇О锛屽 `COM12`锛涗笉浼犱細杩斿洖鈥滀覆鍙ｅ悕绉颁笉鑳戒负绌衡�� |
+| `serialPortName` | 蹇呬紶 | 涓插彛鍚嶇О锛屽 `COM12`锛涗笉浼犱細杩斿洖鈥滀覆鍙ｅ悕绉颁笉鑳戒负绌衡�� |
| `baudRate` | `9600` | 娉㈢壒鐜� |
| `dataBits` | `8` | 鏁版嵁浣嶏紝5~8 |
| `stopBits` | `1` | 鍋滄浣嶏紝1銆�2銆�3(1.5 浣�) |
-| `parity` | `NONE` | 鏍￠獙浣嶏細`NONE`/`ODD`/`EVEN`/`MARK`/`SPACE`锛屼篃鍏煎 0~4 |
-| `flowControl` | `NONE` | 娴佹帶锛歚NONE`/`RTS_CTS`/`XON_XOFF`锛屼篃鍏煎鏁板瓧甯搁噺 |
+| `parity` | `NONE` | 鏍￠獙浣嶏細`NONE`/`ODD`/`EVEN`/`MARK`/`SPACE`锛堜笉鍖哄垎澶у皬鍐欙級锛屼篃鍏煎 0~4 |
+| `flowControl` | `NONE` | 娴佹帶锛歚NONE`/`RTS_CTS`/`XON_XOFF`锛堜笉鍖哄垎澶у皬鍐欙級锛屼篃鍏煎鏁板瓧甯搁噺 |
| `readTimeout` | `1000` | 璇诲彇瓒呮椂锛堟绉掞級 |
-| `endMark` | `*` | 涓�甯ф暟鎹殑缁撴潫鏍囧織锛岀壒娈婂瓧绗︾敤 URL 缂栫爜锛屽鎹㈣ `%0A` |
-| `maxCount` | `6` | 鏁版嵁缂撳瓨闃熷垪闀垮害锛氶槦鍒楁弧鏃舵彃鍏ユ渶鏂颁竴鏉°�佺Щ闄ゆ渶鏃т竴鏉� |
+| `endMark` | `*` | 涓�甯ф暟鎹殑缁撴潫鏍囧織锛岀壒娈婂瓧绗︾敤 URL 缂栫爜浼犲叆锛屽鎹㈣ `%0A` |
+| `maxCount` | `1` | 鏁版嵁缂撳瓨闃熷垪闀垮害锛氶槦鍒楁弧鏃舵彃鍏ユ渶鏂颁竴鏉°�佺Щ闄ゆ渶鏃т竴鏉★紱榛樿 `1` 鍗冲彧杩斿洖璁惧鏈�鏂扮殑涓�鏉� |
| `frameIdleMillis` | `200` | 甯х┖闂茶ˉ甯ф椂闂达紙姣锛夛細瓒呰繃璇ユ椂闂存病鏈夋柊鏁版嵁锛屽氨鎶婄紦鍐插尯鍓╀綑鏁版嵁褰撴垚涓�甯у彇鍑猴紱`0` 琛ㄧず鍏抽棴 |
-绀轰緥锛歚/lims/getFile?fileExtension=.serialPort&listenName=COM12&baudRate=19200&parity=EVEN&endMark=%0A&maxCount=6`
+璋冪敤绀轰緥锛�
-鍙傛暟涓庡綋鍓嶇敓鏁堝弬鏁颁竴鑷存椂澶嶇敤宸茬粡鎵撳紑鐨勪覆鍙ｏ紙涓嶄細閲嶅鎵撳紑锛夛紱鍙傛暟鍙樺寲鏃朵細鑷姩鎸夋柊鍙傛暟閲嶆柊鎵撳紑涓插彛锛�
-鍙傛暟闈炴硶鏃舵帴鍙ｇ洿鎺ヨ繑鍥炲叿浣撳師鍥狅紙濡� `涓插彛鍙傛暟 dataBits 闈炴硶锛�9锛屽彇鍊艰寖鍥� 5~8`锛夈��
+```
+# 寮�鍚洃鍚苟鍙栨暟锛�9600 8N1銆佺粨鏉熸爣蹇� *锛岄粯璁ゅ彧鍙栨渶鏂颁竴鏉★級
+http://127.0.0.1:9527/collection/openSerialPort?serialPortName=COM12
-閲囬泦鍒扮殑鏁版嵁鎸夐槦鍒楃紦瀛橈細鎻掑叆鏈�鏂扮殑涓�鏉★紝瓒呰繃 `maxCount` 鏉★紙榛樿 6 鏉★級鏃剁Щ闄ゆ渶鏃х殑涓�鏉★紝
-鍥犳鎺ュ彛姣忔鏈�澶氳繑鍥� `maxCount` 鏉℃渶鏂扮殑鏁版嵁锛堟寜鎺ユ敹鍏堝悗椤哄簭锛夛紱鏁版嵁琚彇璧板悗闃熷垪娓呯┖锛岄噸鏂板紑濮嬬紦瀛樸��
+# 19200 鍋舵牎楠屻�佷互鎹㈣缁撳熬銆佹渶澶氬彇 6 鏉�
+http://127.0.0.1:9527/collection/openSerialPort?serialPortName=COM12&baudRate=19200&parity=EVEN&endMark=%0A&maxCount=6
-鏁版嵁甯ф寜 `endMark` 鍒囧垎銆傚鏋滆澶囨槸銆屾瘡甯т互鏌愪釜瀛楃寮�澶淬�嶈�屼笉鏄粨灏撅紙渚嬪鍙� `*0 1 0`銆乣*0 2 0`锛�
-`*` 鐢ㄤ簬鍒嗛殧鍓嶅悗涓ゅ抚锛夛紝閭ｄ箞鏈�鍚庝竴甯у悗闈㈡病鏈夋柊鐨� `*` 鏉ヨЕ鍙戝垏鍒嗭紝鍙潬缁撴潫鏍囧織浼氭紡鎺夋渶鍚庝竴鏉★紱
-姝ゆ椂鐢� `frameIdleMillis`锛堥粯璁� 200ms锛夊厹搴曪細涓插彛绌洪棽瓒呰繃璇ユ椂闂村悗锛屾妸鎺ユ敹缂撳啿鍖洪噷鍓╀笅鐨勬暟鎹綋浣滀竴甯у彇鍑恒��
-濡傛灉璁惧姣忓抚閮戒互缁撴潫鏍囧織缁撳熬锛岀紦鍐插尯姣忔鍒囧垎鍚庨兘鏄┖鐨勶紝绌洪棽琛ュ抚涓嶄細鍋氫换浣曚簨銆�
+# 鏌ョ湅鐩戝惉鐘舵�� / 鍏抽棴涓插彛
+http://127.0.0.1:9527/collection/serialPortStatus
+http://127.0.0.1:9527/collection/closeSerialPort
+```
-骞跺彂璇存槑锛氬紑鍚� / 鍏抽棴涓插彛鐢卞悓涓�鎶婇攣淇濇姢锛屽绾跨▼骞跺彂璋冪敤閲囬泦鎺ュ彛涔熷彧浼氭墦寮�涓�娆′覆鍙ｏ紝涓嶄細閲嶅娉ㄥ唽鐩戝惉鍣紱
-閲囬泦缁撴灉瀛樻斁鍦ㄥ悓姝ラ泦鍚堜腑锛屽绾跨▼鍚屾椂鍙栨暟鏃跺悓涓�鏉℃暟鎹彧浼氳涓�涓嚎绋嬪彇璧帮紝涓嶄細閲嶅涔熶笉浼氫涪澶便��
-璋冪敤鍏抽棴鎺ュ彛鍚庡啀娆¤皟鐢ㄩ噰闆嗘帴鍙ｄ細閲嶆柊寮�鍚洃鍚紝骞舵竻鎺変笂涓�杞畫鐣欑殑鍗婂寘鏁版嵁鍜屾湭鍙栬蛋鐨勬暟鎹紝淇濊瘉姣忔閲囬泦鐨勬暟鎹彧灞炰簬鏈疆銆�
+#### 鍙栨暟涓庡垏甯ц鍒�
+
+- 鏁版嵁鎸� `endMark` 鍒囨垚涓�甯т竴甯э紱
+- 鍒囧嚭鏉ョ殑鏁版嵁鏀捐繘闃熷垪缂撳瓨锛氭彃鍏ユ渶鏂扮殑涓�鏉★紝瓒呰繃 `maxCount` 鏉℃椂绉婚櫎鏈�鏃х殑涓�鏉★紝
+ 鍥犳鎺ュ彛姣忔鏈�澶氳繑鍥� `maxCount` 鏉℃渶鏂扮殑鏁版嵁锛堟寜鎺ユ敹鍏堝悗椤哄簭锛夛紱鏁版嵁琚彇璧板悗闃熷垪娓呯┖锛岄噸鏂板紑濮嬬紦瀛橈紱
+- 璁惧鏈�鍚庝竴甯у鏋滀笉甯︾粨鏉熸爣蹇楋紙渚嬪姣忓抚浠� `*` 寮�澶淬�侀潬涓嬩竴涓� `*` 鍒嗛殧鍓嶅悗涓ゅ抚锛夛紝鍙潬缁撴潫鏍囧織浼氭紡鎺夋渶鍚庝竴鏉★紝
+ 姝ゆ椂鐢� `frameIdleMillis` 鍏滃簳锛氫覆鍙ｇ┖闂茶秴杩囪鏃堕棿灏辨妸鎺ユ敹缂撳啿鍖洪噷鍓╀笅鐨勬暟鎹綋浣滀竴甯у彇鍑猴紱
+ 濡傛灉璁惧姣忓抚閮戒互缁撴潫鏍囧織缁撳熬锛岀紦鍐插尯姣忔鍒囧垎鍚庨兘鏄┖鐨勶紝绌洪棽琛ュ抚涓嶄細鍋氫换浣曚簨锛�
+- 鍙傛暟涓庡綋鍓嶇敓鏁堝弬鏁颁竴鑷存椂澶嶇敤宸茬粡鎵撳紑鐨勪覆鍙ｏ紙涓嶄細閲嶅鎵撳紑锛夛紱鍙傛暟鍙樺寲鏃舵寜鏂板弬鏁伴噸鏂版墦寮�锛�
+- 鍐嶆寮�鍚洃鍚椂浼氭竻鎺変笂涓�杞畫鐣欑殑鍗婂寘鏁版嵁鍜屾湭鍙栬蛋鐨勬暟鎹紝淇濊瘉姣忔閲囬泦鐨勬暟鎹彧灞炰簬鏈疆锛�
+- 鍙傛暟闈炴硶鏃舵帴鍙ｇ洿鎺ヨ繑鍥炲叿浣撳師鍥狅紙濡� `涓插彛鍙傛暟 dataBits 闈炴硶锛�9锛屽彇鍊艰寖鍥� 5~8`锛夈��
+
+#### 骞跺彂璇存槑
+
+- 寮�鍚� / 鍏抽棴涓插彛鐢卞悓涓�鎶婇攣淇濇姢锛岄噸澶嶈皟鐢ㄥ彧浼氱湡姝ｆ墦寮�涓�娆′覆鍙ｏ紝涓嶄細閲嶅娉ㄥ唽鐩戝惉鍣紱
+- 閲囬泦缁撴灉瀛樻斁鍦ㄥ悓姝ラ泦鍚堜腑锛屽绾跨▼鍚屾椂鍙栨暟鏃跺悓涓�鏉℃暟鎹彧浼氳涓�涓嚎绋嬪彇璧帮紝涓嶄細閲嶅涔熶笉浼氫涪澶憋紱
+- 鐩戝惉鐘舵�佷娇鐢� volatile 鏍囪锛屽叧闂覆鍙ｅ悗鍥炶皟绾跨▼涓嶅啀澶勭悊鏁版嵁銆�
+
+### 浜屻�佹暟鎹簱閲囬泦
+
+| 鎺ュ彛 | 璇存槑 |
+| --- | --- |
+| `GET /collection/getAccessData` | 閲囬泦 Access 鏁版嵁搴擄紙.mdb/.accdb锛夎〃鏁版嵁锛岃繑鍥� `List<Map<String,Object>>` |
+| `GET /collection/getPostgreSqlData` | 閲囬泦 PostgreSQL 琛ㄦ暟鎹紝杩斿洖 `List<Map<String,Object>>` |
+
+鍙傛暟锛坄DatabaseDTO`锛夛細
+
+| 鍙傛暟 | 璇存槑 |
+| --- | --- |
+| `filePath` | Access 鏁版嵁搴撴枃浠惰矾寰勶紙浠� `getAccessData`锛� |
+| `ipAddress` / `serverPort` / `databaseName` | PostgreSQL 杩炴帴淇℃伅锛堜粎 `getPostgreSqlData`锛� |
+| `userName` / `password` | 鏁版嵁搴撶敤鎴峰悕銆佸瘑鐮侊紱Access 鐨勬枃浠舵墦寮�瀵嗙爜鏀惧湪 `password`锛坄userName` 瀵� Access 鏂囦欢鍔犲瘑鏃犱綔鐢級 |
+| `tableName` | 鏁版嵁琛ㄥ悕 |
+| `pointColumns` | 瑕侀噰闆嗙殑瀛楁锛屽涓敤閫楀彿鍒嗛殧 |
+| `mainColumn` + `batchCode` | 鎸夋壒娆¤繃婊わ細`mainColumn = batchCode` |
+| `orderColumn` / `orderRule` | 鎺掑簭瀛楁涓庤鍒欙紙`ASC`/`DESC`锛屼笉浼犻粯璁� `ASC`锛� |
+
+#### Access 璇诲彇鏂瑰紡锛坄mes.access.read-mode`锛�
+
+- `jackcess`锛堥粯璁わ級锛歫ackcess 鍙鐩磋锛坄DatabaseBuilder.setReadOnly(true).setAutoSync(false)`锛夛紝鎵撳紑鏂囦欢鍚庨『搴忔壂鎻忓崟琛ㄣ�佸彧鍙栭渶瑕佺殑鍒楋紝涓嶆瀯寤� HSQLDB 闀滃儚锛屽彧鍙栨煇寮犺〃鍑犲垪鏃舵渶蹇紱
+- `ucanaccess`锛氳蛋 UCanAccess 鐨� SQL 璺緞锛岄渶瑕� Access SQL銆侀摼鎺ヨ〃 `remap` 绛夊満鏅椂浣跨敤銆�
+
+jackcess 妯″紡鐨勫樊寮傦細
+
+- 鎸� `mainColumn` 杩囨护鏄暣琛ㄦ壂鎻忥細鏈簱鏂囨湰绱㈠紩鐨勬帓搴忚鍒欐槸涓枃 2052锛宩ackcess 涓嶆敮鎸侊紙绱㈠紩鍙銆佺储寮曟父鏍囩洿鎺ユ姏寮傚父锛夛紝娌℃湁鍙敤鐨勭储寮曟煡璇㈣矾寰勶紱`orderColumn` 鎺掑簭鍦� Java 渚у畬鎴愶紱
+- `OLE` 瀛楁涓庡師鏉ヤ竴鏍疯繑鍥� `null`锛屼笖涓嶄細璇诲彇瀹冪殑闀垮�硷紱
+- 鏃堕棿瀛楁缁熶竴杩斿洖 `java.sql.Timestamp`锛圝SON 閲屼粛鏄� `yyyy-MM-dd HH:mm:ss.SSS`锛夛紱
+- 璁剧疆浜嗘墦寮�瀵嗙爜鐨勫簱浼氳嚜鍔ㄧ敤 `jackcess-encrypt` 鎵撳紑锛堝瘑鐮佸彇 `password` 鍙傛暟锛汚ccess 鐨勬枃浠跺姞瀵嗗彧鏈夆�滄墦寮�瀵嗙爜鈥濅竴涓蹇碉紝`userName` 涓嶅弬涓庯紝.mdw 宸ヤ綔缁勭骇瀹夊叏鎬� jackcess 涓嶆敮鎸侊級锛沗ucanaccess` 妯″紡涓嬫湁 `jackcess-encrypt` 鍦� classpath 涓婏紝`password` 鍙傛暟鍚屾牱瀵瑰姞瀵嗗簱鐢熸晥锛�
+- 閾炬帴琛ㄧ殑琛屼负涓� UCanAccess 鐨� `remap` 涓嶅悓锛岄亣鍒伴棶棰樺垏鍥� `ucanaccess` 妯″紡鍗冲彲銆�
+
+### 涓夈�佹墭鐩樺浘鏍�
+
+绋嬪簭鍚姩鍚庝换鍔℃爮鍙充笅瑙掍細鍑虹幇鎵樼洏鍥炬爣锛坄SystemTrayInitializer`锛夛紝鍙抽敭鑿滃崟锛�
+
+| 鑿滃崟椤� | 璇存槑 |
+| --- | --- |
+| 鏌ョ湅涓插彛鐘舵�� | 寮瑰嚭鐘舵�佺獥鍙ｏ細涓插彛鍚嶇О / 鐩戝惉鐘舵�� / 涓插彛杩炴帴 / 缂撳瓨鏉℃暟锛屾瘡绉掕嚜鍔ㄥ埛鏂� |
+| 鍏抽棴涓插彛 | 鍙叧闂覆鍙ｇ洃鍚紙涓嶉��鍑虹▼搴忥級锛屾柟渚跨幇鍦烘妸涓插彛閲婃斁缁欏埆鐨勭▼搴忕敤锛涚粨鏋滅敤鎵樼洏姘旀场鎻愮ず |
+| 鎵撳紑鏃ュ織鐩綍 | 鎵撳紑 `logging.file-location` 閰嶇疆鐨勬棩蹇楃洰褰� |
+| 閫�鍑� | 鍏堝叧闂� Spring 瀹瑰櫒锛堣Е鍙� `@PreDestroy`锛氬叧闂覆鍙ｃ�佸仠姝㈣ˉ甯х嚎绋嬶級锛屽啀缁撴潫杩涚▼ |
+
+- 鍙屽嚮鎵樼洏鍥炬爣 = 鏌ョ湅涓插彛鐘舵�侊紱
+- 鑿滃崟寮瑰嚭鍦ㄥ厜鏍囦笂鏂广�佸彸杈圭紭瀵归綈锛岀偣鑿滃崟澶栭潰 / 鎸� ESC / 鍐嶅彸閿竴娆￠兘浼氭敹璧凤紱
+- 鍥炬爣浼樺厛鐢� `src/main/resources/tray.png`锛堟斁涓�寮� 32x32 鐨� png 鍗冲彲鎹㈡垚鑷繁鐨勫浘鏍囷級锛屾病鏈夊氨鐢ㄤ唬鐮佺敾鐨勯粯璁ゅ浘鏍囷紱
+- 鎵樼洏渚濊禆 AWT锛屽惎鍔ㄧ被閲屽凡缁� `setHeadless(false)`锛涗互 Windows 鏈嶅姟鏂瑰紡杩愯锛堟病鏈夋闈細璇濓級鏃舵墭鐩樹笉鏄剧ず锛屾帴鍙ｄ笉鍙楀奖鍝嶃��
+
+### 鍥涖�佹瀯寤轰笌杩愯
+
+```bash
+# 鎵撳寘锛堜骇鐗� target/data-acquisition.jar锛屽彲鐩存帴 java -jar 杩愯锛�
+mvn clean package -DskipTests
+
+# 杩愯锛堥渶瑕� Java 8锛�
+java -jar target/data-acquisition.jar
+```
+
+- **鍙屽嚮 jar 杩愯**锛氶渶瑕佹妸 `.jar` 鐨勯粯璁ゆ墦寮�鏂瑰紡鎸囧悜 **Java 8 鐨� javaw.exe**
+ 锛堝彸閿� jar 鈫� 鎵撳紑鏂瑰紡 鈫� 閫夋嫨鍏朵粬搴旂敤 鈫� 鏇村搴旂敤 鈫� 鍦ㄨ繖鍙扮數鑴戜笂鏌ユ壘鍏朵粬搴旂敤 鈫� `<jdk8>\bin\javaw.exe` 鈫� 鍕鹃�夆�滃缁堜娇鐢ㄦ搴旂敤鎵撳紑 .jar 鏂囦欢鈥濓級銆�
+ 娉ㄦ剰涓嶈鎸囧悜楂樼増鏈� JDK锛堟湰椤圭洰鏄� Spring Boot 2.6.3锛屽彧鏀寔 Java 8~17锛夛紝涔熶笉瑕佺敤鍘嬬缉杞欢鎵撳紑锛�
+- 鐢� javaw 鍚姩娌℃湁鎺у埗鍙帮紝**鍚姩澶辫触浼氬脊绐楁彁绀�**锛堜緥濡傜鍙ｈ鍗犵敤銆佹棩蹇楃洰褰曚笉鍙啓锛夛紝璇︾粏閿欒鐪� `logging.file-location` 鐩綍涓嬬殑鏃ュ織锛�
+- 閫�鍑鸿鐢ㄦ墭鐩樿彍鍗曠殑銆岄��鍑恒�嶏紙浼氭甯稿叧闂覆鍙ｏ級锛涚洿鎺ョ粨鏉熻繘绋嬩笉浼氳蛋杩欏娓呯悊娴佺▼銆�
+
+### 浜斻�侀厤缃紙application.yml锛�
+
+```yaml
+server:
+ port: 9527 # 鏈嶅姟绔彛
+
+logging:
+ file-location: D:\mes-acquistion\logs # 鏃ュ織鐩綍锛堟墭鐩樷�滄墦寮�鏃ュ織鐩綍鈥濅篃鐢ㄥ畠锛�
+
+#mes:
+# access:
+# read-mode: jackcess # Access 璇诲彇鏂瑰紡锛歫ackcess锛堥粯璁わ紝鐩磋鍗曡〃锛�/ ucanaccess锛圫QL 妯″紡锛�
+```
+
+> 涓插彛鍙傛暟锛堜覆鍙ｅ悕銆佹尝鐗圭巼銆佹暟鎹綅銆佹牎楠屼綅銆佺粨鏉熸爣蹇楃瓑锛夊叏閮ㄧ敱鎺ュ彛浼犲叆锛岄厤缃枃浠堕噷涓嶉渶瑕佷覆鍙ｇ浉鍏抽厤缃��
diff --git a/pom.xml b/pom.xml
index 7ff87b0..9f9af28 100644
--- a/pom.xml
+++ b/pom.xml
@@ -103,6 +103,26 @@
<groupId>net.sf.ucanaccess</groupId>
<artifactId>ucanaccess</artifactId>
<version>5.0.1</version>
+ <exclusions>
+ <exclusion>
+ <groupId>com.healthmarketscience.jackcess</groupId>
+ <artifactId>jackcess</artifactId>
+ </exclusion>
+ </exclusions>
+ </dependency>
+ <!-- Source: https://mvnrepository.com/artifact/com.healthmarketscience.jackcess/jackcess -->
+ <dependency>
+ <groupId>com.healthmarketscience.jackcess</groupId>
+ <artifactId>jackcess</artifactId>
+ <version>4.0.10</version>
+ <scope>compile</scope>
+ </dependency>
+ <!-- 鎵撳紑璁剧疆浜嗗瘑鐮佺殑 Access 鏂囦欢锛�.mdb/.accdb锛夐渶瑕佸畠 -->
+ <dependency>
+ <groupId>com.healthmarketscience.jackcess</groupId>
+ <artifactId>jackcess-encrypt</artifactId>
+ <version>4.0.3</version>
+ <scope>compile</scope>
</dependency>
<dependency>
diff --git a/src/main/java/com/hwtd/mes/collect/service/impl/DataCollectionServiceImpl.java b/src/main/java/com/hwtd/mes/collect/service/impl/DataCollectionServiceImpl.java
index 678eb5e..65410ff 100644
--- a/src/main/java/com/hwtd/mes/collect/service/impl/DataCollectionServiceImpl.java
+++ b/src/main/java/com/hwtd/mes/collect/service/impl/DataCollectionServiceImpl.java
@@ -2,10 +2,12 @@
import com.hwtd.mes.collect.dto.DatabaseDTO;
import com.hwtd.mes.collect.service.DataCollectionService;
+import com.hwtd.mes.collect.util.JackcessTableReader;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
+import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import java.sql.*;
@@ -20,8 +22,24 @@
*/
private final static List<String> MDB_EXCLUDE_TYPES = Arrays.asList("java.sql.Blob");
+ /**
+ * Access 璇诲彇鏂瑰紡锛�
+ * jackcess锛堥粯璁わ級鈥斺�� 鐢� jackcess 鍙鐩磋鍗曡〃銆佹寜闇�鍙栧垪锛屼笉鏋勫缓 HSQLDB 闀滃儚锛屽彧瑕佽〃閲屽嚑鍒楁椂鏈�蹇紱
+ * ucanaccess 鈥斺�� 璧板師鏉ョ殑 SQL 璺緞锛堥渶瑕� Access SQL銆侀摼鎺ヨ〃 remap 绛夊満鏅椂浣跨敤锛夈��
+ */
+ @Value("${mes.access.read-mode:jackcess}")
+ private String accessReadMode = "jackcess";
+
@Override
public List<Map<String, Object>> getAccessData(DatabaseDTO databaseDTO) {
+ if (databaseDTO == null || StringUtils.isBlank(databaseDTO.getFilePath())) {
+ throw new RuntimeException("Access鏁版嵁搴撴枃浠惰矾寰勪笉鑳戒负绌�");
+ }
+ // 榛樿 jackcess 鐩磋锛氬彧鍙栨煇寮犺〃鐨勫嚑鍒椾笉闇�瑕� SQL 寮曟搸锛屼篃涓嶇敤绛夋暣涓簱闀滃儚杩� HSQLDB
+ if (!"ucanaccess".equalsIgnoreCase(StringUtils.trimToEmpty(accessReadMode))) {
+ return JackcessTableReader.read(databaseDTO);
+ }
+
List<Map<String,Object>> list = new ArrayList<>();
try{
Properties prop = new Properties();
diff --git a/src/main/java/com/hwtd/mes/collect/util/JackcessTableReader.java b/src/main/java/com/hwtd/mes/collect/util/JackcessTableReader.java
new file mode 100644
index 0000000..d45a1eb
--- /dev/null
+++ b/src/main/java/com/hwtd/mes/collect/util/JackcessTableReader.java
@@ -0,0 +1,286 @@
+package com.hwtd.mes.collect.util;
+
+import com.healthmarketscience.jackcess.Column;
+import com.healthmarketscience.jackcess.Cursor;
+import com.healthmarketscience.jackcess.CursorBuilder;
+import com.healthmarketscience.jackcess.DataType;
+import com.healthmarketscience.jackcess.Database;
+import com.healthmarketscience.jackcess.DatabaseBuilder;
+import com.healthmarketscience.jackcess.Row;
+import com.healthmarketscience.jackcess.Table;
+import com.healthmarketscience.jackcess.crypt.CryptCodecProvider;
+import com.healthmarketscience.jackcess.crypt.InvalidCredentialsException;
+import com.healthmarketscience.jackcess.crypt.InvalidCryptoConfigurationException;
+import com.hwtd.mes.collect.dto.DatabaseDTO;
+import lombok.extern.slf4j.Slf4j;
+import org.apache.commons.lang3.StringUtils;
+
+import java.io.File;
+import java.io.IOException;
+import java.sql.Timestamp;
+import java.time.LocalDateTime;
+import java.util.ArrayList;
+import java.util.Comparator;
+import java.util.Date;
+import java.util.HashMap;
+import java.util.LinkedHashSet;
+import java.util.List;
+import java.util.Map;
+import java.util.Set;
+
+/**
+ * 鐢� jackcess 鐩磋 .mdb/.accdb 琛ㄦ暟鎹紙鍙锛夛紝鐢ㄤ簬鈥滃彧鍙栨煇寮犺〃鐨勫嚑鍒椻�濊繖绉嶇畝鍗曞満鏅��
+ * <p>
+ * 涓� UCanAccess 鐨勫尯鍒細UCanAccess 鎵撳紑杩炴帴鏃惰鎶婃暣涓簱闀滃儚杩� HSQLDB锛堣〃澶氭椂鏋佹參锛夛紝
+ * 杩欓噷鍙墦寮�鏂囦欢銆侀『搴忔壂鎻忎竴寮犺〃銆佹寜闇�鍙栧垪锛屽畬鍏ㄤ笉缁忚繃 SQL 寮曟搸銆�
+ * <p>
+ * 浠ｄ环涓庨檺鍒讹細
+ * <ul>
+ * <li>娌℃湁 SQL锛氭寜瀛楁杩囨护銆佹寜瀛楁鎺掑簭閮藉湪 Java 渚у畬鎴愶紝杩囨护蹇呯劧鏄暣琛ㄦ壂鎻忊�斺�旀湰搴撴枃鏈储寮曠殑鎺掑簭瑙勫垯鏄�
+ * 涓枃 2052锛宩ackcess 涓嶆敮鎸侊紙绱㈠紩鍙锛夛紝绱㈠紩娓告爣浼氱洿鎺ユ姏寮傚父锛屾墍浠ユ病鏈夊彲鐢ㄧ殑绱㈠紩鏌ヨ璺緞锛�</li>
+ * <li>缁撴灉鍏ㄩ儴瑁呭湪鍐呭瓨閲岋紝鏈姞杩囨护鏉′欢鐨勮秴澶ц〃瑕佺暀鎰忓唴瀛樺崰鐢紱</li>
+ * <li>璁剧疆浜嗘墦寮�瀵嗙爜鐨勫簱浼氳嚜鍔ㄧ敤 jackcess-encrypt 鎵撳紑锛屽瘑鐮佸彇璇锋眰鍙傛暟 {@code password}
+ * 锛圓ccess 鐨勬枃浠跺姞瀵嗗彧鏈夆�滄墦寮�瀵嗙爜鈥濓紝{@code userName} 涓嶅弬涓庯紱.mdw 宸ヤ綔缁勭骇瀹夊叏鎬� jackcess 涓嶆敮鎸侊級锛�</li>
+ * <li>Access 涓寚鍚戝閮ㄥ簱鐨勨�滈摼鎺ヨ〃鈥濊涓轰笌 UCanAccess 鐨� remap 鍙傛暟涓嶅悓锛岄亣鍒伴棶棰樺彲鍒囧洖
+ * {@code mes.access.read-mode=ucanaccess}銆�</li>
+ * </ul>
+ */
+@Slf4j
+public final class JackcessTableReader {
+
+ private JackcessTableReader() {
+ }
+
+ public static List<Map<String, Object>> read(DatabaseDTO databaseDTO) {
+ File file = new File(StringUtils.trimToEmpty(databaseDTO.getFilePath()));
+ if (!file.isFile()) {
+ throw new RuntimeException("Access鏁版嵁搴撴枃浠朵笉瀛樺湪锛�" + databaseDTO.getFilePath());
+ }
+ if (StringUtils.isBlank(databaseDTO.getTableName())) {
+ throw new RuntimeException("Access鏁版嵁搴撹〃鍚嶄笉鑳戒负绌�");
+ }
+
+ // setReadOnly(true)锛氬彧璇绘墦寮�鏂囦欢锛屼笉浜х敓閿佹枃浠讹紝涓嶄細骞叉壈 MES 绛夊叾浠栫▼搴忓啓鍏�
+ // setAutoSync(false)锛氬埛鐩樼瓥鐣ュ彧鍦ㄥ啓搴撴椂鐢熸晥锛屽彧璇诲満鏅繚鎸佸叧闂�
+ DatabaseBuilder builder = new DatabaseBuilder(file)
+ .setReadOnly(true)
+ .setAutoSync(false);
+ // 鏈夊瘑鐮佸氨鐢� jackcess-encrypt 鎵撳紑锛欰ccess 鐨勬枃浠跺姞瀵嗗彧鏈夆�滄墦寮�瀵嗙爜鈥濓紝鐢ㄦ埛鍚嶄笉鍙備笌
+ String password = StringUtils.trimToNull(databaseDTO.getPassword());
+ if (password != null) {
+ builder.setCodecProvider(new CryptCodecProvider(password));
+ }
+
+ try (Database database = builder.open()) {
+
+ Table table = findTable(database, databaseDTO.getTableName());
+ List<Column> columns = resolveColumns(table, databaseDTO.getPointColumns());
+
+ // 杩囨护瀛楁锛氱洏鍙�
+ Column filterColumn = null;
+ String batchCode = null;
+ if (StringUtils.isNotBlank(databaseDTO.getMainColumn()) && StringUtils.isNotBlank(databaseDTO.getBatchCode())) {
+ filterColumn = requireColumn(table, databaseDTO.getMainColumn(), "瀛楁");
+ batchCode = databaseDTO.getBatchCode().trim();
+ }
+ // 鎺掑簭瀛楁
+ Column orderColumn = null;
+ if (StringUtils.isNotBlank(databaseDTO.getOrderColumn())) {
+ orderColumn = requireColumn(table, databaseDTO.getOrderColumn(), "鎺掑簭瀛楁");
+ }
+
+ // 鎶曞奖锛氬彧鎶婇渶瑕佺殑鍒楄鍑烘潵锛堟湭璇锋眰鐨勫垪銆佸挨鍏舵槸 OLE/MEMO 杩欑被闀垮�煎垪涓嶄細琚鍙栵級
+ Set<String> projection = new LinkedHashSet<>();
+ for (Column column : columns) {
+ if (!isOle(column)) {
+ projection.add(column.getName());
+ }
+ }
+ if (filterColumn != null) {
+ projection.add(filterColumn.getName());
+ }
+ if (orderColumn != null) {
+ projection.add(orderColumn.getName());
+ }
+
+ long start = System.currentTimeMillis();
+ int scanned = 0;
+ List<RowData> rows = new ArrayList<>();
+ // CursorBuilder.createCursor(Table) 鏄�滄棤绱㈠紩鈥濈殑椤哄簭鎵弿娓告爣锛屼笉渚濊禆浠讳綍绱㈠紩
+ Cursor cursor = CursorBuilder.createCursor(table);
+ while (cursor.moveToNextRow()) {
+ scanned++;
+ Row row = cursor.getCurrentRow(projection);
+ if (filterColumn != null && !valueMatches(row.get(filterColumn.getName()), batchCode)) {
+ continue;
+ }
+ Map<String, Object> data = new HashMap<>();
+ for (Column column : columns) {
+ // OLE 瀛楁涓庡師鏉ヤ竴鏍疯繑鍥� null锛屼笖涓嶈闀垮��
+ data.put(column.getName(), isOle(column) ? null : normalize(row.get(column.getName())));
+ }
+ rows.add(new RowData(data, (orderColumn == null) ? null : row.get(orderColumn.getName())));
+ }
+
+ // 鎺掑簭锛圓ccess 鍗囧簭鏃� null 鍦ㄥ墠锛岄檷搴忔椂鎸夌浉鍙嶉『搴忥級
+ if (orderColumn != null) {
+ boolean desc = "DESC".equalsIgnoreCase(databaseDTO.getOrderRule());
+ Comparator<RowData> comparator = (o1, o2) -> compareValues(o1.sortKey, o2.sortKey);
+ rows.sort(desc ? comparator.reversed() : comparator);
+ }
+
+ List<Map<String, Object>> list = new ArrayList<>(rows.size());
+ for (RowData row : rows) {
+ list.add(row.data);
+ }
+ log.info("jackcess 鐩磋瀹屾垚锛氳〃 {}锛屾壂鎻� {} 琛屻�佽繑鍥� {} 琛岋紝鑰楁椂 {} ms",
+ table.getName(), scanned, list.size(), System.currentTimeMillis() - start);
+ return list;
+ } catch (InvalidCredentialsException e) {
+ // jackcess-encrypt 鏍￠獙鎵撳紑瀵嗙爜澶辫触鏃舵姏杩欎釜锛圛llegalStateException 瀛愮被锛�
+ throw new RuntimeException("Access 鏂囦欢瀵嗙爜涓嶆纭細" + file.getName(), e);
+ } catch (InvalidCryptoConfigurationException e) {
+ throw new RuntimeException("Access 鏂囦欢鐨勫姞瀵嗘柟寮忎笉鍙楁敮鎸侊細" + e.getMessage(), e);
+ } catch (IOException e) {
+ // .mdb锛圝et 鍔犲瘑锛夋病鏈夊瘑鐮佹牎楠屼綅锛屽瘑鐮佷笉瀵归�氬父琛ㄧ幇涓洪〉闈�/璁板綍璇诲彇寮傚父锛岃繖閲岃ˉ涓�鍙ユ彁绀�
+ String hint = (password == null) ? "" : "锛堣鏂囦欢璁剧疆浜嗘墦寮�瀵嗙爜锛岃纭瀵嗙爜鏄惁姝ｇ‘锛�";
+ throw new RuntimeException("Access鏁版嵁搴撹鍙栧紓甯革細" + e.getMessage() + hint, e);
+ }
+ }
+
+ /**
+ * 琛ㄥ悕鍖归厤锛氬厛鎸夊悕瀛楀彇锛屽彇涓嶅埌鍐嶅仛涓�娆″拷鐣ュぇ灏忓啓鐨勫尮閰嶏紙Access 鐨勮〃鍚嶄笉鍖哄垎澶у皬鍐欙級
+ */
+ private static Table findTable(Database database, String tableName) throws IOException {
+ String name = cleanName(tableName);
+ Table table = database.getTable(name);
+ if (table == null) {
+ for (String candidate : database.getTableNames()) {
+ if (candidate.equalsIgnoreCase(name)) {
+ table = database.getTable(candidate);
+ break;
+ }
+ }
+ }
+ if (table == null) {
+ throw new RuntimeException("Access鏁版嵁搴撲腑娌℃湁鎵惧埌琛細" + tableName);
+ }
+ return table;
+ }
+
+ /**
+ * 瑙ｆ瀽閲囬泦鐐逛綅瀛楁锛屾敮鎸侀�楀彿鍒嗛殧銆�*銆佷互鍙� [瀛楁鍚峕 / `瀛楁鍚峘 杩欑甯﹁浆涔夌殑鍐欐硶
+ */
+ private static List<Column> resolveColumns(Table table, String pointColumns) {
+ List<Column> columns = new ArrayList<>();
+ if ("*".equals(StringUtils.trimToEmpty(pointColumns))) {
+ columns.addAll(table.getColumns());
+ return columns;
+ }
+ if (StringUtils.isBlank(pointColumns)) {
+ throw new RuntimeException("閲囬泦鐐逛綅瀛楁涓嶈兘涓虹┖");
+ }
+ for (String item : pointColumns.split(",")) {
+ String name = cleanName(item);
+ if (name.isEmpty()) {
+ continue;
+ }
+ columns.add(requireColumn(table, name, "瀛楁"));
+ }
+ if (columns.isEmpty()) {
+ throw new RuntimeException("閲囬泦鐐逛綅瀛楁涓嶈兘涓虹┖");
+ }
+ return columns;
+ }
+
+ private static Column requireColumn(Table table, String columnName, String what) {
+ String name = cleanName(columnName);
+ for (Column column : table.getColumns()) {
+ if (column.getName().equalsIgnoreCase(name)) {
+ return column;
+ }
+ }
+ throw new RuntimeException("琛� " + table.getName() + " 涓病鏈夋壘鍒�" + what + "锛�" + columnName);
+ }
+
+ private static String cleanName(String name) {
+ String result = StringUtils.trimToEmpty(name);
+ if (result.length() > 1
+ && ((result.charAt(0) == '[' && result.endsWith("]"))
+ || (result.charAt(0) == '`' && result.endsWith("`")))) {
+ result = result.substring(1, result.length() - 1).trim();
+ }
+ return result;
+ }
+
+ private static boolean isOle(Column column) {
+ return column.getType() == DataType.OLE;
+ }
+
+ /**
+ * 鍊兼瘮杈冿細鏂囨湰鎸夆�滃幓绌烘牸 + 蹇界暐澶у皬鍐欌�濓紝涓庡師鏉� UCanAccess 杩炴帴锛圱RIM(瀛楁) = '鍊�'銆乮gnoreCase 榛樿 true锛変繚鎸佷竴鑷�
+ */
+ private static boolean valueMatches(Object columnValue, String expected) {
+ if (columnValue == null || expected == null) {
+ return false;
+ }
+ if (columnValue instanceof CharSequence) {
+ return columnValue.toString().trim().equalsIgnoreCase(expected);
+ }
+ return String.valueOf(columnValue).trim().equals(expected);
+ }
+
+ /**
+ * 绫诲瀷褰掍竴锛歫ackcess 榛樿杩斿洖 java.util.Date锛岃繖閲岀粺涓�杞垚 java.sql.Timestamp锛�
+ * 淇濇寔涓庡師鏉� JDBC 璺緞杩斿洖鐨勬椂闂寸被鍨嬩竴鑷达紙JSON 閲屽悓鏍锋槸 "yyyy-MM-dd HH:mm:ss.SSS"锛�
+ */
+ private static Object normalize(Object value) {
+ if (value instanceof Date) {
+ return new Timestamp(((Date) value).getTime());
+ }
+ if (value instanceof LocalDateTime) {
+ return Timestamp.valueOf((LocalDateTime) value);
+ }
+ return value;
+ }
+
+ @SuppressWarnings({"unchecked", "rawtypes"})
+ private static int compareValues(Object value1, Object value2) {
+ if (value1 == null && value2 == null) {
+ return 0;
+ }
+ if (value1 == null) {
+ return -1;
+ }
+ if (value2 == null) {
+ return 1;
+ }
+ if (value1 instanceof Number && value2 instanceof Number) {
+ if (value1 instanceof Double || value2 instanceof Double
+ || value1 instanceof Float || value2 instanceof Float) {
+ return Double.compare(((Number) value1).doubleValue(), ((Number) value2).doubleValue());
+ }
+ return Long.compare(((Number) value1).longValue(), ((Number) value2).longValue());
+ }
+ if (value1 instanceof Date && value2 instanceof Date) {
+ return Long.compare(((Date) value1).getTime(), ((Date) value2).getTime());
+ }
+ if (value1.getClass() == value2.getClass() && value1 instanceof Comparable) {
+ return ((Comparable) value1).compareTo(value2);
+ }
+ return String.valueOf(value1).compareTo(String.valueOf(value2));
+ }
+
+ /**
+ * 涓�琛屾暟鎹� + 鎺掑簭閿紙鎺掑簭瀛楁鍙兘涓嶅湪杩斿洖缁撴灉閲岋紝鎵�浠ュ崟鐙瓨涓�浠斤級
+ */
+ private static final class RowData {
+ private final Map<String, Object> data;
+ private final Object sortKey;
+
+ private RowData(Map<String, Object> data, Object sortKey) {
+ this.data = data;
+ this.sortKey = sortKey;
+ }
+ }
+}
--
Gitblit v1.9.3