Chapter 17 Lab Databases

Lab Objectives

Be able to write SQL statements.

Be able to retrieve information from a database.

Be able to add information to a database.

Be able to change information in multiple tables of the database.

Introduction A recreation director needs to keep track of summer T-ball teams, games, and season

records. He has hired you to write a computer program to make his life easier. You have

created a menu driven program that allows the director to use a database to easily

manipulate teams, games, and scores. It also allows the database to be refreshed for a new

year. Complete the tasks below to enable all the functionality that is required for this

application.

Task #1 Database Connection and Initialization

2.Modify the DB_URL constant at the beginning of the main method as directed by your instructor to connect with your database correctly.

3. Create and initialize the database by running the program and selecting option

6. 4.If, at any point after the initialization, you wish to start over, you can select option

6, which will remove the database tables and recreate and initialize them.

5.The Teams table created and initialized in the database is as follows:

TeamName Wins Losses Ties

Astros 0 0 0

Brewers 0

Cubs 0

Marlins 0

0

0

0

6.The Games table created an initialized in the database is as follows:

GameNumber HomeTeam

HomeTeamScore

VisitorTeam

VisitorTeamScore

1 Astros

0

Brewers 0

2 Brewers 0

Cubs 0

3 Cubs 0

Astros 0

Task #2

Retrieve Information Using SQL

1.Select option 1 to view the teams. Your output should be as follows:

Team Name Win Lose Tie

Astros 0 0 0

Brewers 0 0 0

Cubs 0 0 0

Marlins 0 0 0

2. Write the implementation for the viewSchedule method.

a. Write an SQL statement to select all columns from the

Games table.

b. Execute the query by sending the SQL statement to the DBMS.

c. Use a while loop to display the contents of the result set to the monitor.

d.Compile and run the program. Select option 2 and your output should be

as follows: GameID

Home

Score

Visitor

Score

1 Astros 0

Brewers 0

2 Brewers 0 Cubs 0

3 Cubs 0

Astros 0

Task # 3

Add Information Using SQL

1. Write the body of the loop in the addTeams method. You should prompt the user for the new team name, write the SQL statement and update the Teams table with the new team name.

2. Compile and run the program. Select option 3 and enter Braves . EnterN to stop

entering names. Select option 1 again to view the teams. Your output should now

look like: Team Name Win Lose Tie Astros

0 0 0

braves 000

Brewers 0

0

0

0

Task # 4

Change Information in Multiple Tables Using SQL

1. Edit the enterScores

method. Execute a query using the appropriate SQL statement to get the result set containing all information for the

gameNumber the user entered.

2.After the user inputs the scores for both teams, execute updates to the

Games table using the appropriate SQL statements.

3. In each branch of the logic determining winner, loser or tie: retrieve the number

from the appropriate column (losses, wins, or ties) from the Teams able for one

of the teams, increment it, and update the table for that team. Do the same for the

opposing team.

4. Select option 5 to enter scores. Enter scores as follows:

Game 1 Astros

5 Brewers

3 Game 2

Brewers 4

Cubs 4

Game 3

Cubs 1

Astros6

5.Select option 1 to view the team standings and option 2 to see the scores. Your

output should now be: Team Name Win

Lose

Tie

Astros

2

0

0

Braves

0

0

0

Brewers

0

1

1

Cubs

0

1

1

Marlins

0

0

0

List of games and scores:

GameID Home Score Visitor Score

1 Astros

5 Brewers

3 2rewers

4Cubs

4 3

Cubs1

Astros

mport java.util.Scanner;

import java

.sql.*;

/**

This program uses a database to keep track of

summer T

-

ball teams, games, and season records.

*/

public class TeamDB

{

public static void main(String[] args)

{

// Create a named constant for the database URL

// NOTE: T

his value is specific for Java DB

final String DB_URL = "jdbc:derby:TeamDB;" +

"create=true";

Connection conn;

try

{

// Create a connection to the database

conn = DriverManager.getConn

ection(URL);

char choice;

Scanner keyboard = new Scanner(System.in);

System.out.println("Welcome to the Sports " +

"Teams Database Manager!");

do

{

printMenu();

choice = keyboard.nextLine().charAt(0);

switch(choice)

{

case '0':

// Close the connection

conn.close();

break;

case '1':

viewT

eams(conn);

break;

case '2':

viewSchedule(conn);

break;

case '3':

addTeams(conn);

break;

case '4':

addG

ames(conn);

break;

case '5':

enterScores(conn);

break;

case '6':

beginNewSeason(conn);

break;

}

} while(choice != '

0');

}

catch(Exception ex)

{

System.out.println("ERROR: " + ex.getMessage());

}

}

/**

The printMenu method displays the menu choices

for the user to work with the database.

*/

public static void p

rintMenu()

{

System.out.println();

System.out.println("Select from the following " +

"options:");

System.out.println("1. View team standings");

System.out.println("2. View the schedule");

System.out

.println("3. Add a team");

System.out.println("4. Add a game to the schedule");

System.out.println("5. Enter game scores");

System.out.println("6. Begin a new season");

System.out.println("0. Exit the program");

}

/**

T

he beginNewSeason method is a utility method that

removes the tables and allows the user to reset the

database for a new season.

@param conn A connection to the database.

*/

public static void beginNewSeason(Connection conn)

{

try

{

Statement stmt = conn.createStatement();

// Remove the tables if they already exist

// Throws an exception if the tables do not exist

stmt.execute("DROP TABLE Games");

stmt.execute("DROP TABLE

Teams");

// Once the tables have been removed, call the

// method to create and initialize the tables

System.out.println("Reinitializing database " +

"for a new season");

createTeamDB(conn);

}

catch(Exception ex)

{

// Create the tables if they do not exist

System.out.println("Creating database for " +

"the first time");

createTeamDB(conn);

}

}

while(resul

t.next())

{

gameNumber++;

}

do

{

System.out.print("Enter the home " +

"team name: ");

homeTeam = keyboard.nextLine();

System.out.print("Enter

the visiting " +

"team name: ");

visitingTeam = keyboard.nextLine();

stmt.executeUpdate("INSERT INTO Games " +

"(GameNumber, HomeTeam, " +

"VisitorTeam) " +

"VALUES (" + gameNumber +

", '" + homeTeam + "', '" +

visitingTeam + "')");

System.out.print("Do you want to enter " +

"another game: ");

ans = keyboard.nextLine().charAt(0);

} while(ans == 'Y'|| ans == 'y');

}

catch(Exception ex)

{

System.out.println("ERROR: " + ex.getMessage());

}

}

/**

The viewTeams method displays a table listing the

team names and season records. Since teams have not

yet played, all numbers are zero.

@param conn A connection to the database.

*/

public static void viewTeams(Connection conn)

{

try

{

// Create a Statement object

Statement stmt = conn.createStatement();

// Send the statement to the DBMS

ResultSet result = stmt.executeQuery("SELECT * " +

"FROM " +

"Teams");

System.out.printf("%

-

15s %10s %10s %10s

\

n",

"Team Name", "Win", "Lose",

"Tie");

// Display the contents of the res

ult set

// The result set will have 5 columns

while(result.next())

{

System.out.printf("%

-

15s %10d %10d %10d

\

n",

result.getString("TeamName"),

result.getInt(

"Wins"),

result.getInt("Losses"),

result.getInt("Ties"));

}

}

catch(Exception ex)

{

System.out.println("ERROR: " + ex.getMessage());

}

}

/**

The viewSchedule method retrieves and displays the

teams and scores for all games.

@param conn A connection to the database.

*/

public static void viewSchedule(Connection conn)

{

try

{

// Create a Statement objec

t

Statement stmt = conn.createStatement();

// ADD LINES FOR TASK #2 HERE

// Create a string with a SELECT statement

// Send the statement to the DBMS

// This is a suggested column headings display

Sys

tem.out.println("List of games and scores:");

System.out.printf("%

-

6s %

-

20s %6s " +

"%

-

20s %6s

\

n",

"GameID", "Home", "Score",

"Visitor", "Score");

// AD

D LINES FOR TASK #2 HERE

// Use a while loop to display the result set

// The result set will have five columns

}

catch(Exception ex)

{

System.out.println("ERROR: " + ex.getMessage());

}

}

/**

The enterScores method allows user to enter scores

for both teams. The method will update the Games

table with the scores entered. It will also compare

the scores to determine the winning and losing teams

(or tie) and update the a

ppropriate column in the

Teams table for each team involved in the game.

@param conn A connection to the database.

*/

public static void enterScores(Connection conn)

{

Scanner keyboard = new Scanner(System.in);

try

{

char ans;

int gameNumber;

String homeTeam;

String visitingTeam;

int score1;

int score2;

String sqlStatement;

ResultSet result = null;

Statement stmt = conn.createStatement()

;

do

{

viewSchedule(conn);

System.out.print("Enter the game ID: ");

gameNumber = keyboard.nextInt();

// ADD LINES FOR TASK #4 HERE

// Get the result set from a query that

// selects all information for the gameNumber

// the user entered

if(result.next())

{

homeTeam = result.getString("HomeTeam");

visitingTeam = result.getString("Visitor" +

"Team");

System.out.print("Enter the score " +

"for the " + homeTeam);

score1 = keyboard.nextInt();

System.out.print("Enter the score " +

"for the " + visitingTeam);

score2 = keyboard.nextInt();

keyboard.nextLine();

// ADD LINES FOR TASK #4 HERE

// Execute an update to the Games table to

// store the score for each team of that

// game number

if(score1 < score2)

{

// ADD LINES FOR TASK #4 HERE

// Retrieve the number from the

// appropriate

column

// (wins, losses, or ties) for the

// home team, increment, and update

// that team's record.

// Do the same for the visiting team

}

else if(score

2 < score1)

{

// ADD LINES FOR TASK #4 HERE

// Retrieve the number from the

// appropriate column

// (wins, losses, or ties) for the

// home team, incr

ement, and update

// that team's record.

// Do the same for the visiting team

}

else

{

// ADD LINES FOR TASK #4 HERE

// Retrieve the number

from the

// appropriate column

// (wins, losses, or ties) for the

// home team, increment, and update

// that team's record.

// Do the same for the visiting team

}

}

System.out.print("Do you want to enter " +

"another game: ");

ans = keyboard.nextLine().charAt(0);

} while(ans == 'Y'|| ans == 'y');

}

catch(Exception

ex)

{

System.out.println("ERROR: " + ex.getMessage());

}

}

}