Create an object oriented dynamic website with Java and MySQL

From Computing Study
Jump to: navigation, search

Introduction

Tutorial on how to create a dynamic (database driven) website using Object Oriented Java, Servlets, JSP (Java Server Pages), JSTL (Java Standard Tag Library), and a MySQL database. It is advised that you have a basic understanding of programming concepts, including variables, loops, conditional statements, and objects, although the majority of the code is provided and you may be able to work through it without. You will also be expected to have a basic understanding of HTML and CSS since these languages are not covered in the tutorial. The end result will be a music shop with features such as different types of search, shopping basket, login/register, save and retrieve order, and admin features.

You can try out this website and have a glimpse of the project you are about to make at https://markbellingham.me/Congo You will need the email addresses and passwords at the end of the Login and Register section to make full use of it, or just create your own user account.

Setup and Database Creation

This tutorial will be using a MySQL database, although because the web application is object oriented it can be adapted to use any database. I will also be using Eclipse as the Integrated Development Environment (IDE) and Tomcat as the web server. This tutorial will not cover installation and setup of these programs, but you can find excellent tutorials for installing and setting them up below.

Install and test MySQL.

Install Java JDK and Eclipse.

Install Apache Tomcat web server and create a Hello World web page.

If you are able to open a Hello World web page, that should be sufficient for this tutorial.

You will also need the MySQL connector to enable your Java application to open a MySQL connection

Next you need to create the database that will be used for this tutorial. Open this page and copy the text. Open a MySQL prompt in your terminal or command prompt, create and switch to a new database using the commands

CREATE NEW DATABASE congo;
USE congo;

Paste the text from the script into your MySQL prompt and confirm that it has worked correctly using the command

SHOW TABLES;

If you want to see the data in your tables, use the command

SELECT * FROM <table-name>

You can also use the following command to see information about each of the tables in your database using this command

DESCRIBE <table-name>;

If all this is confirmed as working correctly you are ready to start creating the web application project.

Models

You need to create a class for each of the tables in your database that maps your data into a format that your Java program can understand. This is where the MySQL command "DESCRIBE ;" comes in handy. Create a new class for each of your database tables using the same data types for the variables (e.g. INT as int and VARCHAR as String). Right click on your project in the left pane and choose New > Class, give it the same name as your DB table and put it in the package "com.congo.model". Don't create a Main method.
Create-class-music-recordings.png


Inside your class create variables for each of the attributes in your table.

private int recordingId;
private String artistName;
private String title;
private String category;
private String imageName;
private int num_tracks;
private float price;
private int stockCount;

Then you can either choose Source from the Eclipse menu or right click inside the class and choose Source. From here: Generate Constructor Using Fields and select all fields; Generate Constructor From Superclass; Generate toString() method; and Generate Getters and Setters and again select all. When you have finished your class should look something like this.

You need to repeat these steps for each of the tables in your database.

These classes are Java objects, which are used to transfer a collection of variables that are related to each other from one function to another. They are also referred to as the Model in our Model View Controller framework because they model the data coming from the database.

Database Connection

Now create a class that will connect to the database. Sometimes you will see tutorials that include the connection information in the DAO but I prefer to have it as a separate class for a few reasons. First, if in a bigger project you create several DAOs they can all use the same connection object so you only need to change the information in one place. This is also useful if you decide to change databases, for example from a MySQL to a PostgreSQL. Another reason is if you want to upload your code to a hosting service such as GitHub, you can tell git to ignore this connection file, while still uploading your DAO code. If you switch to using Spring or Hibernate you don't need this class at all, putting all your connection config in the settings, but that's a topic for a different tutorial.

In Eclipse, create a new project by right clicking in the Package Explorer on the left and choosing New > Dynamic Web Project. If you can't see it, choose Other and search for it. Call the new project 'Congo' and leave all the other settings as default. Click finish and let Eclipse create your new project.

Right click on the project (in the left pane) and choose New > Class. In the Package box write com.congo.dao and in the Name box use DBConnection. Leave all other settings as default and click Finish.

Create-connection-class.png

Inside 'public class DBConnection' add these two methods for creating and closing a connection. Replace the xxxxxx with your username and password for your installation of MySQL.

	// Method for connecting to the database, returns an open connection
	public Connection openConnection() {		
		String userid = "xxxxxx";
		String userpass = "xxxxxx";
		String url = "jdbc:mysql://localhost:3306/congo?verifyServerCertificate=false&useSSL=true";
		Connection conn = null;
		
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(url, userid, userpass);
		} catch (SQLException | InstantiationException | IllegalAccessException | ClassNotFoundException e) {
			System.err.println(e);
		}
		return conn;		
	}
	
	// Method for closing the connection to the database
	public static void closeConnection(Connection conn) {
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

You will need to import java.sql.Connection, java.sql.DriverManager, and java.sql.SQLException. Be sure to get the sql versions because there are a number of different ones. You will also need to import the MySQL connector that you downloaded earlier. Right click on the project, and select Properties. Choose "Java Build Path" on the left, click on "Add External JARs" button and navigate to the MySQL Connector JAR.

Java-build-path-mysql.png

Next select Deployment Assembly on the left and if your MySQL connector JAR is not listed, select Add... choose 'Java Build Path Entries', then Next and select your MySQL connector JAR. Then click Finish.

Deployment-assembly.png

Apply and Close the Properties dialogue.

All being well you are now ready to create your first Data Accessor Object (DAO).

Data Accessor Object

Create a new class called MusicDAO and place it in the same package as your connection object (com.congo.dao). Be sure to tick the box to add a Main method, which will be used for testing. This class should follow the Singleton design pattern, which ensures that there can only ever be one instance of this class at any one time. While this is not so important for SELECT queries, if you have multiple threads executing UPDATE or DELETE queries at the same time it raises the possibility of errors in your data. A Singleton design pattern has a private constructor, checks for an instance of the class and only creates a new one if one doesn't already exist. If you add this code outside of your Main method it will make this class a Singleton.

private static MusicDAO instance = null;

private MusicDAO() {}

public static synchronized MusicDAO getInstance() {
	if(instance == null) {
		instance = new MusicDAO();
	}
	return instance;
}

You are going to need 4 things that will be common to most of the functions in this class so create them at the top of the class.

private PreparedStatement pstmt = null;
private ResultSet resultSet = null;
private Connection conn = null;
private ArrayList<MusicRecordings> albums = null;

Prepared Statements are a way of executing database queries more efficiently, but more importantly they protect against SQL injection attacks. Sometimes you might see tutorials that use plain Statements. Don't use these, they will leave your application wide open to an attack. Sometimes you will see tutorials that combine Statements with an 'escape values' function, and while this will protect your database, if you forget just once to escape values you will again be vulnerable. By using Prepared Statements you are treating all of your queries as though they are potentially dangerous and dealing with them accordingly. The Result Set will contain all the results from your query, which can be accessed later using key/value pairs. Connection connects to the database. For these three, be sure to import the java.sql versions. ArrayList is how you will store the results in a Java friendly format, import this from java.util, and the ArrayList will be of type Music Recordings, for which you will need to import the class from your model that you created earlier.

Create a public function that returns an ArrayList of type MusicRecordings (or whatever you called your model class). Inside this function open a database connection, establish your query statement and execute it, with the results going in to the Result Set. At the moment your function looks a bit like this:

public ArrayList<MusicRecordings> findAllAlbums() {
    albums = new ArrayList<MusicRecordings>();

    conn = new DBConnection().openConnection();
    pstmt = conn.prepareStatement(FIND_ALL_ALBUMS);
    resultSet = pstmt.executeQuery();

    return albums;
}

Prepared Statement will be giving you an error (because it could potentially fail) so surround those three lines with try/catch, and also add a finally block that will close the database connection no matter what happens. Now your function looks more like this:

public ArrayList<MusicRecordings> findAllAlbums() {
        albums = new ArrayList<MusicRecordings>();

	try {
		conn = new DBConnection().openConnection();
		pstmt = conn.prepareStatement(FIND_ALL_ALBUMS);
		resultSet = pstmt.executeQuery();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBConnection.closeConnection(conn);
	}
	
	return albums;
}

It's still showing an error on FIND_ALL_ALBUMS, this is because it has been defined as a String literal but the String hasn't been created yet, so go ahead and create it. I prefer to store all of the SQL statements together so that if the database is exchanged for another and the syntax changes it's not too difficult to modify them.

private static String FIND_ALL_ALBUMS = "SELECT * FROM Music_Recordings ORDER BY artist_name";

With the results of the query in the Result Set you need to extract them and store them in the ArrayList. Create a new instance of the class MusicRecordings insude a while loop and call it 'album', use the loop to iterate over the Result Set and set the values of 'album', then add the album to the Arraylist 'albums'.

while(resultSet.next()) {
    MusicRecordings album = new MusicRecordings();
    album.setRecordingId(resultSet.getInt("recording_id"));
    album.setArtistName(resultSet.getString("artist_name"));
    album.setTitle(resultSet.getString("title"));
    album.setCategory(resultSet.getString("category"));
    album.setImageName(resultSet.getString("image_name"));
    album.setNum_tracks(resultSet.getInt("num_tracks"));
    album.setPrice(resultSet.getFloat("price"));
    album.setStockCount(resultSet.getInt("stock_count"));
    albums.add(album);
}

The class is now ready to be tested. Inside the Main method, create an instance of the class, call the function findAllAlbums() and put the returned data into a new ArrayList. Then simply print the results to the console.

public static void main(String[] args) {
	MusicDAO mdao = MusicDAO.getInstance();
	ArrayList<MusicRecordings> albums = mdao.findAllAlbums();
	System.out.println(albums);
}

The final code for the DAO should look something like this. Run the program as a Java application by clicking the green circular button with an arrow on it in the Eclipse top menu bar and, if you have done everything correctly, you should see a very long line of key/value pairs in the console.

The reason you are able to simply print the ArrayList using a console print command is because of the toString() method in your model class. Try commenting this method out and run the program again. This time the program only returns a hash representation of the object, which is fairly useless to humans. The toString() method therefore, while it would not be used in the functioning of your application, is a very useful tool for debugging.

Controllers

Now that you have a working database and data accessor object, it is time for the next stage of the application. The Controller sits in the middle of the Model View Controller design pattern. It receives requests from the View, performs any necessary business logic, invokes methods contained in the DAO, and pushes any relevant returned data back to the View.

The controller in this project takes the form of a Servlet. The servlet in this example will contain 3 main sections that we are interested in (although there are many other options), the @WebServlet annotation, which the servlet will listen for, a doGet method and a doPost method. A GET request is one that comes from the URL in the browser. A POST request is usually one that comes from a form or other hidden resource. If you are not familiar with the concept of servlets, there is an excellent resource on StackOverflow: About:Servlets

Begin by right clicking on the project and choosing New > Servlet. Put the servlet in the 'com.congo.controllers' package and give it the name 'Home'. Click Next twice and you will see a number of options for method stubs. You don't need to change anything here, I just wanted to make you aware of their existence. Click Finish and the servlet will be created. At this point you can right click on the servlet and choose Run As > Run on Server. If you want you can select the box to 'Always use this server' and the Tomcat web server will start up and display a short line of text in a browser window (in Eclipse). You can change this behaviour by going to Window > Web Browser and changing the default option.

Once the basic servlet is confirmed to be working, create 'home.jsp' inside 'WebContent/WEB-INF' and put the following code in it to create a basic JSP file

 1 <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
 2 
 3 <!DOCTYPE html>
 4 <html>
 5 <head>
 6 	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 7 	<title>Home | Congo</title>
 8 </head>
 9 <body>
10 	Welcome to Congo!<br/>
11 	<a href="#">See a list of all our albums</a>
12 </body>
13 </html>

In your servlet change the text inside the @WebServlet annotation to "" and change the code inside the doGet method to

request.getRequestDispatcher("/WEB-INF/home.jsp").forward(request, response);

Now right click on the project root in Eclipse's Package Explorer and choose 'Run As > Run on Server'.

Run-on-server.png

With a bit of luck it should start the server and open home.jsp in your browser. If you have not yet set Eclipse to use an alternative browser, you can open up Firefox or Chrome and enter the address

http://localhost:8081/Congo/

assuming that Tomcat is set to use the port 8081 (if not, just change this to whatever yours is set to).

There are two things going on here that we are interested in. First, the server is looking for a servlet with a root attribute in the @WebServlet annotation, which we have defined as "". Then, because this is a GET request it looks in the doGet method of that servlet and processes the code, and we have instructed it to set the response as "home.jsp".

Now we are going to set the target of the link in home.jsp to return a list of all the albums in our database and display them in the browser. Create a new servlet in the 'com.congo.controllers' package and call it "Albums". Inside the doGet method replace any code with the following:

MusicDAO mdao = MusicDAO.getInstance();
ArrayList<MusicRecordings> albums = mdao.findAllAlbums();
request.setAttribute("albums", albums);
request.getRequestDispatcher("/WEB-INF/albums.jsp").forward(request, response);

What this code does is it gets an instance of MusicDAO and assigns it to the variable mdao. Then it creates a new ArrayList of type MusicRecordings with the name albums and populates it with what is returned from the findAllAlbums() method in MusicDAO. It then attaches the ArrayList full of data to the request, gives that attachment a name (the bit in quotes) and forwards it to albums.jsp, which hasn't been created yet and we will do that in the next section.

Just one more thing to do here and that is to modify the link in home.jsp to point to the the Albums servlet. We will be using a relative link so all you need to do is to replace the "#" with "Albums".

Views

Some tutorials with instruct you to use scriptlets <%... ...%> inside your JSP files. This is really bad practice and should be avoided. There should not be any Java code in a JSP, which is only concerned with the presentation of information. For more about this, please read this thread on StackOverflow. In short, using scriptlets leads to messy, hard to read, and unmaintainable code. However, we will still need to use a few of the basic elements of programming, such as loops and conditional statements. For this we will use JSTL (Java Standard Tag library). Grab a copy of jstl-1.2.jar and place it in your WEB-INF/lib directory.

Create albums.jsp inside the WEB-INF directory using the default settings from Eclipse. On the second line add the following:

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>

This tells the server to use the tag library on this page and that any tags will be prefixed with the letter "c".

Inside the body element create a table with the headings 'Artist', 'Album', 'Category', '# of Tracks', and 'Price'. Next add the following code:

<c:forEach items="${albums} var="album">
    <tr><td>${album.artistName}</td><td>${album.title}</td><td>${album.category}</td><td>${album.num_tracks}</td><td>${album.price}</td></tr>
</c:forEach>

What this does is to take the ArrayList called "albums" that was passed through from the Albums servlet, and for each item in the ArrayList assign it to the temporary variable "album". Then it loops through each album and prints the album properties (as defined in the MusicRecordings model class). Run the application on the server again and on the home page, click on the link to show all albums. You should see a (rather bare) table listing the albums. Next we are going to add a little structure to the page and to demonstrate how to have a consistent layout between pages. CSS, and HTML formatting are not covered in this tutorial, apart from the basics needed to have a presentable project.

Create a new folder inside 'WebContent' called "resources", and another inside that called "css". Copy the text on this page (feel free to ignore this and create your own styles, it won't affect the project), paste it into a file inside the css folder called "congo.css". As part of this project I have used an open source JavaScript library called Sorttable, that allows the user to click on the headings and sort the contents of the table. If you want to use this library, download it and place it in "/resources/js/". Note that the styles and JavaScript don't yet take effect because there is no link to the files. We will do that next.

Sometimes when creating web pages it is nice to have a consistent layout that contains things like the header, footer and a menu. Then individual content for the page is placed inside the body of the HTML. Not only does this create a uniform look to all the pages but if something needs changing in these areas, you only need to do it once, minimising the risk of mistakes, which is of course good programming practice. With JSPs this is achieved using JSP 2.0 Tag Files and there is a great write up on StackOverflow on using this technique.

Create a folder inside WEB-INF called "tags" and inside here use the Eclipse wizard to create a new JSP Tag File called "genericPage.tag". I have included below the contents of my generic page and will describe the basic elements of it, so that you can use this information to create your own. Note that I like to include a HTML comment with each closing </div> that states which div it is closing. This approach is useful on large projects in helping you to keep track of which one is which, especially when you employ a templating technique and split a web page over several files, which is what we are doing here.

<%@tag description="Overall Page template" pageEncoding="UTF-8"%>

<%@attribute name="header" fragment="true" %>
<%@attribute name="footer" fragment="true" %>
<head>
    <script type="text/javascript" src="${pageContext.request.contextPath}/resources/js/sorttable.js"></script>
    <link rel="stylesheet" type="text/css" href="${pageContext.request.contextPath}/resources/css/congo.css"/>
</head>
<div id="wrapper">
    <div id="pageheader">
        <h1 id="name">Congo's Music Store</h1>
            <nav>
                <a href="/Congo">Home</a> | 
                <a href="Categories">Categories</a> | 
                <a href="PricePicker">Price Picker</a> | 
                <a href="ArtistFinder">Artist Finder</a> | 
                <a href="Basket">Show Order</a> | 
                <a href="ShowAllMyOrders">Show All My Orders</a> | 
                <a href="Login">Log in/Register</a>
            </nav>
        <jsp:invoke fragment="header"/>
    </div> <!-- ends pageheader -->
    <div id="body">
        <jsp:doBody/>
    </div> <!-- ends body -->
    <div id="pagefooter">
    <jsp:invoke fragment="footer"/>
    </div> <!-- ends pagefooter -->
</div> <!-- ends wrapper -->

This generic page defines three sections of header, body, and footer and specifies how they are ordered on the page. It links to the style sheet and JavaScript file and creates the menu (with links that do not yet work because the servlets or pages they link to haven't been made).

Now the home.jsp and albums.jsp need to be modified to use this generic page. First add a link to the top of the page to allow this feature to be used.

<%@taglib prefix="t" tagdir="/WEB-INF/tags"%>

Then add tags for the header, footer, and body, replacing the body HTML tag that was there before, but leaving the body content.

<t:genericPage>
    <jsp:attribute name="header">
    </jsp:attribute>
    <jsp:attribute name="footer">
    </jsp:attribute>
    <jsp:body>

    </jsp:body>
</t:genericPage>

Please note that the footer goes above the body. I don't know why, it just does - one of life's great mysteries.

One more thing to do and that is to add images for each album. Download this collection of album covers, unzip them and place the 'covers' folder inside '../resources/images/'. It contains medium and small versions of each album cover. In 'albums.jsp' give the table a new header with a class of 'sorttable_nosort' and in the table body create a new td at the start with the following as the image source:

<td class="center"><img src="${pageContext.request.contextPath}/resources/images/covers/sm/${album.recordingId}.jpg"/></td>

I know the database has an imageName attribute but I found it easier to give the images a filename of the recordingId and then just use the recording Id itself to locate the image.

At this point my 'albums.jsp' looks like this and the output looks like this:

Albums.png

Now you understand the basic structure of a Model View Controller Web Application. The rest of this tutorial will explore ways of adding extra features and demonstrating different techniques used when creating a dynamic web application.

Show Track List

This section assumes you have created models for each of the database tables.

Inside MusicDAO create a new method called findAlbumTracks that takes an argument of the recordingId and returns an ArrayList of type MusicTracks. Inside this method, create a new ArrayList that will hold all the data, and call it tracks. The outline of this method is similar to the first one, with a try, catch, and finally block, open the connection, prepare the statement (FIND_ALBUM_TRACKS) and return the execution into a Result Set. Now create the query string at the top of the class with the other one, it should look something like this:

private static String FIND_ALBUM_TRACKS = "SELECT * FROM Music_Tracks WHERE recording_id = ? ORDER BY id";

The question mark is a placeholder for the variable that comes from the browser. Some tutorials will show you a method that concatenates the variable directly to the string. For example:

"SELECT * FROM Music_Tracks WHERE recording_id = " + recordingId;

This is bad practice as explained earlier because the user could manipulate the data coming from the browser and add in a query string that could damage or expose your database. This XKCD comic explains the situation. In it there is a semi-colon ";" followed by a new query string. If unprotected, the database will execute both queries, this is called an SQL Injection Attack. A ProtectedStatement will sanitise any data that is inserted using the ? method. To do this, add a new line below the conn.prepareStatement line like this:

pstmt = conn.prepareStatement(FIND_ALBUM_TRACKS);
pstmt.setInt(1, recordingId);

There are various methods to set the data depending on the data type (setInt, setString, setFloat, etc), the number 1 refers to the first question mark (there is only one here, but there could be several) and the recordingId is the variable passed in through the method arguments.

Create a while loop that loops through the Result Set like before and inside here create a new MusicTracks object, set its values to those from the Result Set and put it in the ArrayList. Remember to close your Result set, statement, and connection. The method should eventually look something like this:

public ArrayList<MusicTracks> findAlbumTracks(int recordingId) {
    ArrayList<MusicTracks> tracks = new ArrayList<MusicTracks>();
        try {
            conn = new DBConnection().openConnection();
            pstmt = conn.prepareStatement(FIND_ALBUM_TRACKS);
            pstmt.setInt(1, recordingId);
            resultSet = pstmt.executeQuery();
            while(resultSet.next()) {
                MusicTracks track = new MusicTracks();
                track.setRecordingId(recordingId);
                track.setTitle(resultSet.getString("title"));
                track.setDuration(resultSet.getInt("duration"));
                tracks.add(track);
            }
            resultSet.close();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnection.closeConnection(conn);
        }
    return tracks;
}

Create a new servlet called TrackLister and put it inside the 'com.congo.controllers' package. The recording ID will come through as a GET request URL parameter to this servlet and there need to be a couple of simple checks on the data that is coming in before it can be passed to the database query. First check that it exists, then that it is numeric. If it passes these two tests it can be converted to an integer and passed to the DAO method that we just created. The data coming back is put into an ArrayList of type MusicTracks and forwarded to 'album.jsp' (not yet created). These steps are very similar to to those in the first servlet. The code is below. This solution either supplies a list of tracks or a suitable error message.

ArrayList<MusicTracks> tracks = new ArrayList<MusicTracks>();
String error = "";
if(request.getParameterMap().containsKey("recordingId")) {
    String parameter = request.getParameter("recordingId");
    if(StringUtils.isStrictlyNumeric(parameter)) {
        int recordingId = Integer.parseInt(parameter);
        MusicDAO mdao = MusicDAO.getInstance();
        tracks = mdao.findAlbumTracks(recordingId);
        if (tracks.isEmpty()) {
                error = "Error: Album does not exist.";
            }
    } else {
        error = "Error: Error: The Recording Id must be an integer.";
    }
} else {
    error = "Error: You must supply a Recording Id.";
}
request.setAttribute("tracks", tracks);
request.setAttribute("error", error);
request.getRequestDispatcher("/WEB-INF/album.jsp").forward(request, response);

Next create "album.jsp", add in the code for the generic page layout and the two lines at the top to enable this and the tag library. In the body, place two divs that will sit side-by side. One will contain the album cover and the other, the track listing in a table. My code for this is below. Note that the reference to the recording id for the album cover can't use the track.recordingId notation because it is not inside the forEach loop, so instead the recordingId is extracted from the first element of the array passed from the servlet.

<jsp:body>
    <p class="center">
        <a href="Albums">See a list of all our albums!</a>
    </p>
    <br />
    <br />
    <div id="album-container">
        <div id="album-cover">
            <img src="${pageContext.request.contextPath}/resources/images/covers/${tracks[0].recordingId}.jpg"/>
        </div> <!-- ends album-cover -->
        <div id="album-tracks">
            <table class="musicList sortable" id="trackLister">
                <tr>
                    <th>Title</th>
                    <th>Duration</th>
                </tr>
                <c:forEach items="${tracks}" var="track">
                    <tr>
                        <td>${track.title}</td>
                        <td class="right">${track.duration}</td>
                    </tr>
                </c:forEach>
            </table>
        </div> <!-- ends album-tracks -->
    </div> <!-- ends album-container -->
</jsp:body>

Finally you need to create a dynamic link in albums.jsp that links to the Album servlet. This link needs to contain a parameter of the recording id. URL parameters are defined by using a question mark at the end of the URL, followed by the parameter name, then an equals sign, then the parameter value. If you need to add more than one parameter (not necessary in this case), simply put an ampersand between them. Here is my code for the link on the album name text in albums.jsp. I also put a link to the same location on the album cover.

<td class="left"><a href="TrackLister?recordingId=${album.recordingId}">${album.title}</a></td>

Now when the album title or cover is clicked the application shows a page with a larger version of the cover along with the album's track listing. This is a good start but there are a number of improvements that can be made. It would be nice if the name of the album and artist were displayed on the page and in the page title, the duration is in seconds but it would be a lot more user friendly if it were displayed in minutes and seconds, also the track listing should show the track number. This last change has a slight problem in that the track number is not stored in the database. Normally I would recommend that you fix your database setup but in this case we have to work with what we have got. Fortunately there is an id column in the Music_Tracks table, so assuming that the tracks were entered into the database in the correct order, it should be simple to output them in order of id and then generate a track number.

Since the artist and album name is not stored with the track information (apart from the recording id) we need to make another request to the database in the TrackLister servlet that returns only a single album, which we don't have a method for in MusicDAO yet so start by creating this method in MusicDAO. It takes a recordingId as an argument and returns a single object of type MusicRecordings. Otherwise it is very similar to the findAllAlbums method, only without the ArrayList. The SQL query is below.

"SELECT * FROM Music_Recordings WHERE recording_id = ?"

And the method in MusicDAO should look something like this

public MusicRecordings findSingleAlbum(int recordingId) {
    MusicRecordings album = new MusicRecordings();
    try {
        conn = new DBConnection().openConnection();
        pstmt = conn.prepareStatement(FIND_SINGLE_ALBUM);
        pstmt.setInt(1, recordingId);
        resultSet = pstmt.executeQuery();
        while(resultSet.next()) {
            album.setRecordingId(resultSet.getInt("recording_id"));
            album.setArtistName(resultSet.getString("artist_name"));
            album.setTitle(resultSet.getString("title"));
            album.setCategory(resultSet.getString("category"));
            album.setImageName(resultSet.getString("image_name"));
            album.setNum_tracks(resultSet.getInt("num_tracks"));
            album.setPrice(resultSet.getFloat("price"));
            album.setStockCount(resultSet.getInt("stock_count"));
        }
        resultSet.close();
        pstmt.close();
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        DBConnection.closeConnection(conn);
    }		
    return album;
}

Then create a new instance of MusicRecordings in the TrackLister servlet, call the new method and set the result as another request attribute.

MusicRecordings album = new MusicRecordings();
...
album = mdao.findSingleAlbum(recordingId);
...
request.setAttribute("album", album);

Finally, in album.jsp print out the album title and artist name. I also chose to print out the error in the same location on the page, so the JSP either prints out the album and artist name or it prints the error.

<h2>${album.title} by ${album.artistName}</h2>
<h2 class="error">${error}</h2>

To convert the duration from seconds to minutes and seconds, this is a method that is directly related to the data in the Music_Tracks table so the best location for this function is inside the MusicTracks class. Create a new variable of type String and called "strDuration", its associated getters and setters, and update the 'toString' method. Then create a new function like the one below.

public static String formatDuration(int duration) {
    int minutes = duration / 60;
    int seconds = duration % 60;
    String formattedDuration = minutes + "m" + String.format("%02d", seconds) + "s";
    return formattedDuration;
}

This method takes the duration as an argument, does an integer division (which returns only whole numbers) to find the minutes, and a modulo division (which returns the amount left over from an integer division) to find the seconds. It then returns the concatenated result with the seconds formatted to 2 characters.

Next create a new constructor in the MusicTracks class that contains only the three variables that need to be printed (recordingId, title, strDuration). Now in the findSingleAlbum method in MusicDAO, call the formattedDuration function and change the variables that make up the MusicTracks object to the three listed. Finally update album.jsp to reference strDuration instead of duration.

If this is all starting to get a bit confusing, I will link the up to date versions of these files at the end of this section.

To create the track number, create a new variable in MusicTracks "trackNumber", add it to the constructor and toString methods, create getters and setters, and then in the findSingleAlbum method, instantiate a variable of the same name starting at 1. Call setTrackNumber(trackNumber), and then after the track has been added to the ArrayList, do trackNumber++. Then add a new column in album.jsp to print out the track number.

DAO version 2 | TrackLister.java | MusicTracks class | album.jsp

And this is what the album details page looks like

Album-jsp.png

Filter By Category

In this chapter we are going to create a dropdown list that when changed will update the page to show only albums from that category. The dropdown list will also be populated from the database, so that if a new category is added the page will automatically update itself and you don't have to remember to recode the page. Finally a link will be added to the albums list that will achieve the same result.

To display the list of categories, first there need to be a function in MusicDAO that retrieves them. This function works in much the same way as the others, only it returns an ArrayList of type MusicCategories and takes no arguments. You will need to import MusicCategories from 'com.congo.model'. The SQL query is

private static String FIND_ALL_CATEGORIES = "SELECT * FROM Music_Categories";

And this is the function in MusicDAO

public ArrayList<MusicCategories> findAllCategories() {
    ArrayList<MusicCategories> categories = new ArrayList<MusicCategories>();
    try {
        conn = new DBConnection().openConnection();
        pstmt = conn.prepareStatement(FIND_ALL_CATEGORIES);
        resultSet = pstmt.executeQuery();
        while(resultSet.next()) {
            MusicCategories category = new MusicCategories();
            category.setId(resultSet.getInt("id"));
            category.setName(resultSet.getString("name"));
            categories.add(category);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        DBConnection.closeConnection(conn);
    }
    return categories;
}

Create a new servlet called "ListCategories" and inside the doGet method replacing the provided code, create a new ArrayList of type MusicCategories, call the method in MusicDAO and add the result as the request attribute and then forward the request dispatcher to categories.jsp.

MusicDAO mdao = MusicDAO.getInstance();
ArrayList<MusicCategories> categories = mdao.findAllCategories();
request.setAttribute("categories", categories);
request.getRequestDispatcher("/WEB-INF/categories.jsp").forward(request, response);

Create "categories.jsp" and after adding the code for genericPage.tag and JSTL, inside the body create a form with action of "Categories" and a method of "GET". Inside the form create a Select dropdown box with the name of "category" and put an onchange to submit the form when it is changed, rather than having a button to submit it. Use a forEach loop to put category.name into the Option. The code for the form is below.

<div class="indent">
    <form action="Categories" method="GET">
        <select name="category" onchange="this.form.submit()">
            <c:forEach items="categories" var="category">
                <option>${category.name}</option>
            </c:forEach>
        </select>
    </form>
</div> <!-- ends indent -->

Before you go any further, start or restart the server and when the website has loaded in the browser, click on Categories in the menu and check that the links work and that the dropdown box has loaded a list of the categories, there should be 6.

Then below this dropdown, create a table that is the same as the one in albums.jsp.

Back to the Categories servlet, and the category will be coming through a URL parameter in the same way that recordingId did for the TrackLister. This time we want to set a default category in case this is the first time loading the page and a parameter has not yet been set. Under the code to find all the categories grab the category from the URL if it there, otherwise set category to "classical" (the first one in the list).

ArrayList<MusicRecordings> albums = new ArrayList<MusicRecordings>();
String category = "Classical";
if(request.getParameterMap().containsKey("category")) {
    category = request.getParameter("category");
}
albums = mdao.findAlbumsByCategory(category);
request.setAttribute("albums", albums);
request.setAttribute("category", category);

In MusicDAO create a new method to find albums by category, it's very similar to find albums by recording id. The SQL query is below:

private static String FIND_ALBUMS_BY_CATEGORY = "SELECT * FROM Music_Recordings WHERE category = ?";

After creating this method you may notice that two of the methods in MusicDAO have very similar code - to create an ArrayList of objects of type MusicRecordings. If you like you can refactor this into its own method (pass in the Result Set and return the ArrayList) and then call it from the other two. This will make your program shorter and easier to understand, as well as having a common point from where to change things should you need to in the future.

Another area that is starting to see repeated code is the table that displays the list of albums. Cut out the whole table and paste it into a new file called "album-list.jsp". Import JSTL core at the top of the page. In all current and future pages that will show the table, just include this single line at the point in the HTML code where you want the table to be displayed.

<jsp:include page="album-list.jsp"/>

Create a dynamic link in the table that lists the albums in the category field, that will go to the Categories servlet and return a list of albums with that category.

<td class="left"><a href="Categories?category=${album.category}">${album.category}</a></td>

Finally, it would be nice if the dropdown selector also displayed the category that is being shown. To do this you need to use JSTL tags that emulate the if/else behaviour. These tags are 'choose', 'when', and 'otherwise'. You need to get the parameter from the URL and compare it with the one in the Option. If they are the same, this option is 'selected'. Here is the final dropdown selector:

<div class="indent">
    <form action="Categories" method="GET">
        <select name="category" onchange="this.form.submit()">
            <c:forEach items="${categories}" var="category">
                <c:choose>
                    <c:when test="${category.name == param.category}">
                        <option selected>${category.name}</option>
                    </c:when>
                    <c:otherwise>
                        <option>${category.name}</option>
                    </c:otherwise>
                </c:choose>
            </c:forEach>
        </select>
    </form>
</div> <!-- ends indent -->
Categories-jsp.png

categories.jsp | Categories.java | MusicDAO

Search By Price

This chapter is very similar to the previous one, so I'm not going to spend too much time on it. The price-picker.jsp page should have a dropdown list with values and the application will find albums whose prices fall between the ranges selected. Although the values in the dropdown can be populated from the database in the same way as for the categories, I am going to recommend that they be hard coded. In this way the application can search for values between, for example, £9.99 and £11.99, whereas if it were populated automatically you could end up with many irregular prices in the list.

The SQL query to get albums by price is:

private static String FIND_ALBUMS_BY_PRICE = "SELECT * FROM Music_Recordings WHERE price >= ? AND price <= ?";

The method in MusicDAO is below. Note that there are two variables to set in the Prepared Statement, and that it is using a new custom method to create an array of albums.

public ArrayList<MusicRecordings> findAlbumsByPrice(float lowerPrice, float higherPrice) {
    albums = new ArrayList<MusicRecordings>();
    try {
        conn = new DBConnection().openConnection();
        pstmt = conn.prepareStatement(FIND_ALBUMS_BY_PRICE);
        pstmt.setFloat(1, lowerPrice);
        pstmt.setFloat(2, higherPrice);
        resultSet = pstmt.executeQuery();
        albums = createAlbumArray(resultSet);
        resultSet.close();
        pstmt.close();
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        DBConnection.closeConnection(conn);
    }
    return albums;
}

This next method uses JSTL functions. Put this line at the top of price-picker.jsp.

<%@ taglib uri = "http://java.sun.com/jsp/jstl/functions" prefix = "fn" %>

In the select box I have represented two values separated by a comma, but commas cannot be used in URLs so they are represented with URL escape characters, in this case '%2C'. The program replaces the URL escape characters with a comma and then uses a different method (to that in 'categories.jsp') to compare the parameter and option in order to select the current option.

<div class="indent">
    <c:set var="string1" value="${param.price}"/>
    <c:set var="string2" value="${fn:replace(string1, '%2C', ',')}"/>
    <form action="PricePicker" method="GET">
        <select name="price" onchange="this.form.submit()">
            <option value="0,10" ${string2 == '0,10' ? "selected" : ""}>Under £10</option>
            <option value="10,12" ${string2 == '10,12' ? "selected" : ""}>£10 &lt; £12
            <option value="12,14" ${string2 == '12,14' ? "selected" : ""}>£12 &lt; £14</option>
            <option value="14,100" ${string2 == '14,100' ? "selected" : ""}>Over £14</option>
        </select>
    </form>
</div> <!-- ends indent -->

And the code in PricePicker.java, the two values in the parameter are separated using String.split(). Like with the categories, 'lower' and 'higher' are initialised with default values.

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    ArrayList<MusicRecordings> albums = new ArrayList<MusicRecordings>();
    float lower = 0; float higher = 10;

    if(request.getParameterMap().containsKey("price")) {
        String price = request.getParameter("price");
        String[] parts = price.split(",");
        lower = Float.parseFloat(parts[0]);
        higher = Float.parseFloat(parts[1]);
    }
		
    MusicDAO mdao = MusicDAO.getInstance();
    albums = mdao.findAlbumsByPrice(lower, higher);

    request.setAttribute("albums", albums);
    request.getRequestDispatcher("/WEB-INF/price-picker.jsp").forward(request, response);
}

price-picker.jsp | PricePicker.java

Search By Artist

This chapter is fairly straightforward, and we have already learnt most of what is needed. Create a new method in MusicDAO that takes the artist name as an argument and returns an ArrayList of type MusicRecordings. This method is almost identical to the others created previously. The SQL query is:

private static String FIND_ALBUMS_BY_ARTIST = "SELECT * FROM Music_Recordings WHERE artist_name LIKE ?";

Create a new servlet called ArtistFinder. Inside the doGet method check for the existence of the parameter 'artist' and call the findAlbumsByArtist method from MusicDAO using 'artist' as an argument. Forward the resulting ArrayList to artist-finder.jsp If there is no artist name provided, display a list of all the albums.

Artist-finder.jsp has the usual table of albums that you can copy from one of the other files. Inside the selector div there need to be a form with an action of "ArtistFinder", method "GET", a text box that has a name of 'artist' (this is where the parameter name comes from) and a submit button.

<div class="indent">
    <form action="ArtistFinder" method="GET">
        <input type="text" name="artist" placeholder="Search for artists"/>
        <input type="submit" value="Search"/>
    </form>
</div> <!-- ends indent -->

This is enough to provide the basic functionality but there are a few improvements to be made. We can make the search return any instances of matching characters rather than requiring an exact match on the name. In MusicDAO.findAlbumsByArtist, where you set the string in the prepared statement, change the parameter 'artist' to:

"%" + artist + "%"

In SQL the % sign is a wildcard that means match any number of any character. Now try the search again, only this time search for "at". This time the application returns results for 'Men At Work' and 'Grateful Dead'.

In the table that lists the albums create a link on the artist name that links to ArtistFinder with the artist name as a parameter:

<td class="left"><a href="ArtistFinder?artist=${album.artistName}">${album.artistName}</a></td>

Now the artist name is a clickable link that takes you to a list of their albums.

In ArtistFinder.java, get the parameter that was passed and send it back as an attribute, then you can display it in the title of the page (or a default one if none was provided).

artist-finder.jsp | ArtistFinder.java | MusicDAO

Persistence and Session

This chapter will cover how to emulate a shopping basket. It will not have payment options but you will be able to add, remove, and change the quantity of items in your basket, and it will persist across pages.

Create a new column in the table in album-list.jsp, it has a blank header (unsorted) and the table data has a form with a button with the word "Add". The action of the form is a servlet with the name "AddToOrder" with a parameter of the recording id. The method of the form is POST.

<td class="center">
    <form action="AddToOrder?${album.recordingId}" method="POST">
        <button class="btn btn-add">Add</button>
    </form>
</td>

Create the "AddToOrder" servlet in 'com.congo.controllers'. Inside the doPost method check for the existence of the recordingId parameter and the 'from' parameter. If they do not exist redirect to the Home page (in case we somehow arrived at this page by mistake), possibly with an error message. If you do not have a way to print the error message on the home page, add it now (just print ${error} inside a div with a class of "error"). I have also put this redirect and error message inside the doGet method, again in case we arrived here by mistake.

The list of albums in the order will be an ArrayList of recordingId. If the recordingId parameter does exist, get a copy of the session, get the ArrayList out of the session and add the new recordingId to it, before saving the order list back to the session. If the session variable does not exist, create an ArrayList, put the recordingId into the list and save the list to the session. Session attributes work in much the same way as request attributes do, there is a name and a thing, which could be an object or a String or whatever you want it to be. Finally send the user back to the page they just came from.

if(request.getParameterMap().containsKey("recordingId") && (request.getParameterMap().containsKey("from"))) {
    int recordingId = Integer.parseInt(request.getParameter("recordingId"));
    String from = getPreviousPageByRequest(request).orElse("/");
    HttpSession session = request.getSession();
    if (session.getAttribute("order") == null) {
        ArrayList<Integer> order = new ArrayList<Integer>();
        order.add(recordingId);
        session.setAttribute("order", order);
        System.out.println(session.getAttribute("order"));
        response.sendRedirect(from);
    } else {
        ArrayList<Integer> order = (ArrayList<Integer>) session.getAttribute("order");
        order.add(recordingId);
        session.setAttribute("order", order);
        System.out.println(session.getAttribute("order"));
        response.sendRedirect(from);
    }
} else {
    request.setAttribute("error", "Sorry, something went wrong");
    request.getRequestDispatcher("/WEB-INF/home.jsp").forward(request, response);
}

This is the function used to retrieve the page that the user came from. Put it underneath the doPost method.

protected Optional<String> getPreviousPageByRequest(HttpServletRequest request)
{
    return Optional.ofNullable(request.getHeader("Referer")).map(requestUrl -> "redirect:" + requestUrl);
}

When retrieving the order from the session, we have to cast it to a type of ArrayList (as you can see above) and Eclipse will show a minor error (yellow underline) here because theoretically it is possible that the object or thing coming in from the session is something different to what is expected. Since we placed that object there ourselves we can be fairly certain that the object being retrieved is of the correct type and will not cause the application to crash, so can safely ignore this error. If you wanted to bomb-proof your application you could perform a series of checks to ensure that the data is of the correct type, but I feel that it is unnecessary in this instance.

I have added a simple console print statement to confirm that the data is actually in the session, because otherwise we have no way to check. Next we will look at retrieving this data and displaying all the albums in the customer's order.

Create a new servlet in 'com.congo.controllers' called "Basket" that forwards to basket.jsp. Inside the doGet method, get a copy of the session and check if the order is in there, if it is not set a request attribute error message that the basket is empty. If the order exists, get it and define it as an ArrayList of type Integer. Get an instance of MusicDAO, and create an ArrayList of type MusicRecordings, which is where the information about each album in the order will be stored. Then for each recordingId in the order, create a new MusicRecordings object called album and fill it with data using the findSingleAlbum method in the DAO. Add the album to the albums ArrayList and once the loop has completed add the albums ArrayList as a request attribute.

HttpSession session = request.getSession();
if (session.getAttribute("order") == null) {
    request.setAttribute("error", "Your basket is empty");
} else {
    ArrayList<Integer> order = (ArrayList<Integer>) session.getAttribute("order");
    MusicDAO mdao = MusicDAO.getInstance();
    ArrayList<MusicRecordings> albums = new ArrayList<MusicRecordings>();
    for(int recordingId : order) {
        MusicRecordings album = mdao.findSingleAlbum(recordingId);
        albums.add(album);
    }
    request.setAttribute("albums", albums);
}		
request.getRequestDispatcher("/WEB-INF/basket.jsp").forward(request, response);

Create "basket.jsp" inside 'WEB-INF' and add the code to enable the generic layout and JSTL. We can't reuse the table in album-list.jsp here because there will be several differences, so create a new table with headers: Cover; Artist; Album; Category; # of Tracks; Price; Quantity; Total Price; and another unsortable blank header. Copy the code from 'album-list.jsp' that display the lines from the cover up to the price. Now run the application, add some albums to the order and try to view the basket, to check that it is working so far. Try adding the same album more than once, what happens when you view the basket again? It should display the same title on multiple lines. This will be corrected later.

Add three more columns inside the forEach, one to contain a form to update the quantity, with a number input field and a submit button, one that displays the total price, and the last contains a form to delete an item from the order, with just a submit button. The code for those three columns is detailed below.

<td class="center">
    <form action="UpdateOrder/${album.recordingId}" method="POST">
        <input name="quantity" type="number" value="${album.quantity}" min="1" max="99" style="width: 3.5em;">
        <button class="btn btn-update" type="submit">Update</button>
    </form>
</td>
<td class="right">£${album.totalPrice}</td>
<td class="center">
    <form action="DeleteFromOrder/${album.recordingId}" method="POST">
        <button class="btn btn-delete" type="submit">Delete</button>
    </form>
</td>

I'm also going to put another row at the bottom of the table after the forEach that will display the grand total.

<tr><td id="grandTotal" colspan="7">Grand Total:</td><td class="right">£${grandTotal}</td></tr>

A number of things to do here. First add two variables to the MusicRecordings model class (quantity and totalPrice). Create the associated getters and setters, update the toString method, and create a new constructor with all fields. Then in Basket.java, to remove any repeated recordingId convert the ArrayList order to a HashSet. We now have the order list in two forms, a HashSet and an ArrayList and the For Loop will iterate over the HashSet not the ArrayList. We can get the quantity of each album by passing the ArrayList to a library called Collections.frequency(), and calculate the totalPrice by multiplying the price with the quantity. If this is starting to look confusing, have a look at the code below.

ArrayList<Integer> orderArray = (ArrayList<Integer>) session.getAttribute("order");
MusicDAO mdao = MusicDAO.getInstance();
ArrayList<MusicRecordings> albums = new ArrayList<MusicRecordings>();
Set<Integer> orderSet = new HashSet<Integer>(orderArray);
for (int recordingId : orderSet) {
    int quantity = Collections.frequency(orderArray, recordingId);
    MusicRecordings album = mdao.findSingleAlbum(recordingId);
    album.setQuantity(quantity);
    float totalPrice = album.getPrice() * quantity;
    album.setTotalPrice(totalPrice);
    albums.add(album);

Once the For Loop has completed you can calculate the grand total by passing the ArrayList of albums to a small function that loops through them and adds up the totalPrice. Then add grandTotal as a separate attribute to the request.

protected float calculateGrandTotal(ArrayList<MusicRecordings> albumsInOrder) {
    float grandTotal = 0;
    for(MusicRecordings albums : albumsInOrder) {
        grandTotal += albums.getTotalPrice();
    }
    return grandTotal;
}

Run the application again and it should now show the correct quantity and total cost of each album, as well as the grand total, although the Update and Delete buttons don't yet work.

Create separate servlets for UpdateOrder and DeleteFromOrder. These both work in a similar way and again the Collections library is going to help. For Update there is a parameter called 'quantity' and one called 'recordingId'. the form action arrives at the doPost method and the servlet should redirect with an error if the user somehow arrives here using GET or without providing these parameters. Invoke the ArrayList method removeAll on Collections.singleton (which restricts it to only removing elements with a particular value), then run a For Loop using the quantity as a limit to add it back in again. Once you have done this, redirect to the Basket servlet to recreate the basket and direct the user back to basket.jsp.

ArrayList<MusicRecordings> albumsInOrder = new ArrayList<MusicRecordings>();
HttpSession session = request.getSession();
if (session.getAttribute("order") != null && request.getParameter("recordingId") != null && request.getParameter("quantity") != null) {
    String strRecordingId = request.getParameter("recordingId");
    String strQuantity = request.getParameter("quantity");
    if(StringUtils.isStrictlyNumeric(strRecordingId) && StringUtils.isStrictlyNumeric(strQuantity)) {
        int recordingId = Integer.parseInt(strRecordingId);
        int quantity = Integer.parseInt(strQuantity);
				
        ArrayList<Integer> orderArray = (ArrayList<Integer>) session.getAttribute("order");
        orderArray.removeAll(Collections.singleton(recordingId));
				
        for(int i = 1; i <= quantity; i++) {
            orderArray.add(recordingId);
        }
        response.sendRedirect("Basket");

DeleteFromOrder works in much the same way except that it only uses the parameter 'recordingId' and there is no need to use the For Loop to add anything back to the ArrayList.

Basket.png

album-list.jsp | AddToOrder.java | Basket.java | basket.jsp | UpdateOrder.java | DeleteFromOrder.java

Login and Register

This section will cover login and logout of users, and also registration of new accounts. It will also discuss some best practices when it comes to handling and storing passwords.

To begin with you will need to create some new tables in the database. Open a mysql prompt in the terminal, select the congo database (USE congo;) and you can use the script on this page to create the tables. Obviously this is just a tutorial and if you are doing this for real you should discuss with your team about which types of data you want to store and whether it complies with relevant legislation for your area such as the European GDPR

Next you need to create login.jsp. If you like you can use my code for this page, it implements both login and register on the same page and uses a tabbed interface to switch between them. If you want to create your own, what's important is that there are two forms, "login" and "register". They don't even have to be on the same page, just as long as one forwards to "Login.java" and the other to "Register.java", and that both methods are POST. The doGet method in both servlets just redirects to login.jsp (or register.jsp). The login form has three fields: email, password, and a submit button. The register form has all the fields that are in your Customer database table (except for custid, which is inserted automatically), and a submit button. To make things easier, be sure to use the variable names in your Customer model class.

Create a model for your customer table in 'com.congo.model', you need to create the variables that are in the table, a constructor from the superclass, a constructor using all fields, a toString method, and getters and setters for all variables, all done via the Source menu in Eclipse.

Since the MusicDAO class is starting to get a bit crowded now, create a new class in 'com.congo.dao' and call it "CustomerDAO". When creating the class remember to tick the 'create a main method' box for testing. Inside the CustomerDAO class add the code that turns it into a singleton, changing the relevant bits so that it applies to this class.

private static CustomerDAO instance = null;
	
private CustomerDAO() {}
	
public static synchronized CustomerDAO getInstance() {
    if(instance == null) {
        instance = new CustomerDAO();
    }
    return instance;
}

Create a method inside CustomerDAO to retrieve a single customer by email. This method is very similar to findSingleAlbum(), the only differences being the names of the variables and the names of the parameters coming back from the database. You can see my version of it here. Test the DAO, make sure you can retrieve the details of a customer using the Main method.

Create the servlet Register.java in 'com.congo.controllers'. Retrieve all the parameters from the form and use them to create a Customer object. For the 'admin' attribute I hard-coded a value of 0 because this is something that only someone with authority should be able to set. Create a boolean method in the CustomerDAO that will check if the customer email from the form already exists in the database. I have provided an example of such a method below. It gets an instance of the CustomerDAO, invokes the method to find a customer by their email and then returns true if it finds one, otherwise false.

public boolean checkEmail(String email) {
    CustomerDAO cdao = CustomerDAO.getInstance();
    Customer customer = cdao.findOneCustomer(email);
    if(customer.getfName() != null) {
        return true;
    } else {
        return false;
    }
}

Call this method in Register.java, if it returns true redirect back to the register page with an error message, otherwise continue to register that person. Next task is to encrypt the password. Many examples you can see online will demonstrate password encryption using MD5 or one of the SHA variants. This is not secure, indeed MD5 and SHA1/2 are now considered completely broken for password hashing because of the speed with which it is possible to brute force these hashing algorithms. With modern hardware it is not even necessary to use rainbow tables with them. The current recommended hashing algorithms are Bcrypt, Scrypt, or Argon2, although that may have changed when you read this so you should research the best methods for yourself. This tutorial will demonstrate encryption using the Bcrypt algorithm, which you will need to download from here. From this archive extract BCrypt.java (located in src/org/mindrot/jbcrypt/) and place it in a new package inside src called "org.mindrot.jbcrypt". Now encrypt the user's password using

String hashedPassword = BCrypt.hashpw(customer.getPassword(), BCrypt.gensalt());	
customer.setPassword(hashedPassword);

Bcrypt generates the salt automatically and stores it with the hash (although you can change the way it is generated if you want) so there is no need to do this step separately.

Next step is to create a method in CustomerDAO that will insert the new customer details into the database. This is the INSERT statement:

private static String INSERT_CUSTOMER_INTO_DATABASE = "INSERT INTO Customers (fname, lname, address1, address2, city, postcode, phone, email, password, admin) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

No need to use the custid because it is generated automatically by the database. You must ensure that the field names are as they appear in MySQL (use the DESCRIBE command to confirm this) and that there are the correct number of '?'. It is helpful if the order is the same as the order in your table. Below is the method for inserting the customer. It takes a Customer object as an argument and uses its get methods to retrieve the values. The pstmt musst be of the same data type as in the MySQL table and in the same order that you listed them in the statement (above). This time pstmt uses executeUpdate() instead of executeQuery() and it will return a value of 1 or 0 depending on whether the insert was successful or not, which you can return to the controller and handle the outcome from there.

public int insertCustomerIntoDatabase(Customer customer) {
    int result = 0;
    try {
        conn = new DBConnection().openConnection();
        pstmt = conn.prepareStatement(INSERT_CUSTOMER_INTO_DATABASE);
        pstmt.setString(1, customer.getfName());
        pstmt.setString(2, customer.getlName());
        pstmt.setString(3, customer.getAddress1());
        pstmt.setString(4, customer.getAddress2());
        pstmt.setString(5, customer.getCity());
        pstmt.setString(6, customer.getPostcode());
        pstmt.setString(7, customer.getPhone());
        pstmt.setString(8, customer.getEmail());
        pstmt.setString(9, customer.getPassword());
        pstmt.setInt(10, customer.getAdmin());			
        result = pstmt.executeUpdate();			
        pstmt.close();
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        DBConnection.closeConnection(conn);
    }
    return result;
}

Back to 'Register.java' and if the customer is inserted correctly (1) return a success message to 'login.jsp', else return an error to the same page.

if (cdao.insertCustomerIntoDatabase(customer) == 1) {
    request.setAttribute("msg", "Success! You are now registered in our database. Please log in.");
    request.getRequestDispatcher("/WEB-INF/login.jsp").forward(request, response);
} else {
    request.setAttribute("error", "Sorry, something went wrong.");
    request.getRequestDispatcher("/WEB-INF/login.jsp").forward(request, response);
}

Try it out. Start the application, go to the register form , enter your details and see if you get the success or error message. If it is successful you can check that the data really did go in to the table by connecting to a mysql prompt in the terminal and using the command:

SELECT * FROM Customers WHERE email = "[email protected]"

Now we need to create the servlet that will log someone in. Using 'Login.java' get the parameters from the form via POST and verify that there has actually been some data sent. I didn't do these checks with Register.java, partly because I was being lazy (there are so many parameters!) but also there are already a couple of checks in place. The HTML form does not allow the user to submit if there are empty fields, and if someone managed to subvert that check the database table does not allow null entries, it will return 0 and there is a system to handle this. It is still possible for a malicious user to fill your database with garbage though, so some more checks probably are necessary, but that is beyond the scope of this tutorial. Once you have the email and password from the form, use the email to get a customer from the database. Then use Bcrypt to check the supplied password with the one in the Customer object. If they match, you can save the customer to the session and log them in. I created an extra variable in the Customer model, a boolean called "loggedIn" but you could just as easily use one of the other variables. If either the email does not exist in the database or the password does not match, return an error message, and it is advisable to use the same error message for both cases.

String email = "";
email = request.getParameter("email");
String password = "";
password = request.getParameter("password");
boolean matched = false;
		
if(email != "" && password != "") {
    CustomerDAO cdao = CustomerDAO.getInstance();
    Customer customer = cdao.findOneCustomer(email);
    if (customer.getPassword != null) {
        matched = BCrypt.checkpw(password, customer.getPassword());
    }
    if (matched) {
        customer.setLoggedIn(true);
        request.setAttribute("customer", customer);
        HttpSession session = request.getSession();
        session.setAttribute("customer", customer);
        request.getRequestDispatcher("/WEB-INF/loggedin.jsp").forward(request, response);       		
    } else {
        request.setAttribute("error", "Invalid Details.");
        request.getRequestDispatcher("/WEB-INF/login.jsp").forward(request, response);
    }

} else {
    request.setAttribute("error", "Please enter your details.");
    request.getRequestDispatcher("/WEB-INF/login.jsp").forward(request, response);
}

Create the page "loggedin.jsp" with all the associated headers etc. I have deliberately left this page sparse because I'm not particularly interested in content here, I'm merely using it to confirm that the user is logged in.

<div class="center"><h2>Welcome ${customer.fName} ${customer.lName}</h2></div>

What would be nice is if the customer has a message at the top of every page, welcoming them by name and providing a means to logout. Also it would be useful to change the 'Login/Register' link to a Logout one. Put this at the top of genericPage.tag just inside the wrapper div. It is JSTL's version of the if/else conditional statement. It also includes the message that you need to log in if you want to add things to the basket, which hasn't been implemented yet and will be covered in the next section.

<c:choose>
    <c:when test="${customer.loggedIn == true}">
        Welcome ${customer.fName} ${customer.lName}. <a href="Logout">Log out</a>
    </c:when>
    <c:otherwise>
        Please log in to add items to the basket.
    </c:otherwise>
</c:choose>

And replace the Login/Register link with the following code

<c:choose>
    <c:when test="${(customer.loggedIn != null) && (customer.loggedIn == true)}">
        <a href="Logout">Log out</a>
    </c:when>
    <c:otherwise>
        <a href="Login">Log in/Register</a>
    </c:otherwise>
</c:choose>

One final thing to do and that is to implement the logout feature. Create a servlet "Logout.java". This is very simple, it gets hold of the Session, invalidates it and then returns the user to the home page.

HttpSession session = request.getSession();
session.invalidate();
request.getRequestDispatcher("/WEB-INF/home.jsp").forward(request, response);

If you want to use the usernames and passwords that are in the database script, which might be useful for the next section because they have order information associated with them, the emails and passwords are as follows:

Email Password Admin
[email protected] thesinger Yes
[email protected] thebassplayer No
[email protected] theguitarist No
[email protected] thedrummer No

Customer.java | CustomerDAO | genericPage.tag | loggedin.jsp | Login.java | login.jsp | Logout.java | Register.java

Save Customer Orders To The Database

Now that we can identify a particular user it would be useful to be able to save their orders to the database. The order information should be held in two tables, Order and Order Details, for a number of reasons. The order information has a many-to-many relationship because the albums will appear on my orders and the customers will make many orders (potentially with the same albums). Also the order will have information that only needs to be recorded once, such as the delivery address or payment information. If you recorded each line of the order together with the full information this increases the possibility of inconsistencies in the data. If you want to learn more about database design I can recommend Database Systems: Design, Implementation, and Management or Database Systems: The Complete Book. The tables used in this tutorial are very simple and merely intended to demonstrate a proof of concept.

The first thing to do is to make sure that the user cannot create an order unless they are logged in. They will still be able to browse the catalogue but will need to log in first if they want to add anything to the shopping basket. The 'Add' button will be disabled unless the user is logged in. Also, if you have looked at the SQL script that contains all the album information you may have noticed that there is a field called stockCount, and that sometimes the value for this field is zero. So at the same time let's make it so that the user can only add an item if they are logged in and the item is in stock. Open 'album-list.jsp' and in the last 'td' where the form is, add choose/when/otherwise tags so that it looks like this.

<td class="center">
    <form action="AddToOrder?recordingId=${album.recordingId}" method="POST">
        <c:choose>
            <c:when test="${(customer.loggedIn != null) && (customer.loggedIn == true) && (album.stockCount > '0')}">
                <button>Add</button>
            </c:when>
            <c:otherwise>
                <button disabled>Add</button>
            </c:otherwise>
        </c:choose>
    </form>
</td>

I'm also going to add similar code to the links in the menu to hide the 'Show Order' and 'Show All My Orders' links unless the user is logged in.

<nav>
    <a href="/Congo">Home</a> | 
    <a href="Categories">Categories</a> | 
    <a href="PricePicker">Price Picker</a> | 
    <a href="ArtistFinder">Artist Finder</a> | 
    <c:if test="${(customer.loggedIn != null) && (customer.loggedIn == true)}">
        <a href="Basket">Show Order</a> | 
        <a href="ShowAllMyOrders">Show All My Orders</a> | 
    </c:if>
    <a href="Login">Log in/Register</a>
</nav>

Now, if you test the application you will see that the 'Add' buttons/links are disabled/hidden, and when you log in and navigate to 'Categories' you can see that most of the albums have the button enabled but that it is still disabled for some. Now open 'basket.jsp' and add a button for the checkout above the table. I have made this a regular link and styled it using CSS.

<a id="checkout" href="Checkout">Checkout</a>

The link goes to a servlet called Checkout, which itself returns a page checkout.jsp that has the same information as basket.jsp except without the options to edit anything. It also contains some customer details. If you wanted to you could include options for changing the delivery address etc but for now let's just keep things simple. The link that was going to 'Checkout.java' in 'basket.jsp' now links to "SubmitOrder.java".

<jsp:body>
    <br/>
    <br/>
    <h2>Confirm Your Order</h2>
    <a id="checkout" href="SubmitOrder">Confirm Order</a>
    <table class="musicList sortable">
        <tr>
            <th class="sorttable_nosort"></th><th>Artist</th><th>Album</th><th>Category</th><th># of Tracks</th><th>Price</th><th>Quantity</th><th>Total</th>
        </tr>
        <c:forEach items="${albums}" var="album">
            <tr>
                <td class="center"><a href="TrackLister?recordingId=${album.recordingId}">
                    <img src="${pageContext.request.contextPath}/resources/images/covers/sm/${album.recordingId}.jpg"/></a></td>
                <td class="left"><a href="ArtistFinder?artist=${album.artistName}">${album.artistName}</a></td>
                <td class="left"><a href="TrackLister?recordingId=${album.recordingId}">${album.title}</a></td>
                <td class="left"><a href="Categories?category=${album.category}">${album.category}</a></td>
                <td class="center">${album.num_tracks}</td>
                <td class="right">£${album.price}</td>
                <td class="center">${album.quantity}</td>
                <td class="right">£${album.totalPrice}</td>
            </tr>
        </c:forEach>
        <tfoot><td id="grandTotal" colspan="7">Grand Total:</td><td class="right">£${grandTotal}</td></tfoot>
    </table>
    <br/>
    <br/>
    <div id="customer-address">
        <h4>Delivery Address:</h4>
        ${customer.fName} ${customer.lName}<br/>
        ${customer.address1}<br/>
        ${customer.address2}<br/>
        ${customer.city}<br/>
        ${customer.postcode}
    </div> <!-- ends customer-address -->
    <div id="customer-contact-details">
        <h4>Contact Number:</h4>
        ${customer.phone}
        <h4>Contact Email:</h4>
        ${customer.email}
    </div> <!-- ends customer-contact-details -->
</jsp:body>

The methods inside Checkout.java to collect and return information back to the View are virtually identical to those in Basket.java, the only difference being what the application does if it does not find the relevant information in the session. 'SubmitOrder.java' servlet also has all these methods, and we are starting to see some code repetition here. You could just copy the code over, or you can do as I have done and create a method inside 'Basket.java', that is then called upon by the other two servlets. Why am I redoing all these calculations instead of just retrieving the information from the session? Because it is possible that the user has added more items to the order in the meantime and the information could be out of date. As usual, the final versions of all the files modified or created in this section will be linked at the end.

Inserting the order information into the database requires inserting into two tables, and the program needs to get the automatically generated key (orderid) from the first insert to use it in the second. The second insert method then needs to run a loop to insert all of the albums from the order. Begin by creating these two methods in CustomerDAO. The SQL statements are:

private static String INSERT_ORDER = "INSERT INTO orders (custid, order_date, order_total) VALUES (?, ?, ?)";
private static String INSERT_ORDER_DETAILS = "INSERT INTO order_details (orderid, recording_id, price, order_quantity, total) VALUES (?, ?, ?, ?, ?)";

All very straightforward so far. The first method takes the Customer object and the grandTotal as arguments. It introduces a new concept, using the PreparedStatement's RETURN_GENERATED_KEYS method. The method then gets that generated key and returns it to the SubmitOrder servlet to be used in the second method. The date is inserted using the current date and time at the time the order is processed.

public int insertOrder(Customer customer, float grandTotal) {
    int orderId = 0;
    try {
        conn = new DBConnection().openConnection();
        pstmt = conn.prepareStatement(INSERT_ORDER, PreparedStatement.RETURN_GENERATED_KEYS);
        pstmt.setInt(1, customer.getCustId());
        pstmt.setTimestamp(2, java.sql.Timestamp.valueOf(java.time.LocalDateTime.now()));
        pstmt.setFloat(3, grandTotal);
        pstmt.executeUpdate();
        ResultSet rs = pstmt.getGeneratedKeys();
        if (rs != null && rs.next()) {
            orderId = rs.getInt(1);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        DBConnection.closeConnection(conn);
    }
return orderId;
}

The second method in CustomerDAO takes the orderId (from teh first method) and the albums ArrayList as arguments. After establishing a connection to the database and defining the statement, it runs a loop through all the albums in the ArrayList, extracts the relevant information to use as variables in the statement and executes them one by one. There is another method by which you can insert all the statements in one go, using executeBatch() instead of executeUpdate(). This would be useful if you had many many rows to insert but not really necessary in this example.

public void insertOrderDetails(int orderId, ArrayList<MusicRecordings> albums) {
    try {
        conn = new DBConnection().openConnection();
        pstmt = conn.prepareStatement(INSERT_ORDER_DETAILS);
        for(MusicRecordings album : albums) {
            pstmt.setInt(1, orderId);
            pstmt.setInt(2, album.getRecordingId());
            pstmt.setFloat(3, album.getPrice());
            pstmt.setInt(4, album.getQuantity());
            pstmt.setFloat(5, album.getTotalPrice());
            pstmt.executeUpdate();
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        DBConnection.closeConnection(conn);
    }
}

With those in place, it is time to use them in the 'SubmitOrder.java' servlet. First get hold of the session and, if the session variables for customer and order exist (otherwise redirect to the home page) use the methods in 'Basket.java' to create an ArrayList of albums and to calculate the grandTotal. Then get hold of the customer from the session, get an instance of CustomerDAO and insert the customer and the grandTotal into the 'insertOrder()' method. If the orderId that comes back is not 0, the insert method worked and you can use it along with the list of albums in the 'insertOrderDetails()' method. Then set a success message for the customer, revoke the 'albums' and 'order' session variables and return the user the the home page. If it didn't work (orderId == 0) return the user to the home page with an error message.

HttpSession session = request.getSession();
if (session.getAttribute("customer") != null && session.getAttribute("order") != null) {

    ArrayList<MusicRecordings> albums = Basket.createAlbumArray(session);
    float grandTotal = Basket.calculateGrandTotal(albums);

    Customer customer = (Customer) session.getAttribute("customer");
    CustomerDAO cdao = CustomerDAO.getInstance();
    int orderId = cdao.insertOrder(customer, grandTotal);
			
    if(orderId != 0) {
        cdao.insertOrderDetails(orderId, albums);
        request.setAttribute("msg", "Order completed successfully.");
        session.setAttribute("order", null);
        session.setAttribute("albums", null);
    } else {
        request.setAttribute("error", "Sorry, something went wrong.");
    }

    request.getRequestDispatcher("/WEB-INF/home.jsp").forward(request, response);	
} else {
    response.sendRedirect("/WEB-INF/home.jsp");
}

When a customer purchases an item we need to reduce the amount in stock by the quantity that the customer has bought. Create a private method inside CustomerDAO called "reduceStockCount()", it takes tow arguments, recordingId and quantity and returns nothing. The SQL statement is simply to reduce the stock_count for the recording_id by the quantity amount.

private static String REDUCE_STOCK_COUNT = "UPDATE Music_Recordings SET stock_count = stock_count - ? WHERE recording_id = ?";

And the method, again it is very similar to the ones we've done before, call this method inside the loop in 'insertOrderDetails()' just after the 'executeUpdate()'.

private void reduceStockCount(int recordingId, int quantity) {
    try {
        conn = new DBConnection().openConnection();
        pstmt = conn.prepareStatement(REDUCE_STOCK_COUNT);
        pstmt.setInt(1, quantity);
        pstmt.setInt(2, recordingId);
        pstmt.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        DBConnection.closeConnection(conn);
    }
}

One more thing left for this section and that is to display a list of all previous orders for the customer that is logged in. To do this I created two new models, one that lists the full details for each order and one that has things that are the same for each item in a particular order (order ID, order date, and total price for the order). Below are the lists of variables for each new model class, you will need to add constructors, getters and setters etc.

private int orderId;
private Date orderDate;
private float orderTotal;
private int orderId;
private int custId;
private Date orderDate;
private float orderTotal;
private int recordingId;
private float price;
private int orderQuantity;
private float itemTotal;
private String artistName;
private String title;
private String category;
private int numTracks;

Next, create two new methods in CustomerDAO to get this information from the database. The SQL statements are below. The first one is straightforward, it merely gets the information that is in the 'orders' table for a particular customer. The second one is a bit more complicated. It needs to combine information from three tables, the 'orders' table, 'order_details', and 'Music_Recordings', in order to get order information only for a particular customer and get all the album information. The SQL uses aliases to shorten the table names because when you specify controlling by an attribute, you need to say which table that attribute belongs to. You might want to test these in the mysql prompt in the terminal first, to ensure that they do return the data that you expect them to (that corresponds with your models).

SELECT * FROM orders WHERE custid = ? ORDER BY orderid DESC;
SELECT * FROM orders o, order_details od, Music_Recordings mr WHERE o.orderid = od.orderid AND od.recording_id = mr.recording_id AND custid = ? ORDER BY order_date;

The actual methods are exactly the same a previous ones for selecting data. Create an ArrayList with the type as the model, open the connection, set the statement, set the customer ID, execute the query which returns a result set, loop over the result set, put the variables into the model and the model into the ArrayList. Then return the ArrayList. I'm not going to print the code here, if you get stuck the complete files will be listed at the end of the section.

The servlet "ShowAllMyOrders" also contains nothing new. Get the session, check that the customer is logged in, get their details, get an instance of the DAO, execute the two methods in the DAO and return the results to the JSP. If the customer is not logged in, return the home page.

HttpSession session = request.getSession();
		
if (session.getAttribute("customer") != null) {
			
    Customer customer = (Customer) session.getAttribute("customer");
    CustomerDAO cdao = CustomerDAO.getInstance();
    ArrayList<CustomerOrders> orders = cdao.customerOrders(customer.getCustId());
    ArrayList<CustomerOrderDetails> customerOrderDetails = cdao.customerOrderDetails(customer.getCustId());			
			
    request.setAttribute("customer", customer);
    request.setAttribute("orders", orders);
    request.setAttribute("order_details", customerOrderDetails);
    request.getRequestDispatcher("/WEB-INF/show-all-orders.jsp").forward(request, response);
} else {
    response.sendRedirect("/WEB-INF/home.jsp");
}

"show-all-orders.jsp" is more interesting. What we want to do is have a separate table for each order. The main details of the order (ID, date, total price) are printed once above the table, then each album has a separate row in the table. This is done using two loops with one for 'order_details' nested inside one for 'orders'. The application compares the orderid from both lists and prints a row where the id for the order details match the id for the order. The row details I copied from the checkout page.

<h2 class="indent">Orders for ${customer.fName} ${customer.lName}</h2>
<c:forEach items="${orders}" var="order">
		
    <div class="indent">Order ID: ${order.orderId} &nbsp - &nbsp Order Date: ${order.orderDate} &nbsp - &nbsp Order Total: £${order.orderTotal}</div>			
    <table class="musicList sortable">
    <tr><th></th><th>Artist</th><th>Title</th><th>Category</th><th># of Tracks</th><th>Price</th><th>Quantity</th><th>Total Price</th></tr>
			
    <c:forEach items="${order_details}" var="album">
        <c:if test="${album.orderId == order.orderId}">
            <tr>
                <td class="center"><a href="TrackLister?recordingId=${album.recordingId}">
                    <img src="${pageContext.request.contextPath}/resources/images/covers/sm/${album.recordingId}.jpg"/></a></td>
                <td class="left"><a href="ArtistFinder?artist=${album.artistName}">${album.artistName}</a></td>
                <td class="left"><a href="TrackLister?recordingId=${album.recordingId}">${album.title}</a></td>
                <td class="left"><a href="Categories?category=${album.category}">${album.category}</a></td>
                <td class="center">${album.numTracks}</td>
                <td class="right">£${album.price}</td>
                <td class="center">${album.orderQuantity}</td>
                <td class="right">£${album.itemTotal}</td>
            </tr>
        </c:if>
    </c:forEach>
    </table>
    <br /><br />
</c:forEach>

The result should look like this. Note that the orders are most recent first. This is the result of 'ORDER BY orderid DESC' in the SQL statement.

Order-history.png

CustomerDAO | Basket.java | Checkout.java | checkout.jsp | SubmitOrder.java | CustomerOrders.java | CustomerOrderDetails.java | ShowAllMyOrders.java | show-all-orders.jsp

Admin Features

In this section we are going to create a simple interface that allows anyone logged in as 'admin' to delete an album from the database, update its details, or to add a new album along with its tracks. If you are using the four users listed above, the admin user is John Lennon. If you have created your own users you will need to make one of them as admin. You can do this through the MySQL command line interface using this command, but replace the question mark with the ID of the admin user.

UPDATE customers SET admin = 1 WHERE custid = ?

We are going to put this code inside 'album-list.jsp' because this code is used by all the pages that show a list of albums, so it will be displayed over all f the relevant pages. In 'album-list.jsp' add the following code at the end of the table. It will add a new column that contains two new [links styled as] buttons. One directs to a servlet that will perform the action to delete an album from the database. The other button links to a servlet that selects the album information and places it into some input fields that will be above the table. The links are contained within a condition that means they will only be displayed if the user is an admin.

<c:if test="${customer.admin == 1}">
    <td style="border: 0;">
        <a class="btn btn-delete" href="DeleteItem?recordingId=${album.recordingId}">
            Delete
        </a>
        <a class="btn btn-select" href="SelectItem?recordingId=${album.recordingId}">
            Select
        </a>
    </td>
</c:if>

Above the header row but still inside the table declaration, add the following code.

<c:if test="${customer.admin == 1}">
    <tr>
        <td style="border: 0;"><form id="UpdateDB" class="tr" action="UpdateDB" method="POST"><input type="hidden" name="recordingId" value="${empty select.recordingId ? '' : select.recordingId}"/></form></td>
        <td style="border: 0;"><input form="UpdateDB" class="admin-edit" name="artist_name" type="text" placeholder="Artist Name" value="${empty select.artistName ? '' : select.artistName}"/></td>
        <td style="border: 0;"><input form="UpdateDB" class="admin-edit" name="title" type="text" placeholder="Album Title" value="${empty select.title ? '' : select.title}"/></td>
        <td style="border: 0;"><select form="UpdateDB" name="category"><c:forEach items="${categories}" var="category"><option ${category.name == select.category ? 'selected' : ''}>${category.name}</option></c:forEach></select></td>
        <td style="border: 0;"><input form="UpdateDB" class="small-td" name="num_tracks" type="number" min="1" max="99" placeholder="Tracks" value="${empty select.num_tracks ? '' : select.num_tracks}"/></td>
        <td style="border: 0;"><input form="UpdateDB" class="small-td" name="price" type="text" placeholder="Price" value="${empty select.price ? '' : select.price}"/></td>
        <td style="border: 0;"><input form="UpdateDB" class="small-td" name="stockCount" type="number" min="0" max="999" placeholder="Stock" value="${empty select.stockCount ? '' : select.stockCount}"/></td>
        <td style="border: 0;">
            <button form="UpdateDB" class="btn btn-insert">Add/Update</button>
            <button form="UpdateDB" class="btn btn-clear" onClick="this.form.reset()">Clear</button>
        </td>
    </tr>
</c:if>

There are a couple of new concepts introduced here. First, a HTML form, if it is to follow HTML4 specifications, must either be entirely enclosed within a table TD, or it must have the table entirely within the form. Neither of these options are suitable for us because the table already contains a form (to add an album to the order) and we want the input fields to line up nicely with the other columns. HTML5 introduces a new attribute that solves this problem. If you give the form an 'id', you can place inputs anywhere on the page as long as they contain a 'form' attribute that is the same as the form id. The 'form' attribute links all the items together and makes them act as a single form.

The other new concept here is using Java Expression Language, which was used before to simply print out the variables passed to the JSP, to evaluate a true or false condition. To use it you pass in a query that can be answered either true or false, then provide the text that you would like to be printed in each instance. An example is below.

${question that can be evaluated to true or false ? 'text if true' : 'text if false'}

In the application code above, the Java EL is testing whether there is a value inside 'select.whatever', which there will be if you clicked the 'Select' button, otherwise it will display the placeholder text.

Another change to make is to replace the 'Add' button with the stock count. An admin does not need to place an order but they might want to view or change the amount in stock. Inside the choose/when/otherwise block that displays the 'Add' button, place another 'when' at the top like this:

<c:when test="${customer.admin == 1}">
    ${album.stockCount}
</c:when>

And in the table headers, there was no column header here previously so you can simply write:

${customer.admin == 1 ? 'Stock' : '' }

Unfortunately I wasn't able to make this admin design work together with the sorttable script, so we'll disable it for admin users. In the table declaration where the table class is listed, replace 'sortable' with:

${customer.admin == 1 ? '' : 'sortable' }

That's all the changes for this page, so go ahead and run the application, log in as an admin and check that everything is displayed properly. It should look something like the screenshot below, except with placeholder text in the input boxes because yours don't work yet. The dropdown box for the categories might display the wrong information because not all servlets have the code to get the list of categories. This will be fixed later.

Admin-controls.png

Create the servlet "SelectItem.java". In it we are going to check for the existence of the recordingId parameter, make sure that it is a number, then get the album information pertaining to that recordingId, set it as the session parameter and return the user to the page they just came from. It's not printed here but I just copied the function to get the previous page from another servlet.

if(request.getParameterMap().containsKey("recordingId")) {
    String r_id = request.getParameter("recordingId");
    if(StringUtils.isStrictlyNumeric(r_id)) {
        int recordingId = Integer.parseInt(r_id);
        MusicDAO mdao = MusicDAO.getInstance();
        MusicRecordings album = mdao.findSingleAlbum(recordingId);
        HttpSession session = request.getSession();
        session.setAttribute("select", album);
    }
}		
String from = getPreviousPageByRequest(request).orElse("/");
response.sendRedirect(from);

The reason I'm using the session parameter here instead of request parameter is because I'm using sendRedirect, which doesn't support parameters and I can't send the user back to the previous page using RequestDispatcher as well as the parameters. This means we need to add a bit of code to Categories.java, ArtistFinder.java, and PricePicker.java to grab the select attribute from the session and add it to the request. I've also added code for the last two servlets to get a list of the categories for the dropdown box. Categories.java already has this code so no need to repeat it.

request.setAttribute("select", null);
HttpSession session = request.getSession();
if(session.getAttribute("select") != null) {
    MusicRecordings album = (MusicRecordings) session.getAttribute("select");
    request.setAttribute("select", album);
    session.setAttribute("select", null);
}
		
if(session.getAttribute("customer") != null) {
    Customer cust = (Customer) session.getAttribute("customer");
    if(cust.getAdmin() == 1) {
        ArrayList<MusicCategories> categories = mdao.findAllCategories();
        request.setAttribute("categories", categories);
    }
}

Now test the application again. Make sure that when you click the 'Select' button, the correct album information is loaded in to the input boxes above the table.

Next task is to create the functionality for the Delete button. The concept is fairly straightforward. The link passes the recording id to the servlet, which then calls two methods in the DAO to delete the album, and the album tracks. I decided to create a new DAO for the tasks in this section, which I called AdminDAO. Include the code that makes it a Singleton. The SQL statements for deleting are both more or less the same

private static String DELETE_ALBUM_TRACKS = "DELETE FROM Music_Tracks WHERE recording_id = ?";
private static String DELETE_ALBUM = "DELETE FROM Music_Recordings WHERE recording_id = ?";

And the method is also identical, so I'll only reproduce one of them here. Be sure to create methods for both 'delete album' and 'delete album tracks'. The method returns an integer, which is 0 if something went wrong otherwise it is the number of records (rows in the database) that were deleted.

public int deleteAlbumTracks(int recordingId) {
    int deleted = 0;
    try {
        conn = new DBConnection().openConnection();
        pstmt = conn.prepareStatement(DELETE_ALBUM_TRACKS);
        pstmt.setInt(1, recordingId);
        deleted = pstmt.executeUpdate();
        pstmt.close();
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        DBConnection.closeConnection(conn);
    }
    return deleted;
}

The servlet "DeleteItem.java" checks for a customer in the session, that the customer is an admin, that there is a recordingId parameter passed in, and that the value can be converted to an integer. If it passes all these checks it passes the recordingId to both of the delete methods in the DAO. Although it doesn't matter for this simple database, you would normally delete the album tracks first before deleting the album because the tracks will depend on the album id as a foreign key and the database will refuse to delete records that have a foreign key dependency. This servlet, like others that modify the data in the database makes more stringent checks that the data coming from the website is correct and workable. A more robust web application would put checks like these everywhere that data is coming in from the front end but I'm trying not to complicate things too much for the sake of this tutorial.

HttpSession session = request.getSession();
if (session.getAttribute("customer") != null) {
    Customer cust = (Customer) session.getAttribute("customer");
    if (cust.getAdmin() == 1 && request.getParameter("recordingId") != null) {
        String r_id = request.getParameter("recordingId");
        try {
            int recordingId = Integer.parseInt(r_id);
            AdminDAO adao = AdminDAO.getInstance();
            int deletedTracks = adao.deleteAlbumTracks(recordingId);
            int deletedAlbum = adao.deleteAlbum(recordingId);
					
            if (deletedTracks == 0) { session.setAttribute("Error", "Error: No tracks to delete."); }
            if (deletedAlbum == 0) { session.setAttribute("Error:", "Error: No album to delete."); }
					
        } catch (Exception e) {
            request.getRequestDispatcher("/WEB-INF/home.jsp").forward(request, response);
        }
    } else {
        request.getRequestDispatcher("/WEB-INF/home.jsp").forward(request, response);
    }
} else {
    request.getRequestDispatcher("/WEB-INF/home.jsp").forward(request, response);
}		
String from = getPreviousPageByRequest(request).orElse("/");
response.sendRedirect(from);

If the script is successful the application should return the user to the page they were on and the album should disappear from view. Try it out. You can always reload the database from the script (at the beginning of the tutorial) afterwards.

The UpdateDB servlet is going to handle both updating an album and inserting a new one because the initial interface is the same for both. Do all the usual checks that the person calling this servlet is authorised to do so, then get the parameters from the form and put them into an object of type MusicRecordings. In the form there is a hidden field that contains the recording id. If this is an Update (i.e. the information in the form was initially placed there via the Select button) the recordingId will be that of the album. Otherwise it will be blank and can be set to 0. Next get a list of the recording ids and do another check to make sure that this is an existing album. The code for this is

private static String GET_RECORDING_IDS = "SELECT recording_id FROM Music_Recordings";
public ArrayList<Integer> getRecordingIds() {
    ArrayList<Integer> recordingIds = new ArrayList<Integer>();
    try {
        conn = new DBConnection().openConnection();
        pstmt = conn.prepareStatement(GET_RECORDING_IDS);
        ResultSet resultSet = pstmt.executeQuery();
        while (resultSet.next()) {
            recordingIds.add(resultSet.getInt("recording_id"));
        }
        pstmt.close();
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        DBConnection.closeConnection(conn);
    }		
    return recordingIds;
}

The method returns an integer ArrayList and you can use the 'contains' method on the ArrayList to see if it contains the recordingId from the form. If it does, run the Update method in the DAO.

private static String UPDATE_ALBUM = "UPDATE Music_Recordings SET artist_name = ?, title = ?, category = ?, image_name = ?, num_tracks = ?, price = ?, stock_count = ? WHERE recording_id = ?";

The update method will set all of the fields because we don't know which one was changed. When setting the values in the PreparedStatement remember to put the recordingId last.This method can also return an integer, which can be used to determine success or failure. Once the update is done, return the user back to where they came from.

public int updateAlbum (MusicRecordings album) {
    int success = 0;
    try {
        conn = new DBConnection().openConnection();
        pstmt = conn.prepareStatement(UPDATE_ALBUM);
        pstmt.setString(1, album.getArtistName());
        pstmt.setString(2, album.getTitle());
        pstmt.setString(3, album.getCategory());
        pstmt.setString(4, album.getImageName());
        pstmt.setInt(5, album.getNum_tracks());
        pstmt.setFloat(6, album.getPrice());
        pstmt.setInt(7, album.getStockCount());
        pstmt.setInt(8, album.getRecordingId());
        success = pstmt.executeUpdate();
        pstmt.close();
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        DBConnection.closeConnection(conn);
    }
    return success;
}

If this is a new album, forst we need to determine what the recording id will be. Normally this would be a field that is automatically incremented in the database, but this database has not been designed in this way. While I could fix it (and it would be a simple fix), when programming you often have to deal with data in a less than desirable format and a workaround has to be created. Since we already have a list of the recording ids we can use Collections.max() to get the highest one in the list, then increment it by one and set album.setRecordingId(). I created a small method for this, partly to keep the code clean and partly because it will be needed again when we come to insert the tracks (which suffers from the same problem).

protected static int getMaxId(ArrayList<Integer> ids) {
    Integer max = Collections.max(ids);
    return max;
}

Call the InsertAlbum method in the DAO, then set the album as a request attribute and forward to a new JSP called "insert-tracks.jsp". All the code for UpdateDB.java is linked at the end of this section.

The page to insert the tracks is very similar to the one that lists album tracks for an album already in the database. the main differences are that the table that lists tracks is now inside a form that submits to "InsertTracks.java". The form contains a hidden input field that contains the 'recordingId', and input fields for title and duration. To display the correct number of input fields I ran a forEach loop limited by the 'num_tracks' attribute from the album passed in. Please note that if you have placed the album cover in the images folder Tomcat won't show it until you have refreshed the project in Eclipse (because it doesn't know it's there). The code for the form is here.

<form action="InsertTracks" method="POST">
    <input type="hidden" name="recordingId" value="${album.recordingId}">
    <table class="musicList sortable" id="trackLister">
        <tr>
            <th>Track #</th>
            <th>Title</th>
            <th>Duration</th>
        </tr>
        <c:forEach begin="1" end="${album.num_tracks}" varStatus="loop">
            <tr>
                <td>${loop.index}</td>
                <td><input name="title" type="text" placeholder="Track Title"></td>
                <td><input name="duration" type="number" min="1" max="1500" style="width: 3em"> seconds</td>
            </tr>
        </c:forEach>
    </table>
    <input type="submit" value="Submit">
</form>

Create the servlet "InsertTracks.java". Once you have made the usual checks that the person accessing this servlet is authorised etc., get the track ids from the database, the method in the DAO is virtually identical to the one that gets the recording ids, just a different statement, and figure out what the highest value is using that method in UpdateDB. The insertAlbumTracks method in the DAO accepts an ArrayList of type MusicTracks but coming from the request we just have a list of parameters, all with the same name (!) Put them into a String array using "String[] names = request.getParameterValues("title");", then iterate over one of them in a For Loop. Remembering to increment the trackId inside the loop, create a new MusicTracks object and set its values using the two arrays created from the request parameters. You need to set the trackid, recording_id, title, and duration. Then add the track to the MusicTracks ArrayList and, once the loop has completed, pass the ArrayList to the insertAlbumTracks method in the DAO. When it has completed you can do a send.Redirect() to the TrackLister servlet to display the tracks as they appear to the user. If you deleted the doPost() method in 'TrackLister.java' you can replace it using 'Source > Override/Implement Methods' in Eclipse. The only code inside the method needed is "doGet(req, resp);"

Finally there needs to be a way to edit the track list. This can be done using files that have already been created, with a few small modifications. In 'album.jsp' create a link styled as a button that only the admin can see. The link goes to the 'TrackLister' servlet and has two parameters, the recordingId and an action of modify. I placed it at the bottom of the table, just after the c:forEach.

<c:if test="${customer.admin == 1}">
    <tr><td class="right" colspan="3" style="border: 0;"><a class="btn btn-update" href="TrackLister?recordingId=${album.recordingId}&action=modify">Modify</a></td></tr>
</c:if>

The TrackLister servlet checks for this parameter and if it finds it, confirms that the user is an admin then redirects the user to 'insert-tracks.jsp'. After this there needs to be a return statement otherwise Java will continue to process the servlet and will be confused by there being two RequestDispatchers.

// If this is an admin edit request
if (request.getParameterMap().containsKey("action")) {
    String action = request.getParameter("action");
    HttpSession session = request.getSession();
    Customer customer = (Customer) session.getAttribute("customer");
    if (action.equals("modify") && customer.getAdmin() == 1) {
        request.getRequestDispatcher("/WEB-INF/insert-tracks.jsp").forward(request, response);
        return;
    }
}

The 'insert-tracks.jsp' needs to know whether this is modifying an existing track list or creating a new one. I used a choose/when/otherwise conditional block to check if a track list is coming through, and two forEach loops to display the input boxes, with or without data.

<c:choose>
    <c:when test="${not empty tracks}">
        <c:forEach items="${tracks}" var="track">
            <tr>
                <td>${track.trackNumber}</td>
                <td><input name="title" type="text" placeholder="Track Title" value="${track.title}"></td>
                <td><input name="duration" type="number" min="1" max="1500" style="width: 3em" value="${track.duration}"> seconds</td>
            </tr>
        </c:forEach>
    </c:when>
    <c:otherwise>						
        <c:forEach begin="1" end="${album.num_tracks}" varStatus="loop">
            <tr>
                <td>${loop.index}</td>
                <td><input name="title" type="text" placeholder="Track Title"></td>
                <td><input name="duration" type="number" min="1" max="999" style="width: 3em"> seconds</td>
            </tr>
        </c:forEach>
    </c:otherwise>
</c:choose>

The servlet 'InsertTracks.java' merely needs to know whether these tracks exist in the database or not. If they do, the easiest way to deal with it is to delete them, then reinsert them. We already have the method in AdminDAO for deleting all the tracks of an album, so call that just before you insert the new ones. What you will need is a new method to get the recording ids from the Music_Tracks table. Unfortunately Java's PreparedStatement does not allow for the table name to be inserted as a variable. It is possible to do something like a String.replace() or to concatenate it in, but both these methods leave you at the risk of SQL injection. While this would not be a problem in this particular instance because the variables would be hard coded, it is still not good practice, so I created a new method in AdminDAO, which is exactly the same as the other method for getting recording ids, just the SQL statement that is different.

private static String GET_TRACK_RECORDING_IDS = "SELECT recording_id FROM Music_Tracks";

And the calling method in 'InsertTracks.java':

// If the recording ID exists in the Music_Tracks table, this is an update, otherwise it's an insert
// Because of how the table is structured, it's easier to delete all the tracks and re add them
ArrayList<Integer> recordingIds = adao.getTrackRecordingIds();
if (recordingIds.contains(recordingId)) {
    adao.deleteAlbumTracks(recordingId);
}

AdminDAO.java | album.jsp | album-list.jsp | ArtistFinder.java | Categories.java | DeleteItem.java | insert-tracks.jsp | InsertTracks.java | PricePicker.java | SelectItem.java | UpdateDB.java

Extra Information

Thank you for using my tutorial, I hope you found it useful and were able to learn a few new ideas and techniques. I am always happy to receive feedback. Let me know what you like, or what you don't like. You can contact me at https://markbellingham.me/contact.php

This tutorial is intended as a basic introduction to dynamic websites and as a demonstration of a few of the things that are possible. It is not intended to be robust enough to be used in a production environment. The author strongly advises that anyone trying to create a dynamic website for a production environment undertakes a serious review into website security. This article may provide a starting point but is by no means exhaustive.

If you want to recreate this project with a more in-depth database based on your own music collection and your music files have good tagging information, you can use my Python script to extract the tags and insert them into a MySQL database. The scripts can be found on my GitHub portal. Please note that the setup of the database tables is different to the one used in this tutorial, so you will have to reconfigure your application to take account of this.