From c74abef816c15953e1699ec1e3efa665c05ea566 Mon Sep 17 00:00:00 2001
From: whycrzg <ruanzhigang@whycst.com>
Date: 星期一, 06 九月 2021 17:27:53 +0800
Subject: [PATCH] db_user库新建表tb_user_work、tb_work_alarm,db_alarm数据库中新建表`tb_alarm_message`
---
BattMonitor_DB_Builder/src/com/sql/Sql_Mysql.java | 2721 +++++++++++++++++++++++++++++-----------------------------
1 files changed, 1,362 insertions(+), 1,359 deletions(-)
diff --git a/BattMonitor_DB_Builder/src/com/sql/Sql_Mysql.java b/BattMonitor_DB_Builder/src/com/sql/Sql_Mysql.java
index 5dfde8e..491a0fb 100644
--- a/BattMonitor_DB_Builder/src/com/sql/Sql_Mysql.java
+++ b/BattMonitor_DB_Builder/src/com/sql/Sql_Mysql.java
@@ -1,1359 +1,1362 @@
-package com.sql;
-import java.sql.Connection;
-import java.sql.ResultSet;
-import java.sql.SQLException;
-import java.sql.Statement;
-import java.util.ArrayList;
-
-import com.base.Com;
-
-/**
- * 创建数据库以及表格
- * @author 军
- *
- */
-public class Sql_Mysql
-{
- //--------------------------------------------------------------------------------------------//
- final public static String DB_AppSys = "`db_app_sys`";
- final public static String DB_BATT_INF = "`db_battinf`";
- final public static String DB_BATT_TESTDATA = "`db_batt_testdata`";
- final public static String DB_PARAM = "`db_param`";
- final public static String DB_ALARM = "`db_alarm`";
- final public static String DB_USER = "`db_user`";
- final public static String DB_RamDB = "`db_ram_db`";
- final public static String DB_RamDB_BATT_RT = "`db_ram_batt_rt`";
-
- final public static String WEB_Site = "`web_site`";
- final public static String DB_BATT_HISTORY = "`db_batt_history`";
- final public static String DB_LD9_TESTDATA = "`db_ld9_testdata`";
-
- 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_INF = "`db_pwrdev_inf`";
- final public static String DB_FBO_TESTDATA = "`db_fbo_testdata`";
-
-
- //--------------------------------------------------------------------------------------------//
- public final static String TB_HardDevSmsState = "tb_hard_dev_sms_state";
- //--------------------------------------------------------------------------------------------//
- public final static String TB_AppSys = "tb_app_sys";
- public final static String TB_ServerState = "tb_server_state";
-
- public final static String TB_BattTestDataInf = "tb_batttestdata_inf";
- public final static String TB_BattRtData = "tb_batt_rtdata";
- public final static String TB_BattRtState = "tb_batt_rtstate";
-
- public final static String TB_UserInf = "tb_user_inf";
- public final static String TB_UserLog = "tb_user_log";
- public final static String TB_UserBattGroup_BaoJiGroup = "tb_user_battgroup_baojigroup";
- public final static String TB_UserBattGroup_BaoJiGroup_BattGroup = "tb_user_battgroup_baojigroup_battgroup";
- public final static String TB_UserBattGroup_BaoJiGroup_Usr = "tb_user_battgroup_baojigroup_usr";
- public final static String TB_UserTaskTemplate = "tb_user_task_batt_template";
- public final static String TB_UserTask = "tb_user_task";
- public final static String TB_UserPaiXiu = "tb_user_paixiu";
- public final static String TB_UserListForTask = "tb_user_task_user_list";
- public final static String TB_UserTaskParam = "tb_user_task_param";
- public final static String TB_UserBattCkeckTask = "tb_user_task_batt_check";
- public final static String TB_UserBattTestTask = "tb_user_task_batt_test";
- public final static String TB_UserBattTaskChange = "tb_user_task_change";
- public final static String TB_UserSms = "tb_user_sms";
- public final static String TB_UserSmsFormat = "tb_user_sms_format";
- public final static String TB_UserPermitGroup = "tb_user_permitgroup";
- public final static String TB_UserPermitGroupData = "tb_user_permitgroup_data";
- public final static String TB_UserJieJiaRi = "tb_user_jiejiari";
- //--------------------------------------------------------------------------------------------//
- //--------------------------------------------------------------------------------------------//
- public final static String CInterfaceState_Table = DB_RamDB + ".`tb_cinterface_state`";
-
- public final static String FBS9600SetParam_Table = DB_RamDB + ".`tb_fbs9600_setparam`";
- //public final static String FBS9600Param_Table = DB_RamDB + ".`tb_fbs9600_param`";
-
- //--------------------------------------------------------------------------------------------//
- //--------------------------------------------------------------------------------------------//
- public final static String AppSys_Table = DB_AppSys + ".`tb_app_sys`";
- public final static String FBSDevStateChangeInf_Table = DB_AppSys + ".`tb_fbsdev_statechange_inf`";
- public final static String BattInf_Table = DB_BATT_INF + ".`tb_battinf`";
- public final static String BattCurrInf_Table = DB_BATT_INF + ".`tb_battcurr_inf`";
- public final static String BattMainInf_Table = DB_BATT_INF + ".`tb_batt_maint_inf`";
- public final static String BattMainProcess_Table = DB_BATT_INF + ".`tb_batt_maint_process`";
-
-
-
- public final static String BattTestDataInf_Table = DB_BATT_TESTDATA + ".`tb_batttestdata_inf`";
- public final static String BattResDataInf_Table = DB_BATT_TESTDATA + ".`tb_battresdata_inf`";
- public final static String BattResData_Table = DB_BATT_TESTDATA + ".`tb_BattResData`";
-
- public final static String UserInfTable = DB_USER + ".`tb_user_inf`";
- public final static String UserLogTable = DB_USER + ".`tb_user_log`";
- public final static String UserBattGroup_BaoJiGroup_Table = DB_USER + ".`tb_user_battgroup_baojigroup`";
- public final static String UserBattGroup_BaoJiGroup_BattGroupTable = DB_USER + ".`tb_user_battgroup_baojigroup_battgroup`";
- public final static String UserBattGroup_BaoJiGroup_UsrTable = DB_USER + ".`tb_user_battgroup_baojigroup_usr`";
- public final static String UserTaskTemplateTable = DB_USER + ".`tb_user_task_batt_template`";
- public final static String UserTaskTable = DB_USER + ".`tb_user_task`";
- public final static String UserPaiXiuTable = DB_USER + ".`tb_user_paixiu`";
- public final static String UserListForTaskTable = DB_USER + ".`tb_user_task_user_list`";
- public final static String UserTaskParamTable = DB_USER + ".`tb_user_task_param`";
- public final static String UserBattCheckTaskTable = DB_USER + ".`tb_user_task_batt_check`";
- public final static String UserBattTestTaskTable = DB_USER + ".`tb_user_task_batt_test`";
-
- public final static String UserBattTaskChangeTable = DB_USER + ".`tb_user_task_change`";
-
- public final static String UsrBattMaintCheck_Table = DB_USER + ".`tb_user_battmaint_check`";
- public final static String UsrBattMaintCheckProcess_Table = DB_USER + ".`tb_user_battmaint_check_process`";
-
- public final static String UserSmsTable = DB_USER + ".`tb_user_sms`";
- public final static String UserSmsFormatTable = DB_USER + ".`tb_user_sms_format`";
- public final static String UserPermitGroupTable = DB_USER + ".`tb_user_permitgroup`";
- public final static String UserPermitGroupDataTable = DB_USER + ".`tb_user_permitgroup_data`";
- public final static String UserJieJiaRiTable = DB_USER + ".`tb_user_jiejiari`";
-
-
- public final static String BTSStationEvent_Table = DB_ALARM + ".`tb_bts_station_event`"; //机房状态历史表
-
- /**************************** db_ram_db数据库 ***************************************************/
- public final static String SocketClientStateTable = DB_RamDB + ".`tb_socketclient_state`";
- public final static String ServerState_Table = DB_RamDB + ".`tb_server_state`";
- public final static String Ld9_Updatestatue_Table = DB_RamDB + ".`tb_ld9_updatestatue`";
- public final static String Ld9_State_Table = DB_RamDB + ".`tb_ld9_state`";
- public final static String Ld9_Setparam_Table = DB_RamDB + ".`tb_ld9_setparam`";
- public final static String Ld9_Concentratestate_Table = DB_RamDB + ".`tb_ld9_concentratestate`";
- public final static String Ld9_Concentrateparam_Table = DB_RamDB + ".`tb_ld9_concentrateparam`";
- public final static String HardDevSmsState_Table = DB_RamDB + ".`tb_hard_dev_sms_state`";
- public final static String FBS9600State_Table = DB_RamDB + ".`tb_fbs9600_state`";
- public final static String FBS9100_NiBian_State = DB_RamDB + ".tb_fbs9100s_nibian_state"; //61850离线养护计划参数表
- public final static String FBS9100s_fod_state = DB_RamDB + ".tb_fbs9100s_fod_state"; //61850除硫状态表
- public final static String FBS9100SetDLParam_Table = DB_RamDB + ".`tb_fbs9100s_fod_param`"; //61850除硫参数表
- public final static String FBS9100S_DFUState_Table = DB_RamDB + ".`tb_fbs9100s_DFU_state`";
- public final static String FBS9100S_DcDcState_Table = DB_RamDB + ".`tb_fbs9100s_dcdc_state`";
- public final static String FBS9100SysParam_Table = DB_RamDB + ".`tb_fbs9100_sysparam`"; //系统参数表
- public final static String FBS9100State_Table = DB_RamDB + ".`tb_fbs9100_state`";
- public final static String FBS9100SetParam_Table = DB_RamDB + ".`tb_fbs9100_setparam`";
- public final static String FBS9100_offlineyhplan = DB_RamDB + ".tb_fbs9100_offlineyhplan"; //61850离线养护计划参数表
- public final static String FBS9100BattParam_Table = DB_RamDB + ".`tb_fbs9100_battparam`"; //电池组参数表
- public final static String BTSStationState_Table = DB_RamDB + ".`tb_bts_station_state`"; //机房状态表
- public final static String BTSGPRSState_Table = DB_RamDB + ".`tb_bts_gprs_state`"; //gprs 信息表
- public final static String BMP7100State_Table = DB_RamDB + ".`tb_bpm7100_state`";
- public final static String BattRtState_Table = DB_RamDB + ".`tb_batt_rtstate`";
- public final static String BattRtData_Table = DB_RamDB + ".`tb_batt_rtdata`";
- public final static String TB_61850_JHState_Table = DB_RamDB +".tb_61850_jhstate"; //61850均衡61850供电模块信息
- public final static String RamDB_CMCC_POWER_Data = DB_RamDB + ".`cmcc_power_data`";
- public final static String FBS9100_InsulatstateTable = DB_RamDB + ".`tb_fbs9100_insulatstate`"; //绝缘装置
- public final static String FBS9100_CollectionstateTable = DB_RamDB + ".`tb_fbs9100_collectionstate`"; //汇集装置
- public final static String FBS9100_ChargerstateTable = DB_RamDB + ".`tb_fbs9100_chargerstate`"; //充电机
- public final static String Tb_Lithium_DataTable = DB_RamDB + ".`tb_lithium_data`"; //锂电池数据表
-
- public final static String Tb_Batt_RSAlarm_Table = DB_RamDB + ".`tb_batt_rsalarm`"; //锂电池数据表
- public final static String Tb_Batt_RTAlarm_Table = DB_RamDB + ".`tb_batt_rtalarm`"; //锂电池数据表
-
- public final static String FBS9100s_stemnode_state_Table = DB_RamDB + ".`tb_fbs9100s_stemnode_state`"; //干节点状态表
- public final static String FBS9100s_buscouple_state_Table = DB_RamDB + ".`tb_fbs9100s_buscouple_state`"; //母联开关状态表
- public final static String Fgcd_filedownload_Table = DB_RamDB + ".`tb_fgcd_filedownload`"; //FGCD充放电一体机控制
-
-
- /**************************** db_user数据库 ***************************************************/
- public final static String User_Permitgroup_Table = DB_USER + ".`tb_user_permitgroup`"; //权限表
- public final static String User_Permitgroup_Data_Table = DB_USER + ".`tb_user_permitgroup_data`"; //用户权限表
- public final static String User_Task_User_List_Table = DB_USER + ".`tb_user_task_user_list`"; //
- public final static String User_Task_Template_Table = DB_USER + ".`tb_user_task_template`"; //
- public final static String User_Task_Param_Table = DB_USER + ".`tb_user_task_param`"; //
- public final static String User_Task_Change_Table = DB_USER + ".`tb_user_task_change`"; //
- public final static String User_Task_Batt_Test_Table = DB_USER + ".`tb_user_task_batt_test`"; //
- public final static String User_Task_Batt_Template_Table = DB_USER + ".`tb_user_task_batt_template`"; //
- public final static String User_Task_Batt_Check_Table = DB_USER + ".`tb_user_task_batt_check`"; //
- public final static String User_Task_Table = DB_USER + ".`tb_user_task`"; //
- public final static String User_Sms_Format_Table = DB_USER + ".`tb_user_sms_format`"; //
- public final static String User_Sms_Table = DB_USER + ".`tb_user_sms`"; //
- public final static String User_Paixiu_Table = DB_USER + ".`tb_user_paixiu`"; //
- public final static String User_Log_Table = DB_USER + ".`tb_user_log`"; //
- public final static String User_Jiejiari_Table = DB_USER + ".`tb_user_jiejiari`"; //
- public final static String User_Face_Table = DB_USER + ".`tb_user_face`"; //
- public final static String User_Battmaint_Check_Process_Table = DB_USER + ".`tb_user_battmaint_check_process`"; //
- public final static String User_Battmaint_Check_Table = DB_USER + ".`tb_user_battmaint_check`"; //
- public final static String User_Battgroup_Baojigroup_Usr_Table = DB_USER + ".`tb_user_battgroup_baojigroup_usr`"; //
- public final static String User_Battgroup_Baojigroup_Battgroup_Table = DB_USER + ".`tb_user_battgroup_baojigroup_battgroup`"; //
- public final static String User_Battgroup_Baojigroup_Table = DB_USER + ".`tb_user_battgroup_baojigroup`"; //
- public final static String User_Limit_Table = DB_USER + ".`tb_user_limit`"; //
-
-
-
-
-
-
- /**************************** web_site数据库 ***************************************************/
- public final static String ThreadUtil_Table = WEB_Site + ".`tb_thread_util`"; //线程帮助表
- public final static String PositiomapUsr_Table = WEB_Site + ".`tb_positiomap_usr`"; //首页地图中心点定位表
- public final static String ChartColor_Table = WEB_Site + ".`tb_chart_color`"; //用户实时监测页面指定柱状的颜色设置表
- public final static String UserChart_Table = WEB_Site + ".`tb_user_chart`"; //班组管理表
- public final static String ProcessSurvey_Table = WEB_Site + ".`tb_process_survey`"; //线程监控表
- public final static String EchartsUsr_Table = WEB_Site + ".`tb_echarts_usr`"; //用户首页定制表
- public final static String DevstateUsr_Table = WEB_Site + ".`tb_devstate_usr`"; //用户放电添加记录表
- public final static String CustomPage_Table = WEB_Site + ".`tb_custompage`"; //导航数据表
- public final static String Announce_Table = WEB_Site + ".`tb_announce`"; //公告表
- public final static String BattAttention_Table = WEB_Site + ".`tb_batt_attention`"; //创建电池组单体关注表
- public final static String BattpowerOff_Table = WEB_Site + ".`tb_battpower_off`"; //机房停电表
- public final static String BadbattMon_Table = WEB_Site + ".`tb_badbatt_mon`"; //落后单体表
- public final static String BadbattMonFlag_Table = WEB_Site + ".`tb_badbatt_mon_flag`"; //落后单体控制表
- public final static String BattEndurance_Table = WEB_Site + ".`tb_batt_endurance`"; //机房续航表
- public final static String BattParamLow_Table = WEB_Site + ".`tb_batt_param_low`"; //电池参数表
- public final static String BattDischargeParam_Table = WEB_Site + ".`tb_battdischarge_param`"; //放电计划参数表
- public final static String BattDischargePlan_Table = WEB_Site + ".`tb_battdischarge_plan`"; //放电计划表
- public final static String Avoid_plan_Table = WEB_Site + ".`tb_avoid_plan`"; //放电计划规避时间表
- public final static String BattMapInformation_Table = WEB_Site + ".`tb_battmap_information`"; //基站定位表
- public final static String BattDischarge_Table = WEB_Site + ".`tb_batt_discharge`"; //基站故障表
- public final static String BattReplace_Table = WEB_Site + ".`tb_batt_replace`"; //电池更换记录表
- public final static String Dev_Restart_Plan_Table = WEB_Site +".`tb_dev_restart_plan`"; //设备定期重启计划表
- public final static String Vip_User_Table = WEB_Site +".`tb_vip_user`"; //超级用户表
- public final static String License_Table = WEB_Site +".`tb_license`"; //超级用户表
- public final static String Database_Backup_Table = WEB_Site +".`tb_database_backup`"; //超级用户表
-
-
- /***************************** db_battinf数据库 ***************************************/
- public final static String DeverrorRecord_Table = DB_BATT_INF + ".`tb_deverror_record`"; //机房问题记录表
- public final static String BattinfApply_TABLE = DB_BATT_INF + ".`tb_battinf_apply`"; //机房安装审批表
- public final static String BattinfEx_TABLE = DB_BATT_INF + ".`tb_battinf_ex`"; //基站信息表
- public final static String BattinfRebuild_TABLE = DB_BATT_INF + ".`tb_battinf_rebuild`"; //机房配组表
- public final static String BattmonTestcap_TABLE = DB_BATT_INF + ".`tb_battmon_testcap`"; //机房标准测试曲线记录表
- public final static String Battcurr_Inf_TABLE = DB_BATT_INF + ".`tb_battcurr_inf`";
- public final static String Batt_Maint_Process_TABLE = DB_BATT_INF + ".`tb_batt_maint_process`";
- public final static String Batt_Maint_Inf_TABLE = DB_BATT_INF + ".`tb_batt_maint_inf`";
- public final static String Batt_Alm_Param_TABLE = DB_BATT_INF + ".`tb_batt_alm_param`";
-
-
-
-
- /***************************** db_alarm数据库 ***************************************/
- public final static String Devalarm_Data_Table = DB_ALARM + ".`tb_devalarm_data`";
- public final static String Devalarm_Data__History_Table = DB_ALARM + ".`tb_devalarm_data_history`";
- public final static String Bts_Station_Event_Table = DB_ALARM + ".`tb_bts_station_event`";
- public final static String Battalarm_Data_Table = DB_ALARM + ".`tb_battalarm_data`";
- public final static String Battalarm_Data_History_Table = DB_ALARM + ".`tb_battalarm_data_history`";
-
-
- /***************************** db_app_sys 数据库 ***************************************/
- public final static String Page_Param_Table = DB_AppSys + ".`tb_page_param`";
- public final static String Fbsdev_Statechange_Inf_Table = DB_AppSys + ".`tb_fbsdev_statechange_inf`";
- public final static String Dashboard_Module_Chart_Table = DB_AppSys + ".`tb_dashboard_module_chart`";
- public final static String Dashboard_Module_Table = DB_AppSys + ".`tb_dashboard_module`";
- public final static String Dashboard_Chart_Type_Table = DB_AppSys + ".`tb_dashboard_chart_type`";
- public final static String App_Sys_Table = DB_AppSys + ".`tb_app_sys`";
- public final static String App_Bts_Comm_Task_Table = DB_AppSys + ".`tb_app_bts_comm_task`";
- public final static String App_Bts_Battgroup_Table = DB_AppSys + ".`tb_app_bts_battgroup`";
- public final static String Menu_Table = DB_AppSys + ".`tb_menu`";
-
- /***************************** db_ld9_testdata 数据库 ***************************************/
- public final static String Ld9testdata_Inf_Table = DB_LD9_TESTDATA + ".`tb_ld9testdata_inf`";
-
- /***************************** db_param 数据库 ***************************************/
- public final static String Dev_Param_Table = DB_PARAM + ".`tb_dev_param`";
- public final static String App_Param_Table = DB_PARAM + ".`tb_app_param`";
- public final static String Alarm_Param_Table = DB_PARAM + ".`tb_alarm_param`";
-
- /***************************** db_pwrdev_alarm 数据库 ************************************/
- 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`";
-
- /***************************** db_pwrdev_data_rt 数据库 ************************************/
- 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`";
-
- /***************************** db_pwrdev_inf 数据库 ************************************/
- 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 Display_Config_Table = DB_PWRDEV_INF + ".`tb_display_config`";
-
- public final static String Pwrapp_Stainf_Table = DB_PWRDEV_INF + ".`tb_pwrapp_stainf`";
- public final static String Pwrapp_Dcinf_Table = DB_PWRDEV_INF + ".`tb_pwrapp_dcinf`";
- public final static String Pwrapp_Acinf_Table = DB_PWRDEV_INF + ".`tb_pwrapp_acinf`";
- public final static String Pwrapp_Acdcinf_Table = DB_PWRDEV_INF + ".`tb_pwrapp_acdcinf`";
-
-
-
-
- /***************************** db_fbo_testdata 数据库 ************************************/
- public final static String Fbotestdata_Inf_Table = DB_FBO_TESTDATA + ".`tb_fbotestdata_inf`";
- //--------------------------------------------------------------------------------------------//
- //--------------------------------------------------------------------------------------------//
- public Connection mysql_con;
-
- public Sql_Mysql(Connection conn)
- {
- mysql_con = conn;
- }
-
- /*
- public static Connection getConnection() throws SQLException, java.lang.ClassNotFoundException{
- String url = "jdbc:mysql://192.168.48.128:3306/studentinfo";
- Class.forName("com.mysql.jdbc.Driver");
- String userName = "root";
- String password = "lmx8688139";
- Connection con = DriverManager.getConnection(url,userName,password);
- return con;
- }
- */
-
- public void close_con()
- {
- try {
- mysql_con.close();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
-
- public void checkAndCreateDB() throws SQLException
- {
- sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + DB_AppSys);
- sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + DB_BATT_INF);
- 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_RamDB);
-
- //sqlMysqlExecute("DROP DATABASE IF EXISTS " + DB_RamDB_BATT_RT);
- //sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + DB_RamDB_BATT_RT);
-
- sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + WEB_Site);
-
- String table_0 = "CREATE TABLE IF NOT EXISTS " + AppSys_Table
- + " ( `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_Reinit_BattGroupData_EN` BOOLEAN NOT NULL DEFAULT false, "
- + "`AppServer_Reinit_BattGroupData_LD9_EN` BOOLEAN NOT NULL DEFAULT false, " //用于9度程序重新加载设备
- + "`AppServer_Reinit_Config_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 " + BattInf_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',"
- + "`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 " + BattCurrInf_Table
- + " ( `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 " + BattMainInf_Table
- + " ( `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 " + BattMainProcess_Table
- + " ( `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 " + UsrBattMaintCheck_Table
- + " ( `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 " + UsrBattMaintCheckProcess_Table
- + " ( `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 " + BattTestDataInf_Table
- + " ( `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 " + BattResDataInf_Table
- + " ( `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 " + SocketClientStateTable + "; ";
- String table_7 = "CREATE TABLE IF NOT EXISTS " + SocketClientStateTable
- + "(`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 " + FBSDevStateChangeInf_Table
- + "(`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;";
-
-
-
-
- String table_9 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.BTSStationEvent_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_event_type` INT NOT NULL DEFAULT 0, "
- + "`station_event_trig` INT NOT NULL DEFAULT 0, "
-
- + " INDEX index_dev_id (`dev_id`), "
- + " INDEX index_station_id (`station_id`), "
- + " PRIMARY KEY (`num`) ) "
- + " ENGINE=InnoDB DEFAULT CHARSET=utf8";
- //-------------------------------------------------------------------------------------//
- //-------------------------------------------------------------------------------------//
- sqlMysqlUseDB(Sql_Mysql.DB_AppSys);
- if(false == sqlMysqlCheckIfTableExist(TB_AppSys))
- {
- sqlMysqlExecute(table_0);
- sqlMysqlExecute("INSERT INTO " + Sql_Mysql.AppSys_Table
- + " (AppName) VALUES ('蓄电池组综合管理系统')");
- }
- //-------------------------------------------------------------------------------------//
- {
- ResultSet rest = sqlMysqlQuery("SELECT AppServer_Version FROM " + Sql_Mysql.AppSys_Table);
- if(rest.next()) {
- float ser_version = rest.getFloat(1);
- if(ser_version < 1.387) {
- sqlMysqlExecute("UPDATE " + Sql_Mysql.AppSys_Table + " 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(false == res0.next()) {
- sqlMysqlExecute("ALTER TABLE " + AppSys_Table
- + " 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(false == res0.next()) {
- sqlMysqlExecute("ALTER TABLE " + AppSys_Table
- + " ADD COLUMN `SqlDB_BackUpManual_EN` BOOLEAN NOT NULL DEFAULT false"
- + " AFTER `SqlDB_BackUpTime`");
- }
-
- /**************************** db_app_sys 表中添加 AppServer_Reinit_BattGroupData_EN列(重新载入电池组信息) *******************************************/
- res0 = sqlMysqlQuery("SELECT * FROM information_schema.columns"
- + " WHERE table_schema='db_app_sys'"
- + " AND table_name='tb_app_sys'"
- + " AND column_name='AppServer_Reinit_BattGroupData_EN'");
- if(false == res0.next()) {
- sqlMysqlExecute("ALTER TABLE " + AppSys_Table
- + " ADD COLUMN `AppServer_Reinit_BattGroupData_EN` BOOLEAN NOT NULL DEFAULT false"
- + " AFTER `SqlDB_BackUpManual_EN`");
- }
-
-
- /**************************** db_app_sys 表中添加 AppServer_Reinit_BattGroupData_EN 列(用于主程序动态调整运行参数) *******************************************/
- res0 = sqlMysqlQuery("SELECT * FROM information_schema.columns"
- + " WHERE table_schema='db_app_sys'"
- + " AND table_name='tb_app_sys'"
- + " AND column_name='AppServer_Reinit_Config_EN'");
- if(false == res0.next()) {
- sqlMysqlExecute("ALTER TABLE " + AppSys_Table
- + " ADD COLUMN `AppServer_Reinit_Config_EN` BOOLEAN NOT NULL DEFAULT false"
- + " AFTER `AppServer_Reinit_BattGroupData_EN`");
- }
-
-
- /**************************** db_app_sys 表中添加 AppServer_Reinit_BattGroupData_LD9_EN 列(用于LD9主程序动态加载设备参数) *******************************************/
- res0 = sqlMysqlQuery("SELECT * FROM information_schema.columns"
- + " WHERE table_schema='db_app_sys'"
- + " AND table_name='tb_app_sys'"
- + " AND column_name='AppServer_Reinit_BattGroupData_LD9_EN'");
- if(false == res0.next()) {
- sqlMysqlExecute("ALTER TABLE " + AppSys_Table
- + " ADD COLUMN `AppServer_Reinit_BattGroupData_LD9_EN` BOOLEAN NOT NULL DEFAULT false"
- + " AFTER `AppServer_Reinit_BattGroupData_EN`");
- }
- //---------------------------------------------------------------------------------------------//
-
- 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);
- sqlMysqlExecute(table_9);
-
- //---------------------------------------------------------------------------------------------//
- ResultSet res = sqlMysqlQuery("show index from " + BattInf_Table + " where column_name='BattGroupId'");
- if(false == res.next())
- {
- sqlMysqlExecute("ALTER TABLE " + BattInf_Table + " ADD INDEX `index_station_id` (`StationId` ASC);");
- sqlMysqlExecute("ALTER TABLE " + BattInf_Table + " ADD INDEX `index_battgroup_id` (`BattGroupId` ASC);");
- sqlMysqlExecute("ALTER TABLE " + BattInf_Table + " ADD INDEX `index_station_name` (`StationName` ASC);");
- sqlMysqlExecute("ALTER TABLE " + BattInf_Table + " ADD INDEX `index_battgroup_name` (`BattGroupName` ASC);");
- }
- //---------------------------------------------------------------------------------------------//
- //-------------------------------- 2017-06-06 by mxpopstar ------------------------------------//
- res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
- + " WHERE table_schema='db_battinf'"
- + " AND table_name='tb_battinf'"
- + " AND column_name='BattGuarantDayCount'");
- if(false == res.next()) {
- sqlMysqlExecute("ALTER TABLE " + BattInf_Table
- + " ADD COLUMN `BattGuarantDayCount` INT NOT NULL DEFAULT 1095 AFTER `BattInUseDate`");
- }
- //---------------------------------------------------------------------------------------------//
- //-------------------------------- 2018-10-12 by lijun ( binf表中添加 Load_curr->负载电流 disCurrMax -> 最大核容电流 )------------------------------------//
- res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
- + " WHERE table_schema='db_battinf'"
- + " AND table_name='tb_battinf'"
- + " AND column_name='Load_curr'");
- if(false == res.next()) {
- sqlMysqlExecute("ALTER TABLE " + BattInf_Table
- + " ADD COLUMN `Load_curr` float NOT NULL DEFAULT 15");
- }
-
- res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
- + " WHERE table_schema='db_battinf'"
- + " AND table_name='tb_battinf'"
- + " AND column_name='disCurrMax'");
- if(false == res.next()) {
- sqlMysqlExecute("ALTER TABLE " + BattInf_Table
- + " ADD COLUMN `disCurrMax` float NOT NULL DEFAULT 0");
- }
-
- //-------------------------------- 2018-10-16 by lijun ( binf表中添加 station_phone->基站手机号 )------------------------------------//
- res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
- + " WHERE table_schema='db_battinf'"
- + " AND table_name='tb_battinf'"
- + " AND column_name='station_phone'");
- if(false == res.next()) {
- sqlMysqlExecute("ALTER TABLE " + BattInf_Table
- + " ADD COLUMN `station_phone` varchar(32) DEFAULT ''");
- }
-
- //---------------------------------------------------------------------------------------------//
-
- //-------------------------------- 2018-10-16 by lijun ( binf表中添加 station_install->标识机房是否已经安装 )------------------------------------//
- res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
- + " WHERE table_schema='db_battinf'"
- + " AND table_name='tb_battinf'"
- + " AND column_name='station_install'");
- if(false == res.next()) {
- sqlMysqlExecute("ALTER TABLE " + BattInf_Table
- + " ADD COLUMN `station_install` BOOLEAN NOT NULL DEFAULT false");
- }
-
- //---------------------------------------------------------------------------------------------//
- //-------------------------------- 2018-11-29 by lijun ( binf表中添加 StationId_ex->基站站址编号 install_user-->安装人)------------------------------------//
- res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
- + " WHERE table_schema='db_battinf'"
- + " AND table_name='tb_battinf'"
- + " AND column_name='StationId_ex'");
- if(false == res.next()) {
- sqlMysqlExecute("ALTER TABLE " + BattInf_Table
- + " ADD COLUMN `StationId_ex` varchar(20) DEFAULT '' AFTER StationId");
- }
- res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
- + " WHERE table_schema='db_battinf'"
- + " AND table_name='tb_battinf'"
- + " AND column_name='install_user'");
- if(false == res.next()) {
- sqlMysqlExecute("ALTER TABLE " + BattInf_Table
- + " ADD COLUMN `install_user` varchar(64) DEFAULT ''");
- }
-
- //---------------------------------------------------------------------------------------------//
- //-------------------------------- 2018-12-12 by lijun ( binf表中添加 charge_curr_max 最大充电电流)------------------------------------//
- res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
- + " WHERE table_schema='db_battinf'"
- + " AND table_name='tb_battinf'"
- + " AND column_name='charge_curr_max'");
- if(false == res.next()) {
- sqlMysqlExecute("ALTER TABLE " + BattInf_Table
- + " ADD COLUMN `charge_curr_max` float DEFAULT '20'");
- }
- //---------------------------------------------------------------------------------------------//
- //为紫晶南网平台添加指定的列
- boolean isZijing = true;
- if(isZijing) {
- //添加电压等级列
- res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
- + " WHERE table_schema='db_battinf'"
- + " AND table_name='tb_battinf'"
- + " AND column_name='Vol_grade'");
- if(false == res.next()) {
- sqlMysqlExecute("ALTER TABLE " + BattInf_Table
- + " ADD COLUMN `Vol_grade` float DEFAULT '0'");
- }
-
- //生产厂家
- res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
- + " WHERE table_schema='db_battinf'"
- + " AND table_name='tb_battinf'"
- + " AND column_name='Manufacturers'");
- if(false == res.next()) {
- sqlMysqlExecute("ALTER TABLE " + BattInf_Table
- + " ADD COLUMN `Manufacturers` varchar(64) DEFAULT ''");
- }
-
- //是否资产级设备
- res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
- + " WHERE table_schema='db_battinf'"
- + " AND table_name='tb_battinf'"
- + " AND column_name='Assetequipment'");
- if(false == res.next()) {
- sqlMysqlExecute("ALTER TABLE " + BattInf_Table
- + " ADD COLUMN `Assetequipment` varchar(64) DEFAULT ''");
- }
-
-
-
- //设备分类全路径
- res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
- + " WHERE table_schema='db_battinf'"
- + " AND table_name='tb_battinf'"
- + " AND column_name='Deviceclasspath'");
- if(false == res.next()) {
- sqlMysqlExecute("ALTER TABLE " + BattInf_Table
- + " ADD COLUMN `Deviceclasspath` varchar(64) DEFAULT ''");
- }
-
- //设备运维部门
- res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
- + " WHERE table_schema='db_battinf'"
- + " AND table_name='tb_battinf'"
- + " AND column_name='Deviceoperationdepartment'");
- if(false == res.next()) {
- sqlMysqlExecute("ALTER TABLE " + BattInf_Table
- + " ADD COLUMN `DeviceOperationDepartment` varchar(64) DEFAULT ''");
- }
-
- //产权单位
- res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
- + " WHERE table_schema='db_battinf'"
- + " AND table_name='tb_battinf'"
- + " AND column_name='PropertyRightUnit'");
- if(false == res.next()) {
- sqlMysqlExecute("ALTER TABLE " + BattInf_Table
- + " ADD COLUMN `PropertyRightUnit` varchar(64) DEFAULT ''");
- }
-
- //产权属性
- res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
- + " WHERE table_schema='db_battinf'"
- + " AND table_name='tb_battinf'"
- + " AND column_name='PropertyAttribute'");
- if(false == res.next()) {
- sqlMysqlExecute("ALTER TABLE " + BattInf_Table
- + " ADD COLUMN `PropertyAttribute` varchar(64) DEFAULT ''");
- }
-
- //出厂编号
- res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
- + " WHERE table_schema='db_battinf'"
- + " AND table_name='tb_battinf'"
- + " AND column_name='FactoryNumber'");
- if(false == res.next()) {
- sqlMysqlExecute("ALTER TABLE " + BattInf_Table
- + " ADD COLUMN `FactoryNumber` varchar(64) DEFAULT ''");
- }
-
- //运维班组
- res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
- + " WHERE table_schema='db_battinf'"
- + " AND table_name='tb_battinf'"
- + " AND column_name='OperationTeam'");
- if(false == res.next()) {
- sqlMysqlExecute("ALTER TABLE " + BattInf_Table
- + " ADD COLUMN `OperationTeam` varchar(64) DEFAULT ''");
- }
-
- //设备身份编码
- res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
- + " WHERE table_schema='db_battinf'"
- + " AND table_name='tb_battinf'"
- + " AND column_name='DeviceIdentityCode'");
- if(false == res.next()) {
- sqlMysqlExecute("ALTER TABLE " + BattInf_Table
- + " ADD COLUMN `DeviceIdentityCode` varchar(64) DEFAULT ''");
- }
-
-
- //项目类型
- res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
- + " WHERE table_schema='db_battinf'"
- + " AND table_name='tb_battinf'"
- + " AND column_name='ProjectType'");
- if(false == res.next()) {
- sqlMysqlExecute("ALTER TABLE " + BattInf_Table
- + " ADD COLUMN `ProjectType` varchar(64) DEFAULT ''");
- }
-
- //蓄电池应用类型
- res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
- + " WHERE table_schema='db_battinf'"
- + " AND table_name='tb_battinf'"
- + " AND column_name='BatteryApplicationType'");
- if(false == res.next()) {
- sqlMysqlExecute("ALTER TABLE " + BattInf_Table
- + " ADD COLUMN `BatteryApplicationType` varchar(64) DEFAULT ''");
- }
-
- //单电池额定电压
- res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
- + " WHERE table_schema='db_battinf'"
- + " AND table_name='tb_battinf'"
- + " AND column_name='SingleRatedVoltage'");
- if(false == res.next()) {
- sqlMysqlExecute("ALTER TABLE " + BattInf_Table
- + " ADD COLUMN `SingleRatedVoltage` float DEFAULT '0'");
- }
-
- //是否实现远程监控
- res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
- + " WHERE table_schema='db_battinf'"
- + " AND table_name='tb_battinf'"
- + " AND column_name='RemoteMonitor'");
- if(false == res.next()) {
- sqlMysqlExecute("ALTER TABLE " + BattInf_Table
- + " ADD COLUMN `RemoteMonitor` varchar(64) DEFAULT ''");
- }
-
- //是否独立蓄电池室放置
- res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
- + " WHERE table_schema='db_battinf'"
- + " AND table_name='tb_battinf'"
- + " AND column_name='BatttIndependentPlaced'");
- if(false == res.next()) {
- sqlMysqlExecute("ALTER TABLE " + BattInf_Table
- + " ADD COLUMN `BatttIndependentPlaced` varchar(64) DEFAULT ''");
- }
-
- //均充电压设定值
- res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
- + " WHERE table_schema='db_battinf'"
- + " AND table_name='tb_battinf'"
- + " AND column_name='JunChargeVoltageValue'");
- if(false == res.next()) {
- sqlMysqlExecute("ALTER TABLE " + BattInf_Table
- + " ADD COLUMN `JunChargeVoltageValue` float DEFAULT '0'");
- }
-
-
- //公钥X
- res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
- + " WHERE table_schema='db_battinf'"
- + " AND table_name='tb_battinf'"
- + " AND column_name='PublicKeyX'");
- if(false == res.next()) {
- sqlMysqlExecute("ALTER TABLE " + BattInf_Table
- + " ADD COLUMN `PublicKeyX` varchar(200) NOT NULL DEFAULT ''");
- }
-
- //公钥Y
- res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
- + " WHERE table_schema='db_battinf'"
- + " AND table_name='tb_battinf'"
- + " AND column_name='PublicKeyY'");
- if(false == res.next()) {
- sqlMysqlExecute("ALTER TABLE " + BattInf_Table
- + " ADD COLUMN `PublicKeyY` varchar(200) NOT NULL DEFAULT ''");
- }
-
- //KeyID
- res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
- + " WHERE table_schema='db_battinf'"
- + " AND table_name='tb_battinf'"
- + " AND column_name='KeyID'");
- if(false == res.next()) {
- sqlMysqlExecute("ALTER TABLE " + BattInf_Table
- + " ADD COLUMN `KeyID` varchar(200) NOT NULL DEFAULT ''");
- }
-
- //唯一索引
- res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
- + " WHERE table_schema='db_battinf'"
- + " AND table_name='tb_battinf'"
- + " AND column_name='SerialNumber'");
- if(false == res.next()) {
- sqlMysqlExecute("ALTER TABLE " + BattInf_Table
- + " ADD COLUMN `SerialNumber` varchar(200) NOT NULL DEFAULT ''");
- }
- }
-
-
-
-
- //---------------------------------------------------------------------------------------------//
- 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(false == res.next()) {
- sqlMysqlExecute("ALTER TABLE " + BattMainInf_Table
- + " 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 " + BattTestDataInf_Table + " where column_name='test_starttime'");
- if(false == res.next())
- {
- sqlMysqlExecute("ALTER TABLE " + BattTestDataInf_Table + " ADD INDEX `index_test_record_count` (`test_record_count` ASC);");
- sqlMysqlExecute("ALTER TABLE " + BattTestDataInf_Table + " ADD INDEX `index_test_type` (`test_type` ASC);");
- sqlMysqlExecute("ALTER TABLE " + BattTestDataInf_Table + " ADD INDEX `index_test_starttime` (`test_starttime` ASC);");
- }
- //---------------------------------------------------------------------------------------------//
- //---------------------------------------------------------------------------------------------//
- res = sqlMysqlQuery("show index from " + BattResDataInf_Table + " where column_name='test_starttime'");
- if(false == res.next())
- {
- sqlMysqlExecute("ALTER TABLE " + BattResDataInf_Table + " ADD INDEX `index_test_record_count` (`test_record_count` ASC);");
- sqlMysqlExecute("ALTER TABLE " + BattResDataInf_Table + " ADD INDEX `index_test_type` (`test_type` ASC);");
- sqlMysqlExecute("ALTER TABLE " + BattResDataInf_Table + " 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(false == res.next())
- {
- sqlMysqlExecute("ALTER TABLE " + BattTestDataInf_Table
- + " 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(false == res.next())
- {
- sqlMysqlExecute("ALTER TABLE " + BattTestDataInf_Table
- + " 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 " + BattResDataInf_Table
- + " ADD COLUMN `upload_client_type` INT NOT NULL DEFAULT 0 AFTER `upload_usr_id`");
- }
- //---------------------------------------------------------------------------------------------//
- //----------------------------将num自增字段改为bigint类型---------------------------------------//
- 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(true == res.next()) {
- if(false == "bigint".equals(res.getString("DATA_TYPE").toLowerCase())) {
- sqlMysqlExecute("ALTER TABLE " + BattTestDataInf_Table + " MODIFY COLUMN num BIGINT NOT NULL AUTO_INCREMENT;");
- sqlMysqlExecute("ALTER TABLE " + BattResDataInf_Table + " MODIFY COLUMN num BIGINT NOT NULL AUTO_INCREMENT;");
- }
- }
- //---------------------------------------------------------------------------------------------//
- //----------------------------- tb_fbsdev_statechange_inf 表中新增2个字段 ----------------------------------------------------------------//
- res = sqlMysqlQuery("SELECT DATA_TYPE FROM information_schema.columns"
- + " WHERE table_schema='db_app_sys'"
- + " AND table_name='tb_fbsdev_statechange_inf'"
- + " AND column_name='eve_type'");
- if(false == res.next()) {
- sqlMysqlExecute("ALTER TABLE " + FBSDevStateChangeInf_Table
- + " ADD COLUMN `eve_type` int DEFAULT 0;");
- }
-
- res = sqlMysqlQuery("SELECT DATA_TYPE FROM information_schema.columns"
- + " WHERE table_schema='db_app_sys'"
- + " AND table_name='tb_fbsdev_statechange_inf'"
- + " AND column_name='module_num'");
- if(false == res.next()) {
- sqlMysqlExecute("ALTER TABLE " + FBSDevStateChangeInf_Table
- + " ADD COLUMN `module_num` int DEFAULT 0;");
- }
-
- }
-
- //使用db数据库
- public void sqlMysqlUseDB(String db) throws SQLException
- {
- sqlMysqlExecute("use " + db);
- }
-
- //获取电池组机房的个数
- public int getBattStationCount()
- {
- int st_count = 0;
-
- try {
- sqlMysqlUseDB(Sql_Mysql.DB_BATT_INF);
-
- String sql_str = "SELECT DISTINCT StationId FROM " + BattInf_Table;
- ResultSet res = sqlMysqlQuery(sql_str);
- while(res.next())
- {
- st_count++;
- }
- } catch (SQLException e) {
- e.printStackTrace();
- }
-
- return st_count;
- }
-
- /**
- * 检查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());
- }
-
- 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());
- }
- }
-
- //根据电池组id 获取机房名称
- public String getStationName(int bg_id)
- {
- String stname = " ";
-
- String sql_str = "SELECT DISTINCT StationName FROM "
- + Sql_Mysql.BattInf_Table + " WHERE BattGroupId=" + bg_id;
- ResultSet res = sqlMysqlQuery(sql_str);
- try {
- if(res.next())
- {
- stname = res.getString("StationName");
- }
- } catch (SQLException e) {
- e.printStackTrace();
- }
-
- return stname;
- }
-
- //根据电池组id 获取电池组名称
- public String getBattGroupName(int bg_id)
- {
- String bgname = " ";
-
- String sql_str = "SELECT DISTINCT BattGroupName FROM "
- + Sql_Mysql.BattInf_Table + " WHERE BattGroupId=" + bg_id;
- ResultSet res = sqlMysqlQuery(sql_str);
- try {
- if(res.next())
- {
- bgname = res.getString("BattGroupName");
- }
- } catch (SQLException e) {
- e.printStackTrace();
- }
-
- return bgname;
- }
-
- //根据用户的id获取用户的名称
- public String getUsrName(int uid)
- {
- String usrname = " ";
-
- String sql_str = "SELECT uName FROM " + Sql_Mysql.UserInfTable + " WHERE uId=" + uid;
- ResultSet res = sqlMysqlQuery(sql_str);
- try {
- if(res.next())
- {
- usrname = res.getString("uName");
- }
- } catch (SQLException e) {
- e.printStackTrace();
- }
-
- return usrname;
- }
-
- //根据format_type 获取format_str 的样式
- public String getTaskSmsFormatStr(String format_type)
- {
- String str = "";
- String sql_str = "SELECT DISTINCT "
- + Sql_Mysql.UserSmsFormatTable + ".format_str"
- + " FROM " + Sql_Mysql.UserSmsFormatTable
- + " WHERE "
- + Sql_Mysql.UserSmsFormatTable + ".format_type='" + format_type + "'";
-
- ResultSet sql_res = sqlMysqlQuery(sql_str);
- try {
- if(sql_res.next())
- {
- str = sql_res.getString("format_str");
- }
- } catch (SQLException e1) {
- // TODO Auto-generated catch block
- //e1.printStackTrace();
- }
-
- return str;
- }
-
- //根据作业id获取作业的名称
- public String getUsrTaskName(int taskid)
- {
- String taskname = " ";
-
- String sql_str = "SELECT task_name FROM " + Sql_Mysql.UserTaskTable + " 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;
- }
-
- //根据作业id获取用户作业的创建时间
- public String getUsrTaskCreateTime(int taskid)
- {
- String taskcreatetime = " ";
-
- String sql_str = "SELECT task_create_date FROM " + Sql_Mysql.UserTaskTable + " WHERE task_id=" + taskid;
- ResultSet res = sqlMysqlQuery(sql_str);
- try {
- if(res.next())
- {
- taskcreatetime = Com.getDateTimeFormat(res.getTimestamp("task_create_date"), Com.DTF_YMDhms);
- }
- } catch (SQLException e) {
- e.printStackTrace();
- }
-
- return taskcreatetime;
- }
-
-
- //获取新创建的记录的testrecordcount的值
- 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 {
- 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);
- }
-
- mysql_con.commit();
- } catch (SQLException e) {
- e.printStackTrace();
- res_exe = false;
- } finally {
- if(false == res_exe) {
- try {
- mysql_con.rollback();
- mysql_con.setAutoCommit(true);
- } catch (SQLException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
- }
- }
-
- return count;
- }
-}
+package com.sql;
+import java.sql.Connection;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.ArrayList;
+
+import com.base.Com;
+
+/**
+ * 创建数据库以及表格
+ * @author 军
+ *
+ */
+public class Sql_Mysql
+{
+ //--------------------------------------------------------------------------------------------//
+ final public static String DB_AppSys = "`db_app_sys`";
+ final public static String DB_BATT_INF = "`db_battinf`";
+ final public static String DB_BATT_TESTDATA = "`db_batt_testdata`";
+ final public static String DB_PARAM = "`db_param`";
+ final public static String DB_ALARM = "`db_alarm`";
+ final public static String DB_USER = "`db_user`";
+ final public static String DB_RamDB = "`db_ram_db`";
+ final public static String DB_RamDB_BATT_RT = "`db_ram_batt_rt`";
+
+ final public static String WEB_Site = "`web_site`";
+ final public static String DB_BATT_HISTORY = "`db_batt_history`";
+ final public static String DB_LD9_TESTDATA = "`db_ld9_testdata`";
+
+ 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_INF = "`db_pwrdev_inf`";
+ final public static String DB_FBO_TESTDATA = "`db_fbo_testdata`";
+
+
+ //--------------------------------------------------------------------------------------------//
+ public final static String TB_HardDevSmsState = "tb_hard_dev_sms_state";
+ //--------------------------------------------------------------------------------------------//
+ public final static String TB_AppSys = "tb_app_sys";
+ public final static String TB_ServerState = "tb_server_state";
+
+ public final static String TB_BattTestDataInf = "tb_batttestdata_inf";
+ public final static String TB_BattRtData = "tb_batt_rtdata";
+ public final static String TB_BattRtState = "tb_batt_rtstate";
+
+ public final static String TB_UserInf = "tb_user_inf";
+ public final static String TB_UserLog = "tb_user_log";
+ public final static String TB_UserBattGroup_BaoJiGroup = "tb_user_battgroup_baojigroup";
+ public final static String TB_UserBattGroup_BaoJiGroup_BattGroup = "tb_user_battgroup_baojigroup_battgroup";
+ public final static String TB_UserBattGroup_BaoJiGroup_Usr = "tb_user_battgroup_baojigroup_usr";
+ public final static String TB_UserTaskTemplate = "tb_user_task_batt_template";
+ public final static String TB_UserTask = "tb_user_task";
+ public final static String TB_UserPaiXiu = "tb_user_paixiu";
+ public final static String TB_UserListForTask = "tb_user_task_user_list";
+ public final static String TB_UserTaskParam = "tb_user_task_param";
+ public final static String TB_UserBattCkeckTask = "tb_user_task_batt_check";
+ public final static String TB_UserBattTestTask = "tb_user_task_batt_test";
+ public final static String TB_UserBattTaskChange = "tb_user_task_change";
+ public final static String TB_UserSms = "tb_user_sms";
+ public final static String TB_UserSmsFormat = "tb_user_sms_format";
+ public final static String TB_UserPermitGroup = "tb_user_permitgroup";
+ public final static String TB_UserPermitGroupData = "tb_user_permitgroup_data";
+ public final static String TB_UserJieJiaRi = "tb_user_jiejiari";
+ //--------------------------------------------------------------------------------------------//
+ //--------------------------------------------------------------------------------------------//
+ public final static String CInterfaceState_Table = DB_RamDB + ".`tb_cinterface_state`";
+
+ public final static String FBS9600SetParam_Table = DB_RamDB + ".`tb_fbs9600_setparam`";
+ //public final static String FBS9600Param_Table = DB_RamDB + ".`tb_fbs9600_param`";
+
+ //--------------------------------------------------------------------------------------------//
+ //--------------------------------------------------------------------------------------------//
+ public final static String AppSys_Table = DB_AppSys + ".`tb_app_sys`";
+ public final static String FBSDevStateChangeInf_Table = DB_AppSys + ".`tb_fbsdev_statechange_inf`";
+ public final static String BattInf_Table = DB_BATT_INF + ".`tb_battinf`";
+ public final static String BattCurrInf_Table = DB_BATT_INF + ".`tb_battcurr_inf`";
+ public final static String BattMainInf_Table = DB_BATT_INF + ".`tb_batt_maint_inf`";
+ public final static String BattMainProcess_Table = DB_BATT_INF + ".`tb_batt_maint_process`";
+
+
+
+ public final static String BattTestDataInf_Table = DB_BATT_TESTDATA + ".`tb_batttestdata_inf`";
+ public final static String BattResDataInf_Table = DB_BATT_TESTDATA + ".`tb_battresdata_inf`";
+ public final static String BattResData_Table = DB_BATT_TESTDATA + ".`tb_BattResData`";
+
+ public final static String UserInfTable = DB_USER + ".`tb_user_inf`";
+ public final static String UserLogTable = DB_USER + ".`tb_user_log`";
+ public final static String UserBattGroup_BaoJiGroup_Table = DB_USER + ".`tb_user_battgroup_baojigroup`";
+ public final static String UserBattGroup_BaoJiGroup_BattGroupTable = DB_USER + ".`tb_user_battgroup_baojigroup_battgroup`";
+ public final static String UserBattGroup_BaoJiGroup_UsrTable = DB_USER + ".`tb_user_battgroup_baojigroup_usr`";
+ public final static String UserTaskTemplateTable = DB_USER + ".`tb_user_task_batt_template`";
+ public final static String UserTaskTable = DB_USER + ".`tb_user_task`";
+ public final static String UserPaiXiuTable = DB_USER + ".`tb_user_paixiu`";
+ public final static String UserListForTaskTable = DB_USER + ".`tb_user_task_user_list`";
+ public final static String UserTaskParamTable = DB_USER + ".`tb_user_task_param`";
+ public final static String UserBattCheckTaskTable = DB_USER + ".`tb_user_task_batt_check`";
+ public final static String UserBattTestTaskTable = DB_USER + ".`tb_user_task_batt_test`";
+
+ public final static String UserBattTaskChangeTable = DB_USER + ".`tb_user_task_change`";
+
+ public final static String UsrBattMaintCheck_Table = DB_USER + ".`tb_user_battmaint_check`";
+ public final static String UsrBattMaintCheckProcess_Table = DB_USER + ".`tb_user_battmaint_check_process`";
+
+ public final static String UserSmsTable = DB_USER + ".`tb_user_sms`";
+ public final static String UserSmsFormatTable = DB_USER + ".`tb_user_sms_format`";
+ public final static String UserPermitGroupTable = DB_USER + ".`tb_user_permitgroup`";
+ public final static String UserPermitGroupDataTable = DB_USER + ".`tb_user_permitgroup_data`";
+ public final static String UserJieJiaRiTable = DB_USER + ".`tb_user_jiejiari`";
+
+
+ public final static String BTSStationEvent_Table = DB_ALARM + ".`tb_bts_station_event`"; //机房状态历史表
+
+ /**************************** db_ram_db数据库 ***************************************************/
+ public final static String SocketClientStateTable = DB_RamDB + ".`tb_socketclient_state`";
+ public final static String ServerState_Table = DB_RamDB + ".`tb_server_state`";
+ public final static String Ld9_Updatestatue_Table = DB_RamDB + ".`tb_ld9_updatestatue`";
+ public final static String Ld9_State_Table = DB_RamDB + ".`tb_ld9_state`";
+ public final static String Ld9_Setparam_Table = DB_RamDB + ".`tb_ld9_setparam`";
+ public final static String Ld9_Concentratestate_Table = DB_RamDB + ".`tb_ld9_concentratestate`";
+ public final static String Ld9_Concentrateparam_Table = DB_RamDB + ".`tb_ld9_concentrateparam`";
+ public final static String HardDevSmsState_Table = DB_RamDB + ".`tb_hard_dev_sms_state`";
+ public final static String FBS9600State_Table = DB_RamDB + ".`tb_fbs9600_state`";
+ public final static String FBS9100_NiBian_State = DB_RamDB + ".tb_fbs9100s_nibian_state"; //61850离线养护计划参数表
+ public final static String FBS9100s_fod_state = DB_RamDB + ".tb_fbs9100s_fod_state"; //61850除硫状态表
+ public final static String FBS9100SetDLParam_Table = DB_RamDB + ".`tb_fbs9100s_fod_param`"; //61850除硫参数表
+ public final static String FBS9100S_DFUState_Table = DB_RamDB + ".`tb_fbs9100s_DFU_state`";
+ public final static String FBS9100S_DcDcState_Table = DB_RamDB + ".`tb_fbs9100s_dcdc_state`";
+ public final static String FBS9100SysParam_Table = DB_RamDB + ".`tb_fbs9100_sysparam`"; //系统参数表
+ public final static String FBS9100State_Table = DB_RamDB + ".`tb_fbs9100_state`";
+ public final static String FBS9100SetParam_Table = DB_RamDB + ".`tb_fbs9100_setparam`";
+ public final static String FBS9100_offlineyhplan = DB_RamDB + ".tb_fbs9100_offlineyhplan"; //61850离线养护计划参数表
+ public final static String FBS9100BattParam_Table = DB_RamDB + ".`tb_fbs9100_battparam`"; //电池组参数表
+ public final static String BTSStationState_Table = DB_RamDB + ".`tb_bts_station_state`"; //机房状态表
+ public final static String BTSGPRSState_Table = DB_RamDB + ".`tb_bts_gprs_state`"; //gprs 信息表
+ public final static String BMP7100State_Table = DB_RamDB + ".`tb_bpm7100_state`";
+ public final static String BattRtState_Table = DB_RamDB + ".`tb_batt_rtstate`";
+ public final static String BattRtData_Table = DB_RamDB + ".`tb_batt_rtdata`";
+ public final static String TB_61850_JHState_Table = DB_RamDB +".tb_61850_jhstate"; //61850均衡61850供电模块信息
+ public final static String RamDB_CMCC_POWER_Data = DB_RamDB + ".`cmcc_power_data`";
+ public final static String FBS9100_InsulatstateTable = DB_RamDB + ".`tb_fbs9100_insulatstate`"; //绝缘装置
+ public final static String FBS9100_CollectionstateTable = DB_RamDB + ".`tb_fbs9100_collectionstate`"; //汇集装置
+ public final static String FBS9100_ChargerstateTable = DB_RamDB + ".`tb_fbs9100_chargerstate`"; //充电机
+ public final static String Tb_Lithium_DataTable = DB_RamDB + ".`tb_lithium_data`"; //锂电池数据表
+
+ public final static String Tb_Batt_RSAlarm_Table = DB_RamDB + ".`tb_batt_rsalarm`"; //锂电池数据表
+ public final static String Tb_Batt_RTAlarm_Table = DB_RamDB + ".`tb_batt_rtalarm`"; //锂电池数据表
+
+ public final static String FBS9100s_stemnode_state_Table = DB_RamDB + ".`tb_fbs9100s_stemnode_state`"; //干节点状态表
+ public final static String FBS9100s_buscouple_state_Table = DB_RamDB + ".`tb_fbs9100s_buscouple_state`"; //母联开关状态表
+ public final static String Fgcd_filedownload_Table = DB_RamDB + ".`tb_fgcd_filedownload`"; //FGCD充放电一体机控制
+
+
+ /**************************** db_user数据库 ***************************************************/
+ public final static String User_Permitgroup_Table = DB_USER + ".`tb_user_permitgroup`"; //权限表
+ public final static String User_Permitgroup_Data_Table = DB_USER + ".`tb_user_permitgroup_data`"; //用户权限表
+ public final static String User_Task_User_List_Table = DB_USER + ".`tb_user_task_user_list`"; //
+ public final static String User_Task_Template_Table = DB_USER + ".`tb_user_task_template`"; //
+ public final static String User_Task_Param_Table = DB_USER + ".`tb_user_task_param`"; //
+ public final static String User_Task_Change_Table = DB_USER + ".`tb_user_task_change`"; //
+ public final static String User_Task_Batt_Test_Table = DB_USER + ".`tb_user_task_batt_test`"; //
+ public final static String User_Task_Batt_Template_Table = DB_USER + ".`tb_user_task_batt_template`"; //
+ public final static String User_Task_Batt_Check_Table = DB_USER + ".`tb_user_task_batt_check`"; //
+ public final static String User_Task_Table = DB_USER + ".`tb_user_task`"; //
+ public final static String User_Sms_Format_Table = DB_USER + ".`tb_user_sms_format`"; //
+ public final static String User_Sms_Table = DB_USER + ".`tb_user_sms`"; //
+ public final static String User_Paixiu_Table = DB_USER + ".`tb_user_paixiu`"; //
+ public final static String User_Log_Table = DB_USER + ".`tb_user_log`"; //
+ public final static String User_Jiejiari_Table = DB_USER + ".`tb_user_jiejiari`"; //
+ public final static String User_Face_Table = DB_USER + ".`tb_user_face`"; //
+ public final static String User_Battmaint_Check_Process_Table = DB_USER + ".`tb_user_battmaint_check_process`"; //
+ public final static String User_Battmaint_Check_Table = DB_USER + ".`tb_user_battmaint_check`"; //
+ public final static String User_Battgroup_Baojigroup_Usr_Table = DB_USER + ".`tb_user_battgroup_baojigroup_usr`"; //
+ public final static String User_Battgroup_Baojigroup_Battgroup_Table = DB_USER + ".`tb_user_battgroup_baojigroup_battgroup`"; //
+ public final static String User_Battgroup_Baojigroup_Table = DB_USER + ".`tb_user_battgroup_baojigroup`"; //
+ public final static String User_Limit_Table = DB_USER + ".`tb_user_limit`"; //
+ public static final String Tb_User_Work = DB_USER + ".`tb_user_work`";
+ public static final String Tb_Work_Alarm = DB_USER + ".`tb_work_alarm`";
+
+
+
+
+
+ /**************************** web_site数据库 ***************************************************/
+ public final static String ThreadUtil_Table = WEB_Site + ".`tb_thread_util`"; //线程帮助表
+ public final static String PositiomapUsr_Table = WEB_Site + ".`tb_positiomap_usr`"; //首页地图中心点定位表
+ public final static String ChartColor_Table = WEB_Site + ".`tb_chart_color`"; //用户实时监测页面指定柱状的颜色设置表
+ public final static String UserChart_Table = WEB_Site + ".`tb_user_chart`"; //班组管理表
+ public final static String ProcessSurvey_Table = WEB_Site + ".`tb_process_survey`"; //线程监控表
+ public final static String EchartsUsr_Table = WEB_Site + ".`tb_echarts_usr`"; //用户首页定制表
+ public final static String DevstateUsr_Table = WEB_Site + ".`tb_devstate_usr`"; //用户放电添加记录表
+ public final static String CustomPage_Table = WEB_Site + ".`tb_custompage`"; //导航数据表
+ public final static String Announce_Table = WEB_Site + ".`tb_announce`"; //公告表
+ public final static String BattAttention_Table = WEB_Site + ".`tb_batt_attention`"; //创建电池组单体关注表
+ public final static String BattpowerOff_Table = WEB_Site + ".`tb_battpower_off`"; //机房停电表
+ public final static String BadbattMon_Table = WEB_Site + ".`tb_badbatt_mon`"; //落后单体表
+ public final static String BadbattMonFlag_Table = WEB_Site + ".`tb_badbatt_mon_flag`"; //落后单体控制表
+ public final static String BattEndurance_Table = WEB_Site + ".`tb_batt_endurance`"; //机房续航表
+ public final static String BattParamLow_Table = WEB_Site + ".`tb_batt_param_low`"; //电池参数表
+ public final static String BattDischargeParam_Table = WEB_Site + ".`tb_battdischarge_param`"; //放电计划参数表
+ public final static String BattDischargePlan_Table = WEB_Site + ".`tb_battdischarge_plan`"; //放电计划表
+ public final static String Avoid_plan_Table = WEB_Site + ".`tb_avoid_plan`"; //放电计划规避时间表
+ public final static String BattMapInformation_Table = WEB_Site + ".`tb_battmap_information`"; //基站定位表
+ public final static String BattDischarge_Table = WEB_Site + ".`tb_batt_discharge`"; //基站故障表
+ public final static String BattReplace_Table = WEB_Site + ".`tb_batt_replace`"; //电池更换记录表
+ public final static String Dev_Restart_Plan_Table = WEB_Site +".`tb_dev_restart_plan`"; //设备定期重启计划表
+ public final static String Vip_User_Table = WEB_Site +".`tb_vip_user`"; //超级用户表
+ public final static String License_Table = WEB_Site +".`tb_license`"; //超级用户表
+ public final static String Database_Backup_Table = WEB_Site +".`tb_database_backup`"; //超级用户表
+
+
+ /***************************** db_battinf数据库 ***************************************/
+ public final static String DeverrorRecord_Table = DB_BATT_INF + ".`tb_deverror_record`"; //机房问题记录表
+ public final static String BattinfApply_TABLE = DB_BATT_INF + ".`tb_battinf_apply`"; //机房安装审批表
+ public final static String BattinfEx_TABLE = DB_BATT_INF + ".`tb_battinf_ex`"; //基站信息表
+ public final static String BattinfRebuild_TABLE = DB_BATT_INF + ".`tb_battinf_rebuild`"; //机房配组表
+ public final static String BattmonTestcap_TABLE = DB_BATT_INF + ".`tb_battmon_testcap`"; //机房标准测试曲线记录表
+ public final static String Battcurr_Inf_TABLE = DB_BATT_INF + ".`tb_battcurr_inf`";
+ public final static String Batt_Maint_Process_TABLE = DB_BATT_INF + ".`tb_batt_maint_process`";
+ public final static String Batt_Maint_Inf_TABLE = DB_BATT_INF + ".`tb_batt_maint_inf`";
+ public final static String Batt_Alm_Param_TABLE = DB_BATT_INF + ".`tb_batt_alm_param`";
+
+
+
+
+ /***************************** db_alarm数据库 ***************************************/
+ public final static String Devalarm_Data_Table = DB_ALARM + ".`tb_devalarm_data`";
+ public final static String Devalarm_Data__History_Table = DB_ALARM + ".`tb_devalarm_data_history`";
+ public final static String Bts_Station_Event_Table = DB_ALARM + ".`tb_bts_station_event`";
+ public final static String Battalarm_Data_Table = DB_ALARM + ".`tb_battalarm_data`";
+ public final static String Battalarm_Data_History_Table = DB_ALARM + ".`tb_battalarm_data_history`";
+ public final static String Tb_Alarm_Message = DB_ALARM + ".`tb_alarm_message`";
+
+
+ /***************************** db_app_sys 数据库 ***************************************/
+ public final static String Page_Param_Table = DB_AppSys + ".`tb_page_param`";
+ public final static String Fbsdev_Statechange_Inf_Table = DB_AppSys + ".`tb_fbsdev_statechange_inf`";
+ public final static String Dashboard_Module_Chart_Table = DB_AppSys + ".`tb_dashboard_module_chart`";
+ public final static String Dashboard_Module_Table = DB_AppSys + ".`tb_dashboard_module`";
+ public final static String Dashboard_Chart_Type_Table = DB_AppSys + ".`tb_dashboard_chart_type`";
+ public final static String App_Sys_Table = DB_AppSys + ".`tb_app_sys`";
+ public final static String App_Bts_Comm_Task_Table = DB_AppSys + ".`tb_app_bts_comm_task`";
+ public final static String App_Bts_Battgroup_Table = DB_AppSys + ".`tb_app_bts_battgroup`";
+ public final static String Menu_Table = DB_AppSys + ".`tb_menu`";
+
+ /***************************** db_ld9_testdata 数据库 ***************************************/
+ public final static String Ld9testdata_Inf_Table = DB_LD9_TESTDATA + ".`tb_ld9testdata_inf`";
+
+ /***************************** db_param 数据库 ***************************************/
+ public final static String Dev_Param_Table = DB_PARAM + ".`tb_dev_param`";
+ public final static String App_Param_Table = DB_PARAM + ".`tb_app_param`";
+ public final static String Alarm_Param_Table = DB_PARAM + ".`tb_alarm_param`";
+
+ /***************************** db_pwrdev_alarm 数据库 ************************************/
+ 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`";
+
+ /***************************** db_pwrdev_data_rt 数据库 ************************************/
+ 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`";
+
+ /***************************** db_pwrdev_inf 数据库 ************************************/
+ 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 Display_Config_Table = DB_PWRDEV_INF + ".`tb_display_config`";
+
+ public final static String Pwrapp_Stainf_Table = DB_PWRDEV_INF + ".`tb_pwrapp_stainf`";
+ public final static String Pwrapp_Dcinf_Table = DB_PWRDEV_INF + ".`tb_pwrapp_dcinf`";
+ public final static String Pwrapp_Acinf_Table = DB_PWRDEV_INF + ".`tb_pwrapp_acinf`";
+ public final static String Pwrapp_Acdcinf_Table = DB_PWRDEV_INF + ".`tb_pwrapp_acdcinf`";
+
+
+
+
+ /***************************** db_fbo_testdata 数据库 ************************************/
+ public final static String Fbotestdata_Inf_Table = DB_FBO_TESTDATA + ".`tb_fbotestdata_inf`";
+
+ //--------------------------------------------------------------------------------------------//
+ //--------------------------------------------------------------------------------------------//
+ public Connection mysql_con;
+
+ public Sql_Mysql(Connection conn)
+ {
+ mysql_con = conn;
+ }
+
+ /*
+ public static Connection getConnection() throws SQLException, java.lang.ClassNotFoundException{
+ String url = "jdbc:mysql://192.168.48.128:3306/studentinfo";
+ Class.forName("com.mysql.jdbc.Driver");
+ String userName = "root";
+ String password = "lmx8688139";
+ Connection con = DriverManager.getConnection(url,userName,password);
+ return con;
+ }
+ */
+
+ public void close_con()
+ {
+ try {
+ mysql_con.close();
+ } catch (SQLException e) {
+ e.printStackTrace();
+ }
+ }
+
+ public void checkAndCreateDB() throws SQLException
+ {
+ sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + DB_AppSys);
+ sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + DB_BATT_INF);
+ 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_RamDB);
+
+ //sqlMysqlExecute("DROP DATABASE IF EXISTS " + DB_RamDB_BATT_RT);
+ //sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + DB_RamDB_BATT_RT);
+
+ sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + WEB_Site);
+
+ String table_0 = "CREATE TABLE IF NOT EXISTS " + AppSys_Table
+ + " ( `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_Reinit_BattGroupData_EN` BOOLEAN NOT NULL DEFAULT false, "
+ + "`AppServer_Reinit_BattGroupData_LD9_EN` BOOLEAN NOT NULL DEFAULT false, " //用于9度程序重新加载设备
+ + "`AppServer_Reinit_Config_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 " + BattInf_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',"
+ + "`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 " + BattCurrInf_Table
+ + " ( `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 " + BattMainInf_Table
+ + " ( `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 " + BattMainProcess_Table
+ + " ( `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 " + UsrBattMaintCheck_Table
+ + " ( `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 " + UsrBattMaintCheckProcess_Table
+ + " ( `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 " + BattTestDataInf_Table
+ + " ( `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 " + BattResDataInf_Table
+ + " ( `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 " + SocketClientStateTable + "; ";
+ String table_7 = "CREATE TABLE IF NOT EXISTS " + SocketClientStateTable
+ + "(`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 " + FBSDevStateChangeInf_Table
+ + "(`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;";
+
+
+
+
+ String table_9 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.BTSStationEvent_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_event_type` INT NOT NULL DEFAULT 0, "
+ + "`station_event_trig` INT NOT NULL DEFAULT 0, "
+
+ + " INDEX index_dev_id (`dev_id`), "
+ + " INDEX index_station_id (`station_id`), "
+ + " PRIMARY KEY (`num`) ) "
+ + " ENGINE=InnoDB DEFAULT CHARSET=utf8";
+ //-------------------------------------------------------------------------------------//
+ //-------------------------------------------------------------------------------------//
+ sqlMysqlUseDB(Sql_Mysql.DB_AppSys);
+ if(false == sqlMysqlCheckIfTableExist(TB_AppSys))
+ {
+ sqlMysqlExecute(table_0);
+ sqlMysqlExecute("INSERT INTO " + Sql_Mysql.AppSys_Table
+ + " (AppName) VALUES ('蓄电池组综合管理系统')");
+ }
+ //-------------------------------------------------------------------------------------//
+ {
+ ResultSet rest = sqlMysqlQuery("SELECT AppServer_Version FROM " + Sql_Mysql.AppSys_Table);
+ if(rest.next()) {
+ float ser_version = rest.getFloat(1);
+ if(ser_version < 1.387) {
+ sqlMysqlExecute("UPDATE " + Sql_Mysql.AppSys_Table + " 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(false == res0.next()) {
+ sqlMysqlExecute("ALTER TABLE " + AppSys_Table
+ + " 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(false == res0.next()) {
+ sqlMysqlExecute("ALTER TABLE " + AppSys_Table
+ + " ADD COLUMN `SqlDB_BackUpManual_EN` BOOLEAN NOT NULL DEFAULT false"
+ + " AFTER `SqlDB_BackUpTime`");
+ }
+
+ /**************************** db_app_sys 表中添加 AppServer_Reinit_BattGroupData_EN列(重新载入电池组信息) *******************************************/
+ res0 = sqlMysqlQuery("SELECT * FROM information_schema.columns"
+ + " WHERE table_schema='db_app_sys'"
+ + " AND table_name='tb_app_sys'"
+ + " AND column_name='AppServer_Reinit_BattGroupData_EN'");
+ if(false == res0.next()) {
+ sqlMysqlExecute("ALTER TABLE " + AppSys_Table
+ + " ADD COLUMN `AppServer_Reinit_BattGroupData_EN` BOOLEAN NOT NULL DEFAULT false"
+ + " AFTER `SqlDB_BackUpManual_EN`");
+ }
+
+
+ /**************************** db_app_sys 表中添加 AppServer_Reinit_BattGroupData_EN 列(用于主程序动态调整运行参数) *******************************************/
+ res0 = sqlMysqlQuery("SELECT * FROM information_schema.columns"
+ + " WHERE table_schema='db_app_sys'"
+ + " AND table_name='tb_app_sys'"
+ + " AND column_name='AppServer_Reinit_Config_EN'");
+ if(false == res0.next()) {
+ sqlMysqlExecute("ALTER TABLE " + AppSys_Table
+ + " ADD COLUMN `AppServer_Reinit_Config_EN` BOOLEAN NOT NULL DEFAULT false"
+ + " AFTER `AppServer_Reinit_BattGroupData_EN`");
+ }
+
+
+ /**************************** db_app_sys 表中添加 AppServer_Reinit_BattGroupData_LD9_EN 列(用于LD9主程序动态加载设备参数) *******************************************/
+ res0 = sqlMysqlQuery("SELECT * FROM information_schema.columns"
+ + " WHERE table_schema='db_app_sys'"
+ + " AND table_name='tb_app_sys'"
+ + " AND column_name='AppServer_Reinit_BattGroupData_LD9_EN'");
+ if(false == res0.next()) {
+ sqlMysqlExecute("ALTER TABLE " + AppSys_Table
+ + " ADD COLUMN `AppServer_Reinit_BattGroupData_LD9_EN` BOOLEAN NOT NULL DEFAULT false"
+ + " AFTER `AppServer_Reinit_BattGroupData_EN`");
+ }
+ //---------------------------------------------------------------------------------------------//
+
+ 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);
+ sqlMysqlExecute(table_9);
+
+ //---------------------------------------------------------------------------------------------//
+ ResultSet res = sqlMysqlQuery("show index from " + BattInf_Table + " where column_name='BattGroupId'");
+ if(false == res.next())
+ {
+ sqlMysqlExecute("ALTER TABLE " + BattInf_Table + " ADD INDEX `index_station_id` (`StationId` ASC);");
+ sqlMysqlExecute("ALTER TABLE " + BattInf_Table + " ADD INDEX `index_battgroup_id` (`BattGroupId` ASC);");
+ sqlMysqlExecute("ALTER TABLE " + BattInf_Table + " ADD INDEX `index_station_name` (`StationName` ASC);");
+ sqlMysqlExecute("ALTER TABLE " + BattInf_Table + " ADD INDEX `index_battgroup_name` (`BattGroupName` ASC);");
+ }
+ //---------------------------------------------------------------------------------------------//
+ //-------------------------------- 2017-06-06 by mxpopstar ------------------------------------//
+ res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
+ + " WHERE table_schema='db_battinf'"
+ + " AND table_name='tb_battinf'"
+ + " AND column_name='BattGuarantDayCount'");
+ if(false == res.next()) {
+ sqlMysqlExecute("ALTER TABLE " + BattInf_Table
+ + " ADD COLUMN `BattGuarantDayCount` INT NOT NULL DEFAULT 1095 AFTER `BattInUseDate`");
+ }
+ //---------------------------------------------------------------------------------------------//
+ //-------------------------------- 2018-10-12 by lijun ( binf表中添加 Load_curr->负载电流 disCurrMax -> 最大核容电流 )------------------------------------//
+ res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
+ + " WHERE table_schema='db_battinf'"
+ + " AND table_name='tb_battinf'"
+ + " AND column_name='Load_curr'");
+ if(false == res.next()) {
+ sqlMysqlExecute("ALTER TABLE " + BattInf_Table
+ + " ADD COLUMN `Load_curr` float NOT NULL DEFAULT 15");
+ }
+
+ res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
+ + " WHERE table_schema='db_battinf'"
+ + " AND table_name='tb_battinf'"
+ + " AND column_name='disCurrMax'");
+ if(false == res.next()) {
+ sqlMysqlExecute("ALTER TABLE " + BattInf_Table
+ + " ADD COLUMN `disCurrMax` float NOT NULL DEFAULT 0");
+ }
+
+ //-------------------------------- 2018-10-16 by lijun ( binf表中添加 station_phone->基站手机号 )------------------------------------//
+ res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
+ + " WHERE table_schema='db_battinf'"
+ + " AND table_name='tb_battinf'"
+ + " AND column_name='station_phone'");
+ if(false == res.next()) {
+ sqlMysqlExecute("ALTER TABLE " + BattInf_Table
+ + " ADD COLUMN `station_phone` varchar(32) DEFAULT ''");
+ }
+
+ //---------------------------------------------------------------------------------------------//
+
+ //-------------------------------- 2018-10-16 by lijun ( binf表中添加 station_install->标识机房是否已经安装 )------------------------------------//
+ res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
+ + " WHERE table_schema='db_battinf'"
+ + " AND table_name='tb_battinf'"
+ + " AND column_name='station_install'");
+ if(false == res.next()) {
+ sqlMysqlExecute("ALTER TABLE " + BattInf_Table
+ + " ADD COLUMN `station_install` BOOLEAN NOT NULL DEFAULT false");
+ }
+
+ //---------------------------------------------------------------------------------------------//
+ //-------------------------------- 2018-11-29 by lijun ( binf表中添加 StationId_ex->基站站址编号 install_user-->安装人)------------------------------------//
+ res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
+ + " WHERE table_schema='db_battinf'"
+ + " AND table_name='tb_battinf'"
+ + " AND column_name='StationId_ex'");
+ if(false == res.next()) {
+ sqlMysqlExecute("ALTER TABLE " + BattInf_Table
+ + " ADD COLUMN `StationId_ex` varchar(20) DEFAULT '' AFTER StationId");
+ }
+ res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
+ + " WHERE table_schema='db_battinf'"
+ + " AND table_name='tb_battinf'"
+ + " AND column_name='install_user'");
+ if(false == res.next()) {
+ sqlMysqlExecute("ALTER TABLE " + BattInf_Table
+ + " ADD COLUMN `install_user` varchar(64) DEFAULT ''");
+ }
+
+ //---------------------------------------------------------------------------------------------//
+ //-------------------------------- 2018-12-12 by lijun ( binf表中添加 charge_curr_max 最大充电电流)------------------------------------//
+ res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
+ + " WHERE table_schema='db_battinf'"
+ + " AND table_name='tb_battinf'"
+ + " AND column_name='charge_curr_max'");
+ if(false == res.next()) {
+ sqlMysqlExecute("ALTER TABLE " + BattInf_Table
+ + " ADD COLUMN `charge_curr_max` float DEFAULT '20'");
+ }
+ //---------------------------------------------------------------------------------------------//
+ //为紫晶南网平台添加指定的列
+ boolean isZijing = true;
+ if(isZijing) {
+ //添加电压等级列
+ res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
+ + " WHERE table_schema='db_battinf'"
+ + " AND table_name='tb_battinf'"
+ + " AND column_name='Vol_grade'");
+ if(false == res.next()) {
+ sqlMysqlExecute("ALTER TABLE " + BattInf_Table
+ + " ADD COLUMN `Vol_grade` float DEFAULT '0'");
+ }
+
+ //生产厂家
+ res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
+ + " WHERE table_schema='db_battinf'"
+ + " AND table_name='tb_battinf'"
+ + " AND column_name='Manufacturers'");
+ if(false == res.next()) {
+ sqlMysqlExecute("ALTER TABLE " + BattInf_Table
+ + " ADD COLUMN `Manufacturers` varchar(64) DEFAULT ''");
+ }
+
+ //是否资产级设备
+ res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
+ + " WHERE table_schema='db_battinf'"
+ + " AND table_name='tb_battinf'"
+ + " AND column_name='Assetequipment'");
+ if(false == res.next()) {
+ sqlMysqlExecute("ALTER TABLE " + BattInf_Table
+ + " ADD COLUMN `Assetequipment` varchar(64) DEFAULT ''");
+ }
+
+
+
+ //设备分类全路径
+ res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
+ + " WHERE table_schema='db_battinf'"
+ + " AND table_name='tb_battinf'"
+ + " AND column_name='Deviceclasspath'");
+ if(false == res.next()) {
+ sqlMysqlExecute("ALTER TABLE " + BattInf_Table
+ + " ADD COLUMN `Deviceclasspath` varchar(64) DEFAULT ''");
+ }
+
+ //设备运维部门
+ res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
+ + " WHERE table_schema='db_battinf'"
+ + " AND table_name='tb_battinf'"
+ + " AND column_name='Deviceoperationdepartment'");
+ if(false == res.next()) {
+ sqlMysqlExecute("ALTER TABLE " + BattInf_Table
+ + " ADD COLUMN `DeviceOperationDepartment` varchar(64) DEFAULT ''");
+ }
+
+ //产权单位
+ res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
+ + " WHERE table_schema='db_battinf'"
+ + " AND table_name='tb_battinf'"
+ + " AND column_name='PropertyRightUnit'");
+ if(false == res.next()) {
+ sqlMysqlExecute("ALTER TABLE " + BattInf_Table
+ + " ADD COLUMN `PropertyRightUnit` varchar(64) DEFAULT ''");
+ }
+
+ //产权属性
+ res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
+ + " WHERE table_schema='db_battinf'"
+ + " AND table_name='tb_battinf'"
+ + " AND column_name='PropertyAttribute'");
+ if(false == res.next()) {
+ sqlMysqlExecute("ALTER TABLE " + BattInf_Table
+ + " ADD COLUMN `PropertyAttribute` varchar(64) DEFAULT ''");
+ }
+
+ //出厂编号
+ res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
+ + " WHERE table_schema='db_battinf'"
+ + " AND table_name='tb_battinf'"
+ + " AND column_name='FactoryNumber'");
+ if(false == res.next()) {
+ sqlMysqlExecute("ALTER TABLE " + BattInf_Table
+ + " ADD COLUMN `FactoryNumber` varchar(64) DEFAULT ''");
+ }
+
+ //运维班组
+ res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
+ + " WHERE table_schema='db_battinf'"
+ + " AND table_name='tb_battinf'"
+ + " AND column_name='OperationTeam'");
+ if(false == res.next()) {
+ sqlMysqlExecute("ALTER TABLE " + BattInf_Table
+ + " ADD COLUMN `OperationTeam` varchar(64) DEFAULT ''");
+ }
+
+ //设备身份编码
+ res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
+ + " WHERE table_schema='db_battinf'"
+ + " AND table_name='tb_battinf'"
+ + " AND column_name='DeviceIdentityCode'");
+ if(false == res.next()) {
+ sqlMysqlExecute("ALTER TABLE " + BattInf_Table
+ + " ADD COLUMN `DeviceIdentityCode` varchar(64) DEFAULT ''");
+ }
+
+
+ //项目类型
+ res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
+ + " WHERE table_schema='db_battinf'"
+ + " AND table_name='tb_battinf'"
+ + " AND column_name='ProjectType'");
+ if(false == res.next()) {
+ sqlMysqlExecute("ALTER TABLE " + BattInf_Table
+ + " ADD COLUMN `ProjectType` varchar(64) DEFAULT ''");
+ }
+
+ //蓄电池应用类型
+ res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
+ + " WHERE table_schema='db_battinf'"
+ + " AND table_name='tb_battinf'"
+ + " AND column_name='BatteryApplicationType'");
+ if(false == res.next()) {
+ sqlMysqlExecute("ALTER TABLE " + BattInf_Table
+ + " ADD COLUMN `BatteryApplicationType` varchar(64) DEFAULT ''");
+ }
+
+ //单电池额定电压
+ res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
+ + " WHERE table_schema='db_battinf'"
+ + " AND table_name='tb_battinf'"
+ + " AND column_name='SingleRatedVoltage'");
+ if(false == res.next()) {
+ sqlMysqlExecute("ALTER TABLE " + BattInf_Table
+ + " ADD COLUMN `SingleRatedVoltage` float DEFAULT '0'");
+ }
+
+ //是否实现远程监控
+ res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
+ + " WHERE table_schema='db_battinf'"
+ + " AND table_name='tb_battinf'"
+ + " AND column_name='RemoteMonitor'");
+ if(false == res.next()) {
+ sqlMysqlExecute("ALTER TABLE " + BattInf_Table
+ + " ADD COLUMN `RemoteMonitor` varchar(64) DEFAULT ''");
+ }
+
+ //是否独立蓄电池室放置
+ res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
+ + " WHERE table_schema='db_battinf'"
+ + " AND table_name='tb_battinf'"
+ + " AND column_name='BatttIndependentPlaced'");
+ if(false == res.next()) {
+ sqlMysqlExecute("ALTER TABLE " + BattInf_Table
+ + " ADD COLUMN `BatttIndependentPlaced` varchar(64) DEFAULT ''");
+ }
+
+ //均充电压设定值
+ res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
+ + " WHERE table_schema='db_battinf'"
+ + " AND table_name='tb_battinf'"
+ + " AND column_name='JunChargeVoltageValue'");
+ if(false == res.next()) {
+ sqlMysqlExecute("ALTER TABLE " + BattInf_Table
+ + " ADD COLUMN `JunChargeVoltageValue` float DEFAULT '0'");
+ }
+
+
+ //公钥X
+ res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
+ + " WHERE table_schema='db_battinf'"
+ + " AND table_name='tb_battinf'"
+ + " AND column_name='PublicKeyX'");
+ if(false == res.next()) {
+ sqlMysqlExecute("ALTER TABLE " + BattInf_Table
+ + " ADD COLUMN `PublicKeyX` varchar(200) NOT NULL DEFAULT ''");
+ }
+
+ //公钥Y
+ res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
+ + " WHERE table_schema='db_battinf'"
+ + " AND table_name='tb_battinf'"
+ + " AND column_name='PublicKeyY'");
+ if(false == res.next()) {
+ sqlMysqlExecute("ALTER TABLE " + BattInf_Table
+ + " ADD COLUMN `PublicKeyY` varchar(200) NOT NULL DEFAULT ''");
+ }
+
+ //KeyID
+ res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
+ + " WHERE table_schema='db_battinf'"
+ + " AND table_name='tb_battinf'"
+ + " AND column_name='KeyID'");
+ if(false == res.next()) {
+ sqlMysqlExecute("ALTER TABLE " + BattInf_Table
+ + " ADD COLUMN `KeyID` varchar(200) NOT NULL DEFAULT ''");
+ }
+
+ //唯一索引
+ res = sqlMysqlQuery("SELECT * FROM information_schema.columns"
+ + " WHERE table_schema='db_battinf'"
+ + " AND table_name='tb_battinf'"
+ + " AND column_name='SerialNumber'");
+ if(false == res.next()) {
+ sqlMysqlExecute("ALTER TABLE " + BattInf_Table
+ + " ADD COLUMN `SerialNumber` varchar(200) NOT NULL DEFAULT ''");
+ }
+ }
+
+
+
+
+ //---------------------------------------------------------------------------------------------//
+ 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(false == res.next()) {
+ sqlMysqlExecute("ALTER TABLE " + BattMainInf_Table
+ + " 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 " + BattTestDataInf_Table + " where column_name='test_starttime'");
+ if(false == res.next())
+ {
+ sqlMysqlExecute("ALTER TABLE " + BattTestDataInf_Table + " ADD INDEX `index_test_record_count` (`test_record_count` ASC);");
+ sqlMysqlExecute("ALTER TABLE " + BattTestDataInf_Table + " ADD INDEX `index_test_type` (`test_type` ASC);");
+ sqlMysqlExecute("ALTER TABLE " + BattTestDataInf_Table + " ADD INDEX `index_test_starttime` (`test_starttime` ASC);");
+ }
+ //---------------------------------------------------------------------------------------------//
+ //---------------------------------------------------------------------------------------------//
+ res = sqlMysqlQuery("show index from " + BattResDataInf_Table + " where column_name='test_starttime'");
+ if(false == res.next())
+ {
+ sqlMysqlExecute("ALTER TABLE " + BattResDataInf_Table + " ADD INDEX `index_test_record_count` (`test_record_count` ASC);");
+ sqlMysqlExecute("ALTER TABLE " + BattResDataInf_Table + " ADD INDEX `index_test_type` (`test_type` ASC);");
+ sqlMysqlExecute("ALTER TABLE " + BattResDataInf_Table + " 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(false == res.next())
+ {
+ sqlMysqlExecute("ALTER TABLE " + BattTestDataInf_Table
+ + " 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(false == res.next())
+ {
+ sqlMysqlExecute("ALTER TABLE " + BattTestDataInf_Table
+ + " 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 " + BattResDataInf_Table
+ + " ADD COLUMN `upload_client_type` INT NOT NULL DEFAULT 0 AFTER `upload_usr_id`");
+ }
+ //---------------------------------------------------------------------------------------------//
+ //----------------------------将num自增字段改为bigint类型---------------------------------------//
+ 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(true == res.next()) {
+ if(false == "bigint".equals(res.getString("DATA_TYPE").toLowerCase())) {
+ sqlMysqlExecute("ALTER TABLE " + BattTestDataInf_Table + " MODIFY COLUMN num BIGINT NOT NULL AUTO_INCREMENT;");
+ sqlMysqlExecute("ALTER TABLE " + BattResDataInf_Table + " MODIFY COLUMN num BIGINT NOT NULL AUTO_INCREMENT;");
+ }
+ }
+ //---------------------------------------------------------------------------------------------//
+ //----------------------------- tb_fbsdev_statechange_inf 表中新增2个字段 ----------------------------------------------------------------//
+ res = sqlMysqlQuery("SELECT DATA_TYPE FROM information_schema.columns"
+ + " WHERE table_schema='db_app_sys'"
+ + " AND table_name='tb_fbsdev_statechange_inf'"
+ + " AND column_name='eve_type'");
+ if(false == res.next()) {
+ sqlMysqlExecute("ALTER TABLE " + FBSDevStateChangeInf_Table
+ + " ADD COLUMN `eve_type` int DEFAULT 0;");
+ }
+
+ res = sqlMysqlQuery("SELECT DATA_TYPE FROM information_schema.columns"
+ + " WHERE table_schema='db_app_sys'"
+ + " AND table_name='tb_fbsdev_statechange_inf'"
+ + " AND column_name='module_num'");
+ if(false == res.next()) {
+ sqlMysqlExecute("ALTER TABLE " + FBSDevStateChangeInf_Table
+ + " ADD COLUMN `module_num` int DEFAULT 0;");
+ }
+
+ }
+
+ //使用db数据库
+ public void sqlMysqlUseDB(String db) throws SQLException
+ {
+ sqlMysqlExecute("use " + db);
+ }
+
+ //获取电池组机房的个数
+ public int getBattStationCount()
+ {
+ int st_count = 0;
+
+ try {
+ sqlMysqlUseDB(Sql_Mysql.DB_BATT_INF);
+
+ String sql_str = "SELECT DISTINCT StationId FROM " + BattInf_Table;
+ ResultSet res = sqlMysqlQuery(sql_str);
+ while(res.next())
+ {
+ st_count++;
+ }
+ } catch (SQLException e) {
+ e.printStackTrace();
+ }
+
+ return st_count;
+ }
+
+ /**
+ * 检查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());
+ }
+
+ 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());
+ }
+ }
+
+ //根据电池组id 获取机房名称
+ public String getStationName(int bg_id)
+ {
+ String stname = " ";
+
+ String sql_str = "SELECT DISTINCT StationName FROM "
+ + Sql_Mysql.BattInf_Table + " WHERE BattGroupId=" + bg_id;
+ ResultSet res = sqlMysqlQuery(sql_str);
+ try {
+ if(res.next())
+ {
+ stname = res.getString("StationName");
+ }
+ } catch (SQLException e) {
+ e.printStackTrace();
+ }
+
+ return stname;
+ }
+
+ //根据电池组id 获取电池组名称
+ public String getBattGroupName(int bg_id)
+ {
+ String bgname = " ";
+
+ String sql_str = "SELECT DISTINCT BattGroupName FROM "
+ + Sql_Mysql.BattInf_Table + " WHERE BattGroupId=" + bg_id;
+ ResultSet res = sqlMysqlQuery(sql_str);
+ try {
+ if(res.next())
+ {
+ bgname = res.getString("BattGroupName");
+ }
+ } catch (SQLException e) {
+ e.printStackTrace();
+ }
+
+ return bgname;
+ }
+
+ //根据用户的id获取用户的名称
+ public String getUsrName(int uid)
+ {
+ String usrname = " ";
+
+ String sql_str = "SELECT uName FROM " + Sql_Mysql.UserInfTable + " WHERE uId=" + uid;
+ ResultSet res = sqlMysqlQuery(sql_str);
+ try {
+ if(res.next())
+ {
+ usrname = res.getString("uName");
+ }
+ } catch (SQLException e) {
+ e.printStackTrace();
+ }
+
+ return usrname;
+ }
+
+ //根据format_type 获取format_str 的样式
+ public String getTaskSmsFormatStr(String format_type)
+ {
+ String str = "";
+ String sql_str = "SELECT DISTINCT "
+ + Sql_Mysql.UserSmsFormatTable + ".format_str"
+ + " FROM " + Sql_Mysql.UserSmsFormatTable
+ + " WHERE "
+ + Sql_Mysql.UserSmsFormatTable + ".format_type='" + format_type + "'";
+
+ ResultSet sql_res = sqlMysqlQuery(sql_str);
+ try {
+ if(sql_res.next())
+ {
+ str = sql_res.getString("format_str");
+ }
+ } catch (SQLException e1) {
+ // TODO Auto-generated catch block
+ //e1.printStackTrace();
+ }
+
+ return str;
+ }
+
+ //根据作业id获取作业的名称
+ public String getUsrTaskName(int taskid)
+ {
+ String taskname = " ";
+
+ String sql_str = "SELECT task_name FROM " + Sql_Mysql.UserTaskTable + " 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;
+ }
+
+ //根据作业id获取用户作业的创建时间
+ public String getUsrTaskCreateTime(int taskid)
+ {
+ String taskcreatetime = " ";
+
+ String sql_str = "SELECT task_create_date FROM " + Sql_Mysql.UserTaskTable + " WHERE task_id=" + taskid;
+ ResultSet res = sqlMysqlQuery(sql_str);
+ try {
+ if(res.next())
+ {
+ taskcreatetime = Com.getDateTimeFormat(res.getTimestamp("task_create_date"), Com.DTF_YMDhms);
+ }
+ } catch (SQLException e) {
+ e.printStackTrace();
+ }
+
+ return taskcreatetime;
+ }
+
+
+ //获取新创建的记录的testrecordcount的值
+ 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 {
+ 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);
+ }
+
+ mysql_con.commit();
+ } catch (SQLException e) {
+ e.printStackTrace();
+ res_exe = false;
+ } finally {
+ if(false == res_exe) {
+ try {
+ mysql_con.rollback();
+ mysql_con.setAutoCommit(true);
+ } catch (SQLException e) {
+ // TODO Auto-generated catch block
+ e.printStackTrace();
+ }
+ }
+ }
+
+ return count;
+ }
+}
--
Gitblit v1.9.1