bookdb.java~23~

网上书店,JSP,MYSQL,struct,spring,hibernate

/** access mysql database through JDBC Driver */
package bookshoppingsys;
import java.sql.*;
import javax.naming.*;
import javax.sql.*;
import java.util.*;

public class BookDB {

  private ArrayList books;

  public BookDB () throws Exception{
     Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
  }

  public Connection getConnection()throws Exception{
      return java.sql.DriverManager.getConnection("jdbc:odbc:Shopping");
  }

  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();
         bd.setBook_type(rs.getString(2));
         bd.setBook_name(rs.getString(3));
         bd.setAuthor(rs.getString(4));
         bd.setPublishing_house(rs.getString(5));
         bd.setDescription( rs.getString(6));
         bd.setValue(rs.getFloat(7));
         bd.setT_value(rs.getFloat(8));
         bd.setVip_value(rs.getFloat(9));
         bd.setSale_number(rs.getInt(10));
          bd.setBook_image(rs.getString(11));

        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();
        bd.setBook_type(rs.getString(2));
        bd.setBook_name(rs.getString(3));
        bd.setAuthor(rs.getString(4));
        bd.setPublishing_house(rs.getString(5));
        bd.setDescription( rs.getString(6));
        bd.setValue(rs.getFloat(7));
        bd.setT_value(rs.getFloat(8));
        bd.setVip_value(rs.getFloat(9));
        bd.setSale_number(rs.getInt(10));
         bd.setBook_image(rs.getString(11));

        books.add(bd);
      }

    }finally{
      closeResultSet(rs);
      closePrepStmt(prepStmt);
      closeConnection(con);
    }

    Collections.sort(books);
    return books;
  }
//按书名查询
  public Collection getBooksBookname(String bookname)throws Exception{
  Connection con=null;
  PreparedStatement prepStmt=null;
  ResultSet rs =null;
  books = new ArrayList();
  try {
    con=getConnection();
    String selectStatement = "select * from book where book_name like '"+bookname+"%' ";
    prepStmt = con.prepareStatement(selectStatement);
    rs = prepStmt.executeQuery();

    while (rs.next()) {
      BookDetails bd = new BookDetails();
              bd.setBook_type(rs.getString(2));
              bd.setBook_name(rs.getString(3));
              bd.setAuthor(rs.getString(4));
              bd.setPublishing_house(rs.getString(5));
              bd.setDescription( rs.getString(6));
              bd.setValue(rs.getFloat(7));
              bd.setT_value(rs.getFloat(8));
              bd.setVip_value(rs.getFloat(9));
              bd.setSale_number(rs.getInt(10));
               bd.setBook_image(rs.getString(11));

      books.add(bd);
    }

  }finally{
    closeResultSet(rs);
    closePrepStmt(prepStmt);
    closeConnection(con);
  }

  Collections.sort(books);
  return books;
}


//按作者查询
  public Collection getBooksAuthor(String author)throws Exception{
  Connection con=null;
  PreparedStatement prepStmt=null;
  ResultSet rs =null;
  books = new ArrayList();
  try {
    con=getConnection();
    String selectStatement = "select * from book where author like '"+author+"%' ";
    prepStmt = con.prepareStatement(selectStatement);
    rs = prepStmt.executeQuery();

    while (rs.next()) {
      int bookid=rs.getInt(1);
      BookDetails bd = new BookDetails();
        bd.setBook_type(rs.getString(2));
        bd.setBook_name(rs.getString(3));
        bd.setAuthor(rs.getString(4));
        bd.setPublishing_house(rs.getString(5));
        bd.setDescription( rs.getString(6));
        bd.setValue(rs.getFloat(7));
        bd.setT_value(rs.getFloat(8));
        bd.setVip_value(rs.getFloat(9));
        bd.setSale_number(rs.getInt(10));
         bd.setBook_image(rs.getString(11));

      books.add(bd);
    }

  }finally{
    closeResultSet(rs);
    closePrepStmt(prepStmt);
    closeConnection(con);
  }

  Collections.sort(books);
  return books;
}
//按出版社查询
  public Collection getBooksPublishing(String publishing_house)throws Exception{
  Connection con=null;
  PreparedStatement prepStmt=null;
  ResultSet rs =null;
  books = new ArrayList();
  try {
    con=getConnection();
    String selectStatement = "select * from book where publishing_house like'"+publishing_house+"%'";
    prepStmt = con.prepareStatement(selectStatement);
    rs = prepStmt.executeQuery();

    while (rs.next()) {

      BookDetails bd = new BookDetails();
         bd.setBook_type(rs.getString(2));
         bd.setBook_name(rs.getString(3));
         bd.setAuthor(rs.getString(4));
         bd.setPublishing_house(rs.getString(5));
         bd.setDescription( rs.getString(6));
         bd.setValue(rs.getFloat(7));
         bd.setT_value(rs.getFloat(8));
         bd.setVip_value(rs.getFloat(9));
         bd.setSale_number(rs.getInt(10));
          bd.setBook_image(rs.getString(11));

      books.add(bd);
    }

  }finally{
    closeResultSet(rs);
    closePrepStmt(prepStmt);
    closeConnection(con);
  }

  Collections.sort(books);
  return books;
}


//得到书的详细信息
  public BookDetails getBookDetails(String book_name) throws Exception {
    Connection con=null;
    PreparedStatement prepStmt=null;
    ResultSet rs =null;
    try {
      con=getConnection();
      String selectStatement = "select * from book where book_name like'"+book_name+"%' ";
      prepStmt = con.prepareStatement(selectStatement);
      rs = prepStmt.executeQuery();

      while (rs.next()) {
        BookDetails bd = new BookDetails();
        bd.setBook_type(rs.getString(2));
        bd.setBook_name(rs.getString(3));
        bd.setAuthor(rs.getString(4));
        bd.setPublishing_house(rs.getString(5));
        bd.setDescription( rs.getString(6));
        bd.setValue(rs.getFloat(7));
        bd.setT_value(rs.getFloat(8));
        bd.setVip_value(rs.getFloat(9));
        bd.setSale_number(rs.getInt(10));
         bd.setBook_image(rs.getString(11));

        prepStmt.close();

        return bd;
      }return null;
//      else {
//
//      }
    }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);//默认的话为自动提交， 当没执行一个update ,delete或者insert的时候都会自动提交到数据库，无法回滚事务。 设置connection.setautocommit(false);只有程序调用connection.commit()的时候才会将先前执行的语句一起提交到数据库，这样就实现了数据库的事务。

      while (i.hasNext()) {
        ShoppingCartItem sci = (ShoppingCartItem)i.next();
        BookDetails bd = (BookDetails)sci.getItem();
        String book_name = bd.getBook_name();
        int quantity = sci.getQuantity();
        buyBook(book_name, quantity,con);
      }
      con.commit();
      con.setAutoCommit(true);

    } catch (Exception ex) {
      con.rollback();
      throw ex;
    }finally{
       closeConnection(con);
    }
  }

//买书
  public void buyBook(String book_name, int quantity,Connection con) throws Exception {
    PreparedStatement prepStmt=null;
    ResultSet rs=null;
    try{
      String selectStatement = "select * from book where book_name = ? ";
      prepStmt = con.prepareStatement(selectStatement);
      prepStmt.setString(1, book_name);
      rs = prepStmt.executeQuery();

      if (rs.next()) {
          prepStmt.close();
          String updateStatement =
                  "update book set sale_number = sale_number + ? where book_name = ?";
//可能有错
          prepStmt = con.prepareStatement(updateStatement);
          prepStmt.setInt(1, quantity);
          prepStmt.setString(2, book_name);
          prepStmt.executeUpdate();
          prepStmt.close();
       }

    }finally{
      closeResultSet(rs);
      closePrepStmt(prepStmt);
    }
  }

}