SQL query using MySQL #JAVA

Question

This is my database below I am coding with java and my workspace is eclipse

My question goes like this: I am trying to sum the column monthlypaid for each month for example in january except appartmentnum :1 every one paid where the value 0 mean (didn't paid that month)

So in january 530+530+700+660+490+200

and on may 570+530+700+200

I should print it like this 1, january sum 2, febsum and I don't have a clue how to do it

I tried that but I think I am far away from solution

public static String select_Month_payment(String username) {
    int i=1;
    String answer="",ans="";
     try {
         PreparedStatement statement = connect.prepareStatement("select january,february,march,april,may,june,july,august,september,october,november,december from tenant where username= "+"'"+username+"'");
         ResultSet result = statement.executeQuery();

     while(result.next()) 
     {
         for (i=1;i<13;i++)
         {
            answer=result.getString(i);
           if(!answer.matches("0") )
               ans+= answer + "  " ;
         }


      }
     } catch (SQLException var2) {
         var2.printStackTrace();
     }
     return ans;

}

Answer 1

You might want to try:

Double sum = 0.0;
PreparedStatement pstmt = conn.prepareStatement("SELECT sum(monthlyPaid) as ans FROM tenant WHERE username = ?");
pstmt.setString(1, YOUR_USER_NAME);
res = pstmt.executeQuery();

sum = res.getString(1);

There's a built-in function in SQL that returns the sum of the column. You might not want to re-invent the wheel when there's one already..

PLUS:

Concatenating the variable at your query is a bad practice. Your application would be vulnerable to sql injections. Use ? instead, it is called named perimeters like this one: pstmt.setDouble(n, YOUR_VARIABLE);.

For more info, please see: https://www.javaworld.com/article/2077706/core-java/named-parameters-for-preparedstatement.html