leaguedao.java

一个完整的网络订餐系统

package com.league.dao;

import java.sql.*;
import java.util.*;
import com.common.*;
import com.store.form.*;
import com.league.form.*;


public class LeagueDao
{
  public LeagueDao()
  {
  }

  //获得城市的区和街道信息
  public AreaForm getStreetInfo(String areaID)
  {
    AreaForm area = new AreaForm();
    int size = 0;
    int all = 0;
    ArrayList streetList =  null;
    ArrayList areaList =  new ArrayList();

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

    String strSQL = null;

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

      strSQL = " EXEC PROC_LIST_SUBAREA_NUM'"+areaID+"'";
      SubArea sub = null;
      rs = stmt.executeQuery(strSQL);
      while(rs.next())
      {
        String streetID = rs.getString("streetID");
        String streetcode = rs.getString("streetcode");
        String streetName = rs.getString("streetName");
        String isRoot = rs.getString("isRoot");
        int num = rs.getInt("num");

        if(isRoot.equals("1"))
        {
          if(sub!=null && streetList != null)
          {
            sub.setStreetList(streetList);
            sub.setNum(size);
            areaList.add(sub);
            all += size;
          }
          sub = new SubArea();
          streetList = new ArrayList();
          size = 0;
          sub.setStreetcode(streetcode);
          sub.setStreetID(streetID);
          sub.setStreetName(streetName);
        }else
        {
          StreetBean street = new StreetBean();
          street.setIsRoot(isRoot);
          street.setStreetcode(streetcode);
          street.setStreetID(streetID);
          street.setStreetName(streetName);
          street.setNum(num);
          streetList.add(street);
          size += num;
        }
      }//while
      if(sub!=null && streetList != null)
      {
        sub.setStreetList(streetList);
        sub.setNum(size);
        areaList.add(sub);
        all += size;
      }

      area.setAreaID(areaID);
      area.setAreaList(areaList);
      area.setAreaName("");
      area.setNum(all);
    }
    catch (Exception e)
    {
      e.printStackTrace();
      System.out.println("\n" + e.toString() + "获得城市区域信息" + strSQL); /////错误处理！
    }
    finally
    {
      try
      {
        if (stmt != null)  stmt.close();
        if (rs != null)  rs.close();
        if (conn != null)  dbc.closeDBConnection(conn);
      }
      catch (Exception ex) {}
    }
    return area;
  }


  //获得饭店信息
   private List getStoreListBySQL(String sql)
   {
     List  storeList = new ArrayList();
     DBConnection dbc = null;
     Connection conn = null;
     Statement stmt = null;
     ResultSet rs = null;

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

       strSQL = sql;
       Store store  = null;
       rs = stmt.executeQuery(strSQL);
       while(rs.next())
       {
         store = new Store();
         String storeID = rs.getString("storeID");
         String storeName = rs.getString("storeName");
         String phone = rs.getString("phone");
         String createdate = rs.getString("createdate");
         int starNum = rs.getInt("starNum");

         store.setCreatedate(createdate);
         store.setPhone(phone);
         store.setStarNum(starNum);
         store.setStoreID(storeID);
         store.setStoreName(storeName);

         storeList.add(store);
       }
     }
     catch (Exception e)
     {
       e.printStackTrace();
       System.out.println("\n" + e.toString() + "获得饭店信息" + strSQL); /////错误处理！
     }
     finally
     {
       try
       {
         if (stmt != null)  stmt.close();
         if (rs != null)  rs.close();
         if (conn != null)  dbc.closeDBConnection(conn);
       }
       catch (Exception ex) {}
     }

     return storeList;
   }


   //获得饭店信息
    public List getStoreListByStreetID(String streetID)
    {
      String strSQL = "SELECT storeID,storeName,phone,starNum,createdate FROM store WHERE streetID='"+streetID+"'";

      return getStoreListBySQL(strSQL);
    }

    public List getBestStoreList(String areaID)
    {
      String strSQL = " SELECT TOP 5 storeID,storeName,phone,starNum,createdate FROM store WHERE area='"+areaID+"' ORDER BY pointer desc";

      return getStoreListBySQL(strSQL);
    }

    public List getNewStoreList(String areaID)
    {
      String strSQL = " SELECT TOP 5 storeID,storeName,phone,starNum,createdate FROM store WHERE area='"+areaID+"' ORDER BY createdate desc";

      return getStoreListBySQL(strSQL);
    }

    //插入街道信息
   public int inserStreetToDb(StreetBean 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 streetID = model.getStreetID();
       String streetcode = model.getStreetcode();
       String streetName = model.getStreetName();
       String isRoot = model.getIsRoot();



       strSQL = " INSERT Street(streetID,streetcode,streetName,isRoot) values('" +
           streetID + "','" + streetcode + "','" + streetName + "','" + isRoot +"')";

       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;
   }

}