CPSC 343 Database Theory and Practice Fall 2017

DB Application - Java

Over the course of these exercises, you'll create a rudimentary photo-management system. While your application will lack many useful features, you will gain practice with using JDBC to connect to a database - and will implement several things that may be useful in your final project.


It is assumed that you are familiar with using Eclipse, the idea of event-driven programming, and the basics of components, layout managers, and event handling using Swing. If not, ask!

Photo Album

These exercises involve the development of a basic photo management system. Each user can maintain an album of photos. Photos may be tagged with keywords (a given photo may have multiple keywords). The user may view all of the pictures in her album or select just those with a certain keyword. Users are identified by a username and password (so they must log in before viewing or modifying an album). Users may create new accounts, add photos to their album, or add keywords to a photo.

The username and password associated with each user of the photo-management system is distinct from the username and password which allows you to connect to the MySQL server and carry out queries. In the latter case, MySQL handles verifying the correctness of the password and enforcing the permissions associated with the user, while in the former case, the database only stores the necessary information - it is up to the database application (i.e. your web application) to use that information to verify the correctness of the password and to enforce any necessary access permissions.

The necessary data - usernames and passwords, URLs of the images in each album, and the keywords for each image - will be stored in the database with the following schema:

USER ( username, password )
ALBUM ( username, url, id )
KEYWORD ( id, keyword )

USER stores information about users and their passwords, ALBUM links images to users, and KEYWORD links keywords to images. id is numerical ID used to uniquely identify entries in the ALBUM table. ALBUM.username is a foreign key which refers to USER.username and KEYWORD.id is a foreign key which refers to ALBUM.id.

Use the database username_photoalbum (where username is replaced by your username) for the exercises. The user accounts arthur and ford have passwords tea and betelgeuse, respectively.

The full application will be built in stages. The first stage is to display a hardcoded set of photos:

  1. Create a class Album to represent the contents of a photo album. It should have an instance variable that is a list of URLs (type String) for images. Include a constructor that initializes the list to contain at least six URLs (hardcode specific URLs for now). You can find a collection of images to choose from in Use absolute URLs (e.g., not just 108_0822.jpg). Also include a getter which returns the list of image URLs.

  2. Create a class AlbumFrame to display the contents of the photo album. AlbumFrame should extend JFrame and display thumbnails of the images in the album, similar to what is shown below. The thumbnails should be arranged in a grid with no more than five images per row. Each thumbnail image should have dimensions 100x100 (regardless of the actual image size). AlbumFrame should have an instance variable of type Album which is initialized in the constructor.

    Be sure to correctly handle any number of images - modifying the list of images in the Album constructor should be all that is necessary to display a different set of images or a different number of images.

    photoalbum screenshot


  3. Create a main program to create an instance of AlbumFrame so you can test the program.

Stage two is to retrieve a user's album from the database instead of having the set of images hardcoded.

In addition to what is described below, your solution should also do the following things:

  1. Create a class AlbumLoader to handle retrieving picture and album information from the database. Define the following constants:

    And include the following methods:

    Store the currently open connection (if any) in an instance variable.

    Use prepared statements for getAlbum and the two versions of getKeywords. Avoid duplicates in keywords and images. All methods other than openConnection assume that a connection has already been opened.

  2. Finish adding support for loading a user's album from the database:

    1. Modify Album:

      • Add instance variables to Album for the current user (a String) and an AlbumLoader. Initialize both in the constructor. It is OK to hardcode the current user's name (arthur is a user with album contents; ford is a user with an empty album).

      • Change the initialization of the list of images to just be an empty list, and add a reload() method which gets the current user's images and uses that to initialize the list of images.

    2. Modify the user interface:

      • Add a menubar to AlbumFrame with a menu Album and a menu item Load.

      • Add an event handler for Load which calls reload() on the Album and then updates the AlbumFrame's display accordingly. If there are no images in the album, the message "Album is empty" should be displayed instead of the grid of thumbnails.


  3. Display the keywords associated with images in the user's album:

    1. Modify Album:

      • Add an instance variable for the list of keywords. Initialize it to the empty list in the constructor.

      • Update Album's reload() method to also get the keywords for the current user's images, which is used to initialize the list of keywords.

    2. Modify the user interface:

      • Add a combobox containing an alphabetical list of the album's keywords above the thumbnail display.

      • Update Load's event handler to also update the contents of the combobox of keywords from the keyword list in Album.

  4. Display the keywords associated with individual images: Have clicking on a thumbnail image bring up a dialog box displaying the full-size image (up to a bit smaller than the size of the screen) along with the keywords associated with that image, similar to the following (but with the full size image). If there are no keywords for an image, "No Keywords" should be displayed instead of the list of keywords.

    edit keyword page


This stage completes the basic photo-management system.

In addition to what is described below, your solution should also do the following things:

Keep in mind that you can connect to the database via MySQL Workbench. This is useful for debugging actions that change the database - you can check the contents of tables after you've tried to insert something, or you can fix up tables if something got inserted improperly.

  1. Add the following methods to AlbumLoader:

    A note on passwords: MySQL provides a way to encrypt passwords so that they are not stored in plaintext. The way to do this is via the SHA function:

          INSERT INTO USER(username,password) VALUES ('arthur',SHA('tea'))

    would add a new user 'arthur' to the USER table, storing the encrypted version of 'tea' as the password. (You'll need to substitute the actual values entered in the form for 'arthur' and 'tea'.) SHA implements SHA-1, a version of the Secure Hash Algorithm.

    There are two ways logging in can be handled - one is to query the database for the password which goes along with the username that was entered and have the application check if they are the same, while the other is to query the database for the entry which matches the username and password and have the application check whether or not such a row was found. However, the first option doesn't work if the database stores encrypted passwords - because one feature of the encryption is that it is hard to figure out the original password given just the encrypted one, and without knowing how the SHA function works, the application can't encrypt the user's input to compare to what was retrieved from the database. But remember that passwords are stored encrypted, so you'll need to use SHA('tea') instead of just 'tea' (substituting the user's actual value for 'tea').

    Also note: If you look at the table definition for ALBUM, you'll see that ALBUM.id is an auto-increment field. This means that you do not have to supply a value for it when inserting a new row into ALBUM - the system will automatically pick a unique value. For example, you could insert into ALBUM as follows:

    	INSERT INTO ALBUM(username,url) VALUES ('arthur','')

    Auto-increment fields are handy when you want to have a unique integer ID as a primary key (because, say, the actual PK field - like the URL in this case - is long and it wastes space to store that over and over in KEYWORD), but there isn't any meaning to particular values.

  2. Support user accounts:

    1. Add a Create Account item to the Album menu. It should bring up a dialog box where the user can enter their desired username and pasword. When the user clicks "OK", the account should be created and the current user set accordingly.

    2. Add a Login item to the Album menu. It should bring up a dialog box where the user can enter their username and password. When the user clicks "OK", the login information should be checked. If it is correct, the current user should be set accordingly.

    3. Update the behavior of Load Album so that it works with the current user rather than a hardcoded user.

  3. Add the ability to add images to the album:

    1. Add a Add Image menu item to the Album menu.

    2. Add an event handler which pops up a dialog box to prompt for the URL of the image. When the user clicks "OK", the specified URL should be added to the current user's album and the display of the album should be updated to include the new image.

Additional Practice

For additional practice, add some additional features to the photo management system. Add methods to AlbumLoader as needed - no other class should access the DB directly.

  1. Add the ability to add keywords to an image:

    1. Add a text field and an "add keyword" button to the full-size image / keywords display window.

    2. Clicking the button should add the keyword to the image and the list of keywords should be updated, both in the image display window and the album display window. It should end up looking something like the following: (though without the "return to album view" button)

      edit keyword page

      There is a potential wrinkle to the insertion. Given the schema for the KEYWORD table, you might expect to write the following to add the keyword "Ruapehu" to image 1:

            INSERT INTO KEYWORD(keyword,id) VALUES ('Ruapehu',1);

      The problem is that you don't know the id for the image - but you do know the URL. One solution is to query the ALBUM table for the row matching the current user (also from a session variable) and image URL, and then do the INSERT using the id that was retrieved in the query. Another solution is to use a version of INSERT which allows you to do both of these steps at the same time (i.e. to insert data retrieved from another table):

            INSERT INTO KEYWORD(keyword,id) SELECT 'Ruapehu',id 
                                            FROM ALBUM
                                            WHERE url='' AND username='arthur'

      This says to insert the rows retrieved by the SELECT query into KEYWORD, where the first column of the SELECT result goes into the keyword column of KEYWORD and the second column goes into the id column of KEYWORD. Also note the use of a literal in the SELECT clause - SELECT 'Ruapehu',id means that for every row of ALBUM which matches the WHERE condition, the result will contain a row whose first column is 'Ruapehu' and whose second column is the value of the id field on that row. Of course, don't forget to substitute the appropriate values retrieved from session variables and forms instead of hard-coding values like "Ruapehu", "" and "arthur".

  2. Add support for viewing just those images with a specific keyword:

    1. Add "show all" to the top of the combobox of keywords.

    2. Add a button "filter album" next to the list of keywords. Add an event handler to extract the keyword to filter by from the form. If the keyword is anything but "show all", the current filter should be set to the selected keyword. (Otherwise the current filter should be unset.)

    3. Refresh the thumbnails displayed so that just the images with the specified keyword are shown.

In the exercises above, usernames and passwords are stored in a DBMS-user-defined table rather than a DBMS system table and authentication and authorization are handled entirely by the application. (It is up to the application to check that the username and password are correct, and then only send subsequent queries to access the photo album if the login was successful.) This has the advantages that user credentials only need to be sent once (for the initial authentication) and that it is very flexible - different applications can handle authentication and authorization differently, even with the same database - but also has the significant drawback that any such scheme is easily bypassed by simply querying the DB directly.

An alternative (while still using the "one big application user" model) is to bundle authorization into the DB access by creating stored routines which check for a valid login before carrying out the desired task. DBMS user accounts would then be granted access only to the stored routines so that the authorization checks cannot be bypassed, but authentication information has to be sent (and checked) with every request.

  1. For each task below, do three things:

    1. Given a username and an (unencrypted) password, create a new user account.

    2. Given a username and an (unencrypted) password, determine whether the login is valid (i.e. the information corresponds to an existing account).

    3. Given a username and an (unencrypted) password, retrieve the user's photo album (the URLs of the images in the album) if the login is valid and generate an error (SIGNAL SQLSTATE '02000' in that case) if not.

    4. Given a username, a URL, and a keyword, add the keyword to the specified photo in that user's album. If the photo is not already in the user's album, also add the photo to the album.

    5. Given a URL and a keyword, add a global keyword to the specified photo - that is, add the keyword to that photo in every album in which it appears.

Valid HTML 4.01!