Selecting a count from my database using Java and Netbeans

Question

I have the following code:

try {
    String sql = "SELECT COUNT(*) AS \"Jumlah\" FROM dokter";
    ResultSet rs = connection.st.executeQuery(sql);

    if(rs.next()){ 
        abc = rs.getString("Jumlah").toString();    
    }
} catch (Exception e) {
    System.out.println("\n Message: " + e.getMessage());
}

Why can't my ResultSet execute the given SQL?

Can you please post some complete, compilable code. And the exact error message you get. — Gyro Gearless, Jun 07 '15 at 20:58

score 2 · Answer 1 · answered Jun 07 '15 at 20:57

Lose the alias, it's just an unnecessary complication. Just reference the ResultSet by the column's index:

try {
    String sql = "SELECT COUNT(*) FROM dokter";
    ResultSet rs = connection.st.executeQuery(sql);

    if(rs.next()) { 
        abc = rs.getInt(1); // or getString(1) if you need it as a String    
    }
} catch (Exception e) {
    System.out.println("\n Message: " + e.getMessage());
}

score 2 · Answer 2 · answered Jun 07 '15 at 20:58

2

I suggest you use a PreparedStatement and a try-with-resources to close it (and your ResultSet). A count is not a String, and if you have a Connection connection then you might do something like

int count = 0;
try {
    String sql = "SELECT COUNT(*) FROM dokter";
    try (PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery()) {
        if (rs.next()) {
            count = rs.getInt(1);
        }
    }
} catch (Exception e) {
    e.printStackTrace();
} finally {
    if (connection != null) {
        try {
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

answered Jun 07 '15 at 20:58

Elliott Frisch

198,278
20
158
249

A simple [`Statement`](http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html) would be enough here, because there are no bind variables involved and the query is only executed once. – Mick Mnemonic Jun 07 '15 at 22:17
@MickMnemonic But if the query is executed more than once, then the `PreparedStatement` *might* be in the query cache (and a `Statement` won't be). – Elliott Frisch Jun 07 '15 at 23:28
Any query you execute will be cached by the DB (MySQL), whether it's a prepared or a "plain" statement. The main benefit from "caching" is on the DB side: eliminating the need to do a "hard parse" and an execution plan recalculation. `PreparedStatement`s are useful when you need to pass in input, but in this case it's not required. The difference may have been more significant in the "old days" as [this answer](http://stackoverflow.com/a/17999666/905488) suggests. – Mick Mnemonic Jun 07 '15 at 23:43
1

On the other hand, [this thread](http://stackoverflow.com/questions/21716839/prepared-statement-cache-with-mysql-jdbc) suggests that you _should_ always use `PreparedStatement`s to enable caching in MySQL. So, it probably won't hurt. – Mick Mnemonic Jun 07 '15 at 23:55

Selecting a count from my database using Java and Netbeans

2 Answers2