英文:
java.sql.SQLSyntaxErrorException when inserting into MySQL database using PreparedStatement
问题
我正在使用JavaFX创建一个连接到MySQL数据库的应用程序，我希望将用户的输入保存起来，所以我正在使用PreparedStatement。但是我一直在遇到下面显示的异常，我不知道如何修复它和我在做什么错误。
我正在使用FXML，所以下面是场景的控制器类。
import ... //（你的导入语句）
public class AddVolunteerSceneController implements Initializable {
// ...（你的其他代码）
@FXML public TextField txtForename;
@FXML public TextField txtSurname;
@FXML public TextField txtSchool;
@FXML public TextField txtEmail;
@FXML public TextField txtPhone;
@FXML public ComboBox<Department> comboboxDept;
@FXML public DatePicker datepickJoin;
@FXML public ChoiceBox<String> choiceboxSkill;
@FXML public Button btnSave;
@FXML public TextField txtVolID;
@FXML public Button btnCancel;
// ...（你的其他代码）
public void initialize(URL location, ResourceBundle resources) {
try {
Connection conn = driver.connector();
} catch (SQLException e) {
e.printStackTrace();
}
choiceboxSkill.setItems(skillList);
comboboxDept.setItems(deptList);
}
public void saveButton(ActionEvent event) throws SQLException {
Department department = comboboxDept.getSelectionModel().getSelectedItem();
System.out.println(department.getID());
String query = "INSERT INTO volunteers (forename,surname,school,phone,email,joinDate,skill,deptID) VALUES (?,?,?,?,?,?,?,?)";
PreparedStatement pst = driver.connector().prepareStatement(query);
try {
pst.setString(1, txtForename.getText());
pst.setString(2, txtSurname.getText());
pst.setString(3, txtSchool.getText());
pst.setString(4, txtPhone.getText());
pst.setString(5, txtEmail.getText());
pst.setDate(6, java.sql.Date.valueOf(datepickJoin.getValue()));
pst.setString(7, choiceboxSkill.getValue());
pst.setInt(8, department.getID());
pst.executeUpdate(); // 不需要在这里传递查询字符串
AlertBox.display("Alert", "Data Saved Successfully");
} catch (SQLException E) {
E.printStackTrace();
AlertBox.display("Alert", "Error in saving data.");
}
}
// ...（你的其他代码）
}
你提供的代码有一些HTML转义字符，我已经去除了它们，确保这段翻译后的代码能够正常使用。至于异常，它表明你的SQL语法存在问题，请检查你的查询语句，确保语法正确。同时，在执行pst.executeUpdate()时，不需要再传递查询字符串。
英文:
I'm making an application on javafx connected to a MySQL database in which I want the user's input to be saved, so I'm using a PreparedStatement. I keep getting this exception, as shown below, and I don't know how to fix it and what I'm doing wrong.
I'm using FXML, so below is the controller class for the scene.
Please help me out!
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.ResourceBundle;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.event.ActionEvent;
import javafx.fxml.FXML;
import javafx.fxml.FXMLLoader;
import javafx.fxml.Initializable;
import javafx.scene.Node;
import javafx.scene.Parent;
import javafx.scene.Scene;
import javafx.scene.control.*;
import javafx.stage.Stage;
public class AddVolunteerSceneController implements Initializable {
ObservableList<String> skillList = FXCollections.observableArrayList("Communication", "Writing",
"Graphic Designing", "Social Media Marketing","Analytical Thinking",
"Leadership","Teamwork","Teaching","Resource Management");
ObservableList<Department> deptList = FXCollections.observableArrayList (
new Department(1, "Data Analysis"),
new Department(2,"Art & Media"),
new Department(3, "Fundraising"),
new Department(4,"School Resources"),
new Department(5,"Social Media"),
new Department(6,"Technology"),
new Department(7,"Newsletter & Journalism"));
@FXML public TextField txtForename;
@FXML public TextField txtSurname;
@FXML public TextField txtSchool;
@FXML public TextField txtEmail;
@FXML public TextField txtPhone;
@FXML public ComboBox<Department> comboboxDept;
@FXML public DatePicker datepickJoin;
@FXML public ChoiceBox<String> choiceboxSkill;
@FXML public Button btnSave;
@FXML public TextField txtVolID;
@FXML public Button btnCancel;
@Override
public void initialize(URL location, ResourceBundle resources) {
try {
Connection conn = driver.connector();
} catch (SQLException e) {
e.printStackTrace();
}
choiceboxSkill.setItems(skillList);
comboboxDept.setItems(deptList);
}
public void saveButton (ActionEvent event) throws SQLException {
Department department = comboboxDept.getSelectionModel().getSelectedItem();
System.out.println(department.getID());
String query = "INSERT INTO volunteers (forename,surname,school,phone,email,joinDate,skill,deptID) VALUES (?,?,?,?,?,?,?,?)";
PreparedStatement pst = driver.connector().prepareStatement(query);
try {
pst.setString(1, txtForename.getText());
pst.setString(2, txtSurname.getText());
pst.setString(3, txtSchool.getText());
pst.setString(4, txtPhone.getText());
pst.setString(5, txtEmail.getText());
pst.setDate(6, java.sql.Date.valueOf(datepickJoin.getValue()));
pst.setString(7, choiceboxSkill.getValue());
pst.setInt(8, department.getID());
//Statement stmt = driver.connector().createStatement();
pst.executeUpdate(query);
AlertBox.display("Alert", "Data Saved Successfully");
}
catch(SQLException E) {
E.printStackTrace();
AlertBox.display("Alert", "Error in saving data.");
}
}
}
Department Class
public class Department {
public int id;
public String deptName;
public Department (int id, String deptName) {
super();
this.id = id;
this.deptName = deptName;
}
public int getID() {
return id;
}
public void setID(int id) {
this.id = id;
}
public String getDeptName() {
return deptName;
}
public void setDeptName(String deptName) {
this.deptName = deptName;
}
@Override
public String toString() {
return this.getDeptName();
}
}
The exception:
java.sql.SQLSyntaxErrorException: 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 '?,?,?,?,?,?,?,?)' at line 1
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1335)
at com.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2108)
at com.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1245)
at NDvolunteersDB.addVolunteerSceneController.saveButton(addVolunteerSceneController.java:194)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at sun.reflect.misc.Trampoline.invoke(MethodUtil.java:71)
at sun.reflect.GeneratedMethodAccessor1.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at sun.reflect.misc.MethodUtil.invoke(MethodUtil.java:275)
at javafx.fxml.FXMLLoader$MethodHandler.invoke(FXMLLoader.java:1769)
at javafx.fxml.FXMLLoader$ControllerMethodEventHandler.handle(FXMLLoader.java:1657)
at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:86)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
at com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:49)
at javafx.event.Event.fireEvent(Event.java:198)
at javafx.scene.Node.fireEvent(Node.java:8411)
at javafx.scene.control.Button.fire(Button.java:185)
at com.sun.javafx.scene.control.behavior.ButtonBehavior.mouseReleased(ButtonBehavior.java:182)
at com.sun.javafx.scene.control.skin.BehaviorSkinBase$1.handle(BehaviorSkinBase.java:96)
at com.sun.javafx.scene.control.skin.BehaviorSkinBase$1.handle(BehaviorSkinBase.java:89)
at com.sun.javafx.event.CompositeEventHandler$NormalEventHandlerRecord.handleBubblingEvent(CompositeEventHandler.java:218)
at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:80)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
at com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:54)
at javafx.event.Event.fireEvent(Event.java:198)
at javafx.scene.Scene$MouseHandler.process(Scene.java:3757)
at javafx.scene.Scene$MouseHandler.access$1500(Scene.java:3485)
at javafx.scene.Scene.impl_processMouseEvent(Scene.java:1762)
at javafx.scene.Scene$ScenePeerListener.mouseEvent(Scene.java:2494)
at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:394)
at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:295)
at java.security.AccessController.doPrivileged(Native Method)
at com.sun.javafx.tk.quantum.GlassViewEventHandler.lambda$handleMouseEvent$2(GlassViewEventHandler.java:432)
at com.sun.javafx.tk.quantum.QuantumToolkit.runWithoutRenderLock(QuantumToolkit.java:410)
at com.sun.javafx.tk.quantum.GlassViewEventHandler.handleMouseEvent(GlassViewEventHandler.java:431)
at com.sun.glass.ui.View.handleMouseEvent(View.java:555)
at com.sun.glass.ui.View.notifyMouse(View.java:937)
</details>
专注分享java语言的经验与见解，让所有开发者获益！
评论