How can I draw gantt chart with date from database in java

Question

I have problem in taking from the database the startdate and the finaldate of task to draw the chart

public IntervalCategoryDataset getCategoryDataset() {
    conn = ConnectDB.ConnectDB();
    TaskSeriesCollection dataset = new TaskSeriesCollection();

    String sql = "SELECT `TITRE`, `DATE DEBUT Prévi`, `DATE FIN prévi` FROM `projet`;";
    try {
        pst = conn.prepareStatement(sql);
        rs = pst.executeQuery(sql);
        while (rs.next()) {
            String a = rs.getString("TITRE");
            Date StartDate = rs.getDate("DATE DEBUT Prévi");
            Date EndDate = rs.getDate("DATE FIN prévi");
            Names.add(a);
            Dates.add(StartDate);
            Dates.add(EndDate);
            ++count;
        }
    } catch (Exception e) {
        JOptionPane.showMessageDialog(null, e.getMessage());
    }
    int j = 0;
    int k = 1;

    TaskSeries series1 = new TaskSeries("Estimated Date");
    for (int i = 0; i < count; i++) {
        series1.add(new Task(Names.get(i),
                Date.from(LocalDate.of(Dates.get(j).getYear(), Dates.get(j).getMonth(), Dates.get(j).getDay())
                        .atStartOfDay()
                        .toInstant(ZoneOffset.UTC)),
                Date.from(LocalDate.of(Dates.get(k).getYear(), Dates.get(k).getMonth(), Dates.get(k).getDay())
                        .atStartOfDay()
                        .toInstant(ZoneOffset.UTC))));
    }

    dataset.add(series1);
    return dataset;
}

I expect to have for every tasks its chart but I have the same chart for all the tasks.

The values for `j` and `k` are always the same. BTW: Why are you looping twice? You could populate things directly in the `while(rs.next())` loop without having to use intermediate collections. — Mark Rotteveel, May 24 '19 at 17:25
first of all thank you for your answer.I add j=j+2; k+=2 to the loop but still the same problem and i found it difficult to do one loop . — Hiba Rezquellah, May 24 '19 at 17:40
also when i change the dates in the database i hade errors Exception in thread "AWT-EventQueue-0" java.time.DateTimeException: Invalid value for DayOfMonth (valid values 1 - 28/31): 0 — Hiba Rezquellah, May 24 '19 at 17:51
Are you aware of the existence of `Date.toLocalDate`, you really don't have to use the convoluted approach you currently have. If you have problems with the single loop approach, you'd be better off asking a question about that. — Mark Rotteveel, May 25 '19 at 06:07
thank you i solved the problem it was indeed the j and k in the loop — Hiba Rezquellah, May 25 '19 at 13:10

score 1 · Answer 1 · answered May 25 '19 at 06:16

You should apply a single loop, and in addition either use getObject(index, LocalDate.class) or - if your driver doesn't support the previous option - java.sql.Date.toLocalDate():

public IntervalCategoryDataset getCategoryDataset() {
    conn = ConnectDB.ConnectDB();
    TaskSeriesCollection dataset = new TaskSeriesCollection();

    TaskSeries series1 = new TaskSeries("Estimated Date");
    String sql = "SELECT `TITRE`, `DATE DEBUT Prévi`, `DATE FIN prévi` FROM `projet`;";
    try {
        pst = conn.prepareStatement(sql);
        rs = pst.executeQuery(sql);
        while (rs.next()) {
            String name = rs.getString("TITRE");
            LocalDate startDate = rs.getObject("DATE DEBUT Prévi", LocalDate.class);
            LocalDate endDate = rs.getObject("DATE FIN prévi", LocalDate.class);

            series1.add(new Task(name,
                Date.from(startDate.atStartOfDay().toInstant(ZoneOffset.UTC)),
                Date.from(endDate.atStartOfDay().toInstant(ZoneOffset.UTC)));
        }
    } catch (Exception e) {
        JOptionPane.showMessageDialog(null, e.getMessage());
    }

    dataset.add(series1);
    return dataset;
}

score 0 · Answer 2 · answered May 24 '19 at 17:49

You always get the same result because the j and k values are stacked in their default values and not incrementing along with the i counter in your for loop. Referring to the code am seeing, i advise you to get rid of the j and k counters, replace the j with i and the k with i+1. Your for loop should be like the following:

for (int i =0; i<count; i++) {
  series1.add(new Task(Names.get(i),  
Date.from(LocalDate.of(Dates.get(i).getYear(), Dates.get(i).getMonth(),Dates.get(i).getDay()).atStartOfDay().toInstant(ZoneOffset.UTC)),  
Date.from(LocalDate.of(Dates.get(i+1).getYear(),Dates.get(i+1).getMonth(),Dates.get(i+1).getDay()).atStartOfDay().toInstant(ZoneOffset.UTC))  
         ) 
         );
}

It rather looks like `j` should be replaced with `2 * i` and `k` with `2 * i + 1` — Mark Rotteveel, May 25 '19 at 06:06
Yes, thunks for rectifying my answer, so that will be something like this: Names.get(i), Dates.get(2i), Dates.get(2i+1). But there won't be no need for all of this, we can include all the work in the wile loop. — El-MAHMOUDI, May 25 '19 at 13:22

How can I draw gantt chart with date from database in java

2 Answers2

Linked