Jsp.rar_四六级

/** access mysql database through JDBC Driver */ package mypack; import java.sql.*; import javax.naming.*; import javax.sql.*; import java.util.*; public class BookDB { private ArrayList books; private String dbUrl = "jdbc:odbc:db2"; private String dbUser=""; private String dbPwd=""; public BookDB () throws Exception{ Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); } public Connection getConnection()throws Exception{ return java.sql.DriverManager.getConnection(dbUrl,dbUser,dbPwd); } public void closeConnection(Connection con){ try{ if(con!=null) con.close(); }catch(Exception e){ e.printStackTrace(); } } public void closePrepStmt(PreparedStatement prepStmt){ try{ if(prepStmt!=null) prepStmt.close(); }catch(Exception e){ e.printStackTrace(); } } public void closeResultSet(ResultSet rs){ try{ if(rs!=null) rs.close(); }catch(Exception e){ e.printStackTrace(); } } public int getNumberOfBooks() throws Exception { Connection con=null; PreparedStatement prepStmt=null; ResultSet rs=null; books = new ArrayList(); try { con=getConnection(); String selectStatement = "select * " + "from book"; prepStmt = con.prepareStatement(selectStatement); rs = prepStmt.executeQuery(); while (rs.next()) { BookDetails bd = new BookDetails(rs.getString(1), rs.getString(2), rs.getString(3), rs.getFloat(4), rs.getInt(5), rs.getString(6),rs.getInt(7)); books.add(bd); } }finally{ closeResultSet(rs); closePrepStmt(prepStmt); closeConnection(con); } return books.size(); } public Collection getBooks()throws Exception{ Connection con=null; PreparedStatement prepStmt=null; ResultSet rs =null; books = new ArrayList(); try { con=getConnection(); String selectStatement = "select * " + "from book"; prepStmt = con.prepareStatement(selectStatement); rs = prepStmt.executeQuery(); while (rs.next()) { BookDetails bd = new BookDetails(rs.getString(1).getBytes("GB2312"), rs.getString(2), rs.getString(3), rs.getFloat(4), rs.getInt(5), rs.getString(6),rs.getInt(7)); books.add(bd); } }finally{ closeResultSet(rs); closePrepStmt(prepStmt); closeConnection(con); } Collections.sort(books); return books; } public BookDetails getBookDetails(String bookId) throws Exception { Connection con=null; PreparedStatement prepStmt=null; ResultSet rs =null; try { con=getConnection(); String selectStatement = "select * " + "from book where id = ? "; prepStmt = con.prepareStatement(selectStatement); prepStmt.setString(1, bookId); rs = prepStmt.executeQuery(); if (rs.next()) { BookDetails bd = new BookDetails(rs.getString(1), rs.getString(2), rs.getString(3), rs.getFloat(4), rs.getInt(5), rs.getString(6),rs.getInt(7)); prepStmt.close(); return bd; } else { return null; } }finally{ closeResultSet(rs); closePrepStmt(prepStmt); closeConnection(con); } } public void buyBooks(ShoppingCart cart)throws Exception { Connection con=null; Collection items = cart.getItems(); Iterator i = items.iterator(); try { con=getConnection(); con.setAutoCommit(false); while (i.hasNext()) { ShoppingCartItem sci = (ShoppingCartItem)i.next(); BookDetails bd = (BookDetails)sci.getItem(); String id = bd.getBookId(); int quantity = sci.getQuantity(); buyBook(id, quantity,con); } con.commit(); con.setAutoCommit(true); } catch (Exception ex) { con.rollback(); throw ex; }finally{ closeConnection(con); } } public void buyBook(String bookId, int quantity,Connection con) throws Exception { PreparedStatement prepStmt=null; ResultSet rs=null; try{ String selectStatement = "select * " + "from book where id = ? "; prepStmt = con.prepareStatement(selectStatement); prepStmt.setString(1, bookId); rs = prepStmt.executeQuery(); if (rs.next()) { prepStmt.close(); String updateStatement = "update book set xssl = xssl + ? where id = ?"; prepStmt = con.prepareStatement(updateStatement); prepStmt.setInt(1, quantity); prepStmt.setString(2, bookId); prepStmt.executeUpdate(); prepStmt.close(); } }finally{ closeResultSet(rs); closePrepStmt(prepStmt); } } }