informationdao.java

一个完整的网络订餐系统

package com.information.dao;

import java.sql.*;
import java.util.*;
import com.common.*;
import com.util.*;
import com.information.form.Information;
import com.information.form.InformationSearchResult;
import com.information.rule.InformationRule;


public class InformationDao {


  public InformationDao() {
  }

  //增加信息
  public int inserInformationToDb(Information 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 informationID = model.getInformationID();
    String infoTitle = model.getInfoTitle();
    String infotype = model.getInfotype();
    String infoabstract = model.getInfoabstract();
    String keywords = model.getKeywords();
    String comefrom = model.getComefrom();
    String author = model.getAuthor();
    String content = model.getContent();
    String storeID = model.getStoreID();


    strSQL = " INSERT information(informationID,infoTitle,infotype,infoabstract,keywords,comefrom,"+
                             "author, content, createdate, modifydate,storeID) values('" +
                            informationID+"','"+infoTitle+"','"+infotype+"','"+infoabstract+"','"+
                            keywords+"','"+comefrom+"','"+author+"','"+content+"',getdate(),getdate(),'"+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 updatInformationToDb(Information 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 informationID = model.getInformationID();
    String infoTitle = model.getInfoTitle();
    String infotype = model.getInfotype();
    String infoabstract = model.getInfoabstract();
    String keywords = model.getKeywords();
    String comefrom = model.getComefrom();
    String author = model.getAuthor();
    String content = model.getContent();
    String storeID = model.getStoreID();

    strSQL = "update information set infoTitle='" + infoTitle +
        "',infotype='" + infotype + "',infoabstract='" + infoabstract +
        "', keywords='" + keywords + "', comefrom='"+comefrom+"',author='" +
        author + "',content='"+content+"'  where informationID='" + informationID + "'";

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

     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 InformationSearchResult getnformationSearch(InformationRule tempPsr)
 {
   int totalRecNum = 0;
   List informationList = new ArrayList();
   InformationSearchResult bsrt = new InformationSearchResult();

   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 informationTitle = StrUtility.replacenull(tempPsr.getInformationTitle());
   String infoType  = StrUtility.replacenull(tempPsr.getInfoType());
   String keywords  = StrUtility.replacenull(tempPsr.getKeywords());
   String infoabstract  = StrUtility.replacenull(tempPsr.getInfoabstract());
   String modDate1  = StrUtility.replacenull(tempPsr.getModDate1());
   String modDate2  = StrUtility.replacenull(tempPsr.getModDate2());
   String storeID = StrUtility.replacenull(tempPsr.getStoreID());


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

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

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

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

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

   System.out.println("strSQL = " + strSQL);
   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++) {
         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);
         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.setInformationList(informationList);
   bsrt.setTotalRecNum(totalRecNum);
   return bsrt;
 }

//查菜谱记录
 public List getInformationSearch(InformationRule tempPsr)
 {
   int totalRecNum = 0;
   List informationList = new ArrayList();

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

   String strSQL = null;
   String strSQLForCount = null;


   String orderStr = StrUtility.replacenull(tempPsr.getOrderStr());


   String informationTitle = StrUtility.replacenull(tempPsr.getInformationTitle());
   String infoType  = StrUtility.replacenull(tempPsr.getInfoType());
   String keywords  = StrUtility.replacenull(tempPsr.getKeywords());
   String infoabstract  = StrUtility.replacenull(tempPsr.getInfoabstract());
   String modDate1  = StrUtility.replacenull(tempPsr.getModDate1());
   String modDate2  = StrUtility.replacenull(tempPsr.getModDate2());
   String storeID = StrUtility.replacenull(tempPsr.getStoreID());


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

   if (! ( ("").equals(informationTitle)) && informationTitle != null) {
     whereStr = "infoTitle LIKE '%" +
         StrUtility.replaceString(informationTitle, "'", "''") + "%'  OR ";
   }
   if (! ( ("-1").equals(infoType)) && infoType != null) {
     whereStr = whereStr + " infoType LIKE '%" +
         StrUtility.replaceString(infoType, "'", "''") + "%'  OR ";
   }

   if (! ( ("").equals(keywords)) && keywords != null) {