从Mysql DB填充JFreechart TimeSeriesCollection？ [英] Populate JFreechart TimeSeriesCollection from Mysql DB?

问题描述

我正在尝试在我的应用程序中创建一个图表，该图表会返回几个月内的温度。

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

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

为了解决这个问题，我尝试将图表实现为已发布并在时间序列图表中显示数据集。请注意， JDBCXYDataset 可识别时间序列数据。作为检查，查询 JDBCXYDataset 以获取返回的日期值。

典型的每小时数据：

 
 ... 
 2014年7月15日下午1:10:25 
 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 
 ... 
 

代码：

  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 
 * / 
公共类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（
库存，日期，计数，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，，）; 
语句st = conn.createStatement（）; 
 st.execute（创建表库存（当时间戳，n1整数，n2整数））; 
 PreparedStatement ps = conn.prepareStatement（
插入库存值（？，？，？））; 
日历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（从库存中选择时间，n1，n2）; 
返回jds; 
} catch（SQLException ex）{
 ex.printStackTrace（System.err）; 
} 
返回null; 
} 
 
 public static void main（String [] args）{
 EventQueue.invokeLater（new Runnable（）{
 @Override 
 public void run （）{
 new JDBCTest（）。display（）; 
} 
}）; 
} 
} 
  

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.

解决方案

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

这篇关于从Mysql DB填充JFreechart TimeSeriesCollection？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！