HW 5留学生辅导、讲解SQL Programming、辅导java编程、讲解SQL设计辅导Web开发|辅导R语言编程

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:
Username:
Password:
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
* search

* book
* reservations
* cancel
* 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 is5/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

HW 5留学生辅导、讲解SQL Programming、辅导java编程、讲解SQL设计 辅导Web开发|辅导R语言编程

HW 5留学生辅导、讲解SQL Programming、辅导java编程、讲解SQL设计辅导Web开发|辅导R语言编程