JDBC ResultSet Aggregate Functions

Question

Edit: oops. These JDBC statements work; I had forgotten to commit in SQL Plus. Thanks Paul.

When I query the database with SQL Plus:

select count(tid) from retweets where tid = 35          => 2
select count(tid) from tweets where replyto = 35        => 1

I've tried several methods to pull these aggregate counts from the database through JDBC, but in all cases they returned 0.

Examples:

Statement stmt = m_con.createStatement();
ResultSet retweets = stmt.executeQuery("select count(tid) from retweets where tid = 35");

if (retweets.next()) {  System.out.println("# of Retweets: " + retweets.getInt(1));}
 ResultSet replies = stmt.executeQuery("select count(tid) Replies from tweets where replyto = "+tid);


if (replies.next()) {System.out.println("# of Replies : " + replies.getInt("Replies"));}

Both times, 0 was printed. Why did this happen and how can I fix it? Thanks.

Answer 1

Something like this:

public class TweetDao {
    private static final String SELECT_TWEETS = "SELECT COUNT(tid) as TC FROM TWEETS WHERE replyTo =  ? ";
    // inject this - setter or constructor
    private Connection connection;

    public int getTweetCount(int tid) throws SQLException {
        int tweetCount = -1;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = this.connection.prepareStatement(SELECT_TWEETS);
            ps.setInt(1, tid);
            rs = ps.executeQuery();
            while (rs.hasNext()) {
                tweetCount = rs.getInt("TC");
            }
        } finally {
            DatabaseUtils.close(rs);
            DatabaseUtils.close(ps);
        }
        return tweetCount;
    }
}

Answer 2

Try PreparedStatement:

String sql = "select count(tid) from retweets where tid = 35";
PreparedStatement stmt = m_con.prepareStatement(sql);

Answer 3

**try {
            ps = this.connection.prepareStatement(SELECT_TWEETS);
            ps.setInt(1, tid);
            rs = ps.executeQuery();
            while (rs.hasNext()) {
                tweetCount = rs.getInt("TC");
            }
        } finally {
            DatabaseUtils.close(rs);`enter code here`**

I think here no need of using while loop because we are getting only single result. Please let me know, if I am wrong.