英文:
Query executed twice (by error) in Java with unwanted values
问题
我正在使用 JFreeChart 在 Java 和 MySQL 中创建图表。
当我尝试将我的值插入另一个表中时,查询似乎被执行了两次,因为最终我会得到相同的时间戳多次出现...
这是我的代码的一部分:
private JDBCXYDataset createDataset() {
try {
Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost:bd?serverTimezone=UTC","MySQL", "MySQL");
conn.setAutoCommit(false);
SQLException savedException = null;
Statement st = conn.createStatement();
st.execute("DROP TABLE IF EXISTS test ");
st.execute("create table test(Table timestamp, Table float,Table float)");
String Date_Debut = "2020-06-25 00:00:00";
String Date_Fin = "2020-06-26 00:00:00";
String sql1 = "INSERT INTO test (Table ,Table ,Table ) "
+ "SELECT Table ,Table ,Table "
+ "FROM Table "
+ "WHERE Table BETWEEN ? AND ? ";
try ( PreparedStatement ps = conn.prepareStatement(sql1)){
ps.setString(1,Date_Debut);
ps.setString(2, Date_Fin);
ps.executeUpdate();
ps.close();
JDBCXYDataset jds = new JDBCXYDataset(conn);
st.close();
jds.executeQuery("SELECT Table ,Table ,Table FROM test");
conn.commit();
return jds;
} catch (SQLException ex) {
savedException = ex;
conn.rollback();
} finally {
conn.setAutoCommit(true);
if(savedException != null) {
throw savedException;
}
}
} catch (SQLException ex1) {
}
return null;
}
编辑:实际上,错误似乎直接来自数据库,如果版主们愿意,他们可以删除这篇帖子。然而,我保留 Trashgod 的回答,因为它非常有帮助。对于所有可能遇到类似问题的人,首先详细检查你的数据库,看看问题是否出在那里,而不是你的代码。
英文:
I'm using JFreeChart to create a chart in Java and MySQL.
When I try to insert my values in another table the query seems to be executed twice since I end up with the same timestamps multiple times...
Here's a part of my code :
private JDBCXYDataset createDataset() {
try {
Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost:bd?serverTimezone=UTC","MySQL", "MySQL");
conn.setAutoCommit(false);
SQLException savedException = null;
Statement st = conn.createStatement();
st.execute("DROP TABLE IF EXISTS test ");
st.execute("create table test(Table timestamp, Table float,Table float)");
String Date_Debut = "2020-06-25 00:00:00";
String Date_Fin = "2020-06-26 00:00:00";
String sql1 = "INSERT INTO test (Table ,Table ,Table ) "
+ "SELECT Table ,Table ,Table "
+ "FROM Table "
+ "WHERE Table BETWEEN ? AND ? ";
try ( PreparedStatement ps = conn.prepareStatement(sql1)){
ps.setString(1,Date_Debut);
ps.setString(2, Date_Fin);
ps.executeUpdate();
ps.close();
JDBCXYDataset jds = new JDBCXYDataset(conn);
st.close();
jds.executeQuery("SELECT Table ,Table ,Table FROM test");
conn.commit();
return jds;
} catch (SQLException ex) {
savedException = ex;
conn.rollback();
} finally {
conn.setAutoCommit(true);
if(savedException != null) {
throw savedException;
}
}
} catch (SQLException ex1) {
}
return null;
}
EDIT : Actually it seems like the errors where comming directly from the database, the moderators can delete this post if they want. However I keep Trashgod's response validated as it was more than helpful.
For everyone that might come here with a similar issue, inspect in detail your database first to see if it isn't comming from there instead of your code.
答案1
得分: 2
追踪数据中的异常是一项艰巨的任务,但JFreeChart
至少可以使结果更容易可视化。一些用于测试的启发式方法:
-
要验证您表格清单中的预期重复项是否确实重复,请将时间戳格式化以包括毫秒,例如,向
SimpleDateFormat
添加S
或向DateTimeFormatter
添加A
。 -
为了研究,暂时将查询直接传递给
JDBCXYDataset
,并添加ORDER BY
子句(未经测试):jds.executeQuery( "SELECT Date_Heure, PV, SV FROM cmd3 " + "WHERE Date_Heure BETWEEN " + "2020-06-25 00:00:00 AND 2020-06-26 00:00:00 " + "ORDER BY Date_Heure");
-
在您的
ChartFactory
中启用工具提示,就像您在这里所做的那样,以在现场查看数据值。这可能会为您的WHERE
子句提供额外的条件,例如PV BETWEEN 5.1 AND 5.9
。 -
使用交互式的
JFreeChart
平移/缩放控件,可在这里讨论如何检查数据;如果能够使同事更容易地查看您的发现,可以添加适当的按钮,就像这里所示。 -
根据设计,
JDBCXYDataset
执行由String
定义的查询。如果您的设计需要显示由PreparedStatement
定义的查询的数据,您可以使用现有的实现作为指南。public class PreparedDataset extends AbstractXYDataset implements XYDataset, TableXYDataset, RangeInfo { private final PreparedStatement ps; public PreparedDataset(PreparedStatement ps) { this.ps = ps; } … }
英文:
Chasing down anomalies in data is arduous, but JFreeChart
can at least make the result easier to visualize. Some heuristics for testing:
-
To verify that the the presumed duplicates in your tabular listing are indeed duplicates, format the timestamps to include milliseconds, e.g. add an
S
to aSimpleDateFormat
orA
to aDateTimeFormatter
. -
For study, temporarily pass the query directly to
JDBCXYDataset
, and add anORDER BY
clause (untested):jds.executeQuery( "SELECT Date_Heure, PV, SV FROM cmd3 " + "WHERE Date_Heure BETWEEN " + "2020-06-25 00:00:00 AND 2020-06-26 00:00:00 " + "ORDER BY Date_Heure");
-
Enable tooltips in your
ChartFactory
, as you did here, to see data values in situ. This may suggest additional conditions for yourWHERE
clause, e.g.PV BETWEEN 5.1 AND 5.9
. -
Use the interactive
JFreeChart
pan/zoom controls, discussed here to examine the data; add suitable buttons, shown here, if it will make it easier for colleagues to see your findings. -
By design,
JDBCXYDataset
executes a query defined by aString
. If your design needs to display data from a query defined by aPreparedStatement
, you can use the existing implementation as a guide.public class PreparedDataset extends AbstractXYDataset implements XYDataset, TableXYDataset, RangeInfo { private final PreparedStatement ps; public PreparedDataset(PreparedStatement ps) { this.ps = ps; } … }
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论