Search code examples
javasqljfreechart

Multiple series Issue in line chart using JDBCCategoryDataset

I want to add multiple series to create a line chart using JFreeChart in a Java application. I retrieve values from a database and use these values generate a chart. But only one line appears on the chart at the time. Can anyone help in displaying to lines at the same time.? 

try{

    String query = "SELECT hardware, SUM(Quantity) FROM request_1v2 where date "
                            + "between '"+txt1+"' and '"+txt2+"'group by hardware";

    String querys = "SELECT hardware, SUM(Quantity) FROM request_1v2 where date "
            + "between '"+txt5+"' and '"+txt6+"'group by hardware";                     

    JDBCCategoryDataset dataset1 = new JDBCCategoryDataset(dbConnection.dbConnector());
    dataset1.executeQuery(query);

    JDBCCategoryDataset dataset2 = new JDBCCategoryDataset(dbConnection.dbConnector());
    dataset2.executeQuery(querys);

    System.out.println(dataset1);

    JFreeChart chart = ChartFactory.createLineChart("Hardware booked out between "+txt1+" and "+txt2+"", "Hardware", "Number", dataset1, PlotOrientation.VERTICAL, false, true, true);
    ChartPanel chartPanel = new ChartPanel(chart);
    panel.removeAll();
    panel.add(chartPanel, BorderLayout.CENTER);
    panel.validate();

    JFreeChart chart1 = ChartFactory.createLineChart("Hardware booked out between "+txt5+" and "+txt6+"", "Hardware", "Number", dataset2, PlotOrientation.VERTICAL, false, true, true);
    ChartPanel chartPanel1 = new ChartPanel(chart1);
    panel_1.removeAll();
    panel_1.add(chartPanel1, BorderLayout.CENTER);
    panel_1.validate();

    String sql = "select * from request_1v2 where Date between '"+txt1+"' and '"+txt2+"' order by date desc";
    PreparedStatement pst = connection.prepareStatement(sql);
    pst=connection.prepareStatement(sql);                   
    ResultSet rs = pst.executeQuery();
    table.setModel(DbUtils.resultSetToTableModel(rs));
}catch (Exception e){
    JOptionPane.showConfirmDialog(null, e);
}
Solution

  • JDBCCategoryDataset specifies that "The first column will be the category name and remaining columns values (each column represents a series)." To get two series, you need to select three attributes. It should be possible to use aliases in a single query to distinguish references to the same table, as suggested here, possibly something like this:

    SELECT hardware, SUM(t1.quantity) AS Series1, SUM(t2.quantity) AS Series2
FROM request_1v2 t1, request_1v2 t2
WHERE t1.date BETWEEN …

    Alternatively, you can combine the two datasets into new CategoryDataset and use it to create a single chart.

    DefaultCategoryDataset dcd = new DefaultCategoryDataset();
for (int i = 0; i < dataset1.getColumnCount(); i++) {
    dcd.addValue(dataset1.getValue(dataset1.getRowKey(0), dataset1.getColumnKey(i)),
        dataset1.getRowKey(0), dataset1.getColumnKey(i));
    dcd.addValue(dataset2.getValue(dataset2.getRowKey(0), dataset2.getColumnKey(i)),
        dataset2.getRowKey(0), dataset1.getColumnKey(i));
}
JFreeChart chart = ChartFactory.createLineChart(…, dcd, …);