catalogdao.java

一个完整的网络订餐系统

package com.catalog.dao;

import java.sql.*;
import java.util.*;
import com.common.*;
import com.util.*;
import com.catalog.form.Catalog;
import com.catalog.form.*;
import com.catalog.rule.CatalogRule;

public class CatalogDao {
  public CatalogDao() {
  }

  //增加目录
  public int inserCatalogToDb(Catalog 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 catalogID = model.getCatalogID();
    String catalogname = model.getCatalogname();
    String pararentID = model.getPararentID();
    String catlogPhoto = model.getCatlogPhoto();

    strSQL = " INSERT catalog(catalogID,catalogname,pararentID,catlogPhoto) values('" +
                            catalogID+"','"+catalogname+"','"+pararentID+"','"+catlogPhoto+"')";

    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 updatCatalogToDb(Catalog 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 catalogID = model.getCatalogID();
    String catalogname = model.getCatalogname();
    String pararentID = model.getPararentID();
    String catlogPhoto = model.getCatlogPhoto();

    strSQL = "update catalog set catalogname='" + catalogname +
        "',pararentID='" + pararentID +"',catlogPhoto='"+catlogPhoto+"'  where catalogID='" + catalogID + "'";

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

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

 /**
  * 获得该目录的二级子目录
  * @param catalogID String
  * @return List
  */
 public List getSubCatalog(String catalogID) {
   List catalogList = new ArrayList();
   DBConnection dbc = null;
   Connection conn = null;
   Statement stmt = null;
   ResultSet rs = null;
   Catalog model = null;
   String strSQL = " SELECT * FROM catalog  where pararentID = '" + catalogID + "'";
   try
   {
     dbc = new DBConnection();
     conn = dbc.getDBConnection();
     stmt = conn.createStatement();
     rs = stmt.executeQuery(strSQL);
     while (rs.next())
     {
       model = new Catalog();
       model.setCatalogID(rs.getString("catalogID"));
       model.setCatalogname(rs.getString("catalogname"));
       model.setPararentID(rs.getString("pararentID"));
       model.setCatlogPhoto(rs.getString("catlogPhoto"));
       catalogList.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 catalogList;
 }

 /**
   * 获得该目录的二级子目录
   * @param
   * @return List
   */
  public List getMainCatalog() {
    List catalogList = new ArrayList();
    DBConnection dbc = null;
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    Catalog model = null;
    String strSQL = " SELECT * FROM catalog  where pararentID = '0'";
    try
    {
      dbc = new DBConnection();
      conn = dbc.getDBConnection();
      stmt = conn.createStatement();
      rs = stmt.executeQuery(strSQL);
      while (rs.next())
      {
        model = new Catalog();
        model.setCatalogID(rs.getString("catalogID"));
        model.setCatalogname(rs.getString("catalogname"));
        model.setPararentID(rs.getString("pararentID"));
        model.setCatlogPhoto(rs.getString("catlogPhoto"));
        catalogList.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 catalogList;
  }

  /**
    * 获得所有的二级子目录
    * @param
    * @return List
    */
   public List getAllSubCatalog() {
     List catalogList = new ArrayList();
     DBConnection dbc = null;
     Connection conn = null;
     Statement stmt = null;
     ResultSet rs = null;
     Catalog model = null;
     String strSQL = " SELECT * FROM catalog  where pararentID != '0'";
     try
     {
       dbc = new DBConnection();
       conn = dbc.getDBConnection();
       stmt = conn.createStatement();
       rs = stmt.executeQuery(strSQL);
       while (rs.next())
       {
         model = new Catalog();
         model.setCatalogID(rs.getString("catalogID"));
         model.setCatalogname(rs.getString("catalogname"));
         model.setPararentID(rs.getString("pararentID"));
         model.setCatlogPhoto(rs.getString("catlogPhoto"));
         catalogList.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 catalogList;
   }

 //获得一个目录的详细信息
 public Catalog getOneCatalogByID(String catalogID)
 {
   DBConnection dbc = null;
   Connection conn = null;
   Statement stmt = null;
   ResultSet rs = null;
   Catalog model = null;
   String strSQL = " SELECT * FROM catalog  where catalogID='"+catalogID+"'";
   try
   {
     dbc = new DBConnection();
     conn = dbc.getDBConnection();
     stmt = conn.createStatement();
     rs = stmt.executeQuery(strSQL);
     if (rs.next())
     {
       model = new Catalog();
       model.setCatalogID(rs.getString("catalogID"));
       model.setCatalogname(rs.getString("catalogname"));
       model.setPararentID(rs.getString("pararentID"));
       model.setCatlogPhoto(rs.getString("catlogPhoto"));
     }
    }
   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 CatalogSearchResult getCatalogSearch(CatalogRule tempPsr)
 {
   int totalRecNum = 0;
   List catalogList = new ArrayList();
   CatalogSearchResult bsrt = new CatalogSearchResult();

   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 catalogID = tempPsr.getCatalogID();
   String catalogname = tempPsr.getCatalogname();
   String pararentID = tempPsr.getPararentID();

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

   if (! ( ("").equals(catalogname)) && catalogname != null) {
     whereStr = "catalogname like '%" +
         StrUtility.replaceString(catalogname, "'", "''") + "%'  AND ";
   }
   if (! ( ("").equals(catalogID)) && catalogID != null) {
     whereStr = whereStr + " catalogID = '" +
         StrUtility.replaceString(catalogID, "'", "''") + "'  AND ";
   }
   if (! ( ("").equals(pararentID)) && pararentID != null) {
     whereStr = whereStr + " pararentID = '" +
         StrUtility.replaceString(pararentID, "'", "''") + "'  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("检索目录出错");
     }
     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++) {
         Catalog model = new Catalog();
         model.setCatalogID(rs.getString("catalogID"));
         model.setCatalogname(rs.getString("catalogname"));
         model.setPararentID(rs.getString("pararentID"));
         model.setCatlogPhoto(rs.getString("catlogPhoto"));
         catalogList.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.setCatalogList(catalogList);
   bsrt.setTotalRecNum(totalRecNum);
   return bsrt;
 }

}