Channels ▼
RSS

Database

JavaFX Database Programming with Java DB


The JavaFX Composer

For complex or detailed window layout, such as data entry forms or other detailed displays, the JavaFX Composer makes the task quick and easy. The Composer tool is installed as a NetBeans plug-in, available via the Plugins window (see Figure 1). The "Palette" -- in the upper-right corner in Figure 4 -- contains the available JavaFX Controls, Shapes, Containers, Effects, and other components that can be dragged onto the window you're designing. You can then drag the components around on the window for precise placements, and size them appropriately.

[Click image to view at full size]
Figure 4: Laying out controls within a window with the NetBeans JavaFX Composer tool.

When a component is selected, its programmable properties are displayed in the "Properties" editor -- the lower-right corner of Figure 4. Here you can set properties such as the name of the component, label text, and methods that are called when certain actions occurs (i.e. clicking a button).

The window shown in the Composer tool in Figure 4 is for the Library application I'll examine throughout this article. It contains three main lists -- one for all books in the library; a second for the list of library patrons; and a third to highlight books that are overdue. There are buttons to add new books and patrons to the library, as well as to allow patrons to borrow and return books. The data that populates these lists and enables all actions comes from a Java DB database, accessed through JDBC as with any Java application. Let's now take a deeper look at Java DB.

Java DB and the Library Application

Java DB, which is available with most Java Development Kit download bundles, Java EE download bundles, or as a separate download, is a robust relational database based on Apache Derby. The current Java DB release version is v10.5.3.0, which is based on Derby with the same version number. You can run Java DB in its own process, accessible to multiple network clients via the Java DB Client JDBC driver, or you can run it embedded within your application with the embedded JDBC driver. NetBeans contains a Services tab (next to the Projects tab that lists your development projects) that contains a section for Databases (see Figure 5).

[Click image to view at full size]
Figure 5: The NetBeans Services tab includes support for various types of databases (Java DB, MySQL, PostrgreSQL, and so on).

When you right-mouse click on the Java DB entry under Databases, you can either start or stop the standalone Java DB server, modify properties of the Java DB server, or create a new database. When creating a database, you need to provide a name, along with a username and password to access to the database, and location to store the database files (see Figure 6).

[Click image to view at full size]
Figure 6: Creating a new standalone Java DB database with NetBeans.

However, we won't be using Java DB in standalone mode; instead we'll embed it within our application, and thus include all of the database creation code with the application code. When our Library application is run for the first time, it will create the database along with the users, tables, and indexes that we need. The advantage is that our application won't require a separate database installation process or any input from the user. The downside of embedded mode is that only one application can work with the database at a time. However, for certain applications, this choice works well.

For existing databases, NetBeans allows you to run queries to insert new data, update or view existing data, modify the database schema, and so on. First, you need to connect to the database. To do this, right-mouse click on Databases within the Services tab, and choose New Connection. Next, within the New Database Connection window, choose the driver named Java DB (Embedded), enter the path to the database files, and the username and password to access the database (see Figure 7).

[Click image to view at full size]
Figure 7: Connecting to a database in NetBeans allows you to easily query and update an existing Java DB/Derby, MySQL, or PostgreSQL database.

Once connected, you can right-mouse click on tables within the database to view data, add columns, and perform other tasks. The results from queries are displayed within NetBeans, and can be edited in place (see Figure 8).

[Click image to view at full size]
Figure 8: Connecting to a Java DB database, viewing data, and modifying rows are all a mouse-click away in NetBeans.

The JavaFX Library application we're building will include code to create and connect to a Java DB database. It does this through standard JDBC calls. For instance, Example 2 shows the code used to connect to an existing Java DB database named "librarydb".


Connection con = null;
try {
    System.setProperty(
        "derby.database.forceDatabaseLock", "true");
    String driver = "org.apache.derby.jdbc.EmbeddedDriver";
    Class.forName(driver).newInstance();
    String url = "jdbc:derby:librarydb";
    con = DriverManager.getConnection(url, "dbuser", "dbuser");
}
catch ( Exception e ) {
    ...
}

Example 2: The JDBC code to connect to librarydb.

First, the code ensures that only one application can connect to librarydb at a time by setting the forceDatabaseLock property to true. Next, the code attempts to connect to the database with the URL jdbc:derby:librarydb, using the Java DB Embedded driver, org.apache.derby.jdbc.EmbeddedDriver.

If the connect attempt fails because the database doesn't yet exist, an Exception will be thrown. At this point, to create the database, the code will simply append the text ;create=true to the end of the database URL, and once again attempt a connection. You can do this in the catch block of Example 2. As a result, Java DB will create the database and return a valid connection to it for our application to use. Next step: create the tables.

Our Library application contains three tables; the first named BOOKS; the second named PATRONS; and the third to map books to patrons who have borrowed them (named CHECKEDOUT). Figure 9 shows the tables, columns, and their relationships. When no books are borrowed, the CHECKEDOUT table will be empty. Otherwise when a book is borrowed, a row is added to the CHECKEDOUT table, and the CHECKEDOUT column in the BOOK table is set to the appropriate patron's ID.

[Click image to view at full size]
Figure 9: The Library application's database tables and relationships..

These two relationships allow us to quickly know if a particular book is borrowed, get all of the books that are borrowed, as well as who borrowed them. Listing 1 contains the JDBC code to create all three tables.


static final String CREATE_BOOK_TABLE =
    "CREATE table APP.BOOK ("+
        "ID          INTEGER NOT NULL "+
        "PRIMARY KEY GENERATED ALWAYS AS IDENTITY "+
        "(START WITH 1, INCREMENT BY 1)," +
        "NAME           VARCHAR(50)," +
        "AUTHOR         VARCHAR(40)," +
        "PUBLISHER      VARCHAR(40)," +
        "CHECKEDOUT     INTEGER," +
        "ACTIVE         INTEGER )";

static final String CREATE_PATRON_TABLE =
    "CREATE table APP.PATRON ("+
        "ID          INTEGER NOT NULL "+
        "PRIMARY KEY GENERATED ALWAYS AS IDENTITY "+
        "(START WITH 1, INCREMENT BY 1)," +
        "FIRSTNAME      VARCHAR(25)," +
        "LASTNAME       VARCHAR(25)," +
        "ADDRESS        VARCHAR(40)," +
        "FINES          DOUBLE, " +
        "ACTIVE         INTEGER )";

static final String CREATE_CHECKEDOUT_TABLE =
    "CREATE table APP.CHECKEDOUT ("+
        "ID          INTEGER NOT NULL "+
        "PRIMARY KEY GENERATED ALWAYS AS IDENTITY "+
        "(START WITH 1, INCREMENT BY 1)," +
        "BOOKID         INTEGER," +
        "PATRONID       INTEGER," +
        "MONTHDUE       INTEGER," +
        "DAYDUE         INTEGER, " +
        "YEARDUE        INTEGER )";

private void createTables() {
    this.execQuery(CREATE_BOOK_TABLE);
    this.execQuery(CREATE_PATRON_TABLE);
    this.execQuery(CREATE_CHECKEDOUT_TABLE);
}

...

Listing 1: The JDBC code to create the Library database tables.

Let's dive deeper into the sample JavaFX Library application now to examine the code to query the database, add new books and patrons, and borrow and return books. We'll also see how these actions are mapped to user actions in the JavaFX application GUI.


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