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");
    }

Guido · Accepted Answer · 2010-05-04 08:23:06Z

116

Use aliases:

SELECT COUNT(*) AS total FROM ..

and then

rs3.getInt("total")

edited May 4, 2010 at 8:23

Guido

47.9k28 gold badges127 silver badges177 bronze badges

answered May 4, 2010 at 7:54

Bozho

599k147 gold badges1.1k silver badges1.2k bronze badges

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

3 Comments

user2805346 Over a year ago

at least from my side, it needs first to call rs3.next() before calling rs3.getInt("total")

Suraj Jain Over a year ago

Thanks, I have something to ask from you, can we chat? Please

Md. Over a year ago

It definitely needs a call to rs3.next() (before getInt()). Otherwise, it will throw SQLException java.sql.SQLException: ResultSet.next was not called

Community · Accepted Answer · 2017-05-23 12:10:11Z

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, 2017 at 12:10

CommunityBot

11 silver badge

answered May 4, 2010 at 8:02

Eric Eijkelenboom

7,0212 gold badges28 silver badges29 bronze badges

3 Comments

Andrea Polci Over a year ago

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

Peder Rice Over a year ago

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.

JonnyRaa Over a year ago

@PederRicer couldn't agree more. Far too often people focus on micro-optimisations when clarity should (almost always) be the main goal of code

brabster · Accepted Answer · 2010-05-04 07:55:27Z

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 4, 2010 at 7:55

brabster

43.7k29 gold badges150 silver badges189 bronze badges

Comments

andrew · Accepted Answer · 2013-05-12 09:11:53Z

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='"[email protected]"'";
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, 2013 at 9:11

andrew

3,1514 gold badges27 silver badges30 bronze badges

1 Comment

Shubham Arya Over a year ago

I did the same thing but I am getting NullPointerException(), can you help?

BalajiShanmugam · Accepted Answer · 2018-09-14 07:01:46Z

2

        <%
        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);  
        }

        %>

answered Sep 14, 2018 at 7:01

BalajiShanmugam

212 bronze badges

1 Comment

sɐunıɔןɐqɐp Over a year ago

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

Cezar · Accepted Answer · 2013-08-20 10:53:31Z

1

Statement stmt3 = con.createStatement();

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

count = rs3.getInt("count");

edited Aug 20, 2013 at 10:53

Cezar

56.5k19 gold badges88 silver badges87 bronze badges

answered Aug 20, 2013 at 10:33

geek

191 bronze badge

1 Comment

Hatim Over a year ago

you need to call next() method to start the result set. e.g. while(recSet.next()){ num = recSet.getInt("..."); }

Dhruv Pal · Accepted Answer · 2018-07-25 19:09:05Z

1

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);
        }
    }
}

edited Jul 25, 2018 at 19:09

answered Jul 25, 2018 at 13:35

Dhruv Pal

9992 gold badges13 silver badges27 bronze badges

Collectives™ on Stack Overflow

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

7 Answers 7

3 Comments

3 Comments

Comments

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

3 Comments

3 Comments

Comments

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related