How add more than one line at a freechart Java?

Question

I did a Java application and need to add a graphics to that. I could do this, but I can only add a product (line) to each graph.

I wish I could add more.

Here is my code

String query="select date,price from produtcs where idProduct like 'Prod1'";
            JDBCCategoryDataset dataset = new JDBCCategoryDataset (CriaConexao.getConexao(),query);
            JFreeChart chart = ChartFactory.createLineChart(Record of Price", "Date", "Price", dataset, PlotOrientation.VERTICAL, false, true, true);
            BarRenderer renderer = null;
            CategoryPlot plot= null;
            renderer=new BarRenderer();
            ChartFrame frame = new ChartFrame("Record of Price", chart);
            frame.setVisible(true);
            frame.setSize(400,650);

Chart----

The record that show in chart---------

idProduct             Date  Price
Pro01           2014-05-29  19.1
Pro01           2014-05-29  18.8
Pro01           2014-05-29  18.7
Pro01           2014-05-29  18.9
Pro01           2014-05-29  18.7
Pro01           2014-05-29  18.5

The record that I want show in chart---------

idProduct             Date  Price
Pro01           2014-05-29  19.1
Pro01           2014-05-29  18.8
Pro02           2014-05-29  18.7
Pro02           2014-05-29  18.9
Pro03           2014-05-29  18.7
Pro03           2014-05-29  18.5

I try this query, but only show one line

String query="select date,price from produtcs where idProduct like 'Prod%'";

---

EDIT

I edit a new query:

SELECT p1.`Date`
     , p1.`Price` as `Price of Prod01`
     , p2.`Price` as `Price of Prod02`
     , concat(p1.idProduct, ' / ', p2.idProduct) idProduct
FROM   (SELECT idProduct, Date, Price
        FROM products
        WHERE idProduct LIKE 'Pro01') p1
       LEFT  JOIN (SELECT idProduct, Date, Price
                   FROM products
                   WHERE idProduct LIKE 'Pro02') p2
       ON p1.Date = p2.Date

And the result is:

 Date       Price of Prod01     Price of Prod02      Products
2014-05-29    23.8                  23.0                 BrgTH001 / BrgTH002
2014-05-29    23.8                  23.1               BrgTH001 / BrgTH002
2014-05-29    23.8                  22.6               BrgTH001 / BrgTH002
2014-05-29    23.8                  22.5               BrgTH001 / BrgTH002
2014-05-29    23.8                  22.8               BrgTH001 / BrgTH002
2014-05-29    23.8                  23.1               BrgTH001 / BrgTH002

But the result is one line again :S

Answer 1

As from the Javadoc on the JDBCCategoryDataset:

The first column will be the category name and remaining columns values (each column represents a series).

Hence if you put the price of your second product in the third column, that should work fine.

If, however, you cannot do that because of schema or data restrictions or what not, you have several other options available to you:

1. Use a DefaultCategoryDataset as the closest thing to JDBCCategoryDataset and populate it manually.
2. You seem to be creating a time series graph (values plotted against dates). As such, it is only reasonable to try a TimeSeriesCollection as your dataset. It is a collection of Time Series (surprise!), and each Time Series is a collection of (date, value) points that will be plotted on your graph as a line.
3. A more general approach is to use Abstract Dataset and provide a renderer, but that requires quite a bit of background knowledge and is reserved for when you need something very non trivial. (But is useful to learn JFreeChart)

I would use option 2. A snippet of it may look like this:

TimeSeriesCollection timeSeries = new TimeSeriesCollection();
TimeSeries product1 = new TimeSeries("product1");
new JDBCTemplate("query", params, new ResultSetExtractor<Void>() {  
    public Void extractData(ResultSet rs) throws SQLException {
        product1.addOrUpdate(new FixedMillisecond(rs.getLong(1)), rs.getDouble(2));
        return null;
    }
}
<other products to follow>