bookstore图书销售系统

/*访问数据库，查询书的信息，处理购书事物 -books : ArrayList -dbUrl : String -dbUser : String -dbPwd : String +BookDB (); +getConnection() : Connection +closeConnection : void +closePrepStmt(prepStmt : PreparedStatement) : void +closeResultSet(rs : ResultSet) : void +getNumberOfBooks() : int +getBooks() : Collection +getBookDetails(bookId : String) : BookDetails +buyBooks(cart : ShoppingCart) : void +buyBook(bookId : String, quantity : int, con : Connection) : void */ package bookstore; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.Collection; import java.util.Iterator; import com.mysql.jdbc.Driver; public class BookDB { private ArrayList books; private String dbUrl="jdbc:mysql://localhost:3306/BookDB"; private String dbUser="root"; private String dbPwd="root"; public BookDB (){ } //获取与数据库的链接 public Connection getConnection(){ Connection conn=null; try { Class.forName(Driver.class.getName()); try { conn=DriverManager.getConnection(dbUrl, dbUser, dbPwd); } catch (SQLException e) { e.printStackTrace(); } } catch (ClassNotFoundException e) { e.printStackTrace(); } return conn; } //关闭数据链接 public void closeConnection(Connection conn) { if (conn != null) { try { conn.close(); conn = null; } catch (SQLException ex) { ex.printStackTrace(); } } } //关闭查询语句 public void closePrepStmt(PreparedStatement prepStmt) { if (prepStmt != null) { try { prepStmt.close(); prepStmt = null; } catch (SQLException ex) { ex.printStackTrace(); } } } //关闭结果集 public void closeResultSet(ResultSet rs) { if (rs != null) { try { rs.close(); rs = null; } catch (SQLException ex) { ex.printStackTrace(); } } } //获得书目种类的数量 public int getNumberOfBooks(){ String selectStatement = "select count(*) from books"; int n = 0; Connection conn=null; try { conn=getConnection(); Statement stmt = conn.createStatement(); n = stmt.executeUpdate(selectStatement); } catch (SQLException e) { e.printStackTrace(); } return n; } //得到购买书的总数 public Collection<BookDetails> getBooks() throws SQLException{ Connection conn = null; Statement stmt = null; ResultSet rs = null; ArrayList<BookDetails> bookList = new ArrayList<BookDetails>(); try { conn = getConnection(); stmt = conn.createStatement(); rs = stmt.executeQuery("select * from books"); while (rs.next()) { BookDetails book = new BookDetails(rs.getString(1), rs .getString(2), rs.getString(3), rs.getFloat(4), rs .getInt(5), rs.getString(6), rs.getInt(7)); bookList.add(book); } return bookList; } finally { closeResultSet(rs); closeConnection(conn); } } //得到书的详细情况 public BookDetails getBookDetails(String bookId) throws SQLException{ Connection conn=null; // PreparedStatement prepStmt=null; Statement stmt=null; ResultSet rs=null; try { conn = getConnection(); // prepStmt = conn.prepareStatement("select * from books where id = '?'"); // prepStmt.setString(1, bookId); // rs=prepStmt.executeQuery(); stmt=conn.createStatement(); rs=stmt.executeQuery("select * from books where id = '"+bookId+"'"); BookDetails book=null; //从数据库中获取书目详细信息，对构造方法赋值。将其初始化 if(rs.next()) { book = new BookDetails(rs.getString(1), rs .getString(2), rs.getString(3), rs.getFloat(4), rs .getInt(5), rs.getString(6), rs.getInt(7)); } return book; } finally { closeResultSet(rs); closeConnection(conn); } } //此方法定义是为了更新数据库使用。。并未用到 public void buyBooks(ShoppingCart cart) throws SQLException{ Connection conn = null; PreparedStatement prepStmt = null; Iterator<ShoppingCartItem> it = cart.getItems().iterator(); try { conn = getConnection(); String sql = "update books set amount = amount - ? where id = ?"; prepStmt = conn.prepareStatement(sql); while (it.hasNext()) { ShoppingCartItem item = (ShoppingCartItem) it.next(); BookDetails book = (BookDetails) item.getItem(); String bookId = book.getBookId(); int quantity = item.getQuantity(); prepStmt.setInt(1, quantity); prepStmt.setString(2, bookId); prepStmt.addBatch(); } prepStmt.executeBatch(); } finally { closeConnection(conn); } } //此方法为判断数据库书是否还有余量 public void buyBook(String bookId, int quantity, Connection con) throws SQLException{ Connection conn=null; Statement stmt=null; ResultSet rs=null; boolean bEnough=false; try { conn=getConnection(); stmt=conn.createStatement(); rs=stmt.executeQuery("select amount from books where id = "+bookId); while(rs.next()) { int amount=rs.getInt(1); if(amount >= quantity) bEnough=true; } } finally { closeResultSet(rs); closeConnection(conn); } } /** * 通过关键字搜索图书的信息。 */ public Collection<BookDetails> searchBook(String keyword) throws SQLException { Connection conn = null; Statement stmt = null; ResultSet rs = null; ArrayList<BookDetails> bookList = new ArrayList<BookDetails>(); try { conn = getConnection(); stmt = conn.createStatement(); String sql = "select * from books where id like '%" + keyword + "%'"; rs = stmt.executeQuery(sql); while (rs.next()) { BookDetails book = new BookDetails(rs.getString(1), rs .getString(2), rs.getString(3), rs.getFloat(4), rs .getInt(5), rs.getString(6), rs.getInt(7)); bookList.add(book); } return bookList; } finally { closeResultSet(rs); closeConnection(conn); } } }