orderdao.java

来自「一个完整的网络订餐系统」· Java 代码 · 共 440 行

package com.order.dao;

import java.sql.*;
import java.util.*;
import com.util.*;
import com.common.*;
import com.order.form.Orders;
import com.order.form.OrderItem;
import com.order.rule.OrderRule;
import com.order.form.OrderSearchResult;
import com.order.form.OrderView;


public class OrderDao {
  public OrderDao() {
  }

  //插入定单
  public int inserOrderToDb(Orders model)
  {
    int nRet = 0;
    DBConnection dbc = null;
    Connection conn = null;
    Statement stmt = null;
    String strSQL = null;

    try {
      dbc = new DBConnection();
      conn = dbc.getDBConnection();
      stmt = conn.createStatement();

      String orderID = model.getOrderID();
      String memberID = model.getMemberID();
      float orderPrice = model.getOrderPrice();
      float discount = model.getDiscount();
      String sendTo = model.getSendTo();
      String descript = model.getDescript();
      String status = model.getStatus();
      String storeID = model.getStoreID();

      strSQL = " INSERT Orders(orderID,memberID,orderPrice,discount,sendTo,createDate,modifyDate,descript,status,storeID) values('" +
          orderID + "','" + memberID + "'," + orderPrice + ",'" + discount +
          "','" + sendTo + "',getdate(),getdate(),'" + descript + "','" +
          status + "','"+storeID+"')";

      nRet = stmt.executeUpdate(strSQL);
    }
    catch (Exception e) {
      nRet = -1;
      e.printStackTrace();
      System.out.println("\n" + e.toString() + "插入定单记录" + strSQL); /////错误处理！
    }
    finally {
      try {
        if (stmt != null) {
          stmt.close();
        }
        if (conn != null) {
          dbc.closeDBConnection(conn);
        }
      }
      catch (Exception ex) {}
    }
    return nRet;
  }

  //插入定单
  public int inserOrderItemToDb(ArrayList itemList)
  {
    int nRet = 0;
    DBConnection dbc = null;
    Connection conn = null;
    Statement stmt = null;
    String strSQL = null;

    try {
      dbc = new DBConnection();
      conn = dbc.getDBConnection();
      stmt = conn.createStatement();

      for(int i=0;i<itemList.size();i++)
      {
        OrderItem item = (OrderItem)itemList.get(i);
        String orderID =item.getOrderID();
        String foodID = item.getFoodID();
        String foodName = item.getFoodName();
        float amount = item.getAmount();
        String unit = item.getUnit();
        float price = item.getPrice();
        String sendDate = item.getSendDate();
        String foodtype = item.getFoodtype();
        String status = item.getStatus();
        String storeID = item.getStoreID();

        strSQL = " INSERT orderItem(orderID,foodID,foodName,amount,unit,price,sendDate,foodtype,status,storeID) values('" +
                                   orderID + "','" + foodID + "','" + foodName + "'," + amount +
                                    ",'" + unit + "'," + price + ",'" + sendDate+"','"+foodtype + "','"+status+"','"+storeID+"')";

        nRet = stmt.executeUpdate(strSQL);
      }
    }
    catch (Exception e) {
      nRet = -1;
      e.printStackTrace();
      System.out.println("\n" + e.toString() + "插入定单记录" + strSQL); /////错误处理！
    }
    finally {
      try {
        if (stmt != null) {
          stmt.close();
        }
        if (conn != null) {
          dbc.closeDBConnection(conn);
        }
      }
      catch (Exception ex) {}
    }
    return nRet;
  }

 public int deleteOrder(String orderID)
 {
   int nRet;
   nRet = 0;
   DBConnection dbc = null;
   Connection conn = null;
   Statement stmt = null;
   try {
     dbc = new DBConnection();
     conn = dbc.getDBConnection();
     String strSQL = "DELETE FROM Orders WHERE orderID='" + orderID + "'";

     stmt = conn.createStatement();
     nRet = stmt.executeUpdate(strSQL);
     if (nRet != 1)
       nRet = -1;
   }
   catch (Exception e) {
     e.printStackTrace();
     nRet = -1;
   }
   finally {
     try {
       if (stmt != null)
         stmt.close();
       if (conn != null)
         dbc.closeDBConnection(conn);
     }
     catch (SQLException e) {}
   }
   return nRet;
 }

 public int deleteOrderItem(String orderID,String foodID)
 {
   int nRet;
   nRet = 0;
   DBConnection dbc = null;
   Connection conn = null;
   Statement stmt = null;
   try {
     dbc = new DBConnection();
     conn = dbc.getDBConnection();
     String strSQL = "DELETE FROM orderItem WHERE orderID='" + orderID + "' and foodID='"+foodID+"'";

     stmt = conn.createStatement();
     nRet = stmt.executeUpdate(strSQL);
     if (nRet != 1)
       nRet = -1;
   }
   catch (Exception e) {
     e.printStackTrace();
     nRet = -1;
   }
   finally {
     try {
       if (stmt != null)
         stmt.close();
       if (conn != null)
         dbc.closeDBConnection(conn);
     }
     catch (SQLException e) {}
   }
   return nRet;
 }



 //查定单记录
 public OrderSearchResult getOrderSearch(OrderRule tempPsr)
 {
   int totalRecNum = 0;
   List orderItemList = new ArrayList();
   OrderSearchResult bsrt = new OrderSearchResult();

   DBConnection dbc = null;
   Connection conn = null;
   Statement stmt = null;
   ResultSet rs = null;

   String strSQL = null;
   String strSQLForCount = null;

   int startRecNum = tempPsr.getStartRecNum();
   int recNumOfPage = tempPsr.getRecNumOfPage();

   String orderStr = StrUtility.replacenull(tempPsr.getOrderStr());
   String status = StrUtility.replacenull(tempPsr.getStatus());
   String memberID =  StrUtility.replacenull(tempPsr.getMemberID());

   String sendDate1 = StrUtility.replacenull(tempPsr.getSendDate1());
   String sendDate2 = StrUtility.replacenull(tempPsr.getSendDate2());

   String storeID = StrUtility.replacenull(tempPsr.getStoreID());


   strSQL = " SELECT * FROM OrderView ";
   strSQLForCount = " SELECT sum(amount) FROM OrderView ";
   String whereStr = "";

   if (! ( ("").equals(status)) && status != null) {
     whereStr = "status LIKE '%" +
         StrUtility.replaceString(status, "'", "''") + "%'  AND ";
   }
   if (! ( ("").equals(memberID)) && memberID != null) {
   whereStr = whereStr + "memberID= '" +
       StrUtility.replaceString(memberID, "'", "''") + "'  AND ";
   }

   if (! ( ("").equals(storeID)) && storeID != null) {
   whereStr = whereStr + "storeID= '" +
       StrUtility.replaceString(storeID, "'", "''") + "'  AND ";
   }

   if (! ( ("").equals(sendDate1)) && sendDate1 != null) {
     whereStr = whereStr + " sendDate>= '" + sendDate1 + "'  AND ";
   }
   if (! ( ("").equals(sendDate2)) && sendDate2 != null) {
     whereStr = whereStr + " sendDate<='" + sendDate2 + "'  AND ";
   }
   int lenOfWhereStr = whereStr.length();
   if (lenOfWhereStr - 4 >= 0) {
     whereStr = whereStr.substring(0, lenOfWhereStr - 4);
   }
   if (!whereStr.equals("")) {
     strSQL = strSQL + " WHERE " + whereStr;
     strSQLForCount = strSQLForCount + " WHERE " + whereStr;
   }
   if (! ( ("".equals(orderStr))) && (orderStr != null)) {
     strSQL = strSQL + " Order by " + orderStr;
   }


   try {
     dbc = new DBConnection();
     conn = dbc.getDBConnection();
     stmt = conn.createStatement();
     rs = stmt.executeQuery(strSQLForCount);
     if (!rs.next()) {
       throw new Exception("\u83B7\u5F97\u603B\u8BB0\u5F55\u6570\u5931\u8D25");
     }
     totalRecNum = rs.getInt(1);

     rs.close();
     rs = null;
     rs = stmt.executeQuery(strSQL);
     boolean rsresult = false;
     boolean hasnext = false;
     for (int j = 1; j <= startRecNum; j++)
       rsresult = rs.next();

     if (rsresult) {
       hasnext = true;
       for (int i = 1; i <= recNumOfPage && hasnext; i++) {
         OrderView model = new OrderView();
         model.setOrderID(rs.getString("orderID"));
         model.setFoodID(rs.getString("foodID"));
         model.setFoodName(rs.getString("foodName"));
         model.setAmount(rs.getFloat("amount"));
         model.setUnit(rs.getString("unit"));
         model.setPrice(rs.getFloat("price"));
         model.setSendDate(rs.getString("sendDate"));
         model.setFoodtype(rs.getString("foodtype"));
         model.setStatus(rs.getString("status"));
         model.setDescript(rs.getString("descript"));
         model.setMemberID(rs.getString("memberID"));
         model.setSendTo(rs.getString("sendTo"));
         orderItemList.add(model);
         if (rs.next()) {
           hasnext = true;
         }
         else {
           hasnext = false;
         }
       }
     }

   }
   catch (Exception exception) {
     exception.printStackTrace();
   }
   finally {
     try {
       if (rs != null)
         rs.close();
       if (stmt != null)
         stmt.close();
       if (conn != null)
         dbc.closeDBConnection(conn);
     }
     catch (SQLException e) {}
   }

   bsrt.setFoodList(orderItemList);
   bsrt.setTotalRecNum(totalRecNum);
   return bsrt;
 }

 //判断该定单是不是还有定单条目
 public boolean isHavaOrderItem(String orderID)
 {
   boolean bHave = false;
   DBConnection dbc = null;
   Connection conn = null;
   Statement stmt = null;
   ResultSet rs = null;
   try {
     dbc = new DBConnection();
     conn = dbc.getDBConnection();
     String strSQL = "select * FROM orderItem WHERE orderID='" + orderID + "'";

     stmt = conn.createStatement();
     rs = stmt.executeQuery(strSQL);
     if(rs.next())
       bHave = true;
   }
   catch (Exception e) {
     e.printStackTrace();
   }
   finally {
     try {
       if (stmt != null)
         stmt.close();
       if (conn != null)
         dbc.closeDBConnection(conn);
     }
     catch (SQLException e) {}
   }
   return bHave;
 }


 public OrderView getOneOrderView(String orderID,String foodID)
 {
  DBConnection dbc = null;
  Connection conn = null;
  Statement stmt = null;
  ResultSet rs = null;
  OrderView model = null;

  String strSQL = " SELECT * FROM OrderView where orderID='"+orderID+"' and foodID ='"+foodID+"'";
  try
  {
    dbc = new DBConnection();
    conn = dbc.getDBConnection();
    stmt = conn.createStatement();

    rs = stmt.executeQuery(strSQL);
    if(rs.next())
    {
        model = new OrderView();
        model.setOrderID(rs.getString("orderID"));
        model.setFoodID(rs.getString("foodID"));
        model.setFoodName(rs.getString("foodName"));
        model.setAmount(rs.getFloat("amount"));
        model.setUnit(rs.getString("unit"));
        model.setPrice(rs.getFloat("price"));
        model.setSendDate(rs.getString("sendDate"));
        model.setFoodtype(rs.getString("foodtype"));
        model.setStatus(rs.getString("status"));
        model.setMemberID(rs.getString("memberID"));
        model.setSendTo(rs.getString("sendTo"));
        model.setStoreID(rs.getString("storeID"));
        model.setStoreName(rs.getString("storeName"));
      }
    }
  catch (Exception exception) {
    exception.printStackTrace();
  }
  finally {
    try {
      if (rs != null)
        rs.close();
      if (stmt != null)
        stmt.close();
      if (conn != null)
        dbc.closeDBConnection(conn);
    }
    catch (SQLException e) {}
  }
  return model;
 }

 public int updateOrderItemStatus(String orderID,String foodID,String status)
 {
   int nRet;
   nRet = 0;
   DBConnection dbc = null;
   Connection conn = null;
   Statement stmt = null;
   try {
     dbc = new DBConnection();
     conn = dbc.getDBConnection();
     String strSQL = "update orderItem set status = '"+status+ "' WHERE orderID='" + orderID + "' and foodID='"+foodID+"'";

     stmt = conn.createStatement();
     nRet = stmt.executeUpdate(strSQL);
     if (nRet != 1)
       nRet = -1;
   }
   catch (Exception e) {
     e.printStackTrace();
     nRet = -1;
   }
   finally {
     try {
       if (stmt != null)
         stmt.close();
       if (conn != null)
         dbc.closeDBConnection(conn);
     }
     catch (SQLException e) {}
   }
   return nRet;
 }


}