Assigning SELECT COUNT(*) Query Result to a Java Variable

Question

I have been having problems assigning the result of a SELECT COUNT(*) query to a Java variable. I am assigning the result of the query to a ResultSet. Then, I am trying to retrieve the value of the count and assigning it to a variable. I am getting an error when trying to do this.

Here is my code:

ResultSet rc1 = null;
int rowCount1;
Statement stat = conn.createStatement();

rc1 = stat.executeQuery("SELECT COUNT(*) AS rowcount1 
   FROM Signal WHERE SignalId = 1;");

if (rc1.next())
        rowCount1 = rc1.getInt("rowcount1");

Then I get the following error:

java.sql.SQLException: no such column: 'rowcount1'
at org.sqlite.RS.findColumn(RS.java:116)
at org.sqlite.RS.getInt(RS.java:219)

Apparently, the problem is when trying to assign what goes after AS to a variable. I can't find a lot of information on queries containing AS. I get the same error with queries where I am not counting. For example if I have the following code:

ResultSet rp1 = null;
int rowCount1 = 0;
Statement stat = conn.createStatement();

rp1 = stat.executeQuery("SELECT Signal AS Sig1 
  FROM Observations WHERE SignalId = 1;");

if (rp1.next())
       rowCount1 = rp1.getInt("rowcount1");

I get the same error with the previous code (no such column: rowCount1). What I am doing wrong? I am making sure the table I am reading contains the correct values so the query has to be true.

Answer 1

Simply use rp1.getInt(1) -- this returns the first column from the resultset as an int -- which is what you want.

If you have more values use rp1.getInt(2) to get the second value etc...