package com.dev.fbs9100;
|
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
|
import javax.net.ssl.HandshakeCompletedEvent;
|
|
import com.sql.MysqlConnPool;
|
import com.sql.Sql_Mysql;
|
|
public class FBS9100_Task_Thread_SQL {
|
|
/**
|
* ´´½¨ tb_fbs9100_state ±í²¢ÇÒ½«É豸idºÍÉ豸hip²åÈëµ½±íÖÐ
|
* @param con_pool
|
* @param al_param
|
*/
|
public static void createFBS9100StateTableOnRam(MysqlConnPool con_pool,boolean recreate_tb)
|
{
|
String str1 = "DROP TABLE IF EXISTS " + Sql_Mysql.FBS9100State_Table;
|
String str2 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.FBS9100State_Table
|
+ " ( `num` BIGINT NOT NULL AUTO_INCREMENT, "
|
+ "`dev_ip` varchar(20) NOT NULL DEFAULT '127.0.0.0', "
|
+ "`dev_version` varchar(20) NOT NULL DEFAULT '0', "
|
+ "`dev_id` BIGINT NOT NULL DEFAULT 0, "
|
+ "`op_cmd` INT NOT NULL DEFAULT 0, "
|
+ "`record_datetime` varchar(20) NOT NULL DEFAULT '2015-12-02 12:10:00', "
|
+ "`dev_datetime` varchar(20) NOT NULL DEFAULT '2015-12-02 12:10:00', "
|
+ "`dev_testtype` INT NOT NULL DEFAULT 0, "
|
+ "`dev_testgroupnum` INT NOT NULL DEFAULT 0, "
|
+ "`dev_workstate` INT NOT NULL DEFAULT 0, "
|
+ "`dev_alarmstate` INT NOT NULL DEFAULT 0, "
|
+ "`dev_temp` float NOT NULL DEFAULT 0, " //É豸ζÈ
|
+ "`dev_res_test_state` INT NOT NULL DEFAULT 0, " //ÄÚ×è²âÊÔ״̬
|
+ "`dev_onlinevollow` BOOLEAN NOT NULL DEFAULT false, "
|
+ "`dev_eachgroup_battsum` INT NOT NULL DEFAULT 0, "
|
+ "`dev_captest_onlinevol` float NOT NULL DEFAULT 0, "
|
+ "`dev_captest_groupvol` float NOT NULL DEFAULT 0, "
|
+ "`dev_captest_curr` float NOT NULL DEFAULT 0, "
|
+ "`dev_captest_cap` float NOT NULL DEFAULT 0, "
|
+ "`dev_captest_timelong` INT NOT NULL DEFAULT 0, "
|
+ "`dev_restest_count` INT NOT NULL DEFAULT 0, "
|
+ "`dev_restest_moncount` INT NOT NULL DEFAULT 0, "
|
+ "`dev_restest_monindex` INT NOT NULL DEFAULT 0, "
|
+ "`dev_commcount` INT NOT NULL DEFAULT 0, "
|
+ "`dev_errcommcount` INT NOT NULL DEFAULT 0, "
|
+ "`dev_rxnullerrcount` INT NOT NULL DEFAULT 0, "
|
|
+ "`dev_last_captest_stop_type` INT NOT NULL DEFAULT 0, "
|
+ "`dev_concurr` float NOT NULL DEFAULT 0, "
|
+ "`dev_condvoldp` float NOT NULL DEFAULT 0, "
|
+ "`dev_conresist` float NOT NULL DEFAULT 0, "
|
+ "`dev_condcurr` float NOT NULL DEFAULT 0, "
|
+ "`dev_61850alarms` varchar(64) NOT NULL DEFAULT '', "
|
|
+ "`dev_batt_xuhang_tlong` INT NOT NULL DEFAULT 0, " //Ðøº½Ê±³¤
|
+ "`dev_station_poff_tlong` INT NOT NULL DEFAULT 0, " //Í£µçʱ³¤
|
+ "`dev_station_poff_cnt` INT NOT NULL DEFAULT 0, " //Í£µç´ÎÊý
|
+ "`dev_data_flowsum` BIGINT NOT NULL DEFAULT 0, " //»ú·¿Á÷Á¿
|
+ "`batt_online_state` INT NOT NULL DEFAULT 0, " //µç³Ø״̬(0:±ÕºÏ,1£º¶Ï¿ª)
|
|
+ " UNIQUE INDEX index_dev_id_uniq (`dev_id`), "
|
+ " PRIMARY KEY (`num`) ) "
|
+ " ENGINE=InnoDB DEFAULT CHARSET=utf8";
|
/*
|
String str3 = "INSERT INTO " + Sql_Mysql.FBS9100State_Table + " (dev_ip, dev_id) ";
|
str3 += " SELECT dev_ip, dev_id FROM " + Sql_Mysql.BattInf_Table;
|
*/
|
Sql_Mysql sql = new Sql_Mysql(con_pool.getConn());
|
ResultSet res = null;
|
try {
|
if(recreate_tb) {
|
sql.sqlMysqlExecute(str1);
|
}
|
sql.sqlMysqlExecute(str2);
|
//Ìí¼ÓÉ豸ζÈÁÐ
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_ram_db'"
|
+ " AND table_name='tb_fbs9100_state'"
|
+ " AND column_name='dev_temp'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.FBS9100State_Table
|
+ " ADD COLUMN `dev_temp` float NOT NULL DEFAULT 0;");
|
}
|
//Ìí¼ÓÄÚ×è²âÊÔ״̬ÁÐ
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_ram_db'"
|
+ " AND table_name='tb_fbs9100_state'"
|
+ " AND column_name='dev_res_test_state'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.FBS9100State_Table
|
+ " ADD COLUMN `dev_res_test_state` INT NOT NULL DEFAULT 0;");
|
}
|
//Ìí¼Ódev_restest_count
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_ram_db'"
|
+ " AND table_name='tb_fbs9100_state'"
|
+ " AND column_name='dev_restest_count'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.FBS9100State_Table
|
+ " ADD COLUMN `dev_restest_count` INT NOT NULL DEFAULT 0;");
|
}
|
//Ìí¼Ódev_last_captest_stop_type
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_ram_db'"
|
+ " AND table_name='tb_fbs9100_state'"
|
+ " AND column_name='dev_last_captest_stop_type'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.FBS9100State_Table
|
+ " ADD COLUMN `dev_last_captest_stop_type` INT NOT NULL DEFAULT 0;");
|
}
|
|
//Ìí¼Ódev_concurr
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_ram_db'"
|
+ " AND table_name='tb_fbs9100_state'"
|
+ " AND column_name='dev_concurr'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.FBS9100State_Table
|
+ " ADD COLUMN `dev_concurr` float NOT NULL DEFAULT 0;");
|
}
|
|
//Ìí¼Ódev_condvoldp
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_ram_db'"
|
+ " AND table_name='tb_fbs9100_state'"
|
+ " AND column_name='dev_condvoldp'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.FBS9100State_Table
|
+ " ADD COLUMN `dev_condvoldp` float NOT NULL DEFAULT 0;");
|
}
|
//Ìí¼Ódev_conresist
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_ram_db'"
|
+ " AND table_name='tb_fbs9100_state'"
|
+ " AND column_name='dev_conresist'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.FBS9100State_Table
|
+ " ADD COLUMN `dev_conresist` float NOT NULL DEFAULT 0;");
|
}
|
//Ìí¼Ódev_condcurr
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_ram_db'"
|
+ " AND table_name='tb_fbs9100_state'"
|
+ " AND column_name='dev_condcurr'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.FBS9100State_Table
|
+ " ADD COLUMN `dev_condcurr` float NOT NULL DEFAULT 0;");
|
}
|
|
//Ìí¼Ódev_61850alarms
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_ram_db'"
|
+ " AND table_name='tb_fbs9100_state'"
|
+ " AND column_name='dev_61850alarms'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.FBS9100State_Table
|
+ " ADD COLUMN `dev_61850alarms` varchar(64) NOT NULL DEFAULT '';");
|
}
|
//Ìí¼Ódev_batt_xuhang_tlong
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_ram_db'"
|
+ " AND table_name='tb_fbs9100_state'"
|
+ " AND column_name='dev_batt_xuhang_tlong'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.FBS9100State_Table
|
+ " ADD COLUMN `dev_batt_xuhang_tlong` INT NOT NULL DEFAULT 0;");
|
}
|
|
//Ìí¼Ódev_station_poff_tlong
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_ram_db'"
|
+ " AND table_name='tb_fbs9100_state'"
|
+ " AND column_name='dev_station_poff_tlong'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.FBS9100State_Table
|
+ " ADD COLUMN `dev_station_poff_tlong` INT NOT NULL DEFAULT 0;");
|
}
|
|
//Ìí¼Ódev_station_poff_cnt(Í£µç´ÎÊý)
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_ram_db'"
|
+ " AND table_name='tb_fbs9100_state'"
|
+ " AND column_name='dev_station_poff_cnt'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.FBS9100State_Table
|
+ " ADD COLUMN `dev_station_poff_cnt` INT NOT NULL DEFAULT 0;");
|
}
|
|
|
//Ìí¼Ódev_data_flowsum(Á÷Á¿Í³¼Æ)
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_ram_db'"
|
+ " AND table_name='tb_fbs9100_state'"
|
+ " AND column_name='dev_data_flowsum'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.FBS9100State_Table
|
+ " ADD COLUMN `dev_data_flowsum` BIGINT NOT NULL DEFAULT 0;");
|
}
|
//Ìí¼Óbatt_online_state(µç³Ø¿ª¹Ø״̬)
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_ram_db'"
|
+ " AND table_name='tb_fbs9100_state'"
|
+ " AND column_name='batt_online_state'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.FBS9100State_Table
|
+ " ADD COLUMN `batt_online_state` INT NOT NULL DEFAULT 0;");
|
}
|
//sql.sqlMysqlExecute(str3);
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ´´½¨ tb_fbs9100s_dcdc_data ±í²¢ÇÒ½«É豸idºÍÉ豸ip²åÈëµ½±íÖÐ
|
* @param con_pool
|
* @param al_param
|
*/
|
public static void createFBS9100S_DCDC_DataTableOnRam(MysqlConnPool con_pool)
|
{
|
String str1 = "DROP TABLE IF EXISTS " + Sql_Mysql.FBS9100S_DcDcState_Table;
|
String str2 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.FBS9100S_DcDcState_Table
|
+ " ( `num` BIGINT NOT NULL AUTO_INCREMENT, "
|
+ "`dev_id` BIGINT NOT NULL DEFAULT 0, "
|
+ "`dcdc_num` BIGINT NOT NULL DEFAULT 0, "
|
+ "`dcdc_Vsys` float NOT NULL DEFAULT 0, "
|
+ "`dcdc_Vout` float NOT NULL DEFAULT 0, "
|
+ "`dcdc_Vbat` float NOT NULL DEFAULT 0, "
|
+ "`dcdc_Iout` float NOT NULL DEFAULT 0, "
|
+ "`dcdc_Ibuck` float NOT NULL DEFAULT 0, "
|
+ "`dcdc_Tmp_Dis` float NOT NULL DEFAULT 0, "
|
+ "`dcdc_Tmp_Chr` float NOT NULL DEFAULT 0, "
|
+ "`dcdc_SMR_Alarm1` INT NOT NULL DEFAULT 0, "
|
+ "`dcdc_SMR_Alarm2` INT NOT NULL DEFAULT 0, "
|
+ "`dcdc_version` INT NOT NULL DEFAULT 0, "
|
+ "`dcdc_ModeSetComm` INT NOT NULL DEFAULT 0, "
|
+ "`dcdc_RunSetComm` INT NOT NULL DEFAULT 0, "
|
+ "`dcdc_BuckISet` float NOT NULL DEFAULT 0, "
|
+ "`dcdc_DisVolSet` float NOT NULL DEFAULT 0, "
|
+ "`dcdc_DisCurrSet` float NOT NULL DEFAULT 0, "
|
+ "`dcdc_CommTxCnt` INT NOT NULL DEFAULT 0, "
|
+ "`dcdc_CommRxCnt` INT NOT NULL DEFAULT 0, "
|
+ " UNIQUE INDEX index_dcdc_num_uniq (`dcdc_num`), "
|
+ " PRIMARY KEY (`num`) ) "
|
+ " ENGINE=MEMORY DEFAULT CHARSET=utf8";
|
|
Sql_Mysql sql = new Sql_Mysql(con_pool.getConn());
|
try {
|
sql.sqlMysqlExecute(str1);
|
sql.sqlMysqlExecute(str2);
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ´´½¨ tb_fbs9100_setparam ±í£¬²¢ÇÒ½«É豸idºÍÉ豸ip²åÈëµ½±íÖÐ
|
* @param con_pool
|
* @param al_param
|
*/
|
public static void createFBS9100SetTestParamTableOnRam(MysqlConnPool con_pool,boolean recreate_tb)
|
{
|
String str1 = "DROP TABLE IF EXISTS " + Sql_Mysql.FBS9100SetParam_Table;
|
String str2 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.FBS9100SetParam_Table
|
+ " ( `num` BIGINT NOT NULL AUTO_INCREMENT, "
|
+ "`dev_ip` varchar(20) NOT NULL DEFAULT '127.0.0.0', "
|
+ "`dev_id` BIGINT NOT NULL DEFAULT 0, "
|
+ "`op_cmd` INT NOT NULL DEFAULT 0, "
|
+ "`TestCmd` INT NOT NULL DEFAULT 0, "
|
+ "`HourRate` INT NOT NULL DEFAULT 0, "
|
+ "`DisCurr` FLOAT NOT NULL DEFAULT 0, "
|
+ "`DisCap` FLOAT NOT NULL DEFAULT 0, "
|
+ "`DisTime` INT NOT NULL DEFAULT 0, "
|
+ "`GroupVol_LOW` FLOAT NOT NULL DEFAULT 0, "
|
+ "`MonomerVol_LOW` FLOAT NOT NULL DEFAULT 0, "
|
+ "`MonomerLowCount` INT NOT NULL DEFAULT 0, "
|
+ "`BattGroupNum` INT NOT NULL DEFAULT 0, "
|
+ "`OnlineVolLowAction` INT NOT NULL DEFAULT 0, "
|
+ "`DCVolHighLimit` FLOAT NOT NULL DEFAULT 0, "
|
+ "`ChargeCurrSet` FLOAT NOT NULL DEFAULT 0, "
|
+ "`MonomerTmp_High` FLOAT NOT NULL DEFAULT 0, "
|
+ "`AutoTestStartVol` float NOT NULL DEFAULT 0, " //Æðʼµçѹ
|
+ " UNIQUE INDEX index_dev_id_uniq (`dev_id`), "
|
+ " PRIMARY KEY (`num`) ) "
|
+ " ENGINE=MEMORY DEFAULT CHARSET=utf8";
|
|
Sql_Mysql sql = new Sql_Mysql(con_pool.getConn());
|
ResultSet res = null;
|
try {
|
if(true == recreate_tb) {
|
sql.sqlMysqlExecute(str1); //Öؽ¨±í
|
}
|
sql.sqlMysqlExecute(str2);
|
//Ìí¼ÓÖ¸¶¨µÄBattGroupNumÁÐ
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_ram_db'"
|
+ " AND table_name='tb_fbs9100_setparam'"
|
+ " AND column_name='BattGroupNum'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.FBS9100SetParam_Table
|
+ " ADD COLUMN `BattGroupNum` INT NOT NULL DEFAULT 0;");
|
}
|
//Ìí¼ÓÖ¸¶¨µÄOnlineVolLowActionÁÐ
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_ram_db'"
|
+ " AND table_name='tb_fbs9100_setparam'"
|
+ " AND column_name='OnlineVolLowAction'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.FBS9100SetParam_Table
|
+ " ADD COLUMN `OnlineVolLowAction` INT NOT NULL DEFAULT 0;");
|
}
|
//Ìí¼ÓÖ¸¶¨µÄ DCVolHighLimit ÁÐ
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_ram_db'"
|
+ " AND table_name='tb_fbs9100_setparam'"
|
+ " AND column_name='DCVolHighLimit'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.FBS9100SetParam_Table
|
+ " ADD COLUMN `DCVolHighLimit` FLOAT NOT NULL DEFAULT 0;");
|
}
|
//Ìí¼ÓÖ¸¶¨µÄ ChargeCurrSet ÁÐ
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_ram_db'"
|
+ " AND table_name='tb_fbs9100_setparam'"
|
+ " AND column_name='ChargeCurrSet'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.FBS9100SetParam_Table
|
+ " ADD COLUMN `ChargeCurrSet` FLOAT NOT NULL DEFAULT 0;");
|
}
|
//Ìí¼ÓÖ¸¶¨µÄ MonomerTmp_High ÁÐ
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_ram_db'"
|
+ " AND table_name='tb_fbs9100_setparam'"
|
+ " AND column_name='MonomerTmp_High'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.FBS9100SetParam_Table
|
+ " ADD COLUMN `MonomerTmp_High` FLOAT NOT NULL DEFAULT 0;");
|
}
|
//Ìí¼ÓÖ¸¶¨µÄ AutoTestStartVol ÁÐ
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_ram_db'"
|
+ " AND table_name='tb_fbs9100_setparam'"
|
+ " AND column_name='AutoTestStartVol'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.FBS9100SetParam_Table
|
+ " ADD COLUMN `AutoTestStartVol` float NOT NULL DEFAULT 0;");
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ´´½¨ Sql_Mysql.RamDB_CMCC_POWER_Data ±í²¢ÇÒ½«É豸id²åÈëµ½±íÖÐ
|
* @param con_pool
|
* @param al_param
|
*/
|
public static void createFBS9100CmccPowerDataOnRam(MysqlConnPool con_pool)
|
{
|
String str1 = "DROP TABLE IF EXISTS " + Sql_Mysql.RamDB_CMCC_POWER_Data;
|
String str2 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.RamDB_CMCC_POWER_Data
|
+ " ( `num` BIGINT NOT NULL AUTO_INCREMENT, "
|
+ "`dev_id` BIGINT NOT NULL DEFAULT 0, "
|
+ "`vol1` float NOT NULL DEFAULT 0, "
|
+ "`vol2` float NOT NULL DEFAULT 0, "
|
+ "`vol3` float NOT NULL DEFAULT 0, "
|
+ "`vol4` float NOT NULL DEFAULT 0, "
|
+ "`vol5` float NOT NULL DEFAULT 0, "
|
+ "`curr1` float NOT NULL DEFAULT 0, "
|
+ "`curr2` float NOT NULL DEFAULT 0, "
|
+ "`curr3` float NOT NULL DEFAULT 0, "
|
+ "`curr4` float NOT NULL DEFAULT 0, "
|
+ "`curr5` float NOT NULL DEFAULT 0, "
|
+ " UNIQUE INDEX index_dev_id_uniq (`dev_id`), "
|
+ " PRIMARY KEY (`num`) ) "
|
+ " ENGINE=InnoDB DEFAULT CHARSET=utf8";
|
|
Sql_Mysql sql = new Sql_Mysql(con_pool.getConn());
|
try {
|
sql.sqlMysqlExecute(str1);
|
sql.sqlMysqlExecute(str2);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
/***********************************************************************************/
|
|
/**
|
*
|
* @param gB_MysqlConnPool
|
*/
|
public static void createBTSStationState_TableOnRam(MysqlConnPool con_pool,boolean recreate_tb) {
|
String str1 = "DROP TABLE IF EXISTS " + Sql_Mysql.BTSStationState_Table;
|
String str2 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.BTSStationState_Table
|
+ " ( `num` BIGINT NOT NULL AUTO_INCREMENT, "
|
+ "`station_id` BIGINT NOT NULL DEFAULT 0, "
|
+ "`dev_id` BIGINT NOT NULL DEFAULT 0, "
|
+ "`record_datetime` varchar(20) NOT NULL DEFAULT '2015-12-02 12:10:00', "
|
+ "`station_stat` INT NOT NULL DEFAULT 0, "
|
+ "`station_poff_start_time` varchar(20) NOT NULL DEFAULT '2015-12-02 12:10:00', "
|
//+ "`station_poff_tlong` INT NOT NULL DEFAULT 0, "
|
+ "`station_fadian_start_time` varchar(20) NOT NULL DEFAULT '2015-12-02 12:10:00', "
|
//+ "`station_fadian_tlong` INT NOT NULL DEFAULT 0, "
|
+ "`station_diaozhan_start_time` varchar(20) NOT NULL DEFAULT '2015-12-02 12:10:00', "
|
//+ "`station_diaozhan_tlong` INT NOT NULL DEFAULT 0, "
|
+ "`station_temp` float NOT NULL DEFAULT 0, " //ζÈ
|
+ "`station_damp` float NOT NULL DEFAULT 0, " //ʪ¶È
|
+ "`station_dooropen_start_time` varchar(20) NOT NULL DEFAULT '2015-12-02 12:10:00', "
|
//+ "`station_dooropen_tlong` INT NOT NULL DEFAULT 0, "
|
+ "`station_xuhang_start_time` varchar(20) NOT NULL DEFAULT '2015-12-02 12:10:00', "
|
+ "`station_xuhang_end_time` varchar(20) NOT NULL DEFAULT '2015-12-02 12:10:00', "
|
+ " UNIQUE INDEX index_station_id_uniq (`station_id`), "
|
+ " PRIMARY KEY (`num`) ) "
|
+ " ENGINE=InnoDB DEFAULT CHARSET=utf8";
|
Sql_Mysql sql = new Sql_Mysql(con_pool.getConn());
|
try {
|
if(recreate_tb) {
|
sql.sqlMysqlExecute(str1);
|
}
|
sql.sqlMysqlExecute(str2);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ²åÈëÔÚ»ú·¿×´Ì¬±íÖÐδÌí¼Ó»ú·¿×´Ì¬µÄ¼Ç¼
|
* @param gB_MysqlConnPool
|
*/
|
public static void insertBTSStationState_TableOnRam(MysqlConnPool con_pool) {
|
//²éѯûÓÐÌí¼Ó»ú·¿×´Ì¬µÄ»ú·¿¼Ç¼
|
String sql_str1 = " SELECT DISTINCT StationId,FBSDeviceId " +
|
" FROM " + Sql_Mysql.BattInf_Table +
|
" WHERE StationId NOT IN( " +
|
" SELECT station_id " +
|
" FROM "+ Sql_Mysql.BTSStationState_Table +
|
")";
|
//Ìí¼Ó»ú·¿×´Ì¬µÄ¼Ç¼
|
String sql_str2 = " INSERT INTO " + Sql_Mysql.BTSStationState_Table + "(dev_id,station_id) VALUES ";
|
|
Sql_Mysql sql = new Sql_Mysql(con_pool.getConn());
|
ResultSet rs = null;
|
try {
|
rs= sql.sqlMysqlQuery(sql_str1);
|
//System.out.println(sql_str1);
|
boolean hasInsertStation = false;;
|
while(rs.next()) {
|
int dev_id = rs.getInt("FBSDeviceId");
|
int stationid = rs.getInt("StationId");
|
if( !hasInsertStation ) {
|
sql_str2 = sql_str2 + "(" + dev_id + "," + stationid + ")";
|
hasInsertStation = true;
|
}else {
|
sql_str2 = sql_str2 + ",(" + dev_id + "," + stationid + ")";
|
}
|
}
|
if(hasInsertStation) {
|
//System.out.println(sql_str2);
|
sql.sqlMysqlExecute(sql_str2);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ¼ì²é»ú·¿idÓëÉ豸id²»¶ÔÓ¦µÄ»ú·¿¼Ç¼²¢ÓèÒÔ¸ÄÕýÐÞ¸´
|
* @param gB_MysqlConnPool
|
*/
|
public static void checkBTSStationState_TableOnRam(MysqlConnPool gB_MysqlConnPool) {
|
String sql_str1 = "SELECT DISTINCT StationName,dev_id,FBSDeviceId,StationId " +
|
" FROM " + Sql_Mysql.BattInf_Table + "," + Sql_Mysql.BTSStationState_Table +
|
" WHERE tb_battinf.StationId = tb_bts_station_state.station_id " +
|
" AND tb_battinf.FBSDeviceId != tb_bts_station_state.dev_id ";
|
Sql_Mysql sql = new Sql_Mysql(gB_MysqlConnPool.getConn());
|
ResultSet rs = null;
|
try {
|
rs = sql.sqlMysqlQuery(sql_str1);
|
while(rs.next()) {
|
String stationId = rs.getString("StationId");
|
String FBSDeviceId = rs.getString("FBSDeviceId");
|
sql.sqlMysqlExecute("UPDATE " + Sql_Mysql.BTSStationState_Table + " SET dev_id = " + FBSDeviceId + " WHERE Station_Id = " + stationId);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(rs != null) {
|
try {
|
rs.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ´´½¨É豸µÄϵͳ²ÎÊý±í
|
* @param con_pool
|
* @param recreate_tb
|
*/
|
public static void createFBS9100SysParam_TableOnRam(MysqlConnPool con_pool,boolean recreate_tb) {
|
String sql_str0 = "DROP TABLE IF EXISTS " + Sql_Mysql.FBS9100SysParam_Table + "; ";
|
String sql_str1 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.FBS9100SysParam_Table
|
+ " ( `num` BIGINT NOT NULL AUTO_INCREMENT, "
|
+ "`dev_ip` varchar(20) NOT NULL DEFAULT '127.0.0.0', "
|
+ "`dev_id` BIGINT NOT NULL DEFAULT 0, "
|
+ "`op_cmd` INT NOT NULL DEFAULT 0, "
|
+ "`IPADDR` varchar(20) NOT NULL DEFAULT '127.0.0.0', "
|
+ "`SubIPADDR` varchar(20) NOT NULL DEFAULT '127.0.0.0', "
|
+ "`NetGateADDR` varchar(20) NOT NULL DEFAULT '127.0.0.0', "
|
+ "`GroupVolRange` INT NOT NULL DEFAULT 0, "
|
+ "`GroupVolSorce` INT NOT NULL DEFAULT 0, "
|
+ "`CurrentRange` INT NOT NULL DEFAULT 0, "
|
+ "`CurrentTyte` INT NOT NULL DEFAULT 0, "
|
+ "`MonomerOrder` INT NOT NULL DEFAULT 0, "
|
+ "`BackLightTime` INT NOT NULL DEFAULT 0, "
|
+ "`MACADDR` varchar(30) NOT NULL DEFAULT '0.0.0.0.0.0', "
|
+ "`LoaderCount` INT NOT NULL DEFAULT 0, "
|
+ "`DtCardCount` INT NOT NULL DEFAULT 0, "
|
+ "`PowerBreakChargeCurr` FLOAT NOT NULL DEFAULT 0, "
|
+ "`MajorBattGroupNum` INT NOT NULL DEFAULT 0, "
|
+ "`POF_BG_Boost_Cnt` INT NOT NULL DEFAULT 0, "
|
+ "`POF_BG_Boost_VolStop` FLOAT NOT NULL DEFAULT 0, "
|
+ "`POF_BG_Boost_VolStart` FLOAT NOT NULL DEFAULT 0, "
|
+ " UNIQUE INDEX index_dev_id_uniq (`dev_id`), "
|
+ " PRIMARY KEY (`num`) ) "
|
+ " ENGINE=MEMORY DEFAULT CHARSET=utf8";
|
Sql_Mysql sql = new Sql_Mysql(con_pool.getConn());
|
try {
|
if(true == recreate_tb) {
|
sql.sqlMysqlExecute(sql_str0);
|
} else {
|
//sql.sqlMysqlExecute("DELETE FROM " + Sql_Mysql.FBS9100SysParam_Table);
|
}
|
sql.sqlMysqlExecute(sql_str1);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ´´½¨61850¾ùºâ¹©µçÄ£¿éÐÅÏ¢±í
|
* @param pool
|
* @param recreate_tb
|
*/
|
public static void createTB_61850_JHState_TableOnRam(MysqlConnPool pool, boolean recreate_tb) {
|
String sql_str0 = "DROP TABLE IF EXISTS " + Sql_Mysql.TB_61850_JHState_Table + "; ";
|
|
String sql_str1 = "CREATE TABLE IF NOT EXISTS "+Sql_Mysql.TB_61850_JHState_Table+" (" +
|
" `num` bigint(20) NOT NULL DEFAULT '0'," +
|
" `dev_id` int(11) NOT NULL DEFAULT '0'," +
|
" `dev_version` varchar(255) NOT NULL DEFAULT ''," +
|
" `input_vol_total` double NOT NULL DEFAULT '0'," +
|
" `output_vol_total` double NOT NULL DEFAULT '0'," +
|
" `input_curr_total` double NOT NULL DEFAULT '0'," +
|
" `output_curr_total` double NOT NULL DEFAULT '0'," +
|
" `output_vol_one` double NOT NULL DEFAULT '0'," +
|
" `output_vol_two` double NOT NULL DEFAULT '0'," +
|
" `output_vol_three` double NOT NULL DEFAULT '0'," +
|
" `output_vol_four` double NOT NULL DEFAULT '0'," +
|
" `output_vol_five` double NOT NULL DEFAULT '0'," +
|
" `output_vol_six` double NOT NULL DEFAULT '0'," +
|
" `output_vol_seven` double NOT NULL DEFAULT '0'," +
|
" `output_vol_eight` double NOT NULL DEFAULT '0'," +
|
" `output_vol_nine` double NOT NULL DEFAULT '0'," +
|
" `output_vol_ten` double NOT NULL DEFAULT '0'," +
|
" `dev_temp` double NOT NULL DEFAULT '0'," +
|
" PRIMARY KEY (`num`)," +
|
" UNIQUE KEY `index_dev_id_unique` (`dev_id`) USING BTREE" +
|
") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
|
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
if(true == recreate_tb) {
|
sql.sqlMysqlExecute(sql_str0);
|
} else {
|
|
}
|
sql.sqlMysqlExecute(sql_str1);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
|
}
|
