membermanage_dao.java

这是一个Struts网上商城(图书)的管理系统源代码.

package com.bookshop.dao;
import java.sql.*;
import java.util.*;

import com.bookshop.db.*;
import com.bookshop.dto.*;
public class memberManage_dao {
	
	//查询所有用户信息
	public ArrayList member_select()
	{
		Connection conn=DBConnection.getConnection();
		Statement st=null;
		ResultSet rs=null;
		ArrayList list =new ArrayList();
		String sql="";
		try{
			sql="select * from tb_member order by id desc";
			st=conn.createStatement();
			rs=st.executeQuery(sql);
			
			while(rs.next()){
			member_dto dto=new member_dto();
			dto.setId(rs.getString("id"));
			dto.setUsername(rs.getString("username"));
			dto.setTruename(rs.getString("truename"));
			dto.setAddress(rs.getString("address"));
			dto.setTel(rs.getString("tel"));
			dto.setEmail(rs.getString("email"));
			dto.setAmount(rs.getFloat("amount"));
			dto.setFreeze(rs.getString("freeze"));
			
			list.add(dto);
			}
		}catch(SQLException e){e.printStackTrace();}
		
		finally{
			
			try{
				
				rs.close();
				st.close();
				conn.close();
			}catch(SQLException e){e.printStackTrace();}
		}	
		//System.out.print("dffffffffffffffffffff______________");
		return list;
	}
	//冻结用户
	public int close_member(String id,int free)
	{
		
		Connection conn=DBConnection.getConnection();
		Statement st=null;
		int n=0;
		String sql="";
		try{
			
			sql="update tb_member set freeze="+free+" where id="+id;
			st=conn.createStatement();
			n=st.executeUpdate(sql);
		}catch(SQLException e){e.printStackTrace();}
		finally{
			
			try{
				
				st.close();
				conn.close();
			}catch(SQLException e){e.printStackTrace();}
		}
		return n;
	}
//	求分页最大页	
	public int maxpage(int pagesize)
	 {
	      
	      Connection conn=DBConnection.getConnection();
	      ResultSet r = null;
	      PreparedStatement pstm = null;
	      int rowcount=0;   //总记录数
	      int maxpage=0;
	     
	      
	      String countSql="select count(*) as t  from tb_member";
	     try {
	    	
	         pstm = conn.prepareStatement(countSql);
	         r = pstm.executeQuery();
	         while (r.next()) {
	             rowcount = r.getInt("t");
	           
		         if(rowcount%pagesize!=0)
		         {
		           maxpage=rowcount/pagesize+1;
		         }
		         else
		         {
		        	 maxpage=rowcount/pagesize;
		         }
	         }
	     }catch (SQLException ex) {
	            ex.printStackTrace();
	        } finally {          
	        	 try {
	        		 r.close();
	        		 pstm.close();
	                 conn.close();
	             } catch (SQLException ex1) { }                              
	        }
	      return maxpage;
	 }
	    
//求当前页码
	public ArrayList sel_member(int pagesize, int curpage) {
		 ArrayList list = new ArrayList();
	     Connection conn = DBConnection.getConnection();
	    
	     ResultSet rs = null;
	     PreparedStatement pstmt=null;
	     String selectSQL = "select * from tb_member";	     
	     int tip =pagesize * (curpage - 1);  //定位到指定的记录
	     
	     try {
	       pstmt = conn.prepareStatement(selectSQL, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
	     rs = pstmt.executeQuery();
	     System.out.print(tip);
	       if (tip <= 0) {
	           rs.beforeFirst();
	       } else {
	           if (!rs.absolute(tip)) {
	               rs.beforeFirst();
	           }
	       }
	       for (int i = 1; rs.next() && i <= pagesize; i++) {
				member_dto dto=new member_dto();
				dto.setId(rs.getString("id"));
				dto.setUsername(rs.getString("username"));
				dto.setTruename(rs.getString("truename"));
				dto.setAddress(rs.getString("address"));
				dto.setTel(rs.getString("tel"));
				dto.setEmail(rs.getString("email"));
				dto.setAmount(rs.getFloat("amount"));
				dto.setFreeze(rs.getString("freeze"));
				
				list.add(dto);
	      }

	   
	        } catch (SQLException ex) {
	            ex.printStackTrace();
	        } finally {          
	        	 try {
	        		 rs.close();
	        		 pstmt.close();
	                 conn.close();
	             } catch (SQLException ex1) { }                              
	        }
	        return list;
			}

}