java - 从 Mysql 数据库填充 JFreechart TimeSeriesCollection？

Question

我正在尝试在我的应用程序中制作一个图表，以返回几个月中几天的温度。

此图表是 JFreechart TimeSeriesCollection，我无法让图表从数据库中读取正确的数据。

它显示了一些值，但不是全部，并且没有显示正确的时间。

为了解决这个问题，我尝试实现发布的图形 here , 但仍然无法解决我的问题，即使去看了这个 question , 正如人们所建议的那样

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

}

}

这是我的代码生成的图表。但是查询的结果是:

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   |

等...

如此处所示，它没有显示所有值，也没有列出小时数。

--------------------编辑--------------------

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'

实现这个并没有改变输出。

Answer 1

我怀疑您在将 String 转换为 Date 时丢失了精度。这个完整的示例在内存中创建了一个合适的数据库表，将其查询到 JDBCXYDataset 中。并在时间序列图表中显示数据集。请注意，JDBCXYDataset 可识别时间序列数据。作为检查，查询 JDBCXYDataset 以获取返回的日期值。

典型的每小时数据:

…Jul 15, 2014 1:10:25 PMJul 15, 2014 2:10:25 PMJul 15, 2014 3:10:25 PMJul 15, 2014 4:10:25 PMJul 15, 2014 5:10:25 PMJul 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();
            }
        });
    }
}