OP160/src/com/mes/util/JdbcUtils.java

package com.mes.util;

import com.alibaba.fastjson2.JSONObject;
import com.mes.ui.ProdReq;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class JdbcUtils {
    //通过上面的工具就可以获取到properties文件中的键值从而可以加载驱动 获取链接 从而 可以增删改查
    public static Connection conn = null;
    public static String Drivde = "org.sqlite.JDBC";

    public static Connection getConn() {
        try {
            Class.forName(Drivde);// 加载驱动,连接sqlite的jdbc
            conn = DriverManager.getConnection("jdbc:sqlite:mes_db.db");//连接数据库zhou.db,不存在则创建
            System.out.println("连接到SQLite数据库成功！");
            create_bw_record();//初始化结构表
            create_bw_prod();
            create_config_table();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            close();//关闭数据库连接
            e.printStackTrace();
        }
        return conn;
    }

    public static void create_config_table() throws SQLException {
        Statement statement = conn.createStatement();
        String sql = "CREATE TABLE if not exists bw_config("
                + "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                + "a_set_num SHORT, "
                + "b_set_num SHORT, "
                + "plc_ip_a VARCHAR(20), "
                + "plc_ip_b VARCHAR(20), "
                + "bolt_type_a VARCHAR(50), "
                + "bolt_type_b VARCHAR(50), "
                + "mes_gw VARCHAR(50), "
                + "mes_gw_des VARCHAR(100))";
        statement.executeUpdate(sql);

        // 检查并添加缺失的列（如果表已存在但没有新列）
        try {
            statement.executeUpdate("ALTER TABLE bw_config ADD COLUMN bolt_type_a VARCHAR(50)");
        } catch (SQLException e) {
        }
        try {
            statement.executeUpdate("ALTER TABLE bw_config ADD COLUMN bolt_type_b VARCHAR(50)");
        } catch (SQLException e) {
        }
        try {
            statement.executeUpdate("ALTER TABLE bw_config ADD COLUMN mes_gw VARCHAR(50)");
        } catch (SQLException e) {
        }
        try {
            statement.executeUpdate("ALTER TABLE bw_config ADD COLUMN mes_gw_des VARCHAR(100)");
        } catch (SQLException e) {
        }

        // 检查是否已有数据，若无则插入默认值
        ResultSet rs = statement.executeQuery("SELECT count(*) FROM bw_config");
        if (rs.next() && rs.getInt(1) == 0) {
            statement.executeUpdate("INSERT INTO bw_config (a_set_num, b_set_num, plc_ip_a, plc_ip_b, bolt_type_a, bolt_type_b, mes_gw, mes_gw_des) "
                    + "VALUES (40, 0, '192.168.200.10', '', 'M6', '', 'OP300', '默认工位')");
        }
        rs.close();
        System.out.println("表config创建并初始化成功！");
        statement.close();
    }

    public static java.util.Map<String, Object> getConfig() {
        java.util.Map<String, Object> config = new java.util.HashMap<>();
        String sql = "SELECT a_set_num, b_set_num, plc_ip_a, plc_ip_b, bolt_type_a, bolt_type_b, mes_gw, mes_gw_des FROM bw_config LIMIT 1";
        Connection conn = JdbcUtils.getConn();
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            if (rs.next()) {
                config.put("a_set_num", rs.getShort("a_set_num"));
                config.put("b_set_num", rs.getShort("b_set_num"));
                config.put("plc_ip_a", rs.getString("plc_ip_a"));
                config.put("plc_ip_b", rs.getString("plc_ip_b"));
                config.put("bolt_type_a", rs.getString("bolt_type_a") == null ? "M6" : rs.getString("bolt_type_a"));
                config.put("bolt_type_b", rs.getString("bolt_type_b") == null ? "M8" : rs.getString("bolt_type_b"));
                config.put("mes_gw", rs.getString("mes_gw") == null ? "OP300" : rs.getString("mes_gw"));
                config.put("mes_gw_des", rs.getString("mes_gw_des") == null ? "默认工位" : rs.getString("mes_gw_des"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return config;
    }

    public static void updateConfig(short aSetNum, short bSetNum, String plcIpA, String plcIpB, String boltTypeA, String boltTypeB, String mesGw, String mesGwDes) {
        String sql = "UPDATE bw_config SET a_set_num = ?, b_set_num = ?, plc_ip_a = ?, plc_ip_b = ?, bolt_type_a = ?, bolt_type_b = ?, mes_gw = ?, mes_gw_des = ? WHERE id = 1";
        Connection conn = JdbcUtils.getConn();
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setShort(1, aSetNum);
            pstmt.setShort(2, bSetNum);
            pstmt.setString(3, plcIpA);
            pstmt.setString(4, plcIpB);
            pstmt.setString(5, boltTypeA);
            pstmt.setString(6, boltTypeB);
            pstmt.setString(7, mesGw);
            pstmt.setString(8, mesGwDes);
            pstmt.executeUpdate();
            System.out.println("更新本地配置成功：aSetNum=" + aSetNum + ", bSetNum=" + bSetNum + ", IP_A=" + plcIpA + ", IP_B=" + plcIpB + ", TypeA=" + boltTypeA + ", TypeB=" + boltTypeB + ", GW=" + mesGw);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void saveStationConfig(String gw, String gwDes) {
        getConn();
        String sql = "UPDATE bw_config SET mes_gw = ?, mes_gw_des = ? WHERE id = 1";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, gw);
            pstmt.setString(2, gwDes);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static JSONObject getStationConfig() {
        getConn();
        String sql = "SELECT mes_gw, mes_gw_des FROM bw_config WHERE id = 1";
        JSONObject config = null;
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                config = new JSONObject();
                config.put("mes_gw", rs.getString("mes_gw") == null ? "OP300" : rs.getString("mes_gw"));
                config.put("mes_gw_des", rs.getString("mes_gw_des") == null ? "默认工位" : rs.getString("mes_gw_des"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return config;
    }

    public static void saveGunConfig(String deviceId, String name, String ip, int port, int presetCount, String boltType) {
        getConn();
        String sql = "";
        if ("gunA".equals(deviceId)) {
            sql = "UPDATE bw_config SET plc_ip_a = ?, a_set_num = ?, bolt_type_a = ? WHERE id = 1";
        } else if ("gunB".equals(deviceId)) {
            sql = "UPDATE bw_config SET plc_ip_b = ?, b_set_num = ?, bolt_type_b = ? WHERE id = 1";
        }
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, ip);
            pstmt.setInt(2, presetCount);
            pstmt.setString(3, boltType);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static JSONObject getGunConfig(String deviceId) {
        getConn();
        String sql = "";
        if ("gunA".equals(deviceId)) {
            sql = "SELECT plc_ip_a as ip, a_set_num as preset_count, bolt_type_a as bolt_type FROM bw_config WHERE id = 1";
        } else if ("gunB".equals(deviceId)) {
            sql = "SELECT plc_ip_b as ip, b_set_num as preset_count, bolt_type_b as bolt_type FROM bw_config WHERE id = 1";
        }
        JSONObject config = null;
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                config = new JSONObject();
                config.put("device_id", deviceId);
                config.put("device_name", "gunA".equals(deviceId) ? "拉铆枪A" : "拉铆枪B");
                config.put("ip", rs.getString("ip"));
                config.put("port", 2525);
                config.put("preset_count", rs.getInt("preset_count"));
                config.put("bolt_type", rs.getString("bolt_type"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return config;
    }

    public static void create_bw_record() throws SQLException {
        Statement statement = conn.createStatement();   //创建连接对象，是Java的一个操作数据库的重要接口
        //设备结构数据库
        String sqlEquipment = "CREATE TABLE if not exists bw_record("
                + "id INTEGER PRIMARY KEY AUTOINCREMENT,gw VARCHAR(20),gy VARCHAR(20),message_type VARCHAR(20),sn VARCHAR(48),bw VARCHAR(1000),record_time DATETIME,"
                + "info_01 VARCHAR(200),info_02 VARCHAR(200),info_03 VARCHAR(200))";
//        statement.executeUpdate("drop table if exists bw_record");//判断是否有表tables的存在。有则删除
        statement.executeUpdate(sqlEquipment);
        String clientConfig = "CREATE TABLE if not exists client_config("
                + "config_key VARCHAR(100) PRIMARY KEY,"
                + "config_value VARCHAR(500),"
                + "update_time DATETIME"
                + ")";
        statement.executeUpdate(clientConfig);
        System.out.println("表record创建成功！");
        statement.close();
    }

    public static void saveClientConfig(String key, String value) {
        try {
            if (conn == null || conn.isClosed()) {
                getConn();
            }
            String sql = "INSERT INTO client_config (config_key, config_value, update_time) VALUES (?, ?, ?) "
                    + "ON CONFLICT(config_key) DO UPDATE SET config_value = excluded.config_value, update_time = excluded.update_time";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, key);
            ps.setString(2, value);
            ps.setString(3, DateLocalUtils.getCurrentTime());
            ps.executeUpdate();
            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static String getClientConfig(String key, String defaultValue) {
        try {
            if (conn == null || conn.isClosed()) {
                getConn();
            }
            String sql = "SELECT config_value FROM client_config WHERE config_key = ?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, key);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                String value = rs.getString("config_value");
                rs.close();
                ps.close();
                return value;
            }
            rs.close();
            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return defaultValue;
    }

    public static String getProductTypeConfigKey(String gw) {
        return "product_type_" + gw;
    }

    //插入数据
    public static boolean insertData(String gw, String gy, String bw, String message_type, String sn) {
        boolean ret = false;
        String record_time = DateLocalUtils.getCurrentTime();
        if (conn == null) {
            ret = false;
        } else {
            try {
                //创建连接对象，是Java的一个操作数据库的重要接口
                Statement statement = conn.createStatement();
                statement.executeUpdate("INSERT INTO bw_record (gw,gy,bw,record_time,message_type,sn) VALUES"
                        + " ('" + gw + "', '" + gy + "', '" + bw + "', '" + record_time + "','" + message_type + "','" + sn + "')");//向数据库中插入数据
                statement.close();
                ret = true;
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                //e.printStackTrace();
                ret = false;
            }
        }

        return ret;
    }

    public static void create_bw_prod() throws SQLException {
        Statement statement = conn.createStatement();   //创建连接对象，是Java的一个操作数据库的重要接口
        //设备结构数据库
        String sqlEquipment = "CREATE TABLE if not exists bw_prod("
                + "id INTEGER PRIMARY KEY AUTOINCREMENT,gw VARCHAR(20),line_sn VARCHAR(20),type VARCHAR(20),sn VARCHAR(48),"
                + "fout VARCHAR(20),sout VARCHAR(20),fmin VARCHAR(20),smin VARCHAR(20),fmax VARCHAR(20),smax VARCHAR(20),"
                + "qty VARCHAR(5),serial_number VARCHAR(50),sync int(10) NULL DEFAULT 0,record_time DATETIME,ucode VARCHAR(50))";   // 0=未同步到MES 1=已同步到MES
        //statement.executeUpdate("drop table if exists bw_prod");//判断是否有表tables的存在。有则删除
        statement.executeUpdate(sqlEquipment);
        System.out.println("表prod创建成功！");
        statement.close();
    }

    public static void close() {
        System.out.println("SQLite数据库连接关闭！");
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //插入拉铆数据
    public static boolean insertProdData(String gw, String lineSn, String sn, String type, String fout, String sout, String fmin, String smin, String fmax, String smax, String qty, String serial_number, String ucode) {
        boolean ret = false;
        String record_time = DateLocalUtils.getCurrentTime();
        JdbcUtils.getConn();

        if (conn == null) {
            ret = false;
        } else {
            try {
                //创建连接对象，是Java的一个操作数据库的重要接口
                Statement statement = conn.createStatement();
                statement.executeUpdate("INSERT INTO bw_prod (gw,line_sn,type,sn,fout,sout,fmin,smin,fmax,smax,qty,serial_number,ucode,record_time) VALUES"
                        + " ('" + gw + "', '" + lineSn + "', '" + type + "', '" + sn + "', '" + fout + "', '" + sout + "', '" + fmin + "', '" + smin + "', '" + fmax + "', '" + smax + "', '" + qty + "','" + serial_number + "','" + ucode + "','" + record_time + "')");//向数据库中插入数据
                statement.close();

                if (!qty.equals("1")) {
                    com.mes.ui.DataUtil.upAlarm(sn, ucode);
                }

                ret = true;
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
                ret = false;
            }
        }

        return ret;
    }

    public static void updateProdSync(Integer id, Integer sync) throws SQLException {
        Connection conn = JdbcUtils.getConn();
        Statement statement = conn.createStatement();   //创建连接对象，是Java的一个操作数据库的重要接口
        statement.executeUpdate("update bw_prod set sync = " + sync + " where id = " + id);
        statement.close();
    }

    public static List<ProdReq> getProds() {
        //SQL语句
        String sql = "select id,gw,line_sn,type,sn,fout,sout,fmin,smin,fmax,smax,qty,serial_number,sync,record_time,ucode from bw_prod where sync = 0 order by id asc limit 100";
        Connection conn = JdbcUtils.getConn();
        Statement stmt = null;
        ResultSet ret = null;
        String password = null;
        List<ProdReq> prods = new ArrayList<>();
        try {
            stmt = conn.createStatement();
            //执行语句，得到结果集
            ret = stmt.executeQuery(sql);
            System.out.println("sql:" + sql);
            while (ret.next()) {
                ProdReq prodReq = new ProdReq();
                prodReq.setId(ret.getInt(1));
                prodReq.setGw(ret.getString(2));
                prodReq.setLineSn(ret.getString(3));
                prodReq.setType(ret.getString(4));
                prodReq.setSn(ret.getString(5));
                prodReq.setFout(ret.getString(6));
                prodReq.setSout(ret.getString(7));
                prodReq.setFmin(ret.getString(8));
                prodReq.setSmin(ret.getString(9));
                prodReq.setFmax(ret.getString(10));
                prodReq.setSmax(ret.getString(11));
                prodReq.setQty(ret.getString(12));
                prodReq.setSerialNumber(ret.getString(13));
                prodReq.setSync(ret.getInt(14));
                prodReq.setRecordTime(ret.getString(15));
                prodReq.setUcode(ret.getString(16));
                prods.add(prodReq);
            }
            ret.close();
            stmt.close();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
        return prods;
    }

}