DriverManager 查询数据库拼接BUFFER
时间：2021-07-01 10:21:17
帮助过：25人阅读
;
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(FRAC_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);
}
}
DriverManager 查询数据库拼接BUFFER
标签：list pen rtti bip dstat tco mit size duration
