java - JDBC (Derby) INSERT 查询因 SQLSyntaxErrorException 而失败

Question

我的 INSERT 查询一直失败并出现 SQLSyntaxErrorException。我试图检查我的表的结构以查看是否存在错误的数据类型，但我一直收到此消息。请帮助我确定导致此错误的原因。我卡住了。我不确定我对组件的引用是否正确。

下面是我的 .grab 文件详细信息

        String completeBirthday = getCompleteBirthdayFromComboBoxes(); 
        String employmentStatus = getStringValue_Of_EmploymentStatus_Selected();

        Connection con = DriverManager.getConnection(url, username, password);
        Statement stat = con.createStatement();

        String InsertQuery = "INSERT INTO EMPLOYEE(EMPLOYEE_ID,EMP_LASTNAME,EMP_FIRSTNAME,EMP_MIDDLENAME,ADDRESS,POSITION,GENDER,BIRTHMONTH,BIRTHDAY,BIRTHYEAR,EMAIL,YEARSOFEXPERIENCE,BASICSALARY,EMPLOYMENTSTATUS)"
                + "VALUES "
                + "(' "+AddEmployee_EmployeeID_TxtField.getText()+" ',' "
                + " "+AddEmployee_LastName_TxtField.getText()+" ',' "
                + " "+AddEmployee_FirstName_TxtField.getText()+" ',' "
                + " "+AddEmployee_MiddleName_TxtField.getText()+" ',' "
                + " "+AddEmployee_Address_TxtField.getText()+" ',' "
                + " "+AddEmployee_Position_TxtField.getText()+" ',' "
                + " "+AddEmployee_Gender_ComboBox.getSelectedItem()+" ',' "
                + " "+AddEmployee_BirthdayMonth_ComboBox.getSelectedItem()+" ',' "
                + " "+AddEmployee_BirthdayDay_ComboBox.getSelectedItem()+" ',' "
                + " "+AddEmployee_BirthdayYear_ComboBox.getSelectedItem()+" ',' "
                + " "+AddEmployee_Email_TxtField.getText()+" ',' "
                + " "+AddEmployee_YearsOfExperience_TxtField.getText()+" ',' "
                + " "+AddEmployee_BasicSalary_TxtField.getText()+" ',' "
                + " "+employmentStatus+" "
                + " ')"
                ;


        stat.execute(InsertQuery); //execute() will run the insert query above.
        JOptionPane.showMessageDialog(null,"Insert Success!"); 

如果有任何帮助，我将不胜感激。提前致谢。

Answer 1

正如问题评论中提到的，您确实应该使用 PreparedStatement 来执行参数化查询，如下所示:

String insertQuery = "INSERT INTO EMPLOYEE(EMPLOYEE_ID,EMP_LASTNAME,EMP_FIRSTNAME,EMP_MIDDLENAME,ADDRESS,POSITION,GENDER,BIRTHMONTH,BIRTHDAY,BIRTHYEAR,EMAIL,YEARSOFEXPERIENCE,BASICSALARY,EMPLOYMENTSTATUS)"
        + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
PreparedStatement stat = con.prepareStatement(insertQuery);
stat.setString(1, AddEmployee_EmployeeID_TxtField.getText());
stat.setString(2, AddEmployee_LastName_TxtField.getText());
stat.setString(3, AddEmployee_FirstName_TxtField.getText());
stat.setString(4, AddEmployee_MiddleName_TxtField.getText());
stat.setString(5, AddEmployee_Address_TxtField.getText());
stat.setString(6, AddEmployee_Position_TxtField.getText());
stat.setString(7, AddEmployee_Gender_ComboBox.getSelectedItem());
stat.setString(8, AddEmployee_BirthdayMonth_ComboBox.getSelectedItem());
stat.setString(9, AddEmployee_BirthdayDay_ComboBox.getSelectedItem());
stat.setString(10, AddEmployee_BirthdayYear_ComboBox.getSelectedItem());
stat.setString(11, AddEmployee_Email_TxtField.getText());
stat.setInt(12, Integer.valueOf(AddEmployee_YearsOfExperience_TxtField.getText()));
stat.setInt(13, Integer.valueOf(AddEmployee_BasicSalary_TxtField.getText()));
stat.setString(14, employmentStatus);
stat.executeUpdate();

这种方法避免了担心引用字符串值带来的不便，并防止了 SQL 注入(inject)问题(例如，当 LastName 是“O'Brian”时，您的代码不会崩溃)