package com.watersystem.monitor;
|
|
import java.security.interfaces.RSAKey;
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.List;
|
|
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"));
|
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();
|
}
|
}
|
/**
|
* ³õʼ»¯Êý¾Ý¿â±í
|
* @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);
|
}
|
|
//²åÈë»ò¸üÐÂË®ÀäϵͳʵʱÐÅÏ¢±í
|
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();
|
}
|
}
|
//²åÈë»ò¸üÐÂË®Àäϵͳʵʱ·ÖÖ§ÐÅÏ¢±í
|
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();
|
}
|
}
|
//²åÈë»ò¸üÐÂË®ÀäϵͳʵʱÐÅÏ¢±í
|
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();
|
}
|
}
|
}
|
