storedao.java

来自「一个完整的网络订餐系统」· Java 代码 · 共 611 行 · 第 1/2 页

package com.store.dao;

import java.sql.*;
import java.util.*;
import com.common.*;
import com.util.*;
import com.store.form.Store;
import com.store.form.StoreResult;
import com.store.rule.StoreRule;

public class StoreDao {

  public StoreDao() {
  }

  //插入会员
 public int insertStoreToDb(Store 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 storeID = model.getStoreID();
    String adminname = model.getAdminname();
    String adminpassword = model.getAdminpassword();
    String storeName = model.getStoreName();
    String province = model.getProvince();
    String area = model.getArea();
    String relationer = model.getRelationer();
    String phone = model.getPhone();
    String fax = model.getFax();
    String email = model.getEmail();
    String address = model.getAddress();
    String postcode = model.getPostcode();
    String netadderss = model.getNetadderss();
    String location = model.getLocation();
    String memo = model.getMemo();
    int pointer = model.getPointer();
    int starNum = model.getStarNum();

    strSQL = " INSERT store(storeID,adminname,adminpassword,storeName,"+
                             "province, area, relationer, phone, fax,"+
                            "email, address, postcode, netadderss, location,"+
                            "memo, pointer, starNum, createdate, modifydate) values('" +
                            storeID+"','"+adminname+"','"+adminpassword+"','"+storeName+"','"+
                            province+"','"+area+"','"+relationer+"','"+phone+"','"+fax+"','"+
                            email+"','"+address+"','"+postcode+"','"+netadderss+"','"+location+"','"+
                            memo+"',"+pointer+","+starNum+",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 updatStoreToDb(Store 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 storeID = model.getStoreID();
    String adminname = model.getAdminname();
    String adminpassword = model.getAdminpassword();
    String storeName = model.getStoreName();
    String province = model.getProvince();
    String area = model.getArea();
    String relationer = model.getRelationer();
    String phone = model.getPhone();
    String fax = model.getFax();
    String email = model.getEmail();
    String address = model.getAddress();
    String postcode = model.getPostcode();
    String netadderss = model.getNetadderss();
    String location = model.getLocation();
    String memo = model.getMemo();
    int pointer = model.getPointer();
    int starNum = model.getStarNum();


    strSQL = "update store set adminname='" + adminname +
        "',adminpassword='" + adminpassword + "',storeName='" + storeName +
        "', province='" + province + "', area='" +
        area + "', relationer='" + relationer + "'," +
        "phone='" + phone + "', fax='" + fax +
        "', email='" + email + "', address='" + address + "', " +
        "postcode='" + postcode + "',netadderss='" + netadderss + "', location='" + location +
        "', memo='" + memo + "', pointer=" + pointer + ",starNum=" +starNum +
        ", modifyDate=getdate() where storeID='" + 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 updateStoreToDb(String storeID,String pointer,int starNum)
{
  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();
    strSQL = "update store set pointer=" + pointer +"," +
             "starNum="+starNum+
             " where storeID='" + 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 deleteOneStore(String storeID)
 {
   int nRet;
   nRet = 0;
   DBConnection dbc = null;
   Connection conn = null;
   Statement stmt = null;
   try {
     dbc = new DBConnection();
     conn = dbc.getDBConnection();
     String strSQL = "DELETE FROM store WHERE storeID='" + storeID +"'";

     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 StoreResult getStoreSearch(StoreRule tempPsr)
 {
   int totalRecNum = 0;
   List storeList = new ArrayList();
   StoreResult bsrt = new StoreResult();

   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 storeName  = StrUtility.replacenull(tempPsr.getStoreName());
    String location = StrUtility.replacenull(tempPsr.getLocation());
    String province = StrUtility.replacenull(tempPsr.getProvince());
    String area = StrUtility.replacenull(tempPsr.getArea());

   String regDate1 = StrUtility.replacenull(tempPsr.getRegDate1());
   String regDate2 = StrUtility.replacenull(tempPsr.getRegDate2());

   int starNum = tempPsr.getStarNum();


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

   if (! ( ("").equals(storeName)) && storeName != null) {
     whereStr = "storeName LIKE '%" +
         StrUtility.replaceString(storeName, "'", "''") + "%'  AND ";
   }
   if (! ( ("").equals(location)) && location != null) {
     whereStr = whereStr + " location LIKE '%" +
         StrUtility.replaceString(location, "'", "''") + "%'  AND ";
   }
   if (! ( ("").equals(province)) && province != null) {
     whereStr = whereStr + " province LIKE '%" +
         StrUtility.replaceString(province, "'", "''") + "%'  AND ";
   }
   if (! ( ("").equals(area)) && area != null) {
     whereStr = whereStr + " area LIKE '%" +
         StrUtility.replaceString(area, "'", "''") + "%'  AND ";
   }

   whereStr = whereStr + " starNum = "+starNum +"  AND ";

   if (! ( ("").equals(regDate1)) && regDate1 != null) {
     whereStr = whereStr + " createdate>= '" + regDate1 + "'  AND ";
   }
   if (! ( ("").equals(regDate2)) && regDate2 != null) {
     whereStr = whereStr + " createdate<='" + regDate2 + "'  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++) {
         Store model = new Store();