package com.electrical.FourKW;
|
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.List;
|
import com.sql.MysqlConnPool;
|
import com.sql.Sql_Mysql;
|
|
public class Electrical_Task_SQL {
|
|
|
|
/**
|
* ²éѯËùÓеÄÉ豸¼Ç¼
|
* @param pool
|
* @param electric
|
*/
|
public static void queryAllElectric(MysqlConnPool pool,List<Electric_inf> electrics) {
|
String sql_str = " select * from " + Sql_Mysql.Tb_Electric_Inf + " order by electric_id;";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
res = sql.sqlMysqlQuery(sql_str);
|
Electric_inf elect = null;
|
while(res.next()) {
|
elect = new Electric_inf(res.getInt("electric_id"));
|
elect.setElectric_name(res.getString("electric_name"));
|
elect.setElectric_type(res.getInt("electric_type"));
|
elect.setElectric_ip(res.getString("electric_ip"));
|
electrics.add(elect);
|
}
|
} 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 init(MysqlConnPool pool) {
|
|
createDB_ElectricSystem(pool);
|
|
createTb_Electric_inf(pool);
|
|
createTb_Electric_Switch(pool);
|
|
createTb_Electric_State(pool);
|
|
createTb_Electric_State(pool);
|
|
createTb_Electric_Rt(pool);
|
|
createTb_Electric_Power(pool);
|
|
createTb_Electric_Control_Single(pool);
|
|
}
|
|
|
|
|
/**
|
* ´´½¨4KWµçÔ´Êý¾Ý¿â
|
* @param pool
|
*/
|
public static void createDB_ElectricSystem(MysqlConnPool pool) {
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + Sql_Mysql.DB_ElectricSystem);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
|
|
|
|
/**
|
* ´´½¨µçÔ´ÐÅÏ¢±í
|
* @param pool
|
*/
|
public static void createTb_Electric_inf(MysqlConnPool pool) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS "+Sql_Mysql.Tb_Electric_Inf+" (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Ö÷¼ü'," +
|
" electric_id int(11) NOT NULL DEFAULT '3001' COMMENT 'É豸id'," +
|
" electric_name varchar(255) NOT NULL DEFAULT '' COMMENT 'µçÔ´Ãû³Æ'," +
|
" electric_type int(11) NOT NULL DEFAULT '0' COMMENT 'µçÔ´ÀàÐÍ(±¸ÓÃ)'," +
|
" electric_ip varchar(255) NOT NULL DEFAULT '127.0.0.1' COMMENT 'µçÔ´É豸ip(ÖØÒª)'," +
|
" note varchar(255) NOT NULL DEFAULT ''," +
|
" PRIMARY KEY (num)," +
|
" KEY electric_id_key (electric_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_Electric_Switch(MysqlConnPool pool) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_Electric_Switch + " (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Ö÷¼ü'," +
|
" km1_switchon_one int(11) NOT NULL DEFAULT '0' COMMENT 'Ò»ºÅת»»¿ª¹Ø¹ñKM1ºÏÕ¢Ö¸Áî'," +
|
" km1_switchoff_one int(11) NOT NULL DEFAULT '0' COMMENT 'Ò»ºÅת»»¿ª¹Ø¹ñKM1·ÖÕ¢Ö¸Áî'," +
|
" km2_switchon_one int(11) NOT NULL DEFAULT '0' COMMENT 'Ò»ºÅת»»¿ª¹Ø¹ñKM2ºÏÕ¢Ö¸Áî'," +
|
" km2_switchoff_one int(11) NOT NULL DEFAULT '0' COMMENT 'Ò»ºÅת»»¿ª¹Ø¹ñKM2·ÖÕ¢Ö¸Áî'," +
|
" km3_switchon_one int(11) NOT NULL DEFAULT '0' COMMENT 'Ò»ºÅת»»¿ª¹Ø¹ñKM3ºÏÕ¢Ö¸Áî'," +
|
" km3_switchoff_one int(11) NOT NULL DEFAULT '0' COMMENT 'Ò»ºÅת»»¿ª¹Ø¹ñKM3·ÖÕ¢Ö¸Áî'," +
|
" km4_switchon_one int(11) NOT NULL DEFAULT '0' COMMENT 'Ò»ºÅת»»¿ª¹Ø¹ñKM4ºÏÕ¢Ö¸Áî'," +
|
" km4_switchoff_one int(11) NOT NULL DEFAULT '0' COMMENT 'Ò»ºÅת»»¿ª¹Ø¹ñKM4·ÖÕ¢Ö¸Áî'," +
|
" km1_switchon_two int(11) NOT NULL DEFAULT '0' COMMENT '¶þºÅת»»¿ª¹Ø¹ñKM1ºÏÕ¢Ö¸Áî'," +
|
" km1_switchoff_two int(11) NOT NULL DEFAULT '0' COMMENT '¶þºÅת»»¿ª¹Ø¹ñKM1·ÖÕ¢Ö¸Áî'," +
|
" km2_switchon_two int(11) NOT NULL DEFAULT '0' COMMENT '¶þºÅת»»¿ª¹Ø¹ñKM2ºÏÕ¢Ö¸Áî'," +
|
" km2_switchoff_two int(11) NOT NULL DEFAULT '0' COMMENT '¶þºÅת»»¿ª¹Ø¹ñKM2·ÖÕ¢Ö¸Áî'," +
|
" km3_switchon_two int(11) NOT NULL DEFAULT '0' COMMENT '¶þºÅת»»¿ª¹Ø¹ñKM3ºÏÕ¢Ö¸Áî'," +
|
" km3_switchoff_two int(11) NOT NULL DEFAULT '0' COMMENT '¶þºÅת»»¿ª¹Ø¹ñKM3·ÖÕ¢Ö¸Áî'," +
|
" km4_switchon_two int(11) NOT NULL DEFAULT '0' COMMENT '¶þºÅת»»¿ª¹Ø¹ñKM4ºÏÕ¢Ö¸Áî'," +
|
" km4_switchoff_two int(11) NOT NULL DEFAULT '0' COMMENT '¶þºÅת»»¿ª¹Ø¹ñKM4·ÖÕ¢Ö¸Áî'," +
|
" note varchar(255) NOT NULL DEFAULT ''," +
|
" PRIMARY KEY (num)" +
|
") 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_Electric_State(MysqlConnPool pool) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_Electric_State + " (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Ö÷¼ü'," +
|
" electric_id int(11) NOT NULL DEFAULT '3001' COMMENT 'É豸id'," +
|
" case_trip_output int(11) NOT NULL DEFAULT '0' COMMENT '»úÏäÌøÕ¢Êä³ö'," +
|
" fault_reset_signal int(11) NOT NULL DEFAULT '0' COMMENT '¹ÊÕϸ´Î»ÐźÅ'," +
|
" high_vol_switch_status int(11) NOT NULL DEFAULT '0' COMMENT '¸ßѹ¿ª¹Ø״̬'," +
|
" stop_button int(11) NOT NULL DEFAULT '0' COMMENT '¼±Í£°´Å¥'," +
|
" main_cabinet_fan_status int(11) NOT NULL DEFAULT '0' COMMENT 'Ö÷¹ñ·ç»ú״̬'," +
|
" trans_cabinet_door_lock int(11) NOT NULL DEFAULT '0' COMMENT '±äѹÆ÷¹ñÃÅÁ¬Ëø'," +
|
" trans_cabinet_overtem_alarm int(11) NOT NULL DEFAULT '0' COMMENT '±äѹÆ÷³¬Î±¨¾¯'," +
|
" trans_cabinet_overtem_trip int(11) NOT NULL DEFAULT '0' COMMENT '±äѹÆ÷³¬ÎÂÌøÕ¢'," +
|
" qs1_state int(11) NOT NULL DEFAULT '0' COMMENT 'QS1״̬'," +
|
" qs2_state int(11) NOT NULL DEFAULT '0' COMMENT 'QS2״̬'," +
|
" switch_cabinet_door_status int(11) NOT NULL DEFAULT '0' COMMENT 'ת»»¿ª¹Ø¹ñÃÅ״̬'," +
|
" alldcvol float NOT NULL DEFAULT '0' COMMENT '×ÜÖ±Á÷µçѹ'," +
|
" alldccurr float NOT NULL DEFAULT '0' COMMENT '×ÜÖ±Á÷µçÁ÷'," +
|
" note varchar(255) NOT NULL DEFAULT ''," +
|
" PRIMARY KEY (num)," +
|
" KEY electric_id_key (electric_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 createTb_Electric_Rt(MysqlConnPool pool) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_Electric_Rt + " (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Ö÷¼ü'," +
|
" electric_id int(11) NOT NULL DEFAULT '3001' COMMENT 'É豸id'," +
|
" upsetcurr float NOT NULL DEFAULT '0' COMMENT 'ÉÏλ»úÉ趨µçÁ÷'," +
|
" upsetvol float NOT NULL DEFAULT '0' COMMENT 'ÉÏλ»úÉ趨µçѹ'," +
|
" convolcontrol int(11) NOT NULL DEFAULT '0' COMMENT 'ºãѹ¿ØÖÆ'," +
|
" concurrcontrol int(11) NOT NULL DEFAULT '0' COMMENT 'ºãÁ÷¿ØÖÆ'," +
|
" othermodes int(11) NOT NULL DEFAULT '0' COMMENT 'ÆäËûģʽÔËÐÐ'," +
|
" parallelmodes int(11) NOT NULL DEFAULT '0' COMMENT '²¢ÁªÎÈѹÔËÐÐ'," +
|
" startposition int(11) NOT NULL DEFAULT '0' COMMENT 'Æô¶¯Î»'," +
|
" stopposition int(11) NOT NULL DEFAULT '0' COMMENT 'ֹͣλ'," +
|
" resetposition int(11) NOT NULL DEFAULT '0' COMMENT '¸´Î»'," +
|
" fanstart int(11) NOT NULL DEFAULT '0' COMMENT '·ç»úÆô¶¯'," +
|
" fanstop int(11) NOT NULL DEFAULT '0' COMMENT '·ç»úÍ£Ö¹'," +
|
" dcvol float NOT NULL DEFAULT '0' COMMENT 'Ö±Á÷µçѹ '," +
|
" dccurr float NOT NULL DEFAULT '0' COMMENT 'Ö±Á÷µçÁ÷'," +
|
" controlangle int(11) NOT NULL DEFAULT '0' COMMENT '¿ØÖƽÇ'," +
|
" loopflag int(11) NOT NULL DEFAULT '0' COMMENT '¿ª±Õ»·±ê־루0¿ª»·£¬1±Õ»·£©'," +
|
" remoteflag int(11) NOT NULL DEFAULT '0' COMMENT '¾ÍµØÔ¶³Ì±ê־루0Ô¶³Ì£¬1¾ÍµØ£©'," +
|
" orderflag int(11) NOT NULL DEFAULT '0' COMMENT 'ÕýÐòÄæÐò±ê־루0ÕýÐò£¬1ÄæÐò£©'," +
|
" consvolcurrflag int(11) NOT NULL DEFAULT '0' COMMENT 'ºãѹºãÁ÷±ê־루0ºãѹ£¬1ºãÁ÷£©'," +
|
" powerstartflag int(11) NOT NULL DEFAULT '0' COMMENT 'µçÔ´Æô¶¯±ê־루0Í£Ö¹£¬1Æô¶¯£© '," +
|
" workmodelflag int(11) NOT NULL DEFAULT '0' COMMENT '¹¤×÷ģʽ±ê־루0ÆäËûģʽ£¬1²¢ÁªÎÈѹ£©'," +
|
" note varchar(255) NOT NULL DEFAULT ''," +
|
" PRIMARY KEY (num)," +
|
" KEY electric_id_key (electric_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 createTb_Electric_Power(MysqlConnPool pool) {
|
String sql_str = " CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_Electric_Power + " (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Ö÷¼ü'," +
|
" start_a int(11) NOT NULL DEFAULT '0' COMMENT 'AÌ×Æô¶¯'," +
|
" stop_a int(11) NOT NULL DEFAULT '0' COMMENT 'AÌ×Í£Ö¹'," +
|
" start_b int(11) NOT NULL DEFAULT '0' COMMENT 'BÌ×Æô¶¯'," +
|
" stop_b int(11) NOT NULL DEFAULT '0' COMMENT 'BÌ×Í£Ö¹'," +
|
" start_c int(11) NOT NULL DEFAULT '0' COMMENT 'CÌ×Æô¶¯'," +
|
" stop_c int(11) NOT NULL DEFAULT '0' COMMENT 'CÌ×Í£Ö¹'," +
|
" start_d int(11) NOT NULL DEFAULT '0' COMMENT 'DÌ×Æô¶¯'," +
|
" stop_d int(11) NOT NULL DEFAULT '0' COMMENT 'DÌ×Í£Ö¹'," +
|
" abconn_start int(11) NOT NULL DEFAULT '0' COMMENT 'AÌ×BÌײ¢ÁªÔËÐÐÆô¶¯'," +
|
" abconn_stop int(11) NOT NULL DEFAULT '0' COMMENT 'AÌ×BÌײ¢ÁªÔËÐÐÍ£Ö¹'," +
|
" cdconn_start int(11) NOT NULL DEFAULT '0' COMMENT 'CÌ×DÌײ¢ÁªÔËÐÐÆô¶¯'," +
|
" cdconn_stop int(11) NOT NULL DEFAULT '0' COMMENT 'CÌ×DÌײ¢ÁªÔËÐÐÍ£Ö¹'," +
|
" allconn_start int(11) NOT NULL DEFAULT '0' COMMENT 'ËÄÌײ¢ÁªÔËÐÐÆô¶¯'," +
|
" allconn_stop int(11) NOT NULL DEFAULT '0' COMMENT 'ËÄÌײ¢ÁªÔËÐÐÍ£Ö¹'," +
|
" abconn_flag int(11) NOT NULL DEFAULT '0' COMMENT 'AB²¢´®Áª±ê־λ'," +
|
" cdconn_flag int(11) NOT NULL DEFAULT '0' COMMENT 'CD²¢´®Áª±ê־λ'," +
|
" abconn_volset int(11) NOT NULL DEFAULT '0' COMMENT 'AB²¢Áªµçѹ¸ø¶¨'," +
|
" abconn_currset int(11) NOT NULL DEFAULT '0' COMMENT 'AB²¢ÁªµçÁ÷¸ø¶¨'," +
|
" cdconn_volset int(11) NOT NULL DEFAULT '0' COMMENT 'CD²¢Áªµçѹ¸ø¶¨'," +
|
" cdconn_currset int(11) NOT NULL DEFAULT '0' COMMENT 'CD²¢ÁªµçÁ÷¸ø¶¨'," +
|
" note varchar(255) NOT NULL DEFAULT ''," +
|
" PRIMARY KEY (num)" +
|
") 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_Electric_Control_Single(MysqlConnPool pool) {
|
String sql_str = " CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_Electric_Control_Single + " (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Ö÷¼ü'," +
|
" electric_id int(11) NOT NULL DEFAULT '3001' COMMENT 'µçÔ´id'," +
|
" upsetcurr float NOT NULL DEFAULT '0' COMMENT 'ÉÏλ»úÉ趨µçÁ÷'," +
|
" upsetvol float NOT NULL DEFAULT '0' COMMENT 'ÉÏλ»úÉ趨µçѹ'," +
|
" convolcontrol int(11) NOT NULL DEFAULT '0' COMMENT 'ºãѹ¿ØÖÆ'," +
|
" concurrcontrol int(11) NOT NULL DEFAULT '0' COMMENT 'ºãÁ÷¿ØÖÆ'," +
|
" othermodes int(11) NOT NULL DEFAULT '0' COMMENT 'ÆäËûģʽÔËÐÐ'," +
|
" parallelmodes int(11) NOT NULL DEFAULT '0' COMMENT '²¢ÁªÎÈѹÔËÐÐ'," +
|
" startposition int(11) NOT NULL DEFAULT '0' COMMENT 'Æô¶¯Î»'," +
|
" stopposition int(11) NOT NULL DEFAULT '0' COMMENT 'ֹͣλ'," +
|
" resetposition int(11) NOT NULL DEFAULT '0' COMMENT '¸´Î»'," +
|
" fanstart int(11) NOT NULL DEFAULT '0' COMMENT '·ç»úÆô¶¯'," +
|
" fanstop int(11) NOT NULL DEFAULT '0' COMMENT '·ç»úÍ£Ö¹'," +
|
" dcvol float NOT NULL DEFAULT '0' COMMENT 'Ö±Á÷µçѹ'," +
|
" dccurr float NOT NULL DEFAULT '0' COMMENT 'Ö±Á÷µçÁ÷'," +
|
" controlangle float NOT NULL DEFAULT '0' COMMENT '¿ØÖƽÇ'," +
|
" PRIMARY KEY (num)" +
|
") 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();
|
}
|
}
|
}
|
