package com.sql;
|
import java.sql.Connection;
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.sql.Statement;
|
import java.util.ArrayList;
|
|
import org.apache.logging.log4j.LogManager;
|
import org.apache.logging.log4j.Logger;
|
|
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 DB_LD9_TESTDATA = "`db_ld9_testdata`";
|
final public static String WEB_Site = "web_site";
|
final public static String DB_Equipstand = "`db_equipstand`"; //¶ÀÁ¢É豸ÐÅÏ¢Êý¾Ý¿â
|
final public static String DB_Equip_History = "`db_equip_history`"; //¶ÀÁ¢É豸ÀúÊ·Êý¾ÝÊý¾Ý¿â
|
final public static String DB_Equip_Alarm = "`db_equip_alarm`";
|
|
final public static String DB_DynamicSystem = "`db_dynamicsystem`";
|
final public static String DB_Elemachinery = "`db_elemachinery`";
|
final public static String DB_Motor_Realhistory = "`db_motor_realhistory`";
|
|
final public static String DB_ElectricSystem = "`db_electricsystem`";
|
|
final public static String DB_Watersystem = "`db_watersystem`";
|
|
final public static String DB_Electric2MWSystem = "`db_electric2mwsystem`";
|
|
final public static String DB_MW_Motor = "`db_3.5mw_motor`"; //3.5mwʵʱÊý¾Ý¿â
|
final public static String DB_MW_Motor_History = "`db_3.5mw_motor_history`"; //3.5mwÀúʷʵʱÊý¾Ý¿â
|
|
//--------------------------------------------------------------------------------------------//
|
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_BattTestDataInf = "tb_batttestdata_inf";
|
|
public final static String TB_Ld9testdata_inf=DB_LD9_TESTDATA+".tb_ld9testdata_inf";
|
public final static String TB_Ld9testdata=DB_LD9_TESTDATA+".tb_ld9testdata_";
|
public final static String TB_Ld9testdatastop=DB_LD9_TESTDATA+".tb_ld9testdatastop_";
|
|
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 ServerState_Table = DB_RamDB + ".`tb_server_state`";
|
public final static String BattRtData_Table = DB_RamDB + ".`tb_batt_rtdata`";
|
public final static String BattRtState_Table = DB_RamDB + ".`tb_batt_rtstate`";
|
|
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 CInterfaceState_Table = DB_RamDB + ".`tb_cinterface_state`";
|
|
public final static String HardDevSmsState_Table = DB_RamDB + ".`tb_hard_dev_sms_state`";
|
public final static String FBS9100State_Table = DB_RamDB + ".`tb_fbs9100_state`";
|
public final static String FBS9100S_DcDcState_Table = DB_RamDB + ".`tb_fbs9100s_dcdc_state`";
|
public final static String FBS9100SetParam_Table = DB_RamDB + ".`tb_fbs9100_setparam`";
|
public final static String FBS9100S_DFUState_Table = DB_RamDB + ".`tb_fbs9100s_DFU_state`";
|
|
public final static String BMP7100State_Table = DB_RamDB + ".`tb_bpm7100_state`";
|
|
public final static String FBS9600State_Table = DB_RamDB + ".`tb_fbs9600_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 SocketClientStateTable = DB_RamDB + ".`tb_socketclient_state`";
|
public final static String RamDB_CMCC_POWER_Data = DB_RamDB + ".`cmcc_power_data`";
|
public final static String RamLD9_UpdateStatue_Table = DB_RamDB + ".`tb_ld9_updatestatue`"; //9¶ÈÉ豸Éý¼¶±í
|
|
public final static String LD9_ConcentratorParam_Table = DB_RamDB + ".`tb_ld9_ConcentrateParam`"; //9¶È»ã¼¯Æ÷²ÎÊý±í
|
public final static String LD9_ConcentratorState_Table = DB_RamDB + ".`tb_ld9_ConcentrateState`"; //9¶È»ã¼¯Æ÷״̬±í
|
|
public final static String BTSGPRSState_Table = DB_RamDB + ".`tb_bts_gprs_state`";
|
|
|
//--------------------------------------------------------------------------------------------//
|
//--------------------------------------------------------------------------------------------//
|
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 BattResData_Table = DB_BATT_TESTDATA + ".`tb_BattResData`";
|
public final static String BattResDataInf_Table = DB_BATT_TESTDATA + ".`tb_battresdata_inf`";
|
|
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 Tb_BattDischarge_ParamTable = WEB_Site+".tb_battdischarge_param"; //µç³Ø·Åµç¼Æ»®²ÎÊý±í
|
public final static String Tb_BattMap_InformationTable = WEB_Site+".tb_battmap_information"; //µç³Ø¶¨Î»ÐÅÏ¢±í
|
public final static String Tb_Batt_DevDischargeTable = WEB_Site+".tb_batt_devdischarge"; //¼¸ÖÖÔËÓªÉ̵ĵçѹµçÁ÷Çé¿ö±í
|
public final static String Tb_BattDischarge_PlanTable = WEB_Site+".tb_battdischarge_plan"; //µç³Ø·Åµç¼Æ»®±í
|
public final static String Tb_Batt_ElectricityTable = WEB_Site+".tb_batt_electricity"; //ÔËÓªÉÌÉ豸µÄÓõçÁ¿±í
|
public final static String Tb_Batt_Devdischarge_ParamTable = WEB_Site+".tb_batt_devdischarge_param"; //ÔËÓªÉÌÉ豸¼Ç¼µÄ²ÎÊý±í
|
public final static String Tb_Batt_Devdischarge_CMTable = WEB_Site+".tb_batt_devdischarge_CM"; //Òƶ¯Ã¿¸öʱ¼ä´ÁµÄµçѹµçÁ÷¹¦Âʱí
|
public final static String Tb_Batt_Devdischarge_CTTable = WEB_Site+".tb_batt_devdischarge_CT"; //µçÐÅÿ¸öʱ¼ä´ÁµÄµçѹµçÁ÷¹¦Âʱí
|
public final static String Tb_Batt_Devdischarge_CUTable = WEB_Site+".tb_batt_devdischarge_CU"; //ÁªÍ¨Ã¿¸öʱ¼ä´ÁµÄµçѹµçÁ÷¹¦Âʱí
|
public final static String Tb_CustomPageTable = WEB_Site + ".tb_custompage"; //Ò³Ãæµ¼º½±í
|
public final static String Tb_Vip_UserTable = WEB_Site + ".tb_vip_user"; //³¬¼¶¹ÜÀíÔ±±í
|
public final static String Tb_Batt_Param_LowTable = WEB_Site + ".tb_batt_param_low"; //Ðîµç³Ø¸÷Öָ澯¸ü»»²ÎÊý±í
|
public final static String Tb_AnnounceTable = WEB_Site + ".tb_announce"; //¹«¸æÐÅÏ¢±í
|
public final static String Tb_ProcessSurvey = WEB_Site + ".tb_process_survey"; //Ï̼߳à¿Ø±í
|
//--------------------------------------------------------------------------------------------//
|
public final static String Tb_Device_InfTable = DB_Equipstand + ".tb_device_inf"; //¶ÀÁ¢É豸-É豸ÐÅÏ¢±í
|
public final static String Tb_DevData_Rt = DB_Equipstand + ".tb_devdata_rt_"; //¶ÀÁ¢É豸-ʵʱ״̬±í
|
public final static String Tb_DevData_Rt_Cfg = DB_Equipstand + ".tb_devdata_rt_cfg"; //¶ÀÁ¢É豸-ʵʱ/Àúʷ״̬ÅäÖñí
|
public final static String Tb_Devdata_Backup_Cfg = DB_Equipstand + ".tb_devdata_backup_cfg"; //¶ÀÁ¢É豸-FTPÎļþ±¸·ÝÐÅÏ¢±í
|
//--------------------------------------------------------------------------------------------//
|
public final static String Tb_DevData_historyTable = DB_Equip_History + ".tb_devdata_history_"; //¶ÀÁ¢É豸-ÀúʷʵʱÊý¾Ý¼Ç¼±í
|
public final static String Tb_DevAlarm_Data = DB_Equip_Alarm + ".tb_devalarm_data_"; //¶ÀÁ¢É豸-ʵʱ¸æ¾¯Êý¾Ý¼Ç¼±í
|
public final static String Tb_DevAlarm_Data_History = DB_Equip_Alarm + ".tb_devalarm_data_history_"; //¶ÀÁ¢É豸-ÀúÊ·¸æ¾¯Êý¾Ý¼Ç¼±í
|
//--------------------------------------------------------------------------------------------//
|
public final static String Tb_Dynamicload_inf = DB_DynamicSystem + ".tb_dynamicload_inf"; //É豸ÐÅÏ¢±í
|
public final static String Tb_Dynamicload_State_rt = DB_DynamicSystem + ".tb_dynamicload_state_rt"; //É豸״̬±í
|
public final static String Tb_Dynamicload_Button_Rt = DB_DynamicSystem +".tb_dynamicload_button_rt"; //°´Å¥·´À¡±í
|
public final static String Tb_Dynamicload_Control = DB_DynamicSystem + ".tb_dynamicload_control"; //µçÔ´¹ñ¿ØÖƱí
|
//--------------------------------------------------------------------------------------------//
|
public final static String Tb_Motor_inf = DB_Elemachinery + ".tb_motor_inf"; //µç»úÐÅÏ¢±í
|
public final static String Tb_Motor_control = DB_Elemachinery + ".tb_motor_control"; //µç»ú¿ØÖƱí
|
public final static String Tb_Load_Motor_state = DB_Elemachinery + ".tb_load_motor_state"; //¼ÓÔصç»ú״̬±í
|
public final static String Tb_Subject_Motor_state = DB_Elemachinery + ".tb_subject_motor_state"; //ÊÜÊÔµç»ú״̬±í
|
public final static String Tb_Devalarm_Rt = DB_Elemachinery + ".tb_devalaram_rt"; //µç»úʵʱ¸æ¾¯¼Ç¼±í
|
public final static String Tb_Devalarm_History = DB_Elemachinery + ".tb_devalaram_history"; //µç»úÀúÊ·¸æ¾¯¼Ç¼±í
|
public final static String Tb_Motor_Readdata = DB_Motor_Realhistory + ".tb_motor_readdata_"; //µç»úÀúʷʵʱ¼Ç¼±í
|
|
//--------------------------------------------------------------------------------------------//
|
/*** µçÔ´¿ØÖÆÏà¹Ø±í *****/
|
public final static String Tb_Electric_Control_Single = DB_ElectricSystem + ".tb_electric_control_single"; //µçÔ´µ¥µã¿ØÖƱí
|
public final static String Tb_Electric_Power = DB_ElectricSystem + ".tb_electric_power"; //µçÔ´¿ØÖÆ״̬±í
|
public final static String Tb_Electric_Rt = DB_ElectricSystem + ".tb_electric_rt";
|
public final static String Tb_Electric_State = DB_ElectricSystem + ".tb_electric_state";
|
public final static String Tb_Electric_Switch = DB_ElectricSystem + ".tb_electric_switch";
|
|
//-----------------------------------------------------------------------------------------------//
|
/* Ë®ÀäϵͳÏà¹Ø±í */
|
public final static String Tb_water_inf = DB_Watersystem + ".tb_water_inf"; //Ë®ÀäϵͳÐÅÏ¢
|
public final static String Tb_water_rt = DB_Watersystem + ".tb_water_rt"; //Ë®ÀäϵͳʵʱÊý¾Ý
|
public final static String Tb_water_brach = DB_Watersystem + ".tb_water_brach"; //Ë®Àäϵͳ·Ö֧ʵʱÊý¾Ý
|
public final static String Tb_water_state = DB_Watersystem + ".tb_water_state"; //Ë®Àäϵͳʵʱ״̬
|
public final static String Tb_water_alarm = DB_Watersystem + ".tb_water_alarm"; //Ë®Àäϵͳ¸æ¾¯
|
public final static String Tb_water_realdata = DB_Watersystem + ".tb_water_realdata_"; //Ë®ÀäÀúʷʵʱ¼Ç¼±í
|
|
public final static String Tb_Device_ConnectState = DB_Electric2MWSystem+".tb_device_connectstate"; //É豸ͨѶ״̬±í
|
//--------------------------------------------------------------------------------------------//
|
public final static String Tb_MW_Motor_inf = DB_MW_Motor + ".`tb_3.5mw_motor_inf`";
|
public final static String Tb_Central_Monitor_Sys_St = DB_MW_Motor + ".tb_central_monitor_sys_st";
|
public final static String Tb_Central_Monitor_Sys_Rt = DB_MW_Motor + ".tb_central_monitor_sys_rt";
|
public final static String Tb_Central_Monitor_Sys_Control = DB_MW_Motor + ".tb_central_monitor_sys_control";
|
|
|
public final static String Tb_AFE_Inverter_RT = DB_MW_Motor + ".tb_afe_inverter_rt";
|
public final static String Tb_AFE_Inverter_Control = DB_MW_Motor + ".tb_afe_inverter_control";
|
public final static String Afe_Rectifier_State_Table = DB_MW_Motor + ".tb_afe_rectifier_state";
|
public final static String Afe_Inverter_State_Table = DB_MW_Motor + ".tb_afe_inverter_state";
|
|
|
|
//--------------------------------------------------------------------------------------------//
|
public final static String Tb_Central_Monitor_Sys_St_RealData = DB_MW_Motor_History + ".tb_central_monitor_sys_st_";
|
public final static String Tb_Central_Monitor_Sys_Rt_RealData = DB_MW_Motor_History + ".tb_central_monitor_sys_rt_";
|
public final static String Afe_Rectifier_State_RealData_Table = DB_MW_Motor_History + ".tb_afe_rectifier_state_";
|
public final static String Afe_Inverter_State_RealData_Table = DB_MW_Motor_History + ".tb_afe_inverter_state_";
|
|
//--------------------------------------------------------------------------------------------//
|
|
public Logger logger = null;
|
public Connection mysql_con;
|
|
public Sql_Mysql(Connection conn)
|
{
|
mysql_con = conn;
|
logger = LogManager.getLogger(this.getClass());
|
}
|
|
/*
|
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) {
|
logger.error(e.toString(),e);
|
}
|
}
|
|
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_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;";
|
//-------------------------------------------------------------------------------------//
|
//-------------------------------------------------------------------------------------//
|
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`");
|
}
|
//---------------------------------------------------------------------------------------------//
|
|
sqlMysqlExecute(table_1);
|
sqlMysqlExecute(table_2);
|
sqlMysqlExecute(table_3);
|
sqlMysqlExecute(table_3_1);
|
sqlMysqlExecute(table_3_2);
|
sqlMysqlExecute(table_3_2_1);
|
sqlMysqlExecute(table_4);
|
sqlMysqlExecute(table_5);
|
sqlMysqlExecute(table_6);
|
sqlMysqlExecute(table_7);
|
sqlMysqlExecute(table_8);
|
|
//---------------------------------------------------------------------------------------------//
|
ResultSet res = sqlMysqlQuery("show index from " + 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`");
|
}
|
//---------------------------------------------------------------------------------------------//
|
//---------------------------------------------------------------------------------------------//
|
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;");
|
}
|
}
|
//---------------------------------------------------------------------------------------------//
|
}
|
|
//ʹÓÃ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;
|
}
|
}
|
