Z02的DAO类

package com.pz.dao;

import java.sql.*;
import java.util.*;

import com.pz.bean.User;
import com.pz.business.user.UserActionForm;
import com.pz.util.Database;

public class UserDAO {

 private Connection con=null;

 public UserDAO(){
  try {
   con = Database.getConnection();
  } catch (Exception e) {
   e.printStackTrace();
  }  
 }
 
   /**
    * 分页相关代码
    */
   private int rowCount;  //总行数
   private int pageCount; //总页数
   private int length;    //限定每页显示长度 （10行）
   private String pagestr;    //带有超链接的页数，....  (一个字符串)
   private String conditionStr = "";   //翻页时， 保存相同的查询条件在conditionStr中
 
   private String witchAction="";
  
   public int getLength() {
     return (this.length);
   }
   public void setLength(int length) {
     this.length = length;
   }
   public String getWitchAction() {
  return witchAction;
   }
   public void setWitchAction(String witchAction) {
  this.witchAction = witchAction;
   }
  
   public void setConditionStr(String conditionStr) {
  this.conditionStr = conditionStr;
   }
   public String getConditionStr() {
   return conditionStr;
   }
  
   /**
    * 分页中下标字符串的获得
    * @param ipage
    * @return
    */
   public String getPagestr(int ipage) {
      String strPage = "";
 
      if (getLength() > 0) {
        strPage += "共";
        strPage += String.valueOf(rowCount);
        strPage += "条记录，共";
        strPage += String.valueOf(pageCount);
        strPage += "页，当前是第";
        strPage += String.valueOf(ipage);
        strPage += "页，      ";
 
        int istart, iend;   //能看到的页数  （如： 5，6，7，8，9）
        istart = ipage - 5;    //如果当前页为7， 则开始页为 2
        if (istart < 0) {
          istart = 0;     //如果开始页被计算出来小于0，就从0+1页开始。  for...
        }
        iend = istart + 10;  //总共能显示10页
        if (iend > pageCount) {
          iend = pageCount;      //如果最后一页计算出来，超过了总页数，就把总页数设为最后一页。
        }
        istart = iend - 10;  //防止结束页不是计算出来的页，而是指定的最后页   这是需要重设置开始页 
        if (istart < 0) {
          istart = 0;
        }
 
        for (int i = istart; i < iend; i++) {   //从开始页到结束页，分别设置超链接
          strPage +=
              "<a href='"+witchAction+"?page=";  //witchAction是指定在超链接中的Action
          strPage += String.valueOf(i + 1);
          strPage += conditionStr;
          strPage += "'>";
          strPage += String.valueOf(i + 1);
          strPage += "</a>";
          strPage += "  ";
        }
      }
 
      this.pagestr = strPage;  //带有超链接的页数，...(一个字符串)  获得后同时赋值给： ExamineeDAO  private String pagestr;
      return strPage;
    }

      /**
       * 分页查询
       * @param user_arg
       * @param ipage
       * @return
       * @throws SQLException
       */                                                  // 页码
   public Collection paginationSearch(User user_arg, int ipage) throws SQLException {
   
   PreparedStatement ps = null;
      ResultSet rs = null;
      User user = null;
     
      Collection list = new ArrayList();
 
      String user_name=user_arg.getUser_name();   
   String user_dc = user_arg.getUser_dc();
      String user_bm = user_arg.getUser_bm();                        
      String user_qx = user_arg.getUser_qx(); 
     
      String sqlstr=null;
      sqlstr="select * from t_user where 1=1 ";
     
      if (user_name!=null&&!user_name.equals("")){               //当查询条件中用户名有输入时
     sqlstr=sqlstr+"and user_name like '"+user_name+"%' ";      //SQL语句页加上这条件
     conditionStr += ("&user_name=" + user_name);              //超链接中的条件也加上
    }
      if (user_dc!=null&&!user_dc.equals("")){
        sqlstr=sqlstr+"and user_dc = '"+user_dc+"' ";
        conditionStr += ("&user_dc=" + user_dc);
      }
      if (user_bm!=null&&!user_bm.equals("")){
        sqlstr=sqlstr+"and user_bm = '"+user_bm+"' ";
        conditionStr += ("&user_bm=" + user_bm);
      }
      if (user_qx!=null&&!user_qx.equals("")){
        sqlstr=sqlstr+"and user_qx = '"+user_qx+"' ";
        conditionStr += ("&user_qx=" + user_qx);
      }

      sqlstr = sqlstr + "ORDER by user_bm";

      try {   
          ps = con.prepareStatement(sqlstr,
                                    ResultSet.TYPE_SCROLL_SENSITIVE,
                                    ResultSet.CONCUR_UPDATABLE);
                rs=ps.executeQuery();
  
          if (false == rs.last()) {    //如果结果集指针到了最后一行
           rowCount = 0;
           pageCount = 0;
           ipage = 0;
           return list;
          }
  
          this.rowCount = rs.getRow();   // private int rowCount;  检索当前行编号。 (查询后的最后一行)
          int offset = 1;
          int pagesize = getLength(); //examineeDAO.setLength(100); //ExamineeDAO属性：private int length; （每页长度）
          if (getLength() < 1) {
           pagesize = rowCount;
           pageCount = 1;
          }
          else {    //得到 总页数 ---总行数/每页行数 +   ( (总行数%每页行数 ) > 0 ? 1 : 0)    //整除后  有余的部分就  + 1
           pageCount = rowCount / getLength() + ( (rowCount % getLength()) > 0 ? 1 : 0);
          
           offset = (ipage - 1) * getLength() + 1;   //记录集指针定位
           if (offset < 1) {
             offset = 1;
           }
   
           if (offset > rowCount) {
             offset = rowCount;
           }
          }
          rs.absolute(offset);//记录集指针定位
                           //每页限定数  并         //总行数
          for (int i = 0; i < pagesize && offset < rowCount + 1; i++, offset++) {
           user = new User();
           user.setUser_id(rs.getInt("user_id"));       
           user.setUser_name(rs.getString("user_name"));
           user.setUser_dc(rs.getString("user_dc"));
           user.setUser_pw(rs.getString("user_pw"));
           user.setUser_bm(rs.getString("user_bm"));
           user.setUser_qx(rs.getString("user_qx"));
           rs.next();
           list.add(user);
          }
      } catch (SQLException ex) {
            ex.printStackTrace();
      } finally {
         try {
     rs.close();
     rs = null;         
     ps.close();
           ps = null;
           con.close();
           con = null;
         } catch (SQLException ex1) {
           ex1.printStackTrace();
         }
      }
      return list;
 }

  
   /**
    * 添加用户
    * @param user
    */
   public void addUser(User user){
   PreparedStatement ps=null;
   try {
    ps = con.prepareStatement("insert into t_user (user_name,user_dc,user_bm,user_pw,user_qx) values (?,?,?,?,?)");
    ps.setString(1, user.getUser_name());
    ps.setString(2, user.getUser_dc());
    ps.setString(3, user.getUser_bm());
    ps.setString(4, user.getUser_pw());
    ps.setString(5, user.getUser_qx());
      ps.executeUpdate();
   } catch (SQLException e) {
    e.printStackTrace();
   }finally{
    try {
     ps.close();
     ps = null;
     con.close();
     con=null;
    } catch (SQLException e1) {
     e1.printStackTrace();
    }   
    
   }  
  }
  
   /**
    * 修改用户
    * @param user
    */
   public void editUser(User user){
     
    PreparedStatement ps=null;
    try {
     ps = con
     .prepareStatement("update t_user set user_name=?,user_dc=?,user_bm=?,user_qx=? where user_id=?");
     ps.setString(1, user.getUser_name());
     ps.setString(2, user.getUser_dc());
     ps.setString(3, user.getUser_bm());
     ps.setString(4, user.getUser_qx());
     ps.setLong(5, user.getUser_id());
     ps.executeUpdate();
    } catch (SQLException e) {
     e.printStackTrace();
    } finally{
     try {
      ps.close();
      ps = null;
      con.close();
      con = null;
     } catch (SQLException e1) {
      e1.printStackTrace();
     }   
    }   
   }
  
   /**
    * 单个用户的删除
    * @param id
    */
   public void deleteUserByKey(int id){
      
      PreparedStatement ps=null;
   try {
    ps=con.prepareStatement("delete from t_user where user_id=?");
    ps.setInt(1, id);
    ps.executeUpdate();
   } catch (SQLException e) {
    e.printStackTrace();
   }finally{
    try {
     ps.close();
     ps = null;
     con.close();
     con = null;
    } catch (SQLException e1) {
     e1.printStackTrace();
    }   
   }
     }
  
   /**
    * 多个用户的删除
    * @param userlist
    * @throws SQLException
    */
   public void deleteListUser(String [] userlist) throws SQLException{

    String ids="";
    for(int i=0;i<userlist.length;i++){
     ids+="'"+userlist[i]+"'";
     if (i<userlist.length-1){
      ids+=",";
     }
    }
     
    Statement st=null;           //delete from t_user where user_id in ('44','45') ....
    try {
     st = con.createStatement();
     st.executeUpdate("delete from t_user where user_id in ("+ids+")");
    } catch (SQLException e) {
     e.printStackTrace();
    } finally{
     con.close();
     con=null;
     st.close();
     st=null;
    }    
  }  
 
   /**
    * 根据用户ID得到用户信息
    * @return
    */
   public User getUserByKey(int id){
     
     PreparedStatement ps=null;
     ResultSet rs=null;
      User user=new User();
   try {
    ps=con.prepareStatement("select * from t_user where user_id=?");
    ps.setInt(1, id);
    rs=ps.executeQuery();
    while(rs.next()){
     user.setUser_id(rs.getInt("user_id"));
     user.setUser_name(rs.getString("user_name"));
     user.setUser_dc(rs.getString("user_dc"));
     user.setUser_bm(rs.getString("user_bm"));
     user.setUser_pw(rs.getString("user_pw"));
     user.setUser_qx(rs.getString("user_qx"));
    }
   } catch (SQLException e) {
    e.printStackTrace();
   }finally{
    try {
     rs.close();
     rs=null;
     ps.close();
     ps = null;
     con.close();
     con = null;
    } catch (SQLException e1) {
     e1.printStackTrace();
    }   
   }
   return user;
   }
  
 
 /**
  * 判断用户是否存在
  * @return
  */
   public boolean isLogin(String name,String password){
   
   boolean result=false;
   String pw=null;

     PreparedStatement ps=null;
     ResultSet rs=null;
     
   try {
    ps=con.prepareStatement("select user_pw from t_user where user_name=?");
    ps.setString(1, name);
    rs=ps.executeQuery();
    while(rs.next()){
     pw=rs.getString(1);
    }
    if ((pw!=null)&&pw.trim().equals(password)){
      result=true;
    }else{
     result=false;
    }
   } catch (SQLException e) {
    e.printStackTrace();
   }finally{
    try {
     if (rs!=null){
      rs.close();
      rs=null;
     }
     if (ps!=null) {
      ps.close();
      ps = null;
     }     
     if (con!=null) {
      con.close();
      con = null;
     }     
    } catch (SQLException e1) {
     e1.printStackTrace();
    }   
   }
    
   return result;
  }

}