java - Java中的PreparedStatement插入

Question

这个问题已经有答案了:

Syntax error due to using a reserved word as a table or column name in MySQL (1 个回答)

已关闭 9 年前。

我编写了一段代码，如果按下按钮，则应采用文本字段中的值在数据库中创建记录。代码可以编译，但是当我运行它时，我收到此错误消息:

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 'From, To, TotalDays, VacationType, Notes, Signature, Date) VALUES('','','','',''' at line 1

有什么建议吗？

    final JButton btnSubmit = new JButton("Submit");
    btnSubmit.addActionListener(new ActionListener() {
        public void actionPerformed(ActionEvent arg0) {                 
        try {

            String vacationid = text_vacationID.getText();
            String staffid = text_staffID.getText();
            String from = text_from.getText();
            String to = text_to.getText();
            String totaldays = text_totalDays.getText();
            String vacationtype = text_vacationType.getText();
            String notes = textArea.getText();
            String signature = text_signature.getText();
            String date = text_date.getText();


            String sql = "INSERT into vacation (VacationID, StaffID, From, To, TotalDays, VacationType, Notes, Signature, Date) VALUES" + "(?,?,?,?,?,?,?,?,?)";    

            PreparedStatement prest = con.prepareStatement(sql);
            prest.setString(1, vacationid);
            prest.setString(2, staffid);
            prest.setString(3, from);
            prest.setString(4, to);
            prest.setString(5, totaldays);
            prest.setString(6, vacationtype);
            prest.setString(7, notes);
            prest.setString(8, signature);
            prest.setString(9, date);

            prest.executeUpdate();
            JOptionPane.showMessageDialog(frmBookVacation, "Vacation has been booked for Employee with ID: " + vacationid);


        } 

        catch (SQLException e) {
        //System.out.println("Record couldn't be added!");
        e.printStackTrace();
        JOptionPane.showMessageDialog(frmBookVacation, "Vacation couldn't be booked. Please try again.");
        }
        }

        });
    btnSubmit.setBounds(201, 350, 89, 23);
    panel_1.add(btnSubmit);

Answer 1

from是SQL中的保留字，需要escape it with backticks (如果启用了 ANSI 模式，则加引号)。

String sql = "INSERT into vacation (`VacationID`, `StaffID`, `From`, `To`, `TotalDays`, `VacationType`, `Notes`, `Signature`, `Date`) VALUES" + "(?,?,?,?,?,?,?,?,?)";

为了保持一致性，我已经在那里完成了所有列名称，并且因为其他一些列名称可能被某些引擎保留(或不保留)。