INSERT语句的未知SQL问题

Question

基本上我试图用getSelectRow的值更新数据库表。正如您所看到的，查询找到了正确的数据，但是在实际尝试将其添加到数据库中时存在巨大的问题。
错误是在SQL语法，但我不知道我在哪里出错。请帮助。

这是它执行的查询，但我不知道为什么它不更新表。

INSERT INTO customerdetails 
      FName        = 'Tim'
  AND SName        = 'Cooley'
  AND Address      = '52     Buckminster Drive Dorridge Solihull West Mids'
  AND Postcode     = 'B93 8PG'

Java代码:

private void sendBtnMouseClicked(java.awt.event.MouseEvent evt) {                                     
    // TODO add your handling code here:
    int insertRow = newOrderTbl.getSelectedRow();
    int col2 = 0;
    String sql3 = "INSERT INTO customerdetails VALUES "
            + "FName            = '" + newOrderTbl.getValueAt(insertRow, col2)     +"'"
            + "AND SName        = '" + newOrderTbl.getValueAt(insertRow, col2+1)   +"'"
            + "AND Address      = '" + newOrderTbl.getValueAt(insertRow, col2+2)   +"'"
            + "AND Postcode     = '" + newOrderTbl.getValueAt(insertRow, col2+3)   +"'";
    System.out.println(sql3); 
    try{
        pst = conn.prepareStatement(sql3);
        pst.executeUpdate(sql3);
        JOptionPane.showMessageDialog(null, "Deleted");   

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

}   

Answer 1

首先，您的SQL语法是错误的(至少它是针对您的数据库引擎的非标准SQL语法)。其次，你的代码很容易受到SQL注入攻击。

为了解决这两个问题，您应该使用PreparedStatement(您的做法是错误的)。代码中的一个基本示例:

String sql = "INSERT INTO customerdetails (FName, SName, Address, Postcode) VALUES (?, ?, ?,?)";
PreparedStatement pst = conn.prepareStatemtnt(sql);
pst.setString(1, newOrderTbl.getValueAt(insertRow, col2));
pst.setString(2, newOrderTbl.getValueAt(insertRow, col2+1));
pst.setString(3, newOrderTbl.getValueAt(insertRow, col2+2));
pst.setString(4, newOrderTbl.getValueAt(insertRow, col2+3));
pst.executeUpdate();
//rest of code...

假设您的SQL语法可以正常工作，那么您应该将值作为参数传递，类似于前面的示例:

String sql3 = "INSERT INTO customerdetails VALUES "
        + "FName            = ?"
        + "AND SName        = ?"
        + "AND Address      = ?"
        + "AND Postcode     = ?"
pst = conn.prepareStatement(sql3);
pst.setString(1, newOrderTbl.getValueAt(insertRow, col2));
pst.setString(2, newOrderTbl.getValueAt(insertRow, col2+1));
pst.setString(3, newOrderTbl.getValueAt(insertRow, col2+2));
pst.setString(4, newOrderTbl.getValueAt(insertRow, col2+3));
pst.executeUpdate();
//rest of code...

Answer 2

对于update语句，它将是-

String sql3 = "INSERT INTO customerdetails(FName,SName,Address,Postcode) VALUES "
            + " '" + newOrderTbl.getValueAt(insertRow, col2)     +"',"
            + " '" + newOrderTbl.getValueAt(insertRow, col2+1)   +"',"
            + " '" + newOrderTbl.getValueAt(insertRow, col2+2)   +"',"
            + " '" + newOrderTbl.getValueAt(insertRow, col2+3) + "')";

还应该使用PreparedStatement。

谢谢

Answer 3

请改为

String sql3 = "INSERT INTO customerdetails(FName,SName,Address,Postcode) VALUES ("
            + "'" + newOrderTbl.getValueAt(insertRow, col2)     +"'"
            + "'" + newOrderTbl.getValueAt(insertRow, col2+1)   +"'"
            + "'" + newOrderTbl.getValueAt(insertRow, col2+2)   +"'"
            + "'" + newOrderTbl.getValueAt(insertRow, col2+3)   +"')";

代码中生成的插入语句似乎无效。有关更多信息，请参阅SQL插入语句

另外，更好的方法是创建一个专用的服务器端DAO类来处理数据库操作。