从 Mysql DB 填充 JFreechart TimeSeriesCollection? [英] Populate JFreechart TimeSeriesCollection from Mysql DB?
问题描述
我正在尝试在我的应用程序中制作一个图表,该图表可以返回几个月中的天数.
此图表是 JFreechart TimeSeriesCollection,我无法让图表从数据库中读取正确的数据.
它显示了一些值,但不是全部,并且没有显示正确的时间.
为了解决这个问题,我尝试实现发布的图表 并在时间序列图表中显示数据集.请注意,JDBCXYDataset
识别时间序列数据.作为检查,JDBCXYDataset
被查询返回的日期值.
典型的每小时数据:
<前>…2014 年 7 月 15 日下午 1:10:252014 年 7 月 15 日下午 2:10:252014 年 7 月 15 日下午 3:10:252014 年 7 月 15 日下午 4:10:252014 年 7 月 15 日下午 5:10:252014 年 7 月 15 日下午 6:10:25…代码:
import java.awt.EventQueue;导入 java.sql.Connection;导入 java.sql.Date;导入 java.sql.DriverManager;导入 java.sql.PreparedStatement;导入 java.sql.SQLException;导入 java.sql.Statement;导入 java.sql.Timestamp;导入 java.text.DateFormat;导入 java.util.Calendar;导入 java.util.Random;导入 javax.swing.JFrame;导入 org.jfree.chart.ChartFactory;导入 org.jfree.chart.ChartPanel;导入 org.jfree.chart.JFreeChart;导入 org.jfree.data.jdbc.JDBCXYDataset;/*** @see http://stackoverflow.com/a/24762078/230513*/公共类 JDBCTest {私有静态最终 int N = 24;私有静态最终随机 r = 新随机();私人无效显示(){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);for (int i = 0; i < jds.getItemCount(); i++) {System.out.println(DateFormat.getDateTimeInstance().format(new Date(jds.getX(0, i).longValue()));}}私有 JDBCXYDataset createDataset() {尝试 {连接 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.执行();c.add(Calendar.HOUR_OF_DAY, 1);}JDBCXYDataset jds = new JDBCXYDataset(conn);jds.executeQuery("select when, n1, n2 from inventory");返回 jds;} catch (SQLException ex) {ex.printStackTrace(System.err);}返回空;}公共静态无效主(字符串 [] args){EventQueue.invokeLater(new Runnable() {@覆盖公共无效运行(){新的 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屋!