www.pudn.com > myapp.rar > BookDBAO2.java

package DBAO2; 
import BookClass.BookDetail; 
import CItemClass.ShoppingCartItem; 
import SCartClass.ShoppingCart; 
import java.util.*; 
import java.sql.*; 
 
 
 
 
 public class BookDBAO2 
{ 
	private ArrayList books=null; 
	//private String url="jdbc:odbc:SCHOOL"; 
	private String DBNum="0";//来自BookDB 
	private Connection con=null;	 
	//private PreparedStatement prepStmt=null; 
	//private ResultSet rs=null; 
	 
	 
	 
	//=============================================================== 
	 
	 
	 
	 
	//=============================================================== 
	 
	//构造函数--连接数据库 
	public BookDBAO2() 
	{ 
		try{ 
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); 
		}catch(ClassNotFoundException e){ 
			System.out.println("加载驱动器异常"); 
		} 
					 
	 
		try{ 
			con=DriverManager.getConnection("jdbc:odbc:SCHOOL"); 
		}catch(SQLException e){ 
			System.out.println("不能连接数据库"); 
		} 
	} 
	 
	 
	//关闭连接 
	 
	public void closedbao() 
	{ 
		try{ 
			con.close(); 
		}catch(SQLException ex){ 
			System.out.println("关闭数据库异常"+ex.getMessage()); 
		} 
	} 
	 
	//来自BookDB============== 
	public void setDBNum(String DBNum) 
	{ 
		this.DBNum=DBNum; 
	} 
	public String getDBNum() 
	{ 
		return this.DBNum; 
	} 
	 
	 
    //查询数据库取得书目列表 
     public List getBooks(){ //throws BooksNotFoundException 
           books=new ArrayList(); 
            
           try{ 
               String selectStatement="select * from Book2"; 
               PreparedStatement prepStmt=con.prepareStatement(selectStatement); 
               ResultSet rs=prepStmt.executeQuery(); 
                
               while(rs.next()){ 
                  BookDetail bd= 
                      new BookDetail(rs.getString(1),rs.getString(2), 
                          rs.getString(3),rs.getFloat(4), 
                          rs.getString(5),rs.getInt(6)); 
                           
                  //if(rs.getInt(6)>0){ 
                       books.add(bd); 
                  //} 
                   
                  //以下是用来测试的打印--打印书的信息 
                  /* 
                  System.out.println(bd.getBDescription()); 
                  System.out.println(bd.getBName()); 
                  System.out.println(bd.getBNum()); 
                  System.out.println(bd.getBPrice()); 
                  System.out.println(bd.getBStore()); 
                  System.out.println(bd.getBWriter());*/ 
              } 
               
              prepStmt.close(); 
               
            }catch(SQLException ex){ 
              System.out.println("查询数据库取得书目列表 异常"); 
              //throw new BooksNotFoundException(ex.getMessage()); 
            } 
             
            Collections.sort(books); 
             
            return books; 
     } 
      
      
     //得到书的全部信息（属性） 
     public BookDetail getBookDetails(String BNum){  
       /*throws BookNotFoundException*/ 
       try{ 
           String selectStatement="select BNum,BName,BWriter,BPrice,BDescription,BStore"+"from Book2 where BNum=?"; 
           PreparedStatement prepStmt=con.prepareStatement(selectStatement);  
           prepStmt.setString(1,BNum); 
            
           ResultSet rs=prepStmt.executeQuery(); 
            
           if(rs.next()){ 
             BookDetail bd= 
                      new BookDetail(rs.getString(1),rs.getString(2), 
                          rs.getString(3),rs.getFloat(4), 
                          rs.getString(5),rs.getInt(6)); 
              
             prepStmt.close(); 
             return bd; 
       }else{ 
             prepStmt.close();                  
             //System.out.println("找不到书，书号："+bookId); 
       } 
     }catch(SQLException ex){ 
        System.err.println(ex.getMessage()); 
        //throw new BookNotFoundException("Couldn't find book:"+bookId+ 
            //""+ex.getMessage()); 
     } 
     return null; 
   } 
    
    
   //用ShoppingCart买书1 
   public void buyBooks(ShoppingCart cart)/*throws OrderException*/{ 
        Collection items=cart.getItems(); 
        Iterator i=items.iterator(); 
         
        try{ 
            con.setAutoCommit(false); 
             
            while(i.hasNext()){ 
                ShoppingCartItem sci=(ShoppingCartItem)i.next(); 
                BookDetail bd=(BookDetail)sci.getItem(); 
                String id=bd.getBNum(); 
                int quantity=sci.getQuantity(); 
                buyBook(id,quantity); 
            } 
             
            con.commit(); 
            con.setAutoCommit(true); 
        }catch(Exception ex){ 
           try{ 
               con.rollback(); 
               /*throw new OrderException("Transaction failed:"+ 
                   ex.getMessage());*/ 
             }catch(SQLException sqx){ 
                 System.out.println("用ShoppingCart买书异常"); 
             } 
        } 
    } 
     
     
   //买书2  
   public void buyBook(String BNum,int quantity)/*throws OrderException*/{ 
       try{ 
           String selectStatement="select BNum,BName,BWriter,BPrice,BDescription,BStore"+"from Book2 where BNum=?"; 
           PreparedStatement prepStmt=con.prepareStatement(selectStatement);  
           prepStmt.setString(1,BNum); 
            
           ResultSet rs=prepStmt.executeQuery(); 
            
           if(rs.next()){ 
               int inventory=rs.getInt(6); 
               prepStmt.close(); 
                
               if((inventory-quantity)>=0){ 
                   String updateStatement= 
                        "update Book set BStore=inventory-?where BNum=?"; 
                   prepStmt=con.prepareStatement(updateStatement);      
                   prepStmt.setInt(1,quantity); 
                   prepStmt.setString(2,BNum); 
                   prepStmt.executeUpdate(); 
                   prepStmt.close(); 
               }else{ 
                  //throw new OrderException("Not enough of"+bookId+ 
                    //  "in stock to complete order."); 
               } 
           } 
   }catch(SQLException ex){ 
       System.out.println("买书代码块出错"); 
   } 
  } 
   
 /* 
  public static void main(String []args) 
  { 
  	BookDBAO dbao=new BookDBAO(); 
    dbao.getBooks(); 
  } 
  */ 
   
}