package com.wrox.databases;

import java.sql.*;
import java.util.*;

public class Books {

  String error;
  Connection con;

  public Books()   { }

  public void connect() throws ClassNotFoundException,
                               SQLException, 
                               Exception {
    try {
      Class.forName("org.gjt.mm.mysql.Driver").newInstance(); 
      con = DriverManager.getConnection(
        "jdbc:mysql://localhost/Wrox ?user=root&password=adarsh ");
    } catch (ClassNotFoundException cnfe) {
      error = "ClassNotFoundException: Could not locate DB driver.";
      throw new ClassNotFoundException(error);
    } catch (SQLException cnfe) {
      error = "SQLException: Could not connect to database.";
      throw new SQLException(error);
    } catch (Exception e) {
      error = "Exception: An unknown error occurred while connecting " +
              "to database.";
      throw new Exception(error);
    }
  } 

  public void disconnect() throws SQLException {
    try {
      if ( con != null ) {
        con.close();
      }
    } catch (SQLException sqle) {
      error = ("SQLException: Unable to close the database connection.");
      throw new SQLException(error);
    }
  }

  public ResultSet viewBooks() throws SQLException, Exception {
    ResultSet rs = null;
    try  {
      String queryString = ("SELECT * FROM Book;");
      Statement stmt = con.createStatement();
      rs = stmt.executeQuery(queryString); 
    } catch (SQLException sqle) {
      error = "SQLException: Could not execute the query.";
      throw new SQLException(error);
    } catch (Exception e) {
      error = "An exception occured while retrieving books.";
      throw new Exception(error);			 
    }
    return rs;
  }

  public void addBooks(int id, String title, float price, int cid) 
                      throws SQLException, Exception {
    if (con != null) {
      try {
        PreparedStatement updatebooks; 
        updatebooks = con.prepareStatement(
                      "insert into Book values(?, ?, ?, ?);"); 
        updatebooks.setInt(1, id); 
        updatebooks.setString(2, title); 
        updatebooks.setInt(3, cid);
        updatebooks.setFloat(4, price); 
        updatebooks.execute(); 
      } 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 (con != null) {
      try {
        PreparedStatement delete;
        delete = con.prepareStatement("DELETE FROM Book WHERE Title_ID=?;"); 
        for (int i = 0; i < pkeys.length; i++) {
          delete.setInt(1, Integer.parseInt(pkeys[i])); 
          delete.execute(); 
        }
      } 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);
    }
  } 
}