@Override
public List<controlresult> getControlresultList(Map<String, Object> params) throws Exception
{
Logger log = Logger.getLogger(RemoteControlController.class);
List<controlresult> resultList = new ArrayList<>();
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try{
Class.forName("com.mysql.jdbc.Driver");
String dbNmae = "logall"+params.get("dbName");
String dbIp = ConfigUtil.pro.get("linkLogIp").toString();
String linkCloudSql = "jdbc:mysql://"+dbIp+"/"+dbNmae;
String linkCloudUsername =ConfigUtil.pro.get("linkLogIpUsername").toString();
String linkCloudPassword =ConfigUtil.pro.get("linkLogIpPassword").toString();
conn = DriverManager
.getConnection(
linkCloudSql,linkCloudUsername, linkCloudPassword);
stmt = conn.createStatement();
PreparedStatement ps=null;
StringBuffer buffer = new StringBuffer("SELECT `id`,`idc`,`flag`,`cmdStartTime`,`json`,`cmdEndTime`,`retValue`,`crmId`,CASE WHEN `fName` = '' OR `fName` IS NULL THEN `idsid` ELSE `fName` END `fName`,`insertTime`,TIMESTAMPDIFF(SECOND,cmdstarttime,cmdendtime ) AS duration FROM log_controlresult WHERE idc = ? and flag in ('BlueToothControl','carControlNew')");
if(params.get("ret") != null && StringUtils.isNoneEmpty(params.get("ret").toString())){
buffer.append("and retValue=?");
}
buffer.append(" order by crmid desc");
buffer.append(" LIMIT "+params.get("startIndex")+","+params.get("pageSize"));
ps=(PreparedStatement) conn.prepareStatement(buffer.toString());
ps.setString(1, params.get("idc").toString());
if(params.get("ret") != null && StringUtils.isNoneEmpty(params.get("ret").toString())){
ps.setString(2, params.get("ret").toString());
}
log.info("控制历史sql:"+buffer.toString());
rs = ps.executeQuery();
while(rs.next())
{
controlresult cresult = new controlresult();
cresult.setId(rs.getLong("id"));
cresult.setIdc(rs.getString("idc"));
cresult.setFlag(rs.getString("flag"));
cresult.setCmdStartTime(rs.getTimestamp("cmdStartTime"));
cresult.setJson(rs.getString("json"));
cresult.setCmdEndTime(rs.getTimestamp("cmdEndTime"));
cresult.setRetValue(rs.getString("retValue"));
cresult.setCrmId(rs.getLong("crmId"));
cresult.setfName(rs.getString("fName"));
cresult.setInsertTime(rs.getTimestamp("insertTime"));
cresult.setDuration(rs.getInt("duration"));
cresult.setCmdName(GetSendTypeNameByJson(cresult.getJson()));
resultList.add(cresult);
}
}
finally {
try {
if (!conn.isClosed()) {
conn.close();
}
if (!stmt.isClosed()) {
stmt.close();
}
if (!rs.isClosed()) {
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return resultList;
}