Database.java

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

public class Database{
	public static void main (String args []) throws SQLException, IOException {
		// Load the Oracle JDBC driver
		DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

		String serverName = "csor6.bsu.edu";
		String portNumber = "1521";
		String sid = "or6db";
		String url ="jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
		Boolean running = true;

		Connection conn = DriverManager.getConnection (url, "ltmartin", "4910");

		System.out.println ("Oracle or6db is connected.");

		// Create a statement
		Statement stmt = conn.createStatement();

		// Do the SQL 
		Scanner kb = new Scanner(System.in);

		while (running){
			String answer = Database.show_menu(kb);
			
			if (answer.equalsIgnoreCase("1")) {
				ResultSet rset1 = stmt.executeQuery ("select * from employee");
				System.out.println("\nEmployee table:");
				System.out.println("--------------------------------------");
				
				while (rset1.next()){
					System.out.println (rset1.getString (1) + "\t| " + rset1.getString(2) 
						  + "\t| " + rset1.getString(3)
						  + "\t| " + rset1.getString(4)
						  + "\t| "+ rset1.getString(5)
						  + "\t| " + rset1.getString(6)); 
				}
				
				rset1.close();			
			}else if (answer.equalsIgnoreCase("2")) {
				System.out.println("Enter Name:");
				String name = kb.nextLine();

				System.out.println("Enter SSN:");
				String ssn = kb.nextLine();
				
				System.out.println("Enter DOB (yyyy-MM-dd):");
				String date = kb.nextLine();

				System.out.println("Enter Position:");
				String position = kb.nextLine();

				System.out.println("Enter Address:");
				String addy = kb.nextLine();

				System.out.println("Enter Salary:");
				String salary = kb.nextLine();

				System.out.println("Enter stage working at:");
				int stage = kb.nextInt();

				String sql1 = "insert into employee values('" + name +"' , '" + java.lang.Integer.parseInt(ssn) + "', to_date('" + date + "', 'yyyy-MM-dd'), '"+ position + "', '" + addy + "', " + java.lang.Integer.parseInt(salary) + ", " + stage + ")";

				stmt.executeUpdate(sql1);

			}else if(answer.equalsIgnoreCase("3")){
				ResultSet rset2 = stmt.executeQuery("select name, salary, works_at from employee where position = 'Stage Manager' order by name");
				System.out.println("\nStage managers:");
				System.out.println("Name\t\t  Salary  Stage");
				System.out.println("-------------------------------");

				while(rset2.next()){
					System.out.println (rset2.getString (1) + "\t| " + rset2.getString(2) 
							  + "\t| " + rset2.getString(3));
					System.out.println("-------------------------------");
				}
				
				rset2.close();
				
			}else if (answer.equalsIgnoreCase("4")){
				System.out.println("\nStage Table:");
				System.out.println("Stage\t  Capacity");
				System.out.println("-------------------------------");
				ResultSet rset3 = stmt.executeQuery("select * from stage");
			
				while (rset3.next()) {
					System.out.println (rset3.getString (1) + "\t| " + rset3.getString(2));
				}
				
				rset3.close();
			
			}else if (answer.equalsIgnoreCase("5")){
				System.out.print("\nStage number:");
				int stagenum = kb.nextInt();
				System.out.println();
				System.out.println("Capacity");
				int cap = kb.nextInt();

				String sql2 = "insert into stage " + "values("+stagenum+ " ," +cap+")";
				stmt.executeUpdate(sql2);
			}else if (answer.equalsIgnoreCase("6")){
				System.out.println("\nShow Table:");
				System.out.println("-------------------------------");
				ResultSet rset4 = stmt.executeQuery("select * from show");
				
				while (rset4.next()){
					System.out.println (rset4.getString (1) + "\t| " + rset4.getString(2));
				}
			
			}else if (answer.equalsIgnoreCase("7")){
				System.out.print("Enter ID: ");
				String id = kb.nextLine();

				System.out.print("Enter Location: ");
				String location = kb.nextLine();

				System.out.print("Start Time (yyyy-MM-dd): ");
				String date = kb.nextLine();
				
				String sql3 = "insert into show values(to_date('" + date + "', 'yyyy-MM-dd'), " + java.lang.Integer.parseInt(id) + ", " + java.lang.Integer.parseInt(location) + ")";
				stmt.executeUpdate(sql3);
			}else if (answer.equalsIgnoreCase("8")){
				System.out.println("\nBand Table:");
				System.out.println("-------------------------------");
				ResultSet rset5 = stmt.executeQuery("select * from band");
				
				while (rset5.next()){
					System.out.println (rset5.getString(1) + ": " + rset5.getString(2) + " (Show: " + rset5.getString(3) + ")");
				}
				rset5.close();
			}else if (answer.equalsIgnoreCase("9")){
				System.out.print("Please enter a show number: ");
				int bandlocal = kb.nextInt();

				ResultSet rset6 = stmt.executeQuery("select band_name from band where plays_at = " + bandlocal);
				System.out.println("\nBand Name");
				System.out.println("-------------------------------");
				
				while(rset6.next()){
					System.out.println(rset6.getString(1));
				}
				rset6.close();
			}else if (answer.equalsIgnoreCase("10")){
				System.out.print("Please enter a ticket number: ");
				int ticketnumber = kb.nextInt();

				ResultSet rset6 = stmt.executeQuery("select price, seats_available, purchased_for from ticket where ticket_number = " + ticketnumber);
				System.out.println("\nPrice   | Seats | Purchased For");
				System.out.println("-------------------------------");
				
				while(rset6.next()){
					System.out.println(rset6.getString(1) + "\t| " + rset6.getString(2) + "\t| " + rset6.getString(3));
				}
				rset6.close();
			}else if (answer.equalsIgnoreCase("q")){
				break;
			}
		}
		// Close the statement
		stmt.close();

		// Close the connection
		conn.close();
	}
	
	public static String show_menu(Scanner kb){
		System.out.println("\n%%%%%%%%%%%%%%%%%%%%%%%%%%");
		System.out.println("% Menu                   %");
		System.out.println("%%%%%%%%%%%%%%%%%%%%%%%%%%");
		System.out.println("1) View employees");
		System.out.println("2) Add employee");
		System.out.println("3) See stage managers");
		System.out.println("4) Show stages");
		System.out.println("5) Add stage");
		System.out.println("6) List shows");
		System.out.println("7) Create show");
		System.out.println("8) Show bands");
		System.out.println("9) View bands at certain show");
		System.out.println("10) View ticket info");
		System.out.println("q) Quit");
		System.out.print("Please enter a choice: ");
		return kb.nextLine();
	
	}
}