Query executed twice (by error) in Java with unwanted values

Question

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.

Yes I'm sure, I've checked and also with how "cmd3" is working it's impossible to have twice the same timestamp as it's based on current time. I tried to add unique to my new table but as I said it only keep the unwanted values and not the one I'm supposed to get, which is weird I agree but I don't know how tp fix it. ( it was an answer to somebody before he deleted his comment, I will keep it to add some detail ) — Nemrod, Sep 18 '20 at 13:41
You are not doing ps.executeUpdate, nor closing statements and result sets. Closing is important, use try-with-resources (commits etc.). But my first guess was a bug with JDBCXYDataset jds. — Joop Eggen, Sep 18 '20 at 13:52
@JoopEggen I edited my post with what you said, is it how you thought about it ? If it is then it doesn't work better unfortunately.... Ps : My images have been deleted.... that makes my post less understandable but I guess I don't have a choice. — Nemrod, Sep 18 '20 at 15:14
Yes, in principle the usage of the prepared statement is better. Maybe there is still some answer coming. — Joop Eggen, Sep 18 '20 at 16:02
@Nemrod: nothing is lost; see the [edit history](https://stackoverflow.com/posts/63956674/revisions) for details. — trashgod, Sep 19 '20 at 03:46

trashgod · Accepted Answer · 2020-09-19T12:33:59.730

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;
    }
    …
}

Query executed twice (by error) in Java with unwanted values

1 Answers1

Linked