CSE 344: Section 6

Indexing +

HW6 Flights App

May 4th, 2023

Administrivia

• Quiz 3 due May 5th at 10pm
• HW6 Milestone 1 due May 9th at 10pm
• HW6 Milestone 2 due May 16th at 10pm

​

​

Indexing

Indexes

• Goal: Only access the tuples you need instead of reading the entire file
• Result: Significantly speeds up queries
• Creating an index

​

• Multi-Attribute Index

​

​

Example

Types of Indexes

• Clustered
• Main data file is sorted on the attribute
• Useful for point selections and range queries
• You can only create one per table
• Unclustered
• Main data file is NOT sorted on the attribute
• Useful for point selections, not so great for range queries
• You can have multiple for one table

Never store passwords in plaintext!

Hashing

• Pass the plaintext password through a cryptographic hash function
• Store the hashed password in the database instead
• Problem: You are guaranteed to have users with bad passwords :(
• Hashing their passwords won’t help

Salting

• Adds a small amount of randomness to users’ passwords
• Makes identical passwords not have identical hashes

HW6 Hashing/Salting

• Don’t need to store salt in a separate column
• ​

HW6 Demo

PreparedStatements

• How they work:
• Your SQL Connection Object parses the String passed to the `prepareStatement` function and pre-compiles it to a parameterized SQL query
• The “?” in the query are interpreted as parameters that can be inserted via calls on the PreparedStatement Object

​

String rawQuery = “SELECT * FROM Flights WHERE origin_city = ? AND day_of_month = ?”;

PreparedStatement ps = conn.prepareStatement(rawQuery); // Pre-compiles the query into a PreparedStatement Object

​

ps.clearParameters(); // Clears parameters from previous use

ps.setString(1, originCity); // Sets the first parameter (the first “?”) to the value of the variable “originCity”

ps.setInt(2, dayOfMonth); // Sets the second parameter (the second “?”) to the value of the variable “dayOfMonth”

ResultSet rs = ps.executeQuery(); // Executes the query and stores the ResultSet in the variable “rs”

ResultSet

• A ResultSet represents a table of data returned from executing a query
• It maintains an internal cursor that points to the current row of data, initially that cursor is positioned before the first row so the first call to `next` moves the cursor to the first row
• Makes it ideal for a while loop:

​

// ... continued from previous slide

ResultSet rs = ps.executeQuery();

​

while (rs.next()) { // check if there is another row and move to the next row

String destCity = rs.getString(“dest_city”); // Gets the value of the attribute “dest_city” for the current row

...

} // When `next` finally returns false, we know we’ve seen every row

rs.close(); // Remember to close the ResultSet

Final HW6 Notes

• The `Flight` class is there for you to use if you like, feel free to modify it, add methods, etc.
• You can also add additional classes as you see fit, preferably add them as internal classes so all your code is in Query.java
• It might be helpful to create a class that represents an Itinerary, if it implements the `Comparable` interface then you can use `Collections.sort` to sort it (this would be helpful for sorting one-hop and direct flights with one another)