package com.watersystem.monitor;
|
|
import java.security.interfaces.RSAKey;
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.Date;
|
import java.util.List;
|
|
import com.base.Com;
|
import com.modbus.data.MyModbusMaster;
|
import com.sql.MysqlConnPool;
|
import com.sql.Sql_Mysql;
|
|
public class Water_Task_SQL {
|
|
/**
|
* ²éѯËùÓеÄË®Àä¼Ç¼
|
* @param pool
|
* @param electric
|
*/
|
public static void queryAllWater(MysqlConnPool pool,List<Water_inf> winfs) {
|
String sql_str = " select * from " + Sql_Mysql.Tb_water_inf+ " order by water_id;";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str);
|
while(res.next()) {
|
Water_inf winf=new Water_inf();
|
winf.setWater_id(res.getInt("water_id"));
|
winf.setWater_name(res.getString("water_name"));
|
winf.setWater_ip(res.getString("water_ip"));
|
winf.setWater_type(res.getInt("water_type"));
|
|
winf.wrt = new Water_rt(winf.water_id);
|
winf.wbrach = new Water_brach(winf.water_id);
|
winf.wstate = new Water_state(winf.water_id);
|
winf.walarm = new Water_alarm(winf.water_id);
|
winfs.add(winf);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(res != null) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
/**
|
* ´´½¨Ë®ÀäϵͳÊý¾Ý¿â
|
* @param pool
|
*/
|
public static void createDB_WaterSystem(MysqlConnPool pool) {
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + Sql_Mysql.DB_Watersystem);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
/**
|
* ´´½¨Ë®ÀäÐÅÏ¢±í
|
* @param pool
|
*/
|
public static void createTb_water_inf(MysqlConnPool pool) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS "+Sql_Mysql.Tb_water_inf+" (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Ö÷¼ü'," +
|
" water_id int(11) NOT NULL DEFAULT '5001' COMMENT 'Ë®Àäϵͳid'," +
|
" water_name varchar(255) NOT NULL DEFAULT '' COMMENT 'Ë®ÀäϵͳÃû³Æ'," +
|
" water_type int(11) NOT NULL DEFAULT '0' COMMENT 'Ë®ÀäϵͳÀàÐÍ(±¸ÓÃ)'," +
|
" water_ip varchar(255) NOT NULL DEFAULT '127.0.0.1' COMMENT 'Ë®Àäϵͳip(ÖØÒª)'," +
|
" note varchar(255) NOT NULL DEFAULT ''," +
|
" PRIMARY KEY (num)," +
|
" KEY water_id_key (water_id)" +
|
") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ´´½¨Ë®ÀäϵͳʵʱÐÅÏ¢±í
|
* @param pool
|
*/
|
public static void createTb_water_rt(MysqlConnPool pool) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_water_rt + " (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Ö÷¼ü'," +
|
" water_id int(11) NOT NULL DEFAULT '5001' COMMENT 'Ë®Àäϵͳid'," +
|
" cooling_flow float NOT NULL DEFAULT '0' COMMENT 'ÀäÈ´Ë®Á÷Á¿'," +
|
" supply_tmp float NOT NULL DEFAULT '0' COMMENT '¹©Ë®Î¶È'," +
|
" Supply_pre float NOT NULL DEFAULT '0' COMMENT '¹©Ë®Ñ¹Á¦'," +
|
" back_tem float NOT NULL DEFAULT '0' COMMENT '»ØˮζÈ'," +
|
" back_pre float NOT NULL DEFAULT '0' COMMENT '»ØˮѹÁ¦'," +
|
" Cooling_ser float NOT NULL DEFAULT '0' COMMENT 'ÀäÈ´Ë®µçµ¼ÂÊ'," +
|
" Buffer_tank_level float NOT NULL DEFAULT '0' COMMENT '»º³å¹ÞҺλ'," +
|
" Buffer_tank_pre float NOT NULL DEFAULT '0' COMMENT '»º³å¹ÞѹÁ¦'," +
|
" Humidity_chamber float NOT NULL DEFAULT '0' COMMENT '·§Ìüʪ¶È'," +
|
" Tem_chamber float NOT NULL DEFAULT '0' COMMENT '·§ÌüζÈ'," +
|
" External_tmp float NOT NULL DEFAULT '0' COMMENT 'ÍâˮζÈ'," +
|
" External_pre float NOT NULL DEFAULT '0' COMMENT 'ÍâˮѹÁ¦'," +
|
" note varchar(255) NOT NULL DEFAULT ''," +
|
" PRIMARY KEY (num)," +
|
" UNIQUE KEY water_id_key (water_id) USING BTREE " +
|
") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
/**
|
* ´´½¨Ë®Àäϵͳ·ÖÖ§ÐÅÏ¢±í
|
* @param pool
|
*/
|
public static void createTb_water_brach(MysqlConnPool pool) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_water_brach + " (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Ö÷¼ü'," +
|
" water_id int(11) NOT NULL DEFAULT '5001' COMMENT 'Ë®Àäϵͳid'," +
|
" Branch_flow_1 float NOT NULL DEFAULT '0' COMMENT '1#֧·Á÷Á¿'," +
|
" Branch_flow_2 float NOT NULL DEFAULT '0' COMMENT '2#֧·Á÷Á¿'," +
|
" Branch_flow_3 float NOT NULL DEFAULT '0' COMMENT '3#֧·Á÷Á¿'," +
|
" Branch_flow_4 float NOT NULL DEFAULT '0' COMMENT '4#֧·Á÷Á¿'," +
|
" Branch_flow_5 float NOT NULL DEFAULT '0' COMMENT '5#֧·Á÷Á¿'," +
|
" Branch_flow_6 float NOT NULL DEFAULT '0' COMMENT '6#֧·Á÷Á¿'," +
|
" Branch_flow_7 float NOT NULL DEFAULT '0' COMMENT '7#֧·Á÷Á¿'," +
|
" Branch_flow_8 float NOT NULL DEFAULT '0' COMMENT '8#֧·Á÷Á¿'," +
|
" Branch_flow_9 float NOT NULL DEFAULT '0' COMMENT '9#֧·Á÷Á¿'," +
|
" " +
|
" Branch_tmp_1 float NOT NULL DEFAULT '0' COMMENT '1#֧·Î¶È'," +
|
" Branch_tmp_2 float NOT NULL DEFAULT '0' COMMENT '2#֧·Î¶È'," +
|
" Branch_tmp_3 float NOT NULL DEFAULT '0' COMMENT '3#֧·Î¶È'," +
|
" Branch_tmp_4 float NOT NULL DEFAULT '0' COMMENT '4#֧·Î¶È'," +
|
" Branch_tmp_5 float NOT NULL DEFAULT '0' COMMENT '5#֧·Î¶È'," +
|
" Branch_tmp_6 float NOT NULL DEFAULT '0' COMMENT '6#֧·Î¶È'," +
|
" Branch_tmp_7 float NOT NULL DEFAULT '0' COMMENT '7#֧·Î¶È'," +
|
" Branch_tmp_8 float NOT NULL DEFAULT '0' COMMENT '8#֧·Î¶È'," +
|
" Branch_tmp_9 float NOT NULL DEFAULT '0' COMMENT '9#֧·Î¶È'," +
|
" " +
|
" Branch_pre_1 float NOT NULL DEFAULT '0' COMMENT '1#֧·ѹÁ¦'," +
|
" Branch_pre_2 float NOT NULL DEFAULT '0' COMMENT '2#֧·ѹÁ¦'," +
|
" Branch_pre_3 float NOT NULL DEFAULT '0' COMMENT '3#֧·ѹÁ¦'," +
|
" Branch_pre_4 float NOT NULL DEFAULT '0' COMMENT '4#֧·ѹÁ¦'," +
|
" Branch_pre_5 float NOT NULL DEFAULT '0' COMMENT '5#֧·ѹÁ¦'," +
|
" Branch_pre_6 float NOT NULL DEFAULT '0' COMMENT '6#֧·ѹÁ¦'," +
|
" Branch_pre_7 float NOT NULL DEFAULT '0' COMMENT '7#֧·ѹÁ¦'," +
|
" Branch_pre_8 float NOT NULL DEFAULT '0' COMMENT '8#֧·ѹÁ¦'," +
|
" Branch_pre_9 float NOT NULL DEFAULT '0' COMMENT '9#֧·ѹÁ¦'," +
|
" " +
|
" note varchar(255) NOT NULL DEFAULT ''," +
|
" PRIMARY KEY (num)," +
|
" UNIQUE KEY water_id_key (water_id) USING BTREE " +
|
") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
/**
|
* ´´½¨Ë®Àäϵͳ״̬±í
|
* @param pool
|
*/
|
public static void createTb_water_state(MysqlConnPool pool) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_water_state + " (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Ö÷¼ü'," +
|
" water_id int(11) NOT NULL DEFAULT '5001' COMMENT 'Ë®Àäϵͳid'," +
|
" P01_main_pump int(11) NOT NULL DEFAULT '0' COMMENT 'P01Ö÷±ÃÔËÐÐ'," +
|
" P11_replenish_pump int(11) NOT NULL DEFAULT '0' COMMENT 'P11²¹Ë®±ÃÔËÐÐ'," +
|
" P12_pretest_pump int(11) NOT NULL DEFAULT '0' COMMENT 'P12ÊÔѹ±ÃÔËÐÐ'," +
|
" P13_replenish_pump int(11) NOT NULL DEFAULT '0' COMMENT 'P13²¹Æø±ÃÔËÐÐ'," +
|
" H01_electric_heater int(11) NOT NULL DEFAULT '0' COMMENT 'H01µç¼ÓÈÈÆ÷ÔËÐÐ'," +
|
" H02_electric_heater int(11) NOT NULL DEFAULT '0' COMMENT 'H02µç¼ÓÈÈÆ÷ÔËÐÐ'," +
|
" Solenoid_openflag int(11) NOT NULL DEFAULT '0' COMMENT 'µç´Å·§¿ªÏÞλ'," +
|
" Solenoid_shutflag int(11) NOT NULL DEFAULT '0' COMMENT 'µç´Å·§¹ØÏÞλ'," +
|
" valve_start int(11) NOT NULL DEFAULT '0' COMMENT '·§´ò¿ª'," +
|
" Exhaust_start int(11) NOT NULL DEFAULT '0' COMMENT 'ÅÅÆø·§´ò¿ª'," +
|
" remote_start int(11) NOT NULL DEFAULT '0' COMMENT 'Ô¶³ÌÆô¶¯Ë®Àäϵͳ'," +
|
" remote_stop int(11) NOT NULL DEFAULT '0' COMMENT 'Ô¶³ÌֹͣˮÀäϵͳ'," +
|
"" +
|
" Operation_ooled_devices int(11) NOT NULL DEFAULT '0' COMMENT '±»ÀäÈ´Æ÷¼þͶÔË'," +
|
" Auto_control_state int(11) NOT NULL DEFAULT '0' COMMENT 'Ë®Àäϵͳ×Ô¶¯¿ØÖÆ״̬'," +
|
" Manual_control_state int(11) NOT NULL DEFAULT '0' COMMENT 'Ë®ÀäϵͳÊÖ¶¯¿ØÖÆ״̬'," +
|
" remote_control_state int(11) NOT NULL DEFAULT '0' COMMENT 'Ë®ÀäϵͳԶ³Ì¿ØÖÆ״̬'," +
|
" Water_system_function int(11) NOT NULL DEFAULT '0' COMMENT 'Ë®ÀäϵͳÔËÐÐ'," +
|
" note varchar(255) NOT NULL DEFAULT ''," +
|
" PRIMARY KEY (num)," +
|
" UNIQUE KEY water_id_key (water_id) USING BTREE" +
|
") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
//´´½¨Ë®Àä¹ÊÕϱí
|
public static void createTb_water_alarm(MysqlConnPool pool) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_water_alarm + " (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Ö÷¼ü'," +
|
" water_id int(11) NOT NULL DEFAULT '5001' COMMENT 'É豸id'," +
|
" alarm_id int(11) NOT NULL DEFAULT '12930' COMMENT '¸æ¾¯id'," +
|
" alarm_starttime datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '¸æ¾¯¿ªÊ¼Ê±¼ä'," +
|
" alarm_endtime datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '¸æ¾¯½áÊøʱ¼ä'," +
|
" alarm_confirm int(11) NOT NULL DEFAULT '0' COMMENT '¸æ¾¯È·ÈÏ'," +
|
" alarm_confirmtime datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '¸æ¾¯È·ÈÏʱ¼ä'," +
|
" note varchar(255) NOT NULL DEFAULT ''," +
|
" PRIMARY KEY (num),"+
|
" index water_id_key(water_id) " +
|
") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ³õʼ»¯Êý¾Ý¿â±í
|
* @param pool
|
*/
|
public static void init(MysqlConnPool pool) {
|
createDB_WaterSystem(pool);
|
|
createTb_water_inf(pool);
|
|
createTb_water_rt(pool);
|
|
createTb_water_brach(pool);
|
|
createTb_water_state(pool);
|
|
createTb_water_alarm(pool);
|
}
|
|
//²åÈë»ò¸üÐÂË®ÀäϵͳʵʱÐÅÏ¢±í
|
public static void replace_water_rt(MysqlConnPool pool,Water_rt wrt) {
|
String sql_str = " replace into "+ Sql_Mysql.Tb_water_rt+"(water_id,cooling_flow,supply_tmp,Supply_pre,back_tem,back_pre,Cooling_ser,Buffer_tank_level,Buffer_tank_pre,Humidity_chamber,Tem_chamber,External_tmp,External_pre) "
|
+ " values("+wrt.water_id+","+wrt.cooling_flow+","+wrt.supply_tmp+","+wrt.Supply_pre+","+wrt.back_tem+","+wrt.back_pre+","+wrt.Cooling_ser+","+wrt.Buffer_tank_level+","+wrt.Buffer_tank_pre+","+wrt.Humidity_chamber+","+wrt.Tem_chamber+","+wrt.External_tmp+","+wrt.External_pre+")";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ¸üÐÂwater_rt±í
|
* @param pool
|
* @param wrt
|
*/
|
public static void update_water_rt(MysqlConnPool pool,Water_rt wrt) {
|
String sql_str = " update " + Sql_Mysql.Tb_water_rt +" set "
|
+ "water_id=" + wrt.water_id
|
+ ",cooling_flow=" +wrt.cooling_flow
|
+ ",supply_tmp=" + wrt.supply_tmp
|
+ ",Supply_pre=" + wrt.Supply_pre
|
+ ",back_tem=" + wrt.back_tem
|
+ ",back_pre=" + wrt.back_pre
|
+ ",Cooling_ser=" + wrt.Cooling_ser
|
+ ",Buffer_tank_level=" + wrt.Buffer_tank_level
|
+ ",Buffer_tank_pre=" + wrt.Buffer_tank_pre
|
+ ",Humidity_chamber=" + wrt.Humidity_chamber
|
+ ",Tem_chamber=" + wrt.Tem_chamber
|
+ ",External_tmp=" + wrt.External_tmp
|
+ ",External_pre=" + wrt.External_pre
|
+ " where water_id= " + wrt.water_id;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
|
|
//²åÈë»ò¸üÐÂË®Àäϵͳʵʱ·ÖÖ§ÐÅÏ¢±í
|
public static void replace_water_brach(MysqlConnPool pool,Water_brach wbrach) {
|
String sql_str = " replace into "+ Sql_Mysql.Tb_water_brach+"(water_id,Branch_flow_1,Branch_flow_2,Branch_flow_3,Branch_flow_4,Branch_flow_5,Branch_flow_6,Branch_flow_7,Branch_flow_8,Branch_flow_9,Branch_tmp_1,Branch_tmp_2,Branch_tmp_3,Branch_tmp_4,Branch_tmp_5,Branch_tmp_6,Branch_tmp_7,Branch_tmp_8,Branch_tmp_9,Branch_pre_1,Branch_pre_2,Branch_pre_3,Branch_pre_4,Branch_pre_5,Branch_pre_6,Branch_pre_7,Branch_pre_8,Branch_pre_9) "
|
+ " values("+wbrach.water_id+","+wbrach.Branch_flow_1+","+wbrach.Branch_flow_2+","+wbrach.Branch_flow_3+","+wbrach.Branch_flow_4+","+wbrach.Branch_flow_5+","+wbrach.Branch_flow_6+","+wbrach.Branch_flow_7+","+wbrach.Branch_flow_8+","+wbrach.Branch_flow_9
|
+","+wbrach.Branch_tmp_1+","+wbrach.Branch_tmp_2+","+wbrach.Branch_tmp_3+","+wbrach.Branch_tmp_4+","+wbrach.Branch_tmp_5+","+wbrach.Branch_tmp_6+","+wbrach.Branch_tmp_7+","+wbrach.Branch_tmp_8+","+wbrach.Branch_tmp_9
|
+","+wbrach.Branch_pre_1+","+wbrach.Branch_pre_2+","+wbrach.Branch_pre_3+","+wbrach.Branch_pre_4+","+wbrach.Branch_pre_5+","+wbrach.Branch_pre_6+","+wbrach.Branch_pre_7+","+wbrach.Branch_pre_8+","+wbrach.Branch_pre_9+")";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ¸üÐÂË®Àäϵͳʵʱ·ÖÖ§ÐÅÏ¢±í
|
* @param pool
|
* @param wbrach
|
*/
|
public static void update_water_brach(MysqlConnPool pool,Water_brach wbrach) {
|
String sql_str = " update "+Sql_Mysql.Tb_water_brach +" "
|
+ " Set water_id=" + wbrach.water_id
|
+ ",Branch_flow_1=" + wbrach.Branch_flow_1
|
+ ",Branch_flow_2=" + wbrach.Branch_flow_2
|
+ ",Branch_flow_3=" + wbrach.Branch_flow_3
|
+ ",Branch_flow_4=" + wbrach.Branch_flow_4
|
+ ",Branch_flow_5=" + wbrach.Branch_flow_5
|
+ ",Branch_flow_6=" + wbrach.Branch_flow_6
|
+ ",Branch_flow_7=" + wbrach.Branch_flow_7
|
+ ",Branch_flow_8=" + wbrach.Branch_flow_8
|
+ ",Branch_flow_9=" + wbrach.Branch_flow_9
|
+ ",Branch_tmp_1=" + wbrach.Branch_tmp_1
|
+ ",Branch_tmp_2=" + wbrach.Branch_tmp_2
|
+ ",Branch_tmp_3=" + wbrach.Branch_tmp_3
|
+ ",Branch_tmp_4=" + wbrach.Branch_tmp_4
|
+ ",Branch_tmp_5=" + wbrach.Branch_tmp_5
|
+ ",Branch_tmp_6=" + wbrach.Branch_tmp_6
|
+ ",Branch_tmp_7=" + wbrach.Branch_tmp_7
|
+ ",Branch_tmp_8=" + wbrach.Branch_tmp_8
|
+ ",Branch_tmp_9=" + wbrach.Branch_tmp_9
|
+ ",Branch_pre_1=" + wbrach.Branch_pre_1
|
+ ",Branch_pre_2=" + wbrach.Branch_pre_2
|
+ ",Branch_pre_3=" + wbrach.Branch_pre_3
|
+ ",Branch_pre_4=" + wbrach.Branch_pre_4
|
+ ",Branch_pre_5=" + wbrach.Branch_pre_5
|
+ ",Branch_pre_6=" + wbrach.Branch_pre_6
|
+ ",Branch_pre_7=" + wbrach.Branch_pre_7
|
+ ",Branch_pre_8=" + wbrach.Branch_pre_8
|
+ ",Branch_pre_9=" + wbrach.Branch_pre_9
|
+ " Where water_id = " + wbrach.water_id;
|
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
//System.out.println(sql_str);
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
|
|
//²åÈë»ò¸üÐÂË®ÀäϵͳʵʱÐÅÏ¢±í
|
public static void replace_water_state(MysqlConnPool pool,Water_state wstate) {
|
String sql_str = " replace into "+ Sql_Mysql.Tb_water_state+"(water_id,P01_main_pump,P11_replenish_pump,P12_pretest_pump,P13_replenish_pump,H01_electric_heater,H02_electric_heater,Solenoid_openflag,Solenoid_shutflag,valve_start,Exhaust_start,remote_start,remote_stop,Operation_ooled_devices,Auto_control_state,Manual_control_state,remote_control_state,Water_system_function) "
|
+ " values("+wstate.water_id+","+wstate.P01_main_pump+","+wstate.P11_replenish_pump+","+wstate.P12_pretest_pump+","+wstate.P13_replenish_pump+","+wstate.H01_electric_heater+","+wstate.H02_electric_heater+","+wstate.Solenoid_openflag+","+wstate.Solenoid_shutflag+","+wstate.valve_start+","+wstate.Exhaust_start+","+wstate.remote_start+","+wstate.remote_stop
|
+","+wstate.Operation_ooled_devices+","+wstate.Auto_control_state+","+wstate.Manual_control_state+","+wstate.remote_control_state+","+wstate.Water_system_function+")";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ²åÈë»ò¸üÐÂË®ÀäϵͳʵʱÐÅÏ¢±í
|
* @param pool
|
* @param wstate
|
*/
|
public static void update_water_state(MysqlConnPool pool,Water_state wstate) {
|
String sql_str = " update " + Sql_Mysql.Tb_water_state + " "
|
+ " Set water_id=" + wstate.water_id
|
+ ",P01_main_pump=" + wstate.P01_main_pump
|
+ ",P11_replenish_pump=" + wstate.P11_replenish_pump
|
+ ",P12_pretest_pump=" + wstate.P12_pretest_pump
|
+ ",P13_replenish_pump=" + wstate.P13_replenish_pump
|
+ ",H01_electric_heater=" + wstate.H01_electric_heater
|
+ ",H02_electric_heater=" + wstate.H02_electric_heater
|
+ ",Solenoid_openflag=" + wstate.Solenoid_openflag
|
+ ",Solenoid_shutflag=" + wstate.Solenoid_shutflag
|
+ ",valve_start=" + wstate.valve_start
|
+ ",Exhaust_start=" + wstate.Exhaust_start
|
+ ",remote_start=" + wstate.remote_start
|
+ ",remote_stop=" + wstate.remote_stop
|
+ ",Operation_ooled_devices=" + wstate.Operation_ooled_devices
|
+ ",Auto_control_state=" + wstate.Auto_control_state
|
+ ",Manual_control_state=" + wstate.Manual_control_state
|
+ ",remote_control_state=" + wstate.remote_control_state
|
+ ",Water_system_function=" + wstate.Water_system_function
|
+ " Where water_id = " + wstate.water_id;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
//System.out.println(sql_str);
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ²åÈë»ò¸üеçÔ´¹ÊÕÏ
|
* @param conn_pool
|
* @param control
|
*/
|
public static void insertOrUpdateWater_alarmTable(MysqlConnPool conn_pool,List<Water_alarm> list) {
|
if(list!=null&&list.size()>0) {
|
for (Water_alarm walarm : list) {
|
String sql_str_sel = " select * from " + Sql_Mysql.Tb_water_alarm + " where water_id = " + walarm.getWater_id()+" and alarm_id="+walarm.getAlarm_id();
|
String sql_str_ins = " INSERT INTO " + Sql_Mysql.Tb_water_alarm + "(water_id,alarm_id,alarm_starttime,alarm_endtime,alarm_confirm,alarm_confirmtime) "
|
+" VALUES("+walarm.water_id+","+walarm.alarm_id+",'"+Com.getDateTimeFormat(walarm.alarm_starttime, Com.DTF_YMDhms)+"','"+Com.getDateTimeFormat(walarm.alarm_endtime, Com.DTF_YMDhms)+"',"+walarm.alarm_confirm+",'"+Com.getDateTimeFormat(walarm.alarm_confirmtime, Com.DTF_YMDhms)+"');";
|
String sql_str_update= " update " + Sql_Mysql.Tb_water_alarm +
|
" set alarm_starttime='"+Com.getDateTimeFormat(walarm.alarm_starttime, Com.DTF_YMDhms)+
|
"',alarm_endtime='"+Com.getDateTimeFormat(walarm.alarm_endtime, Com.DTF_YMDhms)+
|
"',alarm_confirm="+walarm.alarm_confirm+
|
",alarm_confirmtime='"+Com.getDateTimeFormat(walarm.alarm_confirmtime, Com.DTF_YMDhms)+
|
"' where water_id="+walarm.water_id+
|
" and alarm_id="+walarm.alarm_id;
|
ResultSet res = null;
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
res = sql.sqlMysqlQuery(sql_str_sel);
|
if(res.next()) {
|
//¸üе±Ç°¿ØÖÆ״̬
|
sql.sqlMysqlExecute(sql_str_update);
|
}else {
|
sql.sqlMysqlExecute(sql_str_ins);
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
} finally {
|
if(res != null) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
}
|
|
}
|
|
/**
|
* ²åÈë»ò¸üеçÔ´¹ÊÕÏ
|
* @param conn_pool
|
* @param control
|
*/
|
public static void delClearWater_alarmTable(MysqlConnPool conn_pool,List<Water_alarm> list) {
|
if(list!=null&&list.size()>0) {
|
for (Water_alarm walarm : list) {
|
String sql_str = "DELETE FROM "+Sql_Mysql.Tb_water_alarm+" WHERE water_id = "+walarm.water_id+" AND alarm_id = "+walarm.alarm_id;
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (Exception e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
}
|
|
}
|
|
/**
|
* ²åÈë»ò¸üе±Ç°É豸µÄÁ¬½Ó״̬
|
* @param pool
|
* @param dev_id
|
* @param master
|
*/
|
public static void insertOrUpdateDeviceConnectState(MysqlConnPool pool,int dev_id,MyModbusMaster master) {
|
String sql_str_sel = " SELECT * FROM "+Sql_Mysql.Tb_Device_ConnectState+" WHERE dev_id = " + dev_id;
|
String sql_str_ins = " INSERT INTO "+Sql_Mysql.Tb_Device_ConnectState+"(dev_id,connect_en,record_time) values("+dev_id+","+master.getConnectState()+",'"+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)+"');";
|
String sql_str_upd = " UPDATE "+Sql_Mysql.Tb_Device_ConnectState+" SET connect_en = "+master.getConnectState()+",record_time='"+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)+"' WHERE dev_id = " + dev_id;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str_sel);
|
if(res.next()) {
|
sql.sqlMysqlExecute(sql_str_upd);
|
}else {
|
sql.sqlMysqlExecute(sql_str_ins);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(res != null) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
sql.close_con();
|
}
|
}
|
}
|
|
/**
|
* ´´½¨ÀúʷʵʱÊý¾Ý¼Ç¼±í
|
* @param pool
|
* @param water_id
|
* @param now
|
*/
|
public static void createTb_Water_realdata(MysqlConnPool pool, int water_id, Date now) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS "+Sql_Mysql.Tb_water_realdata+water_id+"_"+Com.getDateTimeFormat(now, Com.DTF_Y_M_D)+" (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Ö÷¼ü'," +
|
" water_id int(11) NOT NULL DEFAULT '5001' COMMENT 'Ë®Àäϵͳid'," +
|
" record_time datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '¼Ç¼ʱ¼ä'," +
|
" P01_main_pump int(11) NOT NULL DEFAULT '0' COMMENT 'P01Ö÷±ÃÔËÐÐ'," +
|
" P11_replenish_pump int(11) NOT NULL DEFAULT '0' COMMENT 'P11²¹Ë®±ÃÔËÐÐ'," +
|
" P12_pretest_pump int(11) NOT NULL DEFAULT '0' COMMENT 'P12ÊÔѹ±ÃÔËÐÐ'," +
|
" P13_replenish_pump int(11) NOT NULL DEFAULT '0' COMMENT 'P13²¹Æø±ÃÔËÐÐ'," +
|
" H01_electric_heater int(11) NOT NULL DEFAULT '0' COMMENT 'H01µç¼ÓÈÈÆ÷ÔËÐÐ'," +
|
" H02_electric_heater int(11) NOT NULL DEFAULT '0' COMMENT 'H02µç¼ÓÈÈÆ÷ÔËÐÐ'," +
|
" Solenoid_openflag int(11) NOT NULL DEFAULT '0' COMMENT 'µç´Å·§¿ªÏÞλ'," +
|
" Solenoid_shutflag int(11) NOT NULL DEFAULT '0' COMMENT 'µç´Å·§¹ØÏÞλ'," +
|
" valve_start int(11) NOT NULL DEFAULT '0' COMMENT '·§´ò¿ª'," +
|
" Exhaust_start int(11) NOT NULL DEFAULT '0' COMMENT 'ÅÅÆø·§´ò¿ª'," +
|
" remote_start int(11) NOT NULL DEFAULT '0' COMMENT 'Ô¶³ÌÆô¶¯Ë®Àäϵͳ'," +
|
" remote_stop int(11) NOT NULL DEFAULT '0' COMMENT 'Ô¶³ÌֹͣˮÀäϵͳ'," +
|
" Operation_ooled_devices int(11) NOT NULL DEFAULT '0' COMMENT '±»ÀäÈ´Æ÷¼þͶÔË'," +
|
" Auto_control_state int(11) NOT NULL DEFAULT '0' COMMENT 'Ë®Àäϵͳ×Ô¶¯¿ØÖÆ״̬'," +
|
" Manual_control_state int(11) NOT NULL DEFAULT '0' COMMENT 'Ë®ÀäϵͳÊÖ¶¯¿ØÖÆ״̬'," +
|
" remote_control_state int(11) NOT NULL DEFAULT '0' COMMENT 'Ë®ÀäϵͳԶ³Ì¿ØÖÆ״̬'," +
|
" Water_system_function int(11) NOT NULL DEFAULT '0' COMMENT 'Ë®ÀäϵͳÔËÐÐ'," +
|
" cooling_flow float NOT NULL DEFAULT '0' COMMENT 'ÀäÈ´Ë®Á÷Á¿'," +
|
" supply_tmp float NOT NULL DEFAULT '0' COMMENT '¹©Ë®Î¶È'," +
|
" Supply_pre float NOT NULL DEFAULT '0' COMMENT '¹©Ë®Ñ¹Á¦'," +
|
" back_tem float NOT NULL DEFAULT '0' COMMENT '»ØˮζÈ'," +
|
" back_pre float NOT NULL DEFAULT '0' COMMENT '»ØˮѹÁ¦'," +
|
" Cooling_ser float NOT NULL DEFAULT '0' COMMENT 'ÀäÈ´Ë®µçµ¼ÂÊ'," +
|
" Buffer_tank_level float NOT NULL DEFAULT '0' COMMENT '»º³å¹ÞҺλ'," +
|
" Buffer_tank_pre float NOT NULL DEFAULT '0' COMMENT '»º³å¹ÞѹÁ¦'," +
|
" Humidity_chamber float NOT NULL DEFAULT '0' COMMENT '·§Ìüʪ¶È'," +
|
" Tem_chamber float NOT NULL DEFAULT '0' COMMENT '·§ÌüζÈ'," +
|
" External_tmp float NOT NULL DEFAULT '0' COMMENT 'ÍâˮζÈ'," +
|
" External_pre float NOT NULL DEFAULT '0' COMMENT 'ÍâˮѹÁ¦'," +
|
" Branch_flow_1 float NOT NULL DEFAULT '0' COMMENT '1#֧·Á÷Á¿'," +
|
" Branch_flow_2 float NOT NULL DEFAULT '0' COMMENT '2#֧·Á÷Á¿'," +
|
" Branch_flow_3 float NOT NULL DEFAULT '0' COMMENT '3#֧·Á÷Á¿'," +
|
" Branch_flow_4 float NOT NULL DEFAULT '0' COMMENT '4#֧·Á÷Á¿'," +
|
" Branch_flow_5 float NOT NULL DEFAULT '0' COMMENT '5#֧·Á÷Á¿'," +
|
" Branch_flow_6 float NOT NULL DEFAULT '0' COMMENT '6#֧·Á÷Á¿'," +
|
" Branch_flow_7 float NOT NULL DEFAULT '0' COMMENT '7#֧·Á÷Á¿'," +
|
" Branch_flow_8 float NOT NULL DEFAULT '0' COMMENT '8#֧·Á÷Á¿'," +
|
" Branch_flow_9 float NOT NULL DEFAULT '0' COMMENT '9#֧·Á÷Á¿'," +
|
" Branch_tmp_1 float NOT NULL DEFAULT '0' COMMENT '1#֧·Î¶È'," +
|
" Branch_tmp_2 float NOT NULL DEFAULT '0' COMMENT '2#֧·Î¶È'," +
|
" Branch_tmp_3 float NOT NULL DEFAULT '0' COMMENT '3#֧·Î¶È'," +
|
" Branch_tmp_4 float NOT NULL DEFAULT '0' COMMENT '4#֧·Î¶È'," +
|
" Branch_tmp_5 float NOT NULL DEFAULT '0' COMMENT '5#֧·Î¶È'," +
|
" Branch_tmp_6 float NOT NULL DEFAULT '0' COMMENT '6#֧·Î¶È'," +
|
" Branch_tmp_7 float NOT NULL DEFAULT '0' COMMENT '7#֧·Î¶È'," +
|
" Branch_tmp_8 float NOT NULL DEFAULT '0' COMMENT '8#֧·Î¶È'," +
|
" Branch_tmp_9 float NOT NULL DEFAULT '0' COMMENT '9#֧·Î¶È'," +
|
" Branch_pre_1 float NOT NULL DEFAULT '0' COMMENT '1#֧·ѹÁ¦'," +
|
" Branch_pre_2 float NOT NULL DEFAULT '0' COMMENT '2#֧·ѹÁ¦'," +
|
" Branch_pre_3 float NOT NULL DEFAULT '0' COMMENT '3#֧·ѹÁ¦'," +
|
" Branch_pre_4 float NOT NULL DEFAULT '0' COMMENT '4#֧·ѹÁ¦'," +
|
" Branch_pre_5 float NOT NULL DEFAULT '0' COMMENT '5#֧·ѹÁ¦'," +
|
" Branch_pre_6 float NOT NULL DEFAULT '0' COMMENT '6#֧·ѹÁ¦'," +
|
" Branch_pre_7 float NOT NULL DEFAULT '0' COMMENT '7#֧·ѹÁ¦'," +
|
" Branch_pre_8 float NOT NULL DEFAULT '0' COMMENT '8#֧·ѹÁ¦'," +
|
" Branch_pre_9 float NOT NULL DEFAULT '0' COMMENT '9#֧·ѹÁ¦'," +
|
" note varchar(255) NOT NULL DEFAULT ''," +
|
" PRIMARY KEY (num)," +
|
" KEY water_id_key (water_id) USING BTREE," +
|
" KEY index_record_time (record_time) USING BTREE" +
|
") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
|
/**
|
* ɾ³ý´´½¨Ê±¼ä³¬Ê±µÄÀúʷʵʱ¼Ç¼±í
|
* @param conn_pool
|
* @param deldate
|
*/
|
public static void deleteHistoryData(MysqlConnPool conn_pool,Date deldate) {
|
String sql_select_strs = " select TABLE_NAME,UPDATE_TIME,CREATE_TIME " +
|
" from information_schema.tables " +
|
" where table_schema='db_watersystem' " +
|
" AND TABLE_NAME like 'tb_water_realdata_%' " +
|
" AND CREATE_TIME <= '"+Com.getDateTimeFormat(deldate, Com.DTF_YMDhms)+"';" ;
|
String sql_delete_strs = " DROP TABLE IF EXISTS ";
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
ResultSet res = null;
|
int count = 0;
|
res = sql.sqlMysqlQuery(sql_select_strs);
|
try {
|
while(res.next()) {
|
if(count > 0) {
|
sql_delete_strs += ",";
|
}
|
|
sql_delete_strs += "db_watersystem." + res.getString("TABLE_NAME");
|
System.out.println("ɾ³ý£º"+res.getString("TABLE_NAME")+"\t at "+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms));
|
count++;
|
}
|
if(count >0) {
|
sql.sqlMysqlExecute(sql_delete_strs);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(res != null) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ²åÈëË®ÀäÀúʷʵʱÊý¾Ý
|
* @param pool
|
* @param winf
|
* @param now
|
*/
|
public static void insertTb_water_realdata(MysqlConnPool pool, Water_inf winf, Date now) {
|
Water_brach brach = winf.wbrach;
|
Water_rt rt = winf.wrt;
|
Water_state state = winf.wstate;
|
String sql_str = "INSERT INTO "+Sql_Mysql.Tb_water_realdata+winf.water_id+"_"+Com.getDateTimeFormat(now, Com.DTF_Y_M_D)+"(water_id,record_time,P01_main_pump,P11_replenish_pump,P12_pretest_pump,P13_replenish_pump,H01_electric_heater,H02_electric_heater,Solenoid_openflag,Solenoid_shutflag,valve_start,Exhaust_start,remote_start,remote_stop,Operation_ooled_devices,Auto_control_state,Manual_control_state,remote_control_state,Water_system_function,cooling_flow,supply_tmp,Supply_pre,back_tem,back_pre,Cooling_ser,Buffer_tank_level,Buffer_tank_pre,Humidity_chamber,Tem_chamber,External_tmp,External_pre,Branch_flow_1,Branch_flow_2,Branch_flow_3,Branch_flow_4,Branch_flow_5,Branch_flow_6,Branch_flow_7,Branch_flow_8,Branch_flow_9,Branch_tmp_1,Branch_tmp_2,Branch_tmp_3,Branch_tmp_4,Branch_tmp_5,Branch_tmp_6,Branch_tmp_7,Branch_tmp_8,Branch_tmp_9,Branch_pre_1,Branch_pre_2,Branch_pre_3,Branch_pre_4,Branch_pre_5,Branch_pre_6,Branch_pre_7,Branch_pre_8,Branch_pre_9) "
|
+ "VALUES("+winf.water_id+",'"+Com.getDateTimeFormat(now, Com.DTF_YMDhms)+"',"+state.P01_main_pump+","+state.P11_replenish_pump+","+state.P12_pretest_pump+","+state.P13_replenish_pump+","+state.H01_electric_heater+","+state.H02_electric_heater+","+state.Solenoid_openflag+","+state.Solenoid_shutflag+","+state.valve_start+","+state.Exhaust_start+","+state.remote_start+","+state.remote_stop+","+state.Operation_ooled_devices+","+state.Auto_control_state+","+state.Manual_control_state+","+state.remote_control_state+","+state.Water_system_function+","+rt.cooling_flow+","+rt.supply_tmp+","+rt.Supply_pre+","+rt.back_tem+","+rt.back_pre+","+rt.Cooling_ser+","+rt.Buffer_tank_level+","+rt.Buffer_tank_pre+","+rt.Humidity_chamber+","+rt.Tem_chamber+","+rt.External_tmp+","+rt.External_pre+","+brach.Branch_flow_1+","+brach.Branch_flow_2+","+brach.Branch_flow_3+","+brach.Branch_flow_4+","+brach.Branch_flow_5+","+brach.Branch_flow_6+","+brach.Branch_flow_7+","+brach.Branch_flow_8+","+brach.Branch_flow_9+","+brach.Branch_tmp_1+","+brach.Branch_tmp_2+","+brach.Branch_tmp_3+","+brach.Branch_tmp_4+","+brach.Branch_tmp_5+","+brach.Branch_tmp_6+","+brach.Branch_tmp_7+","+brach.Branch_tmp_8+","+brach.Branch_tmp_9+","+brach.Branch_pre_1+","+brach.Branch_pre_2+","+brach.Branch_pre_3+","+brach.Branch_pre_4+","+brach.Branch_pre_5+","+brach.Branch_pre_6+","+brach.Branch_pre_7+","+brach.Branch_pre_8+","+brach.Branch_pre_9+"); ";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
}
|
