Implementation of Data Access Layer with JPA JPQL in Spring Boot
JPA(Java Persistance API) — JPA is a specification of Java which is used to map Java objects to a relational database, acting as a bridge between the two. It doesn’t perform operations on its own but requires implementation with ORM tools like Hibernate.
Object relational mapping (ORM) is the mapping of Java objects to Database tables.
JPA is used to work directly with objects rather than using SQL statements to perform operations. Persistance metadata (defined by annotations of XML file) defines the mapping between Java objects and the database tables.
JPA defines a SQL-like Query language for the implementation of static and dynamic queries. JPA JPQL is an object oriented query language used to perform database operations. It uses the entity object to perform operations on the database records. The JPQL queries are then transformed to SQL using a JPA implementation. This can be used with any type of database such as MySQL, H2, Oracle, etc.
Implementation
Technology Stack
Spring boot framework
Maven 3.6.3
MySQL 8.0.19
Setting up the project
- Go to https://start.spring.io/
- Choose the Project type depending on what build tool you want to use. We have chosen maven as the build tool.
- Choose the Spring Boot version
- Fill in the Project Metadata info.
- Add the dependencies . In the case of this project we need Spring Web, Spring Data JPA, MySQL Driver.
- Generate the project and import it in your preferred editor
Setting up the Database
- Create database employees in MySQL
- Create table employee
CREATE DATABASE employees;
USE employees;
CREATE TABLE employee(id int not null auto_increment primary key,name varchar(50),gender varchar(10),experience_in_years int,experience_in_months int,experience_in_days int);
The Employee Model will contain the following attributes — id, name, gender, experience in years, experience in months, experience in days.
Add database configuration with application.properties
The configuration for datasource is added in application.properties.
spring.datasource.url indicates the url of the database
spring.datasource.username and spring.datasource.password indicates the username and password of the database being used.
spring.jpa.hibernate.ddl-auto is a Spring Data JPA property which manipulates how the database schema will be transformed on application startup. The values can be create (creates a new schema every time the application is started), create-drop (the schema is dropped once the application is stopped and only created once the application is started again), update (updates the schema in case of any change) and validate (only validates the schema, not making any changes).
spring.jpa.properties.hibernate.dialect property makes hibernate generate better SQL for the database
spring.jpa.show-sql property when set to true logs the SQL Queries in the console.
spring.datasource.url = jdbc:mysql://localhost:3306/employees
spring.datasource.username =
spring.datasource.password =
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect
spring.jpa.hibernate.ddl-auto = update
spring.jpa.show-sql=trueDefine the Entity
The basic unit of persistence in JPA is the entity. It is a Java class which uses metadata described be in the form of annotations or defined in an XML file(in this example annotations are used) to map the class to the database table.
@Entity annotation is used on all the classes which need to be persisted in the database. An instance of the class is persisted as a row in the database table.
The database table name can be defined specifically using @Table(name=”______”) annotation. Otherwise the table takes the name of the corresponding class.
@Id is used to define the primary key
The primary key can be auto generated in the database using the @GeneratedValue annotation.
The fields in the Entity are saved as columns in the database table. The column name can be defined specifically using @Column(name=”______”) annotation. Otherwise the column takes the name of the corresponding field.
If the field does not need to be persisted in the database as a column it can be annotated with @Transient .
Different ways to execute a query using JPQL
Before starting to implement the queries , it is important to acquire an EntityManager instance.
EntityManager can be container-managed or application-managed.
Application-managed EntityManager is managed by the application, It needs to be created manually and the lifecycle has to be managed by us well.
In this example we use a container managed EntityManager instance where the container is responsible for creating the instance from EntityManagerFactory for us, beginning the transaction, committing it or rolling it back.
@PersistenceContext
EntityManager entityManager;JPQL queries can be executed in different ways.
- createQuery
The createQuery method is used to query the database using JPQL. This method is used to execute dynamic queries which are defined within the business logic of the application.
Query retrieves the matching record from the database table and also maps it to the Entity object.
private final String GET_EMPLOYEE_QUERY = "SELECT employee FROM Employee";@Transactional
public List<Employee> getAllEmployees() {
List<Employee> employees = new ArrayList<>();
Query getAllQuery =
entityManager.createQuery(GET_EMPLOYEE_QUERY);
employees = (List<Employee>) getAllQuery.getResultList();
return employees;
}
getAllEmployees method retrieves the list of employees using createQuery(String jpql) method. The JPQL query is defined in GET_EMPLOYEE_QUERY. Instead of mentioning the table name (employee) in the query, the Entity name (Employee) is mentioned. Since JPA cannot determine the result type, we need to cast the result type before returning it. The getResultList() method is used to retrieve a list of the results after executing the JPQL query.
TypedQuery
JPA also provides a special Query sub-type known as a TypedQuery as a solution to JPA not being able to determine what the result type will be. This is a good solution whenever the result type is known beforehand and also helps in avoiding casting exceptions.
private final String GET_EMPLOYEE_BY_YEARS_OF_EXPERIENCE = "SELECT employee FROM Employee employee WHERE experienceInYears=?1";@Transactional
public List<Employee> getEmployeeByYearsOfExperience(int years) {
List<Employee> employees = new ArrayList<>()
TypedQuery<Employee> getQueryByYear = entityManager
.createQuery(GET_EMPLOYEE_BY_YEARS_OF_EXPERIENCE, Employee.class).setParameter(1, years);
employees = getQueryByYear.getResultList();
return employees;
}
getEmployeesByYearsOfExperience method retrieves the list of employees having specified years of experience using createQuery(String jpql) method. The JPQL query is defined in GET_EMPLOYEES_BY_YEARS_OF_EXPERIENCE.
Positional Parameter has been used to specify the year of experience.
Parameters are used when we don’t want to hardcode values in our query but want the client to specify it. In this example we have used positional parameter.
Positional parameters are declared with a question mark (?) followed by the numeric position of the parameter in the query. We start with 1 and increment the position with every parameter. The Query.setParameter(integer position, Object value) method is used to set the parameter values. We can also use the same parameter more than once within the same query.
We can execute the queries using the following Query interface methods :-
> executeUpdate() — This method executes the update and delete operation.
> getFirstResult() — This method returns the first positioned result the query object was set to retrieve.
> getMaxResults() — This method returns the maximum number of results the query object was set to retrieve.
> getResultList() — This method returns the list of results as an untyped list.
> getSingleResult() — This method returns a single untyped result.
- createNamedQuery
The createNamedQuery method is used to execute static queries. The queries can be defined in the metadata by using the NamedQuery annotation on the Entity class itself. The name element of @NamedQuery specifies the name of the query that will be used with the createNamedQuery method. All NamedQueries must have a unique name.
@Transactional
public List<Employee> getEmployeeByMonthsOfExperience(int months) {
List<Employee> employees = new ArrayList<>();
Query getQueryByMonth = entityManager.createNamedQuery("findEmployeeByMonthsOfExperience")
.setParameter(1, months);
employees = (List<Employee>) getQueryByMonth.getResultList();
return employees;
}getEmployeesByMonthsOfExperience method retrieves the list of employees having specified months of experience using createNamedQuery(String name) method. The name of the NamedQuery is specified in the metadata.
@Entity
@Table(name = "employee")
@NamedQuery(name = "findEmployeeByMonthsOfExperience", query = "SELECT employee FROM Employee employee WHERE experienceInMonths=?1")
public class Employee {
//
//
//
}- createNativeQuery
The createNativeQuery method is used to execute native/pure SQL queries.
A NativeQuery is simply an SQL query. This allows us to utilise features not available in JPQL syntax. However we lose database portability of the application.
private final String GET_EMPLOYEE_BY_DAYS_OF_EXPERIENCE = "SELECT * FROM employee WHERE experience_in_days=?1";@Transactional
public List<Employee> getEmployeeByDaysOfExperience(int days) {
List<Employee> employees = new ArrayList<>();
Query getQueryByDay = entityManager.createNativeQuery(GET_EMPLOYEE_BY_DAYS_OF_EXPERIENCE).setParameter(1, days);
employees = (List<Employee>) getQueryByDay.getResultList();
return employees;
}
getEmployeesByDaysOfExperience method retrieves the list of employees having specified days of experience using createNativeQuery(String sql) method. The SQL query is defined in GET_EMPLOYEES_BY_DAYS_OF_EXPERIENCE. Positional parameters can be used with Native SQL Queries as well.
Some extra methods -:
a) CREATE
The persist method of EntityManager is used to save the Employee instance in the database.
@Transactional
public void createEmployee(Employee employee) {
entityManager.persist(employee);
}b) UPDATE AND DELETE
Updating and deleting the Employee instance using createQuery method of EntityManager and executeUpdate method of the Query interface.
private final String UPDATE_EMPLOYEE_QUERY = "UPDATE Employee SET name=?1,gender=?2,experienceInYears=?3,experienceInMonths=?4,experienceInDays=?5 WHERE id=?6";private final String DELETE_EMPLOYEE_QUERY = "DELETE FROM Employee WHERE id=?1";@Transactional
public void updateEmployee(Employee employee, int id) {
Query updateQuery = entityManager.createQuery(UPDATE_EMPLOYEE_QUERY)
.setParameter(1, employee.getName())
.setParameter(2, employee.getGender())
.setParameter(3, employee.getExperienceInYears())
.setParameter(4, employee.getExperienceInMonths())
.setParameter(5, employee.getExperienceInDays())
.setParameter(6, id);
updateQuery.executeUpdate();
}@Transactional
public void deleteEmployee(int id) {
Query deleteQuery = entityManager.createQuery(DELETE_EMPLOYEE_QUERY)
.setParameter(1, id);
deleteQuery.executeUpdate();
}
c) GET BY ID
This method retrieves an employee record based on the passed ID.
private final String GET_EMPLOYEE_BY_ID_QUERY = "SELECT employee FROM Employee employee WHERE id =?1";@Transactional
public Employee getEmployeeById(int id) {
Employee employee = new Employee();
Query getQueryById = entityManager.createQuery(GET_EMPLOYEE_BY_ID_QUERY)
.setParameter(1, id);
employee = (Employee) getQueryById.getSingleResult();
return employee;
}
d) BETWEEN … AND…
The JPQL Query for getting employee list based on a range of specified values is executed in the following method.
private final String GET_EMPLOYEE_BETWEEN_YEARS_OF_EXPERIENCE = "SELECT employee FROM Employee employee WHERE experience_in_years BETWEEN ?1 and ?2";@Transactional
public List<Employee> getEmployeeBetweenYearsOfExperience(int start, int end) {
List<Employee> employees = new ArrayList<>();
Query getQueryBetweenYear = entityManager.createQuery(GET_EMPLOYEE_BETWEEN_YEARS_OF_EXPERIENCE)
.setParameter(1, start).setParameter(2, end);
employees = (List<Employee>)getQueryBetweenYear.getResultList();
return employees;
}
e) COUNT, MAX AND MIN
The JPQL Queries to execute Aggregation methods like getting employee count, maximum years of experience and minimum years of experience are executed in the following method.
private final String GET_EMPLOYEE_COUNT = "SELECT COUNT(employee) FROM Employee employee";private final String GET_MAXIMUM_EXPERIENCE_IN_YEARS = "SELECT MAX(experienceInYears) FROM Employee";private final String GET_MINIMUM_EXPERIENCE_IN_YEARS = "SELECT MIN(experienceInYears) FROM Employee";@Transactional
public Map<String, Object> getEmployeeStats() {
Map<String, Object> employeeStats = new HashMap<>();
Query employeeCount = entityManager.createQuery(GET_EMPLOYEE_COUNT); Query maximumExperienceInYear = entityManager.createQuery(GET_MAXIMUM_EXPERIENCE_IN_YEARS); Query minimumExperienceInYear = entityManager.createQuery(GET_MINIMUM_EXPERIENCE_IN_YEARS); employeeStats.put("employeeCount", (Long) employeeCount.getSingleResult()); employeeStats.put("maximumExperienceInYear", (Integer) maximumExperienceInYear.getSingleResult()); employeeStats.put("minimumExperienceInYear", (Integer) minimumExperienceInYear.getSingleResult()); return employeeStats;
}
f) SORTING
The JPQL Query to retrieve records in sorted order. The following method retrieves records sorted in descending order based on years of experience.
private final String GET_EMPLOYEE_SORTED_BY_YEAR = "SELECT employee FROM Employee employee ORDER BY experienceInYears DESC";@Transactional
public List<Employee> getEmployeeSortedByYearExperience() {
List<Employee> employees = new ArrayList<>();
Query getQuerySortedByYear = entityManager.createQuery(GET_EMPLOYEE_SORTED_BY_YEAR);
employees = (List<Employee>) getQuerySortedByYear.getResultList();
return employees;
}
Find the implemented code here.
