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

package com.mes.util;

import com.mes.ui.CmtReq;
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.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(DATABASE_URL);//连接数据库zhou.db,不存在则创建
            System.out.println("连接到SQLite数据库成功！");
            create_bw_record();//初始化结构表
            create_cmt_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);

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

	public static void create_cmt_data() throws SQLException {
		Statement statement = conn.createStatement();
		String sqlEquipment = "CREATE TABLE if not exists bw_cmt("
				+ "id INTEGER PRIMARY KEY AUTOINCREMENT,oprno VARCHAR(20),line_sn VARCHAR(20),"
				+ "sn VARCHAR(50),content MEDIUMTEXT,create_time DATETIME,sync int(10) NULL DEFAULT 0"
				+ ")";
		statement.executeUpdate(sqlEquipment);
		statement.close();
	}

	public static boolean insertCmtData(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();
			}
			// 转义单引号，避免 JSON content 破坏 SQL
			String safeOprno = oprno == null ? "" : oprno.replace("'", "''");
			String safeLine = line_sn == null ? "" : line_sn.replace("'", "''");
			String safeSn = sn == null ? "" : sn.replace("'", "''");
			String safeContent = content == null ? "" : content.replace("'", "''");
			Statement statement = conn.createStatement();
			statement.executeUpdate("INSERT INTO bw_cmt (oprno,line_sn,sn,content,create_time) VALUES"
					+ " ('" + safeOprno + "', '" + safeLine + "', '" + safeSn + "', '" + safeContent + "','" + record_time + "')");
			statement.close();
			ret = true;
		} catch (SQLException e) {
			ret = false;
			e.printStackTrace();
		}
		return ret;
	}

	public static void updateProdSync(Integer id, Integer sync) throws SQLException {
		Statement statement = conn.createStatement();
		statement.executeUpdate("update bw_cmt set sync = " + sync + " where id = " + id);
		statement.close();
	}

	public static List<CmtReq> getCmtParams() {
		String sql = "select id,oprno,line_sn,sn,content,sync from bw_cmt where sync = 0 order by id asc limit 100";
		List<CmtReq> prods = new ArrayList<>();
		try {
			if (JdbcUtils.conn == null || JdbcUtils.conn.isClosed()) {
				JdbcUtils.openConnection();
			}
			Statement stmt = conn.createStatement();
			ResultSet ret = stmt.executeQuery(sql);
			while (ret.next()) {
				CmtReq cmtReq = new CmtReq();
				cmtReq.setId(ret.getInt(1));
				cmtReq.setOprno(ret.getString(2));
				cmtReq.setLineSn(ret.getString(3));
				cmtReq.setSn(ret.getString(4));
				cmtReq.setContent(ret.getString(5));
				cmtReq.setSync(ret.getInt(6));
				prods.add(cmtReq);
			}
			ret.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return prods;
	}

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

}