Counting rows sql table with jdbc

Question

I have trouble counting the rows of my sqltable. Using just the query in MS Studio works fine but when I try in Java I get an sql exception:

Connection con = null;
    Statement stat = null;
    int result = 0;
    int i;
    try {
        con = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;user=sa;password=123456; databaseName=RPA;");
        stat = con.createStatement();
        result = stat.executeUpdate("select count(*) FROM RPA_Users");

    }catch (SQLException e) {
        System.out.println("cannot connect!");
        e.printStackTrace();
    } finally {
        if (con != null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    return result;

Could someone explain me what I'm doing wrong? Thanks in advance

EDIT: Nevermind I already found it guys, thanks as usual for the help! My solution if someone is interested:

result = stat.executeQuery("select count(*) FROM /tablename/"); 
        result.next();
        rowCount = result.getInt(1);

If you are using Java 7 and above, you can use try-with-resources to make the code more readable: http://stackoverflow.com/a/8066594/812272 — user3001, Jun 02 '14 at 13:34
You should post your findings as your own answer and accept it. — VH-NZZ, Jun 02 '14 at 13:47

Reimeus · Answer 1 · 2014-06-02T13:33:37.090

2

Use executeQuery instead of executeUpdate for issuing database read queries

result = stat.executeQuery("select count(*) FROM RPA_Users");

Aside: Consider Using Prepared Statements

edited Jun 02 '14 at 13:33

answered Jun 02 '14 at 13:26

Reimeus

158,255
15
216
276

score 1 · Answer 2 · answered Jun 02 '14 at 13:29

1

You are using executeUpdate() for a query. This won't work.

You should switch to executeQuery() This method returns a ResultSet that you will have to evaluate.

answered Jun 02 '14 at 13:29

Dawnkeeper

2,844
1
25
41

1

How do I evaluate/convert the ResultSet so that I can return an integer? EDIT: no problem, I already found it! – user3488736 Jun 02 '14 at 13:40

score 0 · Answer 3 · answered Jun 02 '14 at 13:51

This is the full code:

{
    Connection con = null;
    Statement stat = null;
    ResultSet result = null;
    int rowCount = -1;
    try {
        con = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;user=sa;password=123456; databaseName=RPA;");
        stat = con.createStatement();
        result = stat.executeQuery("select count(*) FROM RPA_Users");   
        result.next();
        rowCount = result.getInt(1);        
        }catch (SQLException e) 
        {
            System.out.println("cannot connect!");
            e.printStackTrace();
        } 
    finally {
        if (con != null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
                }
        }
    }
    return rowCount;

}

Counting rows sql table with jdbc

3 Answers3