How return multiple lines in JFreechart dataset using MySQL and Java

I created a graph with a single line, with connection to the database as I show below.

I just used a dataset that returns the data of the query. I need to display more lines to create this thought in various datasets but don't know how can I implement it or even if it is possible.

I've seen examples of graph with multiple lines, but none helped me in what I wanted, which is to return values from the database.

String query="select date,tempfrom records where idSensor like 'Sensor1'";
JDBCCategoryDataset dataset = new JDBCCategoryDataset(CriaConexao.getConexao(),
        query);
JFreeChart chart = ChartFactory.createLineChart("Records", "date", "temp", 
        dataset, PlotOrientation.VERTICAL, false, true, true);
BarRenderer renderer = null;
CategoryPlot plot= null;
renderer=new BarRenderer();
ChartFrame frame = new ChartFrame("Records", chart);
frame.setVisible(true);
frame.setSize(400,650);

Solution

From the Javadoc of JDBCCategoryDataset:

The SQL query must return at least two columns. The first column will be the category name and remaining columns values (each column represents a series). Subsequent calls to executeQuery(String) will refresh the dataset.

So in your case you should add columns to your query:

String query = "SELECT date, value1, value2, value3, value4 FROM records WHERE " +
               "idSensor LIKE 'Sensor1'";

You can do subselects in SQL as well:

String query = "SELECT date, value1, (SELECT value2 FROM table2 WHERE " +
               "records.date = table2.date), value3, value4 FROM records WHERE " +
               "idSensor LIKE 'Sensor1'";

If that doesn't give you enough flexibility, go for the next advice from the Javadoc:

NOTE: Many people have found this class too restrictive in general use. For the greatest flexibility, please consider writing your own code to read data from a ResultSet and populate a DefaultCategoryDataset directly.

--- Update ---

You can auto-join (join the table against itself) to turn rows into columns, or you can write your own code to read data from a ResultSet. One possible way to auto-join:

SELECT r.date, r1.tempfrom, r2.tempfrom, r3.tempfrom
FROM records r, records r1, records r2, records r3
WHERE r.date = r1.date AND r.date = r2.date AND r1.date = r3.date AND
      r1.idSensor = 'Sensor1' AND r2.idSensor = 'Sensor2' AND r3.idSensor = 'Sensor3';

If it is possible that for one of the idSensors, you don't have a tempfrom on a date, then you need to do an OUTER JOIN. It depends a bit on your database how you write an outer join so I couldn't include that. But try this first and see if it helps you.