Why do I get an empty ResultSet with this:
int numberOfEvents = 5;
long starttime = 1511655677248;
long endtime = 1511684405497;
String query = "SELECT NAME FROM EVENTS WHERE TIME >= ? AND TIME <= ? GROUP BY NAME HAVING COUNT(*) > ? ;";
try (Connection connection = DBManager.getConnection(); PreparedStatement statement = connection.prepareStatement(query);) {
statement.setLong(1, starttime);
statement.setLong(2, endtime);
statement.setInt(3, numberOfEvents);
List<String> ids = new ArrayList<>();
ResultSet rs = statement.executeQuery();
while (rs.next()) {
ids.add(rs.getString("NAME"));
}
return ids;
} catch (SQLException e) {
throw new MessageException(e);
}
But with this I get the expected result:
String query = "SELECT NAME FROM EVENTS WHERE TIME >= 1511655677248 AND TIME <= 1511684405497 GROUP BY NAME HAVING COUNT(*) > 5 ;";
try (Connection connection = DBManager.getConnection(); PreparedStatement statement = connection.prepareStatement(query);) {
List<String> ids = new ArrayList<>();
ResultSet rs = statement.executeQuery();
while (rs.next()) {
ids.add(rs.getString("NAME"));
}
return ids;
} catch (SQLException e) {
throw new MessageException(e);
}
And also this is giving me the same expected result:
long starttime = 1511655677248;
long endtime = 1511684405497;
int numberOfEvents = 5;
String query = "SELECT NAME FROM EVENTS WHERE TIME >= ? AND TIME <= ? GROUP BY NAME HAVING COUNT(*) > " + Integer.toString(numberOfEvents) + " ;";
try (Connection connection = DBManager.getConnection(); PreparedStatement statement = connection.prepareStatement(query);) {
statement.setLong(1, starttime);
statement.setLong(2, endtime);
List<String> ids = new ArrayList<>();
ResultSet rs = statement.executeQuery();
while (rs.next()) {
ids.add(rs.getString("NAME"));
}
return ids;
} catch (SQLException e) {
throw new MessageException(e);
}
So I think there is something wrong with the last parameter. Are there special requirements or restrictions for using parameters in a HAVING clause? I am using sqldroid 1.0.3.