memberdao.java

一个完整的网络订餐系统

package com.member.dao;

import java.sql.*;
import java.util.*;
import com.common.*;
import com.util.*;
import com.member.form.Member;
import com.member.form.MemberSearchResult;
import com.member.rule.MemberRule;

import com.util.security.Encoder;

public class MemberDao {


  public MemberDao() {
  }

  //插入会员
 public int inserMemberToDb(Member 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 memberID =model.getMemberID();
    String memberAccount =model.getMemberAccount();
    String memberPass = model.getMemberPass();
    String memberGrade = model.getMemberGrade();
    int memberPoint = model.getMemberPoint();
    String realName = model.getRealName();
    String gender = model.getGender();
    String birthday = model.getBirthday();
    String company = model.getCompany();
    String department = model.getDepartment();
    String address = model.getAddress();
    String postCode = model.getPostCode();
    String province = model.getProvince();
    String city = model.getCity();
    String phone = model.getPhone();
    String MSN = model.getMSN();
    String email = model.getEmail();
    String foodhabit = model.getFoodhabit();


    strSQL = " INSERT member(memberID,memberAccount,memberPass,memberGrade,"+
                             "memberPoint, realName, gender, birthday, company,"+
                            "department, address, postCode, province, city,"+
                            "phone, MSN, email, foodhabit, regDate, modifyDate) values('" +
                            memberID+"','"+memberAccount+"','"+memberPass+"','"+memberGrade+"',"+
                            memberPoint+",'"+realName+"','"+gender+"','"+birthday+"','"+company+"','"+
                            department+"','"+address+"','"+postCode+"','"+province+"','"+city+"','"+
                            phone+"','"+MSN+"','"+email+"','"+foodhabit+"',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 updatMemberToDb(Member 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 memberID = model.getMemberID();
    String memberAccount = model.getMemberAccount();
    String memberPass = model.getMemberPass();
    String realName = model.getRealName();
    String gender = model.getGender();
    String birthday = model.getBirthday();
    String company = model.getCompany();
    String department = model.getDepartment();
    String address = model.getAddress();
    String postCode = model.getPostCode();
    String province = model.getProvince();
    String city = model.getCity();
    String phone = model.getPhone();
    String MSN = model.getMSN();
    String email = model.getEmail();
    String foodhabit = model.getFoodhabit();

    strSQL = "update member set memberAccount='" + memberAccount +
        "',memberPass='" + memberPass + "',realName='" + realName +
        "', gender='" + gender + "', birthday='" +
        birthday + "', company='" + company + "'," +
        "department='" + department + "', address='" + address +
        "', postCode='" + postCode + "', province='" + province + "', " +
        "city='" + city + "',phone='" + phone + "', MSN='" + MSN +
        "', email='" + email + "', foodhabit='" + foodhabit + "' " +
        ", modifyDate=getdate() where memberID='" + memberID + "'";

    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 updatMemberToDb(String memberID,String memberGrade,int memberPoint,float prePaid)
{
  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 member set memberGrade='" + memberGrade +"'," +
             "memberPoint="+memberPoint+",prePaid="+prePaid+",modifyDate=getdate()"+
             " where memberAccount='" + memberID + "'";

    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 setMemberMoneyToDb(float fMoney,String memberID)
{
  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 member set prePaid=" + fMoney +
              "' where memberID='" + memberID + "'";

    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 float getBalance(String memberID)
{
  DBConnection dbc = null;
  Connection conn = null;
  Statement stmt = null;
  ResultSet rs = null;
  float fPrepaid = 0.0f;
  String strSQL = " SELECT prePaid FROM member  where memberAccount='"+memberID+"'";

  try
  {
    dbc = new DBConnection();
    conn = dbc.getDBConnection();
    stmt = conn.createStatement();
    rs = stmt.executeQuery(strSQL);
    if (rs.next())
    {
      fPrepaid = rs.getFloat("prePaid");
    }
   }
  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 fPrepaid;
}

//删除会员
public int deleteOneMember(String memberID)
 {
   int nRet;
   nRet = 0;
   DBConnection dbc = null;
   Connection conn = null;
   Statement stmt = null;
   try {
     dbc = new DBConnection();
     conn = dbc.getDBConnection();
     String strSQL = "DELETE FROM member WHERE memberID='" + memberID +"'";

     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 MemberSearchResult getMemberSearch(MemberRule tempPsr)
 {
   int totalRecNum = 0;
   List memberList = new ArrayList();
   MemberSearchResult bsrt = new MemberSearchResult();

   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 memberAccount = StrUtility.replacenull(tempPsr.getMemberAccount());
   String realName = StrUtility.replacenull(tempPsr.getRealName());
   String foodhabit =  StrUtility.replacenull(tempPsr.getFoodhabit());
   String gender =  StrUtility.replacenull(tempPsr.getGender());
   String memberGrade =  StrUtility.replacenull(tempPsr.getMemberGrade());

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



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

   if (! ( ("").equals(memberAccount)) && memberAccount != null) {
     whereStr = "memberAccount LIKE '%" +
         StrUtility.replaceString(memberAccount, "'", "''") + "%'  AND ";
   }
   if (! ( ("").equals(realName)) && realName != null) {
     whereStr = whereStr + " realName LIKE '%" +
         StrUtility.replaceString(realName, "'", "''") + "%'  AND ";
   }
   if (! ( ("").equals(foodhabit)) && foodhabit != null) {
     whereStr = whereStr + " foodhabit LIKE '%" +
         StrUtility.replaceString(foodhabit, "'", "''") + "%'  AND ";
   }
   if (! ( ("").equals(gender)) && gender != null) {
     whereStr = whereStr + " gender LIKE '%" +
         StrUtility.replaceString(gender, "'", "''") + "%'  AND ";
   }

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


   if (! ( ("").equals(regDate1)) && regDate1 != null) {
     whereStr = whereStr + " regDate>= '" + regDate1 + "'  AND ";
   }
   if (! ( ("").equals(regDate2)) && regDate2 != null) {
     whereStr = whereStr + " regDate<='" + regDate2 + "'  AND ";
   }
   int lenOfWhereStr = whereStr.length();
   if (lenOfWhereStr - 4 >= 0) {