BKDB Documentation

Transkript

BKDB Documentation

BKDB Documentation
Release 1.0
itudb1408
December 17, 2014
Contents
1
2
User Guide
1.1 User Parts Implemented by Mehmet Hüseyin TEMEL
1.2 Parts Implemented by Taha Emre . . . . . . . . . . .
1.3 Parts Implemented by Mustafa Safa Özdayı . . . . . .
1.4 Parts Implemented by Abdurrahman NAMLI . . . . .
1.5 Parts Implemented by Mücahit Adem Çelebi . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
3
3
5
8
12
14
Developer Guide
2.1 Parts Implemented by Mehmet Hüseyin TEMEL
2.2 Parts Implemented by Taha Emre . . . . . . . .
2.3 Parts Implemented by Mustafa Safa Özdayı . . .
2.4 Parts Implemented by Abdurrahman NAMLI . .
2.5 Parts Implemented by Mücahid Adem Çelebi . .
2.6 1 Database Design . . . . . . . . . . . . . . . .
2.7 2 Software Design . . . . . . . . . . . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
17
17
31
47
74
91
91
93
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
i
ii
BKDB Documentation, Release 1.0
Team itudb1408
Members
• Mehmet Hüseyin TEMEL
• Taha Emre
• Mustafa Safa Özdayı
• Abdurrahman Namlı
• Mücahit Adem ÇELEBİ
BKDB, The Book Database
BKDB is an IMDB inspired project for books. Our aim is to provide easy access to various books for our users.
By using the BKDB, users can create their own To Read and Favourite lists which allows them to organize their
readings and favourite books. They can also rate the books and write reviews about them and based on that, they
will get unique book recommendations from the BKDB. In addition to that, of course, they will be able to learn
many information about the books such as their summary, price, ISBN number etc.
Contents:
Contents
1
2
Contents
CHAPTER 1
User Guide
This part explains how BKDB appears from an users perspective
1.1 User Parts Implemented by Mehmet Hüseyin TEMEL
1.1.1 Regular Users Perspective
1. Publisher Page
2. Review Panel
3. Prices Panel
Publisher Page
Figure 1.1: Publisher Page
In a any book page if user clicks publisher of book ,user is redirected to pusblisher page. In publisher page there
is information about publisher. Also at the bottom of page there is a book panel which lists books of publisher
which is established by this publisher.
User can give rate to these books and can go their page.
Review Panel
In book page bottom of page there is review panel. Review panel includes users’ reviews about that book with
review’s owner name and review date. At the top of review panel there is a form to add a review by user.
Prices Panel
In book page bottom of page there is prices panel with cost, site name and site’s address. Price panel includes
book’s least prices in different sites.
3
Figure 1.2: Review Panel
Figure 1.3: Prices Panel
1.1.2 Admins Perspective
• Admin can access the pages which a regular user can access. In addition to that, admin has some extra
features.
1. Admin Publisher Page
2. Admin Price Page
Admin Publisher Page
Figure 1.4: Admin Publisher Page
Admin publisher page can be seen just by admin user. Admin can come this page from admin panel. Admin
can add a new publisher by fill up boxes which are bottom of page. And also can edit or delete any publisher by
clicking edit or delete buttons which are next to publisher list.
Admin Price Page
Admin price page can be seen just by admin user. Admin can come this page from admin panel. Admin can
add a new price belongs to any book by fill up boxes which are bottom of page. And also can edit or delete any
price by clicking edit or delete buttons which are next to publisher list. Book and site of price can be chosen from
dropdown boxes.
4
Chapter 1. User Guide
Figure 1.5: Admin Price Page
1.2 Parts Implemented by Taha Emre
1. Author Page
2. Prize Page
3. Person Panel
4. Prize Panel
Author Page
• When an user enters to an author page, he/she will be prompted with the following page.
Figure 1.6: AuthorPage consists of 3 parts. On top there is an image of the author. Below that the information
about the author is placed. On bottom there are panels of book which he wrote of, and by clicking it user can
reach the book page
Prize Page
• When an user enters to a prize page, he/she will be prompted with the following page.
Figure 1.7: PrizePage consists of 2 parts. On top there is informations about the prize. On bottom there are author
panels whom the prize is given. By clicking their name user can reach an author page.
1.2. Parts Implemented by Taha Emre
5
Person Panel
• In main page, user can type the name of the author in the label whom he wishes to search. After the clicking
author button, he will be forwarded to a page which contains the results of the search in form of person
panels.
Figure 1.8: Person Panel consists of 2 elements: On the left the picture of the author on the right the name of the
author as a link. As clicked the name, the user will be forwarded to that persons page.
Prize Panel
• In an author page, on bottom there are prize panels which the author won.
Figure 1.9: Prize Panel consists of 2 elements: on top, the name of the prize as a link to that prize page and on
bottom the year that the prize is given to that author.
features.
1. Admin Author Page
2. Admin Prize Page
3. Admin Prize Winners Page
Admin Author Page
• In admin author page, admin can add, delete or update an author.
Admin Prize Page
• In admin prize page, admin can add, delete or update a prize.
6
Figure 1.10: Admin can add an author by filling the labels, or he can click Author List-select one to edit or delete
to open the list of already added authors.
Figure 1.11: Admin can delete an author by clicking delete button near the desired author or can click the edit to
edit an author.
Figure 1.12: After clicking the edit link the labels will be automatically filled, and after applying desired changes,
click the edit button to finish the process.
Figure 1.13: Admin can add a prize by filling the labels, or he can click delete link to delete a prize, or he can
click edit to choose a prize to update.
Figure 1.14: After clicking the edit link the labels will be automatically filled, and after applying desired changes,
click the edit button to finish the process.
7
Admin Prize Winners Page
• In admin prize winners page, admin can give a new prize to an author or delete or update a given prize.
Figure 1.15: Admin can give a prize to an author by filling the labels and selecting the author name from the
dropdown list, or he can click Prize Winner List-select one to edit or delete to open the list of already given prizes.
Figure 1.16: Admin can delete a given prize by clicking delete button near the desired prize or can click the edit
to edit a given prize.
Figure 1.17: After clicking the edit link the labels will be automatically filled and drop down list will be automatically chosen, and after applying desired changes, click the edit button to finish the process.
1.3 Parts Implemented by Mustafa Safa Özdayı
1. BooksPage
2. GenrePanel
3. BooksPanel
4. BookPage
8
BooksPage
• When an user enters to webpage, he/she will be prompted with the following page.
Figure 1.18: BooksPage consist of 2 panels. GenrePanel on top and BookPanel on bottom.
• It is possible to search for books using the bar on top.
Figure 1.19: View of the BooksPage after ‘The’ is searched in Books
GenrePanel
• GenrePanel is a dropdown list. Initially, it appears like this:
Figure 1.20: GenrePanel(not expanded)
• Upon click, GenrePanel will expand and show the list of genres.
BooksPanel
• When a genre is clicked, it will override the BooksPanel (by default, high rated books are listed) and books
have that particular genre will fill the table.
• It is also possible to rate the books by using the dropdown lists.
BookPage
• When a book is clicked from the BooksPanel, the user will be directed to its BookPage.
• On top, information about that particular book is listed along with a rate panel.
• On bottom, there are read/fav buttons, review and prices panel. By using them, users can add books to their
book lists, write reviews about them and check their prices.
1.3. Parts Implemented by Mustafa Safa Özdayı
9
Figure 1.21: GenrePanel(expanded)
Figure 1.22: BooksPanel after ‘Comedy’ is clicked
Figure 1.23: The general view of a BookPage
Figure 1.24: Informations about the The Sirens of Titan
10
Figure 1.25: Review Panel and Prices Panel of the The Sirens of Titan
features.
1. AdminBookPage
2. AdminGenrePage
3. AdminSitePage
4. StatisticsPage
AdminBookPage
• Allows admin to carry table operations on the Book Table.
Figure 1.26: Figure explains how AdminBookPage should be used
AdminGenrePage
• Allows admin to carry table operations on the Genre Table.
Figure 1.27: Figure explains how AdminGenrePage should be used
11
AdminSitePage
• Allows admin to carry table operations on the Site Table.
Figure 1.28: Figure explains how AdminSitePage should be used
StatisticsPage
• A simple page that shows admin some statistics about the DB.
1.4 Parts Implemented by Abdurrahman NAMLI
1. HomePage
2. UserAuthPage
3. UserSettingsPage
4. MyListPage
5. RatePanel
HomePage
• In home page there are list of recommended books and at the top genre list. Also it includes navbar.
Figure 1.29: HomePage consists genre list and recommended books
12
UserAuthPage
• In this page user can login and if have not any membership it can register
Figure 1.30: User can login or register
UserSettingsPage
• In user settings page user can edit membership or delete by filling boxes and clicking buttons
Figure 1.31: User Settings Page
MyListPage
• User can marks the books as favorite or read
Figure 1.32: User can mark books
• In my list page books which are marked favorite or read by user are listed.
RatePanel
• Rate panel provides giving rate to the book.
features.
1.4. Parts Implemented by Abdurrahman NAMLI
13
Figure 1.33: My List Page Shows User’s Books
Figure 1.34: Rate Panel Next to the Books
1. AdminPage
2. AdminUserPage
3. AdminRatePage
AdminPage
• Admin Page main page of admin panel for librarian. There are nav bar to go to objects’ admin page, statistics
link and reset database links.
Figure 1.35: Admin Page
AdminUserPage
• In this page admin can edit and delete a user
AdminRatePage
• Admin here can reset rate of a book by choose the book from dropdown menu.
1.5 Parts Implemented by Mücahit Adem Çelebi
1. EditorPage
2. TranslatorPage
14
Figure 1.36: Admin User Page
Figure 1.37: Reset Rate at Admin User Page
EditorPage
• When a user enters to an editor page, he/she will be prompted with the following page.
Figure 1.38: Editor Page
• EditorPage consists of 3 parts. On top there is an image of the editor. Below that the information about the
editor is placed. On bottom there are panels of book which he wrote of, and by clicking it user can reach
the book page
TranslatorPage
• When a user enters to an translator page, he/she will be prompted with the following page.
• TranslatorPage consists of 3 parts. On top there is an image of the translator. Below that the information
about the translator is placed. On bottom there are panels of book which he wrote of, and by clicking it user
can reach the book page
features.
1. AdminEditorPage
2. AdminTranslatorPage
1.5. Parts Implemented by Mücahit Adem Çelebi
15
Figure 1.39: Translator Page
AdminEditorPage
• Admin can add an editor by filling the labels, or he can click Editor List-select one to edit or delete to open
the list of already added editor.
Figure 1.40: Admin Editor Page
AdminTranslatorPage
• Admin can add an translator by filling the labels, or he can click translator List-select one to edit or delete
to open the list of already added translator.
Figure 1.41: Admin Translator Page
16
CHAPTER 2
Developer Guide
Development details about the BKDB
• E/R diagram:
Figure 2.1: ER diagram of the BKDB
2.1 Parts Implemented by Mehmet Hüseyin TEMEL
2.1.1 Database Design
1 Tables
1.1 Publisher Table
• Publisher table was implemented for representing publisher companies of books
17
Name
id
name
country
Establish_Date
Information
Type
INTEGER
TEXT
TEXT
varchar(4)
TEXT
Not Null
1
1
0
0
0
Primary K.
1
0
0
0
0
• Information stores brief info about publisher.
Sql statement that initialize the publisher table:
1
2
3
4
5
6
7
CREATE TABLE "Publisher" (
"id" INTEGER PRIMARY KEY,
"name" TEXT,
"country" TEXT,
"Establish_Date" varchar(4),
"Information" TEXT
)
1.2 Review Table
• Review table stores all reviews(comments) of users about one books.
Name
id
user_id
book_id
description
date
Type
INTEGER
INTEGER
INTEGER
TEXT
datetime
Not Null
1
1
1
1
0
Primary K.
1
0
0
0
0
• description stores the comment
• user_id references the User Table and if user of a review is deleted(updated) also the review will be
deleted(updated)
• book_id references the Book Table and if user of a review is deleted(updated) also the review will be
deleted(updated)
Sql statement that initialize the Review table:
1
2
3
4
5
6
7
8
9
CREATE TABLE Review (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
user_id INTEGER NOT NULL,
book_id INTEGER NOT NULL,
description TEXT NOT NULL,
"date" datetime,
FOREIGN KEY(user_id) REFERENCES user(ID) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(book_id) REFERENCES book(ID) ON DELETE CASCADE ON UPDATE CASCADE
)
1.3 Price Table
• Price Table prices of books in sites
Name
id
cost
book_id
site_id
Type
INTEGER
FLOAT
INTEGER
INTEGER
Not Null
1
1
1
1
Primary K.
1
0
0
0
• site_id references the Site Table and if user of a review is deleted(updated) also the price will be
deleted(updated)
18
Chapter 2. Developer Guide
• book_id references the Book Table and if user of a review is deleted(updated) also the price will be
deleted(updated)
Sql statement that initialize the Price table:
1
2
3
4
5
6
7
CREATE TABLE "Price" (
"id" INTEGER PRIMARY KEY NOT NULL,
"cost" FLOAT NOT NULL,
"book_id" INTEGER Not Null References BOOK(ID) on delete cascade on update cascade,
"site_id" INTEGER not null References SITE(ID) on delete cascade on update cascade,
CONSTRAINT Kontrol UNIQUE (cost, site_id)
)
2.1.2 Code
1 Publisher.java
This class is for representing companies which publish books. It inherits Model class. It is main class of publisher
object.
Attributes:
1
2
3
4
5
6
private
private
private
private
private
private
String name;
Serializable id;
String country;
List<Book> books;
String ESTABLISH_DATE;
String information;
• name: keeps the name of publisher.
• id : keeps the id of publisher. It generated randomly in database and it is unique.
• country: specifies country of publisher co.
• books : It is used for returning books of publisher published.
• ESTABLISH_DATE : It keeps establish date of publisher co.
• information : It holds brief information about publisher.
Constructors:
First constructor is used for creating empty objects and prevent to be sent null. The second constructor is general
constructor. It sends a sql statement to the database and fill up only the name attribute to send other classes because
other classes needs only Publisher’s id and name.
Getters and setters:
1
2
3
4
5
6
7
8
9
10
11
public String getName()
public String getCountry()
public List<Book> getBooks()
protected Serializable getId()
public void setId(Serializable id)
public String getInfo()
public String getEstablishDate()
public void setName(String name)
public void setCountry(String country)
public void setESTABLISH_DATE(String ESTABLISH_DATE)
public void setInformation(String information)
They are used for common tasks
byId( ) method:
2.1. Parts Implemented by Mehmet Hüseyin TEMEL
19
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
protected Model byId() {
String query = "SELECT NAME, COUNTRY, ESTABLISH_DATE, INFORMATION FROM PUBLISHER WHERE ID = ?";
try {
PreparedStatement statement = db.prepareStatement(query);
statement.setInt(1, ((Integer) this.getId()));
ResultSet results = statement.executeQuery();
while (results.next()) {
this.name = results.getString("NAME");
this.country = results.getString("COUNTRY");
this.ESTABLISH_DATE = results.getString("ESTABLISH_DATE");
this.information = results.getString("INFORMATION");
this.books = Book.byPublisher(this);
}
results.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
return this;
}
It overrides byId( ) method which is abstract method in Model class. It sends a sql statement to the database and
fill up all data of publisher. It takes all information from database results except books. For getting books of
publisher it uses byPublisher(publisher) method which belongs to Book class and it returns all books of publisher.
update( ) method:
1
public void update() {
2
3
4
5
6
7
8
9
10
11
int ID = ((Integer) this.getId());
String query = "UPDATE Publisher SET Name = ?, Country = ?, ESTABLISH_DATE = ?, INFORMATION = ? Wh
try {
PreparedStatement statement = this.db.prepareStatement(query);
statement.setString(1, this.name);
statement.setString(2, this.country);
statement.setString(3, this.ESTABLISH_DATE);
statement.setString(4, this.information);
statement.setInt(5, ID);
12
13
14
15
16
17
statement.executeUpdate();
}
}
delete( ) method:
1
2
3
4
5
6
7
8
9
10
11
public void delete() {
try {
String query = "DELETE FROM Publisher WHERE ID = ? ";
statement.setInt(1, ((Integer) this.id));
statement.close();
}
}
This method deletes a publisher from database which is chosen by user as sending sql statement to the database.
insert( ) method:
20
1
2
3
4
5
6
7
8
9
10
11
12
13
14
public void insert() {
try {
String query = "INSERT INTO PUBLISHER (NAME, COUNTRY, ESTABLISH_DATE, INFORMATION ) VALUES
statement.setString(2, this.country);
statement.setString(3, this.ESTABLISH_DATE);
statement.setString(4, this.information);
statement.close();
}
}
This method adds a new publisher to Publisher table according to user’s inputs by sending sql statement to
database.
all( ) method:
1
2
public static List<Publisher> all() {
List<Publisher> publishers = new LinkedList<Publisher>();
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
String query = "SELECT id FROM Publisher ORDER BY name";
try {
publishers.add(new Publisher(results.getInt("id")));
}
results.close();
statement.close();
}
return publishers;
}
This method is used for getting all publisher. Specially used in AdminPublisherPage because it lists all of publisher.
2 PublisherLink.java
This class inherits Link class and it is used for to go to the specific Publisher’s page. It provides us to pass the id
of clicked publisher to Publisher page.
Constructor:
1
2
3
public PublisherLink(String id, IModel model) {
super(id, model);
}
It takes id as string.
onClick( ) method:
1
2
3
4
5
@Override
public void onClick() {
Publisher publisher = (Publisher) getModel().getObject();
this.setResponsePage(new PublisherPage(publisher));
}
This method overrides When clicking the PublisherLink this method provides pass the publisher to the publisher
page. And then it redirects to this publisher’s page by seResponsePage().
21
3 PublisherPage.java
This class inherits BasePage and it is used for showing a specific publisher’s data in a page. It comes from
publisher link.
Whole of class:
public class PublisherPage extends BasePage {
public PublisherPage(Publisher publisher) {
this.add(new Label("name", publisher.getName()));
this.add(new Label("country", publisher.getCountry()));
this.add(new Label("establishdate", publisher.getEstablishDate()));
this.add(new Label("info",publisher.getInfo()));
this.add(new BooksPanel("booksPanel", publisher.getBooks()));
}
}
1
2
3
4
5
6
7
8
9
It has only a constructor. It fills in labels with publisher’s information by its getters. booksPanel shows its books.
It gets books as a list and it sends to booksPanel which is a class and lists books.
4 AdminPublisherPage.java
This class inherits AdminBasePage. It provides to edit, delete or add publisher to AdminUser.
• This page is only accessible by the admin. It is used to carry add/delete/update operations on the Publisher
Table.
• Top of the page is populated with the list of all publishers.
Constructor:
Top of the page is populated with the list of all publishers:
1
2
3
4
5
6
7
8
9
10
11
this.add(new ListView<Publisher>("publishersList", Publisher.all()) {
@Override
protected void populateItem(ListItem<Publisher> item) {
final Publisher publisher = item.getModelObject();
item.add(new PublisherLink("publisherLink", publisher));
item.add(new Link("editLink") {
@Override
setResponsePage(new AdminPublisherPage(publisher));
}
});
Delete Link for deleting:
1
2
3
4
5
6
7
item.add(new Link("deleteLink") {
@Override
publisher.delete();
setResponsePage(new AdminPublisherPage());
}
});
• Upon click, delete method of the publisher is called which deletes the selected publisher if there are no
constraint fails.
Text fields:
1
2
3
4
5
Form publisherForm = new Form("publisherForm", new CompoundPropertyModel(this.inputPublisher));
publisherForm.add(new RequiredTextField<String>("name"));
publisherForm.add(new RequiredTextField<String>("country"));
publisherForm.add(new TextField<String>("ESTABLISH_DATE"));
publisherForm.add(new TextField<String>("information"));
22
6
7
8
9
10
11
12
publisherForm.add(new Button("addButton") {
@Override
public void onSubmit() {
inputPublisher.insert();
}
});
• Admin can use those in order to add or to change attributes of a publisher
Adding button:
1
2
3
4
5
6
7
8
publisherForm.add(new Button("editButton") {
@Override
inputPublisher.update();
}
});
this.add(publisherForm);
5 Review.java
This class represents users’ reviews about books. It inherits Model class It is main class of publisher object.
Attributes:
1
2
3
4
5
private
private
private
private
private
Serializable id;
String description;
String datetime;
User user;
Book book;
• id : holds id of review which is generated automatically in review table. It is unique.
• description : it holds review of user about book
• datetime : gives us when any review was made
• user : holds owner of review
• book : keeps review belongs to which book
Constructors:
1
2
3
public Review() {
this.id = -1; // For creating empty objects
}
4
5
6
7
8
public Review(Serializable id) {
this.id = id;
this.byId();
}
constructor. It calls byId( ) method and byI() sends a sql statement to the database and fill up the attributes to send
ReviewPanel.
1
2
3
4
5
protected Serializable getId()
public void setDescription(String description)
public String getDescription()
public void setDatetime(String datetime)
public void setUser(User user)
23
6
7
8
public void setBook(Book book)
public String getDatetime()
public User getUser()
byId( ) method:
1
2
int x = ((Integer) this.getId());
String query = "SELECT ID, DESCRIPTION, BOOK_ID, USER_ID FROM REVIEW
try {
statement.setInt(1, x);
String description = results.getString("DESCRIPTION");
int bookId = results.getInt("BOOK_ID");
int userID = results.getInt("USER_ID");
3
4
5
6
7
8
9
10
11
12
WHERE ID= ?";
13
book = new Book(bookId);
user= new User(userID);
this.setDescription(description);
14
15
16
}
}
17
18
19
20
21
return this;
22
23
}
fill up all data of review. It takes all information from database results.
update( ) method:
1
2
String query = "UPDATE Review SET DESCRIPTION = ?, BOOK_ID = ?, USER_ID = ?, DATE = ? Where ID
try {
statement.setString(1, this.description);
statement.setInt(2, (Integer) this.book.getId());
statement.setString(3, (this.datetime));
statement.setInt(4, (Integer) this.user.getId());
}
3
4
5
6
7
8
9
10
11
12
13
14
15
}
delete( ) method:
1
2
3
4
5
6
7
8
9
10
11
try {
String query = "DELETE FROM Review WHERE ID = ? ";
statement.close();
}
}
24
insert( ) method:
1
2
3
4
5
6
7
8
9
10
11
12
13
try {
String query = "INSERT INTO Review (DESCRIPTION, USER_ID, BOOK_ID, DATE) VALUES(?, ?, ?, d
statement.setString(1, this.description);
statement.setInt(2, (Integer) this.user.getId());
statement.close();
}
}
This method adds a new review to Review table. When user clicks “make review” button this method is called
and sends sql statements to the database. It takes user’s information, review description, book information and
datetime from system.
search( ) method:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
public static List<Review> search(int userId) {
List<Review> reviews = new LinkedList<Review>();
String query = "SELECT id, user_id, book_id, description, date FROM Review WHERE user_id= ?";
try {
statement.setInt(1, userId);
Review review = new Review(results.getInt("id"));
review.setUser(new User(results.getInt("user_id")));
review.setBook(new Book(results.getInt("book_id")));
review.setDatetime(results.getString("date"));
review.setDescription(results.getString("description"));
reviews.add(review);
}
results.close();
statement.close();
}
return reviews;
22
23
}
It provides searching reviews by a user’s id. It takes id and sends sql statement and gets the reviews.
byBook( ) method:
1
public static List<Review> byBook(Book book) {
2
3
List<Review> reviews = new LinkedList<Review>();
4
5
6
7
int x = ((Integer) book.getId());
String query = "SELECT ID, DESCRIPTION, BOOK_ID, USER_ID, DATE FROM REVIEW
+ "ORDER BY DATE ASC";
WHERE BOOK_ID= ?
8
9
10
11
12
13
14
15
try {
Serializable revID = (Serializable) results.getInt("ID");
String description = results.getString("DESCRIPTION");
25
String datetime = results.getString("DATE");
16
17
Review review = new Review(revID);
review.setDescription(description);
review.setBook(new Book(results.getInt("book_id")));
review.setUser(new User(results.getInt("user_id")));
review.setDatetime(datetime);
reviews.add(review);
18
19
20
21
22
23
}
results.close();
statement.close();
}
return reviews;
24
25
26
27
28
29
30
31
}
It gets reviews of a book and return list of a book to send to review panel.
6 ReviewPanel.java
Review Panel includes all of the reviews of book. It inherits Panel class.
Attributes:
1
2
3
private String description;
private Book book;
private User user;
Adding review form:
1
2
3
4
5
6
7
8
Form<ReviewPanel> addReviewForm = new Form<ReviewPanel>("addReviewForm",
new CompoundPropertyModel<ReviewPanel>(this));
addReviewForm.add(new TextField<String>("description"));
addReviewForm.add(new Button("submitButton") {
@Override
user = User.getCurrentUser();
book.byId();
9
Review review = new Review();
review.setDescription(description);
review.setUser(user);
review.setBook(book);
review.insert();
10
11
12
13
14
15
setResponsePage(new BookPage(book));
16
}
17
18
});
• User can add review by filling up the textbox and clicking addbutton.
Filling up reviews list:
1
2
MetaDataRoleAuthorizationStrategy.authorize(addReviewForm, RENDER, Roles.USER);
this.add(addReviewForm);
3
4
5
6
7
8
9
this.add(new ListView<Review>("reviewsList", reviews) {
@Override
protected void populateItem(ListItem<Review> item) {
Review review = (Review) item.getModelObject();
item.add(new Label("username", review.getUser().getName()));
item.add(new Label("description", review.getDescription()));
26
item.add(new Label("datetime", review.getDatetime()));
10
}
11
12
});
• It list all of reviews about book in book page.
7 Price.java
Attributes:
1
2
3
4
private
private
private
private
Serializable id;
double cost;
Book book;
Site site;
• id : keeps the id of price. It is generated randomly in database and it is unique.
• cost: keeps the price of book.
• book : owner book of that price
• site : It keeps seller site of that book with that price.
Constructors:
1
2
3
public Price() {
}
4
5
6
7
8
public Price(Serializable id) {
this.id = id;
this.byId();
}
constructor. It calls byId( ) method and it sends a sql statement to the database and fill up attributes to send other
classes.
1
2
3
4
5
6
public
public
public
public
public
public
Book getBook()
Site getSite()
void setBook(Book book)
void setCost(double cost)
double getCost()
void setSite(Site site)
byId( )method:
1
2
@Override
3
4
5
6
7
8
9
10
11
12
13
int x = ((Integer) this.getId());
String query = "SELECT ID, BOOK_ID,COST, SITE_ID FROM PRICE WHERE ID= ?";
try {
int bookId = results.getInt("BOOK_ID");
int siteId = results.getInt("SITE_ID");
double cost = results.getDouble("COST");
14
15
book = new Book(bookId);
27
site = new Site(siteId);
this.cost = cost;
16
17
}
results.close();
statement.close();
}
18
19
20
21
22
23
24
return this;
25
26
}
fill up all data of price. It takes all information from database results.It returns itself.
byBook( ) method:
1
2
public static List<Price> byBook(Book book) {
List<Price> prices = new LinkedList<Price>();
3
int x = ((Integer) book.getId());
String query = "SELECT ID, COST, SITE_ID FROM PRICE WHERE BOOK_ID = ? ORDER BY COST ASC";
4
5
6
try {
int ID = results.getInt("ID");
double cost = results.getDouble("COST");
int siteId = results.getInt("SITE_ID");
7
8
9
10
11
12
13
14
15
Price price = new Price(ID);
price.setCost(cost);
price.setSite(new Site(siteId));
prices.add(price);
16
17
18
19
}
results.close();
statement.close();
}
return prices;
20
21
22
23
24
25
26
27
}
• It gets and returns a list of prices for a book. It is used in BookPage.
update( ) method:
1
2
String query = "UPDATE Price " +
"SET " +
"COST = ?," +
"BOOK_ID = ?," +
"SITE_ID = ?" +
" Where ID = ? ";
try {
statement.setDouble(1, this.cost);
statement.setInt(3, (Integer) this.site.getId());
statement.setInt(4, ID);
3
4
5
6
7
8
9
10
11
12
13
14
15
28
}
16
17
18
19
20
}
delete( ) method:
1
2
3
4
5
6
7
8
9
10
11
try {
String query = "DELETE FROM Price WHERE ID = ? ";
statement.close();
}
}
insert( ) method:
1
2
try {
3
String query = "INSERT INTO Price (COST, BOOK_ID, Site_id) "
+ "VALUES(?, ?, ?)";
4
5
6
7
statement.setDouble(1, this.cost);
statement.setInt(3, (Integer) this.site.getId());
statement.close();
}
8
9
10
11
12
13
14
15
16
17
}
search( ) method:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
public static List<Price> search(double costt) {
String query = "SELECT id, cost, book_id, site_id FROM Price WHERE cost <= ?";
try {
statement.setDouble(1, costt);
Price price = new Price(results.getInt("id"));
price.setSite(new Site(results.getInt("site_id")));
price.setBook(new Book(results.getInt("book_id")));
price.setCost(results.getDouble("cost"));
prices.add(price);
}
results.close();
statement.close();
}
return prices;
21
22
}
29
all( ) method:
public static List<Price> all() {
1
2
3
String query = "SELECT ID FROM PRICE ORDER BY COST ASC";
try {
prices.add(new Price(results.getInt("ID")));
}
results.close();
statement.close();
}
return prices;
4
5
6
7
8
9
10
11
12
13
14
15
16
}
17
It used for listing all prices in AdminPricePage
8 PricesPanel.java
Whole Class:
1
2
3
4
5
6
7
8
9
this.add(new ListView<Price>("pricesList", prices) {
@Override
protected void populateItem(ListItem<Price> item) {
Price price = (Price)item.getModelObject();
item.add(new Label("name", price.getSite().getName()));
item.add(new Label("address", price.getSite().getAddress()));
item.add(new Label("cost", price.getCost()));
}
});
• It lists all of prices of book with their sites.
9 AdminPricePage.java
This class inherits AdminBasePage. It provides to edit, delete or add price to AdminUser.
• This page is only accessible by the admin. It is used to carry add/delete/update operations on the Publisher
Table.
• Top of the page is populated with the list of all books, the corresponding webpage and the price.
Constructor:
public AdminPricePage() {
this(new Price());
1
2
3
}
Top of the page is populated with the list of all books, the corresponding webpage and the price:
1
2
3
4
5
6
7
8
this.add(new ListView<Price>("pricesList", Price.all()) {
@Override
protected void populateItem(ListItem<Price> item) {
final Price price = item.getModelObject();
item.add(new BookLink("bookLink", price.getBook()));
item.add(new Label("siteName", price.getSite().getName()));
item.add(new Label("costt", price.getCost()));
}
30
Delete Link for deleting:
1
2
3
4
5
6
7
8
9
@Override
price.delete();
setResponsePage(new AdminPricePage());
}
});
}
});
• Upon click, delete method of the price is called which deletes the selected price if there are no constraint
fails.
Text Fields and Dropdownlists:
1
2
priceForm.add(new DropDownChoice<Book>("book", Book.all(),
new ChoiceRenderer<Book>("name", "id")));
3
priceForm.add(new DropDownChoice<Site>("site", Site.all(),
new ChoiceRenderer<Site>("name", "id")));
4
5
6
priceForm.add(new RequiredTextField<String>("cost"));
7
• Admin can use those in order to add or to change attributes of a price
Adding button:
1
2
3
4
5
6
7
priceForm.add(new Button("addButton") {
@Override
inputPrice.insert();
setResponsePage(new AdminPricePage());
}
});
•
2.2 Parts Implemented by Taha Emre
2.2.1 1 Database Design
1.1 Tables
1.1.1 Prize Table
• Prize table was implemented for giving away a prize to an author in a specific year
Name
id
prize_id
author_id
year
Type
INTEGER
INTEGER
INTEGER
varchar(4)
Not Null
1
1
1
0
Primary K.
1
0
0
0
AUTOINCREMENT
1
0
0
0
Foreign K.
0
1
1
0
Information
• Foreign keys prize_id and author_id have cascade on delete and update
• It has 1:n relation with author and prizeDetail tables
31
Figure 2.2: E/R diagram for tables by Taha Emre
The structure of the table is like below:
DROP TABLE IF EXISTS "Prize";
CREATE TABLE "Prize" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"prize_id" INTEGER NOT NULL,
"author_id" INTEGER NOT NULL,
"year" varchar(4),
FOREIGN KEY(prize_id) REFERENCES PrizeDetail(id) on delete cascade on update cascade,
FOREIGN KEY(author_id) REFERENCES Author(id) on delete cascade on update cascade
);
1.1.2 Prize Detail Table
• PrizeDetail table was implemented for storing general information about a prize
Name
id
name
definition
Type
INTEGER
varchar(32)
TEXT
Not Null
1
0
0
Primary K.
1
0
0
AUTOINCREMENT
1
0
0
Foreign K.
0
0
0
Information
• It does not require any foreign key since it stores independent data
• It has 1:n relation with prize table
DROP TABLE IF EXISTS "PrizeDetail";
CREATE TABLE "PrizeDetail" (
"name" VARCHAR(32),
32
"definition" TEXT
);
1.1.3 Author Table
• Author table was implemented for storing general information about an author
Name
id
name
year
nationality
deathYear
Type
INTEGER
varchar(32)
INTEGER
VARCHAR(32)
VARCHAR(4)
Not Null
1
1
0
0
0
Primary K.
1
0
0
0
0
AUTOINCREMENT
1
0
0
0
0
Foreign K.
0
0
0
0
0
Information
• It has 1:n relation with book and prize tables
• The variable name must be unique
• The variable deathYear has ‘?’ as default value
DROP TABLE IF EXISTS "Author";
CREATE TABLE "Author" (
"name" VARCHAR(32) UNIQUE NOT NULL,
year INTEGER,
nationality VARCHAR(32),
deathYear varchar(4) DEFAULT "?"
);
2.2.2 2 Software Design
2.1 Prize.java
This page handles details of a prize (Prize_detail table). Also it returns a list of prizes by a specific author form
prize table. It extends model page. So that it can be serializable and take less amount of storage.
Attributes:
private
private
private
private
private
String name;
Serializable id;
List<Person> authors;
String year;
String definition;
• name: keeps the name of prize.
• id: keeps the id of prize.
• authors: keeps the list of authors for a specific prize.
• year: It is for prize table. It keeps the which year the prize given.
• definition: It keeps definition of a prize.
Constructor:
33
public Prize(Serializable id) {
this.id = id;
String query = "SELECT name , definition FROM prizeDetail WHERE id=?";
try {
this.name = results.getString("name");
this.definition = results.getString("definition");
}
results.close();
statement.close();
}
}
Constructor fills an object with an unique id by making query to prizedetail table with its unique id.
Getters and Setters:
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public List<Person> getAuthors() {
return authors;
}
public String getYear() {
return year;
}
public void setYear(String year) {
this.year = year;
}
public void setId(Serializable id) {
this.id = id;
}
protected Serializable getId() {
return id;
}
public String getDefinition() {
return definition;
}
public void setDefinition(String definition) {
this.definition = definition;
}
Basic getters and setters.
search:
public static List<Prize> search(String searchname) {
List<Prize> prizes = new LinkedList<Prize>();
String query = "SELECT id FROM prizeDetail WHERE name LIKE ?";
try {
34
statement.setString(1, searchname);
Prize prize = new Prize(results.getInt("id"));
prizes.add(prize);
}
results.close();
statement.close();
}
return prizes;
}
Searches for prize names which look like the searched name, if found one, creates new prize object with the found
id then adds it to the list, returns their list.
delete:
public void deletePrize(){
try {
String query = "DELETE FROM PrizeDetail WHERE id=?";
statement.setInt(1, (Integer) this.id);
}
}
Deletes a prize from prizeDetail table by finding it with its unique id.
update:
public void updatePrize(){
try {
String query = "UPDATE PrizeDetail SET name = ?, definition = ? "
+ "WHERE id = ?";
statement.setInt(3, (Integer) this.getId());
statement.setString(2, this.definition);
statement.close();
}
}
Updates a prize which has the desired attributes already been changed, by finding it with its unique id.
add:
public void addPrize(){
try {
String query = "INSERT INTO PrizeDetail (name, definition) "
+ "VALUES(?, ?)";
statement.setString(2, this.definition);
statement.close();
35
}
}
Adds a prize which is already created and filled as an object, to prizeDetail table.
allPrize() method:
public static List<Prize> allPrize(){
List<Prize> prizes = new LinkedList<Prize>();
String query = "SELECT id FROM PrizeDetail ORDER BY name";
try {
prizes.add(new Prize(results.getInt("id")));
}
results.close();
statement.close();
}
return prizes;
}
It returns all the added prizes form prizeDetail table.
byId() method:
String query = "SELECT name, definition FROM PrizeDetail WHERE id = ?";
try {
while (results.next()) { // should be executed only for once.
this.definition = results.getString("definition");
this.authors = Author.byPrize(this);
}
results.close();
statement.close();
}
return this;
}
It overrides byId( ) method which is abstract method in Model class. It sends a sql statement to the database
and fill up all data of a Prize object. It is invoked when link of a prize i clicked. since all the class inherits
LoadableDetachableModel only the serializable attributes are saved which is just id attribute.
2.2 PrizeLink.java
It extends Link class. It is used for handling link of a specific prize.
Constructor:
public class PrizeLink extends Link {
public PrizeLink(String id, IModel model) {
super(id, model);
}
36
@Override
Prize prize = (Prize) getModel().getObject();
this.setResponsePage(new PrizePage(prize));
}
public void onComponentTagBody(MarkupStream markupStream, ComponentTag openTag) {
replaceComponentTagBody(markupStream, openTag, ((Prize) getModel().getObject()).getName
}
}
2.3 PrizePage.java
It extends BasePage class. It is used for handling page of a specific prize
Constructor:
public class PrizePage extends BasePage {
public PrizePage(Prize prize) {
this.add(new Label("name", prize.getName()));
this.add(new Label("definition", prize.getDefinition()));
this.add(new PersonsPanel("personsPanel", prize.getAuthors()));
}
}
It includes name and definition of the prize. And also it includes panel for the authors who has won that prize.
2.4 PrizePanel.java
It extends Panel class. It is used as an information panel for prizes in a different page.
Constructor:
public PrizesPanel(String id, List<Prize> prizes) {
super(id);
this.add(new ListView<Prize>("prizesList", prizes) {
@Override
protected void populateItem(ListItem<Prize> item) {
Prize prize = item.getModelObject();
item.add(new Label("prizeYear", prize.getYear()));
item.add(new PrizeLink("prizeLink", prize));
}
});
}
2.5 PrizeWinner.java
It extends Model class. So that it can be serializable and take less amount of storage. It is used only for administration of a given prize to an author in a year.
Attributes:
private
private
private
private
Serializable id;
Author author;
String year;
Prize prize;
Constructor:
37
public PrizeWinner(int id) {
this.id = id;
String query = "SELECT year , author_id, prize_id FROM Prize WHERE id=?";
try {
this.year = results.getString("year");
this.author = new Author(results.getInt("author_id"));
this.prize = new Prize(results.getInt("prize_id"));
}
results.close();
statement.close();
}
}
It makes a query to database and fills the object with necessary information with matching id.
return id;
}
public Prize getPrize(){
return prize;
}
public Author getAuthor(){
return author;
}
public String getYear(){return this.year;}
delete():
try {
String query = "DELETE FROM Prize WHERE id=?";
}
}
Deletes a given prize from prize table by finding it with its unique id.
update():
try {
String query = "UPDATE Prize SET author_id = ?, prize_id = ?, year = ? "
+ "WHERE id = ?";
statement.setInt(4, (Integer) this.getId());
statement.setInt(1, (Integer) this.author.getId());
statement.setInt(2, (Integer) this.prize.getId());
statement.setString(3, this.year);
statement.close();
38
}
}
Updates a given prize which has the desired attributes already been changed, by finding it with its unique id.
insert():
try {
String query = "INSERT INTO Prize (author_id, prize_id, year) "
+ "VALUES(?, ?, ?)";
statement.setInt(2, (Integer) this.prize.getId());
statement.setString(3, this.year);
statement.close();
}
}
Adds a given prize which is already created and filled as an object, to prize table.
2.6 Person.java
ıt extends Model class. It is a general class for Author, Editor, Translator classes:
private Serializable id;
@Override
return id;
}
@Override
return null;
}
public abstract String getName();
public abstract void add();
public abstract void update();
public abstract void delete();
public abstract String getDeathYear();
public abstract int getYear();
public abstract String getNationality();
public abstract List<Book> getBooks();
It includes general functions for child classes to override the functions.
39
2.7 PersonLink.java
A simple link that is used in panels and lists that contain person types in order to refer to a PersonPage:
public PersonLink(String id, IModel model) {
super(id, model);
}
@Override
Person person = (Person) getModel().getObject();
this.setResponsePage(new PersonPage(person));
}
public void onComponentTagBody(MarkupStream markupStream,
ComponentTag openTag) {
replaceComponentTagBody(markupStream, openTag, ((Person) getModel()
.getObject()).getName());
}
2.8 Author.java
This page handles details of an author (author table). It extends person class. So that it can be serializable and
take less amount of storage.
Attributes:
private
private
private
private
private
private
private
Serializable id;
String name;
int year;
String nationality;
String deathYear;
List<Prize> prizes;
List<Book> books;
Constructor:
public Author(Serializable id) {
this.id = id;
try {
String query = "SELECT name FROM Author WHERE ID=?";
}
results.close();
statement.close();
throw new UnsupportedOperationException(e.getMessage());
}
}
public String getNationality() {
return nationality;
}
return name;
}
public int getYear() {
40
return year;
}
public String getDeathYear() {
return deathYear;
}
public List<Book> getBooks() {
return books;
}
public List<Prize> getPrizes() {
return prizes;
}
this.name = name;
}
this.id = id;
}
public void setYear(int year) {
this.year = year;
}
public void setNationality(String nationality) {
this.nationality = nationality;
}
public void setDeathYear(String deathYear) {
this.deathYear = deathYear;
}
return id;
}
Table operations:
@Override
public void add() {
try {
String query = "INSERT INTO Author (NAME, year, nationality, deathYear) "
+ "VALUES(?, ?, ?, ?)";
if (this.year > 0)
statement.setInt(2, this.year);
else
statement.setNull(2, java.sql.Types.INTEGER);
statement.setString(3, this.nationality);
statement.setString(4, this.deathYear);
statement.close();
}
}
@Override
try {
String query = "DELETE FROM Author WHERE ID=?";
41
}
}
@Override
try {
String query = "UPDATE Author SET name=?, year=?, nationality=?, deathYear=? WHERE id
statement.close();
}
}
public static List<Person> search(String name) {
List<Person> authors = new LinkedList<Person>();
String query = "SELECT id FROM Author WHERE Name LIKE ?";
try {
statement.setString(1, "%" + name + "%");
authors.add(new Author(results.getInt("id")));
}
results.close();
statement.close();
}
return authors;
}
public static List<Person> all() {
String query = "SELECT id FROM Author ORDER BY name";
try {
authors.add((Person) new Author(results.getInt("id")));
}
results.close();
statement.close();
}
return authors;
}
byPrize():
42
public static List<Person> byPrize(Prize prize) {
String query = "SELECT author_id FROM Prize WHERE prize_id = ?";
try {
statement.setInt(1, ((Integer) prize.getId()));
authors.add((Person) new Author(results.getInt("author_id")));
}
results.close();
statement.close();
}
return authors;
}
• It gives every author who won that prize, and with found id, created author objects are added to an author
list.
2.9 PersonPage.java
It extends BasePage class. It is used for handling page of a specific person:
public PersonPage(Person person) {
this.add(new Label("name", person.getName()));
this.add(new Label("year",
Integer.toString(person.getYear()) + " - " +person.getDeathYe
this.add(new Label("nationality", person.getNationality()));
List<Book> booksOfPerson = person.getBooks();
this.add(new BooksPanel("booksPanel", booksOfPerson));
List<Prize> prizesOfPerson = new LinkedList<Prize>();
if (person instanceof Author) {
Author author = (Author) person;
prizesOfPerson = author.getPrizes();
}
this.add(new PrizesPanel("prizesPanel", prizesOfPerson));
}
It includes name, year and nationality of the person. Also it has a book list which holds the list of books which
the person is related to. Also it has a prize list which holds the list of prizes which only the author is related to.
2.10 PersonsPage.java
It extends BasePage class. It is used for displaying result of a search in persons. It displays them as panels:
public PersonsPage(List<Person> persons) {
this.add(new PersonsPanel("personsPanel", persons));
}
2.11 PersonsPanel.java
It extends Panel class. It consists of person link:
43
public PersonsPanel(String id, List<Person> persons) {
super(id);
this.add(new ListView<Person>("personsList", persons) {
@Override
protected void populateItem(ListItem<Person> item) {
Person person = item.getModelObject();
item.add(new PersonLink("personLink", person));
}
});
}
2.12 AdminAuthorPage.java
It inherits AdminBasePage class. It is used for updating, deleting and editing an author from author table:
Author inputAuthor;
public AdminAuthorPage() {
this(new Author());
}
public AdminAuthorPage(Author author) {
this.inputAuthor = author;
this.add(new ListView<Person>("authorsList", Author.all()) {
@Override
final Person author = item.getModelObject();
item.add(new PersonLink("personLink", author));
@Override
setResponsePage(new AdminAuthorPage((Author) author));
}
});
@Override
author.delete();
setResponsePage(new AdminAuthorPage());
}
});
}
});
Form authorForm = new Form("authorForm", new CompoundPropertyModel(
this.inputAuthor));
authorForm.add(new RequiredTextField<String>("name"));
authorForm.add(new RequiredTextField<String>("year"));
authorForm.add(new RequiredTextField<String>("deathYear"));
authorForm.add(new RequiredTextField<String>("nationality"));
authorForm.add(new Button("addButton") {
@Override
inputAuthor.add();
}
});
authorForm.add(new Button("editButton") {
44
@Override
inputAuthor.update();
}
});
this.add(authorForm);
}
First there is a list of already added authors. If we click delete link near one of them. It performs delete() operation
for that author. If the edit link clicked, that author is sent to the authorForm so that the labels and dropdown lists
are automatically filled. After editing desired parts clicking edit button will update the object. For adding filing
the parts and clicking add is enough.
2.13 AdminPrizePage.java
It inherits AdminBasePage class. It is used for updating, deleting and editing a prize from prizeDetail table:
Prize inputPrize;
public AdminPrizePage() {
this(new Prize());
}
public AdminPrizePage(Prize prize) {
this.inputPrize = prize;
this.add(new ListView<Prize>("prizeList", Prize.allPrize()) {
@Override
protected void populateItem(ListItem<Prize> item) {
final Prize prize = item.getModelObject();
item.add(new PrizeLink("prizeLink", prize));
@Override
setResponsePage(new AdminPrizePage(prize));
}
});
@Override
prize.deletePrize();
setResponsePage(new AdminPrizePage());
}
});
}
});
Form prizeForm = new Form("prizeForm", new CompoundPropertyModel(this.inputPrize));
prizeForm.add(new RequiredTextField<String>("name"));
prizeForm.add(new TextField<String>("definition"));
prizeForm.add(new Button("addButton") {
@Override
inputPrize.addPrize();
}
});
prizeForm.add(new Button("editButton") {
@Override
inputPrize.updatePrize();
45
}
});
this.add(prizeForm);
}
First there is a list of already added prizes. If we click delete link near one of them. It performs delete() operation
for that prize. If the edit link clicked, that prize is sent to the prizeForm so that the labels and dropdown lists are
automatically filled. After editing desired parts clicking edit button will update the object. For adding filing the
parts and clicking add is enough.
2.14 AdminPrizeWinnersPage.java
It inherits AdminBasePage class. It is used for updating, deleting and editing a prize from prize table:
PrizeWinner inputWinner;
public AdminPrizeWinnersPage(){this(new PrizeWinner());}
public AdminPrizeWinnersPage(PrizeWinner prizeWinner){
this.inputWinner = prizeWinner;
this.add(new ListView<PrizeWinner>("prizeWinnerList",PrizeWinner.all()) {
@Override
protected void populateItem(ListItem<PrizeWinner> item){
final PrizeWinner prizeWinner = item.getModelObject();
item.add(new PrizeLink("prizeLink",prizeWinner.getPrize()));
item.add(new Label("year", prizeWinner.getYear()));
item.add(new PersonLink("authorName", prizeWinner.getAuthor()));
@Override
setResponsePage(new AdminPrizeWinnersPage(prizeWinner));
}
});
@Override
prizeWinner.delete();
setResponsePage(new AdminPrizeWinnersPage());
}
});
}
});
Form prizeWinnerForm = new Form("prizeWinnerForm", new CompoundPropertyModel(
this.inputWinner));
prizeWinnerForm.add(new DropDownChoice<Person>("author", Author.all(),
new ChoiceRenderer<Person>("name", "id")));
prizeWinnerForm.add(new DropDownChoice<Prize>("prize", Prize.allPrize(),
new ChoiceRenderer<Prize>("name", "id")));
prizeWinnerForm.add(new RequiredTextField<String>("year"));
prizeWinnerForm.add(new Button("addButton") {
@Override
46
inputWinner.insert();
}
});
prizeWinnerForm.add(new Button("editButton") {
@Override
inputWinner.update();
}
});
this.add(prizeWinnerForm);
}
First there is a list of already given prizes. If we click delete link near one of them. It performs delete() operation
for that given prize. If the edit link clicked, that given prize is sent to the prizeWinersForm so that the labels and
dropdown lists are automatically filled. After editing desired parts clicking edit button will update the object. For
adding filing the parts and clicking add is enough.
2.3 Parts Implemented by Mustafa Safa Özdayı
This part explains how Book, Genre and Site tables are structured and what operations can run on them.
1 Table Structures
• E/R Diagram of the BKDB
1.1 Book Table
• Attributes of the Book Table.
Name
Type
id
name
INTEGER
VARCHAR
VARCHAR
INTEGER
INTEGER
INTEGER
ISBN
author_id
editor_id
translator_id
publisher_id
type
language
date
format
summary
Not
Null
1
1
Primary
K.
1
0
AutoIncrement
1
0
Foreign
K.
0
0
Unique
1
1
Default
Value
None
None
0
0
0
0
0
None
0
0
0
0
0
0
0
0
0
1
1
1
0
0
0
None
None
None
INTEGER
0
0
0
1
0
None
VARCHAR
VARCHAR
VARCHAR
VARCHAR
TEXT
0
0
0
0
0
None
0
0
0
0
0
None
0
0
0
0
0
None
0
0
0
0
0
None
0
0
0
0
0
None
47
Figure 2.3: Book, Genre and Site tables are highlighted with red rectangles
48
• Format indicates how the book is “printed”. Such as paperback, hardcover, digital etc.
• Type is simply the type of the book. Such as novel, story, tale etc. (not the same thing as genre)
Relations of the Book Table:
• n:1 relation with Author
• n:1 relation with Editor
• n:1 relation with Translator
• n:1 relation with Publisher
SQL Statement that initializes the Book Table:
DROP TABLE IF EXISTS Book;
CREATE TABLE Book (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(64) UNIQUE NOT NULL,
ISBN VARCHAR,
author_id INTEGER,
editor_id INTEGER,
translator_id INTEGER,
publisher_id INTEGER,
"type" VARCHAR(32),
language VARCHAR(32),
"date" VARCHAR(32),
format VARCHAR(32),
summary TEXT,
FOREIGN KEY(author_id) REFERENCES Author(ID)
ON DELETE SET NULL ON UPDATE CASCADE,
FOREIGN KEY(editor_id) REFERENCES Editor(ID)
ON DELETE SET NULL ON UPDATE CASCADE,
FOREIGN KEY(translator_id) REFERENCES Translator(ID)
ON DELETE SET NULL ON UPDATE CASCADE
FOREIGN KEY(publisher_id) REFERENCES Publisher(ID)
ON DELETE SET NULL ON UPDATE CASCADE
);
• As can be seen, all the FKs of the book table has REFERENCES ... ON DELETE SET NULL ON UPDATE
CASCADE which means that the book is kept in DB when a FK is deleted. So, DB allows a book with an
unknown author, translator etc.
49
1.2 Genre Table
• Attributes of the Genre Table.
Name
id
name
book_id
Type
INTEGER
VARCHAR
INTEGER
Not Null
1
1
0
Primary K.
0
0
0
AutoIncrement
0
0
0
Foreign K.
0
0
1
Unique
0
1*
1*
Default Value
None
None
None
• As can be seen, id of the genre table is not unique becouse different books might have the same genre. So,in
order to refer to a specific row in the table, an unique pair is used which consists of name and book_id. Note
that neither name nor book_id is unique by themselves.
• Since id of the genre table is not unique, the following method is created in order to supply id when entering
a new value to the table:
public static Integer getMatchingID(String genreName) {
Integer id = 0;
// check if that genre exists
String query = "SELECT MIN(ID) as MatchID FROM Genre WHERE Name = ?";
try {
statement.setString(1, genreName);
ResultSet result = statement.executeQuery();
while (result.next()) {
id = result.getInt("MatchID");
}
result.close();
statement.close();
}
// if not, create a new ID
if (id == 0) {
String query1 = "SELECT MAX(ID) AS CurMax FROM Genre";
try {
PreparedStatement statement = db.prepareStatement(query1);
id = result.getInt("CurMax") + 1;
}
result.close();
statement.close();
}
}
return id;
}
• Genres with the same name should have the same id. So, if an existing genre is added to a new book, we set
the id of this pair as the id of a previous pair that have the same genre name. Otherwise, id will be the max
value.
Relations of the Table:
• n:n relation with Book
SQL Statement that initializes the Genre Table:
DROP TABLE IF EXISTS "Genre";
CREATE TABLE Genre (
id INTEGER NOT NULL,
50
name VARCHAR(32) NOT NULL,
FOREIGN KEY(book_id) REFERENCES book(ID) ON DELETE CASCADE ON UPDATE CASCADE
UNIQUE(name, book_id)
);
• The FK of the genre table has REFERENCES book(ID) ON DELETE CASCADE ON UPDATE CASCADE.
So, whenever a book is deleted all of its genres will be deleted too.
1.3 Site Table
• Attributes of the Site Table.
Name
id
name
address
Type
INTEGER
VARCHAR
VARCHAR
Not Null
1
1
0
Primary K.
1
0
0
AutoIncrement
1
0
0
Foreign K.
0
0
0
Unique
0
1
1
Default Value
None
None
None
• A simple table that doesn’t have any FK.
SQL Statement that initializes the Site Table:
DROP TABLE IF EXISTS "Site";
CREATE TABLE "Site" (
"adress" VARCHAR(64) UNIQUE
);
2 Table Operations (Add/Delete/Update/Search)
2.1 Book Table Operations
• Add:
1
2
3
4
5
6
7
8
9
10
public void add() {
try {
String query = "INSERT INTO Book(name, ISBN, type, language, format, summary, date,"
+ "author_id, editor_id, translator_id, publisher_id "
+ ") VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
statement.setString(2, this.ISBN);
statement.setString(3, this.type);
statement.setString(4, this.language);
51
statement.setString(5, this.format);
statement.setString(6, this.summary);
statement.setString(7, this.date);
11
12
13
14
// setInt takes primitive type of int(that is, int is not an object.
// Therefore, null check is necessary.
15
16
17
if (this.author != null)
else
18
19
20
21
22
if (this.editor != null)
statement.setInt(9, (Integer) this.editor.getId());
else
23
24
25
26
27
if (this.translator != null)
statement.setInt(10, (Integer) this.translator.getId());
else
28
29
30
31
32
if (this.publisher != null)
statement.setInt(11, (Integer) this.publisher.getId());
else
33
34
35
36
37
statement.close();
38
39
40
}
41
42
43
44
}
Adds a book object to the DB.
• Delete:
1
2
3
4
5
6
7
8
9
10
11
try {
String query = "DELETE FROM book WHERE id = ? ";
statement.close();
}
}
Deletes a book object from the DB.
• Update:
1
2
try {
String query = "UPDATE Book SET Name = ?, ISBN = ?, Type = ?,"
+ "Language = ?, Format = ?, Summary = ?, Date = ?, "
+ "author_id = ?, editor_id = ?, translator_id = ?,"
+ "publisher_id = ? WHERE ID = ?";
3
4
5
6
7
8
9
52
statement.setString(2,
10
11
12
13
14
15
this.ISBN);
this.type);
this.language);
this.format);
this.summary);
this.date);
16
// setInt takes primitive type of int(that is, int is not an object.
// Therefore, null check is necessary.
17
18
19
if (this.author != null)
else
20
21
22
23
24
if (this.editor != null)
statement.setInt(9, (Integer) this.editor.getId());
else
25
26
27
28
29
if (this.translator != null)
statement.setInt(10, (Integer) this.translator.getId());
else
30
31
32
33
34
if (this.publisher != null)
statement.setInt(11, (Integer) this.publisher.getId());
else
35
36
37
38
39
statement.close();
}
40
41
42
43
44
45
46
47
}
Updates a book object that already exists in the DB.
• Search:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
public static List<Book> search(String name) {
List<Book> books = new LinkedList<Book>();
String query = "SELECT id, author_id FROM Book WHERE Name LIKE ?";
try {
Book book = new Book(results.getInt("id"));
book.setAuthor(new Author(results.getInt("author_id")));
book.setRate(Rate.byBook(book));
books.add(book);
}
results.close();
statement.close();
}
return books;
20
53
21
}
Searches a book object in DB by its name using %LIKE% and returns the matches in a list.
2.2 Genre Table Operations
• Add:
public void add() {
try {
String query = "INSERT INTO Genre (ID, NAME, book_id) VALUES(?, ?, ?)";
statement.setInt(1, (Integer) Genre.getMatchingID(this.name));
if (this.book != null)
else
1
2
3
4
5
6
7
8
9
10
11
statement.close();
12
13
14
}
15
16
17
18
19
}
Adds a genre object to the DB.
• Delete:
1
2
3
4
5
6
7
8
try {
String query = "DELETE FROM Genre WHERE ID = ? AND book_id = ? ";
statement.close();
9
}
10
11
12
13
}
Deletes a genre object from the DB.
• Update:
1
2
3
4
5
6
7
8
9
10
11
12
try {
String query = "UPDATE GENRE SET NAME = ?, ID = ? WHERE (ID = ? AND book_id = ?) AND (SEL
+ "WHERE NAME = ?) > 0 ";
statement.setInt(2, getMatchingID(this.name));
statement.close();
13
54
}
14
15
16
17
18
}
Updates a genre object that already exists in the DB.
• Search:
1
2
3
4
5
6
7
8
9
public static List<Genre> search(String name) {
List<Genre> genres = new LinkedList<Genre>();
String query = "SELECT DISTINCT ID FROM Genre WHERE NAME LIKE ?";
try {
genres.add(new Genre(results.getInt("ID")));
10
}
results.close();
statement.close();
}
11
12
13
14
15
16
17
return genres;
18
19
20
}
Searches a genre object in DB by its name using %LIKE% and returns the matches in a list.
2.3 Site Table Operations
• Add:
1
2
3
4
5
6
7
8
9
10
11
12
public void add() {
try {
String query = "INSERT INTO SITE (NAME, ADRESS) " + "VALUES(?, ?)";
statement.setString(2, this.address);
statement.close();
}
}
Adds a site object to the DB.
• Delete:
1
2
3
4
5
6
7
8
9
try {
String query = "DELETE FROM SITE WHERE ID = ? ";
statement.close();
55
}
10
11
}
Deletes a site object from the DB.
• Update:
1
2
3
4
5
6
7
8
9
10
11
String query = "UPDATE SITE SET NAME = ?, ADRESS= ? Where ID = ? ";
try {
statement.setString(2, this.address);
}
12
13
}
Updates a site object that already exists in the DB.
• Search:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
public static List<Site> search(String name) {
List<Site> sites = new LinkedList<Site>();
String query = "SELECT ID FROM Site WHERE NAME LIKE ?";
try {
sites.add(new Site(results.getInt("ID")));
}
results.close();
statement.close();
}
16
return sites;
17
18
19
}
Searches a site object in DB by its name using %LIKE% and returns the matches in a list.
Note: Null check is done when adding or updating for attributes that are Integer type. The reason is that setInt
takes primitive type of int (that is, int is not an object). So, it throws exception on null values.
2.3.2 Software Design
1 Explanation of the Implemented Methods
This part explains the methods of Book.java, Genre.java, Site.java and Statistics.java
1.1 Book.java
• Attributes:
56
1
2
3
4
5
6
7
private
private
private
private
private
private
private
Serializable id;
String name;
Author author;
Publisher publisher;
Rate rate;
Editor editor;
Translator translator;
private
private
private
private
private
private
String
String
String
String
String
String
8
9
10
11
12
13
14
ISBN;
type;
language;
date;
format;
summary;
• Constructor:
1
2
3
public Book(Serializable id) {
super();
this.id = id;
4
String query = "SELECT NAME FROM BOOK WHERE ID = ?";
try {
}
results.close();
statement.close();
}
5
6
7
8
9
10
11
12
13
14
15
16
17
18
}
Builds a book object using the supplied id from the DB.
• byID:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
@Override
// After a link is clicked,
// object loses its content except the ID. So, build it again using its
// ID.
String query = "SELECT NAME, ISBN, author_id, editor_id, translator_id, publisher_id, "
+ "TYPE, LANGUAGE, DATE, FORMAT, SUMMARY FROM book WHERE ID = ?";
try {
while (results.next()) { // should be executed only for once.
this.ISBN = results.getString("ISBN");
this.author = new Author(results.getInt("author_id"));
this.editor = new Editor(results.getInt("editor_id"));
this.translator = new Translator(
results.getInt("translator_id"));
this.publisher = new Publisher(results.getInt("publisher_id"));
this.type = results.getString("TYPE");
this.language = results.getString("LANGUAGE");
this.date = results.getString("DATE");
this.format = results.getString("FORMAT");
this.summary = results.getString("SUMMARY");
57
this.rate = Rate.byBook(this);
25
26
}
results.close();
statement.close();
}
27
28
29
30
31
32
33
return this;
34
35
}
Builds a book object using the supplied id from the DB. Similar to constructor. Such method is created becouse
whenever a link is clicked, the object loses all its attributes except the id. So, byID builds it again with the id
• byGenre:
1
2
public static List<Book> byGenre(Genre genre) {
3
String query = "SELECT id, author_id FROM Book WHERE id IN (SELECT book_id FROM Genre WHERE I
try {
statement.setInt(1, ((Integer) genre.getId()));
books.add(book);
}
results.close();
statement.close();
}
return books;
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
}
Returns the list of the books that have the supplied genre as their genre.
• byAuthor:
1
2
public static List<Book> byAuthor(Author author) {
3
String query = "SELECT id, author_id FROM book WHERE author_id = ?";
try {
statement.setInt(1, ((Integer) author.getId()));
books.add(book);
}
results.close();
statement.close();
}
return books;
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
}
58
Returns the list of the books that have the supplied author as their author.
• byEditor:
1
2
public static List<Book> byEditor(Editor editor) {
3
String query = "SELECT id, author_id FROM book WHERE editor_id = ?";
try {
statement.setInt(1, ((Integer) editor.getId()));
books.add(book);
}
results.close();
statement.close();
}
return books;
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
}
Returns the list of the books that have the supplied editor as their editor.
• byTranslator:
1
2
public static List<Book> byTranslator(Translator translator) {
3
String query = "SELECT id, author_id FROM book WHERE translator_id = ?";
try {
statement.setInt(1, ((Integer) translator.getId()));
books.add(book);
}
results.close();
statement.close();
}
return books;
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
}
Returns the list of the books that have the supplied translator as their translator.
• byPublisher:
1
2
public static List<Book> byPublisher(Publisher publisher) {
3
4
5
6
7
8
9
String query = "SELECT id, author_id FROM book WHERE publisher_id = ?";
try {
statement.setInt(1, ((Integer) publisher.getId()));
59
books.add(book);
10
11
12
13
}
results.close();
statement.close();
}
return books;
14
15
16
17
18
19
20
21
}
Returns the list of the books that have the supplied publisher as their publisher.
• all:
1
2
public static List<Book> all() {
3
String query = "SELECT id FROM Book ORDER BY name";
try {
books.add(new Book(results.getInt("id")));
}
results.close();
statement.close();
}
return books;
4
5
6
7
8
9
10
11
12
13
14
15
16
17
}
Returns the list of all books in DB.
1.2 Genre.java
• Attributes:
1
2
3
private String name;
private Book book;
• Constructor:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
public Genre(Serializable id) {
super();
this.id = id;
String query = "SELECT DISTINCT NAME FROM Genre WHERE ID= ?";
try {
}
results.close();
statement.close();
60
}
16
17
}
Builds a genre object using the supplied id from the DB.
• byID:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
@Override
String query = "SELECT DISTINCT NAME, book_id FROM Genre WHERE ID = ?";
try {
this.book = new Book(results.getInt("book_id"));
}
results.close();
statement.close();
}
17
return this;
18
19
}
Builds a genre object using the supplied id from the DB. Similar to constructor. Such method is created becouse
whenever a link is clicked, the object loses all its attributes except the id. So, byID builds it again with the id
• bySortedList:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
public static List<Genre> bySortedList() {
String query = "SELECT DISTINCT ID FROM Genre ORDER BY NAME ASC";
try {
Statement statement = db.createStatement();
ResultSet results = statement.executeQuery(query);
}
results.close();
statement.close();
}
15
return genres;
16
17
}
Returns the list of the genres in alphabetically sorted order.
• byBook:
1
2
ppublic static List<Genre> byBook(Book book) {
3
4
5
6
7
8
9
10
11
String query = "SELECT ID FROM Genre WHERE book_id = ?";
try {
statement.setInt(1, ((Integer) book.getId()));
}
61
results.close();
statement.close();
}
return genres;
12
13
14
15
16
17
18
19
}
Returns the genres that the supplied book have.
• getMatchingID
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
public static Integer getMatchingID(String genreName) {
Integer id = 0;
// check if that genre exists
String query = "SELECT MIN(ID) as MatchID FROM Genre WHERE Name = ?";
try {
statement.setString(1, genreName);
id = result.getInt("MatchID");
}
result.close();
statement.close();
}
// if not, create a new ID
if (id == 0) {
19
String query1 = "SELECT MAX(ID) AS CurMax FROM Genre";
try {
PreparedStatement statement = db.prepareStatement(query1);
id = result.getInt("CurMax") + 1;
}
result.close();
statement.close();
}
20
21
22
23
24
25
26
27
28
29
30
31
}
return id;
32
33
34
35
}
Finds an id for the supplied genre name. Check Database Design, Genre Table part for detailed explanation.
1.3 Site.java
• Attributes:
1
2
3
private String name;
private String address;
• Constructor:
62
1
2
3
public Site() {
this.id = -1;
}
Site has an empty constructor. Building a site object is done using setters.
• byID:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
@Override
String query = "SELECT ID, NAME, ADRESS FROM SITE WHERE ID= ?";
try {
this.address = results.getString("ADRESS");
}
results.close();
statement.close();
}
17
return this;
18
19
}
Builds a site object using the supplied id from the DB. Such method is created becouse whenever a link is clicked,
the object loses all its attributes except the id. So, byID builds it again with the id
• all:
1
2
public static List<Site> all() {
List<Site> sites = new LinkedList<Site>();
3
String query = "SELECT ID FROM SITE ORDER BY NAME";
try {
sites.add(new Site(results.getInt("ID")));
}
results.close();
statement.close();
}
return sites;
4
5
6
7
8
9
10
11
12
13
14
15
16
17
}
Returns the list of all sites in DB.
1.4 Statistics.java
• Attributes:
1
2
3
4
5
6
public
public
public
public
public
public
static
static
static
static
static
static
Integer
Integer
Integer
Integer
Integer
Integer
bookCount;
userCount;
genreCount;
authorCount;
rateCount;
prizeCount;
63
7
8
9
10
11
public
public
public
public
public
static
static
static
static
static
Integer reviewCount;
Integer publisherCount;
String mostExpensive;
Integer avgRate;
String bestRated;
• getBookCount:
1
2
3
4
5
6
7
8
9
10
11
12
String query = "SELECT COUNT(*) AS Count FROM book";
try {
bookCount = result.getInt("Count");
}
result.close();
statement.close();
}
13
return bookCount;
14
15
}
Returns the # of books that exist in the DB.
• getUserCount:
public static Integer getUserCount() {
String query = "SELECT COUNT(*) AS Count FROM User";
try {
userCount = result.getInt("Count");
}
result.close();
statement.close();
}
return userCount;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
}
Returns the # of users that are registered to the BKDB.
• getGenreCount:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
String query = "SELECT COUNT(DISTINCT Name) AS Count FROM Genre";
try {
genreCount = result.getInt("Count");
}
result.close();
statement.close();
}
return genreCount;
}
Returns the # of distinct genres that exist in the DB.
64
• getAuthorCount:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
String query = "SELECT COUNT(*) AS Count FROM Author";
try {
authorCount = result.getInt("Count");
}
result.close();
statement.close();
}
return authorCount;
}
Returns the # of authors that exist in the DB.
• getRateCount:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
String query = "SELECT COUNT(*) AS Count FROM Rate";
try {
rateCount = result.getInt("Count");
}
result.close();
statement.close();
}
return rateCount;
}
Returns the # of votes that are cast by the users of the BKDB.
• getAvgRate:
1
2
3
4
5
6
7
8
9
10
11
12
String query = "SELECT AVG(value) AS Average FROM Rate";
try {
avgRate = result.getInt("Average");
}
result.close();
statement.close();
}
13
return avgRate;
14
15
}
Returns the average of the points that are given by the users of the BKDB.
• getPrizeCount:
1
2
3
4
5
public static Integer getPrizeCount() {
String query = "SELECT COUNT(*) AS Count FROM Prize";
try {
65
prizeCount = result.getInt("Count");
}
result.close();
statement.close();
}
return prizeCount;
6
7
8
9
10
11
12
13
14
15
}
Returns the # of prizes that exist in the DB.
• getReviewCount:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
public static Integer getReviewCount() {
String query = "SELECT COUNT(*) AS Count FROM Review";
try {
reviewCount = result.getInt("Count");
}
result.close();
statement.close();
}
return reviewCount;
}
Returns the # of reviews that are written by the users of the BKDB.
• getPublisherCount:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
public static Integer getPublisherCount() {
String query = "SELECT COUNT(*) AS Count FROM Publisher";
try {
publisherCount = result.getInt("Count");
}
result.close();
statement.close();
}
return publisherCount;
}
Returns the # of publishers that exist in the DB.
• getMostExpensive:
1
2
3
4
5
6
7
8
9
10
String query = "Select name AS Expensive FROM Book WHERE id = "
+ "(SELECT book_id FROM Price WHERE cost = (SELECT MAX(cost) FROM Price))";
try {
mostExpensive = result.getString("Expensive");
}
result.close();
statement.close();
66
}
return mostExpensive;
11
12
13
14
15
}
Returns the most expensive book that exists in the DB.
• getBestRated:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
String query = "SELECT NAME As Best FROM BOOK WHERE ID ="
+ " (SELECT book_id FROM (SELECT BOOK_ID, AVG(VALUE)"
+ " FROM RATE GROUP BY BOOK_ID ORDER BY AVG(VALUE) DESC) LIMIT 1)";
try {
bestRated = result.getString("Best");
}
result.close();
statement.close();
}
return bestRated;
}
Returns the best rated book that exists in the DB.
Note: In addition to the above methods, classes also have getters&setters for their attributes (except for the
Statistics.java which only consists of getters that execute SQL statements). They’re omitted here for simplicity.
2 Explanation of the Pages, Panels and Links
This part explains the pages, panels and links that are related with Book.java, Genre.java, Site.java and Statistics.java
2.1 Pages/Panels/Links related with Book.java
1. AdminBookPage.java
• This page is only accessible by the admin. It is used to carry add/delete/update operations on the Book
Table.
• Top of the page is populated with the list of all books.
1
2
3
4
5
6
7
8
9
10
11
this.add(new ListView<Book>("booksList", Book.all()) {
@Override
protected void populateItem(ListItem<Book> item) {
final Book book = item.getModelObject();
item.add(new BookLink("bookLink", book));
@Override
setResponsePage(new AdminBookPage(book));
}
});
• There are links, textfields, dropdownlists and buttons that allows the admin to carry the desired operation.
Some examples are given below:
DeleteLink
67
1
2
3
4
5
6
7
@Override
book.delete();
setResponsePage(new AdminBookPage());
}
});
• Upon click, delete method of the book is called which deletes the selected book if there are no constraint
fails.
TextFields and DropDownLists
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
bookForm.add(new RequiredTextField<String>("name"));
bookForm.add(new DropDownChoice<Person>("author", Author.all(),
bookForm.add(new DropDownChoice<Publisher>("publisher",
Publisher.all(), new ChoiceRenderer<Publisher>("name", "id")));
bookForm.add(new DropDownChoice<Person>("editor", Editor.all(),
bookForm.add(new DropDownChoice<Person>("translator", Translator.all(),
bookForm.add(new TextField<String>("ISBN"));
bookForm.add(new TextField<String>("type"));
bookForm.add(new TextField<String>("language"));
bookForm.add(new TextField<String>("date"));
bookForm.add(new TextField<String>("format"));
bookForm.add(new TextField<String>("summary"));
• Admin can use those in order to add or to change attributes of a book
AddButton
1
2
3
4
5
6
7
bookForm.add(new Button("addButton") {
@Override
inputBook.add();
setResponsePage(new AdminBookPage());
}
});
• Upon click, add method of the book is called which adds the book to the DB if there are no constraint fails.
2. BookLink.java
• A simple link that is used in panels and lists that contain books in order to refer to a BookPage.
public class BookLink extends Link {
public BookLink(String id, IModel model) {
super(id, model);
}
1
2
3
4
5
@Override
Book book = (Book) getModel().getObject();
this.setResponsePage(new BookPage(book));
}
6
7
8
9
10
11
}
3. BooksPanel.java
• A table structured panel that contains BookLink, AuthorLink and rate of the book.
1
2
3
public class BooksPanel extends Panel {
public BooksPanel(String id, List<Book> books) {
super(id);
68
4
this.add(new ListView<Book>("booksList", books) {
@Override
protected void populateItem(ListItem<Book> item) {
final Book book = item.getModelObject();
5
6
7
8
9
item.add(new BookLink("bookLink", book));
item.add(new PersonLink("authorLink", book.getAuthor()));
item.add(new RatePanel("ratePanel", book));
10
11
12
}
13
});
14
15
16
}
• Corresponding html is structured like the following which is built by using Bootstrap:
1
2
3
4
5
6
<table class="table table-hover" >
<thead><tr>
<th>Book</th>
<th>Author</th>
<th>Rate</th>
</tr></thead>
7
<tbody>
<tr wicket:id="booksList">
<td><a href="#" wicket:id="bookLink">name of the book</a></td>
<td> <a href="#" wicket:id="authorLink">author</a></td>
<td><span wicket:id="ratePanel"></span>rate</td>
8
9
10
11
12
13
14
15
</tr>
</tbody>
</table>
4. BookPage.java
• This page lists attributes of a book using labels.
1
2
3
4
5
6
7
public BookPage(final Book book) {
this.add(new BookLink("bookLink", book));
this.add(new PersonLink("authorLink", book.getAuthor()));
this.add(new PersonLink("editorLink", book.getEditor()));
this.add(new PersonLink("translatorLink", book.getTranslator()));
this.add(new PublisherLink("publisherLink", book.getPublisher()));
this.add(new RatePanel("ratePanel", book));
8
9
10
11
12
this.add(new ListView<Genre>("genresList", book.getGenres()) {
@Override
protected void populateItem(ListItem<Genre> item) {
Genre Genre = item.getModelObject();
13
item.add(new GenreLink("genreLink", Genre));
14
15
16
17
18
}
});
this.add(new ReviewPanel("reviewPanel", book.getReviews(), book));
this.add(new PricesPanel("pricePanel", book.getPrices()));
19
20
21
22
23
24
25
this.add(new
this.add(new
this.add(new
this.add(new
this.add(new
this.add(new
Label("summary", book.getSummary()));
Label("type", book.getType()));
Label("language", book.getLanguage()));
Label("date", book.getDate()));
Label("ISBN", book.getISBN()));
Label("format", book.getFormat()));
• It also has fav and read buttons which allows the user to add books to their To Read and My Favourites lists.
69
1
2
3
4
5
6
7
8
9
10
Form readFavForm = new Form("readFavForm");
readFavForm.add(new Button("readButton") {
if (book.isRead()) {
User.getCurrentUser().unread(book);
} else {
User.getCurrentUser().read(book);
}
}
11
@Override
protected void onComponentTag(ComponentTag tag) {
super.onComponentTag(tag);
if (book.isRead()) {
tag.put("class", "btn btn-success");
} else {
tag.put("class", "btn btn-default");
}
}
12
13
14
15
16
17
18
19
20
});
21
22
readFavForm.add(new Button("favButton") {
if (book.isFav()) {
User.getCurrentUser().unfav(book);
} else {
User.getCurrentUser().fav(book);
}
}
23
24
25
26
27
28
29
30
31
• Upon click, those buttons call the correct user methods.
5. BooksPage.java
• A simple page that has only BookPanel and GenrePanel in it. It simply displays them in a single page.
public class BooksPage extends BasePage {
public BooksPage(List<Book> books) {
this.add(new BooksPanel("booksPanel", books));
this.add(new GenrePanel("genrePanel"));
}
1
2
3
4
5
6
}
2.2 Pages/Panels/Links related with Genre.java
1. AdminGenrePage.java
• This page is only accessible by the admin. It is used to carry add/delete/update operations on the Book
Table.
• Top of the page is populated with the list of all genres.
1
2
3
4
5
6
7
this.add(new ListView<Genre>("genresList", Genre.bySortedList()) {
@Override
protected void populateItem(ListItem<Genre> item) {
final Genre genre = item.getModelObject();
item.add(new GenreLink("genreLink", genre));
}
});
70
• There are links, textfields, dropdownlists and buttons that allows the admin to carry the desired operation.
Some examples are given below:
SelectLink
1
2
3
4
5
6
item.add(new Link("selectLink") {
@Override
setResponsePage(new AdminGenrePage(genre));
}
});
• Upon click, fills the textfield with the name of the selected genre.
TextFields and DropDownLists
genreForm.add(new RequiredTextField<String>("name"));
genreForm.add(new DropDownChoice<Book>("book", Book.all(),
new ChoiceRenderer<Book>("name")));
1
2
3
• Admin can use those in order to add or to change attributes of a genre.
AddButton
1
2
3
4
5
6
7
genreForm.add(new Button("addButton") {
@Override
inputGenre.add();
setResponsePage(new AdminGenrePage());
}
});
• Upon click, add method of the genre is called which adds the genre to the DB if there are no constraint fails.
2. GenreLink.java
• A simple link that is used in panels and lists that contain genres.
1
2
3
4
public class GenreLink extends Link {
public GenreLink(String id, IModel model) {
super(id, model);
}
5
@Override
Genre genre = (Genre) getModel().getObject();
List<Book> books = Book.byGenre(genre);
this.setResponsePage(new BooksPage(books));
}
6
7
8
9
10
11
12
}
• Upon click, gets the list of books with byGenre method that have this particular genre. Then it displays
those books in BooksPage.
3. GenrePanel.java
• A table structured panel that contains GenreLink.
1
2
3
4
public class GenreLink extends Link {
public GenreLink(String id, IModel model) {
super(id, model);
}
5
6
7
8
9
@Override
Genre genre = (Genre) getModel().getObject();
List<Book> books = Book.byGenre(genre);
71
this.setResponsePage(new BooksPage(books));
10
}
11
12
}
• Corresponding html is structured like the following which is built by using Bootstrap. It expands vertically
as a table when clicked.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<div class="panel panel-default">
<div class="panel-heading" role="tab" >
<h4 class="panel-title">
<a class="collapsed" data-toggle="collapse" data-parent="#accordion" href="#collapseGenre" ar
Genre List
</a>
</h4>
</div>
<div id="collapseGenre" class="panel-collapse collapse" role="tabpanel" aria-labelledby="Genre Li
<div class="panel-body" >
<ul class="list-group" >
<li class="list-group-item" wicket:id="genresList"><a href="#" wicket:id="genreLink"> genre</a
</ul>
</div>
</div>
</div>
2.3 Pages/Panels/Links related with Site.java
1. AdminSitePage.java
• This page is only accessible by the admin. It is used to carry add/delete/update operations on the Site Table.
• Top of the page is populated with the list of all sites.
1
2
protected void populateItem(ListItem<Site> item) {
final Site site = item.getModelObject();
3
item.add(new Label("name", site.getName()));
item.add(new Label("address", site.getAddress()));
4
5
6
}
• There are links, textfields and buttons that allows the admin to carry the desired operation. Some examples
are given below:
DeleteLink
1
2
3
4
5
6
7
@Override
site.delete();
setResponsePage(new AdminSitePage());
}
});
• Upon click, delete method of the site is called which deletes the selected book if there are no constraint fails.
TextFields
siteForm.add(new RequiredTextField<String>("name"));
siteForm.add(new RequiredTextField<String>("address"));
1
2
• Admin can use those in order to add or to change attributes of a book
AddButton
72
1
2
3
4
5
6
7
siteForm.add(new Button("addButton") {
@Override
inputSite.insert();
setResponsePage(new AdminSitePage());
}
});
• Upon click, add method of the site is called which adds the book to the DB if there are no constraint fails.
2.4 Pages/Panels/Links related with Statistics.java
1. StatisticsPage.java
• A simple page that shows some statistics about the BKDB in a list.
1
2
3
public class StatisticsPage extends BasePage {
public StatisticsPage() {
initGUI();
4
5
}
6
7
8
9
10
11
12
13
14
15
16
17
18
private void initGUI() {
add(new Label("bookCount", Statistics.getBookCount()));
add(new Label("authorCount", Statistics.getAuthorCount()));
add(new Label("genreCount", Statistics.getGenreCount()));
add(new Label("userCount", Statistics.getUserCount()));
add(new Label("rateCount", Statistics.getRateCount()));
add(new Label("prizeCount", Statistics.getPrizeCount()));
add(new Label("reviewCount", Statistics.getReviewCount()));
add(new Label("publisherCount", Statistics.getPublisherCount()));
add(new Label("mostExpensive", Statistics.getMostExpensive()));
add(new Label("avgRate", Statistics.getAvgRate()));
add(new Label("bestRated", Statistics.getBestRated()));
19
}
20
21
}
• It simply gets the required statistics from Statistics.java.
• Corresponding html is structured like the following which is built by using Bootstrap:
1
2
3
4
5
<ul class="list-group">
<li class="list-group-item" >
<span class="badge"># of Total Books</span>
<span wicket:id = "bookCount"> </span>
</li>
6
7
8
9
10
<span class="badge"># of Total Authors</span>
<span wicket:id = "authorCount"> </span>
</li>
11
12
13
14
15
<span class="badge"># of Different Genres</span>
<span wicket:id = "genreCount"> </span>
</li>
16
17
18
19
20
<span class="badge"># of Total Publishers</span>
<span wicket:id = "publisherCount"> </span>
</li>
21
73
22
23
24
25
<span class="badge"># of Total Prizes Earned By Authors</span>
<span wicket:id = "prizeCount"> </span>
</li>
26
27
28
29
30
<span class="badge"># of Total Members</span>
<span wicket:id = "userCount"> </span>
</li>
31
32
33
34
35
36
<span wicket:id = "rateCount"> </span>
<span class="badge"># of Total Votes by Members</span>
</li>
37
38
39
40
41
<span class="badge">Average Rate of # Total Votes </span>
<span wicket:id = "avgRate"> </span>
</li>
42
43
44
45
46
<span class="badge"># of Total Reviews by Members</span>
<span wicket:id = "reviewCount"> </span>
</li>
47
48
49
50
51
<span class="badge">The Best Rated Book</span>
<span wicket:id = "bestRated"> </span>
</li>
52
53
54
55
56
<span class="badge">The Most Expensive Book</span>
<span wicket:id = "mostExpensive"> </span>
</li>
57
58
</ul>
2.4 Parts Implemented by Abdurrahman NAMLI
1 Tables
1.1 Rate Table
• Rate table was implemented for representing rates which user gives to books
Name
id
user_id
book_id
value
Type
INTEGER
INTEGER
INTEGER
INTEGER
Not Null
1
1
1
1
Primary K.
1
0
0
0
• Value stores rate value which user gave to the book.
• user_id references the User Table and if user of a rate is deleted(updated) also the rate will be
deleted(updated)
74
• book_id references the Book Table and if user of a rate is deleted(updated) also the rate will be
deleted(updated)
Sql statement that initialize the rate table:
CREATE TABLE Rate (
value INTEGER NOT NULL DEFAULT 0,
UNIQUE(user_id, book_id),
FOREIGN KEY(user_id) REFERENCES User(ID) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(book_id) REFERENCES Book(ID) ON DELETE CASCADE ON UPDATE CASCADE
)
1.2 User Table
• User table was implemented for representing the user.
Name
id
name
email
hashedpassword
Type
INTEGER
VARCHAR(32)
VARCHAR(32)
CHAR(43)
Not Null
1
1
1
1
Primary K.
1
0
0
0
• hashedpassword stores the password of user which was hashed.
Sql statement that initialize the User table:
CREATE TABLE User (
name VARCHAR(32) UNIQUE NOT NULL,
email VARCHAR(64) UNIQUE NOT NULL,
hashedPassword CHAR(43) NOT NULL
)
1.3 UserBooks Table
• UserBooks Table stores the user’s books which were marked as favorite or read.
Name
user_id
book_id
isFav
isRead
Type
INTEGER
INTEGER
BOOLEAN
BOOLEAN
Not Null
1
1
1
1
Primary K.
1
0
0
0
• isRed and isFav is 0 as default
• user_id references the User Table and if user of a userbooks is deleted(updated) also the userbooks will be
deleted(updated)
• book_id references the Book Table and if user of a userbooks is deleted(updated) also the userbooks will be
deleted(updated)
Sql statement that initialize the UserBooks table:
CREATE TABLE UserBooks (
isFav BOOLEAN NOT NULL DEFAULT 0,
isRead BOOLEAN NOT NULL DEFAULT 0,
FOREIGN KEY(user_id) REFERENCES User(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(book_id) REFERENCES Book(id) ON DELETE CASCADE ON UPDATE CASCADE,
75
UNIQUE(user_id, book_id)
)
2.4.2 Code
1 WicketApplication.java
It inherits AuthenticatedWebApplication class.
init( ):
public void init() {
super.init();
String homeDir = System.getProperty("user.home");
String dbFilePath = homeDir + File.separator + "BKDB.sqlite";
Model.connect(dbFilePath);
Model.initializeDB();
}
It initializes the application and database and also gets director of database and sends application’s connector of
db.
Other methods:
@Override
public Class<? extends WebPage> getHomePage() {
return HomePage.class;
}
@Override
protected Class<? extends AbstractAuthenticatedWebSession> getWebSessionClass() {
return WicketApplicationWebSession.class;
}
@Override
protected Class<? extends WebPage> getSignInPageClass() {
return UserAuthPage.class;
}
2 WicketApplicationWebSession.java
It inherits AuthenticatedWebSession class.
authenticate( ):
:linennos:
@Override
public boolean authenticate(String email, String password) {
int id = User.authenticate(email, password);
if (id < 0) {
return false; // it is not our user
}
userId = id;
return true;
}
• It authenticates user according to its user id.
getRoles( ):
76
@Override
public Roles getRoles() {
Roles roles = new Roles();
if (isSignedIn()) { // our user
roles.add(Roles.USER);
} else {
roles.add("GUEST");
}
if (userId != null && ((Integer) userId) == 0) { // + Admin
roles.add(Roles.ADMIN);
}
return roles;
}
• It gets role of user. If user did not sign in it is marked as ‘GUEST’ and if user id equals ‘0’ so it is marked
as Admin.
3 Start.java
It starts the application. It connects our application to a server.
connector.setMaxIdleTime(timeout);
connector.setSoLingerTime(-1);
connector.setPort(8081);
server.addConnector(connector);
WebAppContext bb = new WebAppContext();
bb.setServer(server);
bb.setContextPath("/");
bb.setWar("WebContent");
server.setHandler(bb);
4 Model.java
It is a abstract class.It inherits LoadableDetachableModel class. And classes in that application inherits that Model
class
connect( ):
static void connect(String dbFilePath) {
try {
Class.forName("org.sqlite.JDBC");
} catch (ClassNotFoundException e) {
}
try {
String jdbcURL = "jdbc:sqlite:" + dbFilePath;
db = DriverManager.getConnection(jdbcURL);
} catch (SQLException ex) {
throw new UnsupportedOperationException(ex.getMessage());
}
}
• It takes path and connects.
initializeDB( ):
77
static void initializeDB() {
ServletContext context = WebApplication.get().getServletContext();
InputStream stream = context.getResourceAsStream("/WEB-INF/BKDB.sql");
String query = "";
try {
query = IOUtils.toString(stream, "UTF-8");
} catch (IOException e) {
}
try {
Statement statement = db.createStatement();
statement.executeUpdate(query);
statement.close();
PreparedStatement constraintStatement = db.prepareStatement("PRAGMA foreign_keys
constraintStatement.execute();
constraintStatement.close();
}
}
• It initiliaze database according to BKDB.sql file which includes all creating tables and filling up tables sql
statements
• “PRAGMA foreign_keys = ON” it provides to database for checking constraints.
5 BasePage.java
It inherits WebPage class and all Page classes in that application inherits BasePage class.
There are some links for navbar which is at every page.
login link
Link loginLink = new Link("loginLink") {
@Override
this.setResponsePage(new UserAuthPage());
}
};
MetaDataRoleAuthorizationStrategy.authorize(loginLink, RENDER, "GUEST");
this.add(loginLink);
• It provides signing in to users
register link
Link registerLink = new Link("registerLink") {
@Override
this.setResponsePage(new UserAuthPage());
}
};
MetaDataRoleAuthorizationStrategy.authorize(registerLink, RENDER, "GUEST");
this.add(registerLink);
• It provides register to users
user name
Label username = new Label("username") {
public void onComponentTagBody(MarkupStream markupStream, ComponentTag openTag) {
replaceComponentTagBody(markupStream, openTag, User.getCurrentUser().getName());
}
78
};
MetaDataRoleAuthorizationStrategy.authorize(username, RENDER, Roles.USER);
this.add(username);
• It shows user’s name if user is logged in.
my list link:
Link myListLink = new Link("myListLink") {
@Override
this.setResponsePage(new MyListPage());
}
};
MetaDataRoleAuthorizationStrategy.authorize(myListLink, RENDER, Roles.USER);
this.add(myListLink);
• It redirects to user’s book list page (MyListPage) which includes user’s favorite and read books.
userSettingsLink:
Link userSettingsLink = new Link("userSettingsLink") {
@Override
this.setResponsePage(new UserSettingsPage());
}
};
MetaDataRoleAuthorizationStrategy.authorize(userSettingsLink, RENDER, Roles.USER);
this.add(userSettingsLink);
• It redirects to user’s settings page to edit its information.
logoutLink:
Link logoutLink = new Link("logoutLink") {
@Override
User.logout();
this.setResponsePage(new HomePage());
}
};
MetaDataRoleAuthorizationStrategy.authorize(logoutLink, RENDER, Roles.USER);
this.add(logoutLink);
• It log out the user who is already is signed.
adminLink:
Link adminLink = new Link("adminLink") {
@Override
this.setResponsePage(new AdminPage());
}
};
MetaDataRoleAuthorizationStrategy.authorize(adminLink, RENDER, Roles.ADMIN);
this.add(adminLink);
• It redirects to admin panel.
search:
Form<BasePage> searchForm = new Form<BasePage>("searchForm",
new CompoundPropertyModel<BasePage>(this));
searchForm.add(new TextField<String>("searchInput"));
searchForm.add(new Button("searchAuthorButton") {
79
@Override
setResponsePage(new PersonsPage(Author.search(searchInput)));
}
});
searchForm.add(new Button("searchBookButton") {
@Override
setResponsePage(new BooksPage(Book.search(searchInput)));
}
});
this.add(searchForm);
• It is searching author or book.
6 AdminBasePage.java
It inherits WebPage. It is used for admin panel which is called as “Management Panel For Librarian”.
Links:
this.add(new Link("generalPageLink") {
@Override
}
});
this.add(new Link("authorPageLink") {
@Override
this.setResponsePage(new AdminAuthorPage());
}
});
this.add(new Link("bookPageLink") {
@Override
this.setResponsePage(new AdminBookPage());
}
});
this.add(new Link("editorPageLink") {
@Override
this.setResponsePage(new AdminEditorPage());
}
});
this.add(new Link("translatorPageLink") {
@Override
this.setResponsePage(new AdminTranslatorPage());
}
});
this.add(new Link("pricePageLink") {
@Override
this.setResponsePage(new AdminPricePage());
}
});
this.add(new Link("sitePageLink") {
@Override
this.setResponsePage(new AdminSitePage());
}
80
});
this.add(new Link("prizePageLink") {
@Override
this.setResponsePage(new AdminPrizePage());
}
});
this.add(new Link("prizeWinnerPageLink") {
@Override
public void onClick(){ this.setResponsePage(new AdminPrizeWinnersPage());}
});
this.add(new Link("publisherPageLink") {
@Override
this.setResponsePage(new AdminPublisherPage());
}
});
this.add(new Link("ratePageLink") {
@Override
this.setResponsePage(new AdminRatePage());
}
});
this.add(new Link("genrePageLink") {
@Override
this.setResponsePage(new AdminGenrePage());
}
});
this.add(new Link("userPageLink") {
@Override
this.setResponsePage(new AdminUserPage());
}
});
• There are links to Admin pages for all objects: author, price, publisher, book etc. In those pages admin can
edit, delete and update any object.
7 HomePage.java
It inherits BasePage class
Class:
public class HomePage extends BasePage {
public HomePage() {
this.add(new GenrePanel("genrePanel"));
List<Book> recommendedBooks = User.getRecommendedBooks();
this.add(new BooksPanel("booksPanel", recommendedBooks));
}
}
• It is main page of application. It includes recommended books and genre list.
8 Recommendation.java
It inherits model class. It lists recommended books in home page.
getBooks( ):
81
public static List<Book> getBooks() {
if (books != null) {
return books;
}
books = new LinkedList<Book>();
String query = "SELECT book_id, AVG(value) AS average, " +
"(SELECT author_id FROM Book WHERE id=book_id) AS author_id " +
"FROM Rate GROUP BY book_id ORDER BY average DESC";
try {
Book book = new Book(results.getInt("book_id"));
Rate rate = new Rate();
rate.setAverageRate(results.getFloat("average"));
book.setRate(rate);
books.add(book);
}
results.close();
statement.close();
}
return books;
}
• It sends a sql statement to select books which have the highest rate average.
9 User.java
It inherits Model class and it handles user-database relations.
Attributes:
private
private
private
private
private
Serializable id;
String name;
String email;
String hashedPassword;
List<Book> books;
Constructor:
public User(Serializable id) {
this.id = id;
String query = "SELECT name FROM User WHERE id = ?";
try {
}
results.close();
statement.close();
}
}
Builds an User object using the supplied id from the DB.
82
return name;
}
this.name = name;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
books = new LinkedList<Book>();
String query = "SELECT book_id, author_id FROM UserBooks JOIN Book ON book.id = UserBooks
try {
statement.setInt(1, ((Integer) User.getCurrentUser().getId()));
Book book = new Book(results.getInt("book_id"));
books.add(book);
}
results.close();
statement.close();
}
return books;
}
return id;
}
public static List<Book> getRecommendedBooks() {
return Recommendation.getBooks();
}
hash(string):
public static String hash(String password) {
// LATER: randomly salt the password
MessageDigest md = null;
try {
md = MessageDigest.getInstance("SHA-256");
} catch (NoSuchAlgorithmException e) {
}
byte[] digest = md.digest(password.getBytes());
return Base64.encodeBase64URLSafeString(digest);
}
• It hashes the user password to save it in db.
Login, Register and Logout functions:
83
public static void register(String name, String email, String password) {
User user = new User();
user.name = name;
user.email = email;
user.hashedPassword = hash(password);
user.insert();
}
public static boolean login(String name, String password) {
AuthenticatedWebSession session = WicketApplicationWebSession.get();
return session.signIn(name, password);
}
public static void logout() {
session.invalidate();
}
authenticate(string,string):
public static int authenticate(String name, String password) {
Integer id = -1;
String query = "SELECT id FROM User WHERE name = ? AND hashedPassword = ?";
try {
statement.setString(1, name);
statement.setString(2, hash(password));
id = results.getInt("id");
}
results.close();
statement.close();
}
if (id >= 0) { // Id of ADMIN is 0. Id of USERs are positive. ADMIN is a USER as well.
session.setAttribute("id", id);
session.bind(); // LATER: bind session in most appropriate positions
} else { // wrong username-password
id = -1;
}
return id;
}
• It is used for getting the id of the user with matching name and hashed password.
ısLoggedIn():
public static boolean isLoggedIn() {
// You can use @AuthorizeInstantiation(Roles.USER) to only initialize pages or components
// You can use @AuthorizeAction(action = "ENABLE", roles = {Roles.ADMIN}) to only permit
// Our defined roles are Roles.ADMIN and Roles.USER
return WicketApplicationWebSession.get().isSignedIn();
}
getCurrentUser():
public static User getCurrentUser() {
Integer id = (Integer) session.getAttribute("id");
84
return new User(id);
}
• It returns current user, so favorite books, reading list etc. can be filled using the current user id.
update:
public void changeSettings(String name, String email, String old_password, String new_passwo
this.byId();
if (this.hashedPassword.equals(hash(old_password))) {
this.name = name;
this.email = email;
this.hashedPassword = hash(new_password);
this.update();
}
}
try {
String query = "UPDATE User SET name = ?, email = ?, hashedPassword = ? WHERE id = ?"
statement.setString(2, this.email);
statement.setString(3, this.hashedPassword);
statement.close();
}
}
• After the user changed it’s information, update function is invoked to make a query to apply changes in
database.
other query functions:
try {
String query = "DELETE FROM User WHERE id = ?";
statement.close();
}
}
public static List<User> search(String name) {
List<User> users = new LinkedList<User>();
String query = "SELECT id, email FROM User WHERE name LIKE ?";
try {
User user = new User(results.getInt("id"));
user.setEmail(results.getString("email"));
users.add(user);
}
results.close();
statement.close();
85
}
return users;
}
public static List<User> all() {
List<User> users = new LinkedList<User>();
String query = "SELECT id, email FROM User ORDER BY name";
try {
User user = new User(results.getInt("id"));
user.setEmail(results.getString("email"));
users.add(user);
}
results.close();
statement.close();
}
return users;
}
fav, unfav and isfav:
public void fav(Book book) {
try {
String query = "INSERT OR REPLACE INTO UserBooks (user_id, book_id, isFav, isRead) VA
" (?, ?, 1, (SELECT isRead FROM UserBooks WHERE user_id=? AND book_id=?) )";
Integer user_id = (Integer) User.getCurrentUser().getId();
Integer book_id = (Integer) book.getId();
statement.setInt(1, user_id);
statement.setInt(2, book_id);
statement.close();
}
}
public void unfav(Book book) {
try {
" (?, ?, 0, (SELECT isRead FROM UserBooks WHERE user_id=? AND book_id=?) )";
statement.close();
}
}
public boolean isFav(Book book) {
86
Boolean value = false;
String query = "SELECT isFav FROM UserBooks WHERE user_id = ? AND book_id = ?";
try {
value = results.getBoolean("isFav");
}
results.close();
statement.close();
}
return value;
}
• fav function add a book as favourite to UserBooks table, unfav does the opposite, and isfav returns whether
that book is favourite of that user or not. And if the same book and user couple is added before as a reading
list, it just updates favourite part instead of inserting.
read, unread, isread:
public void read(Book book) {
try {
" (?, ?, (SELECT isFav FROM UserBooks WHERE user_id=? AND book_id=?), 1 )";
statement.close();
}
}
public void unread(Book book) {
try {
" (?, ?, (SELECT isFav FROM UserBooks WHERE user_id=? AND book_id=?), 0 )";
statement.close();
}
}
public boolean isRead(Book book) {
Boolean value = false;
String query = "SELECT isRead FROM UserBooks WHERE user_id = ? AND book_id = ?";
87
try {
value = results.getBoolean("isRead");
}
results.close();
statement.close();
}
return value;
}
• read function add a book to the readlist in UserBooks table, unread does the opposite, and isRead returns
whether that book is in reading list of that user or not. And if the same book and user couple is added before
as a favourite book, it just updates isread part instead of inserting.
10 UserAuthPage.java
It inherits BasePage class. It is register, login page and has required labels, and takes the user information:
public String name;
public String email;
public String password;
public UserAuthPage() {
this.add(new SignInPanel("loginPanel"));
Form registerForm = new Form("registerForm", new CompoundPropertyModel(this));
registerForm.add(new RequiredTextField<String>("name"));
registerForm.add(new RequiredTextField<String>("email"));
registerForm.add(new PasswordTextField("password"));
registerForm.add(new Button("registerButton") {
@Override
User.register(name, email, password);
User.login(name, password);
setResponsePage(new HomePage());
}
});
this.add(registerForm);
}
11 UserSettingsPage.java
It inherits BasePage class. It has form to update or delete the current user:
12 MyListPage.java
It inherits BasePage class and lists user’s books which are marked as favorite or read by user.
Class:
public class MyListPage extends BasePage {
public MyListPage() {
List<Book> myBooks = User.getCurrentUser().getBooks();
88
this.add(new BooksPanel("booksPanel", myBooks));
}
}
13 AdminUserPage.java
It inherits AdminBasePage class. In this page admin can edit or delete a user.
Lists all user:
this.add(new ListView<User>("usersList", User.all()) {
@Override
protected void populateItem(ListItem<User> item) {
final User user = item.getModelObject();
item.add(new Label("userName", user.getName()));
item.add(new Label("userEmail", user.getEmail()));
@Override
setResponsePage(new AdminUserPage(user));
}
});
@Override
user.delete();
setResponsePage(new AdminUserPage());
}
});
}
});
• Every user has edit and delete link next to them. Admin can edit or delete a user by clicking one of them.
14 AdminPage.java
It inherits AdminBasePage and it is main page of management page for librarian.
Class:
public class AdminPage extends AdminBasePage {
public AdminPage() {
this.add(new Link("statsLink") {
@Override
this.setResponsePage(new StatisticsPage());
}
});
this.add(new Link("resetLink") {
@Override
Model.initializeDB();
}
});
}
}
• There is stats link to statistics page which include statistics about application. And also there is reset link
that resets database.
89
15 Rate.java
It inherits Model class. It directs rate of books.
Attributes:
private
private
private
private
private
Serializable id;
Book book;
User user;
Integer value;
float averageRate;
• value : keeps how user gave rate to the book.
• user : keeps who gave the rate.
• book : keeps rate is given which book.
• averageRate : keeps average rate of a book.
Getters and setter:
public
public
public
public
public
float getAverageRate()
void setAverageRate(float avg)
void setValue(Integer value)
void setUser(User user)
void setBook(Book book)
byBook( ):
public static Rate byBook(Book book) {
try {
String query = "SELECT AVG(value) FROM rate WHERE book_id = ? GROUP BY book_id";
rate.averageRate = results.getFloat(1);
}
results.close();
statement.close();
}
return rate;
}
• byBook method returns average rate of a book by sends a swl statement to the database with book id.
resetByBook( ):
public static void resetByBook(Book book) {
try {
String query = "DELETE FROM Rate WHERE book_id = ?";
statement.setInt(1, (Integer) book.getId());
statement.close();
}
}
• It is used in admin panel to reset rates of a book by admin.
90
16 RatePanel.java
It inherits Panel class and it provides to users giving rate to books between 0 and 5 points. If user is logged in it
can be seen.
giving rate form:
Form rateForm = new Form("rateForm", new CompoundPropertyModel(this));
rateForm.add(new DropDownChoice<Integer>("userRate", Arrays.asList(new Integer[] {0, 1, 2, 3,
rateForm.add(new Button("rateButton") {
@Override
rate.setValue(userRate);
rate.setUser(User.getCurrentUser());
rate.setBook(book);
rate.insert();
setResponsePage(this.getWebPage());
}
});
MetaDataRoleAuthorizationStrategy.authorize(rateForm, RENDER, Roles.USER);
this.add(rateForm);
this.add(new Label("rate", String.format("%.2g%n", book.getRate().getAverageRate())));
• The rate point from dropdown menu. To give rate before user should choose rate point from dropdown
menu then click rate button.
17 AdminRatePage.java
It inherits AdminBasePage class. It provides to admin user reset rates of a book.
dropdown form and reset button:
Form rateForm = new Form("rateForm", new CompoundPropertyModel(this));
rateForm.add(new DropDownChoice<Book>("book", Book.all(),
new ChoiceRenderer<Book>("name", "id")));
rateForm.add(new Button("resetButton") {
@Override
Rate.resetByBook(book);
setResponsePage(new AdminRatePage());
}
});
this.add(rateForm);
• From dropdown menu book is chosen and by clicking reset button rates of books deleted and avegrage rate
of book becomes default value.
2.5 Parts Implemented by Mücahid Adem Çelebi
2.6 1 Database Design
2.6.1 1.1 Tables
1.1.1 Translator Table
• Translator table was implemented for storing general information about a Translator
2.5. Parts Implemented by Mücahid Adem Çelebi
91
Name
id
name
year
nationality
deathYear
Type
INTEGER
varchar(32)
INTEGER
VARCHAR(32)
VARCHAR(4)
Not Null
1
1
0
0
0
Primary K.
1
0
0
0
0
AUTOINCREMENT
1
0
0
0
0
Foreign K.
0
0
0
0
0
Information
• It has 1:n relation with book table
DROP TABLE IF EXISTS "Translator";
CREATE TABLE "Translator" (
year INTEGER,
);
1.1.1 Editor Table
• Editor table was implemented for storing general information about an Editor
Name
id
name
year
nationality
deathYear
Type
INTEGER
varchar(32)
INTEGER
VARCHAR(32)
VARCHAR(4)
Not Null
1
1
0
0
0
Primary K.
1
0
0
0
0
AUTOINCREMENT
1
0
0
0
0
Foreign K.
0
0
0
0
0
Information
• It has 1:n relation with book table
DROP TABLE IF EXISTS "Editor";
CREATE TABLE "Editor" (
year INTEGER,
);
92
2.7 2 Software Design
2.7.1 2.1 Editor.java
It inherits Person class. It holds an editor from the table, and performs table operations:
private
private
private
private
private
private
String name;
Serializable id;
int year;
String nationality;
String deathYear;
List<Book> books;
public Editor() {
}
public Editor(Serializable id) {
this.id = id;
try {
String query = "SELECT name FROM Editor WHERE id=?";
}
results.close();
statement.close();
}
}
@Override
return nationality;
}
@Override
return year;
}
@Override
return id;
}
this.name = name;
}
this.id = id;
}
this.year = year;
}
2.7. 2 Software Design
93
}
}
@Override
String query = "SELECT NAME, year, nationality, deathYear FROM Editor WHERE id=?";
try {
this.year = results.getInt("year");
this.nationality = results.getString("nationality");
this.deathYear = results.getString("deathYear");
this.books = Book.byEditor(this);
}
results.close();
statement.close();
}
return this;
}
@Override
return name;
}
@Override
return books;
}
@Override
return deathYear;
}
@Override
public void add() {
try {
String query = "INSERT INTO Editor (NAME, year, nationality, deathYear) "
+ "VALUES(?, ?, ?, ?)";
statement.close();
}
}
@Override
94
try {
String query = "DELETE FROM Editor WHERE ID=?";
statement.close();
}
}
@Override
try {
String query = "UPDATE Editor SET name=?, year=?, nationality=?, deathYear=? WHERE id
statement.close();
}
}
List<Person> editors = new LinkedList<Person>();
String query = "SELECT id FROM Editor ORDER BY name";
try {
editors.add((Person) new Editor(results.getInt("id")));
}
results.close();
statement.close();
}
return editors;
}
2.7.2 2.2 Translator.java
It inherits Person class. It holds a translator from the table, and performs table operations:
private
private
private
private
private
private
String name;
Serializable id;
int year;
String nationality;
String deathYear;
List<Book> books;
public Translator() {
}
95
public Translator(Serializable id) {
this.id = id;
try {
String query = "SELECT name FROM Translator WHERE id=?";
}
results.close();
statement.close();
}
}
@Override
return deathYear;
}
@Override
return nationality;
}
@Override
return year;
}
@Override
return id;
}
this.name = name;
}
this.id = id;
}
this.year = year;
}
}
}
@Override
String query = "SELECT NAME, year, nationality, deathYear FROM Translator WHERE id=?";
try {
96
this.year = results.getInt("year");
this.nationality = results.getString("nationality");
this.deathYear = results.getString("deathYear");
this.books = Book.byTranslator(this);
}
results.close();
statement.close();
}
return this;
}
@Override
return name;
}
@Override
return books;
}
@Override
public void add() {
try {
String query = "INSERT INTO Translator (NAME, year, nationality, deathYear) "
+ "VALUES(?, ?, ?, ?)";
statement.close();
}
}
@Override
try {
String query = "DELETE FROM Translator WHERE ID=?";
statement.close();
}
}
@Override
try {
String query = "UPDATE Translator SET name=?, year=?, nationality=?, deathYear=? WHER
97
statement.close();
}
}
List<Person> translators = new LinkedList<Person>();
String query = "SELECT id FROM Translator ORDER BY name";
try {
translators.add((Person) new Translator(results.getInt("id")));
}
results.close();
statement.close();
}
return translators;
}
2.7.3 2.3 AdminEditorPage.java
It inherits AdminBasePage class. It is used for updating, deleting and editing an editor from editor table:
Editor inputEditor;
public AdminEditorPage() {
this(new Editor());
}
public AdminEditorPage(Editor editor) {
this.inputEditor = editor;
this.add(new ListView<Person>("editorsList", Editor.all()) {
@Override
final Person editor = item.getModelObject();
item.add(new PersonLink("personLink", editor));
@Override
setResponsePage(new AdminEditorPage((Editor) editor));
}
});
@Override
editor.delete();
setResponsePage(new AdminEditorPage());
}
});
98
}
});
Form editorForm = new Form("editorForm", new CompoundPropertyModel(
this.inputEditor));
editorForm.add(new RequiredTextField<String>("name"));
editorForm.add(new RequiredTextField<String>("year"));
editorForm.add(new RequiredTextField<String>("deathYear"));
editorForm.add(new RequiredTextField<String>("nationality"));
editorForm.add(new Button("addButton") {
@Override
inputEditor.add();
}
});
editorForm.add(new Button("editButton") {
@Override
inputEditor.update();
}
});
this.add(editorForm);
}
2.7.4 2.4 AdminTranslatorPage.java
It inherits AdminBasePage class. It is used for updating, deleting and editing a translator from translator table:
Translator inputTranslator;
public AdminTranslatorPage() {
this(new Translator());
}
public AdminTranslatorPage(Translator translator) {
this.inputTranslator = translator;
this.add(new ListView<Person>("translatorsList", Translator.all()) {
@Override
final Person translator = item.getModelObject();
item.add(new PersonLink("personLink", translator));
@Override
setResponsePage(new AdminTranslatorPage((Translator) translator));
}
});
@Override
translator.delete();
setResponsePage(new AdminTranslatorPage());
}
});
}
});
99
Form translatorForm = new Form("translatorForm", new CompoundPropertyModel(
this.inputTranslator));
translatorForm.add(new RequiredTextField<String>("name"));
translatorForm.add(new RequiredTextField<String>("year"));
translatorForm.add(new RequiredTextField<String>("deathYear"));
translatorForm.add(new RequiredTextField<String>("nationality"));
translatorForm.add(new Button("addButton") {
@Override
inputTranslator.add();
}
});
translatorForm.add(new Button("editButton") {
@Override
inputTranslator.update();
}
});
this.add(translatorForm);
}
100

BKDB Documentation

Transkript

Benzer belgeler

Agents for Web Services

BilVideo Video Database Management System

Solutions of the Pell Equations x2

Premium Soft Navicat Portable

BilVideo-7 - Bilkent University Computer Engineering Department

Ozgur Ozturk`s Introduction to XMPP 1 XMPP Protocol and

third turkish aspect-oriented software development workshop

Content-Based Image Retrieval - Bilkent University Computer