在Java中误执行了两次查询,并获得了不需要的值。

huangapple go评论80阅读模式
英文:

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 a SimpleDateFormat or A to a DateTimeFormatter.

  • For study, temporarily pass the query directly to JDBCXYDataset, and add an ORDER 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 your WHERE 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 a String. If your design needs to display data from a query defined by a PreparedStatement, 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;
    }
    …
    }
    

huangapple
  • 本文由 发表于 2020年9月18日 21:26:51
  • 转载请务必保留本文链接:https://go.coder-hub.com/63956674.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定