informationdao.java

一个完整的网络订餐系统

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

   if (! ( ("").equals(storeID)) && storeID != null || ("").equals(storeID)) {
     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);
    while(rs.next()){

         Information model = new Information();
         model.setInformationID(rs.getString("informationID"));
         model.setInfoTitle(rs.getString("infoTitle"));
         model.setInfotype(rs.getString("infotype"));
         model.setInfoabstract(rs.getString("infoabstract"));
         model.setKeywords(rs.getString("keywords"));
         model.setComefrom(rs.getString("comefrom"));
         model.setAuthor(rs.getString("author"));
         model.setContent(rs.getString("content"));
         model.setCreatedate(rs.getString("createdate"));
         model.setModifydate(rs.getString("modifydate"));
         model.setStoreID(rs.getString("storeID"));
         informationList.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 informationList;
 }

//获得热点信息记录
 public List getHotInformation(String catalogID,int informationnum)
 {
   List informationList = new ArrayList();

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

  if(informationnum>0)
    strSQL = "SELECT TOP "+informationnum+ " * FROM information where infoType = '" + catalogID + "' or infoType in(select catalogID from catalog where pararentID = '"+ catalogID +"') order by countnum desc";
  else
    strSQL = "SELECT  * FROM information where infoType = '" + catalogID + "' or infoType in(select catalogID from catalog where pararentID = '"+ catalogID +"') order by countnum desc";

   try {
     dbc = new DBConnection();
     conn = dbc.getDBConnection();
     stmt = conn.createStatement();
     rs = stmt.executeQuery(strSQL);
     while (rs.next())
     {
           Information model = new Information();
           model.setInformationID(rs.getString("informationID"));
           model.setInfoTitle(rs.getString("infoTitle"));
           model.setInfotype(rs.getString("infotype"));
           model.setInfoabstract(rs.getString("infoabstract"));
           model.setKeywords(rs.getString("keywords"));
           model.setComefrom(rs.getString("comefrom"));
           model.setAuthor(rs.getString("author"));
           model.setContent(rs.getString("content"));
           model.setCreatedate(rs.getString("createdate"));
           model.setModifydate(rs.getString("modifydate"));
           model.setStoreID(rs.getString("storeID"));
           model.setCountnum(rs.getInt("countnum"));
           informationList.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 informationList;
 }

//查菜谱记录
 public List getInformationOfCatalog(String catalogID,int informationnum)
 {
   List informationList = new ArrayList();
   DBConnection dbc = null;
   Connection conn = null;
   Statement stmt = null;
   ResultSet rs = null;
   String strSQL = null;

   if(informationnum>0)
      strSQL = "SELECT top "+informationnum+" * FROM information where infoType = '" + catalogID + "' order by createdate desc";
    else
      strSQL = "SELECT  * FROM information where infoType = '" + catalogID + "' order by createdate desc";
   try {
     dbc = new DBConnection();
     conn = dbc.getDBConnection();
     stmt = conn.createStatement();
     rs = stmt.executeQuery(strSQL);
     while (rs.next()) {

           Information model = new Information();
           model.setInformationID(rs.getString("informationID"));
           model.setInfoTitle(rs.getString("infoTitle"));
           model.setInfotype(rs.getString("infotype"));
           model.setInfoabstract(rs.getString("infoabstract"));
           model.setKeywords(rs.getString("keywords"));
           model.setComefrom(rs.getString("comefrom"));
           model.setAuthor(rs.getString("author"));
           model.setContent(rs.getString("content"));
           model.setCreatedate(rs.getString("createdate"));
           model.setModifydate(rs.getString("modifydate"));
           model.setStoreID(rs.getString("storeID"));
           model.setCountnum(rs.getInt("countnum"));
           informationList.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 informationList;
 }

 //获得一个菜谱的详细信息
 public Information getOneInformationByID(String informationID)
 {
   DBConnection dbc = null;
   Connection conn = null;
   Statement stmt = null;
   ResultSet rs = null;
   Information model = null;
   String strSQL = " SELECT * FROM information  where informationID='"+informationID+"'";
   try
   {
     dbc = new DBConnection();
     conn = dbc.getDBConnection();
     stmt = conn.createStatement();
     rs = stmt.executeQuery(strSQL);
     if (rs.next())
     {
       model = new Information();
       model.setInformationID(rs.getString("informationID"));
       model.setInfoTitle(rs.getString("infoTitle"));
       model.setInfotype(rs.getString("infotype"));
       model.setInfoabstract(rs.getString("infoabstract"));
       model.setKeywords(rs.getString("keywords"));
       model.setComefrom(rs.getString("comefrom"));
       model.setAuthor(rs.getString("author"));
       model.setContent(rs.getString("content"));
       model.setCreatedate(rs.getString("createdate"));
       model.setModifydate(rs.getString("modifydate"));
       model.setStoreID(rs.getString("storeID"));
       model.setCountnum(rs.getInt("countnum"));
     }
    }
   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 void addCountView(String informationID) {
   int nRet = 0;
   DBConnection dbc = null;
   Connection conn = null;
   Statement stmt = null;
   ResultSet rs = null;
   Information model = null;
   model = this.getOneInformationByID(informationID);
   int countnum = model.getCountnum();
   countnum = countnum + 1;
   String strSQL = " UPDATE information set countnum = " + countnum +
       " where informationID='" + informationID + "'";
   try {
     dbc = new DBConnection();
     conn = dbc.getDBConnection();
     stmt = conn.createStatement();
     nRet = stmt.executeUpdate(strSQL);

   }
   catch (Exception e) {
     nRet = -1;
     e.printStackTrace();
     System.out.println("\n" + e.toString() + "添加文章被浏览次数错误" + strSQL); /////错误处理！
   }
   finally {
     try {
       if (rs != null)
         rs.close();
       if (stmt != null)
         stmt.close();
       if (conn != null)
         dbc.closeDBConnection(conn);
     }
     catch (SQLException e) {}
   }
 }

 //最新的六个信息
 public List getNewInformation(String catalogID,String areaID)
 {
   List informationList = new ArrayList();

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

   String strSQL = "SELECT top 5 * FROM information where infotype in(select catalogID from catalog where pararentID = '" +
       catalogID + "') and storeID IN (SELECT storeID FROM store WHERE streetID LIKE '%"+areaID+"%') order by informationID DESC";

   try {
     dbc = new DBConnection();
     conn = dbc.getDBConnection();
     stmt = conn.createStatement();
     rs = stmt.executeQuery(strSQL);
     while (rs.next()) {
       Information model = new Information();
       model.setInformationID(rs.getString("informationID"));
       model.setInfoTitle(rs.getString("infoTitle"));
       model.setInfotype(rs.getString("infotype"));
       model.setInfoabstract(rs.getString("infoabstract"));
       model.setKeywords(rs.getString("keywords"));
       model.setComefrom(rs.getString("comefrom"));
       model.setAuthor(rs.getString("author"));
       model.setContent(rs.getString("content"));
       model.setCreatedate(rs.getString("createdate"));
       model.setModifydate(rs.getString("modifydate"));
       model.setStoreID(rs.getString("storeID"));
       model.setCountnum(rs.getInt("countnum"));

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