Database Operations in JavaFX with Complete Example!

In this article, we will focus on Database Operations in JavaFX. In the first part of the JavaFX tutorial series, we created a sample JavaFX project, designed the draft version of the UI, and set up an Oracle XE database.

Now 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.

It is great to read the first article of JavaFX before this article: https://www.swtestacademy.com/getting-started-with-javafx/

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 the 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 the employee’s id and show the result on the 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 the employee’s id. (UPDATE)
  • Delete an employee by using the 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 the Oracle XE database and its default HR schema. In order to connect Oracle DB, we will use the 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 the lib folder on IntelliJ.

2-8

Finally, we are ready to start coding. As shown in 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 the DAO pattern, domain (business) logic does not directly communicate with the DB. It communicates with the DAO layer and the DAO layer handles DB operations and sends the results to the business layer.

dao pattern

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

package sample.util;

import com.sun.rowset.CachedRowSetImpl;

import java.sql.*;

/**
 * Created by ONUR BASKIRT on 22.02.2016.
 */
public class DBUtil {
    //Declare JDBC Driver
    private static final String JDBC_DRIVER = "oracle.jdbc.driver.OracleDriver";

    //Connection
    private static Connection conn = null;

    //Connection String
    //String connStr = "jdbc:oracle:thin:Username/Password@IP:Port/SID";
    //Username=HR, Password=HR, IP=localhost, IP=1521, SID=xe
    private static final String connStr = "jdbc:oracle:thin:HR/HR@localhost:1521/xe";


    //Connect to DB
    public static void dbConnect() throws SQLException, ClassNotFoundException {
        //Setting Oracle JDBC Driver
        try {
            Class.forName(JDBC_DRIVER);
        } catch (ClassNotFoundException e) {
            System.out.println("Where is your Oracle JDBC Driver?");
            e.printStackTrace();
            throw e;
        }

        System.out.println("Oracle JDBC Driver Registered!");

        //Establish the Oracle Connection using Connection String
        try {
            conn = DriverManager.getConnection(connStr);
        } catch (SQLException e) {
            System.out.println("Connection Failed! Check output console" + e);
            e.printStackTrace();
            throw e;
        }
    }

    //Close Connection
    public static void dbDisconnect() throws SQLException {
        try {
            if (conn != null && !conn.isClosed()) {
                conn.close();
            }
        } catch (Exception e){
           throw e;
        }
    }

    //DB Execute Query Operation
    public static ResultSet dbExecuteQuery(String queryStmt) throws SQLException, ClassNotFoundException {
        //Declare statement, resultSet and CachedResultSet as null
        Statement stmt = null;
        ResultSet resultSet = null;
        CachedRowSetImpl crs = null;
        try {
            //Connect to DB (Establish Oracle Connection)
            dbConnect();
            System.out.println("Select statement: " + queryStmt + "\n");

            //Create statement
            stmt = conn.createStatement();

            //Execute select (query) operation
            resultSet = stmt.executeQuery(queryStmt);

            //CachedRowSet Implementation
            //In order to prevent "java.sql.SQLRecoverableException: Closed Connection: next" error
            //We are using CachedRowSet
            crs = new CachedRowSetImpl();
            crs.populate(resultSet);
        } catch (SQLException e) {
            System.out.println("Problem occurred at executeQuery operation : " + e);
            throw e;
        } finally {
            if (resultSet != null) {
                //Close resultSet
                resultSet.close();
            }
            if (stmt != null) {
                //Close Statement
                stmt.close();
            }
            //Close connection
            dbDisconnect();
        }
        //Return CachedRowSet
        return crs;
    }

    //DB Execute Update (For Update/Insert/Delete) Operation
    public static void dbExecuteUpdate(String sqlStmt) throws SQLException, ClassNotFoundException {
        //Declare statement as null
        Statement stmt = null;
        try {
            //Connect to DB (Establish Oracle Connection)
            dbConnect();
            //Create Statement
            stmt = conn.createStatement();
            //Run executeUpdate operation with given sql statement
            stmt.executeUpdate(sqlStmt);
        } catch (SQLException e) {
            System.out.println("Problem occurred at executeUpdate operation : " + e);
            throw e;
        } finally {
            if (stmt != null) {
                //Close statement
                stmt.close();
            }
            //Close connection
            dbDisconnect();
        }
    }
}

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 all fields of the employee from the database as shown below.

employee

Employee Class Code:

package sample.model;

import javafx.beans.property.*;
import java.sql.Date;

/**
 * Created by ONUR BASKIRT on 27.02.2016.
 */
public class Employee {
    //Declare Employees Table Columns
    private IntegerProperty employee_id;
    private StringProperty first_name;
    private StringProperty last_name;
    private StringProperty email;
    private StringProperty phone_number;
    private SimpleObjectProperty<Date> hire_date;
    private StringProperty job_id;
    private IntegerProperty salary;
    private DoubleProperty commission_pct;
    private IntegerProperty manager_id;
    private IntegerProperty department_id;

    //Constructor
    public Employee() {
        this.employee_id = new SimpleIntegerProperty();
        this.first_name = new SimpleStringProperty();
        this.last_name = new SimpleStringProperty();
        this.email = new SimpleStringProperty();
        this.phone_number = new SimpleStringProperty();
        this.hire_date = new SimpleObjectProperty<>();
        this.job_id = new SimpleStringProperty();
        this.salary = new SimpleIntegerProperty();
        this.commission_pct = new SimpleDoubleProperty();
        this.manager_id = new SimpleIntegerProperty();
        this.department_id = new SimpleIntegerProperty();
    }

    //employee_id
    public int getEmployeeId() {
        return employee_id.get();
    }

    public void setEmployeeId(int employeeId){
        this.employee_id.set(employeeId);
    }

    public IntegerProperty employeeIdProperty(){
        return employee_id;
    }

    //first_name
    public String getFirstName () {
        return first_name.get();
    }

    public void setFirstName(String firstName){
        this.first_name.set(firstName);
    }

    public StringProperty firstNameProperty() {
        return first_name;
    }

    //last_name
    public String getLastName () {
        return last_name.get();
    }

    public void setLastName(String lastName){
        this.last_name.set(lastName);
    }

    public StringProperty lastNameProperty() {
        return last_name;
    }

    //email
    public String getEmail () {
        return email.get();
    }

    public void setEmail (String email){
        this.email.set(email);
    }

    public StringProperty emailProperty() {
        return email;
    }

    //phone_number
    public String getPhoneNumber () {
        return phone_number.get();
    }

    public void setPhoneNumber (String phoneNumber){
        this.phone_number.set(phoneNumber);
    }

    public StringProperty phoneNumberProperty() {
        return phone_number;
    }

    //hire_date
    public Object getHireDate(){
        return hire_date.get();
    }

    public void setHireDate(Date hireDate){
        this.hire_date.set(hireDate);
    }

    public SimpleObjectProperty<Date> hireDateProperty(){
        return hire_date;
    }

    //job_id
    public String getJobId () {
        return job_id.get();
    }

    public void setJobId (String jobId){
        this.job_id.set(jobId);
    }

    public StringProperty jobIdProperty() {
        return job_id;
    }

    //salary
    public int getSalary() {
        return salary.get();
    }

    public void setSalary(int salary){
        this.salary.set(salary);
    }

    public IntegerProperty salaryProperty(){
        return salary;
    }

    //commission_pct
    public double getCommissionPct() {
        return commission_pct.get();
    }

    public void setCommissionPct(double commissionPct){
        this.commission_pct.set(commissionPct);
    }

    public DoubleProperty commissionPctProperty(){
        return commission_pct;
    }

    //manager_id
    public int getManagerId() {
        return manager_id.get();
    }

    public void setManagerId(int managerId){
        this.manager_id.set(managerId);
    }

    public IntegerProperty managerIdProperty(){
        return manager_id;
    }

    //department_id
    public int getDepartmanId() {
        return department_id.get();
    }

    public void setDepartmantId(int departmentId){
        this.manager_id.set(departmentId);
    }

    public IntegerProperty departmentIdProperty(){
        return department_id;
    }
}

Employee DAO Class (Data Access Object)

Employee DAO class handles employee-related database operations such as searching, deleting, updating employees 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:

package sample.model;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import sample.util.DBUtil;

import java.sql.ResultSet;
import java.sql.SQLException;

public class EmployeeDAO {

    //*******************************
    //SELECT an Employee
    //*******************************
    public static Employee searchEmployee (String empId) throws SQLException, ClassNotFoundException {
        //Declare a SELECT statement
        String selectStmt = "SELECT * FROM employees WHERE employee_id="+empId;

        //Execute SELECT statement
        try {
            //Get ResultSet from dbExecuteQuery method
            ResultSet rsEmp = DBUtil.dbExecuteQuery(selectStmt);

            //Send ResultSet to the getEmployeeFromResultSet method and get employee object
            Employee employee = getEmployeeFromResultSet(rsEmp);

            //Return employee object
            return employee;
        } catch (SQLException e) {
            System.out.println("While searching an employee with " + empId + " id, an error occurred: " + e);
            //Return exception
            throw e;
        }
    }

    //Use ResultSet from DB as parameter and set Employee Object's attributes and return employee object.
    private static Employee getEmployeeFromResultSet(ResultSet rs) throws SQLException
    {
        Employee emp = null;
        if (rs.next()) {
            emp = new Employee();
            emp.setEmployeeId(rs.getInt("EMPLOYEE_ID"));
            emp.setFirstName(rs.getString("FIRST_NAME"));
            emp.setLastName(rs.getString("LAST_NAME"));
            emp.setEmail(rs.getString("EMAIL"));
            emp.setPhoneNumber(rs.getString("PHONE_NUMBER"));
            emp.setHireDate(rs.getDate("HIRE_DATE"));
            emp.setJobId(rs.getString("JOB_ID"));
            emp.setSalary(rs.getInt("SALARY"));
            emp.setCommissionPct(rs.getDouble("COMMISSION_PCT"));
            emp.setManagerId(rs.getInt("MANAGER_ID"));
            emp.setDepartmantId(rs.getInt("DEPARTMENT_ID"));
        }
        return emp;
    }

    //*******************************
    //SELECT Employees
    //*******************************
    public static ObservableList<Employee> searchEmployees () throws SQLException, ClassNotFoundException {
        //Declare a SELECT statement
        String selectStmt = "SELECT * FROM employees";

        //Execute SELECT statement
        try {
            //Get ResultSet from dbExecuteQuery method
            ResultSet rsEmps = DBUtil.dbExecuteQuery(selectStmt);

            //Send ResultSet to the getEmployeeList method and get employee object
            ObservableList<Employee> empList = getEmployeeList(rsEmps);

            //Return employee object
            return empList;
        } catch (SQLException e) {
            System.out.println("SQL select operation has been failed: " + e);
            //Return exception
            throw e;
        }
    }

    //Select * from employees operation
    private static ObservableList<Employee> getEmployeeList(ResultSet rs) throws SQLException, ClassNotFoundException {
        //Declare a observable List which comprises of Employee objects
        ObservableList<Employee> empList = FXCollections.observableArrayList();

        while (rs.next()) {
            Employee emp = new Employee();
            emp.setEmployeeId(rs.getInt("EMPLOYEE_ID"));
            emp.setFirstName(rs.getString("FIRST_NAME"));
            emp.setLastName(rs.getString("LAST_NAME"));
            emp.setEmail(rs.getString("EMAIL"));
            emp.setPhoneNumber(rs.getString("PHONE_NUMBER"));
            emp.setHireDate(rs.getDate("HIRE_DATE"));
            emp.setJobId(rs.getString("JOB_ID"));
            emp.setSalary(rs.getInt("SALARY"));
            emp.setCommissionPct(rs.getDouble("COMMISSION_PCT"));
            emp.setManagerId(rs.getInt("MANAGER_ID"));
            emp.setDepartmantId(rs.getInt("DEPARTMENT_ID"));
            //Add employee to the ObservableList
            empList.add(emp);
        }
        //return empList (ObservableList of Employees)
        return empList;
    }

    //*************************************
    //UPDATE an employee's email address
    //*************************************
    public static void updateEmpEmail (String empId, String empEmail) throws SQLException, ClassNotFoundException {
        //Declare a UPDATE statement
        String updateStmt =
                "BEGIN\n" +
                        "   UPDATE employees\n" +
                        "      SET EMAIL = '" + empEmail + "'\n" +
                        "    WHERE EMPLOYEE_ID = " + empId + ";\n" +
                        "   COMMIT;\n" +
                        "END;";

        //Execute UPDATE operation
        try {
            DBUtil.dbExecuteUpdate(updateStmt);
        } catch (SQLException e) {
            System.out.print("Error occurred while UPDATE Operation: " + e);
            throw e;
        }
    }

    //*************************************
    //DELETE an employee
    //*************************************
    public static void deleteEmpWithId (String empId) throws SQLException, ClassNotFoundException {
        //Declare a DELETE statement
        String updateStmt =
                "BEGIN\n" +
                        "   DELETE FROM employees\n" +
                        "         WHERE employee_id ="+ empId +";\n" +
                        "   COMMIT;\n" +
                        "END;";

        //Execute UPDATE operation
        try {
            DBUtil.dbExecuteUpdate(updateStmt);
        } catch (SQLException e) {
            System.out.print("Error occurred while DELETE Operation: " + e);
            throw e;
        }
    }

    //*************************************
    //INSERT an employee
    //*************************************
    public static void insertEmp (String name, String lastname, String email) throws SQLException, ClassNotFoundException {
        //Declare a DELETE statement
        String updateStmt =
                "BEGIN\n" +
                        "INSERT INTO employees\n" +
                        "(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID)\n" +
                        "VALUES\n" +
                        "(sequence_employee.nextval, '"+name+"', '"+lastname+"','"+email+"', SYSDATE, 'IT_PROG');\n" +
                        "END;";

        //Execute DELETE operation
        try {
            DBUtil.dbExecuteUpdate(updateStmt);
        } catch (SQLException e) {
            System.out.print("Error occurred while DELETE Operation: " + e);
            throw e;
        }
    }
}

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 a Close option to exit the program. The Operations menu has a “New Menu Item” option.  Help Menu has an “About” option to show information about the program.

RootLayout View Code:

<?xml version="1.0" encoding="UTF-8"?>

<?import javafx.scene.control.Menu?>
<?import javafx.scene.control.MenuBar?>
<?import javafx.scene.control.MenuItem?>
<?import javafx.scene.layout.BorderPane?>


<BorderPane maxHeight="-Infinity" maxWidth="-Infinity" minHeight="-Infinity" minWidth="-Infinity" prefHeight="370.0" prefWidth="600.0" xmlns="http://javafx.com/javafx/8.0.65" xmlns:fx="http://javafx.com/fxml/1" fx:controller="sample.controller.RootLayoutController">
   <top>
      <MenuBar BorderPane.alignment="CENTER">
        <menus>
          <Menu mnemonicParsing="false" text="File">
            <items>
              <MenuItem mnemonicParsing="false" onAction="#handleExit" text="Close" />
            </items>
          </Menu>
          <Menu mnemonicParsing="false" text="Edit">
            <items>
              <MenuItem mnemonicParsing="false" text="New Menu Item" />
            </items>
          </Menu>
          <Menu mnemonicParsing="false" text="Help">
            <items>
              <MenuItem mnemonicParsing="false" onAction="#handleHelp" text="About" />
            </items>
          </Menu>
        </menus>
      </MenuBar>
   </top>
</BorderPane>

EmployeeView FXML

EmployeeView FXML file comprises 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 in 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:

<?xml version="1.0" encoding="UTF-8"?>

<?import javafx.scene.control.*?>
<?import javafx.scene.layout.*?>
<?import javafx.scene.text.*?>


<AnchorPane maxHeight="-Infinity" maxWidth="-Infinity" minHeight="-Infinity" minWidth="-Infinity" prefHeight="344.0" prefWidth="600.0" xmlns="http://javafx.com/javafx/8" xmlns:fx="http://javafx.com/fxml/1" fx:controller="sample.controller.EmployeeController">
   <children>
      <TextField fx:id="empIdText" layoutX="193.0" layoutY="41.0" prefHeight="25.0" prefWidth="67.0" promptText="Emp ID" />
      <Label layoutX="194.0" layoutY="21.0" text="Employee ID" />
      <Button fx:id="searchEmpBtn" layoutX="194.0" layoutY="70.0" mnemonicParsing="false" onAction="#searchEmployee" prefHeight="25.0" prefWidth="56.0" text="Search" />
      <Button fx:id="deleteEmpBtn" layoutX="332.0" layoutY="70.0" mnemonicParsing="false" onAction="#deleteEmployee" prefHeight="25.0" prefWidth="56.0" text="Delete" />
      <Button fx:id="updateEmpBtn" layoutX="263.0" layoutY="70.0" mnemonicParsing="false" onAction="#updateEmployeeEmail" prefHeight="25.0" prefWidth="56.0" text="Update" />
      <Button fx:id="addEmpBtn" layoutX="82.0" layoutY="114.0" mnemonicParsing="false" onAction="#insertEmployee" text="Add Employee" />
      <TextArea fx:id="resultArea" layoutX="7.0" layoutY="250.0" prefHeight="85.0" prefWidth="167.0" wrapText="true" />
      <Label layoutX="9.0" layoutY="231.0" text="Result Console">
         <font>
            <Font name="System Bold" size="12.0" />
         </font></Label>
      <TextField fx:id="newEmailText" layoutX="268.0" layoutY="41.0" prefHeight="25.0" prefWidth="120.0" promptText="new email" />
      <Label layoutX="270.0" layoutY="21.0" text="New Email" />
      <VBox layoutX="97.0" layoutY="24.0" spacing="4.0">
         <children>
            <TextField fx:id="nameText" prefHeight="25.0" prefWidth="79.0" promptText="Name" />
            <TextField fx:id="surnameText" prefHeight="25.0" prefWidth="79.0" promptText="Surname" />
            <TextField fx:id="emailText" prefHeight="25.0" prefWidth="79.0" promptText="email" />
         </children>
      </VBox>
      <VBox layoutX="9.0" layoutY="28.0" prefWidth="67.0" spacing="12.0">
         <children>
            <Label text="Name" />
            <Label text="Surname" />
            <Label text="Email" />
         </children>
      </VBox>
      <Separator layoutY="14.0" prefHeight="4.0" prefWidth="600.0" />
      <Separator layoutX="180.0" layoutY="14.0" orientation="VERTICAL" prefHeight="333.0" prefWidth="7.0" />
      <TableView fx:id="employeeTable" editable="true" layoutX="193.0" layoutY="102.0" prefHeight="234.0" prefWidth="393.0" tableMenuButtonVisible="true">
        <columns>
            <TableColumn fx:id="empIdColumn" prefWidth="57.0" text="Id" />
          <TableColumn fx:id="empNameColumn" prefWidth="75.0" text="Name" />
          <TableColumn fx:id="empLastNameColumn" prefWidth="73.0" text="LastName" />
            <TableColumn fx:id="empEmailColumn" prefWidth="79.0" text="Email" />
            <TableColumn fx:id="empPhoneNumberColumn" prefWidth="73.0" text="Phone" />
            <TableColumn fx:id="empHireDateColumn" prefWidth="93.0" text="Hire Date" />
        </columns>
      </TableView>
      <Button fx:id="searchEmpsBtn" layoutX="396.0" layoutY="70.0" mnemonicParsing="false" onAction="#searchEmployees" prefHeight="25.0" prefWidth="139.0" text="Search All Employees" />
   </children>
</AnchorPane>

RootLayoutController Class

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

RootLayoutController Code:

package sample.controller;

import javafx.event.ActionEvent;
import javafx.scene.control.Alert;
import sample.Main;

public class RootLayoutController {

    //Exit the program
    public void handleExit(ActionEvent actionEvent) {
        System.exit(0);
    }

    //Help Menu button behavior
    public void handleHelp(ActionEvent actionEvent) {
        Alert alert = new Alert (Alert.AlertType.INFORMATION);
        alert.setTitle("Program Information");
        alert.setHeaderText("This is a sample JAVAFX application for SWTESTACADEMY!");
        alert.setContentText("You can search, delete, update, insert a new employee with this program.");
        alert.show();
    }
}

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, the application will automatically fill the variables when the FXML file is loaded.

EmployeeController Class handles below operations:

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

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

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

– populateEmployee(Employee) – Populates employee on the 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 the employee table.

– deleteEmployee() – Deletes an employee with an 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 that is different than StringProperty, such as IntegerProperty, DoubleProperty, etc. the setCellValueFactory(…) must have an additional asObject() method:

empIdColumn.setCellValueFactory(cellData -> cellData.getValue().employeeIdProperty().asObject());

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

EmployeeController Class Code:

package sample.controller;

import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.event.ActionEvent;
import javafx.fxml.FXML;
import javafx.scene.control.TableColumn;
import javafx.scene.control.TableView;
import javafx.scene.control.TextArea;
import javafx.scene.control.TextField;
import sample.model.Employee;
import sample.model.EmployeeDAO;

import java.sql.Date;
import java.sql.SQLException;

/**
 * Created by ONUR BASKIRT on 23.02.2016.
 */
public class EmployeeController {

    @FXML
    private TextField empIdText;
    @FXML
    private TextArea resultArea;
    @FXML
    private TextField newEmailText;
    @FXML
    private TextField nameText;
    @FXML
    private TextField surnameText;
    @FXML
    private TextField emailText;
    @FXML
    private TableView employeeTable;
    @FXML
    private TableColumn<Employee, Integer>  empIdColumn;
    @FXML
    private TableColumn<Employee, String>  empNameColumn;
    @FXML
    private TableColumn<Employee, String> empLastNameColumn;
    @FXML
    private TableColumn<Employee, String> empEmailColumn;
    @FXML
    private TableColumn<Employee, String> empPhoneNumberColumn;
    @FXML
    private TableColumn<Employee, Date> empHireDateColumn;

    //Search an employee
    @FXML
    private void searchEmployee (ActionEvent actionEvent) throws ClassNotFoundException, SQLException {
        try {
            //Get Employee information
            Employee emp = EmployeeDAO.searchEmployee(empIdText.getText());
            //Populate Employee on TableView and Display on TextArea
            populateAndShowEmployee(emp);
        } catch (SQLException e) {
            e.printStackTrace();
            resultArea.setText("Error occurred while getting employee information from DB.\n" + e);
            throw e;
        }
    }

    //Search all employees
    @FXML
    private void searchEmployees(ActionEvent actionEvent) throws SQLException, ClassNotFoundException {
        try {
            //Get all Employees information
            ObservableList<Employee> empData = EmployeeDAO.searchEmployees();
            //Populate Employees on TableView
            populateEmployees(empData);
        } catch (SQLException e){
            System.out.println("Error occurred while getting employees information from DB.\n" + e);
            throw e;
        }
    }

    //Initializing the controller class.
    //This method is automatically called after the fxml file has been loaded.
    @FXML
    private void initialize () {
        /*
        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're using a Java 8 feature called Lambdas.
        (Another option would be to use a PropertyValueFactory, but this is not type-safe

        We're only using StringProperty values for our table columns in this example.
        When you want to use IntegerProperty or DoubleProperty, the setCellValueFactory(...)
        must have an additional asObject():
        */
        empIdColumn.setCellValueFactory(cellData -> cellData.getValue().employeeIdProperty().asObject());
        empNameColumn.setCellValueFactory(cellData -> cellData.getValue().firstNameProperty());
        empLastNameColumn.setCellValueFactory(cellData -> cellData.getValue().lastNameProperty());
        empEmailColumn.setCellValueFactory(cellData -> cellData.getValue().emailProperty());
        empPhoneNumberColumn.setCellValueFactory(cellData -> cellData.getValue().phoneNumberProperty());
        empHireDateColumn.setCellValueFactory(cellData -> cellData.getValue().hireDateProperty());
    }

    //Populate Employee
    @FXML
    private void populateEmployee (Employee emp) throws ClassNotFoundException {
        //Declare and ObservableList for table view
        ObservableList<Employee> empData = FXCollections.observableArrayList();
        //Add employee to the ObservableList
        empData.add(emp);
        //Set items to the employeeTable
        employeeTable.setItems(empData);
    }

    //Set Employee information to Text Area
    @FXML
    private void setEmpInfoToTextArea ( Employee emp) {
        resultArea.setText("First Name: " + emp.getFirstName() + "\n" +
                "Last Name: " + emp.getLastName());
    }

    //Populate Employee for TableView and Display Employee on TextArea
    @FXML
    private void populateAndShowEmployee(Employee emp) throws ClassNotFoundException {
        if (emp != null) {
            populateEmployee(emp);
            setEmpInfoToTextArea(emp);
        } else {
            resultArea.setText("This employee does not exist!\n");
        }
    }

    //Populate Employees for TableView
    @FXML
    private void populateEmployees (ObservableList<Employee> empData) throws ClassNotFoundException {
        //Set items to the employeeTable
        employeeTable.setItems(empData);
    }

    //Update employee's email with the email which is written on newEmailText field
    @FXML
    private void updateEmployeeEmail (ActionEvent actionEvent) throws SQLException, ClassNotFoundException {
        try {
            EmployeeDAO.updateEmpEmail(empIdText.getText(),newEmailText.getText());
            resultArea.setText("Email has been updated for, employee id: " + empIdText.getText() + "\n");
        } catch (SQLException e) {
            resultArea.setText("Problem occurred while updating email: " + e);
        }
    }

    //Insert an employee to the DB
    @FXML
    private void insertEmployee (ActionEvent actionEvent) throws SQLException, ClassNotFoundException {
        try {
            EmployeeDAO.insertEmp(nameText.getText(),surnameText.getText(),emailText.getText());
            resultArea.setText("Employee inserted! \n");
        } catch (SQLException e) {
            resultArea.setText("Problem occurred while inserting employee " + e);
            throw e;
        }
    }

    //Delete an employee with a given employee Id from DB
    @FXML
    private void deleteEmployee (ActionEvent actionEvent) throws SQLException, ClassNotFoundException {
        try {
            EmployeeDAO.deleteEmpWithId(empIdText.getText());
            resultArea.setText("Employee deleted! Employee id: " + empIdText.getText() + "\n");
        } catch (SQLException e) {
            resultArea.setText("Problem occurred while deleting employee " + e);
            throw e;
        }
    }
}

Main Class

At the end of the first JavaFX article, I described details of the main method. 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:

package sample;

import javafx.application.Application;
import javafx.fxml.FXMLLoader;
import javafx.scene.Scene;
import javafx.scene.layout.AnchorPane;
import javafx.scene.layout.BorderPane;
import javafx.stage.Stage;

import java.io.IOException;

//Main class which extends from Application Class
public class Main extends Application {

    //This is our PrimaryStage (It contains everything)
    private Stage primaryStage;

    //This is the BorderPane of RootLayout
    private BorderPane rootLayout;

    @Override
    public void start(Stage primaryStage) {
        //1) Declare a primary stage (Everything will be on this stage)
        this.primaryStage = primaryStage;

        //Optional: Set a title for primary stage
        this.primaryStage.setTitle("SW Test Academy - Sample JavaFX App");

        //2) Initialize RootLayout
        initRootLayout();

        //3) Display the EmployeeOperations View
        showEmployeeView();
    }

    //Initializes the root layout.
    public void initRootLayout() {
        try {
            //First, load root layout from RootLayout.fxml
            FXMLLoader loader = new FXMLLoader();
            loader.setLocation(Main.class.getResource("view/RootLayout.fxml"));
            rootLayout = (BorderPane) loader.load();

            //Second, show the scene containing the root layout.
            Scene scene = new Scene(rootLayout); //We are sending rootLayout to the Scene.
            primaryStage.setScene(scene); //Set the scene in primary stage.

            /*//Give the controller access to the main.
            RootLayoutController controller = loader.getController();
            controller.setMain(this);*/

            //Third, show the primary stage
            primaryStage.show(); //Display the primary stage
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //Shows the employee operations view inside the root layout.
    public void showEmployeeView() {
        try {
            //First, load EmployeeView from EmployeeView.fxml
            FXMLLoader loader = new FXMLLoader();
            loader.setLocation(Main.class.getResource("view/EmployeeView.fxml"));
            AnchorPane employeeOperationsView = (AnchorPane) loader.load();

            // Set Employee Operations view into the center of root layout.
            rootLayout.setCenter(employeeOperationsView);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static void main(String args) {
        launch(args);
    }
}

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

2-16

The result of the program is shown below.

2-17

Multi-threaded JavaFX Database Operations

Sometimes big queries take 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 in a multi-threaded way as follows:

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

//For multithreading: Create executor that uses daemon threads:
exec = Executors.newCachedThreadPool((runnable) -> {
   Thread t = new Thread (runnable);
   t.setDaemon(true);
   return t;
});

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

//Populate Employees for TableView with MultiThreading (This is for example usage)
private void fillEmployeeTable(ActionEvent event) throws SQLException, ClassNotFoundException {
    Task<List<Employee>> task = new Task<List<Employee>>(){
        @Override
        public ObservableList<Employee> call() throws Exception{
            return EmployeeDAO.searchEmployees();
        }
    };

    task.setOnFailed(e-> task.getException().printStackTrace());
    task.setOnSucceeded(e-> employeeTable.setItems((ObservableList<Employee>) task.getValue()));
    exec.execute(task);
}

You can use the fillEmployeeTable method instead of the populateEmployees method. In this example, our data is not too big so I don’t need to use the multi-threaded function for data populate operation but if you work with the 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

GitHub Project

 https://github.com/swtestacademy/javafxexample

Thanks,
Onur Baskirt

84 thoughts on “Database Operations in JavaFX with Complete Example!”

  1. 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

    Reply
  2. 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

    Reply
  3. 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; ?
    }

    Reply
  4. 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.

    Reply
  5. 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.

    Reply
  6. 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.

    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.

      Reply
  7. 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?…

    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.

      Reply
  8. 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.

    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.

      Reply
  9. 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.

    Reply
  10. 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?

    Reply
    • Hi John,

      I have the same mistake. Which solution solved the issue for you?
      (I have the same code what we can reach from this tutorial)

      Reply
  11. 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

    Reply
  12. 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.

    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.

      Reply
  13. 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

    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.

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

    Reply
  15. 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

    Reply
  16. 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?

    Reply
  17. 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..

    Reply
  18. 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

    Reply
  19. Hello Onur,
    I am planning to develop an application with licensing in javaFX. But i am not sure how actually i need to do it without using a database and yet strong and secured. Can you please show me the right direction so that i can make my project no matter it is a hello world alerting application but with a license with expiry period of 3, 6 months and 1 year.

    Thank you in advance.

    Reply
  20. Just I ran across the article and reviews.
    As soon as the time comes, I will begin to study this article in more detail.

    What can I say right now – you, Onur Baskirt very sympathetic person. Everyone who asked for your help, received an answer.
    Only for that, I may say: – many thanks!

    Reply
    • Thank you, Dima. You are more sympathetic than me. :) you are welcome! ;) I am also learning a lot of things and I am also an ordinary human from the planet earth. :) By the way, you can also improve that code by using hibernate etc. Good luck!

      Reply
  21. So, had a time to inspect code and article in details. What i may say – Really Very useful! A lot of comments helps a lot. A quite transparent and clean code.
    It help me a lot. Thank you for that!

    But i have one question, and would be very thankful with tips.

    I have a table where should be saved images as well we other string data.
    Say, table is Person and there are fields:
    id(integer), name(string), image(blob)

    So in Person class, id is an IntegerProperty, name StringProperty and what about image?? There is no blob property or something like that (??)

    Reply
  22. Why in EmployeeDAO class you giving Strings parameters to methods?
    I think would be much convinient to give Employee object as parameter and then take his attributes by using getters.

    Reply
    • Hi, I implemented in this way but you can also try in your way. You can improve the code by also using hibernate or another ORM. This is just a simple example project. Thanks for your valuable comment.

      Reply
  23. Problem occurred while inserting employee java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection
    any solution for these error

    Reply
  24. Hey Onur,

    I have similarly set up a project like this except my tableView does not populate with any data. I am not sure which area I went wrong with, I am getting all nulls from inside the intialize method in the controller class when trying to set each cell…. I am assuming the String propertys are not getting data…

    any advice would be greatly appreciated, I can send code sample, but it’s similar to what you have.

    Thanks!

    Reply
    • Hi Thomas, nowadays I have some serious health problems. I can not focus on the site much. I suggest you debug the problem. I think there might be sth wrong when u are setting the data? Are u sure you are retrieving not null data? Did u put any breakpoints at those parts to evaluate each expression with IntelliJ? Today at work I faced a similar problem and I caught that problem in debug mode.

      Reply
  25. PLEASE SIR I WANT TO KNOW HOW DO I PARSE ( unmarshal A DUMP FILE ) dump file back into java Objects??? for a personal project
    thanks in advance for your answer

    Reply
  26. ok all is good!! except that I want to display the the numbers (floats) with two decimals in the TableView ( to represents some prices ) …thanks again for this awesome tuto!

    Reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.