package com.dev.btse.data;
|
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.ArrayList;
|
import java.util.Date;
|
import java.util.List;
|
|
import com.battmonitor.base.Com;
|
import com.battmonitor.sql.MysqlConnPool;
|
import com.battmonitor.sql.Sql_Mysql;
|
|
public class TmpSensor_SQL {
|
|
|
public static void createRealDataBase(MysqlConnPool conn_pool) {
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
sql.sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + Sql_Mysql.DB_Sensor); //´´½¨´«¸ÐÆ÷ÐÅÏ¢±í
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
|
public static void createHisDataBase(MysqlConnPool conn_pool) {
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
sql.sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + Sql_Mysql.DB_Sensor_History); //´´½¨´«¸ÐÆ÷ÀúÊ·Êý¾Ý±í
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
|
/**
|
* ´´½¨´«¸ÐÆ÷ÐÅÏ¢±í
|
* @param conn_pool
|
*/
|
public static void createSensor_infTable(MysqlConnPool conn_pool) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.SensorInf_Table + " (" +
|
" `num` bigint(20) NOT NULL AUTO_INCREMENT," +
|
" `province` varchar(255) NOT NULL COMMENT 'Ê¡' DEFAULT ''," +
|
" `city` varchar(255) NOT NULL COMMENT 'ÊÐ' DEFAULT ''," +
|
" `county` varchar(255) NOT NULL COMMENT 'Çø/ÏØ' DEFAULT ''," +
|
" `sensor_dev_id` int(11) NOT NULL DEFAULT '0' COMMENT 'É豸id'," +
|
" `device_name` varchar(255) NOT NULL COMMENT 'É豸Ãû³Æ' DEFAULT ''," +
|
" `note` varchar(255) NOT NULL DEFAULT ''," +
|
" `airtmp_alarm` float NOT NULL DEFAULT '52.5'," +
|
" `airhum_alarm` float NOT NULL DEFAULT '40.8'," +
|
" `smoke_alarm` int(11) NOT NULL DEFAULT '8000'," +
|
" PRIMARY KEY (`num`)," +
|
" KEY `int_sensor_dev_id` (`sensor_dev_id`) USING BTREE" +
|
") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
|
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
//Ìí¼Ó¿ÕÆøζȸ澯ÁÐ
|
ResultSet res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_sensor'"
|
+ " AND table_name='tb_sensor_inf'"
|
+ " AND column_name='airtmp_alarm'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.SensorInf_Table
|
+ " ADD COLUMN `airtmp_alarm` float NOT NULL DEFAULT '52.5';");
|
}
|
//Ìí¼Ó¿ÕÆøʪ¶È¸æ¾¯ÁÐ
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_sensor'"
|
+ " AND table_name='tb_sensor_inf'"
|
+ " AND column_name='airhum_alarm'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.SensorInf_Table
|
+ " ADD COLUMN `airhum_alarm` float NOT NULL DEFAULT '40.8';");
|
}
|
//Ìí¼ÓÑ̸и澯ÁÐ
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_sensor'"
|
+ " AND table_name='tb_sensor_inf'"
|
+ " AND column_name='smoke_alarm'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.SensorInf_Table
|
+ " ADD COLUMN `smoke_alarm` int(11) NOT NULL DEFAULT '8000';");
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ´´½¨´«¸ÐÆ÷¶¨Î»ÐÅÏ¢±í
|
* @param conn_pool
|
*/
|
public static void createSensor_mapinfoTable(MysqlConnPool conn_pool) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS "+ Sql_Mysql.SensorMapinfo_Table +" (" +
|
" `num` bigint(20) NOT NULL AUTO_INCREMENT," +
|
" `sensor_dev_id` int(11) NOT NULL DEFAULT ''," +
|
" `longitude` double NOT NULL DEFAULT '0'," +
|
" `latitude` double NOT NULL DEFAULT '0'," +
|
" `address` varchar(255) NOT NULL DEFAULT ''," +
|
" `note` varchar(255) NOT NULL DEFAULT ''," +
|
" PRIMARY KEY (`num`)," +
|
" UNIQUE KEY `unique_sensor_dev_id` (`sensor_dev_id`) USING BTREE" +
|
") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ´´½¨´«¸ÐÆ÷É豸ÐÅÏ¢±í
|
* @param conn_pool
|
*/
|
public static void createSensor_StateTable(MysqlConnPool conn_pool) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS "+Sql_Mysql.SensorState_Table+" (" +
|
" `num` bigint(20) NOT NULL AUTO_INCREMENT," +
|
" `sensor_dev_id` int(11) NOT NULL COMMENT 'É豸id'," +
|
" `record_time` datetime NOT NULL COMMENT '¼Ç¼ʱ¼ä'," +
|
" `airtmp` float NOT NULL COMMENT '¿ÕÆøζÈ'," +
|
" `airhum` float NOT NULL COMMENT '¿ÕÆøʪ¶È'," +
|
" `smoke` int(11) NOT NULL COMMENT 'Ñ̸Ð'," +
|
" `water` int(11) NOT NULL," +
|
" `lightintensity` int(11) NOT NULL," +
|
" `CO2concentration` int(11) NOT NULL," +
|
" `COconcentration` int(11) NOT NULL," +
|
" `CH4concentration` int(11) NOT NULL," +
|
" `O2concentration` int(11) NOT NULL," +
|
" `dev_commcount` int(11) NOT NULL," +
|
" `dev_errcommcount` int(11) NOT NULL," +
|
" `note` varchar(255) NOT NULL DEFAULT ''," +
|
" PRIMARY KEY (`num`)," +
|
" UNIQUE KEY `index_sensor_dev_id` (`sensor_dev_id`) USING BTREE" +
|
") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ¸ù¾ÝÉ豸id²éѯÊÇ·ñ´æÔÚµ±Ç°É豸
|
* @param conn_pool
|
* @param sensor_dev_id
|
* @return
|
*/
|
public static TmpSensor_inf queryTmpSensorById(MysqlConnPool conn_pool,int sensor_dev_id) {
|
TmpSensor_inf sensor = null;
|
String sql_str = "SELECT * FROM db_sensor.tb_sensor_inf WHERE sensor_dev_id = "+sensor_dev_id;
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
ResultSet res = sql.sqlMysqlQuery(sql_str);
|
if(res.next()) {
|
sensor = new TmpSensor_inf();
|
sensor.setCity(res.getString("city"));
|
sensor.setProvince(res.getString("province"));
|
sensor.setCounty(res.getString("county"));
|
sensor.setDevice_name(res.getString("device_name"));
|
sensor.setSensor_dev_id(res.getInt("sensor_dev_id"));
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
return sensor;
|
}
|
|
/**
|
* ²åÈë»ò¸üÐÂÉ豸״̬
|
* @param conn_pool
|
* @param state
|
*/
|
public static void insertOrUpdateTmpSensorState(MysqlConnPool conn_pool,TmpSensorState state) {
|
String sql_str = Sql_Mysql.SensorState_Table +
|
" SET sensor_dev_id = " + state.sensor_dev_id + "," +
|
" record_time = '" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms) + "'," +
|
" airtmp = " + state.getAirtmp()+"," +
|
" airhum = " + state.getAirhum()+"," +
|
" smoke = " + state.getSmoke()+"," +
|
" water = " + state.getWater()+"," +
|
" lightintensity = " + state.getLightintensity()+"," +
|
" CO2concentration = " + state.getCO2concentration()+"," +
|
" COconcentration = " + state.getCOconcentration()+"," +
|
" CH4concentration = " + state.getCH4concentration()+"," +
|
" O2concentration = " + state.getO2concentration()+"," +
|
" dev_commcount = " + state.getDev_commcount()+"," +
|
" dev_errcommcount = " + state.getDev_errcommcount();
|
String sql_str_replace = "REPLACE INTO " + sql_str;
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str_replace);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ²éѯËùÓеĴ«¸ÐÆ÷É豸
|
* @return
|
*/
|
public static List<TmpSensor_inf> queryAllTmpSensor(MysqlConnPool pool){
|
String sql_str =
|
" SELECT * " +
|
" FROM db_sensor.tb_sensor_inf " ;
|
List<TmpSensor_inf> tmpinf = new ArrayList<>();
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = sql.sqlMysqlQuery(sql_str);
|
try {
|
while(res.next()) {
|
TmpSensor_inf tmp = new TmpSensor_inf();
|
tmp.setCity(res.getString("city"));
|
tmp.setCounty(res.getString("county"));
|
tmp.setDevice_name(res.getString("device_name"));
|
tmp.setProvince(res.getString("province"));
|
tmp.setSensor_dev_id(res.getInt("sensor_dev_id"));
|
|
tmpinf.add(tmp);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
return tmpinf;
|
}
|
|
//²éѯָ¶¨µÄ´«¸ÐÆ÷µÄ״̬
|
public static TmpSensorState queryTmpSensorState(MysqlConnPool pool,int sensor_dev_id) {
|
TmpSensorState state = null ;
|
String sql_str = "SELECT * " +
|
" FROM " + Sql_Mysql.SensorState_Table +
|
" WHERE sensor_dev_id = " + sensor_dev_id;
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = sql.sqlMysqlQuery(sql_str);
|
try {
|
if(res.next()) {
|
state = new TmpSensorState();
|
state.setSensor_dev_id(res.getInt("sensor_dev_id"));
|
state.setAirhum(res.getFloat("airhum"));
|
state.setAirtmp(res.getFloat("airtmp"));
|
state.setSmoke(res.getInt("smoke"));
|
state.setWater(res.getInt("water"));
|
state.setLightintensity(res.getInt("lightintensity"));
|
state.setCO2concentration(res.getInt("cO2concentration"));
|
state.setCH4concentration(res.getInt("cH4concentration"));
|
state.setCOconcentration(res.getInt("cOconcentration"));
|
state.setO2concentration(res.getFloat("o2concentration"));
|
state.setRecord_time(res.getTimestamp("record_time"));
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
return state;
|
}
|
|
/**
|
* ´´½¨Ö¸¶¨µÄ´«¸ÐÆ÷ÀúÊ·Êý¾Ý±í
|
* @param pool
|
* @param sensor_dev_id
|
*/
|
public static void createTmpSensorHistoryDataTable(MysqlConnPool pool, int sensor_dev_id) {
|
String sql_str = "CREATE TABLE IF NOT EXISTS "+Sql_Mysql.DB_Sensor_History+".`tb_sensor_hisdata_" + sensor_dev_id + "` (" +
|
" `num` bigint(20) NOT NULL AUTO_INCREMENT," +
|
" `sensor_dev_id` int(11) NOT NULL DEFAULT '1' COMMENT 'É豸id'," +
|
" `record_time` datetime NOT NULL DEFAULT '2000-01-01 00:00:00'," +
|
" `airtmp` float NOT NULL DEFAULT '0' COMMENT '¿ÕÆøζÈ'," +
|
" `airhum` float NOT NULL DEFAULT '0' COMMENT '¿ÕÆøʪ¶È'," +
|
" `smoke` int(11) NOT NULL DEFAULT '100' COMMENT 'Ñ̸Ð'," +
|
" `water` int(11) NOT NULL DEFAULT '0' COMMENT 'Ë®½þ'," +
|
" `lightintensity` int(11) NOT NULL DEFAULT '0' COMMENT '¹âÕÕÇ¿¶È'," +
|
" `CO2concentration` int(11) NOT NULL DEFAULT '0' COMMENT 'CO2Ũ¶È'," +
|
" `COconcentration` int(11) NOT NULL DEFAULT '0'," +
|
" `CH4concentration` int(11) NOT NULL DEFAULT '0'," +
|
" `O2concentration` int(11) NOT NULL DEFAULT '0'," +
|
" `note` varchar(255) DEFAULT NULL DEFAULT ''," +
|
" PRIMARY KEY (`num`)," +
|
" KEY `index_sensor_dev_id` (`sensor_dev_id`) USING BTREE" +
|
") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
|
}
|
|
/**
|
* ±£´æÀúÊ·µÄ´«¸ÐÆ÷Êý¾Ý
|
* @param pool
|
* @param state
|
*/
|
public static void saveSensorHistoryData(MysqlConnPool pool,TmpSensorState state) {
|
String sql_str = "INSERT INTO "+Sql_Mysql.DB_Sensor_History +".`tb_sensor_hisdata_"+state.sensor_dev_id+"`"
|
+ "(sensor_dev_id,record_time,airtmp,airhum,smoke,water,lightintensity,CO2concentration,COconcentration,CH4concentration,O2concentration)"
|
+ " VALUES ("
|
+state.getSensor_dev_id()+", '"
|
+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)+"', "
|
+state.getAirtmp()+","
|
+state.getAirhum()+", "
|
+state.getSmoke()+","
|
+state.getWater()+", "
|
+state.getLightintensity()+", "
|
+state.getCO2concentration()+", "
|
+state.getCOconcentration()+","
|
+state.getCH4concentration()+", "
|
+state.getO2concentration()+
|
");";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
}
|
