Channels ▼
RSS

Database

JavaFX Database Programming with Java DB


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;
}

Example 3: The Java DB query (with the QueryTool helper class) to retrieve all books.

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.

[Click image to view at full size]
Figure 10: The Library application main window, where books can be borrowed and returned.

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
        }
    }
}

Listing 2: The JavaFX code to checkout a book.

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;
}

Listing 3: The Java/JDBC code to check out a book.

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();
        }
    }
}

Example 4: The Return Books button handler code to open a new window.

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();
    }

}

Listing 4: The Return Book window.

[Click image to view at full size]
Figure 11: Instantiating a class that extends Stage results in a new window, such as the Return Book window.

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.


Related Reading


More Insights






Currently we allow the following HTML tags in comments:

Single tags

These tags can be used alone and don't need an ending tag.

<br> Defines a single line break

<hr> Defines a horizontal line

Matching tags

These require an ending tag - e.g. <i>italic text</i>

<a> Defines an anchor

<b> Defines bold text

<big> Defines big text

<blockquote> Defines a long quotation

<caption> Defines a table caption

<cite> Defines a citation

<code> Defines computer code text

<em> Defines emphasized text

<fieldset> Defines a border around elements in a form

<h1> This is heading 1

<h2> This is heading 2

<h3> This is heading 3

<h4> This is heading 4

<h5> This is heading 5

<h6> This is heading 6

<i> Defines italic text

<p> Defines a paragraph

<pre> Defines preformatted text

<q> Defines a short quotation

<samp> Defines sample computer code text

<small> Defines small text

<span> Defines a section in a document

<s> Defines strikethrough text

<strike> Defines strikethrough text

<strong> Defines strong text

<sub> Defines subscripted text

<sup> Defines superscripted text

<u> Defines underlined text

Dr. Dobb's encourages readers to engage in spirited, healthy debate, including taking us to task. However, Dr. Dobb's moderates all comments posted to our site, and reserves the right to modify or remove any content that it determines to be derogatory, offensive, inflammatory, vulgar, irrelevant/off-topic, racist or obvious marketing or spam. Dr. Dobb's further reserves the right to disable the profile of any commenter participating in said activities.

 
Disqus Tips To upload an avatar photo, first complete your Disqus profile. | View the list of supported HTML tags you can use to style comments. | Please read our commenting policy.
 

Video