fooddao.java

一个完整的网络订餐系统

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

   if (! ( ("").equals(foodName)) && foodName != null) {
     whereStr = "foodName LIKE '%" +
         StrUtility.replaceString(foodName, "'", "''") + "%'  AND ";
   }
   if (! ( ("").equals(foodType)) && foodType != null) {
     whereStr = whereStr + " foodType LIKE '%" +
         StrUtility.replaceString(foodType, "'", "''") + "%'  AND ";
   }
   if (! ( ("").equals(foodClass)) && foodClass != null) {
     whereStr = whereStr + " foodClass LIKE '%" +
         StrUtility.replaceString(foodClass, "'", "''") + "%'  AND ";
   }
   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 + " modifyDate>= '" + modifyDateDown + "'  AND ";
   }
   if (! ( ("").equals(modifyDateUp)) && modifyDateUp != null) {
     whereStr = whereStr + " modifyDate<='" + 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++) {
         Food model = new Food();
         model.setFoodID(rs.getString("foodID"));
         model.setFoodName(rs.getString("foodName"));
         model.setFoodType(rs.getString("foodType"));
         model.setBigPrice(rs.getFloat("bigPrice"));
         model.setSmallPrice(rs.getFloat("smallPrice"));
         model.setSnackPrice(rs.getFloat("snackPrice"));
         model.setDescript(rs.getString("descript"));
         model.setFoodImage(rs.getString("foodImage"));
         model.setCreateDate(rs.getString("createDate"));
         model.setModifyDate(rs.getString("modifyDate"));
         model.setFoodClass(rs.getString("foodClass"));
         model.setStoreID(rs.getString("storeID"));
         model.setStoreName(rs.getString("storeName"));
         foodList.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(foodList);
   bsrt.setTotalRecNum(totalRecNum);
   return bsrt;
 }

  //获得一道菜的详细信息
  public Food getOneFood(String foodID,String storeID)
  {
    DBConnection dbc = null;
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    Food model = null;
    String strSQL = " SELECT * FROM v_food_store  where foodID='"+foodID+"' and storeID='"+storeID+"'";

    try
    {
      dbc = new DBConnection();
      conn = dbc.getDBConnection();
      stmt = conn.createStatement();
      rs = stmt.executeQuery(strSQL);
      if (rs.next())
      {
        model = new Food();
        model.setFoodID(rs.getString("foodID"));
        model.setFoodName(rs.getString("foodName"));
        model.setFoodType(rs.getString("foodType"));
        model.setBigPrice(rs.getFloat("bigPrice"));
        model.setSmallPrice(rs.getFloat("smallPrice"));
        model.setSnackPrice(rs.getFloat("snackPrice"));
        model.setDescript(rs.getString("descript"));
        model.setFoodImage(rs.getString("foodImage"));
        model.setCreateDate(rs.getString("createDate"));
        model.setModifyDate(rs.getString("modifyDate"));
        model.setFoodClass(rs.getString("foodClass"));
        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 Food getOneFood(String foodID)
{
  DBConnection dbc = null;
  Connection conn = null;
  Statement stmt = null;
  ResultSet rs = null;
  Food model = null;
  String strSQL = " SELECT * FROM v_food_store  where foodID='"+foodID+"'";

  try
  {
    dbc = new DBConnection();
    conn = dbc.getDBConnection();
    stmt = conn.createStatement();
    rs = stmt.executeQuery(strSQL);
    if (rs.next())
    {
      model = new Food();
      model.setFoodID(rs.getString("foodID"));
      model.setFoodName(rs.getString("foodName"));
      model.setFoodType(rs.getString("foodType"));
      model.setBigPrice(rs.getFloat("bigPrice"));
      model.setSmallPrice(rs.getFloat("smallPrice"));
      model.setSnackPrice(rs.getFloat("snackPrice"));
      model.setDescript(rs.getString("descript"));
      model.setFoodImage(rs.getString("foodImage"));
      model.setCreateDate(rs.getString("createDate"));
      model.setModifyDate(rs.getString("modifyDate"));
      model.setFoodClass(rs.getString("foodClass"));
      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 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_food_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 getNewFood(String areaID)
  {
    List foodList = new ArrayList();

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

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

    try
    {
      dbc = new DBConnection();
      conn = dbc.getDBConnection();
      stmt = conn.createStatement();
      rs = stmt.executeQuery(strSQL);
      while (rs.next())
      {
        model = new Food();
        model.setFoodID(rs.getString("foodID"));
        model.setFoodName(rs.getString("foodName"));
        model.setFoodType(rs.getString("foodType"));
        model.setBigPrice(rs.getFloat("bigPrice"));
        model.setSmallPrice(rs.getFloat("smallPrice"));
        model.setSnackPrice(rs.getFloat("snackPrice"));
        model.setDescript(rs.getString("descript"));
        model.setFoodImage(rs.getString("foodImage"));
        model.setCreateDate(rs.getString("createDate"));
        model.setModifyDate(rs.getString("modifyDate"));
        model.setFoodClass(rs.getString("foodClass"));
        model.setStoreID(rs.getString("storeID"));
        model.setStoreName(rs.getString("storeName"));
        foodList.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 foodList;
  }

}