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

package com.mes.util;

import com.mes.ui.ProdReq;
import com.mes.ui.RivetParamRange;

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 Connection getConn(){
        try {
            // 复用已打开连接，避免每次新建导致多连接争用、保存失败却仍提示成功
            if (conn != null && !conn.isClosed()) {
                return conn;
            }
            Class.forName(Drivde);// 加载驱动,连接sqlite的jdbc
            conn = DriverManager.getConnection("jdbc:sqlite:mes_db.db");//连接数据库zhou.db,不存在则创建
            // 忙碌时等待，降低与定时任务并发写库被锁失败的概率
            try (Statement busyStmt = conn.createStatement()) {
                busyStmt.execute("PRAGMA busy_timeout = 5000");
            }
            System.out.println("连接到SQLite数据库成功！");
            create_bw_record();//初始化结构表
            create_bw_prod();
            create_config_table();
            create_rivet_param_table();
        } catch (Exception e) {
            // TODO Auto-generated catch block
        	close();//关闭数据库连接
            e.printStackTrace();
        }
        return conn;
    }
    
    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("表record创建成功！");
        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 create_bw_prod() throws SQLException {
		Statement statement=conn.createStatement();   //创建连接对象，是Java的一个操作数据库的重要接口
		//设备结构数据库
		String sqlEquipment = "CREATE TABLE if not exists bw_prod("
				+ "id INTEGER PRIMARY KEY AUTOINCREMENT,gw VARCHAR(20),line_sn VARCHAR(20),type VARCHAR(20),sn VARCHAR(48),"
				+ "fout VARCHAR(20),sout VARCHAR(20),fmin VARCHAR(20),smin VARCHAR(20),fmax VARCHAR(20),smax VARCHAR(20),"
				+ "qty VARCHAR(5),serial_number VARCHAR(50),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 void create_config_table() throws SQLException {
		Statement statement = conn.createStatement();
		String sql = "CREATE TABLE if not exists bw_config("
				+ "id INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ "a_set_num SHORT, "
				+ "b_set_num SHORT, "
				+ "plc_ip_a VARCHAR(20), "
				+ "plc_ip_b VARCHAR(20))";
		statement.executeUpdate(sql);
		
		// 检查是否已有数据，若无则插入默认值
		ResultSet rs = statement.executeQuery("SELECT count(*) FROM bw_config");
		if (rs.next() && rs.getInt(1) == 0) {
			statement.executeUpdate("INSERT INTO bw_config (a_set_num, b_set_num, plc_ip_a, plc_ip_b) "
					+ "VALUES (41, 14, '192.168.1.7', '192.168.1.8')");
		}
		rs.close();
		System.out.println("表config创建并初始化成功！");
		statement.close();
	}

	public static java.util.Map<String, Object> getConfig() {
		java.util.Map<String, Object> config = new java.util.HashMap<>();
		String sql = "SELECT a_set_num, b_set_num, plc_ip_a, plc_ip_b FROM bw_config LIMIT 1";
		Connection conn = JdbcUtils.getConn();
		try (Statement stmt = conn.createStatement();
			 ResultSet rs = stmt.executeQuery(sql)) {
			if (rs.next()) {
				config.put("a_set_num", rs.getShort("a_set_num"));
				config.put("b_set_num", rs.getShort("b_set_num"));
				config.put("plc_ip_a", rs.getString("plc_ip_a"));
				config.put("plc_ip_b", rs.getString("plc_ip_b"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return config;
	}

	// 铆接参数区间配置表
	public static void create_rivet_param_table() throws SQLException {
		Statement statement = conn.createStatement();
		String sql = "CREATE TABLE if not exists bw_rivet_param("
				+ "id INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ "gun_type VARCHAR(1), "
				+ "start_rivet INTEGER, "
				+ "end_rivet INTEGER, "
				+ "f_min INTEGER, "
				+ "f_max INTEGER, "
				+ "s_min INTEGER, "
				+ "s_max INTEGER, "
				+ "sort_order INTEGER)";
		statement.executeUpdate(sql);

		ResultSet rs = statement.executeQuery("SELECT count(*) FROM bw_rivet_param");
		if (rs.next() && rs.getInt(1) == 0) {
			statement.executeUpdate("INSERT INTO bw_rivet_param (gun_type,start_rivet,end_rivet,f_min,f_max,s_min,s_max,sort_order) VALUES "
					+ "('A',1,41,10000,12000,3500,5500,1),"
					+ "('B',1,14,11000,13000,3500,5500,1)");
		}
		rs.close();
		System.out.println("表rivet_param创建并初始化成功！");
		statement.close();
	}

	public static java.util.List<RivetParamRange> getRivetParams(String gunType) {
		java.util.List<RivetParamRange> list = new java.util.ArrayList<>();
		String sql = "SELECT gun_type,start_rivet,end_rivet,f_min,f_max,s_min,s_max FROM bw_rivet_param "
				+ "WHERE gun_type = ? ORDER BY sort_order ASC, start_rivet ASC";
		Connection conn = JdbcUtils.getConn();
		try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
			pstmt.setString(1, gunType);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				RivetParamRange range = new RivetParamRange();
				range.setGunType(rs.getString("gun_type"));
				range.setStartRivet(rs.getInt("start_rivet"));
				range.setEndRivet(rs.getInt("end_rivet"));
				range.setFMin(rs.getInt("f_min"));
				range.setFMax(rs.getInt("f_max"));
				range.setSMin(rs.getInt("s_min"));
				range.setSMax(rs.getInt("s_max"));
				list.add(range);
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public static synchronized boolean saveRivetParams(String gunType, java.util.List<RivetParamRange> ranges) {
		Connection conn = JdbcUtils.getConn();
		if (conn == null) {
			System.out.println("保存铆接参数失败：数据库未连接, gunType=" + gunType);
			return false;
		}
		try {
			boolean oldAutoCommit = conn.getAutoCommit();
			conn.setAutoCommit(false);
			try {
				PreparedStatement deleteStmt = conn.prepareStatement("DELETE FROM bw_rivet_param WHERE gun_type = ?");
				deleteStmt.setString(1, gunType);
				deleteStmt.executeUpdate();
				deleteStmt.close();

				String insertSql = "INSERT INTO bw_rivet_param (gun_type,start_rivet,end_rivet,f_min,f_max,s_min,s_max,sort_order) "
						+ "VALUES (?,?,?,?,?,?,?,?)";
				PreparedStatement insertStmt = conn.prepareStatement(insertSql);
				for (int i = 0; i < ranges.size(); i++) {
					RivetParamRange range = ranges.get(i);
					insertStmt.setString(1, gunType);
					insertStmt.setInt(2, range.getStartRivet());
					insertStmt.setInt(3, range.getEndRivet());
					insertStmt.setInt(4, range.getFMin());
					insertStmt.setInt(5, range.getFMax());
					insertStmt.setInt(6, range.getSMin());
					insertStmt.setInt(7, range.getSMax());
					insertStmt.setInt(8, i + 1);
					insertStmt.executeUpdate();
				}
				insertStmt.close();
				conn.commit();
				System.out.println("保存铆接参数成功：gunType=" + gunType + ", rows=" + ranges.size());
				return true;
			} catch (SQLException e) {
				try {
					conn.rollback();
				} catch (SQLException rollbackEx) {
					rollbackEx.printStackTrace();
				}
				throw e;
			} finally {
				conn.setAutoCommit(oldAutoCommit);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("保存铆接参数失败：gunType=" + gunType + ", err=" + e.getMessage());
			return false;
		}
	}

	public static synchronized boolean updateConfig(short aSetNum, short bSetNum, String plcIpA, String plcIpB) {
		String sql = "UPDATE bw_config SET a_set_num = ?, b_set_num = ?, plc_ip_a = ?, plc_ip_b = ? WHERE id = 1";
		Connection conn = JdbcUtils.getConn();
		if (conn == null) {
			System.out.println("更新本地配置失败：数据库未连接");
			return false;
		}
		try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
			pstmt.setShort(1, aSetNum);
			pstmt.setShort(2, bSetNum);
			pstmt.setString(3, plcIpA);
			pstmt.setString(4, plcIpB);
			int rows = pstmt.executeUpdate();
			if (rows <= 0) {
				System.out.println("更新本地配置失败：未更新到任何行");
				return false;
			}
			System.out.println("更新本地配置成功：aSetNum=" + aSetNum + ", bSetNum=" + bSetNum + ", IP_A=" + plcIpA + ", IP_B=" + plcIpB);
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("更新本地配置失败：" + e.getMessage());
			return false;
		}
	}

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

	//插入拉铆数据
	public static boolean insertProdData(String gw, String lineSn, String sn, String type, String fout, String sout, String fmin, String smin, String fmax, String smax,String qty,String serial_number,String ucode) {
		boolean ret = false;
		String record_time = DateLocalUtils.getCurrentTime();
		JdbcUtils.getConn();

		if(conn==null) {
			ret = false;
		}else {
			try {
				//创建连接对象，是Java的一个操作数据库的重要接口
				Statement statement=conn.createStatement();
				statement.executeUpdate("INSERT INTO bw_prod (gw,line_sn,type,sn,fout,sout,fmin,smin,fmax,smax,qty,serial_number,ucode,record_time) VALUES"
						+ " ('"+gw+"', '"+lineSn+"', '"+type+"', '"+sn+"', '"+fout+"', '"+sout+"', '"+fmin+"', '"+smin+"', '"+fmax+"', '"+smax+"', '"+qty+"','"+serial_number+"','"+ucode+"','"+record_time+"')");//向数据库中插入数据
				statement.close();
				ret = true;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				ret = false;
			}
		}

		return ret;
	}

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

	public static List<ProdReq> getProds() {
		//SQL语句
		String sql = "select id,gw,line_sn,type,sn,fout,sout,fmin,smin,fmax,smax,qty,serial_number,sync,record_time,ucode from bw_prod where sync = 0 order by id asc limit 100";
		Connection conn = JdbcUtils.getConn();
		Statement stmt=null;
		ResultSet ret = null;
		String password=null;
		List<ProdReq> prods = new ArrayList<>();
		try {
			stmt = conn.createStatement();
			//执行语句，得到结果集
			ret = stmt.executeQuery(sql);
			System.out.println("sql:"+ sql);
			while (ret.next()) {
				ProdReq prodReq = new ProdReq();
				prodReq.setId(ret.getInt(1));
				prodReq.setGw(ret.getString(2));
				prodReq.setLineSn(ret.getString(3));
				prodReq.setType(ret.getString(4));
				prodReq.setSn(ret.getString(5));
				prodReq.setFout(ret.getString(6));
				prodReq.setSout(ret.getString(7));
				prodReq.setFmin(ret.getString(8));
				prodReq.setSmin(ret.getString(9));
				prodReq.setFmax(ret.getString(10));
				prodReq.setSmax(ret.getString(11));
				prodReq.setQty(ret.getString(12));
				prodReq.setSerialNumber(ret.getString(13));
				prodReq.setSync(ret.getInt(14));
				prodReq.setRecordTime(ret.getString(15));
				prodReq.setUcode(ret.getString(16));
				prods.add(prodReq);
			}
			ret.close();
			// 共享连接不要在这里关闭，否则后续保存配置会写失败
			if (stmt != null) {
				stmt.close();
			}
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		return prods;
	}

}