CPSC 343 Database Theory and Practice Fall 2024

DB Application — JavaFX

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.

Preliminaries

Setup

Handin

Exercises #1-9 are needed for full credit. Exercise #0 isn't required or graded, but is useful for making sure that you can successfully run a JavaFX application. Exercises #10-12 also aren't required or graded — they provide extra practice and include some elements that may be useful in your project.

Warmup

This isn't part of the photo album application, but it provides a test that you can successfully run JavaFX applications.

  1. Paste the following into a file called JavaFXDemo.java in your photoalbum project, then run it.

    import javafx.application.Application;
import javafx.scene.Scene;
import javafx.scene.control.Button;
import javafx.scene.control.TextField;
import javafx.scene.layout.BorderPane;
import javafx.stage.Stage;

/**
 * A basic JavaFX application showing basic controls and containers.
 */
public class JavaFXDemo extends Application {

	public static void main ( String[] args ) {
		launch(args);
	}

	@Override
	public void start ( Stage stage ) throws Exception {
		stage.setTitle("JavaFX Demo");

		BorderPane root = new BorderPane();
		Scene scene = new Scene(root);
		stage.setScene(scene);
		// keep the window from being resized by the user
		stage.setResizable(false);

		// controls
		TextField text = new TextField("");
		text.setPrefColumnCount(8);
		Button top = new Button("top");
		Button center = new Button("center");
		Button left = new Button("left");
		Button right = new Button("right");

		// add nodes to the root of the scene graph
		root.setTop(top);
		root.setCenter(center);
		root.setLeft(left);
		root.setRight(right);
		root.setBottom(text);

		stage.show();
	}

}

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 their 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. This just involves Java and JavaFX, without any use of the database.

  1. Create a class Album to represent the contents of a user's photo album. There should be methods getImages(), getKeywords(), and getKeywords(id) which return a list of image URLs (as strings), a list of all of the keywords associated with images in this album, and a list of the keywords associated with the specified image ID (an int), respectively. For now, getImages() should return a list of at least six hardcoded URLs. You can find a collection of images to choose from in http://bridgeman-vm.hws.edu/cs343f24/pics/. Use absolute URLs (e.g. http://bridgeman-vm.hws.edu/cs343f24/pics/108_0822.jpg, not just 108_0822.jpg). The two getKeywords methods should both return empty lists.

  2. Create a main program PhotoAlbum to display the contents of the photo album. The application window should have the title "Photo Album" and should display thumbnails of the images in the album along with the heading "Photo Album", similar to what is shown below. (Create and initialize an instance variable of type Album and display those images. For now hardcode "arthur" for the username.) 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). (You do not need to display a border around each image or the whole set of images, but you can investigate doing that if you want.)

    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

    Consult the reference information posted on the schedule page for information on using JavaFX, including components, layout managers, and event listeners. A few particular notes:





Stage two is to add the database connection — you will 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 Database 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. 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 so that it loads from the database instead of having hardcoded values:

      • Add instance variables to Album for the current user (a String) and a Database. Initialize both and open the connection in the constructor — the constructor should take the user as a parameter. Also update where the Album constructor is called in the main program — it is OK to hardcode the current user's name there (arthur is a user with album contents; ford is a user with an empty album).

      • Change the bodies of getImages and both getKeywords to call the corresponding method on the Database instance variable.

    2. Modify the user interface:

      • Add a menubar to the application window with a menu Album and a menu item Open.

      • Add an event handler for Open which updates the Album instance variable to be a new instance of Album (you can hardcode a user, either arthur or ford) and then updates the image display accordingly. (Remove the existing ImageViews and create/add new instances.) If there are no images in the album, the message "Album is empty" should be displayed instead of the grid of thumbnails.

    Reference:

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

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

    2. Update Open'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

    Reference:





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 Database:

    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','http://bridgeman-vm.hws.edu/cs343f24/pics/IMG_0176-edit.JPG')

    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 User menu with Login and Create Account items. The Login item 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 and the images and keywords from that user's album displayed. The Create Account item 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. Update the behavior of the Open menu item so that it works with the currently logged in 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 Features

Finally, add some additional features to the photo management system. These exercises are optional, but provide additional practice and include some features that may be useful in your project.

Add methods to Database 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 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='http://bridgeman-vm.hws.edu/cs343f24/pics/IMG_0081.JPG' 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 current values instead of hard-coding values like "Ruapehu", "http://bridgeman-vm.hws.edu/cs343f24/pics/IMG_0081.JPG" 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.

last updated: --Sat Oct 15 08:27:34 EDT 2022--
page owned by: bridgeman@hws.edu