//sql classes are needed for database manipulation with SQL
import java.sql.*;
//for input and output
import javax.swing.JOptionPane;

public class ConnectDB{
   public static void main(String[] args){

      
      //Build connection to the database, required
      Connection connection;
      try{
         Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); //driver type
         String url = "jdbc:odbc:cis135"; //odbc registered name
         String user = "Admin"; //is set when odbc is created
         String password = ""; //is set when odbc is created
         connection = DriverManager.getConnection(url, user, password); //opens connection
         JOptionPane.showMessageDialog(null, "Connection made.");
         
         //The SQL statements start here
         //Result set is scrollable and updateable
         Statement statement = connection.createStatement(
         	ResultSet.TYPE_SCROLL_SENSITIVE,
         	ResultSet.CONCUR_UPDATABLE);
         	
         //Query input, can change code to get from gui or swing interface
         String iCode = "DB21";
         
         //Create a string containing SQL
         String query = "SELECT BookOrders.BookCode, BookTitle, BookPrice, Quantity "
                      + "FROM Books, BookOrders "
                      + "WHERE BookOrders.BookCode = '" + iCode + "'"
                      + " AND BookOrders.BookCode = Books.BookCode "
                      + "ORDER BY BookOrders.BookCode ASC";
                      
         //books will contain the result of our search
         ResultSet books = statement.executeQuery(query); //build result set             
         
         System.out.println("query Executed");
         
         double n = 0;
         double quant = 0;
         
         while (books.next() == true) { //While More Books in the result set books
           //quant =  books.getDouble(4)
           quant = books.getDouble("Quantity");
           n = n + quant;
           System.out.println("BookCode: " + books.getString(1));
           System.out.println("BookTitle: " + books.getString(2));
           System.out.println("BookPrice: " + books.getDouble(3));
           System.out.println("Quantity Ordered: " + quant);
           System.out.println("******************");
          }//end while   
           System.out.println("Total Ordered = " + n );
      }
      catch (ClassNotFoundException e){
         JOptionPane.showMessageDialog(null, "ClassNotFound " + e.getMessage());
         System.exit(1);
      }
      catch (SQLException e){
          JOptionPane.showMessageDialog(null, e.getMessage());
      }
   }

}