package jdbc;
import java.sql.*;
import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.util.*;

public class DisplayQuery1 extends JFrame {
   private Connection connection;
   private JTable table;

   public DisplayQuery1() //Constructor
   {
      // The URL specifying the local SQL Database server to which
      // this program connects using JDBC to connect to a
      // Microsoft ODBC database.
      // name of our server is SQLEXPRESS2008EXAMPLES
      String url = "jdbc:odbc:SQLSERVER2008EXAMPLES"; //local SQL Server
      String username = "sa";
      String password = "testing123";

      // Load the driver to allow connection to the database
      try {
         Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );

         connection = DriverManager.getConnection(
            url, username, password );
      }
      // catch exception if driver is not loaded correctly
      catch ( ClassNotFoundException cnfex ) {
         System.err.println(
            "Failed to load JDBC/ODBC driver." );
         cnfex.printStackTrace();
         System.exit( 1 );  // terminate program
      }
      // catch exception if problem with connection to the database
      catch ( SQLException sqlex ) {
         System.err.println( "Unable to connect" );
         sqlex.printStackTrace();
      }

//      getQuery1(); //get the query result
//      getQuery2(); //get the query result
//      getQuery3(); //get the query result
//      getQuery4(); //get the query result
      getQuery5(); //get the query result

      setSize( 450, 150 );  // set the size of the window
      show();   //show the window

   }

   private void getQuery1()
   {
      Statement statement;
      ResultSet resultSet;

      try {
        String query = "USE CSC675; SELECT file_name FROM Audio WHERE file_id='3' UNION"
        + " SELECT file_name FROM Video WHERE file_id='3' UNION"
        + " SELECT file_name FROM Pdf WHERE file_id='3' UNION"
        + " SELECT file_name FROM Images WHERE file_id='3';";
         statement = connection.createStatement();
         resultSet = statement.executeQuery( query );
         displayResultSet( resultSet );
         statement.close();
      }
      catch ( SQLException sqlex ) {
         sqlex.printStackTrace();
      }
    }

   private void getQuery2()
   {
      Statement statement;
      ResultSet resultSet;

      try {
        String query = "USE CSC675; SELECT file_name FROM Audio WHERE displayed_url='url2' UNION "
                + "SELECT file_name FROM Video WHERE displayed_url='url2' UNION "
                + "SELECT file_name FROM Pdf WHERE displayed_url='url2' UNION "
                + "SELECT file_name FROM Images WHERE displayed_url='url2';";
         statement = connection.createStatement();
         resultSet = statement.executeQuery( query );
         displayResultSet( resultSet );
         statement.close();
      }
      catch ( SQLException sqlex ) {
         sqlex.printStackTrace();
      }
   }

   private void getQuery3()
   {
      Statement statement;
      ResultSet resultSet;

      try {
        String query = "USE CSC675; SELECT file_name FROM Audio WHERE download_date='2010-08-08' AND file_type='audio' UNION "
                + "SELECT file_name FROM Video WHERE download_date='2010-08-08' AND file_type='audio' UNION "
                + "SELECT file_name FROM Pdf WHERE download_date='2010-08-08' AND file_type='audio' UNION "
                + "SELECT file_name FROM Images WHERE download_date='2010-08-08'AND file_type='audio';";
         statement = connection.createStatement();
         resultSet = statement.executeQuery( query );
         displayResultSet( resultSet );
         statement.close();
      }
      catch ( SQLException sqlex ) {
         sqlex.printStackTrace();
      }
   }

   private void getQuery4()
   {
      Statement statement;
      ResultSet resultSet;

      try {
        String query = "USE CSC675; SELECT distinct i.file_name FROM Images i, Images_keywords ik "
                + "WHERE i.file_id=ik.file_id and ik.keyword='pdf_keyword1' UNION "
                + "SELECT distinct v.file_name FROM Video v, Video_keywords vk "
                + "WHERE v.file_id=vk.file_id and vk.keyword='pdf_keyword1' UNION "
                + "SELECT distinct p.file_name FROM Pdf p, Pdf_keywords pk "
                + "WHERE p.file_id=pk.file_id and pk.keyword='pdf_keyword1' UNION "
                + "SELECT distinct a.file_name FROM Audio a, Audio_keywords ak "
                + "WHERE a.file_id=ak.file_id and ak.keyword='pdf_keyword1';";
         statement = connection.createStatement();
         resultSet = statement.executeQuery( query );
         displayResultSet( resultSet );
         statement.close();
      }
      catch ( SQLException sqlex ) {
         sqlex.printStackTrace();
      }
   }

   private void getQuery5()
   {
      Statement statement;
      ResultSet resultSet;

      try {
        String query = "USE CSC675; select (select sum (num_of_entries) from ("
                + "select cast(count(download_date) as float) from Audio union all "
                + "select cast(count(download_date) as float) from Pdf union all "
                + "select cast(count(download_date) as float) from Video union all "
                + "select cast(count(download_date) as float) from Images) as x (num_of_entries)"
                + ") / ("
                + "select datediff (day, min (num_of_days), max(num_of_days))+1 "
                + "from (select min(mindate) from ("
                + "select min (download_date) from Audio union all "
                + "select min(download_date) from Pdf union all "
                + "select min (download_date) from Video union all "
                + "select min(download_date) from Images) as x (mindate) union all "
                + "select max(maxdate) from (select max (download_date) from Audio union all "
                + "select min(download_date) from Pdf union all "
                + "select min(download_date) from Video union all "
                + "select min(download_date) from Images) as x (maxdate)) as x (num_of_days))";
         statement = connection.createStatement();
         resultSet = statement.executeQuery( query );
         displayResultSet( resultSet );
         statement.close();
      }
      catch ( SQLException sqlex ) {
         sqlex.printStackTrace();
      }
   }


   private void displayResultSet( ResultSet rs )
      throws SQLException
   {
      // position to first record
      boolean moreRecords = rs.next();

      // If there are no records, display a message
      if ( ! moreRecords ) {
         JOptionPane.showMessageDialog( this,
            "ResultSet contained no records" );
         setTitle( "No records to display" );
         return;
      }

      setTitle( "Car Table from SQL HomeWork" );  //CHANGE THIS!!

      Vector columnHeads = new Vector();
      Vector rows = new Vector();

      try {
         // get column heads
         ResultSetMetaData rsmd = rs.getMetaData();

         for ( int i = 1; i <= rsmd.getColumnCount(); ++i )
            columnHeads.addElement( rsmd.getColumnName( i ) );

         // get row data
         do {
            rows.addElement( getNextRow( rs, rsmd ) );
         } while ( rs.next() );

         // display table with ResultSet contents
         table = new JTable( rows, columnHeads );
         JScrollPane scroller = new JScrollPane( table );
         getContentPane().add(
            scroller, BorderLayout.CENTER );
         validate();
      }
      catch ( SQLException sqlex ) {
         sqlex.printStackTrace();
      }
   }

   private Vector getNextRow( ResultSet rs,
                              ResultSetMetaData rsmd )
       throws SQLException
   {
      Vector currentRow = new Vector();

      for ( int i = 1; i <= rsmd.getColumnCount(); ++i )
         switch( rsmd.getColumnType( i ) ) {
            case Types.VARCHAR:
                  currentRow.addElement( rs.getString( i ) );
               break;
            case Types.FLOAT:
                  currentRow.addElement(
                     new Float( rs.getFloat( i ) ) );
               break;
            default:
               System.out.println( "Type was: " +
                  rsmd.getColumnTypeName( i ) );
         }

      return currentRow;
   }

   public void shutDown()
   {
      try {
         connection.close();
      }
      catch ( SQLException sqlex ) {
         System.err.println( "Unable to disconnect" );
         sqlex.printStackTrace();
      }
   }

   public static void main( String args[] )
   {
      final DisplayQuery1 app = new DisplayQuery1();  //CHANGE THIS!

      app.addWindowListener(new WindowAdapter() {
            public void windowClosing( WindowEvent e )
            {
               app.shutDown();
               System.exit( 0 );
            }
         }
      );
   }
}