package com.sql;
|
|
import com.base.Com;
|
import java.io.PrintStream;
|
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;
|
|
public class Sql_Mysql {
|
public static final String DB_AppSys = "`db_app_sys`";
|
public static final String DB_BATT_INF = "`db_battinf`";
|
public static final String DB_BATT_TESTDATA = "`db_batt_testdata`";
|
public static final String DB_PARAM = "`db_param`";
|
public static final String DB_ALARM = "`db_alarm`";
|
public static final String DB_USER = "`db_user`";
|
public static final String DB_RamDB = "`db_ram_db`";
|
public static final String DB_RamDB_BATT_RT = "`db_ram_batt_rt`";
|
final public static String DB_BATT_HISTORY = "`db_batt_history`";
|
public static final String WEB_Site = "web_site";
|
public static final String TB_HardDevSmsState = "tb_hard_dev_sms_state";
|
public static final String TB_AppSys = "tb_app_sys";
|
public static final String TB_BattTestDataInf = "tb_batttestdata_inf";
|
public static final String TB_UserInf = "tb_user_inf";
|
public static final String TB_UserLog = "tb_user_log";
|
public static final String TB_UserBattGroup_BaoJiGroup = "tb_user_battgroup_baojigroup";
|
public static final String TB_UserBattGroup_BaoJiGroup_BattGroup = "tb_user_battgroup_baojigroup_battgroup";
|
public static final String TB_UserBattGroup_BaoJiGroup_Usr = "tb_user_battgroup_baojigroup_usr";
|
public static final String TB_UserTaskTemplate = "tb_user_task_batt_template";
|
public static final String TB_UserTask = "tb_user_task";
|
public static final String TB_UserPaiXiu = "tb_user_paixiu";
|
public static final String TB_UserListForTask = "tb_user_task_user_list";
|
public static final String TB_UserTaskParam = "tb_user_task_param";
|
public static final String TB_UserBattCkeckTask = "tb_user_task_batt_check";
|
public static final String TB_UserBattTestTask = "tb_user_task_batt_test";
|
public static final String TB_UserBattTaskChange = "tb_user_task_change";
|
public static final String TB_UserSms = "tb_user_sms";
|
public static final String TB_UserSmsFormat = "tb_user_sms_format";
|
public static final String TB_UserPermitGroup = "tb_user_permitgroup";
|
public static final String TB_UserPermitGroupData = "tb_user_permitgroup_data";
|
public static final String TB_UserJieJiaRi = "tb_user_jiejiari";
|
public static final String ServerState_Table = "`db_ram_db`.`tb_server_state`";
|
public static final String BattRtData_Table = "`db_ram_db`.`tb_batt_rtdata`";
|
public static final String BattRtState_Table = "`db_ram_db`.`tb_batt_rtstate`";
|
public static final String CInterfaceState_Table = "`db_ram_db`.`tb_cinterface_state`";
|
public static final String HardDevSmsState_Table = "`db_ram_db`.`tb_hard_dev_sms_state`";
|
public static final String FBS9100State_Table = "`db_ram_db`.`tb_fbs9100_state`";
|
public static final String FBS9100S_DcDcState_Table = "`db_ram_db`.`tb_fbs9100s_dcdc_state`";
|
public static final String FBS9100SetParam_Table = "`db_ram_db`.`tb_fbs9100_setparam`";
|
public static final String FBS9100S_DFUState_Table = "`db_ram_db`.`tb_fbs9100s_DFU_state`";
|
public static final String BMP7100State_Table = "`db_ram_db`.`tb_bpm7100_state`";
|
public static final String FBS9600State_Table = "`db_ram_db`.`tb_fbs9600_state`";
|
public static final String FBS9600SetParam_Table = "`db_ram_db`.`tb_fbs9600_setparam`";
|
public static final String SocketClientStateTable = "`db_ram_db`.`tb_socketclient_state`";
|
public static final String RamDB_CMCC_POWER_Data = "`db_ram_db`.`cmcc_power_data`";
|
public static final String AppSys_Table = "`db_app_sys`.`tb_app_sys`";
|
public static final String FBSDevStateChangeInf_Table = "`db_app_sys`.`tb_fbsdev_statechange_inf`";
|
public static final String BattInf_Table = "`db_battinf`.`tb_battinf`";
|
public static final String BattCurrInf_Table = "`db_battinf`.`tb_battcurr_inf`";
|
public static final String BattMainInf_Table = "`db_battinf`.`tb_batt_maint_inf`";
|
public static final String BattMainProcess_Table = "`db_battinf`.`tb_batt_maint_process`";
|
public static final String BattTestDataInf_Table = "`db_batt_testdata`.`tb_batttestdata_inf`";
|
public static final String BattResData_Table = "`db_batt_testdata`.`tb_BattResData`";
|
public static final String BattResDataInf_Table = "`db_batt_testdata`.`tb_battresdata_inf`";
|
public static final String UserInfTable = "`db_user`.`tb_user_inf`";
|
public static final String UserLogTable = "`db_user`.`tb_user_log`";
|
public static final String UserBattGroup_BaoJiGroup_Table = "`db_user`.`tb_user_battgroup_baojigroup`";
|
public static final String UserBattGroup_BaoJiGroup_BattGroupTable = "`db_user`.`tb_user_battgroup_baojigroup_battgroup`";
|
public static final String UserBattGroup_BaoJiGroup_UsrTable = "`db_user`.`tb_user_battgroup_baojigroup_usr`";
|
public static final String UserTaskTemplateTable = "`db_user`.`tb_user_task_batt_template`";
|
public static final String UserTaskTable = "`db_user`.`tb_user_task`";
|
public static final String UserPaiXiuTable = "`db_user`.`tb_user_paixiu`";
|
public static final String UserListForTaskTable = "`db_user`.`tb_user_task_user_list`";
|
public static final String UserTaskParamTable = "`db_user`.`tb_user_task_param`";
|
public static final String UserBattCheckTaskTable = "`db_user`.`tb_user_task_batt_check`";
|
public static final String UserBattTestTaskTable = "`db_user`.`tb_user_task_batt_test`";
|
public static final String UserBattTaskChangeTable = "`db_user`.`tb_user_task_change`";
|
public static final String UsrBattMaintCheck_Table = "`db_user`.`tb_user_battmaint_check`";
|
public static final String UsrBattMaintCheckProcess_Table = "`db_user`.`tb_user_battmaint_check_process`";
|
public static final String UserSmsTable = "`db_user`.`tb_user_sms`";
|
public static final String UserSmsFormatTable = "`db_user`.`tb_user_sms_format`";
|
public static final String UserPermitGroupTable = "`db_user`.`tb_user_permitgroup`";
|
public static final String UserPermitGroupDataTable = "`db_user`.`tb_user_permitgroup_data`";
|
public static final String UserJieJiaRiTable = "`db_user`.`tb_user_jiejiari`";
|
public static final String Tb_BattDischarge_ParamTable = "web_site.tb_battdischarge_param";
|
public static final String Tb_BattMap_InformationTable = "web_site.tb_battmap_information";
|
public static final String Tb_Batt_DevDischargeTable = "web_site.tb_batt_devdischarge";
|
public static final String Tb_BattDischarge_PlanTable = "web_site.tb_battdischarge_plan";
|
public static final String Tb_Batt_ElectricityTable = "web_site.tb_batt_electricity";
|
public static final String Tb_Batt_Devdischarge_ParamTable = "web_site.tb_batt_devdischarge_param";
|
public static final String Tb_Batt_Devdischarge_CMTable = "web_site.tb_batt_devdischarge_CM";
|
public static final String Tb_Batt_Devdischarge_CTTable = "web_site.tb_batt_devdischarge_CT";
|
public static final String Tb_Batt_Devdischarge_CUTable = "web_site.tb_batt_devdischarge_CU";
|
public static final String Tb_CustomPageTable = "web_site.tb_custompage";
|
public static final String Tb_Vip_UserTable = "web_site.tb_vip_user";
|
public static final String Tb_Batt_Param_LowTable = "web_site.tb_batt_param_low";
|
public static final String Tb_AnnounceTable = "web_site.tb_announce";
|
public static final String Tb_Process_SurveyTable = "web_site.tb_process_survey";
|
|
|
public static final String Tb_Lithium_Data_Table = DB_RamDB + ".`tb_lithium_data`";
|
public final static String Tb_Batt_RealData = DB_BATT_HISTORY + ".tb_batt_realdata_";
|
public Logger logger;
|
public Connection mysql_con;
|
|
public Sql_Mysql(Connection conn) {
|
this.mysql_con = conn;
|
logger = LogManager.getLogger(this.getClass());
|
}
|
|
public void close_con() {
|
try {
|
this.mysql_con.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
|
public void checkAndCreateDB() throws SQLException {
|
sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS `db_app_sys`");
|
sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS `db_battinf`");
|
sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS `db_batt_testdata`");
|
sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS `db_param`");
|
sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS `db_alarm`");
|
sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS `db_user`");
|
sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS `db_ram_db`");
|
|
sqlMysqlExecute("DROP DATABASE IF EXISTS `db_ram_batt_rt`");
|
|
sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS web_site");
|
|
String table_0 = "CREATE TABLE IF NOT EXISTS `db_app_sys`.`tb_app_sys` ( `num` INT NOT NULL AUTO_INCREMENT,`SqlDB_Version` FLOAT NOT NULL DEFAULT '1.0',`SqlDB_BackUpTime` DATETIME NOT NULL DEFAULT '2000-1-1 00:00:00',`SqlDB_BackUpManual_EN` BOOLEAN NOT NULL DEFAULT false, `AppServer_Version` FLOAT NOT NULL DEFAULT '1.0',`AppClient_Version` FLOAT NOT NULL DEFAULT '1.0',`AppName` VARCHAR(50) NOT NULL DEFAULT '0', PRIMARY KEY (`num`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
|
|
String table_1 = "CREATE TABLE IF NOT EXISTS `db_battinf`.`tb_battinf` ( `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',`FBSDeviceId` BIGINT NOT NULL DEFAULT '0',`FBSDeviceIp` VARCHAR(50) NOT NULL DEFAULT '192.168.0.88',`FBSDeviceName` VARCHAR(100) NOT NULL DEFAULT '0',`GroupIndexInFBSDevice` INT NOT NULL DEFAULT '0',`BattModel` VARCHAR(100) NOT NULL DEFAULT ' ',`BattGroupId` INT NOT NULL DEFAULT '0',`BattGroupNum` INT NOT NULL DEFAULT '0',`BattGroupName` VARCHAR(100) NOT NULL DEFAULT ' ',`BattGroupName1` VARCHAR(100) NOT NULL DEFAULT ' ',`BattGroupName2` VARCHAR(100) NOT NULL DEFAULT ' ',`BattGroupName3` VARCHAR(100) NOT NULL DEFAULT ' ',`BattGroupName4` VARCHAR(100) NOT NULL DEFAULT ' ',`BattGroupName5` VARCHAR(100) NOT NULL DEFAULT ' ',`BattGroupName6` VARCHAR(100) NOT NULL DEFAULT ' ',`FloatVolLevel` FLOAT NOT NULL DEFAULT '1.12',`OfflineVolLevel` FLOAT NOT NULL DEFAULT '1.06',`BattFloatCurrent` FLOAT NOT NULL DEFAULT '0',`DeviceId` VARCHAR(20) NOT NULL DEFAULT '0',`DeviceName` VARCHAR(100) NOT NULL DEFAULT '0',`MonCount` INT NOT NULL DEFAULT '0',`MonCapStd` FLOAT NOT NULL DEFAULT '0',`MonVolStd` FLOAT NOT NULL DEFAULT '0',`MonResStd` FLOAT NOT NULL DEFAULT '0',`MonSerStd` FLOAT NOT NULL DEFAULT '0',`MonTmpStd` FLOAT NOT NULL DEFAULT '0',`MonVolLowToAvg` FLOAT NOT NULL DEFAULT '0',`MonNum` INT NOT NULL DEFAULT '0',`BattProducer` VARCHAR(45) NOT NULL DEFAULT '0',`BattProductDate` DATE NOT NULL DEFAULT '2006-07-06',`BattInUseDate` DATE NOT NULL DEFAULT '2006-07-06',`BattGuarantDayCount` INT NOT NULL DEFAULT '1095',`SignalId` VARCHAR(20) NOT NULL DEFAULT '0',`CInterFaceId` INT NOT NULL DEFAULT '0',`SignalName` VARCHAR(50) NOT NULL DEFAULT '0',INDEX index_station_id (`StationId`), INDEX index_battgroup_id (`BattGroupId`), INDEX index_station_name (`StationName`), INDEX index_battgroup_name (`BattGroupName`), PRIMARY KEY (`num`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
|
|
String table_2 = "CREATE TABLE IF NOT EXISTS `db_battinf`.`tb_battcurr_inf` ( `num` INT NOT NULL AUTO_INCREMENT,`StationId` VARCHAR(20) NOT NULL DEFAULT '0',`BattGroupId` VARCHAR(20) NOT NULL DEFAULT '0',`BattGroupName` VARCHAR(100) NOT NULL DEFAULT '0',`CurrDeviceId` VARCHAR(20) NOT NULL DEFAULT '0',`CurrDeviceName` VARCHAR(100) NOT NULL DEFAULT '0',`CurrValueId` VARCHAR(20) NOT NULL DEFAULT '0',`CurrValueName` VARCHAR(100) NOT NULL DEFAULT '0',PRIMARY KEY (`num`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
|
|
String table_3 = "CREATE TABLE IF NOT EXISTS `db_battinf`.`tb_batt_maint_inf` ( `num` BIGINT NOT NULL AUTO_INCREMENT, `BattGroupId` INT NOT NULL DEFAULT 0, `usr_id` INT NOT NULL DEFAULT 0, `fault_type_id` INT NOT NULL DEFAULT 0, `fault_type` VARCHAR(50) NOT NULL DEFAULT ' ', `fault_level` INT NOT NULL DEFAULT 0, `fault_caption` VARCHAR(200) NOT NULL DEFAULT ' ', `fault_time` DATETIME NOT NULL DEFAULT '2005-1-1 00:00:00', `record_uid` INT NOT NULL DEFAULT 0, `record_time` DATETIME NOT NULL DEFAULT '2005-1-1 00:00:00', `maint_time_limit` DATETIME NOT NULL DEFAULT '2005-1-1 00:00:00', `maint_type_id` INT NOT NULL DEFAULT 0, `maint_type` VARCHAR(50) NOT NULL DEFAULT ' ', `maint_result` VARCHAR(100) NOT NULL DEFAULT ' ', `maint_done` BOOLEAN NOT NULL DEFAULT false, `maint_done_time` DATETIME DEFAULT '2005-1-1 00:00:00', `maint_close` BOOLEAN NOT NULL DEFAULT false, `master_id` INT NOT NULL DEFAULT 0, `master_audit` INT NOT NULL DEFAULT 0, `appoint_uid` INT NOT NULL DEFAULT 0, `copy_uids` VARCHAR(200) NOT NULL DEFAULT ' ', `master_check` VARCHAR(100) NOT NULL DEFAULT ' ', `remark` VARCHAR(200) NOT NULL DEFAULT ' ', INDEX index_battgroup_id (`BattGroupId`), INDEX index_user_id (`usr_id`), INDEX index_fault_type_id (`fault_type_id`), INDEX index_maint_type_id (`maint_type_id`), PRIMARY KEY (`num`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
|
|
String table_3_1 = "CREATE TABLE IF NOT EXISTS `db_battinf`.`tb_batt_maint_process` ( `num` BIGINT NOT NULL AUTO_INCREMENT, `batt_maint_rec_id` BIGINT NOT NULL DEFAULT 0, `usr_id` INT NOT NULL DEFAULT 0, `work_caption` VARCHAR(200) NOT NULL DEFAULT ' ', `work_caption_time` DATETIME DEFAULT '2005-1-1 00:00:00', INDEX index_batt_maint_rec_id (`batt_maint_rec_id`), PRIMARY KEY (`num`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
|
|
String table_3_2 = "CREATE TABLE IF NOT EXISTS `db_user`.`tb_user_battmaint_check` ( `num` BIGINT NOT NULL AUTO_INCREMENT, `StationId` INT NOT NULL DEFAULT 0, `usr_id` INT NOT NULL DEFAULT 0, `master_id` INT NOT NULL DEFAULT 0, `appoint_uid` INT NOT NULL DEFAULT 0, `copy_uids` VARCHAR(200) NOT NULL DEFAULT ' ', `task_type_id` INT NOT NULL DEFAULT 0, `task_level` INT NOT NULL DEFAULT 0, `task_caption` VARCHAR(200) NOT NULL DEFAULT ' ', `task_time_start` DATETIME NOT NULL DEFAULT '2005-1-1 00:00:00', `task_time_end` DATETIME NOT NULL DEFAULT '2005-1-1 00:00:00', `task_process` VARCHAR(200) NOT NULL DEFAULT ' ', `task_done` BOOLEAN NOT NULL DEFAULT false, `task_done_time` DATETIME DEFAULT '2005-1-1 00:00:00', `origin_usr_work_mark` INT NOT NULL DEFAULT 0, `task_done_confirm` BOOLEAN NOT NULL DEFAULT false, `master_audit` INT NOT NULL DEFAULT 0, `remark` VARCHAR(200) NOT NULL DEFAULT ' ', INDEX index_station_id (`StationId`), INDEX index_user_id (`usr_id`), INDEX index_task_type_id (`task_type_id`), PRIMARY KEY (`num`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
|
|
String table_3_2_1 = "CREATE TABLE IF NOT EXISTS `db_user`.`tb_user_battmaint_check_process` ( `num` BIGINT NOT NULL AUTO_INCREMENT, `task_rec_id` BIGINT NOT NULL DEFAULT 0, `usr_id` INT NOT NULL DEFAULT 0, `work_caption` VARCHAR(200) NOT NULL DEFAULT ' ', `work_caption_time` DATETIME DEFAULT '2005-1-1 00:00:00', INDEX index_task_rec_id (`task_rec_id`), PRIMARY KEY (`num`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
|
|
String table_4 = "CREATE TABLE IF NOT EXISTS `db_batt_testdata`.`tb_batttestdata_inf` ( `num` BIGINT NOT NULL AUTO_INCREMENT, `BattGroupId` INT NOT NULL DEFAULT 0, `test_record_count` INT NOT NULL DEFAULT 0, `test_record_count_ex` INT NOT NULL DEFAULT 0, `test_type` INT NOT NULL DEFAULT 0, `record_time_interval` INT NOT NULL DEFAULT 0, `data_new` BOOLEAN NOT NULL DEFAULT false, `data_available` BOOLEAN NOT NULL DEFAULT false, `record_num` INT NOT NULL DEFAULT 0, `test_starttime` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', `test_starttime_ex` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', `test_starttype` INT NOT NULL DEFAULT 0, `record_time` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', `test_timelong` INT NOT NULL DEFAULT 0, `test_stoptype` INT NOT NULL DEFAULT 0, `group_vol` FLOAT NOT NULL DEFAULT 0, `test_curr` FLOAT NOT NULL DEFAULT 0, `test_cap` FLOAT NOT NULL DEFAULT 0, `max_monnum` INT NOT NULL DEFAULT 0, `max_monvol` FLOAT NOT NULL DEFAULT 0, `min_monnum` INT NOT NULL DEFAULT 0, `min_monvol` FLOAT NOT NULL DEFAULT 0, `mon_num` INT NOT NULL DEFAULT 0, `mon_vol` FLOAT NOT NULL DEFAULT 0, `upload_usr_id` INT NOT NULL DEFAULT 0, `upload_data_ext` INT NOT NULL DEFAULT 0, `upload_client_type` INT NOT NULL DEFAULT 0, INDEX index_battgroup_id (`BattGroupId`), INDEX index_test_record_count (`test_record_count`), INDEX index_test_type (`test_type`), INDEX index_test_starttime (`test_starttime`), PRIMARY KEY (`num`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
|
|
String table_5 = "CREATE TABLE IF NOT EXISTS `db_batt_testdata`.`tb_battresdata_inf` ( `num` BIGINT NOT NULL AUTO_INCREMENT, `BattGroupId` INT NOT NULL DEFAULT 0, `test_record_count` INT NOT NULL DEFAULT 0, `test_record_count_ex` INT NOT NULL DEFAULT 0, `test_type` INT NOT NULL DEFAULT 0, `data_new` BOOLEAN NOT NULL DEFAULT false, `data_available` BOOLEAN NOT NULL DEFAULT false, `test_starttime` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', `test_starttime_ex` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', `test_devtype` INT NOT NULL DEFAULT 0, `record_time` DATETIME NOT NULL DEFAULT '1982-01-01 00:00:00', `group_vol` FLOAT NOT NULL DEFAULT 0, `test_curr` FLOAT NOT NULL DEFAULT 0, `upload_usr_id` INT NOT NULL DEFAULT 0, `upload_client_type` INT NOT NULL DEFAULT 0, INDEX index_battgroup_id (`BattGroupId`), INDEX index_test_record_count (`test_record_count`), INDEX index_test_starttime (`test_starttime`), PRIMARY KEY (`num`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
|
|
String table_6 = "DROP TABLE IF EXISTS `db_ram_db`.`tb_socketclient_state`; ";
|
String table_7 = "CREATE TABLE IF NOT EXISTS `db_ram_db`.`tb_socketclient_state`(`num` BIGINT NOT NULL AUTO_INCREMENT, `uId` INT NOT NULL DEFAULT 0, `client_type` INT NOT NULL DEFAULT 0, `client_id` BIGINT NOT NULL DEFAULT 0, `BattGroupId` INT NOT NULL DEFAULT 0, `client_is_connected` BOOLEAN NOT NULL DEFAULT false, `client_ip` VARCHAR(64) NOT NULL DEFAULT ' ', `client_conn_time` DATETIME NOT NULL DEFAULT '1980-01-01', `client_disconn_time` DATETIME NOT NULL DEFAULT '1980-01-01', INDEX index_user_id (`uId`), INDEX index_client_type (`client_type`), PRIMARY KEY (`num`)) ENGINE=MEMORY DEFAULT CHARSET=utf8;";
|
|
String table_8 = "CREATE TABLE IF NOT EXISTS `db_app_sys`.`tb_fbsdev_statechange_inf`(`num` BIGINT NOT NULL AUTO_INCREMENT, `dev_id` INT NOT NULL DEFAULT 0, `rec_time` DATETIME NOT NULL DEFAULT '1980-01-01', `last_stat` INT NOT NULL DEFAULT 0, `now_stat` INT NOT NULL DEFAULT 0, `state_change_reason` INT NOT NULL DEFAULT 0, `dev_alarm` INT NOT NULL DEFAULT 0, INDEX index_dev_id (`dev_id`), PRIMARY KEY (`num`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
|
|
sqlMysqlUseDB("`db_app_sys`");
|
if (!sqlMysqlCheckIfTableExist("tb_app_sys")) {
|
sqlMysqlExecute(table_0);
|
sqlMysqlExecute("INSERT INTO `db_app_sys`.`tb_app_sys` (AppName) VALUES ('蓄电池组综合管理系统')");
|
}
|
|
ResultSet rest = sqlMysqlQuery("SELECT AppServer_Version FROM `db_app_sys`.`tb_app_sys`");
|
if (rest.next()) {
|
float ser_version = rest.getFloat(1);
|
if (ser_version < 1.387D) {
|
sqlMysqlExecute("UPDATE `db_app_sys`.`tb_app_sys` SET AppServer_Version=1.388");
|
sqlMysqlExecute("USE db_batt_testdata");
|
sqlMysqlExecute(
|
"UPDATE tb_batttestdata_inf SET test_starttime=date_sub(test_starttime, interval 1 month) WHERE upload_usr_id>0");
|
}
|
|
}
|
|
ResultSet res0 = sqlMysqlQuery(
|
"SELECT * FROM information_schema.columns WHERE table_schema='db_app_sys' AND table_name='tb_app_sys' AND column_name='SqlDB_BackUpTime'");
|
|
if (!res0.next()) {
|
sqlMysqlExecute(
|
"ALTER TABLE `db_app_sys`.`tb_app_sys` ADD COLUMN `SqlDB_BackUpTime` DATETIME NOT NULL DEFAULT '2000-1-1 00:00:00' AFTER `SqlDB_Version`, ADD COLUMN `SqlDB_BackUpManual_EN` BOOLEAN NOT NULL DEFAULT false AFTER `SqlDB_BackUpTime`");
|
}
|
|
res0 = sqlMysqlQuery(
|
"SELECT * FROM information_schema.columns WHERE table_schema='db_app_sys' AND table_name='tb_app_sys' AND column_name='SqlDB_BackUpManual_EN'");
|
|
if (!res0.next()) {
|
sqlMysqlExecute(
|
"ALTER TABLE `db_app_sys`.`tb_app_sys` ADD COLUMN `SqlDB_BackUpManual_EN` BOOLEAN NOT NULL DEFAULT false AFTER `SqlDB_BackUpTime`");
|
}
|
|
sqlMysqlExecute(table_1);
|
sqlMysqlExecute(table_2);
|
sqlMysqlExecute(table_3);
|
sqlMysqlExecute(table_3_1);
|
sqlMysqlExecute(table_3_2);
|
sqlMysqlExecute(table_3_2_1);
|
sqlMysqlExecute(table_4);
|
sqlMysqlExecute(table_5);
|
sqlMysqlExecute(table_6);
|
sqlMysqlExecute(table_7);
|
sqlMysqlExecute(table_8);
|
|
ResultSet res = sqlMysqlQuery("show index from `db_battinf`.`tb_battinf` where column_name='BattGroupId'");
|
if (!res.next()) {
|
sqlMysqlExecute("ALTER TABLE `db_battinf`.`tb_battinf` ADD INDEX `index_station_id` (`StationId` ASC);");
|
sqlMysqlExecute(
|
"ALTER TABLE `db_battinf`.`tb_battinf` ADD INDEX `index_battgroup_id` (`BattGroupId` ASC);");
|
sqlMysqlExecute(
|
"ALTER TABLE `db_battinf`.`tb_battinf` ADD INDEX `index_station_name` (`StationName` ASC);");
|
sqlMysqlExecute(
|
"ALTER TABLE `db_battinf`.`tb_battinf` ADD INDEX `index_battgroup_name` (`BattGroupName` ASC);");
|
}
|
|
res = sqlMysqlQuery(
|
"SELECT * FROM information_schema.columns WHERE table_schema='db_battinf' AND table_name='tb_battinf' AND column_name='BattGuarantDayCount'");
|
|
if (!res.next()) {
|
sqlMysqlExecute(
|
"ALTER TABLE `db_battinf`.`tb_battinf` ADD COLUMN `BattGuarantDayCount` INT NOT NULL DEFAULT 1095 AFTER `BattInUseDate`");
|
}
|
|
res = sqlMysqlQuery(
|
"SELECT * FROM information_schema.columns WHERE table_schema='db_battinf' AND table_name='tb_batt_maint_inf' AND column_name='master_audit'");
|
|
if (!res.next()) {
|
sqlMysqlExecute(
|
"ALTER TABLE `db_battinf`.`tb_batt_maint_inf` ADD COLUMN `fault_type_id` INT NOT NULL DEFAULT 0 AFTER `usr_id`, ADD COLUMN `maint_type_id` INT NOT NULL DEFAULT 0 AFTER `maint_time_limit`, ADD COLUMN `master_audit` INT NOT NULL DEFAULT 0 AFTER `master_id`, ADD COLUMN `maint_close` BOOLEAN NOT NULL DEFAULT FALSE AFTER `maint_done_time`, ADD COLUMN `appoint_uid` INT NOT NULL DEFAULT 0 AFTER `master_audit`, ADD COLUMN `copy_uids` VARCHAR(200) NOT NULL DEFAULT ' ' AFTER `master_audit`");
|
}
|
|
res = sqlMysqlQuery(
|
"show index from `db_batt_testdata`.`tb_batttestdata_inf` where column_name='test_starttime'");
|
if (!res.next()) {
|
sqlMysqlExecute(
|
"ALTER TABLE `db_batt_testdata`.`tb_batttestdata_inf` ADD INDEX `index_test_record_count` (`test_record_count` ASC);");
|
sqlMysqlExecute(
|
"ALTER TABLE `db_batt_testdata`.`tb_batttestdata_inf` ADD INDEX `index_test_type` (`test_type` ASC);");
|
sqlMysqlExecute(
|
"ALTER TABLE `db_batt_testdata`.`tb_batttestdata_inf` ADD INDEX `index_test_starttime` (`test_starttime` ASC);");
|
}
|
|
res = sqlMysqlQuery(
|
"show index from `db_batt_testdata`.`tb_battresdata_inf` where column_name='test_starttime'");
|
if (!res.next()) {
|
sqlMysqlExecute(
|
"ALTER TABLE `db_batt_testdata`.`tb_battresdata_inf` ADD INDEX `index_test_record_count` (`test_record_count` ASC);");
|
sqlMysqlExecute(
|
"ALTER TABLE `db_batt_testdata`.`tb_battresdata_inf` ADD INDEX `index_test_type` (`test_type` ASC);");
|
sqlMysqlExecute(
|
"ALTER TABLE `db_batt_testdata`.`tb_battresdata_inf` ADD INDEX `index_test_starttime` (`test_starttime` ASC);");
|
}
|
|
res = sqlMysqlQuery(
|
"SELECT * FROM information_schema.columns WHERE table_schema='db_batt_testdata' AND table_name='tb_batttestdata_inf' AND column_name='test_record_count_ex'");
|
|
if (!res.next()) {
|
sqlMysqlExecute(
|
"ALTER TABLE `db_batt_testdata`.`tb_batttestdata_inf` ADD COLUMN `test_record_count_ex` INT NOT NULL DEFAULT 0 AFTER `test_record_count`, ADD COLUMN `test_starttime_ex` DATETIME NOT NULL DEFAULT '1920-01-01 00:00:00' AFTER `test_starttime`, ADD COLUMN `upload_usr_id` INT NOT NULL DEFAULT 0 AFTER `mon_vol`");
|
}
|
|
res = sqlMysqlQuery(
|
"SELECT * FROM information_schema.columns WHERE table_schema='db_batt_testdata' AND table_name='tb_batttestdata_inf' AND column_name='upload_data_ext'");
|
|
if (!res.next()) {
|
sqlMysqlExecute(
|
"ALTER TABLE `db_batt_testdata`.`tb_batttestdata_inf` ADD COLUMN `upload_data_ext` INT NOT NULL DEFAULT 0 AFTER `upload_usr_id`, ADD COLUMN `upload_client_type` INT NOT NULL DEFAULT 0 AFTER `upload_data_ext`");
|
|
sqlMysqlExecute(
|
"ALTER TABLE `db_batt_testdata`.`tb_battresdata_inf` ADD COLUMN `upload_client_type` INT NOT NULL DEFAULT 0 AFTER `upload_usr_id`");
|
}
|
|
res = sqlMysqlQuery(
|
"SELECT DATA_TYPE FROM information_schema.columns WHERE table_schema='db_batt_testdata' AND table_name='tb_batttestdata_inf' AND column_name='num'");
|
|
if ((res.next()) && (!"bigint".equals(res.getString("DATA_TYPE").toLowerCase()))) {
|
sqlMysqlExecute(
|
"ALTER TABLE `db_batt_testdata`.`tb_batttestdata_inf` MODIFY COLUMN num BIGINT NOT NULL AUTO_INCREMENT;");
|
sqlMysqlExecute(
|
"ALTER TABLE `db_batt_testdata`.`tb_battresdata_inf` MODIFY COLUMN num BIGINT NOT NULL AUTO_INCREMENT;");
|
}
|
}
|
|
public void sqlMysqlUseDB(String db) throws SQLException {
|
sqlMysqlExecute("use " + db);
|
}
|
|
public int getBattStationCount() {
|
int st_count = 0;
|
try {
|
sqlMysqlUseDB("`db_battinf`");
|
|
String sql_str = "SELECT DISTINCT StationId FROM `db_battinf`.`tb_battinf`";
|
ResultSet res = sqlMysqlQuery(sql_str);
|
while (res.next()) {
|
st_count++;
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
|
return st_count;
|
}
|
|
public boolean sqlMysqlCheckIfTableExist(String tb) throws SQLException {
|
String sql_str = "SHOW TABLES LIKE '" + tb + "'";
|
ResultSet res = sqlMysqlQuery(sql_str);
|
boolean exist = false;
|
if (res.next()) {
|
exist = true;
|
}
|
|
return exist;
|
}
|
|
public void sqlMysqlExecute(String sql_str) throws SQLException {
|
Statement sql = this.mysql_con.createStatement();
|
sql.setQueryTimeout(30);
|
sql.execute(sql_str);
|
}
|
public void sqlMysqlTotalExecute(String sql_str) throws SQLException {
|
Statement sql = this.mysql_con.createStatement();
|
sql.setQueryTimeout(60*10);
|
sql.execute(sql_str);
|
}
|
|
public boolean makeManualCommit(ArrayList<String> al_sql_strs) {
|
boolean exe_res = true;
|
try {
|
this.mysql_con.setAutoCommit(false);
|
|
for (int n = 0; n < al_sql_strs.size(); n++) {
|
sqlMysqlExecute((String) al_sql_strs.get(n));
|
}
|
|
if (exe_res)
|
this.mysql_con.commit();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
exe_res = false;
|
try {
|
if (!exe_res) {
|
this.mysql_con.rollback();
|
}
|
this.mysql_con.setAutoCommit(true);
|
} catch (SQLException e1) {
|
e1.printStackTrace();
|
}
|
} finally {
|
try {
|
if (!exe_res) {
|
this.mysql_con.rollback();
|
}
|
this.mysql_con.setAutoCommit(true);
|
} catch (SQLException e1) {
|
e1.printStackTrace();
|
}
|
}
|
|
return exe_res;
|
}
|
|
public ResultSet sqlMysqlQuery(String sql_str) {
|
ResultSet res = null;
|
try {
|
Statement sql = this.mysql_con.createStatement();
|
sql.setQueryTimeout(30);
|
String query = sql_str;
|
res = sql.executeQuery(query);
|
} catch (SQLException ex) {
|
System.err.println("SQLException:" + ex.getMessage());
|
}
|
|
return res;
|
}
|
|
public ResultSet sqlMysqlTotalQuery(String sql_str) {
|
ResultSet res = null;
|
try {
|
Statement sql = this.mysql_con.createStatement();
|
sql.setQueryTimeout(60*5);
|
String query = sql_str;
|
res = sql.executeQuery(query);
|
} catch (SQLException ex) {
|
System.err.println("SQLException:" + ex.getMessage());
|
}
|
|
return res;
|
}
|
|
public void sqlMysqlUpdate(String sql_str) {
|
try {
|
Statement sql = this.mysql_con.createStatement();
|
sql.setQueryTimeout(30);
|
String query = sql_str;
|
sql.execute(query);
|
} catch (SQLException ex) {
|
System.out.println("SQLException:" + ex.getMessage());
|
}
|
}
|
|
public String getStationName(int bg_id) {
|
String stname = " ";
|
|
String sql_str = "SELECT DISTINCT StationName FROM `db_battinf`.`tb_battinf` WHERE BattGroupId=" + bg_id;
|
ResultSet res = sqlMysqlQuery(sql_str);
|
try {
|
if (res.next()) {
|
stname = res.getString("StationName");
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
|
return stname;
|
}
|
|
public String getBattGroupName(int bg_id) {
|
String bgname = " ";
|
|
String sql_str = "SELECT DISTINCT BattGroupName FROM `db_battinf`.`tb_battinf` WHERE BattGroupId=" + bg_id;
|
ResultSet res = sqlMysqlQuery(sql_str);
|
try {
|
if (res.next()) {
|
bgname = res.getString("BattGroupName");
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
|
return bgname;
|
}
|
|
public String getUsrName(int uid) {
|
String usrname = " ";
|
|
String sql_str = "SELECT uName FROM `db_user`.`tb_user_inf` WHERE uId=" + uid;
|
ResultSet res = sqlMysqlQuery(sql_str);
|
try {
|
if (res.next()) {
|
usrname = res.getString("uName");
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
|
return usrname;
|
}
|
|
public String getTaskSmsFormatStr(String format_type) {
|
String str = "";
|
String sql_str = "SELECT DISTINCT `db_user`.`tb_user_sms_format`.format_str FROM `db_user`.`tb_user_sms_format` WHERE `db_user`.`tb_user_sms_format`.format_type='"
|
+ format_type + "'";
|
|
ResultSet sql_res = sqlMysqlQuery(sql_str);
|
try {
|
if (sql_res.next()) {
|
str = sql_res.getString("format_str");
|
}
|
} catch (SQLException localSQLException) {
|
}
|
|
return str;
|
}
|
|
public String getUsrTaskName(int taskid) {
|
String taskname = " ";
|
|
String sql_str = "SELECT task_name FROM `db_user`.`tb_user_task` WHERE task_id=" + taskid;
|
ResultSet res = sqlMysqlQuery(sql_str);
|
try {
|
if (res.next())
|
taskname = res.getString("task_name");
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
|
return taskname;
|
}
|
|
public String getUsrTaskCreateTime(int taskid) {
|
String taskcreatetime = " ";
|
|
String sql_str = "SELECT task_create_date FROM `db_user`.`tb_user_task` WHERE task_id=" + taskid;
|
ResultSet res = sqlMysqlQuery(sql_str);
|
try {
|
if (res.next()) {
|
taskcreatetime = Com.getDateTimeFormat(res.getTimestamp("task_create_date"), "yyyy-MM-dd HH:mm:ss");
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
|
return taskcreatetime;
|
}
|
|
public int getBattTestRecordCountNew(int bg_id, String table) {
|
int count = 0;
|
boolean res_exe = true;
|
String sql_str0 = "SELECT test_record_count_ex FROM " + table + " WHERE BattGroupId=" + bg_id + " FOR UPDATE";
|
String sql_str1 = "UPDATE " + table + " SET test_record_count_ex=test_record_count+1" + " WHERE BattGroupId="
|
+ bg_id;
|
String sql_str2 = "SELECT MAX(test_record_count_ex) FROM " + table + " WHERE BattGroupId=" + bg_id;
|
String sql_str3 = "INSERT INTO " + table + " (BattGroupId,test_record_count, test_record_count_ex) "
|
+ " VALUES (" + bg_id + "," + 1 + "," + 1 + ")";
|
try {
|
this.mysql_con.setAutoCommit(false);
|
ResultSet res = sqlMysqlQuery(sql_str0);
|
if (res.next()) {
|
sqlMysqlExecute(sql_str1);
|
res = sqlMysqlQuery(sql_str2);
|
if (res.next())
|
count = res.getInt(1);
|
} else {
|
count = 1;
|
sqlMysqlExecute(sql_str3);
|
}
|
|
this.mysql_con.commit();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
res_exe = false;
|
|
if (!res_exe)
|
try {
|
this.mysql_con.rollback();
|
this.mysql_con.setAutoCommit(true);
|
} catch (SQLException e1) {
|
e1.printStackTrace();
|
}
|
} finally {
|
if (!res_exe) {
|
try {
|
this.mysql_con.rollback();
|
this.mysql_con.setAutoCommit(true);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
}
|
|
return count;
|
}
|
}
|
|
/*
|
* Location:
|
* C:\Users\LiJun\Desktop\公司各种设备资料\9600显示模块相关文件\后台程序\2018-09-07\BattFBS9600XSP.
|
* jar Qualified Name: com.sql.Sql_Mysql JD-Core Version: 0.6.2
|
*/
|
