CODEWITHSUNDEEP
javasql-server
user8369850
user8369850

Reputation:

Retrieve total value of column by SQL query and show by Java fx

I am trying to fetch the total value from SQl query and it's giving me error that column name does not exist, where tbl_internet is table name and billFine is column whose datatype is double and total_fine is the label name.

try {
        String sql = "select sum(billFine) from tbl_internet ";
        pst = con.prepareStatement(sql);
        rs = pst.executeQuery();
        if(rs.next())
        {
            String sum= rs.getString("sum(billFine)");
            total_fine.setText(sum);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }

Error Description

com.microsoft.sqlserver.jdbc.SQLServerDriver > Column name not found

Upvotes: 0

Views: 800

Answers (2)

fabian
fabian

Reputation: 82461

Just access the value by the column index in the ResultSet (see also https://docs.oracle.com/en/java/javase/11/docs/api/java.sql/java/sql/ResultSet.html#getString(int)). I prefer using the indices for all queries but SELECT * queries, since this way you avoid having to hardcode the column names multiple times and also avoid passing the wrong value like in this scenario:

String sum = rs.getString(1);

You probably should access the sum as int, long or double though

double sum = rs.getDouble(1);
total_fine.setText(Double.toString(sum));

Upvotes: 1

Daniel B.
Daniel B.

Reputation: 2601

About the Error

The error is telling you that jdbc could not find the SQL column name. You are trying to get a column that is literally named "sum(billFine)", a name you do not have in your table.

Solution

Use the following query String sql = "select sum(billFine) as amount from tbl_internet ";

Now the resultSet that will return, will have a column named "amount" with the sum value stored in it.

Now instead of getString(), you need to use getDouble(), as sum() should return a double.

then to fetch the amount using rs.getDouble("amount");

Upvotes: 3

Related Questions