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.PgAcuCmdDao;
import com.szpg.db.data.PgAcuCmd;
import com.szpg.db.util.ConnectionManager;
import com.szpg.util.TimeFormat;
public class PgAcuCmdDaoImpl implements PgAcuCmdDao {
private final Logger logger = Logger.getLogger(this.getClass().getName());
@Override
public List<PgAcuCmd> findAll() {
Connection conn = null;
String queryStr = "SELECT * FROM PG_ACU_CMD";
try {
conn = ConnectionManager.getConnectionFromC3P0();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
List<PgAcuCmd> list = (List<PgAcuCmd>) runner.query(conn, queryStr, new BeanListHandler<PgAcuCmd>(PgAcuCmd.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 PgAcuCmd findById(String id) {
Connection conn = null;
String queryStr = "SELECT * FROM PG_ACU_CMD WHERE ID = ?";
Object[] param = new Object[1];
param[0] = id;
try {
conn = ConnectionManager.getConnectionFromC3P0();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
List<PgAcuCmd> list = (List<PgAcuCmd>) runner.query(conn, queryStr, new BeanListHandler<PgAcuCmd>(PgAcuCmd.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 PgAcuCmd findLatestCmdByDestAndType(String dest, String type) {
Connection conn = null;
String queryStr = "SELECT C.* FROM PG_ACU_CMD C, PG_ACU A WHERE TIMEOUT='0' AND A.ACU_CODE=C.DEST_ACU_CODE AND A.ACU_DEST=? AND CMD_TYPE=? ORDER BY TM DESC";
Object[] params = new Object[2];
params[0] = dest;
params[1] = type;
try {
conn = ConnectionManager.getConnectionFromC3P0();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
List<PgAcuCmd> list = (List<PgAcuCmd>) runner.query(conn, queryStr, new BeanListHandler<PgAcuCmd>(PgAcuCmd.class), params);
if (null != list && list.isEmpty() == false) {
logger.debug("根据目的地址和命令类型查询内存命令成功[" + list.size() + "]");
return list.get(0);
} else
return null;
} catch (Exception ex) {
logger.error("根据目的地址和命令类型查询内存命令异常", ex);
} finally {
try {
DbUtils.commitAndClose(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
@Override
public PgAcuCmd findLatestCmdByHostAndType(String host, String type) {
Connection conn = null;
String queryStr = "SELECT C.* FROM PG_ACU_CMD C, PG_ACU A WHERE TIMEOUT='0' AND A.ACU_CODE=C.DEST_ACU_CODE AND A.ACU_HOST=? AND CMD_TYPE=? ORDER BY TM DESC";
Object[] params = new Object[2];
params[0] = host;
params[1] = type;
try {
conn = ConnectionManager.getConnectionFromC3P0();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
List<PgAcuCmd> list = (List<PgAcuCmd>) runner.query(conn, queryStr, new BeanListHandler<PgAcuCmd>(PgAcuCmd.class), params);
if (null != list && list.isEmpty() == false) {
logger.debug("根据主机地址和命令类型查询内存命令成功[" + list.size() + "]");
return list.get(0);
} else
return null;
} catch (Exception ex) {
logger.error("根据主机地址和命令类型查询内存命令异常", ex);
} finally {
try {
DbUtils.commitAndClose(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
@Override
public int addCmdRecord(PgAcuCmd cmd) {
Connection conn = null;
String insertStr = "INSERT INTO PG_ACU_CMD " +
"(ID, TM, CMD_TYPE, DEST_ACU_CODE, TIMEOUT) " +
"VALUES (?, TO_DATE(?, 'YYYYMMDDHH24MISS'), ?, ?, '0')";
Object[] params = new Object[4];
params[0] = cmd.getId();
params[1] = TimeFormat.format(cmd.getTm(), "yyyyMMddHHmmss");
params[2] = cmd.getCmd_type();
params[3] = cmd.getDest_acu_code();
try {
conn = ConnectionManager.getConnectionFromC3P0();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
int count = runner.update(conn, insertStr, params);
if (count > 0)
logger.debug("插入命令数据成功" + cmd);
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 updateCmdRecordTimeout(String cmdId) {
Connection conn = null;
String updateStr = "UPDATE PG_ACU_CMD SET TIMEOUT = '1' WHERE ID = ?";
Object[] param = new Object[1];
param[0] = cmdId;
try {
conn = ConnectionManager.getConnectionFromC3P0();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
int count = runner.update(conn, updateStr, param);
if (count > 0) {
logger.debug("更新命令已超时字段成功" + cmdId);
} else {
logger.error("更新命令已超时字段成功" + cmdId);
}
return count;
} catch (Exception ex) {
logger.error("更新命令已超时字段异常", ex);
} finally {
try {
DbUtils.commitAndClose(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
return 0;
}
@Override
public int deleteCmdRecord(String cmdId) {
Connection conn = null;
String updateStr = "DELETE FROM PG_ACU_CMD WHERE ID = ?";
Object[] param = new Object[1];
param[0] = cmdId;
try {
conn = ConnectionManager.getConnectionFromC3P0();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
int count = runner.update(conn, updateStr, param);
if (count > 0)
logger.debug("删除命令成功" + cmdId);
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 deleteTimeoutCmd() {
Connection conn = null;
String updateStr = "DELETE FROM PG_ACU_CMD WHERE TIMEOUT = '1'";
try {
conn = ConnectionManager.getConnectionFromC3P0();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
int count = runner.update(conn, updateStr);
if (count > 0) {
logger.debug("删除超时的命令成功");
}
return count;
} catch (Exception ex) {
logger.error("删除超时的命令异常", ex);
} finally {
try {
DbUtils.commitAndClose(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
return 0;
}
@Override
public int deleteLongTimeNoResponseCmd() {
Connection conn = null;
String updateStr = "DELETE FROM PG_ACU_CMD WHERE TM <= (SELECT SYSDATE - 1 FROM DUAL)";
try {
conn = ConnectionManager.getConnectionFromC3P0();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
int count = runner.update(conn, updateStr);
if (count > 0) {
logger.debug("删除长时间未响应的命令成功");
}
return count;
} catch (Exception ex) {
logger.error("删除长时间未响应的命令异常", ex);
} finally {
try {
DbUtils.commitAndClose(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
return 0;
}
}
