package com.database_util;
|
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
|
import com.sql.MysqlConnPool;
|
import com.sql.Sql_Mysql;
|
|
/**
|
* ¹¹½¨db_battinfÊý¾Ý¿âÖеÄÊý¾Ý¿â±í ¼ÈëÔÚ¸ÃÊý¾Ý¿âÖÐÐÂÌí¼ÓµÄÊý¾Ý¿â±í
|
* @author LiJun
|
*
|
*/
|
public class DB_battinf {
|
public static void init(MysqlConnPool pool, boolean recreate) {
|
createDB_battinf(pool); //´´½¨db_battinf Êý¾Ý¿â ²»´æÔÚÔò´´½¨db_battinfÊý¾Ý¿â
|
|
createBattinfApply_table(pool, recreate); //´´½¨»ú·¿°²×°ÉóÅú±í
|
|
createBattinfEx_TABLE(pool, recreate); //´´½¨»ùÕ¾ÐÅÏ¢
|
|
createDeverrorRecord_Table(pool, recreate); //´´½¨»ú·¿ÎÊÌâ¼Ç¼±í
|
|
createBattinfRebuild_TABLE(pool, recreate); //´´½¨»ú·¿Åä×é¼Ç¼±í
|
|
createBattmonTestcap_TABLE(pool, recreate); //´´½¨±ê×¼²âÊÔÇúÏß±í
|
}
|
|
/**
|
* ´´½¨ db_battinf Êý¾Ý¿â
|
* @param pool
|
*/
|
public static void createDB_battinf(MysqlConnPool pool) {
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + Sql_Mysql.DB_BATT_INF);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ´´½¨»ú·¿°²×°ÉóÅú±í
|
*/
|
public static void createBattinfApply_table(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.BattinfApply_TABLE;
|
String sql_str02 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.BattinfApply_TABLE + " (" +
|
" `num` int(11) NOT NULL AUTO_INCREMENT," +
|
" `apply_uid` int(11) NOT NULL DEFAULT '0'," +
|
" `confrim_uid` int(11) NOT NULL DEFAULT '0'," +
|
" `apply_date` datetime NOT NULL DEFAULT '2018-01-01 00:00:00'," +
|
" `apply_en` int(11) NOT NULL DEFAULT '0'," +
|
" `apply_note` varchar(256) NOT NULL DEFAULT ''," +
|
" `stationid` varchar(64) NOT NULL DEFAULT '0'," +
|
" `stationname` varchar(256) NOT NULL DEFAULT ''," +
|
" `stationname1` varchar(256) NOT NULL DEFAULT ''," +
|
" `stationname2` varchar(256) NOT NULL DEFAULT ''," +
|
" `stationname3` varchar(256) NOT NULL DEFAULT ''," +
|
" `stationname4` varchar(256) NOT NULL DEFAULT ''," +
|
" `stationname5` varchar(256) NOT NULL DEFAULT ''," +
|
" `stationname6` varchar(256) NOT NULL DEFAULT ''," +
|
" `stationname7` varchar(256) NOT NULL DEFAULT ''," +
|
" `stationname8` varchar(256) NOT NULL DEFAULT ''," +
|
" `stationname9` varchar(256) NOT NULL DEFAULT ''," +
|
" `stationip` varchar(64) NOT NULL DEFAULT '127.0.0.1'," +
|
" `FbsDeviceId` int(11) NOT NULL DEFAULT '0'," +
|
" `FbsDeviceIp` varchar(64) NOT NULL DEFAULT '127.000.000.001'," +
|
" `FbsDeviceIp_YM` varchar(64) NOT NULL DEFAULT '255.255.255.0'," +
|
" `FbsDeviceIp_WG` varchar(64) NOT NULL DEFAULT '127.0.0.1'," +
|
" `FBSDeviceName` varchar(64) NOT NULL DEFAULT 'É豸Ãû³Æ'," +
|
" `GroupIndexInFBSDevice` int(11) NOT NULL DEFAULT '1'," +
|
" `BattGroupId` int(11) NOT NULL DEFAULT '0'," +
|
" `BattGroupNum` int(11) NOT NULL DEFAULT '2'," +
|
" `BattGroupName` varchar(64) NOT NULL DEFAULT 'µç³Ø×é1'," +
|
" `FloatVolLevel` float NOT NULL DEFAULT '54'," +
|
" `OfflineVolLevel` float NOT NULL DEFAULT '42.9'," +
|
" `BattFloatCurrent` float NOT NULL DEFAULT '5'," +
|
" `MonCount` int(11) NOT NULL DEFAULT '24'," +
|
" `MonCapStd` float NOT NULL DEFAULT '300'," +
|
" `MonVolStd` float NOT NULL DEFAULT '2'," +
|
" `MonResStd` float NOT NULL DEFAULT '0.2'," +
|
" `MonSerStd` float NOT NULL DEFAULT '5000'," +
|
" `MonVolLowToAvg` float NOT NULL DEFAULT '0.05'," +
|
" `Load_curr` float NOT NULL DEFAULT '15'," +
|
" `DisCurrMax` float NOT NULL DEFAULT '0'," +
|
" `station_phone` varchar(64) NOT NULL DEFAULT ''," +
|
" `BattProducer` varchar(64) NOT NULL DEFAULT ''," +
|
" `BattModel` varchar(64) NOT NULL DEFAULT ''," +
|
" `BattProductDate` datetime NOT NULL DEFAULT '2018-09-18 00:00:00'," +
|
" `BattInUseDate` datetime NOT NULL DEFAULT '2018-09-18 00:00:00'," +
|
" `install_user` varchar(64) NOT NULL DEFAULT ''," +
|
" `check_en` int(11) NOT NULL DEFAULT '0'," +
|
" `check_uid` int(11) NOT NULL DEFAULT '0'," +
|
" `longitude` double NOT NULL DEFAULT '0'," +
|
" `latitude` double NOT NULL DEFAULT '0'," +
|
" `address` varchar(256) NOT NULL DEFAULT ''," +
|
" PRIMARY KEY (`num`)," +
|
" UNIQUE KEY `BattGroupId_index` (`BattGroupId`) USING BTREE," +
|
" KEY `FbsDeviceId_index` (`FbsDeviceId`)" +
|
") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
if(true == recreate) {
|
//sql.sqlMysqlExecute(sql_str01);
|
}
|
sql.sqlMysqlExecute(sql_str02);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
/**
|
* ´´½¨»ùÕ¾ÐÅÏ¢±í
|
*/
|
public static void createBattinfEx_TABLE(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.BattinfEx_TABLE;
|
String sql_str02 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.BattinfEx_TABLE + " (" +
|
" `num` int(11) NOT NULL AUTO_INCREMENT," +
|
" `StationId` varchar(20) NOT NULL DEFAULT '0' COMMENT '»ú·¿id'," +
|
" `FBSDeviceId` bigint(20) NOT NULL DEFAULT '0' COMMENT 'É豸id'," +
|
" `StationName` varchar(100) NOT NULL DEFAULT '' COMMENT '»ú·¿Ãû³Æ'," +
|
" `stationCode` varchar(100) NOT NULL DEFAULT '' COMMENT 'Õ¾Ö·±àÂë'," +
|
" `stationRegion` varchar(100) NOT NULL DEFAULT '' COMMENT 'ËùÊôÇøÓò'," +
|
" `keyWords` varchar(100) NOT NULL DEFAULT '' COMMENT '¹Ø¼ü×Ö'," +
|
" `affiliation` varchar(100) NOT NULL DEFAULT '' COMMENT 'ËùÊô×éÖ¯'," +
|
" `dataSources` varchar(100) NOT NULL DEFAULT '' COMMENT 'Êý¾ÝÀ´Ô´'," +
|
" `stationType` varchar(100) NOT NULL DEFAULT '' COMMENT 'Õ¾Ö·ÀàÐÍ'," +
|
" `longitude` double NOT NULL DEFAULT '0' COMMENT '¾¶È'," +
|
" `latitude` double NOT NULL DEFAULT '0' COMMENT 'γ¶È'," +
|
" `stationAddr` varchar(100) NOT NULL DEFAULT '' COMMENT 'Õ¾Ö·µØÖ·'," +
|
" `stationLevel` varchar(100) NOT NULL DEFAULT '' COMMENT 'Õ¾Ö·µÈ¼¶'," +
|
" `maintenanceState` varchar(100) NOT NULL DEFAULT '' COMMENT 'ά»¤×´Ì¬'," +
|
" `blockedState` tinyint(4) NOT NULL DEFAULT '0' COMMENT '·âËø״̬'," +
|
" `maintenanceCompany` varchar(100) NOT NULL DEFAULT '' COMMENT 'ά»¤µ¥Î»'," +
|
" `businessScenario` varchar(100) NOT NULL DEFAULT '' COMMENT 'ÒµÎñ³¡¾°'," +
|
" `CoverScenario` varchar(100) NOT NULL DEFAULT '' COMMENT '¸²¸Ç³¡¾°'," +
|
" `siteTerrain` varchar(100) NOT NULL DEFAULT '' COMMENT 'Õ¾Ö·µØÐÎ'," +
|
" `propertyRights` varchar(100) NOT NULL DEFAULT '' COMMENT '²úȨÐÔÖÊ'," +
|
" `propertyUnit` varchar(100) NOT NULL DEFAULT '' COMMENT '²úȨµ¥Î»'," +
|
" `isShare` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'ÊÇ·ñ¹²Ïí'," +
|
" `useUnit` varchar(100) NOT NULL DEFAULT '' COMMENT 'ʹÓõ¥Î»'," +
|
" `siteCode` varchar(100) NOT NULL DEFAULT '' COMMENT 'ÎïÀíÕ¾Ö·±àÂë'," +
|
" `historySiteCode` varchar(100) NOT NULL DEFAULT '' COMMENT 'ÀúÊ·ÎïÀíÕ¾Ö·±àÂë'," +
|
" `siteInternalNumber` varchar(100) NOT NULL DEFAULT '' COMMENT 'Õ¾Ö·ÄÚ²¿±àÂë'," +
|
" `sitePinyinReferred` varchar(100) NOT NULL DEFAULT '' COMMENT 'Õ¾Ö·Æ´Òô¼ò³Æ'," +
|
" `siteChineseReferred` varchar(100) NOT NULL DEFAULT '' COMMENT 'ÖÐÎļò³Æ'," +
|
" `siteMergeRecord` varchar(100) NOT NULL DEFAULT '' COMMENT 'Õ¾Ö·ºÏ²¢¼Ç¼'," +
|
" `isOpenBusiness` varchar(16) NOT NULL DEFAULT '' COMMENT 'ÊÇ·ñ¿ªÍ¨ÒµÎñ'," +
|
" `schoolPersonnel` varchar(64) NOT NULL DEFAULT '' COMMENT '¼ÈëÈËÔ±'," +
|
" `entryTime` varchar(64) NOT NULL DEFAULT '' COMMENT '¼Èëʱ¼ä'," +
|
" `modifyPeople` varchar(64) NOT NULL DEFAULT '' COMMENT 'ÐÞ¸ÄÈË'," +
|
" `modifyTime` varchar(64) NOT NULL DEFAULT '' COMMENT 'ÐÞ¸Äʱ¼ä'," +
|
" `note` varchar(255) NOT NULL DEFAULT '' COMMENT '±¸×¢'," +
|
" `siteReceivesMark` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Õ¾Ö·½ÓÊÕ±ê¼Ç'," +
|
" `siteValidity` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'ÊÇ·ñÓÐЧ'," +
|
" `siteNameCMCC` varchar(100) NOT NULL DEFAULT '' COMMENT 'Òƶ¯Õ¾Ö·Ãû³Æ'," +
|
" `siteNameCUCC` varchar(100) NOT NULL DEFAULT '' COMMENT 'ÁªÍ¨Õ¾Ö·Ãû³Æ'," +
|
" `siteNameCTC` varchar(100) NOT NULL DEFAULT '' COMMENT 'µçÐÅÕ¾Ö·Ãû³Æ'," +
|
" `producer` varchar(100) NOT NULL DEFAULT '' COMMENT 'µç³Ø²úÉÌ'," +
|
" `lastTimeLong` float NOT NULL DEFAULT '0' COMMENT 'Ðøº½²âËãʱ³¤'," +
|
" `lastTimeType` varchar(100) NOT NULL DEFAULT '' COMMENT 'Ðøº½²âËãÀà±ð'," +
|
" `lastTimeDate` datetime NOT NULL DEFAULT '1970-10-01 00:00:00' COMMENT 'Ðøº½·¢Éúʱ¼ä'," +
|
" `shareInfo` varchar(100) NOT NULL DEFAULT '' COMMENT '¹²ÏíÇé¿ö'," +
|
" `electPowerCMCC` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Òƶ¯ÊÇ·ñ¹ºÂò·¢µç·þÎñ'," +
|
" `electPowerCUCC` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'ÁªÍ¨ÊÇ·ñ¹ºÂò·¢µç·þÎñ'," +
|
" `electPowerCTC` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'µçÐÅÊÇ·ñ¹ºÂò·¢µç·þÎñ'," +
|
" `isCanElectPower` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'ÊÇ·ñ¾ß±¸·¢µçÌõ¼þ'," +
|
" `upperStationRoute` float NOT NULL DEFAULT '0' COMMENT 'ÉÏվ·³Ì'," +
|
" `upperStationDifficult` varchar(100) NOT NULL DEFAULT '' COMMENT 'ÉÏÕ¾ÄÑÒ׳̶È'," +
|
" `clienteleErrorService` varchar(255) NOT NULL DEFAULT '' COMMENT '¿Í»§ÎÊÌâ¿âδÍê³ÉÐîµç³ØάÐÞ'," +
|
" PRIMARY KEY (`num`)," +
|
" KEY `index_station_id` (`StationId`)," +
|
" KEY `index_battgroup_id` (`FBSDeviceId`)," +
|
" KEY `index_station_name` (`StationName`)" +
|
") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
if(true == recreate) {
|
//sql.sqlMysqlExecute(sql_str01);
|
}
|
sql.sqlMysqlExecute(sql_str02);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ´´½¨»ú·¿ÎÊÌâ¼Ç¼±í
|
*/
|
public static void createDeverrorRecord_Table(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.DeverrorRecord_Table;
|
String sql_str02 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.DeverrorRecord_Table + " (" +
|
" `num` int(11) NOT NULL AUTO_INCREMENT," +
|
" `stationid` varchar(64) NOT NULL DEFAULT '42000001'," +
|
" `record_time` datetime NOT NULL DEFAULT '2018-01-01 00:00:00'," +
|
" `error_descript` longtext NOT NULL," +
|
" `uid` int(11) NOT NULL DEFAULT '1002'," +
|
" `uname` varchar(64) NOT NULL DEFAULT ''," +
|
" `error_en` int(11) NOT NULL DEFAULT '0'," +
|
" `error_dispose` longtext NOT NULL," +
|
" `note` varchar(64) NOT NULL DEFAULT ''," +
|
" `picture_one` longblob," +
|
" `picture_two` longblob," +
|
" `picture_three` longblob," +
|
" `picture_four` longblob," +
|
" `picture_num` int(11) NOT NULL DEFAULT '0'," +
|
" `solve_picture_one` longblob," +
|
" `solve_picture_two` longblob," +
|
" `solve_picture_three` longblob," +
|
" `solve_picture_four` longblob," +
|
" `solve_picture_num` int(11) NOT NULL DEFAULT '0'," +
|
" `solve_uid` int(11) NOT NULL DEFAULT '1002'," +
|
" `solve_uname` varchar(64) NOT NULL DEFAULT ''," +
|
" `solve_record_time` datetime NOT NULL DEFAULT '2018-01-01 00:00:00'," +
|
" `error_state` int(11) NOT NULL DEFAULT '8'," +
|
" PRIMARY KEY (`num`)," +
|
" KEY `stationid_index` (`stationid`)" +
|
") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
if(true == recreate) {
|
//sql.sqlMysqlExecute(sql_str01);
|
}
|
sql.sqlMysqlExecute(sql_str02);
|
//Ìí¼ÓÉ豸¸æ¾¯ÁÐ
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_battinf'"
|
+ " AND table_name='tb_deverror_record'"
|
+ " AND column_name='error_state'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.DeverrorRecord_Table
|
+ " ADD COLUMN `error_state` int(11) NOT NULL DEFAULT '8'");
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
|
/**
|
* ´´½¨»ú·¿Åä×é¼Ç¼±í
|
*/
|
public static void createBattinfRebuild_TABLE(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.BattinfRebuild_TABLE;
|
String sql_str02 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.BattinfRebuild_TABLE + " (" +
|
" `num` int(11) NOT NULL AUTO_INCREMENT," +
|
" `old_stationname` varchar(64) NOT NULL DEFAULT ''," +
|
" `old_battgroupname` varchar(64) NOT NULL DEFAULT ''," +
|
" `old_battproducer` varchar(64) NOT NULL DEFAULT ''," +
|
" `old_stationid` varchar(64) NOT NULL DEFAULT '42000000'," +
|
" `old_battgroupid` int(11) NOT NULL DEFAULT '910000000'," +
|
" `old_monum` int(11) NOT NULL DEFAULT '0'," +
|
" `new_stationname` varchar(64) NOT NULL DEFAULT ''," +
|
" `new_battgroupname` varchar(64) NOT NULL DEFAULT ''," +
|
" `new_battproducer` varchar(64) NOT NULL DEFAULT ''," +
|
" `new_stationid` varchar(64) NOT NULL DEFAULT '42000000'," +
|
" `new_battgroupid` int(11) NOT NULL DEFAULT '910000000'," +
|
" `new_monum` int(11) NOT NULL DEFAULT '0'," +
|
" `rebuild_time` datetime NOT NULL DEFAULT '2018-09-30 00:00:00'," +
|
" `rebuild_uid` int(11) NOT NULL DEFAULT '1002'," +
|
" `rebuild_address` varchar(256) NOT NULL DEFAULT ''," +
|
" `rebuild_clear_type` int(11) NOT NULL DEFAULT '0'," +
|
" PRIMARY KEY (`num`)," +
|
" KEY `index_new_battgroupid` (`new_battgroupid`)," +
|
" KEY `index_new_stationid` (`new_stationid`)" +
|
") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
if(true == recreate) {
|
//sql.sqlMysqlExecute(sql_str01);
|
}
|
sql.sqlMysqlExecute(sql_str02);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
/**
|
* ´´½¨±ê×¼²âÊÔÇúÏ߼Ǽ±í
|
*/
|
public static void createBattmonTestcap_TABLE(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.BattmonTestcap_TABLE;
|
String sql_str02 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.BattmonTestcap_TABLE + " (" +
|
" `num` int(11) NOT NULL AUTO_INCREMENT," +
|
" `monvolstd` float NOT NULL DEFAULT '0'," +
|
" `moncapstd` float NOT NULL DEFAULT '0'," +
|
" `battproducer` varchar(64) NOT NULL DEFAULT ''," +
|
" `battmodel` varchar(64) NOT NULL DEFAULT ''," +
|
" `test_cap` double NOT NULL DEFAULT '0'," +
|
" `mon_vol` double NOT NULL DEFAULT '0'," +
|
" `note` varchar(64) NOT NULL DEFAULT ''," +
|
" PRIMARY KEY (`num`)," +
|
" KEY `test_cap_index` (`test_cap`)" +
|
") ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
if(true == recreate) {
|
//sql.sqlMysqlExecute(sql_str01);
|
}
|
sql.sqlMysqlExecute(sql_str02);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
}
|
