带有PreparedStatement executeUpdate的JDBC,MySQL-DML错误

Question

int ownerUpdateCount = 0;
    PreparedStatement statement = null;
    Connection connection = null;
    try{
        while(featuresIT.hasNext()){
            String feature = (String) featuresIT.next();

            String updateOwnerQuery = "UPDATE `regression_reports` "
                    + "JOIN `feature_testbed_owner_mapping_628` ON (`regression_reports`.Feature='"
                    + feature
                    + "' AND `regression_reports`.Feature = `feature_testbed_owner_mapping_628`.feature_as_on_webpage) "
                    + "SET `regression_reports`.Owner = `feature_testbed_owner_mapping_628`.owner";
            //logger.debug("\n updateOwnerQuery : " + updateOwnerQuery);
            connection = dataSource.getConnection();
            connection.setAutoCommit(true);
            statement = connection.prepareStatement(updateOwnerQuery);
            //ownerUpdateCount += 
            ownerUpdateCount = statement.executeUpdate();
            System.out.println(feature + " ownerUpdateCount : " + ownerUpdateCount);
        }
        logger.debug("\n ownerUpdateCount : " + ownerUpdateCount);
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
           try { if(null!=statement)statement.close();} catch (SQLException e) 
           {e.printStackTrace();}
           try { if(null!=connection)connection.close();} catch (SQLException e) 
           {e.printStackTrace();}
    }

SQLException：不能使用executeQuery（）发出数据操作语句。在com.mysql.jdbc.sqlerror.createsQlException（sqlerror.java:1078)在com.mysql.jdbc.sqlerror.createsQlException（sqlerror.java:989)在com.mysql.jdbc.sqlerror.createsQlException（sqlerror.java:975)在com.mysql.jdbc.sqlerror.createsQlException（sqlerror.java:920)在.tomcat.dbcp.dbcp.delegatingStatement.executeQuery（delegatingStatement.java:208)在org.apache.tomcat.dbcp.dbcp.delegatingStatement.executeQuery（delegatingStatement.java:208)在com.n7k.regarsion.regarsionDao.updateRegreportSDB（regarsionDao.java:71)在com.n7k.regarsion.regarsionServlet.doPost（

此错误仅在循环的第一次迭代中发生。任何调试其原因的建议。

String updateFeaturesQuery = "INSERT INTO `regression_reports` "
                    + "(Feature, `Report`, `P`, `F`, `Remarks`) VALUES ('"
                    + feature + "','" + report + "'," + pass + "," + fail
                    + ") " + "ON DUPLICATE KEY UPDATE `Report` = '"
                    + report + "', `P` = " + pass + ", `F`= " + fail
                    + ", `Remarks` = " + remarks + ";";

       connection = dataSource.getConnection();
       statement = connection.prepareStatement(updateFeaturesQuery);

       Line#73 
       featureUpdateCount = statement.executeUpdate(updateFeaturesQuery);

错误：

上面的执行给出了这个错误。手动执行相同的查询不会给我带来任何错误。

Answer 1

此处完全缺少备注列，

// No `Remarks` even though you included it in the list. `F`, `Remarks`)
 "," + fail /* + Remarks! */ + ") " + "ON DUPLICATE KEY UPDATE `Report` = '"

您的列备注在这里没有转义，

+ ", `Remarks` = '" + remarks + "';";

但是您应该使用绑定参数或字符串stringescapeutils#escapesql（String）,因为您的代码容易受到sql注入的攻击。