dbconnectbean.java

电子商务网站使用MVC模式B/S结构功能不是很全适合初学者看

package Common;
import java.io.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.Hashtable;

public class DBConnectBean{
	String source="";
	String driver="";
	String user="";
	String password="";
	public DBConnectBean(String driver,String source,String user,String password){
		this.driver=driver;
		this.source=source;
	}
	public Connection getConnect(){
		Connection con=null;
		try{
			Class.forName(driver);
			con=DriverManager.getConnection(source,user,password);
		}catch(Exception w){
			System.out.println("connect error "+w.getMessage());
		}
		return con;
	}
	public ArrayList getAllRecord(String sql){
		Connection con=null;
		Statement stmt=null;
		ResultSet rs=null;
		ResultSetMetaData meta=null;
		ArrayList list=new ArrayList();
		
		try{
			con=this.getConnect();
			stmt=con.createStatement();
			rs=stmt.executeQuery(sql);
			meta=rs.getMetaData();
			while(rs.next()){
				//be careful this one
				Hashtable table=new Hashtable();
				int column=meta.getColumnCount();
				for(int i=1;i<=column;i++){
					table.put(meta.getColumnLabel(i).toUpperCase(),rs.getObject(i));
				}
				list.add(table);
			}
			rs.close();
			rs=null;
			stmt.close();
			stmt=null;
		}catch(Exception w){
			System.out.println("getOneRecord error "+w.getMessage());
		}finally{
			try{
				if(con!=null){
					con.close();
					con=null;
				}
			}catch(Exception q){
				System.out.println("close error "+q.getMessage());
				
			}
		}
		return list;
	}
	
	//分页显示，只需要传参数就可以了,这里只负责参数和页码，不会出错的，都在调用此方法的对象中做好手脚了
	public ArrayList getPageRecord(String sql,int pageSize,int pageNumber){
		Connection con=null;
		Statement stmt=null;
		ResultSet rs=null;
		ResultSetMetaData meta=null;
		ArrayList list=new ArrayList();
		
		try{
			con=this.getConnect();
			stmt=con.createStatement();
			rs=stmt.executeQuery(sql);
			meta=rs.getMetaData();
			int rowCount=meta.getColumnCount();
		//	//System.out.println(rowCount+"????");
			//int pages=rowCount%pageSize==0?rowCount/pageSize:rowCount/pageSize+1;
			
			for(int i=0;i<=(pageNumber-1)*pageSize-1;i++)
				rs.next();
				
			for(int j=0;j<=pageSize-1;j++)
				if(rs.next()){
				//be careful this one
					Hashtable table=new Hashtable();
					int column=meta.getColumnCount();
					for(int i=1;i<=column;i++){
						table.put(meta.getColumnLabel(i).toUpperCase(),rs.getObject(i));
					}
					list.add(table);
				}	
			////System.out.println("共"+pages+"页，   每页有"+pageSize+"个，  list大小是"+rowCount);
			rs.close();
			rs=null;
			stmt.close();
			stmt=null;
		}catch(Exception w){
			System.out.println("getOneRecord error "+w.getMessage());
		}finally{
			try{
				if(con!=null){
					con.close();
					con=null;
				}
			}catch(Exception q){
				System.out.println("close error "+q.getMessage());
				
			}
		}
		return list;
	}
	

	public ArrayList getWantedRecord(String sql,int num){
		Connection con=null;
		Statement stmt=null;
		ResultSet rs=null;
		ResultSetMetaData meta=null;
		ArrayList list=new ArrayList();
		
		try{
			con=this.getConnect();
			stmt=con.createStatement();
			rs=stmt.executeQuery(sql);
			meta=rs.getMetaData();
				
			for(int j=0;j<num;j++)
				if(rs.next()){
					Hashtable table=new Hashtable();
					int column=meta.getColumnCount();
					for(int i=1;i<=column;i++){
						table.put(meta.getColumnLabel(i).toUpperCase(),rs.getObject(i));
					}
					list.add(table);
				}	
			rs.close();
			rs=null;
			stmt.close();
			stmt=null;
		}catch(Exception w){
			System.out.println("getOneRecord error "+w.getMessage());
		}finally{
			try{
				if(con!=null){
					con.close();
					con=null;
				}
			}catch(Exception q){
				System.out.println("close error "+q.getMessage());
				
			}
		}
		return list;
	}

	
	public Hashtable getOneRecord(String sql){
		Connection con=null;
		Statement stmt=null;
		ResultSet rs=null;
		ResultSetMetaData meta=null;
		Hashtable table=new Hashtable();
		try{
			con=this.getConnect();
			stmt=con.createStatement();
			rs=stmt.executeQuery(sql);
			meta=rs.getMetaData();
			//System.out.print("here wo go");
			if(rs.next()){
				int column=meta.getColumnCount();
				for(int i=1;i<=column;i++){
					table.put(meta.getColumnLabel(i).toUpperCase(),rs.getObject(i));
//					//System.out.print(rs.getObject(i)+"  ");
				}
			}
			//System.out.print("here wo go");
			rs.close();
			rs=null;
			stmt.close();
			stmt=null;
		}catch(Exception w){
			System.out.println("getOneRecord error "+w.getMessage());
		}finally{
			try{
				if(con!=null){
					con.close();
					con=null;
				}
			}catch(Exception q){
				System.out.println("close error "+q.getMessage());
				
			}
		}
		return table;
	}
	
	public boolean updata(String sql){
		Connection con=null;
		Statement stmt=null;
		boolean flag=false;
		try{
			con=this.getConnect();
			stmt=con.createStatement();
			int result=stmt.executeUpdate(sql);
			if(result!=0) flag=true;
			stmt.close();
			stmt=null;
			con.close();
		}catch(Exception w){
			System.out.println("updata error "+w.getMessage());
		}finally{
			try{
				if(con!=null){
					con.close();
					con=null;
				}
			}catch(Exception q){
				System.out.println("close error "+q.getMessage());
				
			}
		}
		return flag;
	}
}