在循环中自行添加字符串时SQL查询中的错误

Z时代
2024-01-10
分类：问答

我在Servlet中循环查询时遇到了超时错误，因此我决定创建一个只执行一次的长查询。在循环中自行添加字符串时SQL查询中的错误

下面是代码：

public void deactivateAccount(String[] userList) { 
    DBConnectionFactory myFactory = DBConnectionFactory.getInstance(); 
    Connection con = myFactory.getConnection(); 
    try { 
     con.setAutoCommit(false); 
     String combinedQueries = ""; 
     for (int i =0; i<userList.length;i++) { 
      String query = "UPDATE users SET active='0' WHERE userID = ? ; "; 
      combinedQueries += query; 
     } 
     System.out.println(combinedQueries); 
     PreparedStatement p = con.prepareStatement(combinedQueries); 
     for (int i =0; i<userList.length;i++) { 
      int currentNum = i+1; 
      p.setInt(currentNum, Integer.parseInt(userList[i])); 
      System.out.println("current num is " + currentNum); 
      System.out.println("userlist value is " + Integer.parseInt(userList[i])); 
     } 
      p.execute(); 
    } catch (SQLException ex) { 
     try { 
      con.rollback(); 
     } catch (SQLException ex1) { 
      Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex1); 
     } 
     Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex); 
    } 
}

此代码是在我的DAO - 它的作用是循环需要字符串多次（有多少复选框用户检查），然后准备在声明另一个循环（设置准备语句的参数），然后执行它。

我得到错误：

Severe: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'UPDATE users SET active='0' WHERE userID = 1; UPDATE users SET active='0' WHERE ' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

但是当我在执行前打印查询中的代码，这是出现什么：

Info: UPDATE users SET active='0' WHERE userID = ?; UPDATE users SET active='0' WHERE userID = ?; UPDATE users SET active='0' WHERE userID = ?; UPDATE users SET active='0' WHERE userID = ?; (assuming 4 checkboxes were clicked)

该方法接受的阵列，我从一个请求获得.getParameterValues来自servlet。

预先感谢您的帮助！

回答：

这是不是为批量更新运行PreparedStatement单独（由于性能原因），最好的做法，相反，你需要使用executeBatch()，如下图所示：

public void deactivateAccount(String[] userList) { 
    DBConnectionFactory myFactory = DBConnectionFactory.getInstance(); 
    Connection con = myFactory.getConnection(); 
    PreparedStatement p = null; 
    try { 
     con.setAutoCommit(false); 
     String query = "UPDATE users SET active='0' WHERE userID = ?"; 
     p = con.prepareStatement(query); 
     for (int i =0; i<userList.length;i++) { 
      p.setInt(1, Integer.parseInt(userList[i])); 
      p.addBatch(); 
     } 
     int[] affectedRecords = p.executeBatch(); 
     con.commit(); 
     //you can validate rows before commit, upto your requirement 
    } catch (SQLException ex) { 
     try { 
      con.rollback(); 
     } catch (SQLException ex1) { 
      Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex1); 
     } 
     Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex); 
    } finally { 
      if(p != null) 
       p.close(); 
      if(con != null) 
       con.close(); 
    } 
}