package com.szpg.db.dao.impl;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.log4j.Logger;
import com.szpg.db.dao.PgWsDao;
import com.szpg.db.data.PgTemphum;
import com.szpg.db.data.PgTemphumAlm;
import com.szpg.db.util.ConnectionManager;
import com.szpg.util.NumberFormat;
public class PgWsDaoImpl implements PgWsDao {
private final Logger logger = Logger.getLogger(this.getClass().getName());
@Override
public List<PgTemphum> findAllWsdz() {
Connection conn = null;
String queryStr = "SELECT ID, TEMP, HUM, LOGTIME, PGDEVICEID FROM PG_TEMPHUM";
try {
conn = ConnectionManager.getConnectionFromC3P0();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
List<Object[]> tempList = (List<Object[]>) runner.query(conn, queryStr, new ArrayListHandler());
if (null != tempList && tempList.isEmpty() == false) {
logger.debug("查询所有温湿度监测值成功[" + tempList.size() + "]");
List<PgTemphum> list = new ArrayList<PgTemphum>();
for (int i = 0; i < tempList.size(); i++) {
PgTemphum ws = new PgTemphum();
Object[] item = tempList.get(i);
ws.setId(((Number) item[0]).intValue());
ws.setTemp(Float.parseFloat((String) item[1]));
ws.setHum(Float.parseFloat((String) item[2]));
ws.setPgdeviceid(((Number) item[4]).intValue());
long tmValue = ((java.sql.Timestamp) ((oracle.sql.TIMESTAMP) item[3]).toJdbc()).getTime();
Calendar cal = Calendar.getInstance();
cal.setTimeInMillis(tmValue);
ws.setLogtime(cal.getTime());
ws.setUptime(cal.getTime());
list.add(ws);
}
return list;
}
else
return null;
} catch (Exception ex) {
logger.error("查询所有温湿度监测值异常", ex);
} finally {
try {
DbUtils.commitAndClose(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
@Override
public PgTemphum findWsdzById(Integer id) {
Connection conn = null;
String queryStr = "SELECT ID, TEMP, HUM, LOGTIME, PGDEVICEID FROM PG_TEMPHUM WHERE ID = ?";
Object[] param = new Object[1];
param[0] = id;
try {
conn = ConnectionManager.getConnectionFromC3P0();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
List<Object[]> tempList = (List<Object[]>) runner.query(conn, queryStr, new ArrayListHandler(), param);
if (null != tempList && tempList.size() == 1) {
PgTemphum ws = new PgTemphum();
Object[] item = tempList.get(0);
ws.setId(((Number) item[0]).intValue());
ws.setTemp(Float.parseFloat((String) item[1]));
ws.setHum(Float.parseFloat((String) item[2]));
ws.setPgdeviceid(((Number) item[4]).intValue());
long tmValue = ((java.sql.Timestamp) ((oracle.sql.TIMESTAMP) item[3]).toJdbc()).getTime();
Calendar cal = Calendar.getInstance();
cal.setTimeInMillis(tmValue);
ws.setLogtime(cal.getTime());
ws.setUptime(cal.getTime());
logger.debug("根据ID查询温湿度监测值成功" + ws + "]");
return ws;
}
else
return null;
} catch (Exception ex) {
logger.error("根据ID查询温湿度监测值异常", ex);
} finally {
try {
DbUtils.commitAndClose(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
@Override
public PgTemphum findLatestWsdzByDevice(Integer deviceid) {
Connection conn = null;
String queryStr = "SELECT ID, TEMP, HUM, LOGTIME, PGDEVICEID FROM PG_TEMPHUM WHERE PGDEVICEID = ? AND ROWNUM <= 5 ORDER BY LOGTIME DESC";
Object[] param = new Object[1];
param[0] = deviceid;
try {
conn = ConnectionManager.getConnectionFromC3P0();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
List<Object[]> tempList = (List<Object[]>) runner.query(conn, queryStr, new ArrayListHandler(), param);
if (null != tempList && tempList.isEmpty() == false) {
PgTemphum ws = new PgTemphum();
Object[] item = tempList.get(0);
ws.setId(((Number) item[0]).intValue());
ws.setTemp(Float.parseFloat((String) item[1]));
ws.setHum(Float.parseFloat((String) item[2]));
ws.setPgdeviceid(((Number) item[4]).intValue());
long tmValue = ((java.sql.Timestamp) ((oracle.sql.TIMESTAMP) item[3]).toJdbc()).getTime();
Calendar cal = Calendar.getInstance();
cal.setTimeInMillis(tmValue);
ws.setLogtime(cal.getTime());
ws.setUptime(cal.getTime());
logger.debug("根据设备ID查询最新的温湿度监测值成功" + ws + "]");
return ws;
}
else
return null;
} catch (Exception ex) {
logger.error("根据设备ID查询最新的温湿度监测值异常", ex);
} finally {
try {
DbUtils.commitAndClose(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
@Override
public List<PgTemphum> findWsdzByDevice(Integer deviceid) {
Connection conn = null;
String queryStr = "SELECT ID, TEMP, HUM, LOGTIME, PGDEVICEID FROM PG_TEMPHUM WHERE PGDEVICEID = ?";
Object[] param = new Object[1];
param[0] = deviceid;
try {
conn = ConnectionManager.getConnectionFromC3P0();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
List<Object[]> tempList = (List<Object[]>) runner.query(conn, queryStr, new ArrayListHandler());
if (null != tempList && tempList.isEmpty() == false) {
logger.debug("根据设备ID查询温湿度监测值成功[" + tempList.size() + "]");
List<PgTemphum> list = new ArrayList<PgTemphum>();
for (int i = 0; i < tempList.size(); i++) {
PgTemphum ws = new PgTemphum();
Object[] item = tempList.get(i);
ws.setId(((Number) item[0]).intValue());
ws.setTemp(Float.parseFloat((String) item[1]));
ws.setHum(Float.parseFloat((String) item[2]));
ws.setPgdeviceid(((Number) item[4]).intValue());
long tmValue = ((java.sql.Timestamp) ((oracle.sql.TIMESTAMP) item[3]).toJdbc()).getTime();
Calendar cal = Calendar.getInstance();
cal.setTimeInMillis(tmValue);
ws.setLogtime(cal.getTime());
ws.setUptime(cal.getTime());
list.add(ws);
}
return list;
}
else
return null;
} catch (Exception ex) {
logger.error("根据设备ID查询温湿度监测值异常", ex);
} finally {
try {
DbUtils.commitAndClose(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
@Override
public List<PgTemphum> findWsdzByDeviceAndTm(Integer deviceid, String start, String end) {
Connection conn = null;
String queryStr = "SELECT ID, TEMP, HUM, LOGTIME, PGDEVICEID FROM PG_TEMPHUM WHERE PGDEVICEID = ? AND LOGTIME >= TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS') AND LOGTIME <= TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS')";
Object[] params = new Object[3];
params[0] = deviceid;
params[1] = start;
params[2] = end;
try {
conn = ConnectionManager.getConnectionFromC3P0();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
List<Object[]> tempList = (List<Object[]>) runner.query(conn, queryStr, new ArrayListHandler());
if (null != tempList && tempList.isEmpty() == false) {
logger.debug("根据设备ID和时间查询温湿度监测值成功[" + tempList.size() + "]");
List<PgTemphum> list = new ArrayList<PgTemphum>();
for (int i = 0; i < tempList.size(); i++) {
PgTemphum ws = new PgTemphum();
Object[] item = tempList.get(i);
ws.setId(((Number) item[0]).intValue());
ws.setTemp(Float.parseFloat((String) item[1]));
ws.setHum(Float.parseFloat((String) item[2]));
ws.setPgdeviceid(((Number) item[4]).intValue());
long tmValue = ((java.sql.Timestamp) ((oracle.sql.TIMESTAMP) item[3]).toJdbc()).getTime();
Calendar cal = Calendar.getInstance();
cal.setTimeInMillis(tmValue);
ws.setLogtime(cal.getTime());
ws.setUptime(cal.getTime());
list.add(ws);
}
return list;
}
else
return null;
} catch (Exception ex) {
logger.error("根据设备ID和时间查询温湿度监测值异常", ex);
} finally {
try {
DbUtils.commitAndClose(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
@Override
public int addWsdzRecord(PgTemphum ws) {
return addWsdzRecord(ws.getTemp(), ws.getHum(), ws.getTmStr(), ws.getPgdeviceid());
}
@Override
public int addWsdzRecord(float temp, float hum, String tm, int deviceid) {
Connection conn = null;
String insertStr = "INSERT INTO PG_TEMPHUM " +
"(TEMP, HUM, LOGTIME, UPTIME, PGDEVICEID) " +
"VALUES (?, ?, TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS'), TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS'), ?)";
Object[] params = new Object[5];
params[0] = NumberFormat.format(temp, "0.00");
params[1] = NumberFormat.format(hum, "0.00");
params[2] = tm;
params[3] = tm;
params[4] = deviceid;
try {
conn = ConnectionManager.getConnectionFromC3P0();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
int count = runner.update(conn, insertStr, params);
if (count > 0)
logger.debug("插入温湿度监测值成功[tm=" + tm + ", temp=" + temp + ", hum=" + hum + ", deviceid=" + deviceid + "]");
else
logger.error("插入温湿度监测值失败！");
return count;
} catch (Exception ex) {
logger.error("插入温湿度监测值异常", ex);
} finally {
try {
DbUtils.commitAndClose(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
return 0;
}
@Override
public List<PgTemphumAlm> findAllWsdbj() {
Connection conn = null;
String queryStr = "SELECT ID, TEMPALM, HUMALM, LOGTIME, PGDEVICEID FROM PG_TEMPHUM_ALM";
try {
conn = ConnectionManager.getConnectionFromC3P0();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
List<Object[]> tempList = (List<Object[]>) runner.query(conn, queryStr, new ArrayListHandler());
if (null != tempList && tempList.isEmpty() == false) {
logger.debug("查询所有温湿度报警状态成功[" + tempList.size() + "]");
List<PgTemphumAlm> list = new ArrayList<PgTemphumAlm>();
for (int i = 0; i < tempList.size(); i++) {
PgTemphumAlm wsalm = new PgTemphumAlm();
Object[] item = tempList.get(i);
wsalm.setId(((Number) item[0]).intValue());
wsalm.setTempalm(Boolean.parseBoolean((String) item[1]));
wsalm.setHumalm(Boolean.parseBoolean((String) item[2]));
wsalm.setPgdeviceid(((Number) item[4]).intValue());
long tmValue = ((java.sql.Timestamp) ((oracle.sql.TIMESTAMP) item[3]).toJdbc()).getTime();
Calendar cal = Calendar.getInstance();
cal.setTimeInMillis(tmValue);
wsalm.setLogtime(cal.getTime());
wsalm.setUptime(cal.getTime());
list.add(wsalm);
}
return list;
}
else
return null;
} catch (Exception ex) {
logger.error("查询所有温湿度报警状态异常", ex);
} finally {
try {
DbUtils.commitAndClose(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
@Override
public PgTemphumAlm findWsdbjById(Integer id) {
Connection conn = null;
String queryStr = "SELECT ID, TEMPALM, HUMALM, LOGTIME, PGDEVICEID FROM PG_TEMPHUM_ALM WHERE ID = ?";
Object[] param = new Object[1];
param[0] = id;
try {
conn = ConnectionManager.getConnectionFromC3P0();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
List<Object[]> tempList = (List<Object[]>) runner.query(conn, queryStr, new ArrayListHandler(), param);
if (null != tempList && tempList.size() == 1) {
PgTemphumAlm wsalm = new PgTemphumAlm();
Object[] item = tempList.get(0);
wsalm.setId(((Number) item[0]).intValue());
wsalm.setTempalm(Boolean.parseBoolean((String) item[1]));
wsalm.setHumalm(Boolean.parseBoolean((String) item[2]));
wsalm.setPgdeviceid(((Number) item[4]).intValue());
long tmValue = ((java.sql.Timestamp) ((oracle.sql.TIMESTAMP) item[3]).toJdbc()).getTime();
Calendar cal = Calendar.getInstance();
cal.setTimeInMillis(tmValue);
wsalm.setLogtime(cal.getTime());
wsalm.setUptime(cal.getTime());
logger.debug("根据ID查询温湿度报警状态成功" + wsalm + "]");
return wsalm;
}
else
return null;
} catch (Exception ex) {
logger.error("根据ID查询温湿度报警状态异常", ex);
} finally {
try {
DbUtils.commitAndClose(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
@Override
public List<PgTemphumAlm> findWsdbjByDevice(Integer deviceid) {
Connection conn = null;
String queryStr = "SELECT ID, TEMPALM, HUMALM, LOGTIME, PGDEVICEID FROM PG_TEMPHUM_ALM WHERE PGDEVICEID = ?";
Object[] param = new Object[1];
param[0] = deviceid;
try {
conn = ConnectionManager.getConnectionFromC3P0();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
List<Object[]> tempList = (List<Object[]>) runner.query(conn, queryStr, new ArrayListHandler());
if (null != tempList && tempList.isEmpty() == false) {
logger.debug("根据设备ID查询温湿度报警状态成功[" + tempList.size() + "]");
List<PgTemphumAlm> list = new ArrayList<PgTemphumAlm>();
for (int i = 0; i < tempList.size(); i++) {
PgTemphumAlm wsalm = new PgTemphumAlm();
Object[] item = tempList.get(i);
wsalm.setId(((Number) item[0]).intValue());
wsalm.setTempalm(Boolean.parseBoolean((String) item[1]));
wsalm.setHumalm(Boolean.parseBoolean((String) item[2]));
wsalm.setPgdeviceid(((Number) item[4]).intValue());
long tmValue = ((java.sql.Timestamp) ((oracle.sql.TIMESTAMP) item[3]).toJdbc()).getTime();
Calendar cal = Calendar.getInstance();
cal.setTimeInMillis(tmValue);
wsalm.setLogtime(cal.getTime());
wsalm.setUptime(cal.getTime());
list.add(wsalm);
}
return list;
}
else
return null;
} catch (Exception ex) {
logger.error("根据设备ID查询温湿度报警状态异常", ex);
} finally {
try {
DbUtils.commitAndClose(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
@Override
public List<PgTemphumAlm> findWsdbjByDeviceAndTm(Integer deviceid, String start, String end) {
Connection conn = null;
String queryStr = "SELECT ID, TEMPALM, HUMALM, LOGTIME, PGDEVICEID FROM PG_TEMPHUM_ALM WHERE PGDEVICEID = ? AND LOGTIME >= TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS') AND LOGTIME <= TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS')";
Object[] params = new Object[3];
params[0] = deviceid;
params[1] = start;
params[2] = end;
try {
conn = ConnectionManager.getConnectionFromC3P0();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
List<Object[]> tempList = (List<Object[]>) runner.query(conn, queryStr, new ArrayListHandler());
if (null != tempList && tempList.isEmpty() == false) {
logger.debug("根据设备ID和时间查询温湿度报警状态成功[" + tempList.size() + "]");
List<PgTemphumAlm> list = new ArrayList<PgTemphumAlm>();
for (int i = 0; i < tempList.size(); i++) {
PgTemphumAlm wsalm = new PgTemphumAlm();
Object[] item = tempList.get(i);
wsalm.setId(((Number) item[0]).intValue());
wsalm.setTempalm(Boolean.parseBoolean((String) item[1]));
wsalm.setHumalm(Boolean.parseBoolean((String) item[2]));
wsalm.setPgdeviceid(((Number) item[4]).intValue());
long tmValue = ((java.sql.Timestamp) ((oracle.sql.TIMESTAMP) item[3]).toJdbc()).getTime();
Calendar cal = Calendar.getInstance();
cal.setTimeInMillis(tmValue);
wsalm.setLogtime(cal.getTime());
wsalm.setUptime(cal.getTime());
list.add(wsalm);
}
return list;
}
else
return null;
} catch (Exception ex) {
logger.error("根据设备ID和时间查询温湿度报警状态异常", ex);
} finally {
try {
DbUtils.commitAndClose(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
@Override
public int addWsdbjRecord(PgTemphumAlm wsalm) {
return addWsdbjRecord(wsalm.getTempalm(), wsalm.getHumalm(), wsalm.getTmStr(), wsalm.getPgdeviceid());
}
@Override
public int addWsdbjRecord(boolean temp, boolean hum, String tm, int deviceid) {
Connection conn = null;
String insertStr = "INSERT INTO PG_TEMPHUM_ALM " +
"(TEMPALM, HUMALM, LOGTIME, UPTIME, PGDEVICEID) " +
"VALUES (?, ?, TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS'), TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS'), ?)";
Object[] params = new Object[5];
params[0] = temp;
params[1] = hum;
params[2] = tm;
params[3] = tm;
params[4] = deviceid;
try {
conn = ConnectionManager.getConnectionFromC3P0();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
int count = runner.update(conn, insertStr, params);
if (count > 0)
logger.debug("插入温湿度报警状态成功[tm=" + tm + ", temp=" + temp + ", hum=" + hum + ", deviceid=" + deviceid + "]");
else
logger.error("插入温湿度报警状态失败！");
return count;
} catch (Exception ex) {
logger.error("插入温湿度报警状态异常", ex);
} finally {
try {
DbUtils.commitAndClose(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
return 0;
}
}
