java - JFreechart chart with Time from SQL Database

Question

I'm new with JFreeChart and I'm trying to create a chart that display a value on the y-axis and the time on the x-axis. All theses data (value and time) are already in my database and I have something like a value for each 3 to 4 seconds.

However I do not want to display everything or to be dynamic. I just want to display a chart that shows all the values from 2020-06-16 10:31:52 to 2020-06-29 11:31:52 for example (but everything is already in my Database as I said).

Here is my variation of this (without all the imports) :

public class JDBCTest {

    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);
    }

    private JDBCXYDataset createDataset() {
        try {
            Connection conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:","", "");
           
            Statement st = conn.createStatement();
            st.execute("DROP TABLE IF EXISTS Test ");
            st.execute("create table Test(Date_Heure timestamp, PV float)");
            
            String Date_Debut = "2020-06-25 13:28:08";
            String Date_Fin = "2020-06-26 13:28:08";
            String sql1 = "INSERT INTO Test (Date_Heure,PV) "
                    + "SELECT TABLE ,TABLE "
                    + "FROM TABLE "
                    + "WHERE Date_Heure BETWEEN ? AND ?";
            
            PreparedStatement ps = conn.prepareStatement(sql1);
           
            ps.setString(1,Date_Debut);
            ps.setString(2,Date_Fin);
            ps.executeUpdate();
            
            JDBCXYDataset jds = new JDBCXYDataset(conn);
            jds.executeQuery("select TABLE , TABLE from Test");
            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();
            }
        });
    }
}

EDIT : The error was comming from the Database and not from the code itself.

---

Edit : The code is inspired around trashgod's work http://stackoverflow.com/a/24762078/230513

additional information :

Date_Time type's timestamp

PV type's float

Answer 1

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;
}