Populate Jfreechart Timeseriescollection from MySQL Db

Populate JFreechart TimeSeriesCollection from Mysql DB?

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 http://stackoverflow.com/a/24762078/230513
 */
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();
            }
        });
    }
}

JFreechart chart with Time from SQL Database

The example cited using JDBCXYDataset also works with JDBCCategoryDataset, as shown below and in your original question. Using JDBCCategoryDataset, "The first column will be the category name and [the] remaining columns [will be] values (each column represents a series);" using JDBCXYDataset, "The first column will be the x-axis and remaining columns y-axis values. " As a result, I would expect your query to be something like this:

SELECT Date_Time, PV …

As your domain axis is time, consider rotating the label positions, as shown here. When deciding, note that a TimeSeries is less flexible about orientation but more flexible about formatting.

the values in my database are Float.

The following changes to the example illustrate using floating point values. Note that PV is of type float, and the PreparedStatement uses setFloat().

JDBCCategoryDataset jds = createDataset();
JFreeChart chart = ChartFactory.createLineChart("Test", "Time", "PV",
    jds,PlotOrientation.VERTICAL, true, true, false);
CategoryPlot plot = (CategoryPlot) chart.getPlot();
CategoryAxis domain = plot.getDomainAxis();
plot.getDomainAxis().setCategoryLabelPositions(CategoryLabelPositions.UP_45);
…
private JDBCCategoryDataset createDataset() {
    try {
        Connection conn = DriverManager.getConnection(
            "jdbc:h2:mem:test", "", "");
        Statement st = conn.createStatement();
        st.execute("create table data(when timestamp, pv float)");
        PreparedStatement ps = conn.prepareStatement(
            "insert into data values (?, ?)");
        Calendar c = Calendar.getInstance();
        for (int i = 0; i < N; i++) {
            ps.setTimestamp(1, new Timestamp(c.getTimeInMillis()));
            ps.setFloat(2, (float)r.nextGaussian() + 2);
            ps.execute();
            c.add(Calendar.SECOND, r.nextInt(60 * 60));
        }
        JDBCCategoryDataset jds = new JDBCCategoryDataset(conn);
        jds.executeQuery("select when, pv from data");
        return jds;
    } catch (SQLException ex) {
        ex.printStackTrace(System.err);
    }
    return null;
}

Why is the data in a timeseriescollection chart don't appear correctly?

You're truncating dateI to the nearest hour. Instead, use Second to preserve the full resolution retrieved from the query and store the result in the model.

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

In the view, you can setDateFormatOverride() to the desired format on the domain axis, as shown here.

axis.setDateFormatOverride(new SimpleDateFormat("yyyy-MM-dd HH"));

Creating TimeChart from MySql

Your best bet is going to just run a query and create a TimePeriodValuesCollection. JDBCXYDataset is just an XYDataset.

For your second question, you just need to have your Time Period Series populated with three years of data. If you do that the chart is going to show the whole three years by default. Then you can zoom.

When creating the data, use a TimePeriodValues populated with SimpleTimePeriod 's objects.

---

Related Topics