From 0b0ca05018a5f47a1c8b1573e0eff4310ebca1a9 Mon Sep 17 00:00:00 2001
From: Hunter0x7c7 <1125607007@qq.com>
Date: 星期六, 12 八月 2023 10:52:33 +0800
Subject: [PATCH] SQL语句
---
src/main/java/com/github/hunter0x7c7/sync/ctrls/Controller.java | 42 ++++++++++++++++++------------------------
1 files changed, 18 insertions(+), 24 deletions(-)
diff --git a/src/main/java/com/github/hunter0x7c7/sync/ctrls/Controller.java b/src/main/java/com/github/hunter0x7c7/sync/ctrls/Controller.java
index ab3c426..d99e4b2 100644
--- a/src/main/java/com/github/hunter0x7c7/sync/ctrls/Controller.java
+++ b/src/main/java/com/github/hunter0x7c7/sync/ctrls/Controller.java
@@ -35,13 +35,6 @@
import java.awt.MenuItem;
import java.awt.*;
-import java.io.File;
-import java.io.UnsupportedEncodingException;
-import java.net.URI;
-import java.net.URISyntaxException;
-import java.net.URL;
-import java.net.URLDecoder;
-import java.nio.file.Files;
import java.sql.*;
import java.util.Date;
import java.util.List;
@@ -53,6 +46,15 @@
import static com.github.hunter0x7c7.sync.model.global.Parameters.*;
public class Controller {
+ public static final String SQL1_QUERY = "SELECT * FROM IOT_Equipment_Info WHERE IE_Type = 'XPH鐗╄仈缃戝叧'; ";
+ public static final String SQL2_QUERY = "SELECT SD_Addr + '|' + SD_Code IE_Param, * " +
+ "FROM TY_SensorData WHERE SD_Key IN ( '%s' );";
+ public static final String SQL3_UPDATE = "UPDATE IOT_Equipment_Info " +
+ "SET IE_Realtime_Data = ?, IE_Realtime_Time = ?, Edit_User = ?, Edit_Time = ? " +
+ "WHERE IE_Param = ? AND IE_Parent = ( " +
+ "SELECT IE_ID FROM IOT_Equipment_Info WHERE IE_Param = ? " +
+ ") ;";
+
@FXML
private TextField tvInputSrcHost;
@FXML
@@ -176,7 +178,7 @@
//娣诲姞鍒扮郴缁熸墭鐩�
Stage primaryStage = Session.getInstance().getPrimaryStage();
String tooltip = String.format("%s v%s", AppName, VersionName);//SyncTools v1.0
- String mipmap = "mipmap/img_chinese_cabbage_16.png";
+ String mipmap = "mipmap/ic_chinese_cabbage_16.png";
MenuItem show = new MenuItem("鏄剧ず");//鏄剧ずShow
//缁戝畾绯荤粺鎵樼洏浜嬩欢
show.addActionListener(actionListener -> {
@@ -894,7 +896,6 @@
@Override
public void accept(Object o) throws Exception {
String targetUrl = "jdbc:sqlserver://" + targetHost + ";databaseName=" + targetDbName + ";integratedSecurity=false;";
- String sql1 = "SELECT * FROM IOT_Equipment_Info WHERE IE_Type = 'XPH鐗╄仈缃戝叧'; ";//杩欓噷涓鸿緭鍏ョ殑SQL璇彞
List<String> keyList = new ArrayList<>();
//1.浠庣洰鏍囧簱鏌ュ嚭鏉ユ湁澶氬皯浼犳劅鍣ㄩ渶瑕佹煡鎵�
@@ -902,7 +903,7 @@
Statement stmt = con.createStatement()
) {
- ResultSet rs = stmt.executeQuery(sql1);
+ ResultSet rs = stmt.executeQuery(SQL1_QUERY);
while (rs.next()) {
keyList.add(rs.getString("IE_Param"));
}
@@ -919,11 +920,10 @@
for (String key : keyList) {
StringUtil.append(sb, key, "','");
}
- String keyArray = String.format("'%s'", sb);
+ String sql = String.format(SQL2_QUERY, sb);
String srcUrl = "jdbc:sqlserver://" + srcHost + ";databaseName=" + srcDbName + ";integratedSecurity=false;";
- String sql2 = "SELECT SD_Addr + '|' + SD_Code IE_Param, * FROM TY_SensorData WHERE SD_Key IN ( " + keyArray + " );";
- //System.out.println("sql:" + sql2);
+ //System.out.println("sql2:" + sql);
Map<String, List<TargetBean>> map = new HashMap<>();
@@ -932,7 +932,7 @@
Statement stmt = con.createStatement()
) {
List<TargetBean> list;
- ResultSet rs = stmt.executeQuery(sql2);
+ ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
String key = rs.getString("SD_Key");
String param = rs.getString("IE_Param");
@@ -962,23 +962,17 @@
Statement stmt3 = con3.createStatement()
) {
//寰幆淇敼鏁版嵁
+ Timestamp time = new Timestamp(System.currentTimeMillis());
+ String version = String.format("%sV%s", AppName, VersionName);//"SyncToolsV1.0";
Set<Map.Entry<String, List<TargetBean>>> entrySet = map.entrySet();
for (Map.Entry<String, List<TargetBean>> entry : entrySet) {
- String key = entry.getKey();
List<TargetBean> list = entry.getValue();
if (list != null) {
- Timestamp time = new Timestamp(System.currentTimeMillis());
- String version = String.format("%sV%s", AppName, VersionName);//"SyncToolsV1.0";
+ String key = entry.getKey();
for (TargetBean tb : list) {
if (tb == null) continue;
- String sql3 = "UPDATE IOT_Equipment_Info " +
- "SET IE_Realtime_Data = ?, IE_Realtime_Time = ?, Edit_User = ?, Edit_Time = ? " +
- "WHERE IE_Param = ? AND IE_Parent = ( " +
- "SELECT IE_ID FROM IOT_Equipment_Info WHERE IE_Param = ? " +
- ") ;";
-
- PreparedStatement ps = con3.prepareStatement(sql3);
+ PreparedStatement ps = con3.prepareStatement(SQL3_UPDATE);
ps.setString(1, tb.getData());
ps.setTimestamp(2, tb.getTime());
ps.setString(3, version);
--
Gitblit v1.9.1