orderejb.java

一个优秀的供应商管理系统

        return (m_orderId);
    }

    private void insertLineItem(int m_orderId) throws SQLException {
      conn = getDBConnection();
      LineItem LI;
      int resultCount;
      Statement stmt = conn.createStatement();
      for (Iterator it = lineItems.iterator() ; it.hasNext() ;) {
        LI = (LineItem) it.next();
        if (LI == null)
          throw new SQLException ("LineItem is null");
        String queryStr = "INSERT INTO shp_lineitem"
          + "(orderid, linenum, productid, quantity, unitprice) VALUES ("
          + m_orderId + ","
          + LI.getLineNo() + ","
          + "'"+ LI.getItemNo().trim() + "',"
          + LI.getQty() + ","
          + LI.getUnitPrice() + ")";

        Debug.println("queryString is: "+ queryStr);
        resultCount = stmt.executeUpdate(queryStr);
        if ( resultCount != 1 )
          throw new SQLException("ERROR in LINE_ITEM_TABLE INSERT !! resultCount = " + resultCount);
      }
      stmt.close();
    }

    private void deleteLineItem () throws SQLException {
      conn = getDBConnection();
      Statement stmt = conn.createStatement();
      String queryStr = "DELETE FROM shp_lineitem"
	      + " WHERE orderid = " + orderId;
      Debug.println("queryString is: "+ queryStr);
      int resultCount = stmt.executeUpdate(queryStr);
      if ( resultCount < 1 )
        throw new SQLException("ERROR deleteing line items!! resultCount = " + resultCount);
      if (stmt != null) stmt.close();
    }

    private void deleteOrder () throws SQLException {
      conn = getDBConnection();
      Statement stmt = conn.createStatement();
      String queryStr = "DELETE FROM shp_order"
	      + " WHERE orderid = " + orderId;
      Debug.println("queryString is: "+ queryStr);
      int resultCount = stmt.executeUpdate(queryStr);
      if ( resultCount != 1 )
        throw new SQLException("ERROR deleteing order from ORDER_TABLE!! resultCount = " + resultCount);
      if (stmt != null) stmt.close();
    }

    private void selectOrder () throws SQLException {
      conn = getDBConnection();
      Statement stmt = conn.createStatement();
      String queryStr = "SELECT "
        + " userId, orderDate, shipName, shipAddr, shipProvince, "
        + " shipCity, shipZip, shipCountry, billName, billAddr, "
        + " billProvince, billCity, billZip, billCountry, "
        + " creditCard, cardType, exprDate, totalPrice "
        + " FROM shp_order "
	      + " WHERE orderid = " + orderId;
        Debug.println("queryString is: "+ queryStr);
        ResultSet rs = stmt.executeQuery(queryStr);
        if ( !rs.next() )
            throw new SQLException("No record for primary key " + orderId);
        int i = 1;
        userId = rs.getString(i++);
        orderDate =rs.getDate(i++);
        shipName = rs.getString(i++);
        shipAddr = rs.getString(i++);
        shipProvince = rs.getString(i++);
        shipCity = rs.getString(i++);
        shipZip = rs.getString(i++);
        shipCountry = rs.getString(i++);

        billName = rs.getString(i++);
        billAddr = rs.getString(i++);
        billProvince = rs.getString(i++);
        billCity = rs.getString(i++);
        billZip = rs.getString(i++);
        billCountry = rs.getString(i++);

        String cardNo = rs.getString(i++);
        String cardType = rs.getString(i++);
        String expiryDate = rs.getString(i++);
        chargeCard = new CreditCard(cardNo, cardType, expiryDate);
        double totalPrice = rs.getDouble(i++);
        stmt.close();
    }

    private void selectLineItem () throws SQLException {
      conn = getDBConnection();
      Statement stmt = conn.createStatement();
	    ArrayList lineItemList = new ArrayList();
      String queryStr = "SELECT "+
        "linenum, productId, quantity, unitprice"
        + " FROM shp_lineitem"
	      + " WHERE orderid = " + orderId;
      Debug.println("queryString is: "+ queryStr);
      ResultSet result = stmt.executeQuery(queryStr);
      if ( !result.next() )
        throw new SQLException("No Line Items for orderId: " + orderId);
      do {
	      int lineNo = result.getInt(1);
	      String itemNo = result.getString(2);
	      int qty = result.getInt(3);
	      double unitPrice = result.getFloat(4);
        LineItem LI = new LineItem(itemNo, qty, unitPrice, lineNo);
        // add the line items to the line item vector
        lineItemList.add(LI);
      } while (result.next());
      lineItems = lineItemList;
      stmt.close();
    }

    private void updateOrder () throws SQLException {
      conn = getDBConnection();
      PreparedStatement pstmt = null;
      String queryStr = "UPDATE shp_order "
	      + " SET userId  = ?, orderDate = ?, shipName = ?, shipAddr = ?, "
        + " shipProvince = ?, shipCity = ?, shipZip = ?, shipCountry = ?, "
        + " billName = ?, billAddr = ?, billProvince = ?, billCity = ?, "
        + " billZip = ?, billCountry = ?, creditCard = ?, cardType = ?, "
        + " exprDate = ?, totalPrice = ? "
        + " WHERE orderid = " + orderId;
      pstmt = conn.prepareStatement(queryStr);
      pstmt.setString(1, userId);
      pstmt.setDate(2, orderDate);
      pstmt.setString(3, shipName);
      pstmt.setString(4, shipAddr);
      pstmt.setString(5, shipProvince);
      pstmt.setString(6, shipCity);
      pstmt.setString(7, shipZip);
      pstmt.setString(8, shipCountry);

      pstmt.setString(9, billName);
      pstmt.setString(10, billAddr);
      pstmt.setString(11, billProvince);
      pstmt.setString(12, billCity);
      pstmt.setString(13, billZip);
      pstmt.setString(14, billCountry);

      pstmt.setString(15, chargeCard.getCardNo());
      pstmt.setString(16, chargeCard.getCardType());
      pstmt.setString(17, chargeCard.getExpiryDateString());
      pstmt.setDouble(18, totalPrice);

      Debug.println("queryString is: "+ queryStr);
      int resultCount = pstmt.executeUpdate();
      if ( resultCount != 1 )
        throw new SQLException("ERROR updating order in ORDER_TABLE!! resultCount = " + resultCount);
      if (pstmt != null) pstmt.close();
    }

    private void updateLineItem () throws SQLException {
      conn = getDBConnection();
      LineItem LI;
      int resultCount;
      Statement stmt = conn.createStatement();
      for (Iterator it = lineItems.iterator() ; it.hasNext() ;) {
        LI = (LineItem) it.next();
        if (LI == null)
          throw new SQLException ("LineItem is null");
        String queryStr = "UPDATE shp_linelitem"
          + " SET productid = " + "'"+ LI.getItemNo().trim() + "',"
          + "quantity = " + LI.getQty() + ","
          + "unitprice = " +  LI.getUnitPrice()
          + " WHERE orderid = " + orderId
          + " AND linenum = " + LI.getLineNo();
        Debug.println("queryString is: "+ queryStr);
        resultCount = stmt.executeUpdate(queryStr);
        if ( resultCount != 1 )
          throw new SQLException ("ERROR updating LINE_ITEM_TABLE  !! resultCount = " + resultCount);
        }
        stmt.close();
    }

    private boolean orderIdExists ( int orderId ) throws SQLException {
      conn = getDBConnection();
      Statement stmt = conn.createStatement();
      String queryStr ="SELECT orderid FROM shp_order "
	      + " WHERE orderid = " + orderId;
      Debug.println("queryString is: "+ queryStr);
      ResultSet result = stmt.executeQuery(queryStr);
      if ( !result.next() ) {
        stmt.close();
        return false;
      } else {
        stmt.close();
        return true;
      }
    }

    private Collection getOrderIds( String userId) throws SQLException {
      conn = getDBConnection();
      ArrayList orderIdList = new ArrayList();
      Statement stmt = conn.createStatement();
      String queryStr ="SELECT orderid FROM shp_order"
	      + " WHERE userid = " + "'" + userId.trim() +"'";
      Debug.println("queryString is: "+ queryStr);
      ResultSet result = stmt.executeQuery(queryStr);
      if ( !result.next() ) {
        stmt.close();
        throw new SQLException ("No Orders found for user: " + userId);
      } else {
        do {
          orderIdList.add( new OrderPK(result.getInt(1)));
        } while (result.next());
        return orderIdList;
      }
    }

    private Connection getDBConnection() throws SQLException {
      Connection connection;
      try {
        InitialContext ic = new InitialContext();
        DataSource ds = (DataSource)
        ic.lookup(JNDINames.MYSHOP_DATASOURCE);
        connection = ds.getConnection();
      } catch (NamingException ne) {
        Debug.print(ne);
        throw new EJBException(ne);
      } catch (SQLException se) {
        Debug.print(se);
        throw new EJBException(se);
      }
      return connection;
    }

    public void setUserId(String userId) {
      this.userId = userId;
      setDirty(true);
    }

    public void setOrderDate(java.sql.Date orderDate) {
      this.orderDate = orderDate;
      setDirty(true);
    }

    public void setShipName(String shipName) {
      this.shipName = shipName;
      setDirty(true);
    }

    public void setShipAddr(String shipAddr) {
      this.shipAddr = shipAddr;
      setDirty(true);
    }

    public void setShipProvince(String shipProvince) {
      this.shipProvince = shipProvince;
      setDirty(true);
    }

    public void setShipCity(String shipCity) {
      this.shipCity = shipCity;
      setDirty(true);
    }

    public void setShipZip(String shipZip) {
      this.shipZip = shipZip;
      setDirty(true);
    }

    public void setShipCountry(String shipCountry) {
      this.shipCountry = shipCountry;
      setDirty(true);
    }

    public void setBillName(String billName) {
      this.billName = billName;
      setDirty(true);
    }

    public void setBillAddr(String billAddr) {
      this.billAddr = billAddr;
      setDirty(true);
    }

    public void setBillProvince(String billProvince) {
      this.billProvince = billProvince;
      setDirty(true);
    }

    public void setBillCity(String billCity) {
      this.billCity = billCity;
      setDirty(true);
    }

    public void setBillZip(String billZip) {
      this.billZip = billZip;
      setDirty(true);
    }

    public void setBillCountry(String billCountry) {
      this.billCountry = billCountry;
      setDirty(true);
    }

    public void setChargeCard(CreditCard chargeCard) {
      this.chargeCard = chargeCard;
      setDirty(true);
    }

    public void setLineItems(Collection lineItems) {
      this.lineItems = lineItems;
      setDirty(true);
    }

    public void setTotalPrice(double totalPrice) {
      this.totalPrice = totalPrice;
      setDirty(true);
    }
}