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

1. Go to https://start.spring.io/
   
2. Choose the Project type depending on what build tool you want to use. We have chosen maven as the build tool.
   
3. Choose the Spring Boot version.
   
4. Fill in the Project Metadata info.
   
5. Add the dependencies . In the case of this project we need Spring Web, Spring Data JPA, MySQL Driver.
   
6. Generate the project and import it in your preferred editor

Setting up the Database

• Create database employees in MySQL
• Create table employee

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.

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

JPQL queries can be executed in different ways.

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

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.

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.

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

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.

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

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 -:

• CREATE

The persist method of EntityManager is used to save the Employee instance in the database.

• UPDATE AND DELETE

Updating and deleting the Employee instance using createQuery method of EntityManager and executeUpdate method of the Query interface.

• GET BY ID

This method retrieves an employee record based on the passed ID.

• BETWEEN … AND…

The JPQL Query for getting employee list based on a range of specified values is executed in the following method.

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

• SORTING

The JPQL Query to retrieve records in sorted order. The following method retrieves records sorted in descending order based on years of experience.

Find the link to complete code here.