java - MySQLSyntaxErrorException 神秘

Question

我正在尝试更新我的 jtable(更改值并按 Enter 键)，但出现错误。由于大小原因，错误未完整。我认为其余部分只是 c3p0 池连接工具生成的不相关信息。

假设 起初，我认为这可能是 c3p0 的问题，因为为了将它用于准备好的语句，我必须编辑配置文件/更改设置。但我使用传统的从DriverManager获取连接的方式发现它与此无关。

另一个假设 - 是一些数据类型问题。因为我输入 3.00 并且错误显示 ''Flat' = '3.0' where ID = '4'' at line 1ImportData 方法中的文本测试行未执行。所以这意味着它在执行SQL语句时崩溃了。在我的数据库表中，除 ID 和日期之外的列都是 float(9,2) 类型，意味着总位数 = 9；数字“点后”= 2。我确实有 DecimalFormatRenderer 类，可能与此有关。

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Flat' = '3.0' where ID = '4'' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
at com.mysql.jdbc.Util.getInstance(Util.java:360)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2530)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1907)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2141)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2077)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2062)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)
at components.TableWithBottomLine$ImportData.<init>(TableWithBottomLine.java:174)
at components.TableWithBottomLine.tableChanged(TableWithBottomLine.java:147)
at javax.swing.table.AbstractTableModel.fireTableChanged(Unknown Source)
at javax.swing.table.AbstractTableModel.fireTableCellUpdated(Unknown Source)
at components.TableWithBottomLine$MyTableModel.setValueAt(TableWithBottomLine.java:251)
at javax.swing.JTable.setValueAt(Unknown Source)
at javax.swing.JTable.editingStopped(Unknown Source)
at javax.swing.AbstractCellEditor.fireEditingStopped(Unknown Source)
at javax.swing.DefaultCellEditor$EditorDelegate.stopCellEditing(Unknown Source)
at javax.swing.DefaultCellEditor.stopCellEditing(Unknown Source)
at javax.swing.JTable$GenericEditor.stopCellEditing(Unknown Source)
at javax.swing.DefaultCellEditor$EditorDelegate.actionPerformed(Unknown Source)
at javax.swing.JTextField.fireActionPerformed(Unknown Source)
at javax.swing.JTextField.postActionEvent(Unknown Source)
at javax.swing.JTextField$NotifyAction.actionPerformed(Unknown Source)
at javax.swing.SwingUtilities.notifyAction(Unknown Source)
at javax.swing.JComponent.processKeyBinding(Unknown Source)
at javax.swing.JComponent.processKeyBindings(Unknown Source)
at javax.swing.JComponent.processKeyEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.KeyboardFocusManager.redispatchEvent(Unknown Source)
at java.awt.DefaultKeyboardFocusManager.dispatchKeyEvent(Unknown Source)
at java.awt.DefaultKeyboardFocusManager.preDispatchKeyEvent(Unknown Source)
at java.awt.DefaultKeyboardFocusManager.typeAheadAssertions(Unknown Source)
at java.awt.DefaultKeyboardFocusManager.dispatchEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.awt.EventQueue.access$400(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)

我的程序的代码摘录:

public void tableChanged(TableModelEvent e) {
    int row = e.getFirstRow();
    int col = e.getColumn();
    model = (MyTableModel) e.getSource();
    String colName = model.getColumnName(col);
    Object cellValue = model.getValueAt(row, col);        
    Object cell_Id = model.getValueAt(row, 0);

    try {
        new ImportData(colName, cellValue, cell_Id);
        bottomLabel.setText(textForLabel());
    } catch (ClassNotFoundException e1) {
        e1.printStackTrace();
    } catch (SQLException e1) {
        e1.printStackTrace();
    }
}   

public class ImportData {    

    public ImportData(String a, Object b, Object c)
            throws ClassNotFoundException, SQLException {
        Connection connection = null;
        PreparedStatement prepStmt = null;      

        try {
            connection = ConnectionManager.getConnection();
            String colName = a;             
            String cellValue = b.toString();                
            String cell_Id = c.toString();                      

            String updateString = "update finance.fin " + "set ? = ? " + "where ID = ? "+ ";";
            prepStmt = connection.prepareStatement(updateString);
            prepStmt.setString(1, colName);
            prepStmt.setString(2, cellValue);
            prepStmt.setString(3, cell_Id);             
            prepStmt.executeUpdate();  

            System.out.println("Text test line out of ImportData() method");

        } catch (SQLException e) {
            e.printStackTrace();
        } 
        finally {
            closeAll(null, null, prepStmt, connection);
        }  
    }   
}

Answer 1

Roel De Nijs 来自http://coderanch.com回答了问题。老实说，我发现您的代码有 3 个问题。首先也是最重要的:我认为您不能在准备好的语句中执行类似的操作 "set ? = ? " 我认为列名称不能用占位符替换。尝试使用 "set Flat = ? " 并查看它是否有效。如果确实如此，您就知道导致问题的原因。另外两个是您将参数设置为 String，但它们不是字符串，但可能是 double 和 int。所以改变你的代码

prepStmt.setString(1, colName);
prepStmt.setString(2, cellValue);
prepStmt.setString(3, cell_Id);

至

//列名称对于测试不是动态的
prepStmt.setDouble(1, cellValue);
prepStmt.setInt(2, cell_Id);

看看这是否有效。您当然应该确保变量具有适当的类型。附言。不建议在变量名中使用下划线，根据命名约定，它应该是驼峰命名法。工作代码如下:

public class ImportData {    

    public ImportData(String a, Object b, Object c)
            throws ClassNotFoundException, SQLException {
        Connection connection = null;
        PreparedStatement prepStmt = null;          
        try {
            connection = ConnectionManager.getConnection();
            String colName = a;             
           // String cellValue = b.toString();
           // String cellId = c.toString();
            float cellValue = (float) b;
            int cellId = (int) c;               
            String updateString = "update finance.fin " + "set " + colName + "= ? " + "where ID = ? "+ ";";
            prepStmt = connection.prepareStatement(updateString);
            prepStmt.setFloat(1, cellValue);
            prepStmt.setInt(2, cellId);             
            prepStmt.executeUpdate();  

        } catch (SQLException e) {
            e.printStackTrace();
        } 
        finally {
            closeAll(null, null, prepStmt, connection);
        }  
    }   
}