public static void main(String[] args) {
Connection conn = null;
Statement stat = null;
String startTime="2023-01-01 00:00:00";
String endTime="2024-01-12 00:00:00";
List<CpRemoteCallLogDto> callLogDtoList = new ArrayList<>();
String url = "XXXXXXXX";
String username = "parking_v3_test";
String password = "d1csmT1HldSErVfe";
List<String> parkIdList = Arrays.asList("23070126180539683000031000010550","23090121094758870000164280060078");
try {
System.out.println("开始连接数据库");
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, username, password);
System.out.println("集管数据库连接成功:{}"+conn);
String sql = "select id, park_id, call_status, start_time,holdon_time,finish_time from cp_remote_call_log where start_time between ? and ? " ;
if (parkIdList != null && parkIdList.size() > 0){
sql = sql + " and park_id in "+generateInClause(parkIdList);
}
PreparedStatement preparedStatement = conn.prepareStatement(sql);
preparedStatement.setString(1, startTime);
preparedStatement.setString(2, endTime);
// if (parkIdList != null && parkIdList.size() > 0){
// preparedStatement.setString(3, generateInClause(parkIdList));
// }
ResultSet rs = preparedStatement.executeQuery();
System.out.println("集管查询的结果:{}"+rs.next());
while (rs.next()) {
CpRemoteCallLogDto callLogDto = new CpRemoteCallLogDto();
callLogDto.setId(rs.getString("id"));
callLogDto.setParkId(rs.getString("park_id"));
callLogDto.setCallStatus(rs.getString("call_status"));
callLogDto.setCallStartTime(rs.getString("start_time"));
callLogDtoList.add(callLogDto);
}
} catch (Exception e) {
e.printStackTrace();
System.out.println(String.valueOf(e));
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
System.out.println(String.valueOf(e));
}
}
if (stat != null) {
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
System.out.println(String.valueOf(e));
}
}
}
System.out.println("集管数据列表:{}"+callLogDtoList);
}
private static String generateInClause(List<String> values) {
StringBuilder inClause = new StringBuilder("(");
for (int i = 0; i < values.size(); i++) {
inClause.append(values.get(i));
if (i < values.size() - 1) {
inClause.append(",");
}
}
inClause.append(")");
return inClause.toString();
}