JSP Assignment 2 (Java Server Pages)

Library catalog

Create a web application using JSP, MySQL, and the regular front-end technology stack (HTML, CSS, JavaScript) to allow patrons to interact with your local librarys catalog online. Your application can include client-side JavaScript to make the interface more user-friendly, but it should always perform the necessary server-side validations to protect the integrity of the librarys catalog and inventory.

Requirements

The librarian has given you the following requirements:

1. The web application should include a log in and sign up page.

• Log in page should use a username and password.
• Sign up page should require the following fields: first name, last name, username, password, and password confirmation.
• There should be a JSP session running to keep track of whether the user is logged in or not.

2. Any user can browse the catalog, regardless of whether theyve signed up or logged in. In other words, there should be no session check on the page that allows browsing of the catalog.

• The user should be able to select from a list of topics that filters the books visible on the browsing screen. Filtering by a different topic than the one currently selected should fire off a query to the server - filtering should not be happening in the browser / JavaScript.
• If no topic is selected, the page should show all books.
• The following information should be shown at a minimum: Book title, book author, whether its available for checkout, and a way to reserve a copy.

3. When they are logged in, users should be able to reserve a copy of their favorite available books from the browse catalog page. If not logged in, the user should still see the button to reserve the copy which will take them to the log in page upon click.

4. There should be a page which allows logged in users to show their list of reserved books. If not logged in, trying to navigate to this page (even via a direct URL) should require the user to log in first.

Database structure

There is one SQL file attached with the assignment to create the database library_catalog with the following structure:

File db: library_catalog.sql

-- phpMyAdmin SQL Dump

-- version 3.3.9

-- http://www.phpmyadmin.net

--

-- Host: localhost

-- Generation Time: Apr 05, 2017 at 09:30 PM

-- Server version: 5.5.8

-- PHP Version: 5.3.5

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

--

-- Database: `library_catalog`

--

CREATE DATABASE `library_catalog` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;

USE `library_catalog`;

-- --------------------------------------------------------

--

-- Table structure for table `authors`

--

CREATE TABLE IF NOT EXISTS `authors` (

`author_id` int(10) NOT NULL AUTO_INCREMENT,

`author_name` varchar(100) NOT NULL,

PRIMARY KEY (`author_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--

-- Table structure for table `books`

--

CREATE TABLE IF NOT EXISTS `books` (

`book_id` int(10) NOT NULL AUTO_INCREMENT,

`topic_id` int(10) NOT NULL,

`book_name` varchar(100) NOT NULL,

`author_id` int(10) NOT NULL,

`is_available` tinyint(1) NOT NULL,

PRIMARY KEY (`book_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--

-- Table structure for table `reservations`

--

CREATE TABLE IF NOT EXISTS `reservations` (

`user_id` int(10) NOT NULL,

`book_id` int(10) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--

-- Table structure for table `topics`

--

CREATE TABLE IF NOT EXISTS `topics` (

`topic_id` int(10) NOT NULL AUTO_INCREMENT,

`topic_name` varchar(200) NOT NULL,

PRIMARY KEY (`topic_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--

-- Table structure for table `users`

--

CREATE TABLE IF NOT EXISTS `users` (

`user_id` int(10) NOT NULL AUTO_INCREMENT,

`fname` varchar(50) NOT NULL,

`lname` varchar(50) NOT NULL,

`username` varchar(50) NOT NULL,

`password` varchar(50) NOT NULL,

PRIMARY KEY (`user_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Screen capture of final product.

1. Index.jsp

2. Index.jsp (search button clicked)

3. Login.jsp (or register.jsp or reserve.jsp before login session)

4. Login.jsp (or register.jsp or reserve.jsp before login session)

5. Home.jsp (after successfully login with session active)

6. Home.jsp (after successfully login with session active) for searching book topic

7. Reserve.jsp (when the link reserved a copy is clicked)

8. Reserve.jsp (when the link reserved a copy is clicked) after 3 times reserved books

9. If logout is clicked then back to index.jsp

TABLES STRUCTURE:

Hockholm VISD Assignment 2/odening Library Catalog Sign up Login Search book Al took me Topic Author Action localhost80/S Agent.index Library Catalog Sign up Login Search book AI Bookmame Topic Author TH Intro XML Basic Intro ISP Basic XM XM SP Ben Bos Bert Bos Timby Action Resto Reserta peny Rent Reserve a copy localhost/1SP Assignment Moginjsp?b Library Catalog Login Username Pastword Login You must login to continue! Register First Name Name Username Password Confirm Password Signup localhost 1080/SP Assignment/loginjiple Library Catalog Login Username: Password Login Username or password is incorrect! Register First Name: Abhishek Last Name: ariala Username: v10003 Password Confirm Password Sign up Password and confirm password must match! Library Catalog Logout Hello, v000 Search book A Bookm Author Arsion O Library Catalog Logout Hello, v0003 Search book Search Bres ISP Tim echo 0 Library Catalog Logout Hello, v0003 Reservations Intro 1 0 41 Library Catalog Logout Hello, v0003 Reservations Bookm Intro terto Library Catalog Sign up Login Search book Author Action Book IM Reserve Data Type Required authors' table Column Name author_id author_name int (10) yes Description Author ID, primary key Author full name varchar(100) yes 'books' table Column Name Data Type Required Description book_id int (10) yes Book ID, Primary key topic_id int (10) yes Subject ID book_name yes Book name varchar(100) int (10) author_id yes Author ID is_available tinyint (1) Yes If the book is available or not "reservations' table Column Name user_id Required Description Data Type int (10) yes User ID book_id Int (10) yes Book ID 'topics' table Column Name topic_id Data Type Required Description Topic ID, Primary key int (10) yes topic_name varchar (200) yes Topic name 'users' table Column Name Data Type Required Description user_id int (10) yes User ID, Primary key frame varchar(50) yes User first name Iname varchar(50) yes User Last name username varchar(50) yes Username password varchar (50) Yes Password