bookdao.java

JSP网上书店系统,有完整的程序,可以直接使用,无需修改

package com.tsinghua.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.tsinghua.db.DB;
import com.tsinghua.vo.Address;
import com.tsinghua.vo.Orders2;

public class BookDAO {
//	每页显示多少条记录
	private int Pagesize=3;
	//当前的页数
	private int Pageid;
	//共有多少条记录
	private int Size;
	//分页后的总页数
	private int Pagenum;
	//查询全部
	public List queryall(int Pageid){
		Connection conn=null;
		ResultSet rs=null;
		Statement set=null;
		List list=null;
		   try {
			conn=DB.getconn();
			   set=conn.createStatement(1004,1008);
			   String sql="select * from books";
			   rs=DB.getrs(set, sql);
			   list=seeshop(Pageid, rs);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			   try {
				DB.close(conn);
				   DB.close(rs);
				   DB.close(set);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		   }
		return list;
	} 
	  //查询商品类型
	public List querytype(){
		Connection conn=null;
		ResultSet rs=null;
		Statement set=null;
		List list=null;
		   try {
				conn=DB.getconn();
				   set=DB.getset(conn);
				   String sql="select  * from books";
				  // String sql="select  distinct booktype  from books"; 
				   rs=DB.getrs(set, sql);
				   list=new ArrayList();
			   while(rs.next()){
			     Address address=new Address();
			     address.setBookid(rs.getInt(1));
			     address.setBookuid(rs.getString(2));
			     address.setBooknumber(rs.getString(3));
			     address.setBookType(rs.getString(4));
			     address.setBookname(rs.getString(5));
			     address.setBookprice(rs.getString(6));
			     address.setBookpic(rs.getString(7));
			     address.setBookpictrue(rs.getString(8));
			     list.add(address);
			   }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			   try {
				DB.close(conn);
				   DB.close(rs);
				   DB.close(set);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		   }
		return list;
	} 
	
	
	//添加商品 
	public int getaddshop(Address a){
		Connection conn=null;
		ResultSet rs=null;
		PreparedStatement  pmet=null;
		int i=0;
            try {
				conn=DB.getconn();
				String sql="insert into books values(?,?,?,?,?,?,?)";
				pmet=DB.getpset(conn, sql);
				pmet.setString(1, a.getBookuid());
				pmet.setString(2, a.getBooknumber());
				pmet.setString(3, a.getBookType());
				pmet.setString(4, a.getBookname());
				pmet.setString(5, a.getBookprice());
				pmet.setString(6, a.getBookpic());
				pmet.setString(7, a.getBookpictrue());
				i=pmet.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally{
				try {
					pmet.close();
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
			}
		   
		return i;
	}
	

	//按ID查询
	public static Address queryid(int Bookid){
		Connection conn=null;
		ResultSet rs=null;
		Statement set=null;
		Address address=new Address();
		   try {
			conn=DB.getconn();
			   set=DB.getset(conn);
			   String sql="select * from books where bookid='"+Bookid+"'";
			   rs=DB.getrs(set, sql);
			   while(rs.next()){
				   address.setBookid(rs.getInt(1));
				   address.setBookuid(rs.getString(2));
				   address.setBookType(rs.getString(4));
				   address.setBookname(rs.getString(5));
				   address.setBookpic(rs.getString(7));
				   address.setBooknumber(rs.getString(3));
				   address.setBookprice(rs.getString(6));
				   address.setBookpictrue(rs.getString(8));
			   }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				DB.close(rs);
				DB.close(set);
				DB.close(conn);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return address;
	}
	//按类型查询
	public List queryallType(String booktype){
		Connection conn=null;
		ResultSet rs=null;
		Statement set=null;
		List list=null;
		   try {
			conn=DB.getconn();
			   set=DB.getset(conn);
			   String sql="select * from books where booktype='"+booktype+"'";
			   rs=DB.getrs(set, sql);
			   list=new ArrayList();
			   while(rs.next()){
			     Address address=new Address();
			     address.setBookid(rs.getInt(1));
			     System.out.println(rs.getInt(1));
			     address.setBookuid(rs.getString(2));
			     address.setBookname(rs.getString(5));
			     address.setBookprice(rs.getString(6));
			     address.setBookpic(rs.getString(7));
			     address.setBookpictrue(rs.getString(8));
			     list.add(address);
			   }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			   try {
				DB.close(conn);
				   DB.close(rs);
				   DB.close(set);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		   }
		return list;
	} 
//	删除商品
	 public static  int getdeleteshop(int Bookid){
   	 Connection conn=null;
   	 PreparedStatement pstmt=null;
   	 int i=0;
   	     try {
			     conn=DB.getconn();
				 String sql="delete from books where bookid="+Bookid+"";
				 pstmt=DB.getpset(conn, sql);
				 i=pstmt.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally{
  	    	 try {
				pstmt.close();
				 conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
  	     }
            return i;
   }
	//生成订单
	//生成子订单
	public static  int insertorders2(Orders2 order){
		Connection conn=null;
		PreparedStatement pmet=null;
		int i=0;
		try {
			conn =DB.getconn();
			String sql="insert into orders2 values (?,?,?,?,?,getDate())";
			pmet=DB.getpset(conn, sql);
			pmet.setString(1, order.getShopusername());
			pmet.setString(2, order.getShopphone());
			pmet.setString(3, order.getShopaddress());
			pmet.setString(4, order.getShopfangshi());
			pmet.setString(5, order.getUserinfo());
			i=pmet.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			try {
				pmet.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return i;
	}
	//删除订单
	public static int deleteorder(int id){
		Connection conn=null;
		PreparedStatement pmet=null;
		int i=0;
		 try {
			conn=DB.getconn();
			 String sql="delete from orders2 where id="+id+"";
			 pmet=DB.getpset(conn, sql);
			 i=pmet.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			
		try {
			pmet.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		}
		
		return i;
	}
	//修改商品信息
	public int updateshop(Address aa){
		Connection conn=null;
		PreparedStatement pmet=null;
		int i=0;
		try {
			conn=DB.getconn();
			String sql="update books set bookuid=?,booknumber=?,booktype=?,bookname=?,bookpicre=?,bookpic=?,bookpicture=? where bookid=?";
			pmet=DB.getpset(conn, sql);
			pmet.setString(1, aa.getBookuid());
			pmet.setString(2, aa.getBooknumber());
			pmet.setString(3, aa.getBookType());
			pmet.setString(4, aa.getBookname());
			pmet.setString(5, aa.getBookprice());
			pmet.setString(6, aa.getBookpic());
			pmet.setString(7, aa.getBookpictrue());
			pmet.setInt(8, aa.getBookid());
			i=pmet.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				pmet.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	   
		return i;
	} 
	//按关键子搜索商品
	public List sousoshop(String booktype) {

		Connection conn=null;
		Statement set=null;
		ResultSet rs=null;
		List list=null;
		try {
			conn=DB.getconn();
			set=DB.getset(conn);
			String sql="select * from books where booktype like '%"+booktype+"%'";
			rs=DB.getrs(set, sql);
		    list=new ArrayList();
			while(rs.next()){
		       Address address =new Address();
		       address.setBookid(rs.getInt(1));
		       address.setBookuid(rs.getString(2));
		       address.setBooknumber(rs.getString(3));
		       address.setBookType(rs.getString(4));
		       address.setBookname(rs.getString(5));
		       address.setBookprice(rs.getString(6));
			   address.setBookpic(rs.getString(7));
			   address.setBookpictrue(rs.getString(8));
		       list.add(address);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				DB.close(rs);
				DB.close(set);
				DB.close(conn);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return list;
	}
	//分页显示商品
	public List seeshop(int Pageid,ResultSet rs)throws Exception{
		  this.Pageid=Pageid;
		  
		  rs.last();
		   
		   this.Size=rs.getRow();
		   this.Pagenum=Size%Pagesize==0?Size/Pagesize:Size/Pagesize+1;
		 
		   //再将游标移到最前面
		   rs.beforeFirst();
		   
		   if(this.Pageid==0){
			   this.Pageid=Pagenum;
		   }
		   if(this.Pageid==Pagenum+1){
			   this.Pageid=1;
		   }
		  
		  //判断当前
		   List list= new ArrayList(); 
		  for(int i=0;i<(Pageid-1)*Pagesize;i++){
			  rs.next();
		  }
		  //判断当前页能否显满
		  for(int j=0;j<Pagesize;j++){
			  if(!rs.next()){
				  break;
			  }
			
			  Address address=new Address();
			  	address.setBookid(rs.getInt(1));
			     address.setBookuid(rs.getString(2));
			     address.setBookType(rs.getString(4));
			     address.setBooknumber(rs.getString(3));
			     address.setBookname(rs.getString(5));
			     address.setBookprice(rs.getString(6));
			     address.setBookpic(rs.getString(7));
			     address.setBookpictrue(rs.getString(8));
			     list.add(address);
		  }

		return list;
	}
	public int getPageid() {
		return Pageid;
	}
	public int getPagenum() {
		return Pagenum;
	}
	public int getPagesize() {
		return Pagesize;
	}

}