foodsetdao.java

一个完整的网络订餐系统

    }
    catch (Exception ex) {}
  }
  return set;
}


 //查询套餐记录
public FoodSetSearchResult getFoodSetSearch(FoodSetRule tempPsr)
{
  int totalRecNum = 0;
  List foodsetList = new ArrayList();
  FoodSetSearchResult bsrt = new FoodSetSearchResult();

  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 modifyDateUp = StrUtility.replacenull(tempPsr.getModifyDateUp());
  String modifyDateDown = StrUtility.replacenull(tempPsr.getModifyDateDown());

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



  strSQL = " SELECT * FROM foodset ";
  strSQLForCount = " SELECT count(*) FROM foodset ";
  String whereStr = "";

  if (! ( ("").equals(storeID)) && storeID != null) {
  whereStr = whereStr + " storeID LIKE '%" +
      StrUtility.replaceString(storeID, "'", "''") + "%'  AND ";
}
  if (! ( ("").equals(modifyDateDown)) && modifyDateDown != null) {
    whereStr = whereStr + " weekday>= '" + modifyDateDown + "'  AND ";
  }
  if (! ( ("").equals(modifyDateUp)) && modifyDateUp != null) {
    whereStr = whereStr + " weekday<='" + modifyDateUp + "'  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++) {
        FoodSet set = new FoodSet();
        set.setFoodsetID(rs.getString("foodsetID"));
        set.setAlias(rs.getString("alias"));
        set.setFoodList(rs.getString("foodList"));
        set.setPrice(rs.getFloat("price"));
        set.setDescript(rs.getString("descript"));
        set.setWeekNum(rs.getString("weekNum"));
        set.setWeekday(rs.getString("weekday"));
        set.setFoodImage(rs.getString("foodImage"));
        set.setStoreID(rs.getString("storeID"));
        foodsetList.add(set);
        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.setFoodSetList(foodsetList);
  bsrt.setTotalRecNum(totalRecNum);
  return bsrt;
}

//查询套餐记录
public FoodSetSearchResult getFoodSetViewSearch(FoodSetRule tempPsr) {
  int totalRecNum = 0;
  List foodsetList = new ArrayList();
  FoodSetSearchResult bsrt = new FoodSetSearchResult();

  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 modifyDateUp = StrUtility.replacenull(tempPsr.getModifyDateUp());
  String modifyDateDown = StrUtility.replacenull(tempPsr.getModifyDateDown());

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

  strSQL = " SELECT * FROM v_foodset_store ";
  strSQLForCount = " SELECT count(*) FROM v_foodset_store ";
  String whereStr = "";

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

  if (! ( ("").equals(areaID)) && areaID != null) {
    whereStr = whereStr + " (storeID IN (SELECT storeID FROM store  WHERE streetID LIKE '%"+areaID+"%')) AND ";
  }
  if (! ( ("").equals(modifyDateDown)) && modifyDateDown != null) {
    whereStr = whereStr + " weekday>= '" + modifyDateDown + "'  AND ";
  }
  if (! ( ("").equals(modifyDateUp)) && modifyDateUp != null) {
    whereStr = whereStr + " weekday<='" + modifyDateUp + "'  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++) {
        FoodSet set = new FoodSet();
        set.setFoodsetID(rs.getString("foodsetID"));
        set.setAlias(rs.getString("alias"));
        set.setFoodList(rs.getString("foodList"));
        set.setPrice(rs.getFloat("price"));
        set.setDescript(rs.getString("descript"));
        set.setWeekNum(rs.getString("weekNum"));
        set.setWeekday(rs.getString("weekday"));
        set.setFoodImage(rs.getString("foodImage"));
        set.setStoreID(rs.getString("storeID"));
        set.setStoreName(rs.getString("storeName"));
        foodsetList.add(set);
        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.setFoodSetList(foodsetList);
  bsrt.setTotalRecNum(totalRecNum);
  return bsrt;
}


 public List getStoreList(String areaID)
 {
    List storeList = new ArrayList();

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

    String strSQL = null;

    strSQL = "SELECT DISTINCT storeID, storeName FROM v_foodset_store where storeID IN"+
            " (SELECT storeID FROM store WHERE streetID LIKE '%"+areaID+"%')";

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

         while (rs.next()) {
             Store store = new Store();
             store.setStoreID(rs.getString("storeID"));
             store.setStoreName(rs.getString("storeName"));
             storeList.add(store);
           }
         }
       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 storeList;
   }

   //最新的六个套餐
public List getNewFoodSet(String areaID)
{
  List foodsetList = new ArrayList();

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

  FoodSet model = null;
  String strSQL = "select top 6 * from v_foodset_store where storeId in(SELECT top 5 storeID FROM store  WHERE streetID LIKE '%"+areaID+"%' order by starNum DESC) order by weekday DESC";

  try
  {
    dbc = new DBConnection();
    conn = dbc.getDBConnection();
    stmt = conn.createStatement();
    rs = stmt.executeQuery(strSQL);
    while (rs.next())
    {
      model = new FoodSet();
      model.setFoodsetID(rs.getString("foodsetID"));
       model.setAlias(rs.getString("alias"));
       model.setFoodList(rs.getString("foodList"));
       model.setPrice(rs.getFloat("price"));
       model.setDescript(rs.getString("descript"));
       model.setWeekNum(rs.getString("weekNum"));
       model.setWeekday(rs.getString("weekday"));
       model.setFoodImage(rs.getString("foodImage"));
       model.setStoreID(rs.getString("storeID"));
       model.setStoreName(rs.getString("storeName"));
       foodsetList.add(model);
     }
   }
  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 foodsetList;
}

}