无法通过 Java 更新 SQL

Question

我试图使CRUD(创建，读取，更新，删除)到我的项目。但似乎"更新"了。是行不通的。它一直在说

java.sql.SQLSyntaxErrorException : You have an error in your SQL syntax; check the manual that coresponds to your MariaDB server version for the right syntax to use near "Number" = 0813874810 WHERE Name = "Gregory" at line 1)

这个问题的解决方案是什么?

下面是我的代码:

conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/employeedata", "root", "");
String sql = "UPDATE employeetab SET Name = '" + txtEmployeeName.getText()                       
+ "',Address = '" + txtEmployeeAddress.getText()
+ "',Gender = '" + gender_type
+ "',Phone Number = '" + txtEmployeePhone.getText()
+ "' WHERE Name = '" + txtEmployeeName.getText() + "'";
stm = conn.prepareStatement(sql);
stm.execute(sql);
JOptionPane.showMessageDialog(this, "Update successfully");
this.setVisible(false);

Answer 1

问题来自Phone Number列中的空格。要使它工作，您需要用'转义列名.

UPDATE employeetab 
SET Name = 'something',Address = 'some address',Gender = 'whatever',`Phone Number` = '000000000'
WHERE Name = 'something';

您应该遵循sql命名约定，通常列名中的单词由_分隔。您的列名应该是-phone_number。

另外，正如在评论中提到的，你不应该只在sql查询中添加用户输入，因为你会给sql注入留下很大的空间。

Answer 2

您需要遵循命名约定，它们是'电话号码'列之间的空格，您不应该这样写，您需要在这两者之间添加_。

Answer 3

try this:

String gender_type = null;
if (ButtonM.isSelected()){
gender_type = "Male";
}else if(ButtonFM.isSelected()){
gender_type = "Female";
}
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/employeedata","root","");
String sql = "UPDATE employeetab SET Name = ? ," +
"  Address = ? ," +
" Gender = ? ," +
" Phone Number = ? ," +
" WHERE Name = ? ," ;
PreparedStatement pStmt = conn.prepareCall(sql);
pStmt.setString(1, txtEmployeeName.getText()+"");
pStmt.setString(2, txtEmployeeAddress.getText()+"");
pStmt.setString(3, gender_type+"");
pStmt.setString(4, txtEmployeePhone.getText()+"");
pStmt.setString(5, txtEmployeeName.getText());
pStmt.executeUpdate();
JOptionPane.showMessageDialog(this, "Update successfully");
this.setVisible(false);

}catch (Exception e){
JOptionPane.showMessageDialog(null, e);
}

更干净，应该能工作。