Database Operations in JavaFX


Before started this section, please check the first article and learn How to Start JAVAFX!

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 on 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 to 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 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 with 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:

RootLayoutController Class

In RootLayoutController class, we handle exit and help methods. Exit method closes program and help method gives information about program by using Alert class.

RootLayoutController Code:

EmployeeController Class

In EmployeeController class, fields and methods have special @FXML annotation. We need this annotation because fxml file needs to access private fields and private methods. After these settings, application will automatically fill the variables when the fxml file is loaded.

EmployeeController Class handles below operations:

– searchEmployee() – Searches an employee with given employee id. Then, populate employee’s information on table view and print the result on text area.

– searchEmployees() – Gets all employees information and populates them on table view.

initialize() – Handles initialization. When the fxml file is loaded, it will be called automatically.

– populateEmployee(Employee) – Populates employee on table view.

– setEmpInfoToTextArea(Employee) – Prints employee information on text area.

– populateEmployees(ObservableList<Employee>) – Populates employees.

– updateEmployeeEmail() – Updates employee email by using employee id

– insertEmployee() – Insert a new employee into employee table.

– deleteEmployee() – Deletes an employee with employee’s id.

Extra Explanations:

– Private fields and methods where the fxml file needs to access must be annotated with @FXML annotation.

– After the fxml file has been loaded, the initialize() method will be called automatically.

– The setCellValueFactory(…) that we set on the table columns are used to determine which field inside the Employee objects should be used for the particular column. The arrow -> indicates that we are using a Java 8 feature called Lambdas. (Another option would be to use a PropertyValueFactory).

– If you use a property which is different than StringProperty, such as IntegerProperty, DoubleProperty, etc. the setCellValueFactory(…) must have an additional asObject() method:

Note: Always use the javafx imports, NOT awt or swing!

EmployeeController Class Code:

Main Class

At the end of the first JavaFX article, I described details of the main method. In here, I want to explain it briefly. It starts the primary stage, sets its title, initializes the root layout and then displays the “employee view”.

Main Class Code:

We finished coding. It is time to run the code.

2-16

The result of the program is shown below.

2-17

Github: https://github.com/swtestacademy/javafxexample

Bonus Information (Multi-threaded JavaFX Database Operations)

Sometimes big queries takes too much time and GUI is frozen until the query finishes. In order to solve this problem, we need to write our code in a multi threaded way. In this example, you can search all employees with multi-threaded way as follows:

First, you need to add an Executor variable and add below code snippet in EmployeeController Class’s initialize method:

Then, you can use JAVA Task class to handle data table populate operations with below code.

You can use fillEmployeeTable method instead of populateEmployees method. In this example, our data is not too big so I don’t need to use multi-threaded function for data populate operation but if you work with data intensive project, you may need to use multi-threaded functions to eliminate Frozen GUI  problems.

The End 🙂

Java FX Tutorial Series

JavaFX – Part 1: Getting Started with JavaFX

JavaFX – Part 2: JavaFX Database Operations (This Post)

javafx

Database Operations in JavaFX

By Onur Baskirt / Apr 1, 2016 / 56 Comments
Before started this section, please check the first article and learn How to Start 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...
rest assured

REST API Testing with Rest Assured

By Onur Baskirt / Mar 8, 2016 / 32 Comments
Outline In this post, I will explain what is API and API testing, what is the difference between SOAP and REST services, and how to test REST APIs with Rest Assured Library. What is API? API stands for Application Programming...
extentreports

How to Write Effective CSS Locators

By Onur Baskirt / Oct 1, 2017 / 0 Comments
Hi all, in this tutorial, I will describe you how to write effective CSS locators to interrogate web elements for your automation projects. As a rule of thumb, your interrogation strategy should be in below order: First try to use...
extentreports

Selenium-11: Execute JavaScript with JavascriptExecutor

By Onur Baskirt / Jan 27, 2016 / 15 Comments
Outline Sometimes we cannot handle some conditions or problems with Webdriver, web controls don't react well against selenium commands. In this kind of situations, we use Javascript. It is useful for custom synchronizations, hide or show the web elements, change...
javafx

Getting Started with JavaFX

By Onur Baskirt / Mar 25, 2016 / 0 Comments
When I started to work in my current position, one of my task is to do manual operations for campaign products  every week. After the second week, I thought that I have to automate this task using a GUI based...
extentreports

How to Write Smart XPath Locators

By Onur Baskirt / Sep 24, 2017 / 4 Comments
Hi all, in this tutorial, I will describe you how to write smart and non-brittle XPath locators. When we write our test scripts, we generally prefer to use id, name, class, etc. these kinds of locators. However, sometimes we could not...
extentreports

Selenium-1: Quick Start to Automation with Selenium WebDriver & JAVA & JUnit & Maven & IntelliJ

By Onur Baskirt / Sep 8, 2015 / 26 Comments
Outline Selenium Webdriver is the most popular open source web test automation framework across wide range of browsers and platforms. In this tutorial you will learn how to do web test automation with Selenium Webdriver and the related tools. Audience...
page object model

Page Object Model with C#

By Ege Aksoz / Jun 18, 2017 / 8 Comments
In the previous tutorial, we’ve taken the initial steps and entered the world of automated testing. We also wrote our first automated test. From this point on, since we are not just going to write one test, we need to...
extentreports

How to Select a Date From DatePicker Using Selenium

By Onur Baskirt / Aug 13, 2016 / 6 Comments
When you need to automate a airway, hotel, or similar websites you need to deal with Datepickers and some times it is a little bit cumbersome to select a specific date on the Datepicker or calendar.  In this post, I...
extentreports

Selenium Webdriver Performance testing with Jmeter and Selenium Grid

By Ozgur Kaya / Aug 9, 2016 / 2 Comments
In this post, we will complete Selenium Webdriver Performance testing scenario using Jmeter and Selenium Grid. 1- Install Java 7 or later If necessary https://java.com/tr/download/ 2- Download latest Jmeter version 3.0 or higher. http://jmeter.apache.org/download_jmeter.cgi 3- Download Jmeter PluginsManager JAR file and...
By | 2017-10-17T23:42:06+00:00 April 1st, 2016|Java, JavaFX|56 Comments

About the Author:

Onur Baskirt
Onur Baskirt is a senior IT professional with 10+ years of experience. He worked at Bahçesehir University, ST Microelectronics, Huawei and Ericsson as research assistant, design verification engineer and software test leader. Also, he worked as software test leader and software operations manager at Turkey's biggest technology retailer, Teknosa. Now, he is working as Head of Software Testing and Manager of two Development Teams at Kariyer.net. His current research areas are technical software testing, programming, and computer science. Formerly, he has several research works and papers on digital chip design & verification. His hobbies are sport, dancing, traveling, and nutrition. You can find detailed information about him on his linked-in page.

56 Comments

  1. ariesta June 12, 2016 at 6:58 am - Reply

    good tutorial i have many study from this website , can i have source code project for make me easy study ?

    • Onur Baskirt
      Onur Baskirt June 16, 2016 at 11:11 pm - Reply

      I will put it on github and let you know here. 🙂

      • ariesta June 18, 2016 at 10:09 pm - Reply

        ok 🙂 i see , im newbie for javafx because java usually so diffrent with javafx . javafx use implementation mvc all of code.

        i want to make diffrent design for dekstop ,website and mobile in one project application when i running in dekstop it’s large and when i running in mobile it simple , could you teach me how to make like that or give me advice where i can study more deep about javafx ? because good website like that about javafx it’s seldom

  2. KyleMtl June 22, 2016 at 6:54 pm - Reply

    Hi from Canada,
    Your tutorial is very useful to me !
    I have googleing a lot about DAO and Javafx and I have found some real bad code only.
    Could you make a tutorial about the best practices on JavaFx.
    Could you go deeper with the use of DAO and MVC ?
    Thanks

    • Onur Baskirt
      Onur Baskirt June 23, 2016 at 12:18 am - Reply

      First, thank you very much for your comments. Actually, I am not an ultimate JavaFX expert. At my former company, I needed to write a program which handles DB operations and I did that program the way that I described in this article. I suggest you to check Jakop Jenkov’s DAO tutorial series. (http://tutorials.jenkov.com/java-persistence/dao-design-pattern.html) I hope that series will address your problems and needs. Thanks.

  3. aryaman October 7, 2016 at 1:47 pm - Reply

    hey you are very goof in programming i am new to javafx can please tell me i am not able to fetvch the data from my database using a button and the button i have made is from the scene builder can you suggest something i can share my piece of code with yu if you want

  4. guilherme brandão October 26, 2016 at 10:14 pm - Reply

    Greetings! I just started JavaFX after a while using java, followed your example and saw some others which brings to my doubt:

    On model class should I use
    private String name;

    or

    private final StringProperty name;

    and, if the second way is the right one, how to handle with my own classes as atributes?
    class Person {
    private final ObjectProperty car; ?
    }

    • Onur Baskirt
      Onur Baskirt October 27, 2016 at 12:21 am - Reply

      When to use StringProperty firstName over String firstName?

      Use it when this firstName variable is going to be observed by others. You also can observe it by attaching a listener. You can use this variable in bindings with other observable objects of JavaFX. In some circumstances it is mandatory to use JavaFX Property, like Person list rendered with tableView which is editable. To reflect the changes immediately in edited cell, the underlying bound field should be a property.

      Reference: http://stackoverflow.com/questions/31266498/when-to-use-stringproperty-over-string

  5. Jon November 22, 2016 at 4:50 pm - Reply

    Excellent! Thank you. Is that a global search — to search all fields?

    • Onur Baskirt
      Onur Baskirt November 22, 2016 at 11:10 pm - Reply

      You can write any kind of search for all fields in a table.

  6. Jon November 25, 2016 at 4:10 am - Reply

    Hello, Mr. Baskirt.

    Wonderful and Excellent tutorial!

    Wow!

    My focus is to take your incredible code and convert it to JSF web app.

    Thank you.

    • Onur Baskirt
      Onur Baskirt November 26, 2016 at 5:52 pm - Reply

      Thank you @Jon. Have a good luck for your JSF project!

  7. Vinod November 27, 2016 at 2:07 pm - Reply

    Mr.Onur,
    I am an new user of JavaFX. Your tutorial is very good and self explanatory. I tried the code and it worked after few attempts.. thank you very much for this excellent step by step detail.

    • Onur Baskirt
      Onur Baskirt November 27, 2016 at 3:26 pm - Reply

      Hi Vinod,
      I am so happy that my article helped you. Have a good luck to your projects. Thank you!

  8. Sabaya November 29, 2016 at 11:25 pm - Reply

    Hi Baskirt, thank you for your efforts to help others.
    Do you have any example or tutorial for populating the Data to javaFX TableView dynamically from Oracle DB. Without going throw DAO or bean class. Where I would issue (SELECT * FROM tName), pass the table name and iterate throw the column MetaData and table rows and display the content in a tableview. I get something worked from MySQL DB, when tried to get it working for Oracle I got some error. I will share my code if would be able to look at it.

    Thank you again.

    • Onur Baskirt
      Onur Baskirt November 30, 2016 at 2:44 pm - Reply

      Hi Sabaya, I do not have that kind of example. I think only difference between Oracle and MySQL DB is connection code. It is better to check stackoverflow. Maybe other people also got same problem and fixed it. Also you can share the error here. Maybe other ppl also comment about your problem.

  9. yuen December 3, 2016 at 4:11 pm - Reply

    can I ask is your code applicable for NetBeans IDE?…I try to import your GitHub into NetBeans but unsuccessfully…I currently have a database project assign by lecture and me not sure need use java swing or JavaFX better?…

    • Onur Baskirt
      Onur Baskirt December 4, 2016 at 6:52 pm - Reply

      Hello, it is written on IntelliJ. You can use the source code files and try to create a project with those files in NetBeans. I have never used swing but I could say that JavaFX is next generation UI 😉 It is better to use JavaFX for me.

  10. Sudhakar Krishnan February 28, 2017 at 10:43 am - Reply

    Thank you very much. I tried with JavaDB. First it throw some error. The errors are JavaDB Query. The query in your code not supported. Also in New Screenbuilder , your view code not align properly. A small tweak – then fine. JavaDB code is slightly different from your(I think oracle) code.

    It worked, I learned JavaFX8. Next going to create a real app for my customer.

    • SW Test Academy February 28, 2017 at 8:09 pm - Reply

      Hi Sudhakar, I am happy that you solved all the isaues. I wrote this code last year. As you said, it may need some
      modifications and i am glad to see that you did all required modifications without any problem. I used Oracle instead of JAVADB maybe because of this reason queries did not work for you. I wish you a good luck for your commercial project.

  11. baaka young man March 27, 2017 at 1:49 pm - Reply

    thank you this was what i were looking for

  12. nutsack April 7, 2017 at 11:00 am - Reply

    It’s amazing to pay a quick visit this web site and
    reading the views of all friends about this piece of writing, while
    I am also keen of getting know-how.

  13. John May 2, 2017 at 11:09 pm - Reply

    Hi,

    Nice tutorial, i’m now starting to learn JavaFX and redid what you build here but in some other design, and i keep getting these errors:

    Exception in Application start method
    java.lang.reflect.InvocationTargetException
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at com.sun.javafx.application.LauncherImpl.launchApplicationWithArgs(LauncherImpl.java:389)
    at com.sun.javafx.application.LauncherImpl.launchApplication(LauncherImpl.java:328)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at sun.launcher.LauncherHelper$FXHelper.main(LauncherHelper.java:767)
    Caused by: java.lang.RuntimeException: Exception in Application start method
    at com.sun.javafx.application.LauncherImpl.launchApplication1(LauncherImpl.java:917)
    at com.sun.javafx.application.LauncherImpl.lambda$launchApplication$155(LauncherImpl.java:182)
    at java.lang.Thread.run(Thread.java:748)
    Caused by: java.lang.ClassCastException: javafx.scene.layout.AnchorPane cannot be cast to javafx.scene.layout.BorderPane
    at sample.Main.initRootLayout(Main.java:41)
    at sample.Main.start(Main.java:29)
    at com.sun.javafx.application.LauncherImpl.lambda$launchApplication1$162(LauncherImpl.java:863)
    at com.sun.javafx.application.PlatformImpl.lambda$runAndWait$175(PlatformImpl.java:326)
    at com.sun.javafx.application.PlatformImpl.lambda$null$173(PlatformImpl.java:295)
    at java.security.AccessController.doPrivileged(Native Method)
    at com.sun.javafx.application.PlatformImpl.lambda$runLater$174(PlatformImpl.java:294)
    at com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:95)
    at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
    at com.sun.glass.ui.win.WinApplication.lambda$null$148(WinApplication.java:191)
    … 1 more
    Exception running application sample.Main

    Could you help?

  14. John May 3, 2017 at 9:44 pm - Reply

    Hi,

    Found the error, thank you very much!

  15. John May 3, 2017 at 9:53 pm - Reply

    Hi again,

    Now i’m having another problem, i cannot get to show the menu bar from the BorderPane when i set the scene.
    Do you know what could be the problem?

    Thank you,
    John

  16. John May 3, 2017 at 10:02 pm - Reply

    Solved that one too, sorry for all the questions, i’m pretty at Java too 🙂

  17. Cloudsnight May 7, 2017 at 7:25 pm - Reply

    Hello Mr. Onur, i feel happy to see your method, in my opinion it is using DAO and MVC, wanna ask, how about using MySQL database, is the query statement is still same use as this Oracle XE ? i’m using netbeans and MySQL.

    • Onur Baskirt
      Onur Baskirt May 7, 2017 at 11:13 pm - Reply

      Hello Uchin, Thanks for your kind words. You can use also MYSQL as well. I remember that some changed the code for an another DB. It is better to look at JAVA & MYSQL connection on the web and change the DB connection sections of the code.

  18. Connel Evalsam Asikong May 11, 2017 at 5:33 pm - Reply

    Hello. Great tutorials. Don’t get a lot of tutorials out there about FX. I have a school project to design an exam app for a school. I’ve been studying this tutorial for 3 days now. Although haven’t really understood it. I have a problem with your codes in ypur Employee class:

    empHireDateColumn.setCellValueFactory(cellData -> cellData.getValue().hireDateProperty());

    throws this exception:

    java: incompatible types: bad return type in lambda expression javafx.beans.property.SimpleObjectProperty cannot be converted to javafx.beans.value.ObservableValue

    although I won’t be needing that field but would love to get it right.
    Thanks

    • Onur Baskirt
      Onur Baskirt May 11, 2017 at 8:13 pm - Reply

      Would you try below code, please?

      empHireDateColumn.setCellFactory(cellData -> cellData.getValue().hireDateProperty());

      Now, I don’t have Oracle DB on my PC so I cannot try this change. Please, try it, let me know the result. I hope it will fix the problem.

      • Connel Evalsam Asikong May 12, 2017 at 3:19 pm - Reply

        just wanted to find out too if you’ve got a youtube handle

  19. Connel Evalsam Asikong May 12, 2017 at 1:37 am - Reply

    it doesn’t recognize the ‘getValue()’ method on it. And it’s not giving me any good explanation. I’m using MySql.

  20. Dawit August 16, 2017 at 6:41 pm - Reply

    Hi sir, your tutorial helped me a lot but i have a problem with the menu’s, i added submenu ’employee’ under the operation menu i have a problem of showing the Employee form the menu is clicked can you please tell me how to solve this issue

    • Onur Baskirt
      Onur Baskirt August 17, 2017 at 11:51 pm - Reply

      You should check your fxml file. RootLayout View Code part 😉

  21. Svetlana August 28, 2017 at 8:08 pm - Reply

    Hello. How to associate two tables in JavaFX in one виндов(тне same) so that when you click on one table you see lines of another table on an foreign key?

    • Onur Baskirt
      Onur Baskirt August 30, 2017 at 5:18 pm - Reply

      Privet Svetlana, I did not tried that. But you can find a solution on google.

  22. Svetlana August 29, 2017 at 12:32 am - Reply

    I have 2 table vopros otvet. How to connect them? https://github.com/svil1502/Test_Exam27.git

  23. Rizal September 3, 2017 at 1:23 pm - Reply

    Halo Mr. Onur..Thank you for sharing a very useful article for us. There are things I want to ask about JAVAFX. Is Hibernate ORM applicable to JavaFX properties? Or in other words, is the object in JavaFX properties can be mapped to the database using Hibernate ORM? Or, is Hbernate only for ordinary java beans (POJO), not for JavaFX Properties (JavaFX Beans)?. Thank you..

  24. Sara September 5, 2017 at 7:45 am - Reply

    Thank You so much. The best tutorial about JavaFX!

  25. Oussama September 16, 2017 at 6:11 am - Reply

    Thank you so much sir.
    Can you uploade the Employees.sql ?

  26. RAUF MOHAMMED September 29, 2017 at 4:14 am - Reply

    THANKS SO MUCH. PLEASE HELP ME ADD EMPLOYEE IMAGE

  27. RAUF MOHAMMED October 4, 2017 at 11:48 pm - Reply

    thank you very much for the prompt response. what i wanted is how to save the image in the database using the mvc as in you ably demonstrated in your example. please bear with me because i am new to java
    kind regards

Leave A Comment