Mysql Workbench is not reading records inserted th

Question

Description: The autocommit value in workbench had been set to 0( zero ), then I insert a record to a table and successfully committed the transaction in Java App client through jdbc. I execute select query in the command line and the record just inserted had successfully be fetched, but execute the same query script in workbech, the record just inserted could not be fetched. only after executing the commit command in the workbench, the record can be queried.

How to repeat:

1. set autocommit=0 in mysql workbench.
2. insert a record to a table from java app through jdbc and commit the transaction through java.
3. execute select query in the workbech.

Suggested fix: the workbench should read the committed records, not execute the commit command again to display already committed records through jdbc .

Below is my jdbc code for the same

    int recordInserted = 0;
    PreparedStatement prepStmt = null;
    try {

        conn = new DatabaseConnection().getSQLConnection();

        if (conn != null) {
            String sql = "INSERT INTO customerinfo (CustID, Customer_Name, Customer_License) VALUES (?, ?, ?)";

            prepStmt = conn.prepareStatement(sql);
            prepStmt.setString(1, registerRecords.getCustomerID());
            prepStmt.setString(2, registerRecords.getCustomerName());
            prepStmt.setString(3, registerRecords.getCustomerLic());

            recordInserted = prepStmt.executeUpdate();

        }
    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {
        try {
             conn.close(); 
             prepStmt.close();
             conn = null;
             prepStmt=null;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } 

    }

Answer 1

1. set autocommit=0 in mysql workbench

This means that you will have to manually commit the transaction from Java.

2. insert a record to a table from java app through jdbc and commit the transaction through java.

But you never actually commit from Java. You should be doing this:

conn.commit();

Full code:

if (conn != null) {
    String sql = "INSERT INTO customerinfo (CustID, Customer_Name, Customer_License) VALUES (?, ?, ?)";

    prepStmt = conn.prepareStatement(sql);
    prepStmt.setString(1, registerRecords.getCustomerID());
    prepStmt.setString(2, registerRecords.getCustomerName());
    prepStmt.setString(3, registerRecords.getCustomerLic());
    recordInserted = prepStmt.executeUpdate();

    conn.commit();
}