Creating a simple Database Application in NetBeans

In this tutorial we create a simple database Java application in Netbeans. The database contains 2 tables:

Authors
Books

The Authors table is independent; that is, an author can exist in our database without a book. The Books table is a dependent table; that is, a book cannot exist without an author in our database.

1. Creating the Database and Tables

We start of by creating the database using JavaDB, also known as Derby, which is an embedded Java database.

The following video demonstrates how to do this. The SQL code from the video is given below the video.

The SQL to create the AUTHORS table follows:

CREATE TABLE AUTHORS
(
id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
firstname VARCHAR(50),
surname VARCHAR(50),
email VARCHAR(50),
CONSTRAINT author_pk PRIMARY KEY (id)
);

The SQL to create the BOOKS table follows:

CREATE TABLE BOOKS
(
id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
title VARCHAR(50),
edition VARCHAR(50),
author INTEGER,
CONSTRAINT book_pk PRIMARY KEY (id)
);

The following code sets up the author field as a foreign key in the BOOKS table, and links it to the AUTHORS table:

ALTER TABLE BOOKS
ADD FOREIGN KEY (author) 
REFERENCES AUTHORS(id);

2. Linking the Database to our App and Creating the Entitity and JPA Controller Classes

The next step is to link the database to the App and to create Entity and Controller classes. An entity is any object that will be stored in the database, for example, a Books object and an Authors object.

A JPA controller class is a convenience class that provides useful methods to perform CRUD function on the database. These methods essentially free us from writing SQL code for the most important database functions, such as inserting, deleting, editing and retrieving data from the database. Instead of SQL, we simply call Java methods.

The following video demonstrates how to create these classes.

3. Creating Java Forms to Insert and View Authors Data using NetBeans

We now have our database, our entity classes and our JPA controller classes. The next step is to create the user interface will will allow a user to insert data into a table. We start with the Authors table, since it is the independent table.

We also create a jTable that shows the contents of the Authors table in the database.

4. Creating a User Interface to insert a book into the Books database table

Inserting a book into the database table is a little more complicated than inserting an author. The reason for this is that the Books table is dependent on the Authors table. As such, every book in the Books table must have a valid reference to an author in the Authors table. If this is not so, then it will lead to data integrity issues in the database table.

So how do we know that an author exists in the database? And how do we allow the user to choose the author if it exists?

The best way to handle this situation form the user interface is to put in a dropdown list that shows all the authors in the Authors table, and allow the user to chose any one. In Java, we use a jComboBox to do this.

The video demonstrates how to create the user interface. The next video will show how to create the handler for the button to add the book to the database.

5. Creating a handle to add a book to the Database

We’ve created the AddBook form, now it’s time to add functionality persist the book into the database when the user clicks the Insert button.

6. Creating a jTable that display data from more than one table

Just as with the Authors table, we need to have a way to show the books in the Books table. For this we can use a jTable, but the table will have to show data from both the Books table and the Authors table. The reason for this is that each book is linked to an author via the author foreign key, so we need to show the book as well as the author’s name and surname rather than just the author’s id.

The following video shows how to do this.