The Library Application
All of the JDBC code to create the Library database, connect to it, query it, and otherwise add to or update it is contained within the Database class. This class is made a singleton by setting the constructor to private and adding a static getInstance method. The Database class is part of the library.data package within our application project, along with the Book and Patron classes, which match their respective database tables.
In JavaFX, variables are declared with reverse syntax compared with Java. For instance, the following line declares a variable of type String:
var name: String;
The following declares and assigns a variable in JavaFX:
var name: String = "Eric Bruno";
In our application, Library is the main class, which contains the JavaFX components, layout, and code for the main application window. In this class and others that need to access the database, an reference the singleton Database object is added:
public class Library {
public-read var listviewBooks: javafx.scene.control.ListView;
...
var db: Database = Database.getInstance();
...
}
The database is used to populate the Books and Patrons lists, and is quite simple to do. For instance, the code below populates the Books list using the book's title, author, and checked-out status:
function populateBooksListview(): Void {
// Get the latest list of books
var bookArray: Book[] = db.getBooks();
// Add books to the listview
listviewBooks.items = null;
for ( book in bookArray ) {
var entry: String =
"\"{book.name}\" (author: {book.author}";
if ( book.checkedOut > 0 ) {
entry = "CHECKED OUT: {entry}"
}
insert entry into listviewBooks.items;
}
}
Let's look at the query behind the getBooks method. To make it easier to execute queries in JDBC and ensure all of the resources are cleaned-up, I've created a class called QueryTool (available for downloadhere]). It handles getting a JDBC Connection, which works with connection pools as well, and it ensures that transactions are committed and all resources are cleaned up when the query is complete. Example 3 shows QueryTool in action, returning a list of all of the books in our library.
public Book[] getBooks() {
QueryTool query = null;
try {
query = new QueryTool(con);
String req =
"SELECT * FROM APP.BOOK ORDER BY NAME";
PreparedStatement ps = query.setQuery( req );
ResultSet rs = query.exec();
Vector bookVect = new Vector();
while ( rs.next() ) {
Book book = new Book();
book.id = rs.getInt("ID");
book.name = rs.getString("NAME");
book.author = rs.getString("AUTHOR");
book.publisher = rs.getString("PUBLISHER");
book.checkedOut = rs.getInt("CHECKEDOUT");
book.active = rs.getInt("ACTIVE");
if ( book.active > 0 )
bookVect.add(book);
}
// Convert the Vector to an array
Book[] books = new Book[bookVect.size()];
bookVect.toArray(books);
return books;
}
catch ( Exception e ) {
e.printStackTrace();
}
finally {
query.close();
}
return null;
}
The QueryTool object is declared outside of the try..catch..finally scope as the close method is called in the finally block. This ensures that the Statement and ResultSet are closed even if the query raises an Exception. The SQL is straightforward, where all books are selected from the BOOK table, sorted by the BOOK.NAME column (the book title). Each active book (a book may be marked inactive if it's never returned, say) is added to a Vector, which is converted to an array and returned to the caller (the JavaFX Script code in this case). For instance, this array is stored and displayed in the Books list in the main Library window.
To borrow a book, you must select an available book from the Books list, a patron from the Patrons list, and click on the button labeled Checkout Book (see Figure 10). An alert will pop up informing you whether or not the book was successfully checked out.
The code that processes the button click gets the selected book and patron objects, verifies that the book can be borrowed, and calls the database to borrow it (see Listing 2). The JDBC code to check out the book updates the BOOK record to set the CHECKEDOUT column to the borrowing patron's ID, and adds a row to the CHECKEDOUT table with both the patron and book ids (see Listing 3).
function btnCheckoutAction(): Void {
// Get the selected book
var selected: Integer = listviewBooks.selectedIndex;
if (selected == -1)
return;
var book: Book = bookArray[selected];
// Get the selected patron
selected = listviewPatrons.selectedIndex;
if (selected == -1)
return;
var patron: Patron = patronArray[selected];
// Checkout the book and update the books listview
if (db.checkoutBook(book.id, patron.id) == true) {
// Update the books listview to show the book checked out
populateBooksListview();
// Open a small window to verify the checkout
var alert: AlertUser = AlertUser {
style: StageStyle.TRANSPARENT
text: "Book checked out successfully"
}
} else {
// Open a small window to show it failed
var alert: AlertUser = AlertUser {
style: StageStyle.TRANSPARENT
text: "Book was not checked out!"
color: Color.RED
}
}
}
Notice that the Overdue Books list shows one book, borrowed by our very own Jon Erickson in this made-up example, and that books that are borrowed are displayed in the list with the text "CHECK OUT" preceding the title. These are all elements that reflect the state of the database.
public boolean checkoutBook(int bookid, int patronid) {
Book book = getBook(bookid);
if ( book == null )
return false;
Patron patron = getPatron(patronid);
if ( patron == null )
return false;
// Is the book already checked out or lost?
if ( book.checkedOut > 0 || book.active == 0)
return false;
// Is the patron in good standing?
if ( patron.active == 0 )
return false;
// Calculate the due date (today + two weeks)
Calendar cal = Calendar.getInstance();
cal.add(Calendar.DAY_OF_MONTH, 14);
int day = cal.get(Calendar.DAY_OF_MONTH);
int month = cal.get(Calendar.MONTH)+1;
int year = cal.get(Calendar.YEAR);
// Add the check-out record
QueryTool query = null;
try {
query = new QueryTool(con);
String req = "INSERT INTO APP.CHECKEDOUT " +
"(BOOKID, PATRONID, MONTHDUE, DAYDUE, YEARDUE) " +
"VALUES(?,?,?,?,?)";
PreparedStatement ps = query.setQuery(req);
ps.setInt(1, bookid);
ps.setInt(2, patronid);
ps.setInt(3, month);
ps.setInt(4, day);
ps.setInt(5, year);
query.exec();
}
catch ( Exception e ) {
e.printStackTrace();
return false;
}
finally {
query.close();
}
// Update the book record (checkedout field is the patron id)
try {
query = new QueryTool(con);
String req =
"UPDATE APP.BOOK SET \"CHECKEDOUT\"=" + patronid +
" WHERE \"ID\"=" + bookid;
query.setQuery(req);
query.exec();
}
catch ( Exception e ) {
e.printStackTrace();
return false;
}
finally {
query.close();
}
return true;
}
When a book is returned, the user clicks on the Return Book button. As a result, a second window is displayed showing all of the currently borrowed books (see Example 4).
function btnReturnBookAction(): Void {
// Open a new window to return a book
returnBook = ReturnBook {
onClose: function () {
returnBook = null;
populateBooksListview();
populateOverdueListview();
}
}
}
The ReturnBook class extends Stage (see Listing 4); instantiating it opens a window as a result (see Figure 11). The init block creates the components of the Scene, which are arranged in a panel according to their x and y coordinates. Clicking on the button labeled Return updates the database and closes the child window.
package libraryapp;
import javafx.stage.Stage;
import libraryapp.data.*;
import javafx.scene.paint.Color;
import javafx.stage.StageStyle;
public class ReturnBook extends Stage {
override var title = "Return Borrowed Books";
public-read var label: javafx.scene.control.Label;
public-read var btnReturn: javafx.scene.control.Button;
public-read var listviewBooks: javafx.scene.control.ListView;
var db: Database = Database.getInstance();
var bookArray: Book[];
function populateBooksListview() : Void {
var allBooks: Book[] = db.getBooks();
// Add only checked-out books to the listview
listviewBooks.items = null;
bookArray = null;
for ( book in allBooks ) {
if ( book.checkedOut > 0 ) {
// The checkedout field is the patron id
var patron: Patron =
db.getPatron(book.checkedOut);
var duedate: String = db.getDueDate(book.id);
var entry: String =
"\"{book.name}\" (borrower: ";
entry = entry.concat(
"{patron.firstName} {patron.lastName}");
entry = entry.concat(
") Due: {duedate}");
insert entry into listviewBooks.items;
insert book into bookArray;
}
}
}
init {
label = javafx.scene.control.Label {
layoutX: 6.0
layoutY: 6.0
text: "Checked out books:"
};
btnReturn = javafx.scene.control.Button {
layoutX: 413.0
layoutY: 227.0
text: "Return"
action: btnReturnAction
};
listviewBooks = javafx.scene.control.ListView {
layoutX: 6.0
layoutY: 27.0
width: 465.0
height: 194.0
layoutInfo: javafx.scene.layout.LayoutInfo {
width: bind listviewBooks.width
height: bind listviewBooks.height
}
};
scene = javafx.scene.Scene {
width: 480.0
height: 259.0
content: javafx.scene.layout.Panel {
content: getDesignRootNodes ()
}
};
this.populateBooksListview();
}
public function getDesignRootNodes () : javafx.scene.Node[] {
[ label, btnReturn, listviewBooks, ]
}
public function getDesignScene (): javafx.scene.Scene {
scene
}
function btnReturnAction(): Void {
// Get the selected book and mark it as returned
var selected: Integer = listviewBooks.selectedIndex;
if ( selected == -1 )
return;
var success: Boolean =
db.returnBook(bookArray[selected].id);
close();
}
}
When the Return Books window is closed, the ReturnBooks.onClose method in Example 4 is executed. As a result, the lists of all books and overdue books are updated to reflect the changes.
