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.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.log4j.Logger;
import com.szpg.db.dao.PgDeviceDao;
import com.szpg.db.data.PgAlarm;
import com.szpg.db.data.PgDevice;
import com.szpg.db.util.ConnectionManager;
import com.szpg.util.TimeFormat;
public class PgDeviceDaoImpl implements PgDeviceDao {
private final Logger logger = Logger.getLogger(this.getClass().getName());
@Override
public String findAcuCodeByCode(String code) {
Connection conn = null;
String queryStr = "SELECT PARTITION FROM PG_DEVICE WHERE ASSETCODE = ?";
Object[] param = new Object[1];
param[0] = code;
try {
conn = ConnectionManager.getConnectionFromC3P0();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
List<String> tempList = (List<String>) runner.query(conn, queryStr, new ColumnListHandler<String>(), param);
if (null != tempList && tempList.size() == 1) {
String partition = tempList.get(0);
logger.debug("根据设备代码查询所在ACU分区代码成功[" + partition + "]");
return partition;
}
else
return null;
} catch (Exception ex) {
logger.error("根据设备代码查询所在ACU分区代码异常", ex);
} finally {
try {
DbUtils.commitAndClose(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
@Override
public Integer findDeviceIdByCode(String code) {
Connection conn = null;
String queryStr = "SELECT ID FROM PG_DEVICE WHERE ASSETCODE = ?";
Object[] param = new Object[1];
param[0] = code;
try {
conn = ConnectionManager.getConnectionFromC3P0();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
List<Integer> tempList = (List<Integer>) runner.query(conn, queryStr, new ColumnListHandler<Integer>(), param);
if (null != tempList && tempList.size() == 1) {
int id = ((Number) tempList.get(0)).intValue();
logger.debug("根据代码查询设备ID成功[ID=" + id + ", CODE=" + code + "]");
return id;
}
else {
logger.debug("未找到设备[" + code + "]");
return -1;
}
} catch (Exception ex) {
logger.error("根据代码查询设备ID异常", ex);
} finally {
try {
DbUtils.commitAndClose(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
return -1;
}
@Override
public PgDevice findDeviceById(Integer deviceId) {
Connection conn = null;
String queryStr = "SELECT * FROM PG_DEVICE WHERE ID = ?";
Object[] params = new Object[1];
params[0] = deviceId;
try {
conn = ConnectionManager.getConnectionFromC3P0();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
PgDevice device = (PgDevice) runner.query(conn, queryStr, new BeanHandler<PgDevice>(PgDevice.class), params);
if (null != device) {
logger.debug("根据ID查询设备资产成功");
return device;
} else
return null;
} catch (Exception ex) {
logger.error("根据ID查询设备资产异常", ex);
} finally {
try {
DbUtils.commitAndClose(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
@Override
public PgDevice findDeviceByCode(String code) {
Connection conn = null;
String queryStr = "SELECT * FROM PG_DEVICE WHERE ASSETCODE = ?";
Object[] params = new Object[1];
params[0] = code;
try {
conn = ConnectionManager.getConnectionFromC3P0();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
PgDevice device = (PgDevice) runner.query(conn, queryStr, new BeanHandler<PgDevice>(PgDevice.class), params);
if (null != device) {
logger.debug("根据code查询设备资产成功");
return device;
} else
return null;
} catch (Exception ex) {
logger.error("根据code查询设备资产异常", ex);
} finally {
try {
DbUtils.commitAndClose(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
@Override
public List<PgDevice> findDeviceByAcu(String acucode) {
Connection conn = null;
String queryStr = "SELECT * FROM PG_DEVICE WHERE ASSETCODE LIKE ?";
Object[] params = new Object[1];
params[0] = acucode + "%";
try {
conn = ConnectionManager.getConnectionFromC3P0();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
List<PgDevice> list = (List<PgDevice>) runner.query(conn, queryStr, new BeanListHandler<PgDevice>(PgDevice.class), params);
if (null != list && list.isEmpty() == false) {
logger.debug("根据ACU地址查询设备资产列表成功[" + list.size() + "]");
return list;
} else
return null;
} catch (Exception ex) {
logger.error("根据ACU地址查询设备资产列表异常", ex);
} finally {
try {
DbUtils.commitAndClose(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
@Override
public List<PgDevice> findDeviceByPositionAndType(String acucode, String devtype, String cabin) {
Connection conn = null;
String queryStr = "SELECT * FROM PG_DEVICE WHERE ASSETCODE LIKE ?";
Object[] params = new Object[1];
params[0] = acucode + "." + devtype + "." + cabin + "%";
try {
conn = ConnectionManager.getConnectionFromC3P0();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
List<PgDevice> list = (List<PgDevice>) runner.query(conn, queryStr, new BeanListHandler<PgDevice>(PgDevice.class), params);
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 int addGenerlAlarmRecord(PgAlarm alarmRec) {
Connection conn = null;
String insertStr = "INSERT INTO PG_ALARM " +
"(ALARM_DATE, ALARM_VALUE, ALARMDEVID, ALARMTYPEID) " +
"VALUES (TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS'), ?, ?, ?)";
Object[] params = new Object[4];
params[0] = TimeFormat.formatTimestamp(alarmRec.getAlarm_date());
params[1] = alarmRec.getAlarm_value();
params[2] = alarmRec.getAlarmdevid();
params[3] = alarmRec.getAlarmtypeid();
try {
conn = ConnectionManager.getConnectionFromC3P0();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
int count = runner.update(conn, insertStr, params);
if (count > 0)
logger.debug("插入通用报警记录成功" + alarmRec);
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 updateDeviceStatus(int deviceid, String status) {
Connection conn = null;
String insertStr = "UPDATE PG_DEVICE SET ISOPEN = ? WHERE ID = ?";
Object[] params = new Object[2];
params[0] = status;
params[1] = deviceid;
try {
conn = ConnectionManager.getConnectionFromC3P0();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
int count = runner.update(conn, insertStr, params);
if (count > 0)
logger.debug("更新设备状态成功[status=" + status + ", 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;
}
}
