package com.szpg.db.dao.impl;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.log4j.Logger;
import com.szpg.db.dao.PgAlarmRuleDao;
import com.szpg.db.data.PgAlarmRule;
import com.szpg.db.util.ConnectionManager;
import com.szpg.util.NumberFormat;
public class PgAlarmRuleDaoImpl implements PgAlarmRuleDao {
private final Logger logger = Logger.getLogger(this.getClass().getName());
@Override
public List<PgAlarmRule> findAllRule() {
Connection conn = null;
String queryStr = "SELECT * FROM PG_ALARM_RULE ORDER BY ID";
try {
conn = ConnectionManager.getConnectionFromC3P0();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
List<PgAlarmRule> list = (List<PgAlarmRule>) runner.query(conn, queryStr, new BeanListHandler<PgAlarmRule>(PgAlarmRule.class));
if (null != list && list.isEmpty() == false) {
logger.debug("查询所有报警规则成功[" + list.size() + "]");
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 PgAlarmRule findRuleById(Integer id) {
Connection conn = null;
String queryStr = "SELECT * FROM PG_ALARM_RULE WHERE ID = ?";
Object[] param = new Object[1];
param[0] = id;
try {
conn = ConnectionManager.getConnectionFromC3P0();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
List<PgAlarmRule> list = (List<PgAlarmRule>) runner.query(conn, queryStr, new BeanListHandler<PgAlarmRule>(PgAlarmRule.class), param);
if (null != list && list.size() == 1) {
logger.debug("根据ID查询报警规则成功[" + list.size() + "]");
return list.get(0);
} 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<PgAlarmRule> findRuleListByDevtype(String devtype) {
Connection conn = null;
String queryStr = "SELECT * FROM PG_ALARM_RULE WHERE DEVTYPE = ?";
Object[] param = new Object[1];
param[0] = devtype;
try {
conn = ConnectionManager.getConnectionFromC3P0();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
List<PgAlarmRule> list = (List<PgAlarmRule>) runner.query(conn, queryStr, new BeanListHandler<PgAlarmRule>(PgAlarmRule.class), param);
if (null != list && list.size() == 1) {
logger.debug("根据设备类型查询报警规则成功[" + list.size() + "]");
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 PgAlarmRule findRuleByDevice(Integer deviceId) {
Connection conn = null;
String queryStr = "SELECT * FROM PG_ALARM_RULE WHERE PGDEVICEID = ?";
Object[] param = new Object[1];
param[0] = deviceId;
try {
conn = ConnectionManager.getConnectionFromC3P0();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
List<PgAlarmRule> list = (List<PgAlarmRule>) runner.query(conn, queryStr, new BeanListHandler<PgAlarmRule>(PgAlarmRule.class), param);
if (null != list && list.size() == 1) {
logger.debug("根据设备ID查询报警规则成功[" + list.size() + "]");
return list.get(0);
} else
return null;
} catch (Exception ex) {
logger.error("根据设备ID查询报警规则异常", ex);
} finally {
try {
DbUtils.commitAndClose(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
@Override
public PgAlarmRule findRuleByDevtype(String devtype) {
Connection conn = null;
String queryStr = "SELECT * FROM PG_ALARM_RULE WHERE DEVTYPE = ?";
Object[] param = new Object[1];
param[0] = devtype;
try {
conn = ConnectionManager.getConnectionFromC3P0();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
List<PgAlarmRule> list = (List<PgAlarmRule>) runner.query(conn, queryStr, new BeanListHandler<PgAlarmRule>(PgAlarmRule.class), param);
if (null != list && list.size() == 1) {
logger.debug("根据设备ID查询报警规则成功[" + list.size() + "]");
return list.get(0);
} 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 updateWSHighThresholdByDevice(Integer deviceId, Double wd, Double sd) {
Connection conn = null;
String sql = "UPDATE PG_ALARM_RULE SET HIGHVALUE = ? WHERE PGDEVICEID = ?";
Object[] params = new Object[2];
params[0] = NumberFormat.format(wd, "0.00") + ";" + NumberFormat.format(sd, "0.00");
params[1] = deviceId;
try {
conn = ConnectionManager.getConnectionFromC3P0();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
int count = runner.update(conn, sql, params);
if (count > 0)
logger.debug("更新温湿度高限报警阈值成功");
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 int updateHighThresholdByDevice(Integer deviceId, Double high) {
Connection conn = null;
String sql = "UPDATE PG_ALARM_RULE SET HIGHVALUE = ? WHERE PGDEVICEID = ?";
Object[] params = new Object[2];
params[0] = NumberFormat.format(high, "0.00");
params[1] = deviceId;
try {
conn = ConnectionManager.getConnectionFromC3P0();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
int count = runner.update(conn, sql, params);
if (count > 0)
logger.debug("更新高限报警阈值成功");
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 int updateLowThresholdByDevice(Integer deviceId, Double low) {
Connection conn = null;
String sql = "UPDATE PG_ALARM_RULE SET LOWVALUE = ? WHERE PGDEVICEID = ?";
Object[] params = new Object[2];
params[0] = NumberFormat.format(low, "0.00");
params[1] = deviceId;
try {
conn = ConnectionManager.getConnectionFromC3P0();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
int count = runner.update(conn, sql, params);
if (count > 0)
logger.debug("更新低限报警阈值成功");
else {
logger.error("更新低限报警阈值失败！");
}
return count;
} catch (Exception ex) {
logger.error("更新低限报警阈值异常", ex);
} finally {
try {
DbUtils.commitAndClose(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
return 0;
}
}
