OP280/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.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

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_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 deviceParamTable = "CREATE TABLE if not exists bw_device_param(\n" +
				"   id INTEGER PRIMARY KEY AUTOINCREMENT, -- 自增ID\n" +
				"   oprno VARCHAR(20),                      -- 工位号 \n" +
				"   line_sn VARCHAR(20),                    -- 产线编号\n" +
				"   sn VARCHAR(48),                        -- 产品序列号\n" +
				"   content VARCHAR(5000),                 -- 存储内容(60条记录的JSON)\n" +
				"   create_time DATETIME,                  -- 创建时间\n" +
				"   sync INTEGER DEFAULT 0                 -- 同步状态(0=未同步,1=已同步)\n" +
				")";
		statement.executeUpdate(deviceParamTable);

        statement.close();
    }
    
    //插入数据
    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;
	}

	// 向 bw_device_param表 插入设备参数数据
	public static boolean insertDeviceParamData(String oprno, String line_sn, String sn, String content){
		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 bw_device_param (oprno, line_sn, sn, content, create_time, sync)" +
					"VALUES('" + oprno + "', '" + line_sn + "', '" + sn + "', '" + content + "', '" + record_time + "', 0)";
			statement.executeUpdate(insertSQL);
			statement.close();
			ret = true;
			log.info("向bw_device_param表插入数据成功");
		} catch (SQLException e) {
			ret = false;
			log.info("向bw_device_param表插入数据失败: " + e.getMessage());
		}
		return ret;
	}

	// 查询未同步的设备参数数据
	public static List<Map<String, Object>> getDeviceParams() {
		String sql = "select id,oprno,line_sn,sn,content,sync from bw_device_param where sync = 0 order by id asc limit 100";
		List<Map<String, Object>> prods = new ArrayList<>();
		try {
			if (JdbcUtils.conn == null || JdbcUtils.conn.isClosed()) {
				JdbcUtils.openConnection();
			}
			Statement statement = conn.createStatement();
			ResultSet ret = statement.executeQuery(sql);
			while (ret.next()) {
				Map<String, Object> map = new HashMap<>();
				map.put("id", ret.getInt(1));
				map.put("oprno", ret.getString(2));
				map.put("lineSn", ret.getString(3));
				map.put("sn", ret.getString(4));
				map.put("content", ret.getString(5));
				map.put("sync", ret.getInt(6));
				prods.add(map);
			}
			ret.close();
			statement.close();
		} catch (SQLException e) {
			log.info("查询未同步设备参数数据失败: " + e.getMessage());
		}
		return prods;
	}

	// 更新设备参数同步状态
	public static void updateDeviceParamSync(Integer id, Integer sync) throws SQLException {
		if (JdbcUtils.conn == null || JdbcUtils.conn.isClosed()) {
			JdbcUtils.openConnection();
		}
		Statement statement = conn.createStatement();
		statement.executeUpdate("update bw_device_param set sync = " + sync + " where id = " + id);
		statement.close();
	}

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

}