jdbc PreparedStatement bind query parameters with HAVING clause

Viewed 298

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.

0 Answers
Related