ResultSet#next()返回false，即使我的查询是正确的

Question

我在一个jsp文件中编写了以下代码。基本上，它的作用是检查是否有任何特定的银行名称，卡号和密码的数据，为了检查这个，我使用了一个select查询。查询是正确的，但结果集返回false..我甚至单独检查了查询。它工作得很好…这个代码有什么问题吗?请帮助. .我使用netbeans作为我的IDE和Derby数据库..

    String URL="jdbc:derby://localhost:1527/Bank Database";
    try{
        Connection cn=DriverManager.getConnection(URL,"Bank","Bank");
        String qry="SELECT * FROM DATABASE WHERE BANK='"+bname+"' AND CARD_NO='"+cname+"' AND PASWORD='"+pswrd+"'";
        Statement sq=cn.createStatement();
        ResultSet rs=sq.executeQuery(qry);
        if(rs.next()){
            if(rs.getString("PASWORD").equals(pswrd))
            {
                cn.close();
                out.println("<script>function redirect(){ window.location="scsfl.html";} setTimeout('redirect()',3000);</script>");  //redirects to another page   
            }
        }
        else{
            cn.close();
            out.println("<script>function redirect(){ window.location="failed.html";} setTimeout('redirect()',3000);</script>");
        }
    }catch(Exception e){ System.out.print(e);}

Answer 1

正如在注释中提到的，您的实际代码显示了许多问题:

1. 您正在使用Statement而不是PreparedStatement，这更安全，可以防止一些黑客像SQL注入一样移动，您可以在JDBC语句与PreparedStatement - SQL注入示例中找到更多信息。
2. 你正在检查你的Java代码中的密码，而它已经在你的SQL查询中完成，所以查询检查如果密码是正确的，它将返回一个结果，否则结果将是空的。
3. 关闭if..else语句中的连接是一个非常糟糕的做法，您最好使用try..catch语句来处理finally块中的连接，其中您将分别关闭resultset, prepared statement和connexion。

我试着改进你的代码，这是你应该做的:

    Connection con = null;
    String URL="jdbc:derby://localhost:1527/Bank Database";
    PreparedStatement ps = null;
    ResultSet rs = null;
    String qry="SELECT * FROM DATABASE WHERE BANK=? AND CARD_NO=? AND PASWORD=?";
    try {
        con = DriverManager.getConnection(URL,"Bank","Bank");
        ps = con.prepareStatement(qry);
        //set the parameter
        ps.setString(1, bname);
        ps.setString(2, cname);
        ps.setString(3, pswrd);
        rs = ps.executeQuery();
        if (rs.next()) {
            out.println("<script>function redirect(){ window.location="scsfl.html";} setTimeout('redirect()',3000);</script>");  //redirects to another page 
        } else {
            out.println("<script>function redirect(){ window.location="failed.html";} setTimeout('redirect()',3000);</script>");
        }
    } finally {
        if (rs != null)
            rs.close();
        ps.close();
        con.close();
    }

Answer 2

确保您在正确的数据库中，我犯了几次这个错误。查询正确，数据库错误

Answer 3

一般来说，关闭连接会自动关闭与该连接关联的所有语句和结果集。

在这段代码中，我关闭了rs.next()方法中的连接接口引用。这就是JSP引擎无法将其转换为java文件的原因。它显示错误信息。请同时查找代码和相应的错误信息。

Jsp代码:

Class.forName("oracle.jdbc.driver.OracleDriver");
   Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "test", "test");
   
   String username = "Anil3";
   String password = "123";
  
  
   PreparedStatement pst = con.prepareStatement("select * from User_Login where userID='"+ username +"'");
   ResultSet rs = pst.executeQuery();
	while(rs.next())
	{
	  if (password.equals(rs.getString("password"))) 
	   {
		 con.close();
		out.print("inside if loop");
	   }
	  
	  else{
		  
		  con.close();
		  out.print("Inside else loop");
	  }
	}

错误信息:

org.apache.jasper.JasperException: An exception occurred processing JSP page /test.jsp at line 28
25:   
26:    PreparedStatement pst = con.prepareStatement("select * from User_Login where userID='"+ username +"'");
27:    ResultSet rs = pst.executeQuery();
28: 	while(rs.next())
29: 	{
30: 	  if (password.equals(rs.getString("password"))) 
31: 	   {
Stacktrace:
	org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:568)
	org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:455)
	org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390)
	org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
root cause 
javax.servlet.ServletException: java.sql.SQLException: Closed Connection: next
	org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:912)
	org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:841)
	org.apache.jsp.test_jsp._jspService(test_jsp.java:124)
	org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
	org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:432)
	org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390)
	org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
root cause 
java.sql.SQLException: Closed Connection: next
	oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
	oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
	oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:181)
	org.apache.jsp.test_jsp._jspService(test_jsp.java:97)
	org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
	org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:432)
	org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390)
	org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:728)

你不应该关闭ResultSet内的连接。请删除代码中的con.close()语句并尝试。