title: How to solve Java servlet with SQL statements loop took long time to complete and sometimes do not complete and just return blank HTML page?
body: <p>The servlet below is the checkout servlet created by me using Netbeans and Derby SQL. Basically everytime the user checkout their cart and filled out all the input field, the checkout servlet is called.</p>
  <pre><code>package Servlet;
  
  import java.io.IOException;
  import java.io.PrintWriter;
  import java.sql.*;
  import java.util.logging.Level;
  import java.util.logging.Logger;
  import javax.servlet.ServletException;
  import javax.servlet.http.*;
  
  /**
   *
   * @author user
   */
  public class checkout extends HttpServlet {
  
      private ResultSet rs, rs2, rs3;
      private Connection conn;
      private PreparedStatement pstmt, pstmt2, pstmt3, pstmt4, pstmt5, pstmt6, pstmt7;
      private String host = &quot;jdbc:derby://localhost:1527/assignmentdb&quot;;
      private String user = &quot;nbuser&quot;;
      private String password = &quot;nbuser&quot;;
  
      @Override
      public void init() throws ServletException {
          initializeJdbc();
      }
  
      @Override
      protected void doPost(HttpServletRequest request, HttpServletResponse response)
              throws ServletException, IOException {
          response.setContentType(&quot;text/html&quot;);
          PrintWriter out = response.getWriter();
  
          HttpSession httpSession = request.getSession();
          String username = (String) httpSession.getAttribute(&quot;username&quot;);
  
          String address = request.getParameter(&quot;address&quot;);
          String phoneNum = request.getParameter(&quot;phoneNumber&quot;);
          String repName = request.getParameter(&quot;name&quot;);
          String cardNum = request.getParameter(&quot;cardNum&quot;);
          String expiryMonthString = request.getParameter(&quot;expiryMonth&quot;);
          int expiryMonth = Integer.parseInt(expiryMonthString);
          String expiryYearString = request.getParameter(&quot;expiryYear&quot;);
          int expiryYear = Integer.parseInt(expiryYearString);
          String ccvString = request.getParameter(&quot;cardCCV&quot;);
          int cardCCV = Integer.parseInt(ccvString);
          String amountString = request.getParameter(&quot;amount&quot;);
          double amount = Double.parseDouble(amountString);
  
          String orderStatus = &quot;Order Received&quot;;
  
          try {
              int orderId = 0;
              
              pstmt = conn.prepareStatement(&quot;INSERT INTO ORDERS (ORDER_ADDRESS, PHONENUM, RECIPIENTNAME, ORDER_STATUS, ID, CARD_NUMBER, EXPIRY_MONTH, EXPIRY_YEAR, CARD_CCV, TOTAL_AMOUNT, ORDER_TIME) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)&quot;, Statement.RETURN_GENERATED_KEYS);  
              pstmt.setString(1, address);
              pstmt.setString(2, phoneNum);
              pstmt.setString(3, repName);
              pstmt.setString(4, orderStatus);
              pstmt.setString(5, username);
              pstmt.setString(6, cardNum);
              pstmt.setInt(7, expiryMonth);
              pstmt.setInt(8, expiryYear);
              pstmt.setInt(9, cardCCV);
              pstmt.setDouble(10, amount);
              pstmt.executeUpdate();
              rs3 = pstmt.getGeneratedKeys();
              rs3.next();
              orderId = rs3.getInt(1);       
  
              pstmt2 = conn.prepareStatement(&quot;SELECT * FROM CART_ITEM WHERE ID = ?&quot;);
              pstmt2.setString(1, username);
              rs = pstmt2.executeQuery();
  
              while(rs.next()) {
                  pstmt3 = conn.prepareStatement(&quot;UPDATE PRODUCT SET PROD_QUANTITY = PROD_QUANTITY - ? WHERE PROD_ID = ?&quot;);
                  pstmt3.setInt(1, Integer.parseInt(rs.getString(&quot;quantity&quot;)));
                  pstmt3.setInt(2, Integer.parseInt(rs.getString(&quot;prod_id&quot;)));
                  pstmt3.executeUpdate();
  
                  pstmt4 = conn.prepareStatement(&quot;INSERT INTO ORDER_ITEM (ORDER_ID, PROD_ID, ORDER_QUANTITY) VALUES (?, ?, ?)&quot;);
                  pstmt4.setInt(1, orderId);
                  pstmt4.setInt(2, Integer.parseInt(rs.getString(&quot;prod_id&quot;)));
                  pstmt4.setInt(3, Integer.parseInt(rs.getString(&quot;quantity&quot;)));
                  pstmt4.executeUpdate();
  
                  pstmt5 = conn.prepareStatement(&quot;DELETE FROM CART_ITEM WHERE ID = ? AND PROD_ID = ?&quot;);
                  pstmt5.setString(1, rs.getString(&quot;id&quot;));
                  pstmt5.setInt(2, Integer.parseInt(rs.getString(&quot;prod_id&quot;)));
                  pstmt5.executeUpdate();
              }
  
              response.sendRedirect(&quot;main/customer/order.jsp&quot;);
          } catch (SQLException ex) {
              Logger.getLogger(checkout.class.getName()).log(Level.SEVERE, null, ex);
          }
      }
  
      private void initializeJdbc() {
          try {
              Class.forName(&quot;org.apache.derby.jdbc.ClientDriver&quot;);
              conn = DriverManager.getConnection(host, user, password);  
          } catch (Exception ex) {
              ex.printStackTrace();
          }
      }
  
      public boolean equals(Object obj) {
          return (this == obj);
      }
  
  }
  
  </code></pre>
  <p>First, the order is inserted into the ORDERS table. The second SQL statement is to retrieve all items in the cart of the user. Then, the loop is used to update the quantity of the product by deducting the number purchased by the user, insert data into ORDER_ITEM to record every item purchased by the user and lastly remove all the items from the cart of the user.</p>
  <p>However, sometimes when I run it all the SQL statements are executed but took around 30 seconds to 1 minute, or sometimes it just simply return a white blank HTML and some of the SQL statements aren't executed. How can I solve this problem?</p>