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 ÇELEBİ 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 BKDB Documentation, Release 1.0 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 BKDB Documentation, Release 1.0 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 BKDB Documentation, Release 1.0 Figure 1.5: Admin Price Page 1.2 Parts Implemented by Taha Emre 1.2.1 Regular Users Perspective 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 BKDB Documentation, Release 1.0 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. 1.2.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 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 Chapter 1. User Guide BKDB Documentation, Release 1.0 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. 1.2. Parts Implemented by Taha Emre 7 BKDB Documentation, Release 1.0 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.3.1 Regular Users Perspective 1. BooksPage 2. GenrePanel 3. BooksPanel 4. BookPage 8 Chapter 1. User Guide BKDB Documentation, Release 1.0 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 BKDB Documentation, Release 1.0 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 Chapter 1. User Guide BKDB Documentation, Release 1.0 Figure 1.25: Review Panel and Prices Panel of the The Sirens of Titan 1.3.2 Admins Perspective • Admin can access the pages which a regular user can access. In addition to that, admin has some extra 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 1.3. Parts Implemented by Mustafa Safa Özdayı 11 BKDB Documentation, Release 1.0 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.4.1 Regular Users Perspective 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 Chapter 1. User Guide BKDB Documentation, Release 1.0 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. 1.4.2 Admins Perspective • Admin can access the pages which a regular user can access. In addition to that, admin has some extra features. 1.4. Parts Implemented by Abdurrahman NAMLI 13 BKDB Documentation, Release 1.0 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.5.1 Regular Users Perspective 1. EditorPage 2. TranslatorPage 14 Chapter 1. User Guide BKDB Documentation, Release 1.0 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 1.5.2 Admins Perspective • Admin can access the pages which a regular user can access. In addition to that, admin has some extra features. 1. AdminEditorPage 2. AdminTranslatorPage 1.5. Parts Implemented by Mücahit Adem Çelebi 15 BKDB Documentation, Release 1.0 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 1. User Guide 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 BKDB Documentation, Release 1.0 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 BKDB Documentation, Release 1.0 • 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 BKDB Documentation, Release 1.0 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(); } catch (SQLException e) { e.printStackTrace(); } } delete( ) method: 1 2 3 4 5 6 7 8 9 10 11 public void delete() { try { String query = "DELETE FROM Publisher WHERE ID = ? "; PreparedStatement statement = db.prepareStatement(query); statement.setInt(1, ((Integer) this.id)); statement.executeUpdate(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } } This method deletes a publisher from database which is chosen by user as sending sql statement to the database. insert( ) method: 20 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 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 PreparedStatement statement = 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.executeUpdate(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } } 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 { PreparedStatement statement = db.prepareStatement(query); ResultSet results = statement.executeQuery(); while (results.next()) { publishers.add(new Publisher(results.getInt("id"))); } results.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } 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(). 2.1. Parts Implemented by Mehmet Hüseyin TEMEL 21 BKDB Documentation, Release 1.0 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 public void onClick() { setResponsePage(new AdminPublisherPage(publisher)); } }); Delete Link for deleting: 1 2 3 4 5 6 7 item.add(new Link("deleteLink") { @Override public void onClick() { 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 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 6 7 8 9 10 11 12 publisherForm.add(new Button("addButton") { @Override public void onSubmit() { inputPublisher.insert(); setResponsePage(new AdminPublisherPage()); } }); • 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 public void onSubmit() { inputPublisher.update(); setResponsePage(new AdminPublisherPage()); } }); 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(); } First constructor is used for creating empty objects and prevent to be sent null. The second constructor is general constructor. It calls byId( ) method and byI() sends a sql statement to the database and fill up the attributes to send ReviewPanel. Getters and setters: 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) 2.1. Parts Implemented by Mehmet Hüseyin TEMEL 23 BKDB Documentation, Release 1.0 6 7 8 public void setBook(Book book) public String getDatetime() public User getUser() byId( ) method: 1 protected Model byId() { 2 int x = ((Integer) this.getId()); String query = "SELECT ID, DESCRIPTION, BOOK_ID, USER_ID FROM REVIEW try { PreparedStatement statement = this.db.prepareStatement(query); statement.setInt(1, x); ResultSet results = statement.executeQuery(); while (results.next()) { 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 } } catch (SQLException e) { e.printStackTrace(); } 17 18 19 20 21 return this; 22 23 } 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 review. It takes all information from database results. update( ) method: 1 public void update() { 2 int ID = ((Integer) this.getId()); String query = "UPDATE Review SET DESCRIPTION = ?, BOOK_ID = ?, USER_ID = ?, DATE = ? Where ID try { PreparedStatement statement = this.db.prepareStatement(query); statement.setString(1, this.description); statement.setInt(2, (Integer) this.book.getId()); statement.setString(3, (this.datetime)); statement.setInt(4, (Integer) this.user.getId()); statement.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } 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 public void delete() { try { String query = "DELETE FROM Review WHERE ID = ? "; PreparedStatement statement = db.prepareStatement(query); statement.setInt(1, ((Integer) this.id)); statement.executeUpdate(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } } 24 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 insert( ) method: 1 2 3 4 5 6 7 8 9 10 11 12 13 public void insert() { try { String query = "INSERT INTO Review (DESCRIPTION, USER_ID, BOOK_ID, DATE) VALUES(?, ?, ?, d PreparedStatement statement = db.prepareStatement(query); statement.setString(1, this.description); statement.setInt(2, (Integer) this.user.getId()); statement.setInt(3, (Integer) this.book.getId()); statement.executeUpdate(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } } 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 { PreparedStatement statement = db.prepareStatement(query); statement.setInt(1, userId); ResultSet results = statement.executeQuery(); while (results.next()) { 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(); } catch (SQLException e) { e.printStackTrace(); } 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 { PreparedStatement statement = db.prepareStatement(query); statement.setInt(1, x); ResultSet results = statement.executeQuery(); while (results.next()) { Serializable revID = (Serializable) results.getInt("ID"); String description = results.getString("DESCRIPTION"); 2.1. Parts Implemented by Mehmet Hüseyin TEMEL 25 BKDB Documentation, Release 1.0 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(); } catch (SQLException e) { e.printStackTrace(); } 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 public void onSubmit() { 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 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 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() { this.id = -1; // For creating empty objects } 4 5 6 7 8 public Price(Serializable id) { this.id = id; this.byId(); } First constructor is used for creating empty objects and prevent to be sent null. The second constructor is general constructor. It calls byId( ) method and it sends a sql statement to the database and fill up attributes to send other classes. Getters and setters: 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 protected Model byId() { 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 { PreparedStatement statement = this.db.prepareStatement(query); statement.setInt(1, x); ResultSet results = statement.executeQuery(); while (results.next()) { int bookId = results.getInt("BOOK_ID"); int siteId = results.getInt("SITE_ID"); double cost = results.getDouble("COST"); 14 15 book = new Book(bookId); 2.1. Parts Implemented by Mehmet Hüseyin TEMEL 27 BKDB Documentation, Release 1.0 site = new Site(siteId); this.cost = cost; 16 17 } results.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } 18 19 20 21 22 23 24 return this; 25 26 } 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 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 { PreparedStatement statement = db.prepareStatement(query); statement.setInt(1, x); ResultSet results = statement.executeQuery(); while (results.next()) { 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(); } catch (SQLException e) { e.printStackTrace(); } 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 public void update() { 2 int ID = ((Integer) this.getId()); String query = "UPDATE Price " + "SET " + "COST = ?," + "BOOK_ID = ?," + "SITE_ID = ?" + " Where ID = ? "; try { PreparedStatement statement = this.db.prepareStatement(query); statement.setDouble(1, this.cost); statement.setInt(2, (Integer) this.book.getId()); 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 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 statement.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } 16 17 18 19 20 } delete( ) method: 1 2 3 4 5 6 7 8 9 10 11 public void delete() { try { String query = "DELETE FROM Price WHERE ID = ? "; PreparedStatement statement = db.prepareStatement(query); statement.setInt(1, ((Integer) this.id)); statement.executeUpdate(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } } insert( ) method: 1 2 public void insert() { try { 3 String query = "INSERT INTO Price (COST, BOOK_ID, Site_id) " + "VALUES(?, ?, ?)"; PreparedStatement statement = db.prepareStatement(query); 4 5 6 7 statement.setDouble(1, this.cost); statement.setInt(2, (Integer) this.book.getId()); statement.setInt(3, (Integer) this.site.getId()); statement.executeUpdate(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } 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) { List<Price> prices = new LinkedList<Price>(); String query = "SELECT id, cost, book_id, site_id FROM Price WHERE cost <= ?"; try { PreparedStatement statement = db.prepareStatement(query); statement.setDouble(1, costt); ResultSet results = statement.executeQuery(); while (results.next()) { 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(); } catch (SQLException e) { e.printStackTrace(); } return prices; 21 22 } 2.1. Parts Implemented by Mehmet Hüseyin TEMEL 29 BKDB Documentation, Release 1.0 all( ) method: public static List<Price> all() { List<Price> prices = new LinkedList<Price>(); 1 2 3 String query = "SELECT ID FROM PRICE ORDER BY COST ASC"; try { PreparedStatement statement = db.prepareStatement(query); ResultSet results = statement.executeQuery(); while (results.next()) { prices.add(new Price(results.getInt("ID"))); } results.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } 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 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 Delete Link for deleting: 1 2 3 4 5 6 7 8 9 item.add(new Link("deleteLink") { @Override public void onClick() { 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 public void onSubmit() { 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 2.2. Parts Implemented by Taha Emre 31 BKDB Documentation, Release 1.0 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 The structure of the table is like below: DROP TABLE IF EXISTS "PrizeDetail"; CREATE TABLE "PrizeDetail" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "name" VARCHAR(32), 32 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 "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 does not require any foreign key since it stores independent data • It has 1:n relation with book and prize tables • The variable name must be unique • The variable deathYear has ‘?’ as default value The structure of the table is like below: DROP TABLE IF EXISTS "Author"; CREATE TABLE "Author" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "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: 2.2. Parts Implemented by Taha Emre 33 BKDB Documentation, Release 1.0 public Prize(Serializable id) { this.id = id; String query = "SELECT name , definition FROM prizeDetail 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.definition = results.getString("definition"); } results.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } } 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 { PreparedStatement statement = db.prepareStatement(query); 34 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 statement.setString(1, searchname); ResultSet results = statement.executeQuery(); while (results.next()) { Prize prize = new Prize(results.getInt("id")); prizes.add(prize); } results.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } 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=?"; PreparedStatement statement = db.prepareStatement(query); statement.setInt(1, (Integer) this.id); statement.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } } 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 = ?"; PreparedStatement statement = db.prepareStatement(query); statement.setInt(3, (Integer) this.getId()); statement.setString(1, this.name); statement.setString(2, this.definition); statement.executeUpdate(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } } 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(?, ?)"; PreparedStatement statement = db.prepareStatement(query); statement.setString(1, this.name); statement.setString(2, this.definition); statement.executeUpdate(); statement.close(); } catch (SQLException e) { 2.2. Parts Implemented by Taha Emre 35 BKDB Documentation, Release 1.0 e.printStackTrace(); } } 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 { PreparedStatement statement = db.prepareStatement(query); ResultSet results = statement.executeQuery(); while (results.next()) { prizes.add(new Prize(results.getInt("id"))); } results.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } return prizes; } It returns all the added prizes form prizeDetail table. byId() method: protected Model byId() { String query = "SELECT name, definition FROM PrizeDetail WHERE id = ?"; try { PreparedStatement statement = db.prepareStatement(query); statement.setInt(1, ((Integer) this.getId())); ResultSet results = statement.executeQuery(); while (results.next()) { // should be executed only for once. this.name = results.getString("name"); this.definition = results.getString("definition"); this.authors = Author.byPrize(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 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 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 @Override public void onClick() { 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: 2.2. Parts Implemented by Taha Emre 37 BKDB Documentation, Release 1.0 public PrizeWinner(int id) { this.id = id; String query = "SELECT year , author_id, prize_id FROM Prize WHERE id=?"; try { PreparedStatement statement = db.prepareStatement(query); statement.setInt(1, ((Integer) this.getId())); ResultSet results = statement.executeQuery(); while (results.next()) { 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(); } catch (SQLException e) { e.printStackTrace(); } } It makes a query to database and fills the object with necessary information with matching id. Getters and Setters: protected Serializable getId() { return id; } public Prize getPrize(){ return prize; } public Author getAuthor(){ return author; } public String getYear(){return this.year;} delete(): public void delete() { try { String query = "DELETE FROM Prize WHERE id=?"; PreparedStatement statement = db.prepareStatement(query); statement.setInt(1, (Integer) this.id); statement.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } } Deletes a given prize from prize table by finding it with its unique id. update(): public void update() { try { String query = "UPDATE Prize SET author_id = ?, prize_id = ?, year = ? " + "WHERE id = ?"; PreparedStatement statement = db.prepareStatement(query); 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.executeUpdate(); statement.close(); 38 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 } catch (SQLException e) { e.printStackTrace(); } } Updates a given prize which has the desired attributes already been changed, by finding it with its unique id. insert(): public void insert() { try { String query = "INSERT INTO Prize (author_id, prize_id, year) " + "VALUES(?, ?, ?)"; PreparedStatement statement = db.prepareStatement(query); statement.setInt(1, (Integer) this.author.getId()); statement.setInt(2, (Integer) this.prize.getId()); statement.setString(3, this.year); statement.executeUpdate(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } } 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 protected Serializable getId() { return id; } @Override protected Model byId() { 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. 2.2. Parts Implemented by Taha Emre 39 BKDB Documentation, Release 1.0 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 public void onClick() { 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=?"; PreparedStatement statement = this.db.prepareStatement(query); statement.setInt(1, ((Integer) this.getId())); ResultSet results = statement.executeQuery(); while (results.next()) { this.name = results.getString("name"); } results.close(); statement.close(); } catch (SQLException e) { throw new UnsupportedOperationException(e.getMessage()); } } Getters and Setters: public String getNationality() { return nationality; } public String getName() { return name; } public int getYear() { 40 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 return year; } public String getDeathYear() { return deathYear; } public List<Book> getBooks() { return books; } public List<Prize> getPrizes() { return prizes; } public void setName(String name) { this.name = name; } public void setId(Serializable id) { 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; } protected Serializable getId() { return id; } Table operations: @Override public void add() { try { String query = "INSERT INTO Author (NAME, year, nationality, deathYear) " + "VALUES(?, ?, ?, ?)"; PreparedStatement statement = db.prepareStatement(query); statement.setString(1, this.name); 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.executeUpdate(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } } @Override public void delete() { try { String query = "DELETE FROM Author WHERE ID=?"; PreparedStatement statement = db.prepareStatement(query); statement.setInt(1, (Integer) this.id); statement.executeUpdate(); 2.2. Parts Implemented by Taha Emre 41 BKDB Documentation, Release 1.0 } catch (SQLException e) { e.printStackTrace(); } } @Override public void update() { try { String query = "UPDATE Author SET name=?, year=?, nationality=?, deathYear=? WHERE id PreparedStatement statement = db.prepareStatement(query); statement.setString(1, this.name); statement.setInt(2, this.year); statement.setString(3, this.nationality); statement.setString(4, this.deathYear); statement.setInt(5, (Integer) this.id); statement.executeUpdate(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } } public static List<Person> search(String name) { List<Person> authors = new LinkedList<Person>(); String query = "SELECT id FROM Author WHERE Name LIKE ?"; try { PreparedStatement statement = db.prepareStatement(query); statement.setString(1, "%" + name + "%"); ResultSet results = statement.executeQuery(); while (results.next()) { authors.add(new Author(results.getInt("id"))); } results.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } return authors; } public static List<Person> all() { List<Person> authors = new LinkedList<Person>(); String query = "SELECT id FROM Author ORDER BY name"; try { PreparedStatement statement = db.prepareStatement(query); ResultSet results = statement.executeQuery(); while (results.next()) { authors.add((Person) new Author(results.getInt("id"))); } results.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } return authors; } byPrize(): 42 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 public static List<Person> byPrize(Prize prize) { List<Person> authors = new LinkedList<Person>(); String query = "SELECT author_id FROM Prize WHERE prize_id = ?"; try { PreparedStatement statement = db.prepareStatement(query); statement.setInt(1, ((Integer) prize.getId())); ResultSet results = statement.executeQuery(); while (results.next()) { authors.add((Person) new Author(results.getInt("author_id"))); } results.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } 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: 2.2. Parts Implemented by Taha Emre 43 BKDB Documentation, Release 1.0 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 protected void populateItem(ListItem<Person> item) { final Person author = item.getModelObject(); item.add(new PersonLink("personLink", author)); item.add(new Link("editLink") { @Override public void onClick() { setResponsePage(new AdminAuthorPage((Author) author)); } }); item.add(new Link("deleteLink") { @Override public void onClick() { 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 public void onSubmit() { inputAuthor.add(); setResponsePage(new AdminAuthorPage()); } }); authorForm.add(new Button("editButton") { 44 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 @Override public void onSubmit() { inputAuthor.update(); setResponsePage(new AdminAuthorPage()); } }); 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)); item.add(new Link("editLink") { @Override public void onClick() { setResponsePage(new AdminPrizePage(prize)); } }); item.add(new Link("deleteLink") { @Override public void onClick() { 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 public void onSubmit() { inputPrize.addPrize(); setResponsePage(new AdminPrizePage()); } }); prizeForm.add(new Button("editButton") { @Override public void onSubmit() { inputPrize.updatePrize(); 2.2. Parts Implemented by Taha Emre 45 BKDB Documentation, Release 1.0 setResponsePage(new AdminPrizePage()); } }); 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())); item.add(new Link("editLink") { @Override public void onClick() { setResponsePage(new AdminPrizeWinnersPage(prizeWinner)); } }); item.add(new Link("deleteLink") { @Override public void onClick() { 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 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 public void onSubmit() { inputWinner.insert(); setResponsePage(new AdminPrizeWinnersPage()); } }); prizeWinnerForm.add(new Button("editButton") { @Override public void onSubmit() { inputWinner.update(); setResponsePage(new AdminPrizeWinnersPage()); } }); 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ı 2.3.1 Database Design 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 2.3. Parts Implemented by Mustafa Safa Özdayı 47 BKDB Documentation, Release 1.0 Figure 2.3: Book, Genre and Site tables are highlighted with red rectangles 48 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 • 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. 2.3. Parts Implemented by Mustafa Safa Özdayı 49 BKDB Documentation, Release 1.0 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 { PreparedStatement statement = db.prepareStatement(query); statement.setString(1, genreName); ResultSet result = statement.executeQuery(); while (result.next()) { id = result.getInt("MatchID"); } result.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } // if not, create a new ID if (id == 0) { String query1 = "SELECT MAX(ID) AS CurMax FROM Genre"; try { PreparedStatement statement = db.prepareStatement(query1); ResultSet result = statement.executeQuery(); while (result.next()) { id = result.getInt("CurMax") + 1; } result.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } } 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 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 name VARCHAR(32) NOT NULL, book_id INTEGER 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" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "name" VARCHAR(32) UNIQUE NOT NULL, "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(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; PreparedStatement statement = db.prepareStatement(query); statement.setString(1, this.name); statement.setString(2, this.ISBN); statement.setString(3, this.type); statement.setString(4, this.language); 2.3. Parts Implemented by Mustafa Safa Özdayı 51 BKDB Documentation, Release 1.0 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) statement.setInt(8, (Integer) this.author.getId()); else statement.setNull(8, java.sql.Types.INTEGER); 18 19 20 21 22 if (this.editor != null) statement.setInt(9, (Integer) this.editor.getId()); else statement.setNull(9, java.sql.Types.INTEGER); 23 24 25 26 27 if (this.translator != null) statement.setInt(10, (Integer) this.translator.getId()); else statement.setNull(10, java.sql.Types.INTEGER); 28 29 30 31 32 if (this.publisher != null) statement.setInt(11, (Integer) this.publisher.getId()); else statement.setNull(11, java.sql.Types.INTEGER); 33 34 35 36 37 statement.executeUpdate(); statement.close(); 38 39 40 } catch (SQLException e) { e.printStackTrace(); } 41 42 43 44 } Adds a book object to the DB. • Delete: 1 2 3 4 5 6 7 8 9 10 11 public void delete() { try { String query = "DELETE FROM book WHERE id = ? "; PreparedStatement statement = db.prepareStatement(query); statement.setInt(1, (Integer) this.id); statement.executeUpdate(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } } Deletes a book object from the DB. • Update: 1 public void update() { 2 try { String query = "UPDATE Book SET Name = ?, ISBN = ?, Type = ?," + "Language = ?, Format = ?, Summary = ?, Date = ?, " + "author_id = ?, editor_id = ?, translator_id = ?," + "publisher_id = ? WHERE ID = ?"; PreparedStatement statement = db.prepareStatement(query); statement.setString(1, this.name); 3 4 5 6 7 8 9 52 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 statement.setString(2, statement.setString(3, statement.setString(4, statement.setString(5, statement.setString(6, statement.setString(7, 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) statement.setInt(8, (Integer) this.author.getId()); else statement.setNull(8, java.sql.Types.INTEGER); 20 21 22 23 24 if (this.editor != null) statement.setInt(9, (Integer) this.editor.getId()); else statement.setNull(9, java.sql.Types.INTEGER); 25 26 27 28 29 if (this.translator != null) statement.setInt(10, (Integer) this.translator.getId()); else statement.setNull(10, java.sql.Types.INTEGER); 30 31 32 33 34 if (this.publisher != null) statement.setInt(11, (Integer) this.publisher.getId()); else statement.setNull(11, java.sql.Types.INTEGER); 35 36 37 38 39 statement.setInt(12, (Integer) this.id); statement.executeUpdate(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } 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 { PreparedStatement statement = db.prepareStatement(query); statement.setString(1, "%" + name + "%"); ResultSet results = statement.executeQuery(); while (results.next()) { 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(); } catch (SQLException e) { e.printStackTrace(); } return books; 20 2.3. Parts Implemented by Mustafa Safa Özdayı 53 BKDB Documentation, Release 1.0 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(?, ?, ?)"; PreparedStatement statement = db.prepareStatement(query); statement.setInt(1, (Integer) Genre.getMatchingID(this.name)); statement.setString(2, this.name); if (this.book != null) statement.setInt(3, (Integer) this.book.getId()); else statement.setNull(3, java.sql.Types.INTEGER); 1 2 3 4 5 6 7 8 9 10 11 statement.executeUpdate(); statement.close(); 12 13 14 } catch (SQLException e) { e.printStackTrace(); } 15 16 17 18 19 } Adds a genre object to the DB. • Delete: 1 2 3 4 5 6 7 8 public void delete() { try { String query = "DELETE FROM Genre WHERE ID = ? AND book_id = ? "; PreparedStatement statement = db.prepareStatement(query); statement.setInt(1, ((Integer) this.id)); statement.setInt(2, (Integer) this.book.getId()); statement.executeUpdate(); statement.close(); 9 } catch (SQLException e) { e.printStackTrace(); } 10 11 12 13 } Deletes a genre object from the DB. • Update: 1 2 3 4 5 6 7 8 9 10 11 12 public void update() { try { String query = "UPDATE GENRE SET NAME = ?, ID = ? WHERE (ID = ? AND book_id = ?) AND (SEL + "WHERE NAME = ?) > 0 "; PreparedStatement statement = db.prepareStatement(query); statement.setString(1, this.name); statement.setInt(2, getMatchingID(this.name)); statement.setInt(3, (Integer) this.id); statement.setInt(4, (Integer) this.book.getId()); statement.setString(5, this.name); statement.executeUpdate(); statement.close(); 13 54 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 } catch (SQLException e) { e.printStackTrace(); } 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 { PreparedStatement statement = db.prepareStatement(query); statement.setString(1, "%" + name + "%"); ResultSet results = statement.executeQuery(); while (results.next()) { genres.add(new Genre(results.getInt("ID"))); 10 } results.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } 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(?, ?)"; PreparedStatement statement = db.prepareStatement(query); statement.setString(1, this.name); statement.setString(2, this.address); statement.executeUpdate(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } } Adds a site object to the DB. • Delete: 1 2 3 4 5 6 7 8 9 public void delete() { try { String query = "DELETE FROM SITE WHERE ID = ? "; PreparedStatement statement = db.prepareStatement(query); statement.setInt(1, ((Integer) this.id)); statement.executeUpdate(); statement.close(); } catch (SQLException e) { e.printStackTrace(); 2.3. Parts Implemented by Mustafa Safa Özdayı 55 BKDB Documentation, Release 1.0 } 10 11 } Deletes a site object from the DB. • Update: 1 2 3 4 5 6 7 8 9 10 11 public void update() { String query = "UPDATE SITE SET NAME = ?, ADRESS= ? Where ID = ? "; try { PreparedStatement statement = this.db.prepareStatement(query); statement.setString(1, this.name); statement.setString(2, this.address); statement.setInt(3, ((Integer) this.getId())); statement.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } 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 { PreparedStatement statement = db.prepareStatement(query); statement.setString(1, "%" + name + "%"); ResultSet results = statement.executeQuery(); while (results.next()) { sites.add(new Site(results.getInt("ID"))); } results.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } 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 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 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 { PreparedStatement statement = this.db.prepareStatement(query); statement.setInt(1, ((Integer) this.getId())); ResultSet results = statement.executeQuery(); while (results.next()) { this.name = results.getString("NAME"); } results.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } 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 protected Model byId() { // 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 { PreparedStatement statement = db.prepareStatement(query); statement.setInt(1, ((Integer) this.getId())); ResultSet results = statement.executeQuery(); while (results.next()) { // should be executed only for once. this.name = results.getString("NAME"); 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"); 2.3. Parts Implemented by Mustafa Safa Özdayı 57 BKDB Documentation, Release 1.0 this.rate = Rate.byBook(this); 25 26 } results.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } 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) { List<Book> books = new LinkedList<Book>(); 3 String query = "SELECT id, author_id FROM Book WHERE id IN (SELECT book_id FROM Genre WHERE I try { PreparedStatement statement = db.prepareStatement(query); statement.setInt(1, ((Integer) genre.getId())); ResultSet results = statement.executeQuery(); while (results.next()) { 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(); } catch (SQLException e) { e.printStackTrace(); } 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) { List<Book> books = new LinkedList<Book>(); 3 String query = "SELECT id, author_id FROM book WHERE author_id = ?"; try { PreparedStatement statement = db.prepareStatement(query); statement.setInt(1, ((Integer) author.getId())); ResultSet results = statement.executeQuery(); while (results.next()) { 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(); } catch (SQLException e) { e.printStackTrace(); } return books; 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 } 58 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 Returns the list of the books that have the supplied author as their author. • byEditor: 1 2 public static List<Book> byEditor(Editor editor) { List<Book> books = new LinkedList<Book>(); 3 String query = "SELECT id, author_id FROM book WHERE editor_id = ?"; try { PreparedStatement statement = db.prepareStatement(query); statement.setInt(1, ((Integer) editor.getId())); ResultSet results = statement.executeQuery(); while (results.next()) { 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(); } catch (SQLException e) { e.printStackTrace(); } 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) { List<Book> books = new LinkedList<Book>(); 3 String query = "SELECT id, author_id FROM book WHERE translator_id = ?"; try { PreparedStatement statement = db.prepareStatement(query); statement.setInt(1, ((Integer) translator.getId())); ResultSet results = statement.executeQuery(); while (results.next()) { 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(); } catch (SQLException e) { e.printStackTrace(); } 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) { List<Book> books = new LinkedList<Book>(); 3 4 5 6 7 8 9 String query = "SELECT id, author_id FROM book WHERE publisher_id = ?"; try { PreparedStatement statement = db.prepareStatement(query); statement.setInt(1, ((Integer) publisher.getId())); ResultSet results = statement.executeQuery(); while (results.next()) { 2.3. Parts Implemented by Mustafa Safa Özdayı 59 BKDB Documentation, Release 1.0 Book book = new Book(results.getInt("id")); book.setAuthor(new Author(results.getInt("author_id"))); book.setRate(Rate.byBook(book)); books.add(book); 10 11 12 13 } results.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } 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() { List<Book> books = new LinkedList<Book>(); 3 String query = "SELECT id FROM Book ORDER BY name"; try { PreparedStatement statement = db.prepareStatement(query); ResultSet results = statement.executeQuery(); while (results.next()) { books.add(new Book(results.getInt("id"))); } results.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } 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 Serializable id; 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 { PreparedStatement statement = db.prepareStatement(query); statement.setInt(1, ((Integer) this.id)); ResultSet results = statement.executeQuery(); while (results.next()) { this.name = results.getString("NAME"); } results.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); 60 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 } 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 protected Model byId() { String query = "SELECT DISTINCT NAME, book_id FROM Genre 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.book = new Book(results.getInt("book_id")); } results.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } 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() { List<Genre> genres = new LinkedList<Genre>(); String query = "SELECT DISTINCT ID FROM Genre ORDER BY NAME ASC"; try { Statement statement = db.createStatement(); ResultSet results = statement.executeQuery(query); while (results.next()) { genres.add(new Genre(results.getInt("ID"))); } results.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } 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) { List<Genre> genres = new LinkedList<Genre>(); 3 4 5 6 7 8 9 10 11 String query = "SELECT ID FROM Genre WHERE book_id = ?"; try { PreparedStatement statement = db.prepareStatement(query); statement.setInt(1, ((Integer) book.getId())); ResultSet results = statement.executeQuery(); while (results.next()) { genres.add(new Genre(results.getInt("ID"))); } 2.3. Parts Implemented by Mustafa Safa Özdayı 61 BKDB Documentation, Release 1.0 results.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } 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 { PreparedStatement statement = db.prepareStatement(query); statement.setString(1, genreName); ResultSet result = statement.executeQuery(); while (result.next()) { id = result.getInt("MatchID"); } result.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } // 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); ResultSet result = statement.executeQuery(); while (result.next()) { id = result.getInt("CurMax") + 1; } result.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } 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 Serializable id; private String name; private String address; • Constructor: 62 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 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 protected Model byId() { String query = "SELECT ID, NAME, ADRESS FROM SITE WHERE ID= ?"; try { PreparedStatement statement = this.db.prepareStatement(query); statement.setInt(1, ((Integer) this.getId())); ResultSet results = statement.executeQuery(); while (results.next()) { this.name = results.getString("NAME"); this.address = results.getString("ADRESS"); } results.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } 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 { PreparedStatement statement = db.prepareStatement(query); ResultSet results = statement.executeQuery(); while (results.next()) { sites.add(new Site(results.getInt("ID"))); } results.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } 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; 2.3. Parts Implemented by Mustafa Safa Özdayı 63 BKDB Documentation, Release 1.0 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 { PreparedStatement statement = db.prepareStatement(query); ResultSet result = statement.executeQuery(); while (result.next()) { bookCount = result.getInt("Count"); } result.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } 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 { PreparedStatement statement = db.prepareStatement(query); ResultSet result = statement.executeQuery(); while (result.next()) { userCount = result.getInt("Count"); } result.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } 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 { PreparedStatement statement = db.prepareStatement(query); ResultSet result = statement.executeQuery(); while (result.next()) { genreCount = result.getInt("Count"); } result.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } return genreCount; } Returns the # of distinct genres that exist in the DB. 64 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 • getAuthorCount: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 String query = "SELECT COUNT(*) AS Count FROM Author"; try { PreparedStatement statement = db.prepareStatement(query); ResultSet result = statement.executeQuery(); while (result.next()) { authorCount = result.getInt("Count"); } result.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } 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 { PreparedStatement statement = db.prepareStatement(query); ResultSet result = statement.executeQuery(); while (result.next()) { rateCount = result.getInt("Count"); } result.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } 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 { PreparedStatement statement = db.prepareStatement(query); ResultSet result = statement.executeQuery(); while (result.next()) { avgRate = result.getInt("Average"); } result.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } 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 { PreparedStatement statement = db.prepareStatement(query); ResultSet result = statement.executeQuery(); 2.3. Parts Implemented by Mustafa Safa Özdayı 65 BKDB Documentation, Release 1.0 while (result.next()) { prizeCount = result.getInt("Count"); } result.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } 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 { PreparedStatement statement = db.prepareStatement(query); ResultSet result = statement.executeQuery(); while (result.next()) { reviewCount = result.getInt("Count"); } result.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } 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 { PreparedStatement statement = db.prepareStatement(query); ResultSet result = statement.executeQuery(); while (result.next()) { publisherCount = result.getInt("Count"); } result.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } 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 { PreparedStatement statement = db.prepareStatement(query); ResultSet result = statement.executeQuery(); while (result.next()) { mostExpensive = result.getString("Expensive"); } result.close(); statement.close(); 66 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 } catch (SQLException e) { e.printStackTrace(); } 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 { PreparedStatement statement = db.prepareStatement(query); ResultSet result = statement.executeQuery(); while (result.next()) { bestRated = result.getString("Best"); } result.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } 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)); item.add(new Link("editLink") { @Override public void onClick() { 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 2.3. Parts Implemented by Mustafa Safa Özdayı 67 BKDB Documentation, Release 1.0 1 2 3 4 5 6 7 item.add(new Link("deleteLink") { @Override public void onClick() { 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(), new ChoiceRenderer<Person>("name", "id"))); bookForm.add(new DropDownChoice<Publisher>("publisher", Publisher.all(), new ChoiceRenderer<Publisher>("name", "id"))); bookForm.add(new DropDownChoice<Person>("editor", Editor.all(), new ChoiceRenderer<Person>("name", "id"))); bookForm.add(new DropDownChoice<Person>("translator", Translator.all(), new ChoiceRenderer<Person>("name", "id"))); 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 public void onSubmit() { 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 public void onClick() { 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 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 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. 2.3. Parts Implemented by Mustafa Safa Özdayı 69 BKDB Documentation, Release 1.0 1 2 3 4 5 6 7 8 9 10 Form readFavForm = new Form("readFavForm"); readFavForm.add(new Button("readButton") { public void onSubmit() { if (book.isRead()) { User.getCurrentUser().unread(book); } else { User.getCurrentUser().read(book); } setResponsePage(new BookPage(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") { public void onSubmit() { if (book.isFav()) { User.getCurrentUser().unfav(book); } else { User.getCurrentUser().fav(book); } setResponsePage(new BookPage(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 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 • 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 public void onClick() { 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 public void onSubmit() { 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 public void onClick() { 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 public void onClick() { Genre genre = (Genre) getModel().getObject(); List<Book> books = Book.byGenre(genre); 2.3. Parts Implemented by Mustafa Safa Özdayı 71 BKDB Documentation, Release 1.0 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 item.add(new Link("deleteLink") { @Override public void onClick() { 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 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 1 2 3 4 5 6 7 siteForm.add(new Button("addButton") { @Override public void onSubmit() { 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 <li class="list-group-item" > <span class="badge"># of Total Authors</span> <span wicket:id = "authorCount"> </span> </li> 11 12 13 14 15 <li class="list-group-item" > <span class="badge"># of Different Genres</span> <span wicket:id = "genreCount"> </span> </li> 16 17 18 19 20 <li class="list-group-item" > <span class="badge"># of Total Publishers</span> <span wicket:id = "publisherCount"> </span> </li> 21 2.3. Parts Implemented by Mustafa Safa Özdayı 73 BKDB Documentation, Release 1.0 22 23 24 25 <li class="list-group-item" > <span class="badge"># of Total Prizes Earned By Authors</span> <span wicket:id = "prizeCount"> </span> </li> 26 27 28 29 30 <li class="list-group-item" > <span class="badge"># of Total Members</span> <span wicket:id = "userCount"> </span> </li> 31 <li class="list-group-item" > 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 <li class="list-group-item" > <span class="badge">Average Rate of # Total Votes </span> <span wicket:id = "avgRate"> </span> </li> 42 43 44 45 46 <li class="list-group-item" > <span class="badge"># of Total Reviews by Members</span> <span wicket:id = "reviewCount"> </span> </li> 47 48 49 50 51 <li class="list-group-item" > <span class="badge">The Best Rated Book</span> <span wicket:id = "bestRated"> </span> </li> 52 53 54 55 56 <li class="list-group-item" > <span class="badge">The Most Expensive Book</span> <span wicket:id = "mostExpensive"> </span> </li> 57 58 </ul> 2.4 Parts Implemented by Abdurrahman NAMLI 2.4.1 Database Design 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 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 • 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 ( id INTEGER PRIMARY KEY AUTOINCREMENT, book_id INTEGER NOT NULL, value INTEGER NOT NULL DEFAULT 0, user_id INTEGER NOT NULL, 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 ( id INTEGER PRIMARY KEY AUTOINCREMENT, 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 ( user_id INTEGER NOT NULL, book_id INTEGER NOT NULL, 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, 2.4. Parts Implemented by Abdurrahman NAMLI 75 BKDB Documentation, Release 1.0 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 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 @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) { throw new UnsupportedOperationException(e.getMessage()); } try { String jdbcURL = "jdbc:sqlite:" + dbFilePath; db = DriverManager.getConnection(jdbcURL); } catch (SQLException ex) { throw new UnsupportedOperationException(ex.getMessage()); } } • It takes path and connects. initializeDB( ): 2.4. Parts Implemented by Abdurrahman NAMLI 77 BKDB Documentation, Release 1.0 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) { e.printStackTrace(); } try { Statement statement = db.createStatement(); statement.executeUpdate(query); statement.close(); PreparedStatement constraintStatement = db.prepareStatement("PRAGMA foreign_keys constraintStatement.execute(); constraintStatement.close(); } catch (SQLException e) { e.printStackTrace(); } } • 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 public void onClick() { 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 public void onClick() { 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 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 }; 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 public void onClick() { 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 public void onClick() { 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 public void onClick() { 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 public void onClick() { 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") { 2.4. Parts Implemented by Abdurrahman NAMLI 79 BKDB Documentation, Release 1.0 @Override public void onSubmit() { setResponsePage(new PersonsPage(Author.search(searchInput))); } }); searchForm.add(new Button("searchBookButton") { @Override public void onSubmit() { 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 public void onClick() { this.setResponsePage(new AdminPage()); } }); this.add(new Link("authorPageLink") { @Override public void onClick() { this.setResponsePage(new AdminAuthorPage()); } }); this.add(new Link("bookPageLink") { @Override public void onClick() { this.setResponsePage(new AdminBookPage()); } }); this.add(new Link("editorPageLink") { @Override public void onClick() { this.setResponsePage(new AdminEditorPage()); } }); this.add(new Link("translatorPageLink") { @Override public void onClick() { this.setResponsePage(new AdminTranslatorPage()); } }); this.add(new Link("pricePageLink") { @Override public void onClick() { this.setResponsePage(new AdminPricePage()); } }); this.add(new Link("sitePageLink") { @Override public void onClick() { this.setResponsePage(new AdminSitePage()); } 80 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 }); this.add(new Link("prizePageLink") { @Override public void onClick() { this.setResponsePage(new AdminPrizePage()); } }); this.add(new Link("prizeWinnerPageLink") { @Override public void onClick(){ this.setResponsePage(new AdminPrizeWinnersPage());} }); this.add(new Link("publisherPageLink") { @Override public void onClick() { this.setResponsePage(new AdminPublisherPage()); } }); this.add(new Link("ratePageLink") { @Override public void onClick() { this.setResponsePage(new AdminRatePage()); } }); this.add(new Link("genrePageLink") { @Override public void onClick() { this.setResponsePage(new AdminGenrePage()); } }); this.add(new Link("userPageLink") { @Override public void onClick() { 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( ): 2.4. Parts Implemented by Abdurrahman NAMLI 81 BKDB Documentation, Release 1.0 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 { PreparedStatement statement = db.prepareStatement(query); ResultSet results = statement.executeQuery(); while (results.next()) { Book book = new Book(results.getInt("book_id")); book.setAuthor(new Author(results.getInt("author_id"))); Rate rate = new Rate(); rate.setAverageRate(results.getFloat("average")); book.setRate(rate); books.add(book); } results.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } 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 { PreparedStatement statement = this.db.prepareStatement(query); statement.setInt(1, ((Integer) this.getId())); ResultSet results = statement.executeQuery(); while (results.next()) { this.name = results.getString("name"); } results.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } } Builds an User object using the supplied id from the DB. 82 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 Getters and Setters: public String getName() { return name; } public void setName(String name) { this.name = name; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public List<Book> getBooks() { books = new LinkedList<Book>(); String query = "SELECT book_id, author_id FROM UserBooks JOIN Book ON book.id = UserBooks try { PreparedStatement statement = db.prepareStatement(query); statement.setInt(1, ((Integer) User.getCurrentUser().getId())); ResultSet results = statement.executeQuery(); while (results.next()) { Book book = new Book(results.getInt("book_id")); book.setAuthor(new Author(results.getInt("author_id"))); book.setRate(Rate.byBook(book)); books.add(book); } results.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } return books; } protected Serializable getId() { 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) { e.printStackTrace(); } 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: 2.4. Parts Implemented by Abdurrahman NAMLI 83 BKDB Documentation, Release 1.0 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() { AuthenticatedWebSession session = WicketApplicationWebSession.get(); 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 { PreparedStatement statement = db.prepareStatement(query); statement.setString(1, name); statement.setString(2, hash(password)); ResultSet results = statement.executeQuery(); while (results.next()) { id = results.getInt("id"); } results.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } if (id >= 0) { // Id of ADMIN is 0. Id of USERs are positive. ADMIN is a USER as well. AuthenticatedWebSession session = WicketApplicationWebSession.get(); 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() { AuthenticatedWebSession session = WicketApplicationWebSession.get(); Integer id = (Integer) session.getAttribute("id"); 84 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 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(); } } public void update() { try { String query = "UPDATE User SET name = ?, email = ?, hashedPassword = ? WHERE id = ?" PreparedStatement statement = db.prepareStatement(query); statement.setString(1, this.name); statement.setString(2, this.email); statement.setString(3, this.hashedPassword); statement.setInt(4, (Integer) this.id); statement.executeUpdate(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } } • After the user changed it’s information, update function is invoked to make a query to apply changes in database. other query functions: public void delete() { try { String query = "DELETE FROM User WHERE id = ?"; PreparedStatement statement = db.prepareStatement(query); statement.setInt(1, (Integer) this.id); statement.executeUpdate(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } } public static List<User> search(String name) { List<User> users = new LinkedList<User>(); String query = "SELECT id, email FROM User WHERE name LIKE ?"; try { PreparedStatement statement = db.prepareStatement(query); statement.setString(1, "%" + name + "%"); ResultSet results = statement.executeQuery(); while (results.next()) { User user = new User(results.getInt("id")); user.setEmail(results.getString("email")); users.add(user); } results.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); 2.4. Parts Implemented by Abdurrahman NAMLI 85 BKDB Documentation, Release 1.0 } return users; } public static List<User> all() { List<User> users = new LinkedList<User>(); String query = "SELECT id, email FROM User ORDER BY name"; try { PreparedStatement statement = db.prepareStatement(query); ResultSet results = statement.executeQuery(); while (results.next()) { User user = new User(results.getInt("id")); user.setEmail(results.getString("email")); users.add(user); } results.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } 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=?) )"; PreparedStatement statement = db.prepareStatement(query); Integer user_id = (Integer) User.getCurrentUser().getId(); Integer book_id = (Integer) book.getId(); statement.setInt(1, user_id); statement.setInt(2, book_id); statement.setInt(3, user_id); statement.setInt(4, book_id); statement.executeUpdate(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } } public void unfav(Book book) { try { String query = "INSERT OR REPLACE INTO UserBooks (user_id, book_id, isFav, isRead) VA " (?, ?, 0, (SELECT isRead FROM UserBooks WHERE user_id=? AND book_id=?) )"; PreparedStatement statement = db.prepareStatement(query); Integer user_id = (Integer) User.getCurrentUser().getId(); Integer book_id = (Integer) book.getId(); statement.setInt(1, user_id); statement.setInt(2, book_id); statement.setInt(3, user_id); statement.setInt(4, book_id); statement.executeUpdate(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } } public boolean isFav(Book book) { 86 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 Boolean value = false; String query = "SELECT isFav FROM UserBooks WHERE user_id = ? AND book_id = ?"; try { PreparedStatement statement = db.prepareStatement(query); statement.setInt(1, ((Integer) this.getId())); statement.setInt(2, ((Integer) book.getId())); ResultSet results = statement.executeQuery(); while (results.next()) { value = results.getBoolean("isFav"); } results.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } 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 { String query = "INSERT OR REPLACE INTO UserBooks (user_id, book_id, isFav, isRead) VA " (?, ?, (SELECT isFav FROM UserBooks WHERE user_id=? AND book_id=?), 1 )"; PreparedStatement statement = db.prepareStatement(query); Integer user_id = (Integer) User.getCurrentUser().getId(); Integer book_id = (Integer) book.getId(); statement.setInt(1, user_id); statement.setInt(2, book_id); statement.setInt(3, user_id); statement.setInt(4, book_id); statement.executeUpdate(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } } public void unread(Book book) { try { String query = "INSERT OR REPLACE INTO UserBooks (user_id, book_id, isFav, isRead) VA " (?, ?, (SELECT isFav FROM UserBooks WHERE user_id=? AND book_id=?), 0 )"; PreparedStatement statement = db.prepareStatement(query); Integer user_id = (Integer) User.getCurrentUser().getId(); Integer book_id = (Integer) book.getId(); statement.setInt(1, user_id); statement.setInt(2, book_id); statement.setInt(3, user_id); statement.setInt(4, book_id); statement.executeUpdate(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } } public boolean isRead(Book book) { Boolean value = false; String query = "SELECT isRead FROM UserBooks WHERE user_id = ? AND book_id = ?"; 2.4. Parts Implemented by Abdurrahman NAMLI 87 BKDB Documentation, Release 1.0 try { PreparedStatement statement = db.prepareStatement(query); statement.setInt(1, ((Integer) this.getId())); statement.setInt(2, ((Integer) book.getId())); ResultSet results = statement.executeQuery(); while (results.next()) { value = results.getBoolean("isRead"); } results.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } 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 public void onSubmit() { 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 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 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())); item.add(new Link("editLink") { @Override public void onClick() { setResponsePage(new AdminUserPage(user)); } }); item.add(new Link("deleteLink") { @Override public void onClick() { 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 public void onClick() { this.setResponsePage(new StatisticsPage()); } }); this.add(new Link("resetLink") { @Override public void onClick() { Model.initializeDB(); this.setResponsePage(new AdminPage()); } }); } } • There is stats link to statistics page which include statistics about application. And also there is reset link that resets database. 2.4. Parts Implemented by Abdurrahman NAMLI 89 BKDB Documentation, Release 1.0 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) { Rate rate = new Rate(); try { String query = "SELECT AVG(value) FROM rate WHERE book_id = ? GROUP BY book_id"; PreparedStatement statement = db.prepareStatement(query); Integer book_id = (Integer) book.getId(); statement.setInt(1, book_id); ResultSet results = statement.executeQuery(); while (results.next()) { rate.averageRate = results.getFloat(1); } results.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } 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 = ?"; PreparedStatement statement = db.prepareStatement(query); statement.setInt(1, (Integer) book.getId()); statement.executeUpdate(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } } • It is used in admin panel to reset rates of a book by admin. 90 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 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 public void onSubmit() { Rate rate = new Rate(); 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 public void onSubmit() { 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 BKDB Documentation, Release 1.0 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 does not require any foreign key since it stores independent data • It has 1:n relation with book table • The variable name must be unique • The variable deathYear has ‘?’ as default value The structure of the table is like below: DROP TABLE IF EXISTS "Translator"; CREATE TABLE "Translator" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "name" VARCHAR(32) UNIQUE NOT NULL, year INTEGER, nationality VARCHAR(32), deathYear varchar(4) DEFAULT "?" ); 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 does not require any foreign key since it stores independent data • It has 1:n relation with book table • The variable name must be unique • The variable deathYear has ‘?’ as default value The structure of the table is like below: DROP TABLE IF EXISTS "Editor"; CREATE TABLE "Editor" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "name" VARCHAR(32) UNIQUE NOT NULL, year INTEGER, nationality VARCHAR(32), deathYear varchar(4) DEFAULT "?" ); 92 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 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() { this.id = -1; // For creating empty objects } public Editor(Serializable id) { this.id = id; try { String query = "SELECT name FROM Editor WHERE id=?"; PreparedStatement statement = db.prepareStatement(query); statement.setInt(1, ((Integer) this.getId())); ResultSet results = statement.executeQuery(); while (results.next()) { this.name = results.getString("name"); } results.close(); statement.close(); } catch (SQLException e) { throw new UnsupportedOperationException(e.getMessage()); } } @Override public String getNationality() { return nationality; } @Override public int getYear() { return year; } @Override protected Serializable getId() { return id; } public void setName(String name) { this.name = name; } public void setId(Serializable id) { this.id = id; } public void setYear(int year) { this.year = year; } public void setNationality(String nationality) { 2.7. 2 Software Design 93 BKDB Documentation, Release 1.0 this.nationality = nationality; } public void setDeathYear(String deathYear) { this.deathYear = deathYear; } @Override protected Model byId() { String query = "SELECT NAME, year, nationality, deathYear FROM Editor 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.year = results.getInt("year"); this.nationality = results.getString("nationality"); this.deathYear = results.getString("deathYear"); this.books = Book.byEditor(this); } results.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } return this; } @Override public String getName() { return name; } @Override public List<Book> getBooks() { return books; } @Override public String getDeathYear() { return deathYear; } @Override public void add() { try { String query = "INSERT INTO Editor (NAME, year, nationality, deathYear) " + "VALUES(?, ?, ?, ?)"; PreparedStatement statement = db.prepareStatement(query); statement.setString(1, this.name); statement.setInt(2, this.year); statement.setString(3, this.nationality); statement.setString(4, this.deathYear); statement.executeUpdate(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } } @Override public void delete() { 94 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 try { String query = "DELETE FROM Editor WHERE ID=?"; PreparedStatement statement = db.prepareStatement(query); statement.setInt(1, (Integer) this.id); statement.executeUpdate(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } } @Override public void update() { try { String query = "UPDATE Editor SET name=?, year=?, nationality=?, deathYear=? WHERE id PreparedStatement statement = db.prepareStatement(query); statement.setString(1, this.name); statement.setInt(2, this.year); statement.setString(3, this.nationality); statement.setString(4, this.deathYear); statement.setInt(5, (Integer) this.id); statement.executeUpdate(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } } public static List<Person> all() { List<Person> editors = new LinkedList<Person>(); String query = "SELECT id FROM Editor ORDER BY name"; try { PreparedStatement statement = db.prepareStatement(query); ResultSet results = statement.executeQuery(); while (results.next()) { editors.add((Person) new Editor(results.getInt("id"))); } results.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } 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() { this.id = -1; // For creating empty objects } 2.7. 2 Software Design 95 BKDB Documentation, Release 1.0 public Translator(Serializable id) { this.id = id; try { String query = "SELECT name FROM Translator WHERE id=?"; PreparedStatement statement = this.db.prepareStatement(query); statement.setInt(1, ((Integer) this.getId())); ResultSet results = statement.executeQuery(); while (results.next()) { this.name = results.getString("name"); } results.close(); statement.close(); } catch (SQLException e) { throw new UnsupportedOperationException(e.getMessage()); } } @Override public String getDeathYear() { return deathYear; } @Override public String getNationality() { return nationality; } @Override public int getYear() { return year; } @Override protected Serializable getId() { return id; } public void setName(String name) { this.name = name; } public void setId(Serializable id) { 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; } @Override protected Model byId() { String query = "SELECT NAME, year, nationality, deathYear FROM Translator WHERE id=?"; try { PreparedStatement statement = db.prepareStatement(query); 96 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 statement.setInt(1, ((Integer) this.getId())); ResultSet results = statement.executeQuery(); while (results.next()) { this.name = results.getString("NAME"); this.year = results.getInt("year"); this.nationality = results.getString("nationality"); this.deathYear = results.getString("deathYear"); this.books = Book.byTranslator(this); } results.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } return this; } @Override public String getName() { return name; } @Override public List<Book> getBooks() { return books; } @Override public void add() { try { String query = "INSERT INTO Translator (NAME, year, nationality, deathYear) " + "VALUES(?, ?, ?, ?)"; PreparedStatement statement = db.prepareStatement(query); statement.setString(1, this.name); statement.setInt(2, this.year); statement.setString(3, this.nationality); statement.setString(4, this.deathYear); statement.executeUpdate(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } } @Override public void delete() { try { String query = "DELETE FROM Translator WHERE ID=?"; PreparedStatement statement = db.prepareStatement(query); statement.setInt(1, (Integer) this.id); statement.executeUpdate(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } } @Override public void update() { try { String query = "UPDATE Translator SET name=?, year=?, nationality=?, deathYear=? WHER PreparedStatement statement = db.prepareStatement(query); statement.setString(1, this.name); 2.7. 2 Software Design 97 BKDB Documentation, Release 1.0 statement.setInt(2, this.year); statement.setString(3, this.nationality); statement.setString(4, this.deathYear); statement.setInt(5, (Integer) this.id); statement.executeUpdate(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } } public static List<Person> all() { List<Person> translators = new LinkedList<Person>(); String query = "SELECT id FROM Translator ORDER BY name"; try { PreparedStatement statement = db.prepareStatement(query); ResultSet results = statement.executeQuery(); while (results.next()) { translators.add((Person) new Translator(results.getInt("id"))); } results.close(); statement.close(); } catch (SQLException e) { e.printStackTrace(); } 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 protected void populateItem(ListItem<Person> item) { final Person editor = item.getModelObject(); item.add(new PersonLink("personLink", editor)); item.add(new Link("editLink") { @Override public void onClick() { setResponsePage(new AdminEditorPage((Editor) editor)); } }); item.add(new Link("deleteLink") { @Override public void onClick() { editor.delete(); setResponsePage(new AdminEditorPage()); } }); 98 Chapter 2. Developer Guide BKDB Documentation, Release 1.0 } }); 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 public void onSubmit() { inputEditor.add(); setResponsePage(new AdminEditorPage()); } }); editorForm.add(new Button("editButton") { @Override public void onSubmit() { inputEditor.update(); setResponsePage(new AdminEditorPage()); } }); 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 protected void populateItem(ListItem<Person> item) { final Person translator = item.getModelObject(); item.add(new PersonLink("personLink", translator)); item.add(new Link("editLink") { @Override public void onClick() { setResponsePage(new AdminTranslatorPage((Translator) translator)); } }); item.add(new Link("deleteLink") { @Override public void onClick() { translator.delete(); setResponsePage(new AdminTranslatorPage()); } }); } }); 2.7. 2 Software Design 99 BKDB Documentation, Release 1.0 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 public void onSubmit() { inputTranslator.add(); setResponsePage(new AdminTranslatorPage()); } }); translatorForm.add(new Button("editButton") { @Override public void onSubmit() { inputTranslator.update(); setResponsePage(new AdminTranslatorPage()); } }); this.add(translatorForm); } 100 Chapter 2. Developer Guide