package com.power.mysql;
|
|
import com.power.data.EnvmDev_Data;
|
import com.power.data.PowerData_RT_SQL;
|
import com.power.data.PowerDev_acdcData;
|
|
import java.sql.Connection;
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.sql.Statement;
|
import java.util.ArrayList;
|
|
import org.apache.logging.log4j.LogManager;
|
import org.apache.logging.log4j.Logger;
|
|
/**
|
* 创建数据库以及表格
|
* @author 军
|
*
|
*/
|
public class Sql_Mysql
|
{
|
//--------------------------------------------------------------------------------------------//
|
final public static String WEB_Site = "web_site";
|
|
final public static String DB_PWRDEV_INF = "`db_pwrdev_inf`";
|
final public static String DB_PWRDEV_ALARM = "`db_pwrdev_alarm`";
|
final public static String DB_PWRDEV_DATA_RT = "`db_pwrdev_data_rt`";
|
final public static String DB_PWRDEV_DATA_HISTORY_GW = "`db_pwrdev_data_history_gw`"; //国网电源历史实时数据库
|
|
public final static String TB_PWRDEV_INF = "tb_pwrdev_inf";
|
public final static String TB_PWRAPP_SYS = "tb_pwrapp_sys";
|
|
public final static String TB_PWRDEV_ALARM = "tb_pwrdev_alarm";
|
public final static String TB_PWRDEV_ALARM_HISTORY = "tb_pwrdev_alarm_history";
|
|
public final static String TB_PWRDEV_ACDATA = "tb_pwrdev_acdata";
|
public final static String TB_PWRDEV_DCDATA = "tb_pwrdev_dcdata";
|
public final static String TB_PWRDEV_ACDCDATA = "tb_pwrdev_acdcdata";
|
|
public final static String PWRDEV_INF_TABLE = DB_PWRDEV_INF + ".`tb_pwrdev_inf`";
|
public final static String PWRAPP_SYS_TABLE = DB_PWRDEV_INF + ".`tb_pwrapp_sys`";
|
|
public final static String PWRDEV_ALARM_TABLE = DB_PWRDEV_ALARM + ".`tb_pwrdev_alarm`";
|
public final static String PWRDEV_ALARM_HISTORY_TABLE = DB_PWRDEV_ALARM + ".`tb_pwrdev_alarm_history`";
|
public final static String PWRDEV_ALARM_PARAM_FORSX_TABLE = DB_PWRDEV_ALARM + ".`tb_pwrdev_alarm_param_forsx`"; //山西告警参数表
|
|
public final static String PWRDEV_ACDATA_TABLE = DB_PWRDEV_DATA_RT + ".`tb_pwrdev_acdata`";
|
public final static String PWRDEV_DCDATA_TABLE = DB_PWRDEV_DATA_RT + ".`tb_pwrdev_dcdata`";
|
public final static String PWRDEV_ACDCDATA_TABLE = DB_PWRDEV_DATA_RT + ".`tb_pwrdev_acdcdata`";
|
|
public final static String PWRDEV_EVENT_TABLE = DB_PWRDEV_DATA_RT + ".`tb_pwrdev_event`"; //电源事件表
|
|
|
public final static String Pwrdev_Acdcdata_Table = DB_PWRDEV_DATA_RT + ".`tb_pwrdev_acdcdata`";
|
public final static String Pwrdev_Dcdata_Table = DB_PWRDEV_DATA_RT + ".`tb_pwrdev_dcdata`";
|
public final static String Pwrdev_Acdata_Table = DB_PWRDEV_DATA_RT + ".`tb_pwrdev_acdata`";
|
|
public final static String Pwrdev_Historydata_GW_Table = DB_PWRDEV_DATA_HISTORY_GW + ".tb_pwrdev_historydata_gw_"; //电源历史实时数据记录表
|
//--------------------------------------------------------------------------------------------//
|
//--------------------------------------------------------------------------------------------//
|
public Connection mysql_con = null;
|
|
public Logger logger = LogManager.getLogger(Sql_Mysql.class);
|
|
public Sql_Mysql(Connection conn)
|
{
|
mysql_con = conn;
|
}
|
|
public void close_con()
|
{
|
try {
|
mysql_con.close();
|
} catch (SQLException e) {
|
logger.error(e.toString(), e);
|
}
|
}
|
|
public void checkAndCreateDB() throws SQLException
|
{
|
sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + DB_PWRDEV_INF);
|
sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + DB_PWRDEV_ALARM);
|
sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + DB_PWRDEV_DATA_RT);
|
|
String table_1 = "CREATE TABLE IF NOT EXISTS " + PWRDEV_INF_TABLE
|
+ " ( `num` INT NOT NULL AUTO_INCREMENT,"
|
+ "`StationId` VARCHAR(20) NOT NULL DEFAULT '0',"
|
+ "`StationName` VARCHAR(100) NOT NULL DEFAULT ' ',"
|
+ "`StationName1` VARCHAR(100) NOT NULL DEFAULT ' ',"
|
+ "`StationName2` VARCHAR(100) NOT NULL DEFAULT ' ',"
|
+ "`StationName3` VARCHAR(100) NOT NULL DEFAULT ' ',"
|
+ "`StationName4` VARCHAR(100) NOT NULL DEFAULT ' ',"
|
+ "`StationName5` VARCHAR(100) NOT NULL DEFAULT ' ',"
|
+ "`StationName6` VARCHAR(100) NOT NULL DEFAULT ' ',"
|
+ "`StationName7` VARCHAR(100) NOT NULL DEFAULT ' ',"
|
+ "`StationName8` VARCHAR(100) NOT NULL DEFAULT ' ',"
|
+ "`StationName9` VARCHAR(100) NOT NULL DEFAULT ' ',"
|
+ "`StationIP` VARCHAR(20) NOT NULL DEFAULT '0',"
|
+ "`PowerDeviceId` BIGINT NOT NULL DEFAULT '0',"
|
+ "`PowerDeviceIp` VARCHAR(50) NOT NULL DEFAULT '192.168.0.88',"
|
+ "`PowerDeviceName` VARCHAR(100) NOT NULL DEFAULT '0',"
|
+ "`ProtocolName` VARCHAR(100) NOT NULL DEFAULT '0',"
|
+ "`DeviceId` VARCHAR(20) NOT NULL DEFAULT '0',"
|
+ "`DeviceName` VARCHAR(100) NOT NULL DEFAULT '0',"
|
+ "`PowerProducer` VARCHAR(45) NOT NULL DEFAULT '0',"
|
+ "`PowerProductDate` DATE NOT NULL DEFAULT '2006-07-06',"
|
+ "`PowerInUseDate` DATE NOT NULL DEFAULT '2006-07-06',"
|
+ "INDEX index_station_id (`StationId`), "
|
+ "INDEX index_powerdev_id (`PowerDeviceId`), "
|
+ "INDEX index_station_name (`StationName`), "
|
+ "PRIMARY KEY (`num`)) "
|
+ "ENGINE=InnoDB DEFAULT CHARSET=utf8;";
|
|
String table_5 = "CREATE TABLE IF NOT EXISTS " + PWRAPP_SYS_TABLE
|
+ " (`num` int(11) NOT NULL AUTO_INCREMENT,"
|
+ "`SqlDB_Version` float NOT NULL DEFAULT '1',"
|
+ "`SqlDB_BackUpTime` datetime NOT NULL DEFAULT '2000-01-01 00:00:00',"
|
+ "`SqlDB_BackUpManual_EN` tinyint(1) NOT NULL DEFAULT '0',"
|
+ "`AppServer_Reinit_PowerData_EN` tinyint(1) NOT NULL DEFAULT '0',"
|
+ "`AppServer_Reinit_Config_EN` tinyint(1) NOT NULL DEFAULT '0',"
|
+ "`AppServer_Version` float NOT NULL DEFAULT '1',"
|
+ "`AppClient_Version` float NOT NULL DEFAULT '1',"
|
+ "`AppName` varchar(50) NOT NULL DEFAULT '0',"
|
+ "PRIMARY KEY (`num`)) "
|
+ "ENGINE=InnoDB DEFAULT CHARSET=utf8;";
|
|
String table_2_0 = "DROP TABLE IF EXISTS " + PWRDEV_ACDATA_TABLE;
|
String table_2 = "CREATE TABLE IF NOT EXISTS " + PWRDEV_ACDATA_TABLE
|
+ "( `num` bigint NOT NULL AUTO_INCREMENT ,"
|
+ "`PowerDeviceId` bigint NOT NULL DEFAULT 0 ,"
|
+ "`record_datetime` datetime NOT NULL DEFAULT '2005-1-1 00:00:00' ,"
|
+ "`acIn1_volA` float NOT NULL DEFAULT 0 ,"
|
+ "`acIn1_volB` float NOT NULL DEFAULT 0 ,"
|
+ "`acIn1_volC` float NOT NULL DEFAULT 0 ,"
|
+ "`acIn1_currA` float NOT NULL DEFAULT 0 ,"
|
+ "`acIn1_currB` float NOT NULL DEFAULT 0 ,"
|
+ "`acIn1_currC` float NOT NULL DEFAULT 0 ,"
|
+ "`acIn2_volA` float NOT NULL DEFAULT 0 ,"
|
+ "`acIn2_volB` float NOT NULL DEFAULT 0 ,"
|
+ "`acIn2_volC` float NOT NULL DEFAULT 0 ,"
|
+ "`acIn2_currA` float NOT NULL DEFAULT 0 ,"
|
+ "`acIn2_currB` float NOT NULL DEFAULT 0 ,"
|
+ "`acIn2_currC` float NOT NULL DEFAULT 0 ,"
|
+ "`temprature` float NOT NULL DEFAULT 0 ,"
|
+ "`is_acIn1_over_volA` tinyint(1) NOT NULL DEFAULT 0 ,"
|
+ "`is_acIn1_under_volA` tinyint(1) NOT NULL DEFAULT 0 ,"
|
+ "`is_acIn1_less_A` tinyint(1) NOT NULL DEFAULT 0 ,"
|
+ "`is_acIn1_over_volB` tinyint(1) NOT NULL DEFAULT 0 ,"
|
+ "`is_acIn1_under_volB` tinyint(1) NOT NULL ,"
|
+ "`is_acIn1_less_B` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn1_over_volC` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn1_under_volC` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn1_less_C` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn1_thunder_err` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn2_over_volA` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn2_under_volA` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn2_less_A` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn2_over_volB` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn2_under_volB` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn2_less_B` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn2_over_volC` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn2_under_volC` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn2_less_C` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn2_thunder_err` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn1_powerdown` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn1_monitorerr` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn2_powerdown` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn1_trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn2_trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_fsw1_sw1trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_fsw1_sw2trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_fsw1_sw3trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_fsw1_sw4trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_fsw1_sw5trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_fsw1_sw6trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_fsw1_sw7trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_fsw1_sw8trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_fsw1_sw9trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_fsw2_sw1trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_fsw2_sw2trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_fsw2_sw3trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_fsw2_sw4trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_fsw2_sw5trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_fsw2_sw6trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_fsw2_sw7trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_fsw2_sw8trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_fsw2_sw9trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_total_alarm` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "INDEX index_powerdev_id (`PowerDeviceId`), "
|
+ "PRIMARY KEY (`num`)) "
|
+ "ENGINE=InnoDB DEFAULT CHARSET=utf8;";
|
|
String table_3_0 = "DROP TABLE IF EXISTS " + PWRDEV_ACDCDATA_TABLE;
|
String table_3 = "CREATE TABLE IF NOT EXISTS " + PWRDEV_ACDCDATA_TABLE
|
+ "( `num` bigint NOT NULL AUTO_INCREMENT ,"
|
+ "`PowerDeviceId` bigint NOT NULL DEFAULT 0 ,"
|
+ "`record_datetime` datetime NOT NULL DEFAULT '2005-1-1 00:00:00' ,"
|
+ "`acIn1_volA` float NOT NULL DEFAULT 0 ,"
|
+ "`acIn1_volB` float NOT NULL DEFAULT 0 ,"
|
+ "`acIn1_volC` float NOT NULL DEFAULT 0 ,"
|
+ "`acIn2_volA` float NOT NULL DEFAULT 0 ,"
|
+ "`acIn2_volB` float NOT NULL DEFAULT 0 ,"
|
+ "`acIn2_volC` float NOT NULL DEFAULT 0 ,"
|
|
/**
|
* 2022-07-25 lijun 新增字段
|
*/
|
+ "`acIn2_currA` float NOT NULL DEFAULT '0',"
|
+ "`acIn2_currB` float NOT NULL DEFAULT '0',"
|
+ "`acIn2_currC` float NOT NULL DEFAULT '0',"
|
|
|
|
+ "`acIn1_switch_st` int NOT NULL DEFAULT '0' COMMENT '交流输入1分合闸状态:0---未知(用于兼容以前的)，1-分闸，2-合闸，3-脱扣',"
|
+ "`acIn2_switch_st` int NOT NULL DEFAULT '0' COMMENT '交流输入2分合闸状态 :0---未知(用于兼容以前的)，1-分闸，2-合闸，3-脱扣',"
|
|
|
/**
|
* 2022-08-05 lijun 新增字段
|
*/
|
+ "`acOut_volA` float NOT NULL DEFAULT 0 ,"
|
+ "`acOut_volB` float NOT NULL DEFAULT 0 ,"
|
+ "`acOut_volC` float NOT NULL DEFAULT 0 ,"
|
+ "`acOut_currA` float NOT NULL DEFAULT 0 ,"
|
+ "`acOut_currB` float NOT NULL DEFAULT 0 ,"
|
+ "`acOut_currC` float NOT NULL DEFAULT 0 ,"
|
+ "`dcOut_vol` float NOT NULL DEFAULT 0 ,"
|
+ "`dcOut_curr` float NOT NULL DEFAULT 0 ,"
|
+ "`battgroup1_vol` float NOT NULL DEFAULT 0 ,"
|
+ "`battgroup1_curr` float NOT NULL DEFAULT 0 ,"
|
+ "`battgroup2_vol` float NOT NULL DEFAULT 0 ,"
|
+ "`battgroup2_curr` float NOT NULL DEFAULT 0 ,"
|
+ "`temprature` float NOT NULL DEFAULT 0 ,"
|
+ "`m_outputvol` float NOT NULL DEFAULT 0 ,";
|
|
String tmpstr;
|
for(int i=0;i<PowerDev_acdcData.AC_DC_NUM_IN_SYS;i++)
|
{
|
tmpstr = "`m"+(i+1)+"_outCurr` "+" float NOT NULL DEFAULT 0 ,";
|
table_3 += tmpstr;
|
}
|
|
table_3 += "`is_acIn1_over_volA` tinyint(1) NOT NULL DEFAULT 0 ,"
|
+ "`is_acIn1_under_volA` tinyint(1) NOT NULL DEFAULT 0 ,"
|
+ "`is_acIn1_less_A` tinyint(1) NOT NULL DEFAULT 0 ,"
|
+ "`is_acIn1_over_volB` tinyint(1) NOT NULL DEFAULT 0 ,"
|
+ "`is_acIn1_under_volB` tinyint(1) NOT NULL DEFAULT 0,"
|
+ "`is_acIn1_less_B` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn1_over_volC` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn1_under_volC` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn1_less_C` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn1_thunder_err` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn1_powerdown` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn2_over_volA` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn2_under_volA` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn2_less_A` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn2_over_volB` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn2_under_volB` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn2_less_B` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn2_over_volC` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn2_under_volC` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn2_less_C` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn2_thunder_err` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn2_powerdown` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn_monitorerr` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_dcOut_over_vol` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_dcOut_under_vol` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_battgroup_down` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_jun_charge` tinyint(1) NOT NULL DEFAULT 0, ";
|
|
for(int i=0;i<PowerDev_acdcData.AC_DC_NUM_IN_SYS;i++)
|
{
|
tmpstr = "`is_acdcMod"+(i+1)+"_err` "+" tinyint(1) NOT NULL DEFAULT 0, ";
|
table_3 += tmpstr;
|
|
tmpstr = "`is_acdcMod"+(i+1)+"_off` "+" tinyint(1) NOT NULL DEFAULT 1, ";
|
table_3 += tmpstr;
|
}
|
|
table_3 += "`is_acIn1_trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn2_trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_loader_fuse` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_battgroup1_fuse` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_battgroup2_fuse` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_total_alarm` tinyint(1) NOT NULL DEFAULT 0, ";
|
//+ "INDEX index_powerdev_id (`PowerDeviceId`), "
|
//+ "PRIMARY KEY (`num`)) "
|
//+ "ENGINE=InnoDB DEFAULT CHARSET=utf8;";
|
//----------------------------------------------------------------
|
//----------------------------------------------------------------
|
table_3 += "`acIn1_currA` float NOT NULL DEFAULT 0 ,"
|
+ "`acIn1_currB` float NOT NULL DEFAULT 0 ,"
|
+ "`acIn1_currC` float NOT NULL DEFAULT 0 ,"
|
+ "`loader_curr` float NOT NULL DEFAULT 0 ,"
|
+ "`battgroup1_ah` float NOT NULL DEFAULT 0 ,"
|
+ "`battgroup1_temp` float NOT NULL DEFAULT 0 ,"
|
+ "`battgroup2_ah` float NOT NULL DEFAULT 0 ,"
|
+ "`battgroup2_temp` float NOT NULL DEFAULT 0 ,"
|
+ "`acdcm_temp` float NOT NULL DEFAULT 0 ,";
|
for(int i=0;i<PowerDev_acdcData.AC_DC_NUM_IN_SYS;i++)
|
{
|
tmpstr = "`m"+(i+1)+"_out_vol` "+" float NOT NULL DEFAULT 0 ,";
|
table_3 += tmpstr;
|
}
|
|
table_3 += "`is_acb1_over_currA` tinyint(1) NOT NULL DEFAULT 0 ,"
|
+ "`is_acb1_over_currB` tinyint(1) NOT NULL DEFAULT 0 ,"
|
+ "`is_acb1_over_currC` tinyint(1) NOT NULL DEFAULT 0 ,"
|
+ "`is_acIn1_poweron` tinyint(1) NOT NULL DEFAULT 0 ,"
|
+ "`is_acIn2_poweron` tinyint(1) NOT NULL DEFAULT 0,"
|
+ "`is_dcOut_over_curr` tinyint(1) NOT NULL DEFAULT 0, "
|
//+ "`is_batt1_under_vol` tinyint(1) NOT NULL DEFAULT 0, "
|
//+ "`is_batt2_under_vol` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_battgroup1_state` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_battgroup2_state` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_battgroup2_down` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_battgroup1_overChgCurr` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_battgroup2_overChgCurr` tinyint(1) NOT NULL DEFAULT 0, ";
|
for(int i=0;i<PowerDev_acdcData.AC_DC_NUM_IN_SYS;i++)
|
{
|
tmpstr = "`is_acdcMod"+(i+1)+"_overVol` "+" tinyint(1) NOT NULL DEFAULT 0, ";
|
table_3 += tmpstr;
|
tmpstr = "`is_acdcMod"+(i+1)+"_underVol` "+" tinyint(1) NOT NULL DEFAULT 0, ";
|
table_3 += tmpstr;
|
tmpstr = "`is_acdcMod"+(i+1)+"_overCurr` "+" tinyint(1) NOT NULL DEFAULT 0, ";
|
table_3 += tmpstr;
|
tmpstr = "`is_acdcMod"+(i+1)+"_acErr` "+" tinyint(1) NOT NULL DEFAULT 0, ";
|
table_3 += tmpstr;
|
tmpstr = "`is_acdcMod"+(i+1)+"_limitCurr` "+" tinyint(1) NOT NULL DEFAULT 0, ";
|
table_3 += tmpstr;
|
}
|
|
table_3 += "`is_acIn1_fuse` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn2_fuse` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn1_unbanlance` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn2_unbanlance` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn1_freqerr` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn2_freqerr` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_battgroup1_volLow` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_battgroup2_volLow` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_dcOut_total_alarm` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn_total_alarm` tinyint(1) NOT NULL DEFAULT 0, ";
|
|
//1+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
|
for(int i=0;i<PowerDev_acdcData.AC_DC_NUM_IN_SYS;i++){
|
tmpstr = "`is_acdcMod"+(i+1)+"_junChg` "+" tinyint(1) NOT NULL DEFAULT 0, ";
|
table_3 += tmpstr;
|
}
|
|
table_3 +=" `is_powerdev_commErr` tinyint(1) NOT NULL DEFAULT 0, ";
|
|
table_3 += "`userAcIn1_volA` float NOT NULL DEFAULT 0 ,"
|
+ "`userAcIn1_volB` float NOT NULL DEFAULT 0 ,"
|
+ "`userAcIn1_volC` float NOT NULL DEFAULT 0 ,"
|
+ "`userAcIn1_currA` float NOT NULL DEFAULT 0 ,"
|
+ "`userAcIn1_currB` float NOT NULL DEFAULT 0 ,"
|
+ "`userAcIn1_currC` float NOT NULL DEFAULT 0 ,"
|
+ "`userAcIn1_freq` float NOT NULL DEFAULT 0 ,"
|
+ "`userAcIn2_volA` float NOT NULL DEFAULT 0 ,"
|
+ "`userAcIn2_volB` float NOT NULL DEFAULT 0 ,"
|
+ "`userAcIn2_volC` float NOT NULL DEFAULT 0 ,"
|
+ "`userAcIn2_currA` float NOT NULL DEFAULT 0 ,"
|
+ "`userAcIn2_currB` float NOT NULL DEFAULT 0 ,"
|
+ "`userAcIn2_currC` float NOT NULL DEFAULT 0 ,"
|
+ "`userAcIn2_freq` float NOT NULL DEFAULT 0 ,";
|
|
for(int i=0;i<EnvmDev_Data.ENVM_TEMP_CNT_MAX;i++)
|
{
|
tmpstr = "`m_temper"+(i+1)+"` "+" float NOT NULL DEFAULT 0 ,";
|
table_3 += tmpstr;
|
}
|
|
for(int i=0;i<EnvmDev_Data.ENVM_HUMI_CNT_MAX;i++)
|
{
|
tmpstr = "`m_humidity"+(i+1)+"` "+" float NOT NULL DEFAULT 0 ,";
|
table_3 += tmpstr;
|
}
|
|
table_3 +=" `userAcIn1_PowerDown` tinyint(1) NOT NULL DEFAULT 0, "
|
+ " `userAcIn1_CommErr` tinyint(1) NOT NULL DEFAULT 0, "
|
+ " `userAcIn2_PowerDown` tinyint(1) NOT NULL DEFAULT 0, "
|
+ " `userAcIn2_CommErr` tinyint(1) NOT NULL DEFAULT 0, ";
|
|
for(int i=0;i<EnvmDev_Data.ENVM_TEMP_CNT_MAX;i++)
|
{
|
//tmpstr = "`is_TempH_Alarm"+(i+1)+"` "+" tinyint(1) NOT NULL DEFAULT 0, ";
|
//table_3 += tmpstr;
|
//tmpstr = "`is_TempL_Alarm"+(i+1)+"` "+" tinyint(1) NOT NULL DEFAULT 0, ";
|
//table_3 += tmpstr;
|
tmpstr = "`is_Temp_CommErr"+(i+1)+"` "+" tinyint(1) NOT NULL DEFAULT 0, ";
|
table_3 += tmpstr;
|
}
|
|
for(int i=0;i<EnvmDev_Data.ENVM_HUMI_CNT_MAX;i++)
|
{
|
//tmpstr = "`is_HumiH_Alarm"+(i+1)+"` "+" tinyint(1) NOT NULL DEFAULT 0, ";
|
//table_3 += tmpstr;
|
//tmpstr = "`is_HumiL_Alarm"+(i+1)+"` "+" tinyint(1) NOT NULL DEFAULT 0, ";
|
//table_3 += tmpstr;
|
tmpstr = "`is_Humi_CommErr"+(i+1)+"` "+" tinyint(1) NOT NULL DEFAULT 0, ";
|
table_3 += tmpstr;
|
}
|
|
for(int i=0;i<EnvmDev_Data.ENVM_SMOKE_CNT_MAX;i++)
|
{
|
tmpstr = "`is_Smoke_Alarm"+(i+1)+"` "+" tinyint(1) NOT NULL DEFAULT 0, ";
|
table_3 += tmpstr;
|
tmpstr = "`is_Smoke_CommErr"+(i+1)+"` "+" tinyint(1) NOT NULL DEFAULT 0, ";
|
table_3 += tmpstr;
|
}
|
|
table_3 +=" INDEX index_powerdev_id (`PowerDeviceId`), "
|
+ "PRIMARY KEY (`num`)) "
|
+ "ENGINE=InnoDB DEFAULT CHARSET=utf8;";
|
|
String table_4_0 = "DROP TABLE IF EXISTS " + PWRDEV_DCDATA_TABLE;
|
String table_4 = "CREATE TABLE IF NOT EXISTS " + PWRDEV_DCDATA_TABLE
|
+ "( `num` bigint NOT NULL AUTO_INCREMENT ,"
|
+ "`PowerDeviceId` bigint NOT NULL DEFAULT 0 ,"
|
+ "`record_datetime` datetime NOT NULL DEFAULT '2005-1-1 00:00:00' ,"
|
+ "`dcIn1_vol` float NOT NULL DEFAULT 0 ,"
|
+ "`dcIn2_vol` float NOT NULL DEFAULT 0 ,"
|
+ "`dcOut1_vol` float NOT NULL DEFAULT 0 ,"
|
+ "`dcOut1_curr` float NOT NULL DEFAULT 0 ,"
|
+ "`dcOut2_vol` float NOT NULL DEFAULT 0 ,"
|
+ "`dcOut2_curr` float NOT NULL DEFAULT 0 ,"
|
+ "`temprature` float NOT NULL DEFAULT 0 ,"
|
|
+ "`is_dc1_over_vol` tinyint(1) NOT NULL DEFAULT 0 ,"
|
+ "`is_dc1_under_vol` tinyint(1) NOT NULL DEFAULT 0 ,"
|
+ "`is_dc2_over_vol` tinyint(1) NOT NULL DEFAULT 0 ,"
|
+ "`is_dc2_under_vol` tinyint(1) NOT NULL DEFAULT 0 ,"
|
+ "`is_dc_monitorerr` tinyint(1) NOT NULL ,"
|
+ "`is_tempalarm` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn1_trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_acIn2_trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_fsw1_sw1trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_fsw1_sw2trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_fsw1_sw3trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_fsw1_sw4trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_fsw1_sw5trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_fsw1_sw6trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_fsw1_sw7trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_fsw1_sw8trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_fsw1_sw9trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_fsw2_sw1trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_fsw2_sw2trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_fsw2_sw3trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_fsw2_sw4trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_fsw2_sw5trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_fsw2_sw6trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_fsw2_sw7trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_fsw2_sw8trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_fsw2_sw9trip` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "`is_total_alarm` tinyint(1) NOT NULL DEFAULT 0, "
|
+ "INDEX index_powerdev_id (`PowerDeviceId`), "
|
+ "PRIMARY KEY (`num`)) "
|
+ "ENGINE=InnoDB DEFAULT CHARSET=utf8;";
|
|
String table_6 = "CREATE TABLE IF NOT EXISTS " + PWRDEV_ALARM_TABLE
|
+ "( `num` BIGINT NOT NULL AUTO_INCREMENT, "
|
+ "`record_id` BIGINT NOT NULL DEFAULT 0, "
|
+ "`PowerDeviceId` BIGINT NOT NULL DEFAULT 0, "
|
+ "`alm_type` INT NOT NULL DEFAULT 0, "
|
+ "`alm_level` INT NOT NULL DEFAULT 0, "
|
+ "`alm_start_time` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', "
|
+ "`alm_end_time` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', "
|
+ "`alm_value` float NOT NULL DEFAULT '0',"
|
+ "`alm_is_confirmed` tinyint(1) NOT NULL DEFAULT false, "
|
+ "`alm_confirmed_time` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', "
|
+ "`alm_cleared_type` INT NOT NULL DEFAULT 0, "
|
+ "`usr_Id` INT NOT NULL DEFAULT 0, "
|
+ "`alm_trigger` int(11) NOT NULL DEFAULT '0' COMMENT '告警来源[0-来源于点表；1-来源于电源告警参数]' "
|
+ "`alm_severity` int(11) NOT NULL DEFAULT '0' COMMENT '告警重要等级[0:重要；1:紧急]' "
|
+ "INDEX index_record_id (`record_id`), "
|
+ "INDEX index_pwrdev_id (`PowerDeviceId`), "
|
+ "INDEX index_alm_type (`alm_type`), "
|
+ "INDEX index_alm_start_time (`alm_start_time`), "
|
+ "INDEX index_alm_cleared_type (`alm_cleared_type`), "
|
+ "PRIMARY KEY (`num`)) "
|
+ "ENGINE=InnoDB DEFAULT CHARSET=utf8;";
|
|
String table_7 = "CREATE TABLE IF NOT EXISTS " + PWRDEV_ALARM_HISTORY_TABLE
|
+ "( `num` BIGINT NOT NULL AUTO_INCREMENT, "
|
+ "`record_id` BIGINT NOT NULL DEFAULT 0, "
|
+ "`PowerDeviceId` BIGINT NOT NULL DEFAULT 0, "
|
+ "`alm_type` INT NOT NULL DEFAULT 0, "
|
+ "`alm_level` INT NOT NULL DEFAULT 0, "
|
+ "`alm_start_time` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', "
|
+ "`alm_end_time` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', "
|
+ "`alm_value` float NOT NULL DEFAULT '0',"
|
+ "`alm_is_confirmed` tinyint(1) NOT NULL DEFAULT false, "
|
+ "`alm_confirmed_time` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', "
|
+ "`alm_cleared_type` INT NOT NULL DEFAULT 0, "
|
+ "`usr_Id` INT NOT NULL DEFAULT 0, "
|
+ "INDEX index_record_id (`record_id`), "
|
+ "INDEX index_pwrdev_id (`PowerDeviceId`), "
|
+ "INDEX index_alm_type (`alm_type`), "
|
+ "INDEX index_alm_start_time (`alm_start_time`), "
|
+ "INDEX index_alm_cleared_type (`alm_cleared_type`), "
|
+ "PRIMARY KEY (`num`)) "
|
+ "ENGINE=InnoDB DEFAULT CHARSET=utf8;";
|
|
String table_8 = " CREATE TABLE IF NOT EXISTS "+Sql_Mysql.PWRDEV_EVENT_TABLE+" (" +
|
" `num` bigint(20) NOT NULL AUTO_INCREMENT," +
|
" `power_device_id` int(11) NOT NULL DEFAULT '0' COMMENT '电源ID'," +
|
" `record_time` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '记录事件'," +
|
" `now_state` int(11) NOT NULL DEFAULT '0' COMMENT '当前状态'," +
|
" `last_state` int(11) NOT NULL DEFAULT '0' COMMENT '上一次状态'," +
|
" `event_type` int(11) NOT NULL DEFAULT '0' COMMENT '事件类型'," +
|
" `event_txt` text NOT NULL COMMENT '事件描述'," +
|
" PRIMARY KEY (`num`)" +
|
") ENGINE=InnoDB AUTO_INCREMENT=246 DEFAULT CHARSET=utf8;";
|
|
|
//logger.info(table_1);
|
//logger.info(table_2);
|
//logger.info(table_3);
|
//logger.info(table_4);
|
|
sqlMysqlExecute(table_2_0);
|
sqlMysqlExecute(table_3_0);
|
sqlMysqlExecute(table_4_0);
|
|
sqlMysqlExecute(table_1);
|
sqlMysqlExecute(table_5);
|
|
sqlMysqlExecute(table_6);
|
sqlMysqlExecute(table_7);
|
sqlMysqlExecute(table_8);
|
|
|
//sqlMysqlExecute(table_2);
|
PowerData_RT_SQL.createPwrdev_Acdata_Table(this.mysql_con, false);
|
//sqlMysqlExecute(table_3);
|
PowerData_RT_SQL.createPwrdev_Acdcdata_Table(this.mysql_con, false);
|
//sqlMysqlExecute(table_4);
|
PowerData_RT_SQL.createPwrdev_Dcdata_Table(this.mysql_con, false);
|
}
|
|
//使用db数据库
|
public void sqlMysqlUseDB(String db) throws SQLException
|
{
|
sqlMysqlExecute("USE " + db);
|
}
|
|
/**
|
* 检查tb表是否存在
|
* @param tb
|
* @return
|
* @throws SQLException
|
*/
|
public boolean sqlMysqlCheckIfTableExist(String tb) throws SQLException
|
{
|
String sql_str = "SHOW TABLES LIKE '" + tb + "'";
|
ResultSet res = sqlMysqlQuery(sql_str);
|
boolean exist = false;
|
while(res.next())
|
{
|
exist = true;
|
break;
|
}
|
|
return exist;
|
}
|
|
//执行sql语句
|
public void sqlMysqlExecute(String sql_str) throws SQLException
|
{
|
Statement sql = mysql_con.createStatement();
|
sql.setQueryTimeout(30);
|
sql.execute(sql_str);
|
}
|
|
|
//在事物中执行多条sql语句
|
public boolean makeManualCommit(ArrayList<String> al_sql_strs)
|
{
|
boolean exe_res = true;
|
try {
|
mysql_con.setAutoCommit(false);
|
|
for(int n=0; n<al_sql_strs.size(); n++) {
|
sqlMysqlExecute(al_sql_strs.get(n));
|
}
|
|
if(true == exe_res) {
|
mysql_con.commit();
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
exe_res = false;
|
} finally {
|
try {
|
if(false == exe_res) {
|
mysql_con.rollback();
|
}
|
mysql_con.setAutoCommit(true);
|
} catch (SQLException e1) {
|
// TODO Auto-generated catch block
|
e1.printStackTrace();
|
}
|
}
|
|
return exe_res;
|
}
|
|
//根据sql语句执行sql查询语句
|
public ResultSet sqlMysqlQuery(String sql_str)
|
{
|
ResultSet res = null;
|
try
|
{
|
Statement sql = mysql_con.createStatement();
|
sql.setQueryTimeout(30);
|
String query = sql_str;
|
res = sql.executeQuery(query);
|
}
|
catch(SQLException ex)
|
{
|
//System.err.println("SQLException:" + ex.getMessage());
|
logger.error(ex.toString(), ex);
|
}
|
|
return res;
|
}
|
|
//根据sql语句执行sql更新语句
|
public void sqlMysqlUpdate(String sql_str)
|
{
|
try
|
{
|
Statement sql = mysql_con.createStatement();
|
sql.setQueryTimeout(30);
|
String query = sql_str;
|
sql.execute(query);
|
}
|
catch(SQLException ex)
|
{
|
//System.out.println("SQLException:" + ex.getMessage());
|
logger.error(ex.toString(), ex);
|
}
|
}
|
}
|
