java - mysql 数据库与java的连接

Question

我正在使用 mysql 的类型 4 驱动程序。代码如下。在每个 java 文件中，我都创建数据库连接并在最后关闭。例如

在 abc.java 中

 Dbconnection db=null;
 Connection con=null;
 PreparedStatement pstmt = null; 
public ActionForward execute(----)
  {
 try{
    db=new Dbconnection();//instantiating user defined Dbconnection class object
    con=db.getConnection();//creating connection object
    ...........
    Login_Check formBean=(Login_Check)form;//bean class object

    pstmt=con.prepareStatement("select type from user_registration where user_name=? and password=? and user_status=?");
    //form parameter values
    pstmt.setString(1,formBean.getUname().trim());
    pstmt.setString(2,formBean.getPassword().trim());
    pstmt.setString(3,"Active");//user status should be active

    ResultSet rs=pstmt.executeQuery();

        if(rs.next())
        {
            ................
            db.releasePreparedStatement(pstmt);
            db.releaseConnection(con);

            return mapping.findForward(SUCCESS);//redirecting to success page
        }
        else
        {
            ActionErrors errors = new ActionErrors();
            errors.add("both", new ActionMessage("errors.both.wrong"));//if both user name and password is incorrect, gives an error message
            saveErrors(request,errors);

            //closing connection and prepareStatement objects
            db.releasePreparedStatement(pstmt);
            db.releaseConnection(con);

            return mapping.findForward(FAILURE);//redirecting to failure page
        }

    }
    catch(Exception e)
    {
        e.printStackTrace();
    }
    return mapping.findForward(FAILURE);//redirecting to failure page
  }

就像在每个 java 文件中我都遵循相同的方式..

在Dbconnection.java文件中

public class Dbconnection
{
    Connection con=null;
    String DB_URL = "jdbc:mysql://localhost:3306/dbname";
    String USER = "abc";//db user name
    String PASS = "abc";//db password
    PreparedStatement pstmt = null;

     public synchronized Connection getConnection()
     {
       try
       {
          Class.forName("com.mysql.jdbc.Driver");//loading mysql driver 
          con = DriverManager.getConnection(DB_URL,USER,PASS);//connecting to mysql
       }
       catch(Exception e)
       {
          e.printStackTrace();
       }
         return con;
      }

      public void releaseConnection(Connection conn)//releasing Connection
      {
         if(conn!=null)
          {
             try
              {
                 conn.close();
              }
              catch(Exception e)
              {
                 e.printStackTrace();
              }
        }
    }

    public void releasePreparedStatement(PreparedStatement stmt)//closing PreparedStatement object
    {
       if(stmt!=null)
       {
         try
         {
              stmt.close();
         }
         catch(Exception e)
         {
             e.printStackTrace();
         }
      }
  }
}

但问题是有时我会收到成功消息。但有时我会收到失败消息。在服务器中我收到错误

The operation is not allowed after ResultSet is closed

上述问题仅在多个用户访问同一文件(例如 abc.java)时发生。

Answer 1

你应该这样做:

1)在finally block 中关闭PreparedStatement和Connection，因此如果代码出现异常，您的代码将正确关闭资源，否则您可能会内存不足泄漏。

2) 如果在您的代码中使用 ResultSet ，如

ResultSet rs = pstm.executeQuery();
......

那么您应该在再次使用它之前将其关闭...

3) abc.java 中的方法是静态的吗？

我会做这样的事情，将close方法移到finally block 中，以防止出现异常时内存泄漏

public ActionForward execute(----) {
    Dbconnection db=null;
    Connection con=null;
    PreparedStatement pstmt = null; 
    ResultSet rs = null;

    try {
        db=new Dbconnection();//instantiating user defined Dbconnection class object
        con=db.getConnection();//creating connection object

        // some code
        Login_Check formBean = (Login_Check) form;//bean class object

        pstmt = con.prepareStatement("select type from user_registration where user_name=? and password=? and user_status=?");
        //form parameter values
        pstmt.setString(1, formBean.getUname().trim());
        pstmt.setString(2, formBean.getPassword().trim());
        pstmt.setString(3, "Active"); //user status should be active

        rs = pstmt.executeQuery();

        if(rs.next())
        {
            /* some code */
            return mapping.findForward(SUCCESS);//redirecting to success page
        }
        else
        {
            ActionErrors errors = new ActionErrors();
            errors.add("both", new ActionMessage("errors.both.wrong"));//if both user name and password is incorrect, gives an error message
            saveErrors(request,errors);
            return mapping.findForward(FAILURE);//redirecting to failure page
        }
    }
    catch(Exception e)
    {
        e.printStackTrace();
    }
    finally {
        db.releaseResultSet(rs);
        db.releasePreparedStatement(pstmt);
        db.releaseConnection(con);
    }

    return mapping.findForward(FAILURE);//redirecting to failure page
  }

您显然需要在 Dbconnection 中添加新的 releaseResultSet 方法来释放集合...