Returning SQL Count function to Java

Question

I'm having problems getting a SQL count query to return the proper value. I know there are already several questions about this topic already posted, but I'm hoping to get a second pair of eyes look over my code to see if they notice anything I've missed. I've found several examples of similar methods that seem to work, and as far as I can see, mine is the same. Here's my method that calls the SQL query:

public int countAddresses(int id) {
    ResultSet rs = null;
    Statement st = null;
    int count = -1;
    try{
        st = conn.createStatement(); 
        String countAddress = "SELECT COUNT(*) AS AddressCount from address WHERE contact_id =" + id;
        rs = st.executeQuery(countAddress); 
        count = rs.getInt("AddressCount"); //throwing exception
    }
    catch(SQLException e) {
        e.printStackTrace();
    }
    return count;
}

No matter what tweaks I make, it always returns -1 because the line that reassigns count throws a SQLException. It's worth noting that all my other methods that contact the database are connecting and returning the proper values, and that my java code calls this method after it has successfully called and returned some of these other methods. If anyone can find an error in my code or needs additional info, please let me know. Thanks.

Additional specs:

Spring MVC
XAMPP with a MySQL db

Include the stacktrace of the exception (and the message in it) in your question. Otherwise we can only guess what your error is. (but here is one guess: case sensitivity, either add double quotes to the alias in your query, or retrieve column index 0 or column named "addresscount") — dsh
– dsh, Commented Aug 28, 2015 at 19:32

Sergey Kalinichenko · Accepted Answer · 2015-08-28 19:33:20Z

1

The reason you get an exception is that you are calling getInt without calling next():

rs = st.executeQuery(countAddress);
if (rs.next()) {
    count = rs.getInt("AddressCount");
}

The reason it is illegal to call getInt before calling next is that executeQuery() returns you a reader that is not positioned on the first (in this case, the only) result.

answered Aug 28, 2015 at 19:33

Sergey Kalinichenko

729k85 gold badges1.2k silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

uoyilmaz · Accepted Answer · 2015-08-28 19:32:00Z

0

Try using

if( rs.next())
    count = rs.getInt(1);

answered Aug 28, 2015 at 19:32

uoyilmaz

3,11516 silver badges25 bronze badges

Collectives™ on Stack Overflow

Returning SQL Count function to Java

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related