mySQL嵌套循环不会执行

发布于 2024-12-01 13:37:53 字数 935 浏览 0 评论 0原文

我正在开发一个基于 SOAP 的 Web 服务，其中一部分我必须使用嵌套循环对数据库执行一些查询，问题是内部循环在放弃之前只执行一次。这是代码：

          for(int i=0; i<selec.length; i++){
                for(int j=0; j<sintom.length;j++){
                    var[(i*sintom.length)+j] = "INSERT INTO malattia (nome, eta,  descrizione, sesso, etnia, sintomi) "
                + "VALUES ('" + malattia + "','" + eta + "','" + descrizione + "','" +  sexarra[0] + "','" + selec[i] + "','" + sintom[j] + "')";
        }

      }

这是应该执行查询的地方：

        if (errore.equals("")) {
              try {
                    Statement st = conn.createStatement();
      for(int i=0; i<selec.length; i++){
        for(int j=0;j<sintom.length;j++){

                     st.executeUpdate(var[(i*sintom.length)+j]);}}

发生的情况是，无论 select 的大小如何，只要 sintom 的长度为 1，它都会正常工作，大于 1 就不起作用。

感谢您的专家建议，始终不胜感激！

原文

I'm working on a SOAP based webservice where in a part of it i have to perform some queries on the database using nested loop, the problem is that the inner loop just gets executed for ONE time only, before giving up.This is the code:

          for(int i=0; i<selec.length; i++){
                for(int j=0; j<sintom.length;j++){
                    var[(i*sintom.length)+j] = "INSERT INTO malattia (nome, eta,  descrizione, sesso, etnia, sintomi) "
                + "VALUES ('" + malattia + "','" + eta + "','" + descrizione + "','" +  sexarra[0] + "','" + selec[i] + "','" + sintom[j] + "')";
        }

      }

This is where the queries are supposed to get executed:

        if (errore.equals("")) {
              try {
                    Statement st = conn.createStatement();
      for(int i=0; i<selec.length; i++){
        for(int j=0;j<sintom.length;j++){

                     st.executeUpdate(var[(i*sintom.length)+j]);}}

What happens is that no matter the size of select it will work fine as long as the length of sintom is 1,bigger than 1 and it wont work.

Thanks for your expert advices, always appreciated!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

深居我梦 2024-12-08 13:37:53

您的用例是应该使用准备好的语句的完美示例。在JDBC 教程中了解有关它们的更多信息。

使用准备好的语句可以避免

SQL 注入攻击。您永远不应该使用字符串连接来构建 SQL 查询。恶意用户可能会输入一些特殊值，这将完全改变您的查询的含义。非恶意用户可以输入特殊字符（例如引号），这会导致查询失败，因为它在语法上不正确。
让数据库为您正在执行的所有插入查询只准备一次执行计划。事实上，查询总是相同的。仅参数发生变化。

所以，代码应该是这样的：

PreparedStatement ps = conn.prepareStatement("INSERT INTO malattia (nome, eta, ...) values (?, ?, ...)");
for (int i= 0; ...) {
    for (int j = 0; ...) {
        ps.setString(1, malattia);
        ps.setString(2, eta);
        ...
        ps.executeUpdate();
    }
}

Your use-case is a perfect example of a case where a prepared statement should be used. Read more about them in the JDBC tutorial.

Using a prepared statement would allow

avoiding SQL injection attacks. You should never use string concatenation to build your SQL query. A malicious user could enter some special value which would completely change the meaning of your query. A non-malicious user could enter special characters (quotes, for example) which would make the query fail because it's not syntaxically correct.
letting the database prepare the execution plan only once, for all the insert queries you're executing. Indeed, the query is always the same. Only the parameters change.

So, the code should look like this:

PreparedStatement ps = conn.prepareStatement("INSERT INTO malattia (nome, eta, ...) values (?, ?, ...)");
for (int i= 0; ...) {
    for (int j = 0; ...) {
        ps.setString(1, malattia);
        ps.setString(2, eta);
        ...
        ps.executeUpdate();
    }
}

回复收藏 0 原文