Embedding SQL commands in Java program

Need to write sql queries and embed them in the given java program template only and give commentds to the program.

import java.sql.*;
import java.io.*;
class lab4
{
static BufferedReader keyboard;
static Connection conn;
static Statement stmt;
public static void main (String args [])
throws IOException
{
String username=”username”, password = “pwd”;
String ename;
int original_empno=0;
int empno;
keyboard = new BufferedReader(new InputStreamReader (System.in));
try {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
System.out.println(“Registered the driver…”);
conn = DriverManager.getConnection (
“jdbc:oracle:thin:@oracle1.sql.edu:1200/sql.sql.edu”,
username, password);
conn.setAutoCommit(false);
System.out.println(“logged into oracle as ” + username);
stmt = conn.createStatement ();
ResultSet rset=stmt.executeQuery (“select TNAME from TAB”);
while (rset.next()) {
System.out.println( rset.getString(1) );
}
}
rset.close();
catch(SQLException e)
{
System.out.println(“Caught SQL Exception: \n
}
}
” + e);
For this lab, we are going to experiment with embedding ORACLE/SQL commands in a “Java”
program. Perform each of the tasks listed below. When you have finished, submit your source code.
1. (15 pts) Determine the total minutes played by each player who has played in at least one game.
(Answer will have columns Player Name, Team Name, Player Number and “Total Minutes
Played”.
2. (15 pts) Prompt for a Game ID and retrieve the name of the stadium where that game was
played. (Answer will have column STADIUMNAME).
3. (20 pts) Update the stadium for a team in the following way: prompt for a team name; if it is
not in the team table, print an error message and return to the main menu. Next, show the
current stadium name for this team. Then, prompt for the new stadium name for this team. If it
is not in the Stadium table, print an error message and return to the main menu. Otherwise,
update the stadium name for this team. This needs to be done on the tables in your account.
4. (20 pts) Print a report of who played in a given game. First, prompt for the Game ID. Second,
print a table of players who played for the home team. Third, print a table of players who
played for the away team. For each table, print an appropriate header (Home/Away and the
Team Name at least) and then print columns Player Name, Player Number, and Minutes
Played. If a player is neither associated with the home team nor the away team, you may
assume this is an error and ignore it..
NOTE: the queries in parts 1-4 must be presented to the user as a menu choice, i.e. “Enter 1 to display
the total minutes played report, 2 to print the stadium for a game, 3 to update the stadium for a team, 4
to show the Who Played report, 5 to exit.” This must loop until the user enters “5” (5 pts for proper
looping). Don’t forget to comment (10 pts) and use good programming style (10 pts).
To compile and run your program, you need to do the following:
•
•
To compile, type “javac lab.java”.
To run your successful compilation, type “java lab”.
SQL> describe stadium
Name
Null?
Type
STADIUMNAME
STADIUMSTATE
STADIUMCITY
STADIUMSTREET
NOT NULL CHAR (15)
CHAR (2)
VARCHAR2 (20)
VARCHAR2 (25)
SQL> describe team
Name
Null?
Type
TEAMNAME
TEAMSTATE
TEAMCITY
STADIUMNAME
NOT NULL CHAR (15)
CHAR (2)
VARCHAR2 (20)
CHAR (15)
SQL> describe game
Name
Null?
Type
GAMEID
GAMEDATETIME
HOME TEAM
AWAYTEAM
NOT NULL NUMBER
DATE
NOT NULL CHAR (15)
NOT NULL CHAR (15)
SQL> describe played in
Name
Null?
Type
GAMEID
PLAYERNAME
TEAMNAME
PLAYERNUMBER
MINUTESPLAYED
NOT NULL NUMBER
NOT NULL CHAR (25)
NOT NULL CHAR (15)
NOT NULL NUMBER (2)
NUMBER (4)
SQL> describe player
Name
Null?
Type
PLAYERNAME
TEAMNAME
PLAYERNUMBER
POSITION
NOT NULL CHAR (25)
NOT NULL CHAR (15)
NOT NULL NUMBER (2)
VARCHAR2 (15)