CODEWITHSUNDEEP
javamysqlsortingnetbeans
Kasun
Kasun

Reputation: 513

sort sql results and display all the results by a loop in a table in java

I want to sort sql results with a date that i get from jDateChooser and display all the returned results. This is what i have done so far. So far I have only managed to get 1 result even if i looped the sorted sql results. 

public void MonthlyTotalExpenses_Report(){
    String typeOfReport = (String) report_type.getSelectedItem();
    String reportDate = ((JTextField)report_date.getDateEditor().getUiComponent()).getText();

    db databaseCon = new db();
    Connection dbconnect = db.dbconnect();

    if(typeOfReport == "Total expenses"){
        String selectQuery = "select name,type,sum(amount),date_payed from expenses order by ? DESC";

        try{
            Connection con = databaseCon.dbconnect();
            ResultSet rs = null;
            PreparedStatement pst = null;
            pst = con.prepareStatement(selectQuery);
            pst.setString(1,reportDate);
            rs = pst.executeQuery();

            List<String> results = new ArrayList<String>();
            while(rs.next()) {
                results.add(rs.getString(1));
            }
            for(String result : results) {
                System.out.println(result);
            }


        }catch (Exception e){
            JOptionPane.showMessageDialog(null, e);
        }
    }

}

I want sort the results like this (for example -> 2017-01-01 to the date user select with jDateChooser) and print all the records available within that period. I just figured the sql query i wrote is wrong. It only prints the 1st value of the name column in the database. Help me to figure out how to sort according what i have mentioned above and print all the results that returned.

Upvotes: 2

Views: 114

Answers (1)

Tex
Tex

Reputation: 305

You might want to set the date in the where condition in your query.

select name,type,sum(amount),date_payed from expenses where date_payed = ? order by 4 DESC

Consider obtaining Date from String.

String reportDate = ((JTextField)report_date.getDateEditor().getUiComponent()).getText(); // "2017-01-01"
DateFormat format = new SimpleDateFormat("YYYY-MM-DD", Locale.ENGLISH);
Date date = format.parse(reportDate);

Then set your param with

pst.setDate(1,reportDate);

Upvotes: 1

Related Questions