JAKARTA EE FOR JUNIOR DEVELOPERS - Lesson 13 Retrieving Data with JPQL

INTRODUCTION

In this Jakarta EE for Junior Developers course, after first making a simple SELECT request to the database using JPQL, we will display the list of employees in a table after adding the PrimeFaces library to our project.

JPQL (Jakarta Persistence Query Language)

There is an excellent tutorial on JPQL (https://jakarta.ee/learn/docs/jakartaee-tutorial/current/persist/persistence-querylanguage/persistence-querylanguage.html) from Jakarta EE that focuses on the basic commands and syntax, allowing you to start writing and sending queries to the database that supports your project.

The simple web application we have created so far has the ability to insert new employees into the database, but it lacks the functionality to display a complete list of all the company's employees. To perform such an action, we essentially need to send a SELECT query to the Employees table and, once we receive the result, display it to the end user.

The implementation of this code is simple since it only requires the creation of a new, straightforward method inside the EmployeeInfoController class. Let’s first look at the code of the method, and then we will explain what it exactly means.

EmployeeInfoController.java

 
..................................
public List<Employee> getEmployees() throws NotSupportedException, SystemException, RollbackException, HeuristicMixedException, HeuristicRollbackException {

            Query  query = em.createQuery("SELECT e FROM Employee e ");
             employees = query.getResultList();
        
        return employees;
        
    }
 ....................................

As a first step, we need to specify the entity where we are retrieving data from.

"SELECT e FROM Employee e": A JPQL SELECT statement that retrieves all Employee entities from the database.

SELECT e: Selects an alias (e) representing the Employee entity.
FROM Employee e: Specifies that e refers to the Employee entity.
JPQL is different from SQL: It works with entity classes (Employee) instead of database table names.

To execute the query we need to pass it as a parameter to the createQuery( ) method which is managed by the entity manager em object. The getResultList( ) method executes the query and retrieves the result as a List<Employee>. This method fetches all matching records from the database and stores them in a list. If no records are found, it returns an empty list (not null). So at this point we have a list of all the employees.

PRIMEFACES

PrimeFaces is a popular UI (User Interface) framework for Jakarta Faces applications. It provides a rich set of UI components and features to help developers build modern, responsive, and interactive web applications in Java. To use PrimeFaces in an application, we just need to add the PrimeFaces library to the POM file.

POM.xml

.......................................... 
<dependency>
    <groupId>org.primefaces</groupId>
    <artifactId>primefaces</artifactId>
    <version>14.0.0</version>
    <classifier>jakarta</classifier>
</dependency>
......................................... 

Now we need to create our employeelist Jakarta Face page.

employeelist.xhtml

<?xml version='1.0' encoding='UTF-8' ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"
      xmlns:h="jakarta.faces.html"
      xmlns:p="http://primefaces.org/ui"
      xmlns:f="jakarta.faces.core">
    <h:head>
         <link rel="stylesheet" 
              href="https://cdn.jsdelivr.net/npm/bootstrap@4.0.0/dist/css/bootstrap.min.css" 
              integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" 
              crossorigin="anonymous"></link>
        <title>Employees List</title>
    </h:head>
    <h:body>

        <h:form >
            <div class="card">

                <p:dataTable var="employee" value="#{employeeInfoController.employees}" lazy="false" >

                    <p:column headerText="Employee ID">
                        <h:outputText value="#{employee.employee_id}" />
                    </p:column>
                    <p:column headerText="First Name">
                        <h:outputText value="#{employee.firstName}" />
                    </p:column>
                    <p:column headerText="Last Name">
                        <h:outputText value="#{employee.lastName}" />
                    </p:column>
                    <p:column headerText="Email">
                        <h:outputText value="#{employee.email}" />
                    </p:column>
                     <p:column headerText="Department">
                         <h:outputText value="#{employee.department.name}" />
                    </p:column>
                </p:dataTable>
            </div>
        </h:form>
        <h:form>
            
            <div class="col-5">
                <h:commandButton value="Back to Home" action="index" 
                           class="btn btn-primary btn-sm w-100">
            </h:commandButton>
            </div>
        </h:form>
    </h:body>
</html>

The major line of code is the value="{employeeInfoController.employees}" that binds the table to a list of employees provided by the employeeInfoController. When the page is rendered, the employeeInfoController provides a list of employees. The list id displayed in a PrimeFaces dataTable with columns for Employee ID, First Name, Last Name, Email, and Department. Below the table, there's a button that allows the user to navigate back to the home page.

index.xhtml

.................................  
<h:form>

                <div class="button-container">
                    <h:commandButton class="btn btn-primary" value="View All Employees" action="employeeslist" 
                                     style="font-size: 30px">
                    </h:commandButton>
                </div>
            </h:form>
............................ 

Before we run the application we add one more button to the home page so the user has the option to either add a new employee to the database or get a list of all the existing employees.

RUNNING THE WEB APP