CODEWITHSUNDEEP
javamysqlsqloop
Nick L
Nick L

Reputation: 167

Try-catch ignoring the assignment of a variable when executing an SQL query

This piece of code uses an SQL query to return how many entries there are in a certain table.

public int countAmountOfEntries() {
    int amount;
    try (Connection conn = DriverManager.getConnection(Connection.JDBC_URL); 
            PreparedStatement query = conn.prepareStatement("SELECT COUNT(*) FROM Table")) {
        try (ResultSet rs = query.executeQuery();) {
            if (rs.next()) {
                amount = rs.getInt("COUNT(*)");
            }
        }
    } catch (SQLException ex) {
        throw new RuntimeException(ex);
    }
    return amount;
}

This should return any int other than 0. Initialising the variable to 0 will result in a NullPointerException being thrown as I'm using the return value of this to set the length of an array. Using the same code in another class returns the int it should return. I've tried using an alias for the COUNT(*) but to no avail.

Running the query directly into MySQL returns the int as well. I've tried removing the nested try (it was pretty much obsolete since I know it won't throw an exception if no one messes with my DB).

Upvotes: 0

Views: 213

Answers (2)

casey
casey

Reputation: 28

This looks really weird:

amount = rs.getInt("COUNT(*)");

Try this instead

amount = rs.getInt(1);

Upvotes: 0

Siloft
Siloft

Reputation: 126

Did you register the JDBC driver before using it?

Class.forName("com.mysql.jdbc.Driver");

Is it required to provide an username/password upon connecting?

DriverManager.getConnection(url, user, pass);

Did you create a Connection class yourself which overwrites the Connection class returned upon opening the connection. The reason I ask this is because you retrieve the URL to connect to using Connection.JDBC_URL which is (as far as I know) not in the Connection class.

Is there already a connection opened and your database only allows 1 open connection?

Note: do not forget to close the resultset, statement, and connection before returning:

rs.close();
query.close();
conn.close();

Besides that, restructure your function because a try without catch does not help at all.

Upvotes: 1

Related Questions