Before started Database Operations in JavaFX section, please check the first article and learn How to Start JAVAFX!

Getting Started with JAVAFX
http://www.swtestacademy.com/getting-started-with-javafx/

At first, part of JavaFX tutorial series, we created a sample JavaFX project, designed the draft version of the UI and set up an Oracle XE database. In this post, we will create controller, model, DAO, and Util classes to do DB operations. Before starting to code, I want to give more information about our example project.  The latest version of the UI is shown in the figure below.

database operations

You can design UI using SceneBuilder. After drag and drop any element to the anchor pane, you need to set their fx:id properties. These properties must be unique and you can use elements in your code with their unique fx:id properties. Also, you can copy paste any element into anchor pane then change its name and fx:id value. I demonstrated how to design a GUI in the figure below.

GUI

I listed details of the example below:

  • Search an employee using employee’s id and show the result on table view and text area. (SELECT)
  • Search all employees and show them on the table view. (SELECT * FROM)
  • Update the e-mail address of an employee by using employee’s id. (UPDATE)
  • Delete an employee by using employee’s id. (DELETE)
  • Insert a new employee in the “employee table”. (INSERT)
  • Show results of all operations on the Text Area (Print on Result Console)

We will use Oracle XE database and its default HR schema. In order to connect Oracle DB, we will use JDBC driver. JDBC driver is a software component enabling a Java application to interact with a database. To add JDBC driver in our project:

1) Go to your project, right-click, and then click Open Module Settings.

javafx

2) Click Libraries then click “+” sign, click “From Maven

maven

3) Write “odjbc” on the search bar and enter. Then, select the “oracle:ojdbc6:11.2.0.3” and select sources and JavaDocs click OK.

2-5

2-6

2-7

4) Now, we can see ojdbc6:11.2.0.3 library in lib folder on IntelliJ.

2-8

Finally, we are ready to start coding. As shown the picture below, I created 4 packages: controller, model, view and util.

packages

I used DAO Design Pattern to perform Employee operations. I want to explain DAO Pattern briefly, for more information, I suggest that you check Jakop Jenkov’s DAO tutorial. In DAO pattern, domain (business) logic does not directly communicate with the DB. It communicates with DAO layer and DAO layer handles DB operations and sends the results to the business layer.

dao pattern

The philosophy under DAO pattern is that if you need to change the underlying persistence mechanism, you can do it in DAO layer, not all the places in the business layer. It is also very important that no details of underlying DB related mechanism leak out of DAO layer to business layer.

DBUtil Class

I want to start to explain the code to DBUtil class. In our example, DBUtil class is responsible for DB connection, DB disconnection, database query and update operations. DAO Class (EmployeeDAO) uses DBUtil class’s methods to do higher-level DB operations.

In DBUtil Class:

– dbConnect() method connects to DB.

– dbDisconnect() method closes DB connection.

dbExecuteQuery(String queryStmt) method executes given SQL statement and returns cachedRowSet set. In order to eliminate “java.sql.SQLRecoverableException: Closed Connection: next” error we return cachedRowSet instead of ResultSet. Thus, we can use cachedRowSet in other classes and manipulate that data.

dbExecuteUpdate(String sqlStmt) method executes given Update, Insert, Delete SQL operations.

I tried to write explanatory comments in the code below. If you have questions please don’t hesitate to write a comment. I will try to answer your questions.

DBUtil Class Code:

Employee Class (Model)

We need a model class to hold information about the employee. Add a new class to the model package and called it Employee.

2-10

This class holds all fields of the Employee such as name, last name, email, etc. It contains set and get methods and properties for all fields of a model class. A Property notifies us when any variable such as name, last name, etc. is changed. This helps us keep the view in sync with the data.

You can see the all fields of the employee from database as shown below.

employee

Employee Class Code:

Employee DAO Class (Data Access Object)

Employee DAO class handles employee related database operations such as searching, deleting, updating employee with declared SQL statements.

JavaFX view classes need to be informed about any changes made to the list of employees. It is important for the view to be synchronized with the data. For this purpose, we use ObservableList collection and hold the employee in this list.

  • searchEmployee and searchEmployees methods use DBUtil class’s dbExecuteQuery() method.
  • The other methods (update/delete/insert), use DBUtil class’s dbExecuteUpdate() method.

I tried to add descriptive inline comments in the code shown below.

EmployeeDAO Class Code:

RootLayout FXML

RootLayout contains a border pane and menu items. We need to bind it with RootLayoutController class.

2-12

We have three menus: File, Operations, and Help.

2-13

File Menu has Close option to exit the program. Operations menu has “New Menu Item” option.  Help Menu has “About” option to show information about program.

RootLayout View Code:

EmployeeView FXML

EmployeeView fxml file comprises of text fields, buttons, table view and text area for employee-related operations. We need to bind it with EmployeeController class.

2-14

As you see the picture below, we need to declare fx:id values, these values are unique for each element and we can bind methods with these elements by OnAction tag. Methods must start with “#” sign.

2-15

Employee View Code: