How do you access the value of an SQL count () query in a Java program

Question

I want to get to the value I am finding using the COUNT command of SQL. Normally I enter the column name I want to access into the getInt() getString() method, what do I do in this case when there is no specific column name.

I have used 'AS' in the same manner as is used to alias a table, I am not sure if this is going to work, I would think not.

Statement stmt3 = con.createStatement();
ResultSet rs3 = stmt3.executeQuery("SELECT COUNT(*) FROM "+lastTempTable+") AS count");
    while(rs3.next()){
    count = rs3.getInt("count");
    }

score 116 · Accepted Answer · edited May 04 '10 at 08:23

116

Use aliases:

SELECT COUNT(*) AS total FROM ..

and then

rs3.getInt("total")

edited May 04 '10 at 08:23

Guido

46,642
28
120
174

answered May 04 '10 at 07:54

Bozho

588,226
146
1,060
1,140

13

at least from my side, it needs first to call rs3.next() before calling rs3.getInt("total") – user2805346 Feb 09 '17 at 22:31
Thanks, I have something to ask from you, can we chat? Please – Suraj Jain Apr 18 '17 at 08:47
2

It definitely needs a call to rs3.next() (before getInt()). Otherwise, it will throw SQLException java.sql.SQLException: ResultSet.next was not called – Md. Mar 02 '21 at 12:50

score 47 · Answer 2 · edited May 23 '17 at 12:10

47

The answers provided by Bohzo and Brabster will obviously work, but you could also just use:

rs3.getInt(1);

to get the value in the first, and in your case, only column.

edited May 23 '17 at 12:10

Community

1
1

answered May 04 '10 at 08:02

Eric Eijkelenboom

6,943
2
25
29

4

From Javadoc: "Values can be retrieved using either the index number of the column or the name of the column. In general, using the column index will be more efficient." – Andrea Polci May 04 '10 at 09:07
2

This level of performance tuning really isn't important for most applications. Using column indexes exposes you to errors from columns moving around in your SQL query, which can be difficult to trace down. In this particular example (merely grabbing a record count), this isn't a problem, but it's something to keep in mind for application sustainability. – Peder Rice Sep 27 '11 at 19:28
@PederRicer couldn't agree more. Far too often people focus on micro-optimisations when clarity should (almost always) be the main goal of code – JonnyRaa May 09 '14 at 15:38

score 4 · Answer 3 · answered May 04 '10 at 07:55

4

I would expect this query to work with your program:

"SELECT COUNT(*) AS count FROM "+lastTempTable+")"

(You need to alias the column, not the table)

answered May 04 '10 at 07:55

brabster

42,504
27
146
186

score 2 · Answer 4 · answered May 12 '13 at 09:11

2

I have done it this way (example):

String query="SELECT count(t1.id) from t1, t2 where t1.id=t2.id and t2.email='"r@r.com"'";
int count=0;
try {
    ResultSet rs = DatabaseService.statementDataBase().executeQuery(query);
    while(rs.next())
        count=rs.getInt(1);
} catch (SQLException e) {
    e.printStackTrace();
} finally {
    //...
}

answered May 12 '13 at 09:11

andrew

3,083
4
24
29

I did the same thing but I am getting NullPointerException(), can you help? – Shubham Arya May 25 '18 at 07:09

score 2 · Answer 5 · answered Sep 14 '18 at 07:01

        <%
        try{
            Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/bala","bala","bala");
            if(con == null) System.out.print("not connected");
            Statement st = con.createStatement();
            String myStatement = "select count(*) as total from locations";
            ResultSet rs = st.executeQuery(myStatement);
            int num = 0;
            while(rs.next()){
                num = (rs.getInt(1));
            }

        }
        catch(Exception e){
            System.out.println(e);  
        }

        %>

Welcome to Stack Overflow! Please don't answer just with source code. Try to provide a nice description about how your solution works. See: [How do I write a good answer?](https://stackoverflow.com/help/how-to-answer). Thanks — sɐunıɔןɐqɐp, Sep 14 '18 at 07:25

score 1 · Answer 6 · edited Aug 20 '13 at 10:53

1

Statement stmt3 = con.createStatement();

ResultSet rs3 = stmt3.executeQuery("SELECT COUNT(*) AS count FROM "+lastTempTable+" ;");

count = rs3.getInt("count");

edited Aug 20 '13 at 10:53

Cezar

55,636
19
86
87

answered Aug 20 '13 at 10:33

geek

19
1

3

you need to call next() method to start the result set. e.g. while(recSet.next()){ num = recSet.getInt("..."); } – Hatim Mar 11 '17 at 23:28

Dhruv Pal · Answer 7 · 2018-07-25T19:09:05.653

It's similar to above but you can try like

public Integer count(String tableName) throws CrateException {
        String query = String.format("Select count(*) as size from %s", tableName);
        try (Statement s = connection.createStatement()) {
            try (ResultSet resultSet = queryExecutor.executeQuery(s, query)) {
                Preconditions.checkArgument(resultSet.next(), "Result set is empty");
                return resultSet.getInt("size");
            }
        } catch (SQLException e) {
            throw new CrateException(e);
        }
    }
}

How do you access the value of an SQL count () query in a Java program

7 Answers7

Linked