menubilldao.java

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

package com.menubill.dao;

import java.sql.*;
import java.util.*;
import com.common.*;
import com.util.*;
import com.menubill.form.Menubill;
import com.menubill.form.MenubillSearchResult;
import com.menubill.rule.MenubillRule;


public class MenubillDao {


  public MenubillDao() {
  }

  //增加菜谱
  public int inserMenubillToDb(Menubill 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 billID = model.getBillID();
    String billName = model.getBillName() ;
    String billType = model.getBillType() ;
    String characters  = model.getCharacters();
    String photo = model.getPhoto();
    String material = model.getMaterial();
    String howtomake = model.getHowtomake();
    String storeID = model.getStoreID();


    strSQL = " INSERT Menubill(billID,billName,billType,characters,photo,material,"+
                             "howtomake, storeID, createdate, modifydate) values('" +
                            billID+"','"+billName+"','"+billType+"','"+characters+"','"+
                            photo+"','"+material+"','"+howtomake+"','"+storeID+"',getdate(),getdate())";

    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 updatMenubillToDb(Menubill 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 billID = model.getBillID();
    String billName = model.getBillName() ;
    String billType = model.getBillType() ;
    String characters  = model.getCharacters();
    String photo = model.getPhoto();
    String material = model.getMaterial();
    String howtomake = model.getHowtomake();

    strSQL = "update Menubill set billName='" + billName +
        "',billType='" + billType + "',characters='" + characters +
        "', photo='" + photo + "', material='"+material+"',howtomake='" +
        howtomake + "'  where billID='" + billID + "'";

    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 deleteOneMenubill(String billID)
 {
   int nRet;
   nRet = 0;
   DBConnection dbc = null;
   Connection conn = null;
   Statement stmt = null;
   try {
     dbc = new DBConnection();
     conn = dbc.getDBConnection();
     String strSQL = "DELETE FROM Menubill WHERE billID='" + billID +"'";

     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 MenubillSearchResult getMenubillSearch(MenubillRule tempPsr)
 {
   int totalRecNum = 0;
   List menubillList = new ArrayList();
   MenubillSearchResult bsrt = new MenubillSearchResult();

   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 billName = StrUtility.replacenull(tempPsr.getBillName());
   String billType  = StrUtility.replacenull(tempPsr.getBillType());
   String modDate1  = StrUtility.replacenull(tempPsr.getModDate1());
   String modDate2  = StrUtility.replacenull(tempPsr.getModDate2());
   String storeID = StrUtility.replacenull(tempPsr.getStoreID());

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

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

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

   if (! ( ("").equals(modDate1)) && modDate1 != null) {
     whereStr = whereStr + " modifydate>= '" + modDate1 + "'  AND ";
   }
   if (! ( ("").equals(modDate2)) && modDate2 != null) {
     whereStr = whereStr + " modifydate<='" + modDate2 + "'  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++) {
         Menubill model = new Menubill();
         model.setBillID(rs.getString("billID"));
         model.setBillName(rs.getString("billName"));
         model.setBillType(rs.getString("billType"));
         model.setCharacters(rs.getString("characters"));
         model.setPhoto(rs.getString("photo"));
         model.setMaterial(rs.getString("material"));
         model.setHowtomake(rs.getString("howtomake"));
         model.setStoreID(rs.getString("storeID"));
         model.setCreatedate(rs.getString("createdate"));
         model.setModifydate(rs.getString("modifydate"));

         menubillList.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.setMenubillList(menubillList);
   bsrt.setTotalRecNum(totalRecNum);
   return bsrt;
 }


 //获得一个菜谱的详细信息
 public Menubill getOneMenubillByID(String billID)
 {
   DBConnection dbc = null;
   Connection conn = null;
   Statement stmt = null;
   ResultSet rs = null;
   Menubill model = null;
   String strSQL = " SELECT * FROM Menubill  where billID='"+billID+"'";
   try
   {
     dbc = new DBConnection();
     conn = dbc.getDBConnection();
     stmt = conn.createStatement();
     rs = stmt.executeQuery(strSQL);
     if (rs.next())
     {
       model = new Menubill();
       model.setBillID(rs.getString("billID"));
       model.setBillName(rs.getString("billName"));
       model.setBillType(rs.getString("billType"));
       model.setCharacters(rs.getString("characters"));
       model.setPhoto(rs.getString("photo"));
       model.setMaterial(rs.getString("material"));
       model.setHowtomake(rs.getString("howtomake"));
       model.setStoreID(rs.getString("storeID"));
       model.setCreatedate(rs.getString("createdate"));
       model.setModifydate(rs.getString("modifydate"));
     }
    }
   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 getNewMenubillList(String areaID)
 {
   int totalRecNum = 0;
   List menuList = new ArrayList();

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

   String strSQL = null;
   String strSQLForCount = null;

   strSQL = " SELECT top 5 * FROM Menubill  where storeID IN (SELECT storeID FROM store WHERE streetID LIKE '%"+areaID+"%') order by modifydate";

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

     rs = stmt.executeQuery(strSQL);

     while (rs.next()) {
         Menubill model = new Menubill();
         model.setBillID(rs.getString("billID"));
         model.setBillName(rs.getString("billName"));
         model.setBillType(rs.getString("billType"));
         model.setCharacters(rs.getString("characters"));
         model.setPhoto(rs.getString("photo"));
         model.setMaterial(rs.getString("material"));
         model.setHowtomake(rs.getString("howtomake"));
         model.setStoreID(rs.getString("storeID"));
         model.setCreatedate(rs.getString("createdate"));
         model.setModifydate(rs.getString("modifydate"));
         menuList.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 menuList;
 }



}