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?

Answer 1

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

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