java.sql.SQLException : Can not issue executeUpdate() or executeLargeUpdate() for SELECTs

Question

我正在尝试注册页面，如果电子邮件已经退出，它应该收到警报消息，因为下面是我的代码的一部分，我正在使用 executeQuery 进行选择查询，但仍然收到错误:

java.sql.SQLException: Can not issue executeUpdate() or executeLargeUpdate() for SELECTs

java代码:

Class.forName("com.mysql.jdbc.Driver");
            Connection cn=DriverManager.getConnection("jdbc:mysql://localhost:3306/xxxx", "root","root");
            PreparedStatement ps=cn.prepareStatement("select * from Register where email=?");
            ps.setString(1, email);
            ResultSet rs=ps.executeQuery();

            if(rs.next())
            {
                out.println("<script type=\"text/javascript\">");
                   out.println("alert('Email already Exists Please Try with New Email');");
                   out.println("location='index.html';");
                   out.println("</script>");
            }
            else{

                   PreparedStatement ps1 = cn.prepareStatement("insert into Register values(?,?,?,?,?)");
                                ps1.setString(1, name);
                                ps1.setString(2, email);
                                ps1.setString(3, mobile);
                                ps1.setString(4, password);
                                ps1.setString(5, conform_password);

                                int i = ps.executeUpdate();
                                if (i != 0) {
                                    response.sendRedirect("index.html");
                                } else {
                                    out.println("Some Thing went wrong. Try Again...");
                                }

            }
        }

Answer 1

我的猜测是，问题与您在尝试为插入创建另一个语句之前没有关闭用于选择的第一个语句有关。但是，有一种更好的方法来实现您的逻辑，使用单个插入:

String sql = "INSERT INTO Register (name, email, mobile, password, confirm_password) ";
sql += "SELECT ?, ?, ?, ?, ? ";
sql += "WHERE NOT EXISTS (SELECT 1 FROM Register WHERE email = ?)";

PreparedStatement ps = cn.prepareStatement(sql);
ps.setString(1, name);
ps.setString(2, email);
ps.setString(3, mobile);
ps.setString(4, password);
ps.setString(5, conform_password);
ps.setString(6, email);

int i = ps.executeUpdate();
if (i == 0) {
    System.out.println("Email already Exists Please Try with New Email");
}
else {
    response.sendRedirect("index.html");
}

如果上述插入的exists子句失败，则不应插入任何内容，并且executeUpdate()返回的DML行计数应为零。