Mysql Workbench不读取通过JDBC插入的记录 [英] Mysql Workbench is not reading records inserted through JDBC

问题描述

说明：
工作台中的自动提交值已设置为0（零），然后我向表插入一条记录，并通过jdbc在Java App客户端中成功提交了事务。我在命令行中执行select query，并且刚刚插入的记录已成功获取，但在workbech中执行相同的查询脚本，则无法读取刚刚插入的记录。

如何重复：

1. 在mysql workbench中设置autocommit = 0。


2. 通过jdbc从Java应用程序向表中插入记录，




建议的修正： strong>
工作台应该读取已提交的记录，而不是再次执行commit命令以通过jdbc显示已提交的记录。





下面是我的jdbc代码

  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自动生成的catch块
 e.printStackTrace（）; 
} 
 
} 
  




解决方案






1. 在mysql workbench中设置autocommit = 0






这意味着您必须手动提交来自Java的事务。






2. 从java应用程序通过jdbc将记录插入到表中，并通过java提交事务。






但是你从来没有从Java提交。您应该这样做：

  conn.commit（）; 
  

完整代码：

  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（）; 
} 
  




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();
        } 

    }

解决方案

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();
}

这篇关于Mysql Workbench不读取通过JDBC插入的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！