fooddao.java

一个完整的网络订餐系统

package com.food.dao;

import java.sql.*;
import java.util.*;
import com.common.*;
import com.util.*;
import com.food.form.Food;
import com.food.form.FoodSearchResult;
import com.food.rule.FoodRule;
import com.store.form.Store;

public class FoodDao {
  public FoodDao() {
  }

  //插入交往记录
 public int inserFoodToDb(Food 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 foodID = model.getFoodID();
    String foodName = model.getFoodName();
    String foodType= model.getFoodType();
    float  bigPrice = model.getBigPrice();
    float  smallPrice = model.getSmallPrice();
    float  snackPrice = model.getSnackPrice();
    String descript = model.getDescript();
    String foodImage = model.getFoodImage();
    String foodClass = model.getFoodClass();
    String storeID = model.getStoreID();
    strSQL = " INSERT food( foodID,foodName,foodType,bigPrice,smallPrice,snackPrice,"+
                                  "descript,foodImage,createDate,modifyDate,foodClass,storeID) values('"+
                                  foodID+"','"+foodName+"','"+foodType+"',"+bigPrice+","+smallPrice+","+
                                  snackPrice+",'"+descript+"','"+foodImage+"',getdate(),getdate(),'"+foodClass+"','"+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 updatFoodToDb(Food 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 foodID = model.getFoodID();
    String foodName = model.getFoodName();
    String foodType= model.getFoodType();
    float bigPrice = model.getBigPrice();
    float smallPrice = model.getSmallPrice();
    float snackPrice = model.getSnackPrice();
    String descript = model.getDescript();
    String foodImage = model.getFoodImage();
    String foodClass = model.getFoodClass();
    strSQL = " update food set foodName='" + foodName +"',foodType='"+foodType+
        "',bigPrice=" + bigPrice + ",smallPrice=" + smallPrice + "," +
        "snackPrice=" + snackPrice + ",descript='" + descript +
        "',foodImage='" + foodImage + "',modifyDate=getdate(),foodClass='" +foodClass+
        "' where foodID = '" + foodID + "'";
    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 deleteOneFood(String foodID,String storeID)
 {
   int nRet;
   nRet = 0;
   DBConnection dbc = null;
   Connection conn = null;
   Statement stmt = null;
   try {
     dbc = new DBConnection();
     conn = dbc.getDBConnection();
     String strSQL = "DELETE FROM food WHERE foodID='" + foodID +"' and storeID='"+storeID+"'";

     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 FoodSearchResult getFoodSearch(FoodRule tempPsr)
 {
   int totalRecNum = 0;
   List foodList = new ArrayList();
   FoodSearchResult bsrt = new FoodSearchResult();

   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 foodName = StrUtility.replacenull(tempPsr.getFoodName());
   String foodType = StrUtility.replacenull(tempPsr.getFoodType());
   String foodClass =  StrUtility.replacenull(tempPsr.getFoodClass());

   String modifyDateUp = StrUtility.replacenull(tempPsr.getModifyDateUp());
   String modifyDateDown = StrUtility.replacenull(tempPsr.getModifyDateDown());

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



   strSQL = " SELECT * FROM food ";
   strSQLForCount = " SELECT count(*) FROM food ";
   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(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"));
         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 FoodSearchResult getFoodViewSearch(FoodRule tempPsr)
 {
   int totalRecNum = 0;
   List foodList = new ArrayList();
   FoodSearchResult bsrt = new FoodSearchResult();

   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 foodName = StrUtility.replacenull(tempPsr.getFoodName());
   String foodType = StrUtility.replacenull(tempPsr.getFoodType());
   String foodClass =  StrUtility.replacenull(tempPsr.getFoodClass());

   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_food_store ";