SQL Sum Query works in PHPmyAdmin, but not in Java resutset

Question

I have a table called "Transactions". It has 5 attributes: Date, Description, Amount, Clientname, Transaction_ID where Transaction_ID is the primary key. In the example Data, the Clientname, "John Smith" has two transactions where he spend 100.10 and 56.56 each. The SQL Query returns the expected result of 156.66 in PHPMyAdmin, but JDBC doesn't seem to recognize it in the ResultSet.

Here is my code:

 public void calculate_client_spending() throws SQLException {
    ConnectionClass Databaseloader = new ConnectionClass();
    Databaseloader.getConnection();
    String sql = "SELECT SUM(Amount) AS total FROM Transactions WHERE Clientname = 'John Smith';";

    ResultSet rs = Databaseloader.executeSQLRequestCommand(sql);
    //  rs.next();

    // System.out.println(sum);
    ResultSetMetaData rsMetaData = rs.getMetaData();
    int numberOfColumns = rsMetaData.getColumnCount();
    System.out.println(numberOfColumns);
    // get the column names; column indexes start from 1
    for (int i = 1; i < numberOfColumns + 1; i++) {
        String columnName = rsMetaData.getColumnName(i);

        // Get the name of the column's table name
        if ("total".equals(columnName)) {
            System.out.println("Bingo!");
            rs.last();
            int count = rs.getRow();
            rs.beforeFirst();
            System.out.println(count);
            while (rs.next()) {
                Results_trasactions.setText("");
                System.out.println("The total profits today are: " + rs.getString(1));
            }
        }

    }
}

This Query returns null in this example, but if I did rs.getDouble(1), it would return 0. Any idea what the issue may be here? I am able to get similar SUM Query's to work, such as a SUM for all clients and the WHERE clause seems to work for my primary key, but this specific Query JDBC doesn't seem to like it even though the SQL is valid in PHPmyadmin which makes me want to believe that it is a Java issue and not a SQL issues. Any help would be greatly appreciated.

score 0 · Answer 1 · answered Jan 06 '20 at 11:25

Since this is unfortunately way too long for a comment:

Not meaning to be mean, but maybe you shouldn't create a new account to answer your deleted question again (https://stackoverflow.com/questions/59570469) -> Google Cache - also, the Database Classes you're using give away your "real" account (How to retrieve the "total" variable representing a sum in a resultset) - so I'm voting to close this question yet again.

However, to be at least some hints:

ConnectionClass Databaseloader = new ConnectionClass();
Databaseloader.getConnection();

Databaseloader isn't any default JDBC class, but rather some (poorly) written static class, which looks like a weird wrapper to me. You can do it, but by any means you wouldn't do it statically. And by throwing the methods into Google, you find: almost nothing.

For mySQL you'd acquire a database connection like that:

Connection con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "sa", "sa")

and rather work with the connection object.

I'd recommend you to do the following:

Read how to connect your database in Java
Read how to create and execute a prepared statement
Read how to extract a result from a result set
Inform yourself about parameter binding (avoid SQL injections)
Profit!

All these topics are well covered on stackoverflow.

SQL Sum Query works in PHPmyAdmin, but not in Java resutset

1 Answers1