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.

Are you sure you get the correct result from a query such as SELECT COUNT(*) FROM Signal WHERE SignalID = '12345', when you pull the COUNT(*) value itself? — josh.trow
– josh.trow, Commented Apr 26, 2011 at 17:35
@kunal, thanks for editing, but next time please take the time to clean up the code as well so it does not run off the screen....... — Johan
– Johan, Commented Apr 26, 2011 at 17:38
your question is tagged mysql but driver looks like sqlite org.sqlite.RS.findColumn. can you check your driver once. — kdabir
– kdabir, Commented Apr 26, 2011 at 17:39
Don't include a trailing ; in your queries when you query through JDBC — nos
– nos, Commented Apr 26, 2011 at 17:42
getInt(0) won't work @Srinivas -- the column numbering starts at 1. — Liv
– Liv, Commented Apr 26, 2011 at 17:54

Johan · Accepted Answer · 2011-04-26 20:43:20Z

8

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...

edited Apr 26, 2011 at 20:43

Johan

77.4k28 gold badges205 silver badges346 bronze badges

answered Apr 26, 2011 at 17:35

Liv

6,1541 gold badge24 silver badges31 bronze badges

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

8 Comments

Ruth Over a year ago

Ok, what about the second example I am providing. Let's say the query is returning multiple values not just one. How do I assign those values to a variable?

Ruth Over a year ago

Sorry, I found out that I entered something wrong in the second example.

Liv Over a year ago

you can still use column indexing rather that column names, regardless of how many columns your query returns, if that's what you mean.

Ruth Over a year ago

Ok, let me try some of this suggestions. Thanks

Ruth Over a year ago

The error went away. Seem like it is better to stick to the column indeces. Thanks everyone.

|

Johan · Accepted Answer · 2011-05-02 22:44:15Z

0

The second example has an error:

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

will always fail because you have no rowcount1 column in your query.

edited May 2, 2011 at 22:44

Johan

77.4k28 gold badges205 silver badges346 bronze badges

answered Apr 26, 2011 at 18:01

Sitnik

6206 silver badges8 bronze badges

3 Comments

Johan Over a year ago

-1 select count(*) as rowcount1 creates a column called 'rowcount1' in the query. Java doesn't seem to allow getting named columns from a query, but that's another matter.

Sitnik Over a year ago

I know.. I was referring to the second example. Query: SELECT Signal AS Sig1 FROM Observations WHERE SignalId = 1; And then he does a: rp1.getInt("rowcount1"); but there was no rowcount1 in the original query.

Johan Over a year ago

I see, edited your answer to unlock the vote and fixed the vote.

Collectives™ on Stack Overflow

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

2 Answers 2

8 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related