db.java

用java编写的一个简单的购书系统

package DBDBAO;

import BookClass.BookDetail;
import CItemClass.ShoppingCartItem;
import SCartClass.ShoppingCart;
import java.util.*;
import java.sql.*;




public class DB
{
	private String BNum="0";
	private BookDBAO database=null;
	
	//
	public DB()
	{
		try{
			this.database=new BookDBAO();
		}catch(Exception e){
			System.err.println(e);
		}
	}
	
	//
	public void setBNum(String BNum)
	{
		this.BNum=BNum;
	}
	
	//
	public void setDatabase(BookDBAO database)
	{
		this.database=database;
	}
	
	//
	public BookDetail getBookDetail()
	{
		return database.getBookDetails(BNum);
	}
	
	//
	public List getBook()
	{
		return database.getBooks();
	}
	
	//
	public void buyBook(ShoppingCart cart)
	{
		database.buyBooks(cart);
	}
	
	public static void main(String []args)
	{
		DB db=new DB();
		db.getBookDetail();
	}
}



 class BookDBAO
{
	private ArrayList books=null;
	Connection con=null;
	//PreparedStatement prepStm=null;
	//ResultSet rs=null;
	private String url="jdbc:odbc:SCHOOL";
	
	//连接数据库
	public BookDBAO()
	{
		try{
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			System.out.println("加载驱动器 O.K.");
		}catch(ClassNotFoundException e){
			System.out.println("加载驱动器异常");
		}
					
	
		try{
			con=DriverManager.getConnection(url);
			System.out.println("连接数据库 O.K.");
		}catch(SQLException e){
			System.out.println("不能连接数据库");
		}
	}
	
	
	//关闭连接代码预留
	
	
    //查询数据库取得书目列表
     public List getBooks(){/*throws BooksNotFoundException*/
           books=new ArrayList();
           
           try{
               String selectStatement="select * from Book";
               PreparedStatement prepStmt=con.prepareStatement();
               ResultSet rs=prepStmt.excuteQuery(selectStatement);
               
               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);
                  }
              }
              
              prepStmt.close();
            }catch(SQLException ex){
              System.out.println("查询数据库取得书目列表 异常");
              //throw new BooksNotFoundException(ex.getMessage());
            }
            
            Collections.sort(books);
            
            return books;
     }
     
     
     //得到书的全部信息（属性）
     public BookDetail getBookDetails(String bookId){ 
       /*throws BookNotFoundException*/
       try{
           String selectStatement="select BNum,BName,BWriter,BPrice,BDescription,BStore"+"from Book where BNum=?";
           PreparedStatement prepStmt=con.prepareStatement(); 
           prepStmt.setString(1,BNum);
           
           ResultSet rs=prepStmt.executeQuery(selectStatement);
           
           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());
     }
   }
   
   
   //用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 Book where BNum=?";
           PreparedStatement prepStmt=con.prepareStatement(); 
           prepStmt.setString(1,BNum);
           
           ResultSet rs=prepStmt.executeQuery(selectStatement);
           
           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("买书代码块出错");
   }
  }
 }