HW 5留学生代写、代做SQL Programming、代写java编程、代做SQL设计

日期：2019-05-29 11:05

5/27/2019 HW 5 - SQL Programming

https://canvas.uw.edu/courses/1273861/assignments/4733918 1/5

HW 5 - SQL Programming

Due Wednesday by 11:59pm Points 25 Submitting a text entry box or a file upload

Available May 8 at 8:30am - May 29 at 11:59pm 22 days

Submit Assignment

What to turn in:

Submit two files. First, setup.sql should contain the SQL statements you used to create your schema.

Second, FlightsDB.java should contain your updated implementation of the client, which uses the schema

you created.

Resources:

Starter code - hw5.zip

In this assignment, you will have two broad tasks. First, you need to design the rest of the database that will

store information about customers and the flights they have booked. Second, you will need to write the Java

code to query and update the database from the command-line client.

Getting Started

The command-line application is written in Java. When you download the starter code and unpack it,

you will see a number of files:

FlightsApp.java is the entry point for the command-line application. This class is complete, and you

should not need to modify it.

FlightsDB.java is a class that handles querying and updating the database. This is class is only partially

complete, and your job (after designing your schema) will be to complete it's implementation.

User.java and Flight.java are helper classes for the application. You will need to use these (but not

modify them), so look through the code to see how they work. (Both are immutable classes with only a

constructor and no methods.)

sqljdbc4.jar is Microsoft's driver for providing JDBC access to SQL Server. You will need this on your

classpath when running the application.

dbconn.properties contains information on how to connect to the class server. You will need to edit this

file.

Start by opening up dbconn.properties in a text editor. Fill in your username and password, and database

name for connecting to our class server. (If you run into problems connecting, double check that the

database name, shown after database= in flightservice.url matches the name of your database.

In summary, these are the credentials you will use.

5/27/2019 HW 5 - SQL Programming

https://canvas.uw.edu/courses/1273861/assignments/4733918 2/5

Class server: kfleming.info330.ischool.uw.edu

Database name: <your-group>

Username: <your-group>

Password: <your-group>

Use the test database for HW5.

You can use jGRASP as your IDE, and you'll have to set your class path to setup the JDBC driver. We'll be

going over this in lab. Once that is done, you can compile and run the main java application, FlightsApp.java .

For running multiple instances of your program to test out your transactions (later in the assignment), you

cannot do this within jGRASP. Instead, open the terminal and change into the directory where you unpacked

the Java code. Then execute the following commands. (Windows users: change the : (colon) to a ;

(semicolon) on the line starting java -cp ... )

mkdir bin

javac -d bin *.java

java -cp bin:sqljdbc4.jar FlightsApp

(If you get a "command not found" error about javac , then you need to install the JDK (if you haven't

already) and make sure that the JDK directory containing the javac and java tools is on your path.)

You can instead use Eclipse to compile and run the application. To do this, create a new workspace and

project in Eclipse. Copy the .java files you unpacked above into the project src/ folder. Copy

the .jar and .properties files into the project root folder. Select "Build Path > Configure Build Path > Add

Jars" via the menu, and then select the sqljdbc4.jar you copied in just before. Once that is all done, you

should be able to use the "Run" button to run the app.

When you run the application, it will prompt you for a command. If you type help , it will show you a list of the

commands its supports:

Supported commands:

* login <handle> <password>

* search <origin-city> <dest-city> <day-of-month>

* book <itinerary-num>

* reservations

* cancel <itinerary-num>

* quit

Only two of these commands work at the moment: search and quit. The other commands will not do much

of anything yet because they require functionality in FlightsDB that you will write later on.

Nonetheless, here is a brief description of what each command is supposed to do:

login takes a user's handle (a short username) and password and checks that they exist in the

database.

5/27/2019 HW 5 - SQL Programming

https://canvas.uw.edu/courses/1273861/assignments/4733918 3/5

search shows a list of all the one- and two-hop itineraries for flying from the given origin to the given

destination on the given day of the month. (The search is always limited to the month of July 2015, so

the user only needs to specify which day in that month.)

book allows the logged in user to reserve seats on the flights of an itinerary just printed by search. They

do so by passing in the number listed next to that itinerary in the search output.

reservations shows all of the logged in user's current reservations.

cancel allows the logged in user to cancel a reservation made earlier. As with book, they do so by

passing in the number listed next to that itinerary in the reservations output.

You should be able to use search now, but the other commands will mostly do nothing at this point. Each

command will work once you have filled in the missing code in FlightsDB that they rely on.

Problems

Problem 1: Stop SQL Injection [5 points]

The starter code you are given has a serious problem: it is vulnerable to SQL injection attacks.

Use the test database.

To see this, start by performing the following search:

search "Seattle" "Las Vegas" 7

In short, when that destination city is pasted into the SQL query, SQL Server does not parse it as a single

string. Instead, the apostrophe in the text ends that string and the remainder of the text adds additional

conditions to the SQL query itself.

This example was fairly harmless, but it is possible to use the same approach to cause more significant

damage. In particular, it would be easy to drop tables in the database!

You will fix this problem by changing the uses of the Statement class in FlightsDB.java to uses of

PreparedStatement instead. The latter lets you write queries with placeholders (written as ? ) where

parameters are to be inserted. However, unlike with simple text substitution, JDBC will make sure that no

SQL injection is allowed.

See the lecture slides for an example of how to use PreparedStatement or, alternatively, read the

official documentation (https://docs.oracle.com/javase/8/docs/api/java/sql/PreparedStatement.html) .

Once you have removed all uses of Statement , delete the Java import of that class. That will help prevent

you from using it in any of the later parts of the assignment. (You should be using PreparedStatement from

here on.) Anything you turn in that is susceptible to SQL injection (due to using Statement rather than

PreparedStatement ) will receive very little credit.

Problem 2: Support Login [5 points]

5/27/2019 HW 5 - SQL Programming

https://canvas.uw.edu/courses/1273861/assignments/4733918 4/5

Implement the logIn function in FlightsDB.java by having it query your Customer table to see if there is a

user with the given handle and password. If so, return a new User object with the full information about that

user. Otherwise, return null to indicate that login failed.

You should now be able to use the login command in the command-line application.

Problem 3: Display Reservations [5 points]

Implement the getReservations functions in FlightsDB.java by having it query your tables to find a complete

list of all the flights on which the given user is booked. For each flight, create a new Flight object containing

all of the required information (see Flight.java to see what information it requires).

You can test your implementation more easily if you already have some reservations in the database. Use

some INSERT INTO ... statements to add reservations (if you have not done so already) and include these

statements in setup.sql .

Once that is done, you should be able to use the reservations command to see a list of the reservations for

the logged in user.

Problem 4: Removing Reservations [5 points]

Implement the removeReservations function in FlightsDB.java so that it removes the given user's reservations

on all of the given flights.

To be safe, you will want to implement all of these removals in a transaction. That will eliminate the

possibility of ending up removing the user from one hop in their itinerary but not the other.

We have provided helper functions to make this easier. To use a transaction, you can simply call

beginTransaction() at the start of the function and commitTransaction() at the end.

Problem 5: Adding Reservations [5 points]

Your last task is to implement the addReservations function in FlightsDB.java so that it adds reservations for

the given user on each of the given flights provided that doing so would not violate either of the following

constraints:

1. One user cannot reserve multiple itineraries in the same day. (I.e., if they already have a reservation on

that day, then they cannot make another one.)

2. No more than three users can make reservations on the same flight.

If you find that either of these constraints would fail, then you will return an error code (see FlightsDB.java for

details) indicating the failure. If neither fails, then you can go ahead and add the reservations.

As in problem 5, be sure to implement all of these SQL operations (checking the constraints and then, if it is

okay, adding the reservations) in one transaction.

To test that your transactions are working properly, start by inserting an arbitrary length pause between

when you check he constraints and when you add the reservations. The easiest way to do that is to write

"Press any key to continue..." to System.out and then call System.in.read() , which will pause until a key is

5/27/2019 HW 5 - SQL Programming

https://canvas.uw.edu/courses/1273861/assignments/4733918 5/5

Total Points: 30.0

Some Rubric (1)

Criteria Ratings Pts

pressed. Then, you can run two clients at the same time and control when each one reads and writes. Try

letting the first client start to book a reservation on a flight and, while it is paused after checking the

constraints, have the second client try to book another flight on the same day for the same user. If

everything is working correctly, SQL Server should prevent this.

Once you have tested adding reservations (and removing them again), you are done. Turn in your final

version of FlightsDB.java , in which all of the operations are now functional, along with the setup.sql file that

you wrote earlier.

Schema Design

Stop SQL Injection

Support Login

Display Reservations

Removing Reservations

Adding Reservations