mesclient-auto3/src/com/mes/util/JdbcUtils.java

package com.mes.util;

import com.alibaba.fastjson2.JSON;
import com.mes.ui.CacheData;
import com.mes.ui.ParamReq;
import com.mes.util.ParamReq2;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
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";
	// 表结构是否已初始化，建表只需执行一次
	private static volatile boolean tablesInited = false;

	public static Connection getConn(){
		try {
			Class.forName(Drivde);// 加载驱动,连接sqlite的jdbc
			conn = DriverManager.getConnection(DATABASE_URL);//连接数据库zhou.db,不存在则创建
			if (!tablesInited) {
				log.info("连接到SQLite数据库成功！");
				create_bw_record();//初始化结构表
				tablesInited = true;
			}
			//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 sqlEquipment2 = "CREATE TABLE if not exists bw_params("
				+ "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(sqlEquipment2);

		// 创建 提交记录表
		String cache = "CREATE TABLE if not exists cache(\n" +
				"   id INTEGER PRIMARY KEY AUTOINCREMENT, -- 自增ID\n" +
				"   sn VARCHAR(20),                    -- 唯一标识 \n" +
				"   content VARCHAR(3000),                       -- 绑定内容\n" +
				"   record_time DATETIME,                 -- 记录时间\n" +
				"   UNIQUE (sn)                 -- 唯一索引\n" +
				")";
		statement.executeUpdate(cache);

		statement.close();
	}

	public static CacheData getCache(String key) {
		//SQL语句
		String sql = "select id,sn,content from cache where sn = '"+key+"' order by id asc limit 1";
		Connection conn = JdbcUtils.getConn();
		Statement stmt=null;
		ResultSet ret = null;
		String password=null;
		List<ParamReq2> prods = new ArrayList<>();
		String content = "";
		try {
			stmt = conn.createStatement();
			//执行语句，得到结果集
			ret = stmt.executeQuery(sql);
			System.out.println(sql);
			while (ret.next()) {
				content = ret.getString(3);
			}
			System.out.println("content:"+content);
			ret.close();
			conn.close();//关闭连接
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		System.out.println("content2:"+content);
		if(content == null || content.trim().isEmpty()){
			return null;
		}
		content = CommonUtils.base64Decode(content);
		System.out.println("content3:"+content);
		return JSON.parseObject(content,CacheData.class);
	}

	public static Boolean setCache(String key,String value) {
		String record_time = DateLocalUtils.getCurrentTime();
		Connection conn = JdbcUtils.getConn();
		if(value == null || value.trim().isEmpty()) {
			return false;
		}
		value = CommonUtils.base64Encode(value);
		CacheData content = getCache(key);
		if(content == null) { // 新增
			try {
				Statement statement=conn.createStatement();
				statement.executeUpdate("INSERT INTO cache (sn,content,record_time) VALUES"
						+ " ('"+key+"', '"+value+"','"+record_time+"')");//向数据库中插入数据
				statement.close();
			}catch (Exception e) {
				e.printStackTrace();
			}
		}else{
			try{
				Statement statement=conn.createStatement();   //创建连接对象，是Java的一个操作数据库的重要接口
				statement.executeUpdate("update cache set content = '"+value.trim() + "' where sn = '"+key+"'");
				statement.close();
			}catch (Exception e) {
				e.printStackTrace();
			}
		}
		return true;
	}

	public static void updateProdSync(Integer id,Integer sync) throws SQLException {
		Connection conn = JdbcUtils.getConn();
		Statement statement=conn.createStatement();   //创建连接对象，是Java的一个操作数据库的重要接口
		statement.executeUpdate("update bw_params set sync = "+sync + " where id = "+id);
		statement.close();
	}

	public static List<ParamReq> getParams() {
		//SQL语句
		String sql = "select id,oprno,line_sn,sn,content,sync from bw_params where sync = 0 order by id asc limit 100";
		Connection conn = JdbcUtils.getConn();
		Statement stmt=null;
		ResultSet ret = null;
		String password=null;
		List<ParamReq> prods = new ArrayList<>();
		try {
			stmt = conn.createStatement();
			//执行语句，得到结果集
			ret = stmt.executeQuery(sql);
			System.out.println("sql:"+ sql);
			while (ret.next()) {
				ParamReq cmtReq = new ParamReq();
				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(Integer.valueOf(ret.getString(6)));
				prods.add(cmtReq);
			}
			ret.close();
			conn.close();//关闭连接
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		return prods;
	}

	//插入数据
	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;
	}

	// 向 submit_record表 插入提交记录数据
	public static boolean insertSubmitRecord(String oprno, String sn, String bw){
		boolean ret = false;
		String record_time = DateLocalUtils.getCurrentTime();
		if(conn==null) {
			ret = false;
		}else {
			try {
				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_measure_data() throws SQLException {
		Statement statement=conn.createStatement();   //创建连接对象，是Java的一个操作数据库的重要接口
		//statement.executeUpdate("drop table if exists measure_data");//判断是否有表tables的存在。有则删除
		//设备结构数据库
		String sqlEquipment = "CREATE TABLE if not exists measure_data("
				+ "id INTEGER PRIMARY KEY AUTOINCREMENT,function_location VARCHAR(200),description VARCHAR(200),"
				+ "data_item VARCHAR(100),measure_time DATETIME,data_type INT(3)," //data_type=1电阻校准数据，2电感校准数据，3绝缘电阻校准数据
				+ "data_01 double(30),data_02 double(30),data_03 double(30),data_04 double(30),data_05 double(30),"
				+ "data_06 double(30),data_07 double(30),data_08 double(30),data_09 double(30),data_10 double(30),"
				+ "data_11 double(30),data_12 double(30),data_13 double(30),data_14 double(30),data_15 double(30),"
				+ "data_16 double(30),data_17 double(30),data_18 double(30),data_19 double(30),data_20 double(30)"
				+ ")";
		statement.executeUpdate(sqlEquipment);
//        statement.executeUpdate("INSERT INTO measure_data VALUES (1, '第一电厂-设备管理部-电机1',  '第一电厂-设备管理部-电机1', '2022-03-22 15:16:18', '2022-03-22 15:16:18',1,"
//        		+ "0.05, 0.82, 3, 80, 130, 180, 402, 502, 809, 1103, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)");//向数据库中插入数据
//        ResultSet rSet=statement.executeQuery("select * from measure_data");//搜索数据库，将搜索的放入数据集ResultSet中
//        while (rSet.next()) {            //遍历这个数据集
//            log.info("function_location："+rSet.getString(2));//依次输出 也可以这样写 rSet.getString(“name”)
//            log.info("data_01："+rSet.getString(4));
//        }
//        rSet.close();//关闭数据集
		statement.close();
	}

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


	//设备结构数据库
//    String sqlEquipment = "CREATE TABLE if not exists tree_structure(id INTEGER PRIMARY KEY AUTOINCREMENT,function_location VARCHAR(200),description VARCHAR(200),sup_fl VARCHAR(200),level INT(3)," +
//            "isleaf INT(3))";
//    statement.executeUpdate("drop table if exists tree_structure");//判断是否有表tables的存在。有则删除
//    statement.executeUpdate(sqlEquipment);
//    statement.executeUpdate("INSERT INTO tree_structure VALUES (1, '第一电厂', '第一电厂', NULL, 1, 0)");//向数据库中插入数据
//    statement.executeUpdate("INSERT INTO tree_structure VALUES (2, '第一电厂-设备管理部', '第一电厂-设备管理部', '第一电厂', 2, 0)");//向数据库中插入数据
//    statement.executeUpdate("INSERT INTO tree_structure VALUES (3, '第一电厂-设备管理部-发电机组', '第一电厂-设备管理部-发电机组', '第一电厂-设备管理部', 3, 0)");//向数据库中插入数据
//    statement.executeUpdate("INSERT INTO tree_structure VALUES (4, '第一电厂-设备管理部-发电机组-电机1', '第一电厂-设备管理部-发电机组-电机1', '第一电厂-设备管理部-发电机组', 4, 1)");//向数据库中插入数据
//    statement.executeUpdate("INSERT INTO tree_structure VALUES (5, '第一电厂-设备管理部-发电机组-电机2', '第一电厂-设备管理部-发电机组-电机2', '第一电厂-设备管理部-发电机组', 4, 1)");//向数据库中插入数据
//    statement.executeUpdate("INSERT INTO tree_structure VALUES (6, '第二电厂', '第二电厂', NULL, 1, 0)");//向数据库中插入数据
//    statement.executeUpdate("INSERT INTO tree_structure VALUES (7, '第二电厂-设备管理部', '第二电厂-设备管理部', '第二电厂', 2, 0)");//向数据库中插入数据
//    statement.executeUpdate("INSERT INTO tree_structure VALUES (8, '第一电厂-设备管理部-发电机组-电机3', '第一电厂-设备管理部-发电机组-电机3', '第一电厂-设备管理部-发电机组', 4, 1)");//向数据库中插入数据
//    statement.executeUpdate("INSERT INTO tree_structure VALUES (10, '第一电厂-设备管理部-发电机组-电机4', '第一电厂-设备管理部-发电机组-电机4', '第一电厂-设备管理部-发电机组', 4, 1)");//向数据库中插入数据
//    statement.executeUpdate("INSERT INTO tree_structure VALUES (11, '第一电厂-设备管理部-发电机组-电机5', '第一电厂-设备管理部-发电机组-电机5', '第一电厂-设备管理部-发电机组', 4, 1)");//向数据库中插入数据
//    statement.executeUpdate("INSERT INTO tree_structure VALUES (12, '第一电厂-设备管理部-发电机组-电机8', '第一电厂-设备管理部-发电机组-电机8', '第一电厂-设备管理部-发电机组', 4, 1)");//向数据库中插入数据
//    statement.executeUpdate("INSERT INTO tree_structure VALUES (13, '第一电厂-设备管理部-发电机组-电机6', '第一电厂-设备管理部-发电机组-电机6', '第一电厂-设备管理部-发电机组', 4, 1)");//向数据库中插入数据
//    statement.executeUpdate("INSERT INTO tree_structure VALUES (14, '第一电厂-设备管理部-发电机组-电机7', '第一电厂-设备管理部-发电机组-电机7', '第一电厂-设备管理部-发电机组', 4, 1)");//向数据库中插入数据
//    statement.executeUpdate("INSERT INTO tree_structure VALUES (15, '第一电厂-设备管理部-发电机组-电机9', '第一电厂-设备管理部-发电机组-电机9', '第一电厂-设备管理部-发电机组', 4, 1)");//向数据库中插入数据
//    statement.executeUpdate("INSERT INTO tree_structure VALUES (16, '第一电厂-设备管理部-发电机组-电机10', '第一电厂-设备管理部-发电机组-电机10', '第一电厂-设备管理部-发电机组', 4, 1)");//向数据库中插入数据
//    ResultSet rSet=statement.executeQuery("select * from tree_structure");//搜索数据库，将搜索的放入数据集ResultSet中

//    /**
//    * 通过用户名到数据库中获取凭证密码
//    * @param userName
//    * @return
//    */
//    private static String getPasswordByUserName(String userName) {
//    //SQL语句
//    String sql = "select password from users where username = " +"'" + userName+"'";
//        Connection conn = JdbcUtils.getConn();
//        Statement stmt=null;
//        ResultSet ret = null;
//        String password=null;
//    try {
//    stmt = conn.createStatement();
//    //执行语句，得到结果集
//    ret = stmt.executeQuery(sql);
//                while (ret.next()) {
//                //这里只查询的密码
//                password = ret.getString(1);
//                }
//                ret.close();
//                conn.close();//关闭连接
//    } catch (SQLException e1) {
//    e1.printStackTrace();
//    }
//    return password;
//    }
}