OP240/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;

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

        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 void close(){
        try {
        	if(conn!=null) {
        		conn.close();
        	}
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    

}