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

package com.mes.util;

import com.mes.ui.YgslResp;
import com.mes.ygsl.YgslConfig;

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

public class JdbcUtils {
	//通过上面的工具就可以获取到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_bw_tightening();//初始化
            create_ygsl_config();//初始化扭力枪配置表
        } catch (Exception e) {
            // TODO Auto-generated catch block
        	close();//关闭数据库连接
            e.printStackTrace();
			conn = null;
        }
        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 closeConnection() {
		try {
			if (conn != null && !conn.isClosed()) {
				conn.close();
			}
		} 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);
        System.out.println("表创建成功！");
        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();
    	if(conn==null) {
    		ret = false;
    	}else {
    		try {
    			//创建连接对象，是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;
    }
    

    
    public static void close(){
    	System.out.println("SQLite数据库连接关闭！");
        try {
        	if(conn!=null) {
        		conn.close();
        	}
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

	public static void create_bw_tightening() throws SQLException {
		Statement statement=conn.createStatement();   //创建连接对象，是Java的一个操作数据库的重要接口
		//设备结构数据库
		String sqlEquipment = "CREATE TABLE if not exists bw_tightening("
				+ "id INTEGER PRIMARY KEY AUTOINCREMENT,gw VARCHAR(20),line_sn VARCHAR(20),sn VARCHAR(48),"
				+ "tighteningStatus VARCHAR(10),torqueStatus VARCHAR(10),angleStatus VARCHAR(10),torqueMin VARCHAR(30),torqueMax VARCHAR(30),torqueFinal VARCHAR(30),torque VARCHAR(30),"
				+ "angleMin VARCHAR(30),angleMax VARCHAR(30),angleFinal VARCHAR(30),angle VARCHAR(30),tighteningID VARCHAR(30),jobID VARCHAR(30),serialNumber VARCHAR(10),pos VARCHAR(5)," +
				"sync int(10) NULL DEFAULT 0,record_time DATETIME,ucode VARCHAR(50))";   // 0=未同步到MES 1=已同步到MES
		//statement.executeUpdate("drop table if exists bw_prod");//判断是否有表tables的存在。有则删除
		statement.executeUpdate(sqlEquipment);
		System.out.println("表prod创建成功！");
		statement.close();
	}

	//插入数据
	public static boolean insertTighteningData(String gw, String lineSn, String sn, String tighteningStatus, String torqueStatus, String angleStatus, String torqueMin, String torqueMax, String torqueFinal, String torque,
											   String angleMin,String angleMax,String angleFinal,String angle,String tighteningID,String jobID,String serialNumber,String pos,String ucode) {
		boolean ret = false;
    	try{
			// 确保连接已经打开
			if (conn == null || conn.isClosed()) {
				openConnection();
			}

			String record_time = DateLocalUtils.getCurrentTime();
			Statement statement=conn.createStatement();
			statement.executeUpdate("INSERT INTO bw_tightening (gw,line_sn,sn,tighteningStatus,torqueStatus,angleStatus,torqueMin,torqueMax,torqueFinal,torque,angleMin,angleMax,angleFinal,angle,tighteningID,jobID,serialNumber,pos,ucode,record_time) VALUES"
					+ " ('"+gw+"', '"+lineSn+"', '"+sn+"', '"+tighteningStatus+"', '"+torqueStatus+"', '"+angleStatus+"', '"+torqueMin+"', '"+torqueMax+"', '"+torqueFinal+"', '"+torque+"','"+angleMin+"','"+angleMax+"','"+angleFinal+"','"+angle+"','"+tighteningID+"','"+jobID+"','"+serialNumber+"','"+pos+"','"+ucode+"','"+record_time+"')");//向数据库中插入数据
			statement.close();
			ret = true;
		}catch (Exception e){
    		e.printStackTrace();
			ret = false;
		}

		return ret;
	}

	//查询数据是否存在
	public static boolean checkTighteningById(String tighteningId,String pos,String jobID) {
		boolean ret = false;
		try{
			// 确保连接已经打开
			if (conn == null || conn.isClosed()) {
				openConnection();
			}

			Statement statement=conn.createStatement();
			ResultSet retset = statement.executeQuery("SELECT count(*) FROM bw_tightening WHERE tighteningID = '"+tighteningId+"' AND pos = '"+pos+"' AND jobID = '"+jobID+"' LIMIT 1");//向数据库中插入数据

			Integer count = 0;
			while (retset.next()) {
				count = retset.getInt(1);
			}
			statement.close();

			if(count > 0){
				ret = true;
			}
		}catch (Exception e){
			e.printStackTrace();
			ret = false;
		}

		return ret;
	}

	// 修改已同步
	public static void updateSync(Integer id,Integer sync){
		try{
			// 确保连接已经打开
			if (conn == null || conn.isClosed()) {
				openConnection();
			}

			Statement statement=conn.createStatement();   //创建连接对象，是Java的一个操作数据库的重要接口
			statement.executeUpdate("update bw_tightening set sync = "+sync + " where id = "+id);
			statement.close();
		}catch (Exception e){
			e.printStackTrace();
		}
	}


	public static List<YgslResp> getYgsl() {
		List<YgslResp> prods = new ArrayList<>();
		try{
			// 确保连接已经打开
			if (conn == null || conn.isClosed()) {
				openConnection();
			}

			//SQL语句
			String sql = "select id,gw,line_sn,sn,tighteningStatus,torqueStatus,angleStatus,torqueMin,torqueMax,torqueFinal,torque,angleMin,angleMax,angleFinal,angle,tighteningID,pos,serialNumber,record_time,ucode from bw_tightening where sync = 0 order by id asc limit 100";
			Statement statement=conn.createStatement();   //创建连接对象，是Java的一个操作数据库的重要接口
			ResultSet ret = statement.executeQuery(sql);
			while (ret.next()) {
				YgslResp prodReq = new YgslResp();
				prodReq.setId(ret.getInt(1));
				prodReq.setOprno(ret.getString(2));
				prodReq.setLineSn(ret.getString(3));
				prodReq.setSn(ret.getString(4));
				prodReq.setTighteningStatus(ret.getString(5));
				prodReq.setTorqueStatus(ret.getString(6));
				prodReq.setAngleStatus(ret.getString(7));
				prodReq.setTorqueMin(ret.getString(8));
				prodReq.setTorqueMax(ret.getString(9));
				prodReq.setTorqueFinal(ret.getString(10));
				prodReq.setTorque(ret.getString(11));
				prodReq.setAngleMin(ret.getString(12));
				prodReq.setAngleMax(ret.getString(13));
				prodReq.setAngleFinal(ret.getString(14));
				prodReq.setAngle(ret.getString(15));
				prodReq.setTighteningId(ret.getString(16));
				prodReq.setPos(ret.getString(17));
				prodReq.setSerialNumber(ret.getString(18));
				prodReq.setTighteningDate(ret.getString(19));
				prodReq.setCreateBy(ret.getString(20));
				prods.add(prodReq);
			}
			statement.close();
		}catch (Exception e){
			e.printStackTrace();
		}

		return prods;
	}

	public static void create_ygsl_config() throws SQLException {
		Statement statement = conn.createStatement();
		String sql = "CREATE TABLE if not exists ygsl_config("
				+ "id INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "gun_index INTEGER,"
				+ "gun_name VARCHAR(20),"
				+ "ip_address VARCHAR(50),"
				+ "port INTEGER,"
				+ "task_count INTEGER,"
				+ "enabled INTEGER DEFAULT 1,"
				+ "auto_submit INTEGER DEFAULT 1,"
				+ "create_time DATETIME,"
				+ "update_time DATETIME)";
		statement.executeUpdate(sql);
		System.out.println("扭力枪配置表创建成功！");

		ResultSet rs = statement.executeQuery("SELECT count(*) FROM ygsl_config");
		int count = 0;
		if (rs.next()) {
			count = rs.getInt(1);
		}
		statement.close();

		if (count == 0) {
			initDefaultYgslConfig();
		}
	}

	private static void initDefaultYgslConfig() throws SQLException {
		Statement statement = conn.createStatement();
		String now = DateLocalUtils.getCurrentTime();
		statement.executeUpdate("INSERT INTO ygsl_config (gun_index, gun_name, ip_address, port, task_count, enabled, auto_submit, create_time, update_time) VALUES "
				+ "(1, 'M5', '192.168.5.100', 4545, 12, 1, 1, '" + now + "', '" + now + "')");
		statement.executeUpdate("INSERT INTO ygsl_config (gun_index, gun_name, ip_address, port, task_count, enabled, auto_submit, create_time, update_time) VALUES "
				+ "(2, 'M8', '192.168.5.101', 4545, 1, 1, 1, '" + now + "', '" + now + "')");
		System.out.println("默认扭力枪配置初始化完成！");
		statement.close();
	}

	public static List<YgslConfig> getYgslConfigs() {
		List<YgslConfig> configs = new ArrayList<>();
		try {
			if (conn == null || conn.isClosed()) {
				openConnection();
			}
			Statement statement = conn.createStatement();
			ResultSet rs = statement.executeQuery("SELECT id, gun_index, gun_name, ip_address, port, task_count, enabled, auto_submit FROM ygsl_config ORDER BY gun_index");
			while (rs.next()) {
				YgslConfig config = new YgslConfig();
				config.setId(rs.getInt("id"));
				config.setGunIndex(rs.getInt("gun_index"));
				config.setGunName(rs.getString("gun_name"));
				config.setIpAddress(rs.getString("ip_address"));
				config.setPort(rs.getInt("port"));
				config.setTaskCount(rs.getInt("task_count"));
				config.setEnabled(rs.getInt("enabled"));
				config.setAutoSubmit(rs.getInt("auto_submit"));
				configs.add(config);
			}
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return configs;
	}

	public static List<YgslConfig> getEnabledYgslConfigs() {
		List<YgslConfig> configs = new ArrayList<>();
		try {
			if (conn == null || conn.isClosed()) {
				openConnection();
			}
			Statement statement = conn.createStatement();
			ResultSet rs = statement.executeQuery("SELECT id, gun_index, gun_name, ip_address, port, task_count, enabled, auto_submit FROM ygsl_config WHERE enabled = 1 ORDER BY gun_index");
			while (rs.next()) {
				YgslConfig config = new YgslConfig();
				config.setId(rs.getInt("id"));
				config.setGunIndex(rs.getInt("gun_index"));
				config.setGunName(rs.getString("gun_name"));
				config.setIpAddress(rs.getString("ip_address"));
				config.setPort(rs.getInt("port"));
				config.setTaskCount(rs.getInt("task_count"));
				config.setEnabled(rs.getInt("enabled"));
				config.setAutoSubmit(rs.getInt("auto_submit"));
				configs.add(config);
			}
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return configs;
	}

	public static boolean updateYgslConfig(YgslConfig config) {
		boolean ret = false;
		try {
			if (conn == null || conn.isClosed()) {
				openConnection();
			}
			String now = DateLocalUtils.getCurrentTime();
			Statement statement = conn.createStatement();
			String sql = "UPDATE ygsl_config SET gun_index = " + config.getGunIndex() + ", "
					+ "gun_name = '" + config.getGunName() + "', "
					+ "ip_address = '" + config.getIpAddress() + "', "
					+ "port = " + config.getPort() + ", "
					+ "task_count = " + config.getTaskCount() + ", "
					+ "enabled = " + config.getEnabled() + ", "
					+ "auto_submit = " + config.getAutoSubmit() + ", "
					+ "update_time = '" + now + "' "
					+ "WHERE id = " + config.getId();
			System.out.println("执行SQL: " + sql);
			int rowsAffected = statement.executeUpdate(sql);
			System.out.println("更新行数: " + rowsAffected);
			statement.close();
			ret = true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return ret;
	}

	public static boolean insertYgslConfig(YgslConfig config) {
		boolean ret = false;
		try {
			if (conn == null || conn.isClosed()) {
				openConnection();
			}
			String now = DateLocalUtils.getCurrentTime();
			Statement statement = conn.createStatement();
			statement.executeUpdate("INSERT INTO ygsl_config (gun_index, gun_name, ip_address, port, task_count, enabled, auto_submit, create_time, update_time) VALUES ("
					+ config.getGunIndex() + ", '" + config.getGunName() + "', '" + config.getIpAddress() + "', " + config.getPort() + ", " + config.getTaskCount() + ", " + config.getEnabled() + ", " + config.getAutoSubmit() + ", '" + now + "', '" + now + "')");
			statement.close();
			ret = true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return ret;
	}

	public static boolean deleteYgslConfig(int id) {
		boolean ret = false;
		try {
			if (conn == null || conn.isClosed()) {
				openConnection();
			}
			Statement statement = conn.createStatement();
			statement.executeUpdate("DELETE FROM ygsl_config WHERE id = " + id);
			statement.close();
			ret = true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return ret;
	}

}