/*******************************************************
File            : TestJDBC.java
Author          : Original version: Ofer Shir
                : Modified by: Johannes Kruisselbrink
********************************************************/

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

public class TestJDBC
{
	private static String PATH = "jdbc:oracle:thin:";
	private static String HOST = "oracle";
	private static String PORT = "1521";
	private static String DB = "ONW";
	private static String oracleDriver = "oracle.jdbc.driver.OracleDriver";
	private static String TABLE_SCHEM = "";

	private static Connection con = null;
	private static Statement statement = null;
	private static ResultSet result = null;

	public static void main(String argv[])
	{
		try
		{
			if (argv.length < 2)
			{
				System.out.println("Usage: java TestJDBC login password");
				return;
			}

			//Arguments from the user.
			String login = argv[0];
			String password = argv[1];

			//Connecting to the database
			con = establishConnection(login, password);

			statement = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

			createTestTable();

			fillTestTable();

			examineDatabase();

			dropTestTable();

		}
		catch (Exception e)
		{
		    System.out.println("Exception was thrown: " + e.getMessage() + "\n");
		    e.printStackTrace();
		}
	
		finally
		{
			try
			{
				// Note: A ResultSet object is automatically closed by the Statement object
				// that generated it when that Statement object is closed, re-executed,
				// or is used to retrieve the next result from a sequence of multiple results.

				if (statement != null) statement.close();

				if (con != null) con.close();

			}
			catch (Exception e)
			{
				System.out.println("Exception was thrown: " + e.getMessage() + "\n");
				e.printStackTrace();
			}
		}
	}


	// This method constructs the connection to the database.
	static private Connection establishConnection(String login, String password) throws Exception
	{
		try
		{
			TABLE_SCHEM = login.toUpperCase();

			// The url of the driver
			String url = PATH + login + "/" + password + "@" + HOST +":" + PORT + ":" + DB;

			// Select the appropriate driver
			Class.forName(oracleDriver);

			// Make and return the connection to the database
			return DriverManager.getConnection(url);

		}
		catch (Exception e)
		{
			System.out.println("Connection Exception...\n");
			throw e;
		}
	}


	// This method creates a test table.
	static private void createTestTable() throws Exception
	{
		try
		{
			String create_table_staff = 
				  "CREATE TABLE Staff ("
				+ "name VARCHAR2(100) PRIMARY KEY, "
				+ "room CHAR(6), "
				+ "tel CHAR(4), "
				+ "eMail VARCHAR2(100) UNIQUE NOT NULL, "
				+ "position CHAR(20) NOT NULL, "
				+ "image BLOB, "
				+ "curVitae CLOB, "
				+ "CHECK (position IN ('head','professor','lecturer','technician','secretary')))";
			statement.executeUpdate(create_table_staff);
		}
		catch(Exception e)
		{
			System.out.println("CREATE TABLE Exception...\n");
			throw e;
		}
	}

	// This method creates a test table.
	static private void fillTestTable() throws Exception
	{
		try
		{
			String insert_table_staff = "INSERT INTO Staff VALUES ('Michael Emmerich', 161, NULL, 'emmerich@liacs.nl', 'lecturer', EMPTY_BLOB(), EMPTY_CLOB())";
			statement.executeUpdate(insert_table_staff);
		}
		catch (Exception e)
		{
			System.out.println("INSERT INTO Exception...\n");
			throw e;
		}
	}


	// This method drops the test table.
	static private void dropTestTable() throws Exception
	{
		try
		{
			// Dropping the table directly_related_not_distinct if it exists.
			// If the table does not exist the exception is caught and nothing happens.
			String drop_table_staff = "DROP TABLE Staff CASCADE CONSTRAINTS";

			statement.executeUpdate(drop_table_staff);

		}
		catch(Exception e)
		{
			System.out.println("DROP TABLE Exception...\n");
			throw e;
		}
	}


	// This method lists all table names in the Database
	static private void examineDatabase() throws Exception
	{
		try
		{
			// Examine all database metadata;
			DatabaseMetaData dmd = con.getMetaData();

			ResultSet tablesRS = dmd.getTables(null, TABLE_SCHEM, null, new String[] {"TABLE"});

			while (tablesRS.next())
			{
				String tableName = tablesRS.getString("TABLE_NAME");

				System.out.println("The attributes of the table " + tableName + " are:");

				ResultSet columnsRS = dmd.getColumns(null, null, tableName, null);

				while(columnsRS.next())
				{
					System.out.println("\t" + columnsRS.getString("COLUMN_NAME"));
				}

				System.out.println();

				columnsRS.close();
			}

			tablesRS.close();

		}
		catch(Exception e)
		{
			System.out.println("Error when examining the Database...\n");
			throw e;
		}
	}
}