mesclient-siemens-fsw/src/com/mes/util/JdbcUtils.java

package com.mes.util;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

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

public class JdbcUtils {
	public static final Logger log =  LoggerFactory.getLogger(JdbcUtils.class);
	//通过上面的工具就可以获取到properties文件中的键值从而可以加载驱动 获取链接 从而 可以增删改查
	public static Connection conn = null;
    public static String Drivde="org.sqlite.JDBC";
	public static String DATABASE_URL="jdbc:sqlite:mes_db.db";
    
    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_fsw_data();
        } catch (Exception e) {
            // TODO Auto-generated catch block
        	close();//关闭数据库连接
            e.printStackTrace();
        }
        return conn;
    }

	public static void openConnection() {
		try {
			Class.forName(Drivde);// 加载驱动,连接sqlite的jdbc
			conn = DriverManager.getConnection(DATABASE_URL);
			create_fsw_data();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
			reconnect();
		}
	}

	private static void reconnect() {
		try {
			// 关闭旧连接
			if (conn != null && !conn.isClosed()) {
				conn.close();
			}
			// 重新建立连接
			conn = DriverManager.getConnection(DATABASE_URL);
			create_fsw_data();
		} catch (SQLException e) {
			e.printStackTrace();
			// 如果重连失败，可以进一步处理异常，比如记录日志、通知管理员等
		}
	}
    
    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 submitRecord = "CREATE TABLE if not exists submit_record(\n" +
				"   id INTEGER PRIMARY KEY AUTOINCREMENT, -- 自增ID\n" +
				"   oprno VARCHAR(20),                    -- 工位号 \n" +
				"   sn VARCHAR(48),                       -- 二维码\n" +
				"   bw VARCHAR(1000),                     -- 报文 \n" +
				"   record_time DATETIME,                 -- 记录时间\n" +
				"   state CHAR(1)      -- 状态(0 ->未提交, 1 ->已提交)\n" +
				")";
		statement.executeUpdate(submitRecord);

        //新增表
        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);

        statement.close();
    }

    //新增方法
    public static void saveClientConfig(String key, String value) {
        try {
            if (conn == null || conn.isClosed()) {
                openConnection();
            }
            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();
            log.info("保存本地配置: {}={}", key, value);
        } catch (SQLException e) {
            log.info("保存本地配置失败: {}", e.getMessage());
        }
    }

    //新增方法
    public static String getClientConfig(String key, String defaultValue) {
        try {
            if (conn == null || conn.isClosed()) {
                openConnection();
            }
            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) {
            log.info("读取本地配置失败: {}", e.getMessage());
        }
        return defaultValue;
    }

    //新增方法
    public static String getProductTypeConfigKey(String gw) {
        return "product_type_" + gw;
    }

    public static void create_fsw_data() throws SQLException {
        Statement statement = conn.createStatement();
        String sql = "CREATE TABLE if not exists fsw_params("
                + "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                + "oprno VARCHAR(20),"
                + "line_sn VARCHAR(20),"
                + "sn VARCHAR(100),"
                + "spindle_speed VARCHAR(50),"
                + "feed_rate VARCHAR(50),"
                + "pressure VARCHAR(50),"
                + "ucode VARCHAR(50),"
                + "record_time DATETIME,"
                + "sync INT DEFAULT 0"
                + ")";
        statement.executeUpdate(sql);
        statement.close();
    }

    public static boolean insertFswParam(String oprno, String lineSn, String sn,
                                         String spindleSpeed, String feedRate, String pressure, String ucode) {
        String recordTime = DateLocalUtils.getCurrentTime();
        try {
            if (conn == null || conn.isClosed()) {
                openConnection();
            }
            Statement statement = conn.createStatement();
            statement.executeUpdate("INSERT INTO fsw_params (oprno,line_sn,sn,spindle_speed,feed_rate,pressure,ucode,record_time,sync) VALUES ("
                    + "'" + sqlEscape(oprno) + "',"
                    + "'" + sqlEscape(lineSn) + "',"
                    + "'" + sqlEscape(sn) + "',"
                    + "'" + sqlEscape(spindleSpeed) + "',"
                    + "'" + sqlEscape(feedRate) + "',"
                    + "'" + sqlEscape(pressure) + "',"
                    + "'" + sqlEscape(ucode) + "',"
                    + "'" + recordTime + "',"
                    + "0)");
            statement.close();
            return true;
        } catch (SQLException e) {
            log.info("向fsw_params表插入数据失败: {}", e.getMessage());
            return false;
        }
    }

    public static List<FswParamReq> getFswParams() {
        List<FswParamReq> list = new ArrayList<>();
        String sql = "select id,oprno,line_sn,sn,spindle_speed,feed_rate,pressure,ucode,sync from fsw_params where sync = 0 order by id asc limit 100";
        try {
            if (conn == null || conn.isClosed()) {
                openConnection();
            }
            Statement stmt = conn.createStatement();
            ResultSet ret = stmt.executeQuery(sql);
            while (ret.next()) {
                FswParamReq req = new FswParamReq();
                req.setId(ret.getInt(1));
                req.setOprno(ret.getString(2));
                req.setLineSn(ret.getString(3));
                req.setSn(ret.getString(4));
                req.setSpindleSpeed(ret.getString(5));
                req.setFeedRate(ret.getString(6));
                req.setPressure(ret.getString(7));
                req.setUcode(ret.getString(8));
                req.setSync(ret.getInt(9));
                list.add(req);
            }
            ret.close();
            stmt.close();
        } catch (SQLException e) {
            log.info("读取fsw_params失败: {}", e.getMessage());
        }
        return list;
    }

    public static void updateFswSync(Integer id, Integer sync) throws SQLException {
        if (conn == null || conn.isClosed()) {
            openConnection();
        }
        Statement statement = conn.createStatement();
        statement.executeUpdate("update fsw_params set sync = " + sync + " where id = " + id);
        statement.close();
    }

    private static String sqlEscape(String value) {
        if (value == null) {
            return "";
        }
        return value.replace("'", "''");
    }

    //插入数据
    public static boolean insertData(String gw, String gy, String bw, String message_type, String sn) {
    	boolean ret = false;
    	String record_time = DateLocalUtils.getCurrentTime();
		try {
			// 确保连接已经打开
			if (JdbcUtils.conn == null || JdbcUtils.conn.isClosed()) {
				JdbcUtils.openConnection();
			}
			//创建连接对象，是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;
    }

	// 向 submit_record表 插入提交记录数据
	public static boolean insertSubmitRecord(String oprno, String sn, String bw){
		boolean ret = false;
		String record_time = DateLocalUtils.getCurrentTime();
		try {
			// 确保连接已经打开
			if (JdbcUtils.conn == null || JdbcUtils.conn.isClosed()) {
				JdbcUtils.openConnection();
			}
			Statement statement=conn.createStatement();
			String insertSQL = "INSERT INTO submit_record (oprno, sn, bw, record_time, state)" +
					"VALUES('" + oprno + "', '" + sn + "', '" + bw + "', '" + record_time + "', '0')";
			statement.executeUpdate(insertSQL);
			statement.close();
			ret = true;
			log.info("向submit_record表插入数据成功: {}", insertSQL);
		} catch (SQLException e) {
			ret = false;
			log.info("向submit_record表插入数据失败");
		}
		return ret;
	}

    
    public static void close(){
        try {
        	if(conn!=null) {
        		conn.close();
        	}
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    

}