Java mysql 查询不工作

Question

我对这些代码行感到困惑和沮丧:

 String updateSQL="UPDATE " + tableName + " set " + secondColumn + "='"+ value2 + "',"+ thirdColumn + "='"+ value3 +"'" + "," + fourthColumn +"='"+ value4 +"'" + "where " + firstColumn + " = "+ checkvalue ;

我让这个查询完美地工作!虽然我有另一个查询运行不完美，但风格相同:

  String updateSQL="UPDATE " + tableName + " set "  + secondColumn + "='"+ value2 +"'" + ","+ thirdColumn + "='"+ value3 +"'" + "," + fourthColumn +"='"+ value4 +"'" + "," + fifthColumn +"='"+ value5 +"'" + "where " + firstColumn + " = "+ checkvalue ;

当尝试更新时运行这行代码时，我收到错误消息“Where 子句中‘PNO3’中的未知列”

虽然我知道我在第一列中有 PNO3，但是当对第一个查询执行相同操作时它会找到它吗？有什么帮助吗？

我使用此 switch 语句来确保列指向正确的列。

  switch (tableName) {            
             case "s":  
                 firstColumn = "ID";
              secondColumn = "Namn";
              thirdColumn = "Efternamn";
              fourthColumn = "Adress";
                 break;
             case "p":
                  firstColumn = "PNO";
              secondColumn = "PNAME";
              thirdColumn = "COLOR";
              fourthColumn = "WEIGHT";
              fifthColumn = "CITY";
                 break;
             case "j":
                   firstColumn = "JNO";
              secondColumn = "JNAME";
              thirdColumn = "CITY";           
                 break;
             case "spj":
                  firstColumn = "SNO";
              secondColumn = "PNO";
              thirdColumn = "JNO";
              fourthColumn = "QTY";
                 break;

         }

Answer 1

您的第二个查询在 checkvalue 周围缺少单引号，否则它将被视为列名而不是值(假设 firstColumn 是 varchar)。应该是:

String updateSQL="UPDATE " + tableName + " set "  + secondColumn + "='"+ value2 +"'" + ","+ thirdColumn + "='"+ value3 +"'" + "," + fourthColumn +"='"+ value4 +"'" + "," + fifthColumn +"='"+ value5 +"'" + "where " + firstColumn + " = '"+ checkvalue + "'";

注意:您应该真正使用PreparedStatement 而不是字符串连接。这不仅仅是可读性问题，而且是安全问题 - 您的代码容易受到 SQL 注入(inject)