SQL语句

Hunter0x7c7

2023-08-12 0b0ca05018a5f47a1c8b1573e0eff4310ebca1a9

SQL语句

 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
@@ -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);

 src/main/resources/mipmap/ic_chinese_cabbage_240.png