CODEWITHSUNDEEP
javamysqlsqlswingnetbeans
Jackson Etherchain Stake
Jackson Etherchain Stake

Reputation: 87

How to display sum of columns from sql database into Java application?

I have a database which stores records of my project, in one of the columns which is called 'quantity', I want to be able to retrieve this 'quantity' amount and display in my java application. If you see my code below you can see that I am trying to print out that amount from my database but it is not working. When I run my application, it is always the catch that is running and not the try. I am unsure if maybe I did not do the SQL code correctly but that's just my guess, if you know of how to solve this problem do let me know thank you.

private void initComponents() {
private String sum;


try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con1 = DriverManager.getConnection("jdbc:mysql://localhost/restock", "root", "password");
            String template = "SELECT SUM(quantity) as sumquantity FROM buysupply WHERE itemtype IN ('Disposable mask', 'Clothed mask', 'N95 mask')";
            PreparedStatement pst = con1.prepareStatement(template);
            ResultSet rs = pst.executeQuery();
            sum = rs.getString("sumquantity");
            System.out.print("############################" + sum);
                  
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(stock.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(stock.class.getName()).log(Level.SEVERE, null, ex);
        }




}

For your info, the error is this:

Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
Aug 06, 2020 1:22:03 AM pleasework.stock initComponents
SEVERE: null
java.sql.SQLException: Before start of result set
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
    at com.mysql.cj.jdbc.result.ResultSetImpl.checkRowPos(ResultSetImpl.java:492)
    at com.mysql.cj.jdbc.result.ResultSetImpl.getString(ResultSetImpl.java:845)
    at com.mysql.cj.jdbc.result.ResultSetImpl.getString(ResultSetImpl.java:863)
    at pleasework.stock.initComponents(stock.java:92)
    at pleasework.stock.<init>(stock.java:30)
    at pleasework.stock$16.run(stock.java:823)
    at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:311)
    at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:758)
    at java.awt.EventQueue.access$500(EventQueue.java:97)
    at java.awt.EventQueue$3.run(EventQueue.java:709)
    at java.awt.EventQueue$3.run(EventQueue.java:703)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:74)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:728)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:205)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93)
    at java.awt.EventDispatchThread.run(EventDispatchThread.java:82)

Upvotes: 0

Views: 751

Answers (1)

TomStroemer
TomStroemer

Reputation: 1540

You have to call rs.next() to load the first row into your resultset before you can use rs.getString('...').

ResultSet rs = pst.executeQuery();
rs.next();
sum = rs.getString("sumquantity");
System.out.print("############################" + sum);

You should also use rs.next() to check if you received a result. So for example:

ResultSet rs = pst.executeQuery();
if (rs.next()) {
    sum = rs.getString("sumquantity");
    System.out.print("############################" + sum);
} else {
    System.out.print("Query didn't return any results");
}

Another thing that attracts attention: You use getString() to get a numeric value. You probably want to use rs.getInt() or rs.getDouble() instead.

Upvotes: 1

Related Questions