java - 错误: ORA-00900 Invalid SQLStatement but statement still works as intended

Question

我创建了一个 GUI，可以更改和检索数据库中的数据。但是，即使任务已完成，某些按钮也会抛出 SQLException。 (例如，它会给我一个错误，但仍然正确插入到数据库中。

这是我的代码:

String sqlUser = e.getActionCommand();
String sql;
PreparedStatement stmt;
try{
    if(sqlUser.equals("Save Record"))
                {
                    ResultSet rs;
                    sql = "INSERT INTO table_name (name, ID, result, date, date2) values (?,?,?,?,?)";
                    stmt = con.prepareStatement(sql);
                    stmt.setString(1, textField.getText());
                    stmt.setInt(2, Integer.parseInt(textField_1.getText()));
                    stmt.setString(3, textField_2.getText());
                    stmt.setDate(4, new java.sql.Date(System.currentTimeMillis()));
                    stmt.setDate(5, new java.sql.Date(System.currentTimeMillis()));
                    rs = stmt.executeQuery();
                    updateUI(rs);
                    con.commit();
                }
    else if(sqlUser.equals("Save Update"))
                {
                    ResultSet rs;
                    sql = "UPDATE table_name SET result = ? , date = ?, date2 = ? WHERE name = ? AND ID = ?";
                    stmt = con.prepareStatement(sql);
                    stmt.setString(4, textField.getText());
                    stmt.setInt(5, Integer.parseInt(textField_1.getText()));
                    stmt.setString(1, textField_2.getText());
                    stmt.setDate(2, new java.sql.Date(System.currentTimeMillis()));
                    stmt.setDate(3, new java.sql.Date(System.currentTimeMillis()));
                    rs = stmt.executeQuery();
                    updateUI(rs);
                    con.commit();
                }
} catch (SQLException err)
{
    JOptionPane.showMessageDialog(null,err.getMessage());
}

updateUI方法如下:

public void updateUI(ResultSet rs)
    {
        try
        {
            String strCDate = dateFormat.format(rs.getDate("date"));
            String strLADate = dateFormat.format(rs.getDate("date2"));

            textField.setText(rs.getString("name"));
            textField_1.setText(Integer.toString(rs.getInt("ID")));
            textField_2.setText(rs.getString("result"));
            lblCalculationDate.setText("Date: " + strCDate);
            lblLastAccessDate.setText("Date2: " + strLADate);
        }
        catch(SQLException err)
        {
            JOptionPane.showMessageDialog(null,err.getMessage());
        }
    }

DateFormat 是这样的(不要认为它太重要):

private DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");

Answer 1

• 删除 con.commit();，因为数据库连接默认为自动提交。
• 只有在设置时才可以使用commit()con.setAutoCommit(false);
• 插入执行不会返回结果集，因此请删除 rs = stmt.executeQuery(); 并更改 updateUI 方法以接受两个日期参数

完整的新代码:

    String sqlUser = e.getActionCommand();
    String sql;
    PreparedStatement stmt;
    try{
        int id = Integer.parseInt(textField_1.getText());
        java.sql.Date date = new java.sql.Date(System.currentTimeMillis());
                        java.sql.Date date2 = new java.sql.Date(System.currentTimeMillis());
        if(sqlUser.equals("Save Record"))
                    {
                        ResultSet rs;

                        sql = "INSERT INTO table_name (name, ID, result, date, date2) values (?,?,?,?,?)";
                        stmt = con.prepareStatement(sql);
                        stmt.setString(1, textField.getText());
                        stmt.setInt(2, id);
                        stmt.setString(3, textField_2.getText());
                        stmt.setDate(4,date );
                        stmt.setDate(5,date2);
                        // OLD: rs = stmt.executeQuery();
                        // OLD: updateUI(rs);
                        stmt.executeUpdate();
                        updateUI(id,  textField.getText(),textField_2.getText(),date,date2 );
                        // OLD: con.commit();
                    }
        else if(sqlUser.equals("Save Update"))
                    {
                        ResultSet rs;
                        sql = "UPDATE table_name SET result = ? , date = ?, date2 = ? WHERE name = ? AND ID = ?";
                        stmt = con.prepareStatement(sql);
                        stmt.setString(4, textField.getText());
                        stmt.setInt(5, id);
                        stmt.setString(1, textField_2.getText());
                        stmt.setDate(2, date);
                        stmt.setDate(3, date2);
                        // OLD: rs = stmt.executeQuery();
                        // OLD: updateUI(rs);
                        stmt.executeUpdate();
                        updateUI(id,  textField.getText(),textField_2.getText(),date,date2 );
                        // OLD: con.commit();
                    }
    } catch (SQLException err)
    {
        JOptionPane.showMessageDialog(null,err.getMessage());
    }

The updateUI method is as follows:

    public void updateUI(int id, String name, String result, Date date, Date date2)
        {
            try
            {
                String strCDate = dateFormat.format(date);//rs.getDate("date"));
                String strLADate = dateFormat.format(date2);//rs.getDate("date2"));

                textField.setText(name);//rs.getString("name"));
                textField_1.setText(id+"");//Integer.toString(rs.getInt("ID")));
                textField_2.setText(result);//rs.getString("result"));
                lblCalculationDate.setText("Date: " + strCDate);
                lblLastAccessDate.setText("Date2: " + strLADate);
            }
            catch(SQLException err)
            {
                JOptionPane.showMessageDialog(null,err.getMessage());
            }
        }