books.java

beginJsp2.0外文书籍源代码

package com.wrox.library;
import javax.naming.*;
import javax.sql.*;
import java.sql.*;
import java.util.*;

public class Books {

  Connection conn;
  String error;

  public Books() { }

  public void connect() {
    try {
      Context ctx = new InitialContext();
      if(ctx == null )
        throw new Exception("Oops - No Context");

      DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/BooksDB");

      if (ds != null)
        conn = ds.getConnection();
    }catch(Exception e) {
      e.printStackTrace();
    }
 }

  public void disconnect() {
    try {
      if ( conn != null ) {
        conn.close();
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
  }

  public ResultSet viewBooks() {
    ResultSet rs = null;
    try {
      Statement stmt = conn.createStatement();
      rs = stmt.executeQuery
          ("SELECT Book.Title_ID, Book.Title, Book.Price," +
           "Category.Category_Description AS Category " +
           "FROM Book, Category " +
           "WHERE Book.Category_ID = Category.Category_ID " +
           "ORDER BY Category, Book.Price DESC;");

    } catch (Exception e) {
      e.printStackTrace();
    }
    return rs;
  }

  public void addBooks(int id, String title, float price, int cid)
              throws SQLException, Exception {
    if (conn != null) {
      try {
        PreparedStatement updatebooks;
        updatebooks = conn.prepareStatement(
            "insert into Book values(?, ?, ?, ?);");
        updatebooks.setInt(1, id);
        updatebooks.setString(2, title);
        updatebooks.setFloat(3, price);
        updatebooks.setInt(4, cid);
        updatebooks.executeUpdate();
      } catch (SQLException sqle) {
        error = "SQLException: update failed, possible duplicate entry";
        throw new SQLException(error);
      }
    } else {
      error = "Exception: Connection to database was lost.";
      throw new Exception(error);
    }
  }

  public void removeBooks(String [] pkeys) throws SQLException, Exception {
    if (conn != null) {
      try {
        PreparedStatement delete;
        delete = conn.prepareStatement("DELETE FROM Book WHERE Title_ID=?;");
        for (int i = 0; i < pkeys.length; i++) {
          delete.setInt(1, Integer.parseInt(pkeys[i]));
          delete.executeUpdate();
        }
      } catch (SQLException sqle) {
        error = "SQLException: update failed, possible duplicate entry";
        throw new SQLException(error);
      } catch (Exception e) {
        error = "An exception occured while deleting books.";
        throw new Exception(error);
      }
    } else {
      error = "Exception: Connection to database was lost.";
      throw new Exception(error);
    }
  }
}