带有 SQL 数据库时间的 JFreechart 图表 [英] JFreechart chart with Time from SQL Database

问题描述

我是 JFreeChart 的新手，我正在尝试创建一个图表，在 y 轴上显示一个值，在 x 轴上显示时间.所有这些数据(值和时间)都已经在我的数据库中了，我每 3 到 4 秒就有一个值.

但是我不想显示所有内容或动态.例如，我只想显示一个图表，显示从 2020-06-16 10:31:52 到 2020-06-29 11:31:52 的所有值(但正如我所说，所有内容都已经在我的数据库中).

这是我的变体(没有所有导入):

公共类JDBCTest {私人无效显示(){JFrame f = new JFrame("JDBCTest");f.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);JDBCXYDataset jds = createDataset();JFreeChart 图表 = ChartFactory.createTimeSeriesChart(库存"、日期"、计数"、jds、true、true、false)；f.add(new ChartPanel(chart));f.pack();f.setLocationRelativeTo(null);f.setVisible(true);}私有 JDBCXYDataset createDataset() {尝试 {连接 conn = DriverManager.getConnection("jdbc:mysql://localhost:",",");语句 st = conn.createStatement();st.execute(DROP TABLE IF EXISTS Test");st.execute("创建表测试(Date_Heure时间戳，PV浮点)");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)";+ "选择表，表"+从表中"+ "WHERE Date_Heure BETWEEN ?和?";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");返回 jds;} catch (SQLException ex) {ex.printStackTrace(System.err);}返回空；}公共静态无效主(字符串 [] args){EventQueue.invokeLater(new Runnable() {@覆盖公共无效运行(){新的 JDBCTest().display();}});}}

错误来自数据库而不是代码本身.

---

该代码的灵感来自于垃圾神的工作

<块引用>

我的数据库中的值是Float.

对示例的以下更改说明了使用浮点值.注意PV是float类型，PreparedStatement使用setFloat().

JDBCCategoryDataset jds = createDataset();JFreeChart 图表 = ChartFactory.createLineChart("Test", "Time", "PV",jds,PlotOrientation.VERTICAL, true, true, false);CategoryPlot plot = (CategoryPlot) chart.getPlot();类别轴域 = plot.getDomainAxis();plot.getDomainAxis().setCategoryLabelPositions(CategoryLabelPositions.UP_45);…私有 JDBCCategoryDataset createDataset() {尝试 {连接 conn = DriverManager.getConnection("jdbc:h2:mem:test", "", "");语句 st = conn.createStatement();st.execute("创建表数据(当时间戳，pv浮点数)");PreparedStatement ps = conn.prepareStatement(插入数据值(?，?)")；日历 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.执行()；c.add(Calendar.SECOND, r.nextInt(60 * 60));}JDBCCategoryDataset jds = new JDBCCategoryDataset(conn);jds.executeQuery(select when, pv from data");返回 jds;} catch (SQLException ex) {ex.printStackTrace(System.err);}返回空；}

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

解决方案

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

这篇关于带有 SQL 数据库时间的 JFreechart 图表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！