Problem when Using Jfreechart with Mysql

[marouene_]

Hi Everybody how are you ?

Hope you are doing right ...

Great, i'm working on a Java Servlet wich extract vlaues saved on a Mysql database. Actually all is working, when i use a simple sql request, i can draw the chart using Jfreechart...

But the goal of my application is to draw a chart of the previous 24h. This is the query what i tested :

Code: Select all

SELECT `date`, heure, valeur
FROM table1
WHERE STR_TO_DATE(CONCAT(`date`, ' ', heure), '%Y-%m-%d %H:%i:%s') BETWEEN DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 24 HOUR) AND CURRENT_TIMESTAMP

and this is the result ( it shows the variation of the previous 24h)

Code: Select all

 
+------------+----------+---------+
| date           | heure    | valeur       |
+------------+----------+---------+
| 2012-04-16 | 16:09:50 | 4619.46 |
| 2012-04-16 | 17:10:05 | 3497.93 |
| 2012-04-16 | 18:09:13 |  5862.1 |
| 2012-04-16 | 19:09:21 | 6006.03 |
| 2012-04-16 | 20:09:34 | 5716.41 |
| 2012-04-16 | 21:09:21 |  5537.5 |
| 2012-04-16 | 22:09:53 | 4083.13 |
| 2012-04-16 | 23:09:49 | 4212.54 |
| 2012-04-17 | 00:09:40 | 4387.92 |
| 2012-04-17 | 01:09:54 | 3678.67 |
| 2012-04-17 | 02:09:17 | 5696.32 |
| 2012-04-17 | 03:09:52 | 4484.08 |
| 2012-04-17 | 04:09:37 | 4486.34 |
| 2012-04-17 | 05:10:31 | 4373.79 |
| 2012-04-17 | 06:09:49 | 4461.08 |
| 2012-04-17 | 07:09:08 | 5881.93 |
| 2012-04-17 | 08:08:57 | 5532.78 |
| 2012-04-17 | 09:09:47 | 4213.67 |
| 2012-04-17 | 10:09:14 | 5644.68 |
| 2012-04-17 | 11:09:15 | 5217.59 |
| 2012-04-17 | 12:10:04 |    3678 |
| 2012-04-17 | 13:10:22 | 4086.52 |
| 2012-04-17 | 16:01:29 | 940.421 |

The problem is that my servlet draw the variation between [00h, 00h] and no between [16h, 16h] so i juste see the variation on 2012-04-16.

Please find below the servlet code

Code: Select all

 
package metier;
import java.awt.Color;
import java.awt.Graphics;
import java.awt.image.BufferedImage;
import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Enumeration;
 
import javax.imageio.ImageIO;
import javax.servlet.ServletConfig;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
import org.jfree.chart.ChartFactory;
import org.jfree.chart.ChartPanel;
import org.jfree.chart.ChartUtilities;
import org.jfree.chart.JFreeChart;
import org.jfree.chart.axis.DateAxis;
import org.jfree.chart.axis.NumberAxis;
import org.jfree.chart.plot.XYPlot;
import org.jfree.chart.renderer.xy.XYItemRenderer;
import org.jfree.chart.renderer.xy.XYLineAndShapeRenderer;
import org.jfree.chart.title.TextTitle;
import org.jfree.data.time.Hour;
import org.jfree.data.time.Minute;
import org.jfree.data.time.Second;
import org.jfree.data.time.TimeSeries;
import org.jfree.data.time.TimeSeriesCollection;
import org.jfree.ui.RectangleInsets;
 
import com.sun.corba.se.impl.orbutil.graph.Graph;
 
import marouene.TimeSeriesDemoTest;
import marouene.compare;
 
 
public class httpcompare extends HttpServlet {
	protected void processRequest(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException, SQLException {
	 	connexionBD con = new connexionBD();
		response.setContentType("image/png");
 
	        ResultSet rs1 = con.execute("select heure,valeur from table1 WHERE STR_TO_DATE(CONCAT(`date`, ' ', heure), '%Y-%m-%d %H:%i:%s') BETWEEN DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 24 HOUR) AND CURRENT_TIMESTAMP;");
	        final TimeSeries series1 = new TimeSeries("valeur", Hour.class);
	        while(rs1.next()){
 
	        	java.sql.Time v1;
 
	        		v1 = rs1.getTime("heure");
 
	         	double v2=rs1.getFloat("valeur");
 
	         	series1.addOrUpdate( new Minute(v1), v2);
 
	         }   
 
 
 
 
		DateAxis domain = new DateAxis("Time");
   	 	NumberAxis range = new NumberAxis("Débit en Kb/s");
        final TimeSeriesCollection dataset = new TimeSeriesCollection(series1);
 
        dataset.setDomainIsPointsInTime(true);
        XYItemRenderer renderer = new XYLineAndShapeRenderer(true, false);
        XYPlot plot = new XYPlot(dataset, domain, range, renderer);
        plot.setBackgroundPaint(Color.lightGray);
        plot.setDomainGridlinePaint(Color.white);
        plot.setRangeGridlinePaint(Color.white);
        plot.setAxisOffset(new RectangleInsets(5.0, 5.0, 5.0, 5.0));
        range.setAutoRange(true);
        range.setLowerMargin(0.0);
        range.setUpperMargin(0.0);
        range.setTickLabelsVisible(true);
        range.setStandardTickUnits(NumberAxis.createIntegerTickUnits());
        JFreeChart chart = new JFreeChart(plot);
 
        XYItemRenderer r = plot.getRenderer();
        if (r instanceof XYLineAndShapeRenderer) {
            XYLineAndShapeRenderer renderer1 = (XYLineAndShapeRenderer) r;
            renderer1.setBaseShapesVisible(true);
            renderer1.setBaseShapesFilled(true);
        }
 
 
        chart.setTitle(new TextTitle("Comparaison du débit Http"));
 
 
        final ChartPanel chartPanel = new ChartPanel(chart);
        chartPanel.setPreferredSize(new java.awt.Dimension(600, 300));
        chartPanel.setMouseZoomable(true, false);
 
        OutputStream out = response.getOutputStream();
        ChartUtilities.writeChartAsPNG(out, chart, 700, 500);   
          request.setAttribute("image",out);
 
    }		
 
 
 
 
 
	 protected void doGet(HttpServletRequest request, HttpServletResponse response)
     throws ServletException, IOException {
 try {
	processRequest(request, response);
} catch (SQLException e) {
	// TODO Auto-generated catch block
	e.printStackTrace();
}
}
 
	}

Can you help me please ? we can't draw this chart using Jfreechart ?

[marouene_]

no help please ?

[matinh]

Your question is not related to JFreeChart but to databases/SQL.
Try to ask in another forum.

- martin

[marouene_]

Hi,

Now, my question is related to Jfreechart.

Supposed we have this tables :

+------------+----------+---------+
| day | Time | value |
+------------+----------+---------+
| 2012-04-16 | 16:09:50 | 4619.46 |
| 2012-04-16 | 17:10:05 | 3497.93 |
| 2012-04-16 | 18:09:13 | 5862.1 |
| 2012-04-16 | 19:09:21 | 6006.03 |
| 2012-04-16 | 20:09:34 | 5716.41 |
| 2012-04-16 | 21:09:21 | 5537.5 |
| 2012-04-16 | 22:09:53 | 4083.13 |
| 2012-04-16 | 23:09:49 | 4212.54 |
| 2012-04-17 | 00:09:40 | 4387.92 |
| 2012-04-17 | 01:09:54 | 3678.67 |
| 2012-04-17 | 02:09:17 | 5696.32 |
| 2012-04-17 | 03:09:52 | 4484.08 |
| 2012-04-17 | 04:09:37 | 4486.34 |
| 2012-04-17 | 05:10:31 | 4373.79 |
| 2012-04-17 | 06:09:49 | 4461.08 |
| 2012-04-17 | 07:09:08 | 5881.93 |
| 2012-04-17 | 08:08:57 | 5532.78 |
| 2012-04-17 | 09:09:47 | 4213.67 |
| 2012-04-17 | 10:09:14 | 5644.68 |
| 2012-04-17 | 11:09:15 | 5217.59 |
| 2012-04-17 | 12:10:04 | 3678 |
| 2012-04-17 | 13:10:22 | 4086.52 |
| 2012-04-17 | 16:01:29 | 940.421 |

i want to draw a timeseries chart between the first values and the las one.

The problem is that in my abcisse axe, the variation is between |00,00] and no [16:09:50 ,16:01:29]

It show me only the result of 2012-04-16

[paradoxoff]

JFreeChart will draw the values that you are providing. If you do not see the expected values in the chart, you most probably do not provide the expected values. So I agree with martin that this is not a real JFreeChart question.
A quick look at your code suggest that the source of the unexpected results is probably this block

Code: Select all

java.sql.Time v1;
v1 = rs1.getTime("heure");
double v2=rs1.getFloat("valeur");
series1.addOrUpdate( new Minute(v1), v2);

It seems that you are only extracting the values for the "heure" not for the "date".
As a result, JFreeChart has to assume that all data points lie within the same day between the smallest "heure" value (00:09:40) and the largest (23:09:49), and thus scales the axis between 00:00:00 and 00:00:00.
Solution: extract the values from the column "date" as well, combine the "date" and the "heure" to a java.util.Date object, and use that to construct your Minute objects.