Populate JFreechart TimeSeriesCollection from Mysql DB?

Question

I'm trying to make a chart in my application that returns me the temperature of days during the months.

This chart is a JFreechart TimeSeriesCollection, and I am unable to have the graph read correct data from the database.

It shows some of the values, but not all of them, and does not show the correct time.

To fix this, I tried to implement the graph as posted here, but still could not solve my problem, even having gone to see this question, as people suggested

public class NewClass extends ApplicationFrame {

Connection conexao = null;
PreparedStatement pst= null;
ResultSet rs = null;

public NewClass(String title) throws SQLException, ParseException {
    super(title);
    ChartPanel chartPanel = (ChartPanel) createDemoPanel();
    chartPanel.setPreferredSize(new java.awt.Dimension(500, 270));
    setContentPane(chartPanel);


}


private static JFreeChart createChart(XYDataset dataset) {

    JFreeChart chart = ChartFactory.createTimeSeriesChart(
        "Temperatura/Date",  // title
        "Date",             // x-axis label
        "Temperatura",   // y-axis label
        dataset,            // data
        true,               // create legend?
        true,               // generate tooltips?
        false               // generate URLs?
    );


    XYPlot plot = (XYPlot) chart.getPlot();

    DateAxis axis = (DateAxis) plot.getDomainAxis();
    axis.setDateFormatOverride(new SimpleDateFormat("yyyy-MM-dd"));

    return chart;

}


private static XYDataset createDataset() throws SQLException, ParseException {
    Connection con = null;
    String databaseURL = "jdbc:mysql://localhost:3306/world";
    String driverName = "com.mysql.jdbc.Driver";
    String user = "root";
    String password = "rootadmin";
    try {
        Class.forName(driverName).newInstance();
    } catch (Exception ex) {
        System.out.println("");
    }

        con = (Connection) DriverManager.getConnection(databaseURL, user, password);

        if (!con.isClosed()) {
            System.out.println("Successfully connected to the DataBase Server...");
        }


        Statement statement;
        statement = (Statement) con.createStatement();

        String selectQuery = "select (CONCAT(`data_registo`, ' ', hora_registo)) as data,  temperatura, idSensor from registos where idSensor like 'BrgTH001' ";
        ResultSet resultSet = null;
        resultSet = statement.executeQuery(selectQuery);




    TimeSeries s1 = new TimeSeries("Thermomether01");
    while (resultSet.next()) {

                String idSensor = (String) resultSet.getObject("idSensor");
                String data = (String) resultSet.getObject("data");
                String temperatura = (String) resultSet.getObject("temperatura");

                SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM-dd HH:mm");
                Date dateI = sdf2.parse(data);


                System.out.println("" + idSensor + " " + data + "   " + temperatura+ "   |   " );


                s1.addOrUpdate(new Hour(dateI), value);



            } 

        resultSet.close();



    TimeSeriesCollection dataset = new TimeSeriesCollection();
    dataset.addSeries(s1);


    return dataset;


}

public static JPanel createDemoPanel() throws SQLException, ParseException {
    JFreeChart chart = createChart(createDataset());
    ChartPanel panel = new ChartPanel(chart);
    panel.setFillZoomRectangle(true);
    panel.setMouseWheelEnabled(true);
    return panel;
}


public static void main(String[] args) throws SQLException, ParseException {

    NewClass demo = new NewClass("Chart");
    demo.pack();
    demo.setVisible(true);

}

}

This is the chart that results of my code. But the result of query is that:

Thermomether01 2014-04-01 08:47:11   23.8   |   
Thermomether01 2014-04-01 08:47:11   23.8   |   
Thermomether01 2014-04-01 08:51:20   23.9   |   
Thermomether01 2014-04-01 08:53:24   23.9   |   
Thermomether01 2014-04-01 08:55:28   23.9   |   
Thermomether01 2014-04-01 09:43:26   24.1   |   
Thermomether01 2014-04-01 09:48:39   24.0   |   
Thermomether01 2014-04-01 09:50:44   24.1   |   
Thermomether01 2014-04-01 09:52:48   24.0   |   
Thermomether01 2014-04-01 09:54:52   24.1   |   
Thermomether01 2014-04-01 09:56:56   24.1   |   
Thermomether01 2014-04-01 09:59:01   24.1   |   
   .
   .
   .
Thermomether01 2014-06-13 09:35:36   19.2   |   
Thermomether01 2014-06-13 10:03:00   18.7   |   
Thermomether01 2014-06-13 10:33:41   19.0   |   
Thermomether01 2014-06-16 08:57:57   19.1   |   
Thermomether01 2014-06-16 09:07:54   18.9   |   
Thermomether01 2014-06-16 09:08:40   19.0   |   
Thermomether01 2014-06-16 09:36:28   19.1   |   
Thermomether01 2014-06-16 10:03:51   18.8   |   
Thermomether01 2014-06-16 10:31:14   19.2   |   
Thermomether01 2014-06-16 11:00:17   19.1   |   
Thermomether01 2014-06-16 11:27:38   19.2   |   
Thermomether01 2014-06-16 11:54:59   19.1   |   
Thermomether01 2014-06-16 12:51:24   18.5   |

ETC...

As shown here, it doesn't show all values and doesn't list hours.

----------------------EDIT----------------------

select (CONCAT(`data_registo`, ' ', hora_registo)) as data,  temperatura, idSensor from registos where idSensor like 'Thermometer01' and temperatura not in ('---')  and data_registo between '2014-06-01' and '2014-06-10'

Implementing this did not change the output.

Sporangium

Answer 1

I suspect that you're losing precision in the conversion of a String to a Date. This complete example creates a suitable database table in memory, queries it into a JDBCXYDataset and displays the dataset in a time series chart. Note that JDBCXYDataset recognizes time series data. As a check, the JDBCXYDataset is queried for the returned date values.

Typical hourly data:

…
Jul 15, 2014 1:10:25 PM
Jul 15, 2014 2:10:25 PM
Jul 15, 2014 3:10:25 PM
Jul 15, 2014 4:10:25 PM
Jul 15, 2014 5:10:25 PM
Jul 15, 2014 6:10:25 PM
…

Code:

import java.awt.EventQueue;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.util.Calendar;
import java.util.Random;
import javax.swing.JFrame;
import org.jfree.chart.ChartFactory;
import org.jfree.chart.ChartPanel;
import org.jfree.chart.JFreeChart;
import org.jfree.data.jdbc.JDBCXYDataset;

/**
 * @see https://mcmap.net/q/541768/-populate-jfreechart-timeseriescollection-from-mysql-db
 */
public class JDBCTest {

    private static final int N = 24;
    private static final Random r = new Random();

    private void display() {
        JFrame f = new JFrame("JDBCTest");
        f.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        JDBCXYDataset jds = createDataset();
        JFreeChart chart = ChartFactory.createTimeSeriesChart(
            "Inventory", "Date", "Count", jds, true, true, false);
        f.add(new ChartPanel(chart));
        f.pack();
        f.setLocationRelativeTo(null);
        f.setVisible(true);
        for (int i = 0; i < jds.getItemCount(); i++) {
            System.out.println(DateFormat.getDateTimeInstance()
                .format(new Date(jds.getX(0, i).longValue())));
        }
    }

    private JDBCXYDataset createDataset() {
        try {
            Connection conn = DriverManager.getConnection(
                "jdbc:h2:mem:test", "", "");
            Statement st = conn.createStatement();
            st.execute("create table inventory(when timestamp, n1 integer, n2 integer)");
            PreparedStatement ps = conn.prepareStatement(
                "insert into inventory values (?, ?, ?)");
            Calendar c = Calendar.getInstance();
            for (int i = 0; i < N; i++) {
                ps.setTimestamp(1, new Timestamp(c.getTimeInMillis()));
                ps.setInt(2, N / 3 + r.nextInt(N / 2));
                ps.setInt(3, N / 2 + r.nextInt(N / 2));
                ps.execute();
                c.add(Calendar.HOUR_OF_DAY, 1);
            }
            JDBCXYDataset jds = new JDBCXYDataset(conn);
            jds.executeQuery("select when, n1, n2 from inventory");
            return jds;
        } catch (SQLException ex) {
            ex.printStackTrace(System.err);
        }
        return null;
    }

    public static void main(String[] args) {
        EventQueue.invokeLater(new Runnable() {
            @Override
            public void run() {
                new JDBCTest().display();
            }
        });
    }
}

Aldoaldol

Thanks for reply @Aldoaldol . One more question Sir. Why do I even limiting the results on the query in graph appears everything is in the database? I edited my question to explain my doubt. – Sporangium