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

package com.mes.util;

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

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Set;

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(DATABASE_URL);//连接数据库zhou.db,不存在则创建
            System.out.println("连接到SQLite数据库成功！");
            create_bw_record();//初始化结构表
            //create_measure_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);
		} 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);
		} 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 opIpConfig = "CREATE TABLE if not exists op_ip_config(\n" +
				"   op VARCHAR(20) PRIMARY KEY,\n" +
				"   opname VARCHAR(100),\n" +
				"   ip VARCHAR(50)\n" +
				")";
		statement.executeUpdate(opIpConfig);

		String rivetRecord = "CREATE TABLE if not exists rivet_record(\n" +
				"   id INTEGER PRIMARY KEY AUTOINCREMENT,\n" +
				"   oprno VARCHAR(20),\n" +
				"   line_sn VARCHAR(20),\n" +
				"   sn VARCHAR(48),\n" +
				"   point_no INTEGER,\n" +
				"   result VARCHAR(4),\n" +
				"   stroke REAL,\n" +
				"   pressure REAL,\n" +
				"   hold_time INTEGER,\n" +
				"   record_time DATETIME,\n" +
				"   uploaded CHAR(1) DEFAULT '0'\n" +
				")";
		statement.executeUpdate(rivetRecord);

        statement.close();
    }

	public static String getServerIpByOp(String op) {
		try {
			if (op == null || op.trim().isEmpty()) {
				return null;
			}
			if (JdbcUtils.conn == null || JdbcUtils.conn.isClosed()) {
				JdbcUtils.openConnection();
			}
			String sql = "SELECT ip FROM op_ip_config WHERE op = ?";
			try (PreparedStatement ps = conn.prepareStatement(sql)) {
				ps.setString(1, op.trim());
				try (ResultSet rs = ps.executeQuery()) {
					if (rs.next()) {
						String ip = rs.getString("ip");
						return ip == null ? null : ip.trim();
					}
				}
			}
		} catch (Exception e) {
			log.info("查询op_ip_config失败: {}", e.getMessage());
		}
		return null;
	}

	public static String getOpNameByOp(String op) {
		try {
			if (op == null || op.trim().isEmpty()) {
				return null;
			}
			if (JdbcUtils.conn == null || JdbcUtils.conn.isClosed()) {
				JdbcUtils.openConnection();
			}
			String sql = "SELECT opname FROM op_ip_config WHERE op = ?";
			try (PreparedStatement ps = conn.prepareStatement(sql)) {
				ps.setString(1, op.trim());
				try (ResultSet rs = ps.executeQuery()) {
					if (rs.next()) {
						String opname = rs.getString("opname");
						return opname == null ? null : opname.trim();
					}
				}
			}
		} catch (Exception e) {
			log.info("查询op_ip_config.opname失败: {}", e.getMessage());
		}
		return null;
	}

	public static boolean upsertOpIpConfig(String op, String opname, String ip) {
		boolean ret = false;
		try {
			if (op == null || op.trim().isEmpty() || ip == null || ip.trim().isEmpty()) {
				return false;
			}
			if (JdbcUtils.conn == null || JdbcUtils.conn.isClosed()) {
				JdbcUtils.openConnection();
			}
			String sql = "INSERT INTO op_ip_config(op, opname, ip) VALUES(?, ?, ?) " +
					"ON CONFLICT(op) DO UPDATE SET opname=excluded.opname, ip=excluded.ip";
			try (PreparedStatement ps = conn.prepareStatement(sql)) {
				ps.setString(1, op.trim());
				ps.setString(2, opname == null ? "" : opname.trim());
				ps.setString(3, ip.trim());
				ps.executeUpdate();
			}
			ret = true;
		} catch (Exception e) {
			ret = false;
			log.info("保存op_ip_config失败: {}", e.getMessage());
		}
		return ret;
	}

	public static boolean clearOpIpConfig() {
		try {
			if (JdbcUtils.conn == null || JdbcUtils.conn.isClosed()) {
				JdbcUtils.openConnection();
			}
			String sql = "DELETE FROM op_ip_config";
			try (PreparedStatement ps = conn.prepareStatement(sql)) {
				ps.executeUpdate();
			}
			return true;
		} catch (Exception e) {
			log.info("清除op_ip_config失败: {}", e.getMessage());
			return false;
		}
	}

	public static String[] getAnyOpIpConfig() {
		try {
			if (JdbcUtils.conn == null || JdbcUtils.conn.isClosed()) {
				JdbcUtils.openConnection();
			}
			String sql = "SELECT op, opname, ip FROM op_ip_config ORDER BY rowid DESC LIMIT 1";
			try (PreparedStatement ps = conn.prepareStatement(sql);
				 ResultSet rs = ps.executeQuery()) {
				if (rs.next()) {
					String op = rs.getString("op");
					String opname = rs.getString("opname");
					String ip = rs.getString("ip");
					return new String[]{
						op == null ? "" : op.trim(),
						opname == null ? "" : opname.trim(),
						ip == null ? "" : ip.trim()
					};
				}
			}
		} catch (Exception e) {
			log.info("查询op_ip_config任意配置失败: {}", e.getMessage());
		}
		return null;
	}
    
    //插入数据
    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 boolean insertRivetRecord(String oprno, String lineSn, String sn, int pointNo,
											String result, float stroke, float pressure, int holdTime) {
		String recordTime = DateLocalUtils.getCurrentTime();
		try {
			if (JdbcUtils.conn == null || JdbcUtils.conn.isClosed()) {
				JdbcUtils.openConnection();
			}
			String sql = "INSERT INTO rivet_record (oprno, line_sn, sn, point_no, result, stroke, pressure, hold_time, record_time, uploaded) " +
					"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, '0')";
			try (PreparedStatement ps = conn.prepareStatement(sql)) {
				ps.setString(1, oprno);
				ps.setString(2, lineSn);
				ps.setString(3, sn);
				ps.setInt(4, pointNo);
				ps.setString(5, result);
				ps.setFloat(6, stroke);
				ps.setFloat(7, pressure);
				ps.setInt(8, holdTime);
				ps.setString(9, recordTime);
				ps.executeUpdate();
			}
			log.info("铆接数据已保存: sn={}, point={}, result={}", sn, pointNo, result);
			return true;
		} catch (SQLException e) {
			log.info("保存铆接数据失败: {}", e.getMessage());
			return false;
		}
	}

	public static boolean markRivetRecordsUploaded(String sn) {
		try {
			if (JdbcUtils.conn == null || JdbcUtils.conn.isClosed()) {
				JdbcUtils.openConnection();
			}
			String sql = "UPDATE rivet_record SET uploaded = '1' WHERE sn = ? AND uploaded = '0'";
			try (PreparedStatement ps = conn.prepareStatement(sql)) {
				ps.setString(1, sn);
				ps.executeUpdate();
			}
			return true;
		} catch (SQLException e) {
			log.info("更新铆接上传状态失败: sn={}, {}", sn, e.getMessage());
			return false;
		}
	}

	public static class RivetRecordItem {
		public String sn;
		public String oprno;
		public String lineSn;
		public int pointNo;
		public String result;
		public float stroke;
		public float pressure;
		public int holdTime;
	}

	public static List<String> getPendingRivetSnList() {
		List<String> list = new ArrayList<>();
		try {
			if (JdbcUtils.conn == null || JdbcUtils.conn.isClosed()) {
				JdbcUtils.openConnection();
			}
			String sql = "SELECT DISTINCT sn FROM rivet_record WHERE uploaded = '0' ORDER BY sn";
			try (PreparedStatement ps = conn.prepareStatement(sql);
				 ResultSet rs = ps.executeQuery()) {
				Set<String> seen = new LinkedHashSet<>();
				while (rs.next()) {
					seen.add(rs.getString("sn"));
				}
				list.addAll(seen);
			}
		} catch (SQLException e) {
			log.info("查询待上传工件码失败: {}", e.getMessage());
		}
		return list;
	}

	public static List<RivetRecordItem> getRivetRecordsBySn(String sn) {
		List<RivetRecordItem> list = new ArrayList<>();
		try {
			if (JdbcUtils.conn == null || JdbcUtils.conn.isClosed()) {
				JdbcUtils.openConnection();
			}
			String sql = "SELECT sn, oprno, line_sn, point_no, result, stroke, pressure, hold_time " +
					"FROM rivet_record WHERE sn = ? AND uploaded = '0' ORDER BY point_no ASC";
			try (PreparedStatement ps = conn.prepareStatement(sql)) {
				ps.setString(1, sn);
				try (ResultSet rs = ps.executeQuery()) {
					while (rs.next()) {
						RivetRecordItem item = new RivetRecordItem();
						item.sn = rs.getString("sn");
						item.oprno = rs.getString("oprno");
						item.lineSn = rs.getString("line_sn");
						item.pointNo = rs.getInt("point_no");
						item.result = rs.getString("result");
						item.stroke = rs.getFloat("stroke");
						item.pressure = rs.getFloat("pressure");
						item.holdTime = rs.getInt("hold_time");
						list.add(item);
					}
				}
			}
		} catch (SQLException e) {
			log.info("查询铆接记录失败: sn={}, {}", sn, e.getMessage());
		}
		return list;
	}

	public static void deleteRivetRecordsBySn(String sn) {
		try {
			if (JdbcUtils.conn == null || JdbcUtils.conn.isClosed()) {
				JdbcUtils.openConnection();
			}
			String sql = "DELETE FROM rivet_record WHERE sn = ?";
			try (PreparedStatement ps = conn.prepareStatement(sql)) {
				ps.setString(1, sn);
				ps.executeUpdate();
			}
		} catch (SQLException e) {
			log.info("删除铆接记录失败: {}", e.getMessage());
		}
	}

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

}