package com.database_util;
|
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.Date;
|
|
import com.base.Com;
|
import com.sql.MysqlConnPool;
|
import com.sql.Sql_Mysql;
|
|
public class DB_Param {
|
public static void init(MysqlConnPool pool, boolean recreate) {
|
System.out.println(" db_param init start at " + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms));
|
|
createDB_PARAM(pool);
|
|
createDev_Param_Table(pool, recreate);
|
|
createApp_Param_Table(pool, recreate);
|
|
createAlarm_Param_Table(pool, recreate);
|
|
System.out.println(" db_param init end at " + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms));
|
|
}
|
|
/**
|
* ´´½¨ db_param Êý¾Ý¿â
|
* @param pool
|
*/
|
public static void createDB_PARAM(MysqlConnPool pool) {
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + Sql_Mysql.DB_PARAM);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ´´½¨ tb_dev_param ±í
|
* @param pool
|
* @param recreate
|
*/
|
public static void createDev_Param_Table(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.Dev_Param_Table;
|
String sql_str02 = " CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Dev_Param_Table + " (" +
|
" num int(11) NOT NULL AUTO_INCREMENT," +
|
" dev_Id int(11) NOT NULL DEFAULT '91000000'," +
|
" alm_id int(11) NOT NULL DEFAULT '119001'," +
|
" alm_name varchar(64) NOT NULL DEFAULT 'Batt_Alarm_Type_'," +
|
" alm_high_coe float NOT NULL DEFAULT '0.9'," +
|
" alm_low_coe float NOT NULL DEFAULT '0.5'," +
|
" alm_high_level int(11) NOT NULL DEFAULT '4'," +
|
" alm_low_level int(11) NOT NULL DEFAULT '1'," +
|
" alm_high_en int(11) NOT NULL DEFAULT '0'," +
|
" alm_low_en int(11) NOT NULL DEFAULT '0'," +
|
" note varchar(64) NOT NULL DEFAULT ''," +
|
" `alm_high_coe_upper` float NOT NULL DEFAULT '3.2' COMMENT 'ÉÏÉÏÏ޸澯ϵÊýÖµ'," +
|
" `alm_high_coe_upper_en` tinyint(4) NOT NULL DEFAULT '1' COMMENT 'ÉÏÉÏÏÞʹÄÜ[0-²»ÆôÓà 1-ÆôÓÃ]'," +
|
" `alm_low_coe_lower` float NOT NULL DEFAULT '0' COMMENT 'ÏÂÏÂÏ޸澯ϵÊýÖµ'," +
|
" `alm_low_coe_lower_en` tinyint(4) NOT NULL DEFAULT '1' COMMENT 'ÏÂÏÂÏÞãÐֵʹÄÜ[0-²»ÆôÓà 1-ÆôÓÃ]'," +
|
" PRIMARY KEY (num)," +
|
" KEY index_alm_id (alm_id)," +
|
" KEY index_dev_id (dev_Id)" +
|
") 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);
|
|
|
//Ìí¼Óalm_high_coe_upper
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_param'"
|
+ " AND table_name='tb_dev_param'"
|
+ " AND column_name='alm_high_coe_upper'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Dev_Param_Table
|
+ " ADD COLUMN `alm_high_coe_upper` float NOT NULL DEFAULT '3.2' COMMENT 'ÉÏÉÏÏ޸澯ϵÊýÖµ';");
|
}
|
res = null;
|
//Ìí¼Óalm_high_coe_upper_en
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_param'"
|
+ " AND table_name='tb_dev_param'"
|
+ " AND column_name='alm_high_coe_upper_en'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Dev_Param_Table
|
+ " ADD COLUMN `alm_high_coe_upper_en` tinyint(4) NOT NULL DEFAULT '1' COMMENT 'ÉÏÉÏÏÞʹÄÜ[0-²»ÆôÓà 1-ÆôÓÃ]';");
|
}
|
res = null;
|
//Ìí¼Óalm_low_coe_lower
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_param'"
|
+ " AND table_name='tb_dev_param'"
|
+ " AND column_name='alm_low_coe_lower'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Dev_Param_Table
|
+ " ADD COLUMN `alm_low_coe_lower` float NOT NULL DEFAULT '0' COMMENT 'ÏÂÏÂÏ޸澯ϵÊýÖµ';");
|
}
|
res = null;
|
//Ìí¼Óalm_low_coe_lower_en
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_param'"
|
+ " AND table_name='tb_dev_param'"
|
+ " AND column_name='alm_low_coe_lower_en'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Dev_Param_Table
|
+ " ADD COLUMN `alm_low_coe_lower_en` tinyint(4) NOT NULL DEFAULT '1' COMMENT 'ÏÂÏÂÏÞãÐֵʹÄÜ[0-²»ÆôÓà 1-ÆôÓÃ]';");
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ´´½¨ tb_app_param ±í
|
* @param pool
|
* @param recreate
|
*/
|
public static void createApp_Param_Table(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.App_Param_Table;
|
String sql_str02 = " CREATE TABLE IF NOT EXISTS " + Sql_Mysql.App_Param_Table + " (" +
|
" num int(11) NOT NULL AUTO_INCREMENT," +
|
" param_name varchar(50) DEFAULT NULL," +
|
" param_value varchar(50) DEFAULT NULL," +
|
" param_caption varchar(200) DEFAULT NULL," +
|
" PRIMARY KEY (num)" +
|
") ENGINE=InnoDB AUTO_INCREMENT=17 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();
|
}
|
}
|
|
/**
|
* ´´½¨ tb_alarm_param ±í
|
* @param pool
|
* @param recreate
|
*/
|
public static void createAlarm_Param_Table(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.Alarm_Param_Table;
|
String sql_str02 = " CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Alarm_Param_Table + " (" +
|
" num int(11) NOT NULL AUTO_INCREMENT," +
|
" alm_id int(11) NOT NULL DEFAULT '0'," +
|
" alm_name varchar(50) NOT NULL DEFAULT ' '," +
|
" alm_high_coe float NOT NULL DEFAULT '0'," +
|
" alm_low_coe float NOT NULL DEFAULT '0'," +
|
" alm_high_level int(11) NOT NULL DEFAULT '0'," +
|
" alm_low_level int(11) NOT NULL DEFAULT '0'," +
|
" alm_high_en tinyint(1) NOT NULL DEFAULT '0'," +
|
" alm_low_en tinyint(1) NOT NULL DEFAULT '0'," +
|
" PRIMARY KEY (num)" +
|
") ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
if(true == recreate) {
|
sql.sqlMysqlExecute(sql_str01);
|
}
|
sql.sqlMysqlExecute(sql_str02);
|
|
//ÔÚÏßµçѹ¸æ¾¯
|
if(!checkAlarmParamExists(pool, SqlsUtils.ALM_TYPE_OnlineVol_ID)) {
|
sql.sqlMysqlExecute("INSERT INTO " + Sql_Mysql.Alarm_Param_Table + "(alm_id,alm_name,alm_high_coe,alm_low_coe,alm_high_level,alm_low_level,alm_high_en,alm_low_en) VALUES("+SqlsUtils.ALM_TYPE_OnlineVol_ID+",'Batt_Alarm_Type_OnlineVol',1.2,0.8,2,2,1,0)");
|
}
|
|
//×é¶Ëµçѹ¸æ¾¯
|
if(!checkAlarmParamExists(pool, SqlsUtils.ALM_TYPE_GroupVol_ID)) {
|
sql.sqlMysqlExecute("INSERT INTO " + Sql_Mysql.Alarm_Param_Table + "(alm_id,alm_name,alm_high_coe,alm_low_coe,alm_high_level,alm_low_level,alm_high_en,alm_low_en) VALUES("+SqlsUtils.ALM_TYPE_GroupVol_ID+",'Batt_Alarm_Type_GroupVol',1.2,0.8,2,2,1,1)");
|
}
|
//³äµçµçÁ÷¸ß
|
if(!checkAlarmParamExists(pool, SqlsUtils.ALM_TYPE_BattChargeCurr_ID)) {
|
sql.sqlMysqlExecute("INSERT INTO " + Sql_Mysql.Alarm_Param_Table + "(alm_id,alm_name,alm_high_coe,alm_low_coe,alm_high_level,alm_low_level,alm_high_en,alm_low_en) VALUES("+SqlsUtils.ALM_TYPE_BattChargeCurr_ID+",'Batt_Alarm_Type_BattChargeCurr',3,0,3,3,1,0)");
|
}
|
//·ÅµçµçÁ÷¸æ¾¯
|
if(!checkAlarmParamExists(pool, SqlsUtils.ALM_TYPE_BattDisChargeCurr_ID)) {
|
sql.sqlMysqlExecute("INSERT INTO " + Sql_Mysql.Alarm_Param_Table + "(alm_id,alm_name,alm_high_coe,alm_low_coe,alm_high_level,alm_low_level,alm_high_en,alm_low_en) VALUES("+SqlsUtils.ALM_TYPE_BattDisChargeCurr_ID+",'Batt_Alarm_Type_BattDisChargeCurr',3,0,3,3,1,0)");
|
}
|
//µ¥Ìåµçѹ¸æ¾¯
|
if(!checkAlarmParamExists(pool, SqlsUtils.ALM_TYPE_MonVol_ID)) {
|
sql.sqlMysqlExecute("INSERT INTO " + Sql_Mysql.Alarm_Param_Table + "(alm_id,alm_name,alm_high_coe,alm_low_coe,alm_high_level,alm_low_level,alm_high_en,alm_low_en) VALUES("+SqlsUtils.ALM_TYPE_MonVol_ID+",'Batt_Alarm_Type_MonVol',1.2,0.8,2,2,1,1)");
|
}
|
//µ¥Ìåζȸ澯
|
if(!checkAlarmParamExists(pool, SqlsUtils.ALM_TYPE_MonTmp_ID)) {
|
sql.sqlMysqlExecute("INSERT INTO " + Sql_Mysql.Alarm_Param_Table + "(alm_id,alm_name,alm_high_coe,alm_low_coe,alm_high_level,alm_low_level,alm_high_en,alm_low_en) VALUES("+SqlsUtils.ALM_TYPE_MonTmp_ID+",'Batt_Alarm_Type_MonTmp',1.5,0.5,2,2,1,1)");
|
}
|
//µ¥ÌåÄÚ×è¸æ¾¯
|
if(!checkAlarmParamExists(pool, SqlsUtils.ALM_TYPE_MonRes_ID)) {
|
sql.sqlMysqlExecute("INSERT INTO " + Sql_Mysql.Alarm_Param_Table + "(alm_id,alm_name,alm_high_coe,alm_low_coe,alm_high_level,alm_low_level,alm_high_en,alm_low_en) VALUES("+SqlsUtils.ALM_TYPE_MonRes_ID+",'Batt_Alarm_Type_MonRes',2,0.5,2,2,1,1)");
|
}
|
//Á¬½ÓÌõ×迹¸æ¾¯
|
if(!checkAlarmParamExists(pool, SqlsUtils.ALM_TYPE_ConnRes_ID)) {
|
sql.sqlMysqlExecute("INSERT INTO " + Sql_Mysql.Alarm_Param_Table + "(alm_id,alm_name,alm_high_coe,alm_low_coe,alm_high_level,alm_low_level,alm_high_en,alm_low_en) VALUES("+SqlsUtils.ALM_TYPE_ConnRes_ID+",'Batt_Alarm_Type_ConnRes',2,0.5,2,3,0,0)");
|
}
|
//·Åµçµ¥Ìåµçѹ¸æ¾¯
|
if(!checkAlarmParamExists(pool, SqlsUtils.ALM_TYPE_DisChargeMonVol_ID)) {
|
sql.sqlMysqlExecute("INSERT INTO " + Sql_Mysql.Alarm_Param_Table + "(alm_id,alm_name,alm_high_coe,alm_low_coe,alm_high_level,alm_low_level,alm_high_en,alm_low_en) VALUES("+SqlsUtils.ALM_TYPE_DisChargeMonVol_ID+",'Batt_Alarm_Type_DisChargeMonVol',1.2,0.8,2,2,0,0)");
|
}
|
//ÈÝÁ¿¸æ¾¯¸æ¾¯
|
if(!checkAlarmParamExists(pool, SqlsUtils.ALM_TYPE_MONCAP_ALMALARM_ID)) {
|
sql.sqlMysqlExecute("INSERT INTO " + Sql_Mysql.Alarm_Param_Table + "(alm_id,alm_name,alm_high_coe,alm_low_coe,alm_high_level,alm_low_level,alm_high_en,alm_low_en) VALUES("+SqlsUtils.ALM_TYPE_MONCAP_ALMALARM_ID+",'Batt_Alarm_Type_CapAlarm',3,0.8,3,2,0,1)");
|
}
|
//ÈÝÁ¿¸ü»»¸æ¾¯
|
if(!checkAlarmParamExists(pool, SqlsUtils.ALM_TYPE_MONCAP_CHANGEALARM_ID)) {
|
sql.sqlMysqlExecute("INSERT INTO " + Sql_Mysql.Alarm_Param_Table + "(alm_id,alm_name,alm_high_coe,alm_low_coe,alm_high_level,alm_low_level,alm_high_en,alm_low_en) VALUES("+SqlsUtils.ALM_TYPE_MONCAP_CHANGEALARM_ID+",'Batt_Alarm_Type_CapChange',2,0.6,2,2,0,1)");
|
}
|
|
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ¼ì²édb_param.tb_alarm_param ±íÖÐalm¼Ç¼ÊÇ·ñ´æÔÚ
|
* @param pool
|
* @param alm_id
|
* @return
|
*/
|
public static boolean checkAlarmParamExists(MysqlConnPool pool,int alm_id) {
|
boolean isExists = false;
|
String sql_str = "SELECT * FROM " + Sql_Mysql.Alarm_Param_Table + " WHERE alm_id = " + alm_id;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
|
try {
|
res = sql.sqlMysqlQuery(sql_str);
|
if(res.next()) {
|
isExists = true;
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
return isExists;
|
}
|
|
}
|
