JPA Tutorial

What is Object persistence?
Today most of the development is carried out in an object oriented manner using languages like java,C++ etc.
When thinking in terms of Java as the programming language,the business logic of an application works with Objects of different class types. However when dealing with the data store,it's important to note that the tables of a database are not objects,which becomes an issue. This is where the concept of Object Persistence comes in.


Object Persistence deals with persistence in object oriented program such as java.It means determining how objects and their relationships are persisted in a relational database.


What is ORM?
Object Oriented Programming use Classes whereas Relational Database use tables. In programming this will creates a gap. This gap is called impedance mismatch.
We can bridge the gap between Object oriented model and Relational model is known as Object Relational Mapping (ORM).
The Object-Relational Impedance Mismatch:
Object-Relational Impedance Mismatch' (sometimes called the 'paradigm mismatch') is just a fancy way of saying that object models and relational models do not work very well together. RDBMSs represent data in a tabular format , whereas object-oriented languages, such as Java, represent it as an interconnected graph of objects.

Solution of Impedance Mismatch:
Use an ORM mapping tool which will provide a simple API for storing and retrieving Java objects directly from database.
There are several good ORM tools available in market that will do mapping between Objects and Database tables and solve the impedance mismatch.
Following are ORM tools:
·         Hibernate
·         OpenJPA
·         EJB Entity Bean
·         EclipseLink
·         TopLink
           

Java Persistence API:
JPA has been introduced in Enterprise Java Beans 3(EJB3).Java Persistence API is new way of communicating with the database.
The Java Persistence API (JPA) is a standard API for accessing databases from within Java applications. The main advantage of JPA over JDBC is that in JPA data is represented by classes and objects rather than by tables and records as in JDBC.


JPA Practical Overview

What is JPA ?
The process of mapping Java objects to database tables and vice versa is called "Object-relational mapping" (ORM).
Java Persistence API (JPA) provides Plain Old Java Object standard and object relational mapping (OR mapping) for data persistence among applications. JPA is a specification and several implementations are available. Popular implementations are Hibernate, EclipseLink and Apache OpenJPA. The reference implementation of JPA is EclipseLink.
By using JPA the developer can map, store, update and retrieve data from relational databases to Java objects and vice versa. JPA permits the developer to work directly with objects rather then with SQL statements.
The JPA implementation is typically called persistence provider. JPA can be used in Java-EE and Java-SE applications

The mapping between Java objects and database tables is defined via persistence metadata. JPA typically defines the metadata via annotations in the Java class. Alternatively the metadata can be defined via external defined XML or a combination of both XML and Annotation. A XML configuration overwrites the annotations.
JPA uses Annotations or XML to map Objects to a database,these objects are called Entities.
Entities are Plain Old Java Objects(POJO).
JPA has its own Query Language called Java Persistence Query Language(JPQL).It generates all the necessary SQL calls for CRUD (Create Retrieve Update Delete) operation.


Typical Web Application using JPA



Application communicates with the configured JPA provider(Here Hibernate) to access the database.
The information about the mapping between the instance variables of classes and the columns of the tables in the database is mapped either through XML ans/or POJO with annotations.
POJO are Java Classes that represent the tables in the database.
The application talks to hibernate using JPA to perform CRUD operations on the database tables.


Architecture Of JPA:


Persistence:
The javax.persistence.Persistence class contains methods to obtain EntityManagerFactory instances.


EntityManagerFactory:
The EntityManagerFactory is used to create an instance of EntityManager.The EntityManagerFactory is created with the help of Persistence Unit during the application start up.It serves as a factory for EntityManager.
EntityManager:
The EntityManager interface is providing the API for interacting with the Entity.It provides the main interface to perform actual database operations.
All the POJO's i.e persistent objects are saved and retrieved with the help of an EntityManager Object.
Typically,EntityManager objects are created as needed and destroyed when not required.



Entity Bean:
Entity is the persistence (POJO) objects that represent one record in the table. The Entity is simple annoted POJO class, which is easy to develop.
Consider the following simple java class, named Employee.
class Employee{

 private int empId;
 private String name;
 private String phone;
 private String email;

// set/get
}
The above class represents a Employee object which has id, name, phone and email.If we want to persist this Employee java class using JPA we need to use @Entity annotation.
@Entity :
The first step is to qualify this java class as an entity. This can be done by marking the class with @Entity annotation.We have to use @Entity annotation to make this POJO class an entity.We need to update the Employee class as shown below.
@Entity
class Employee{
  private int empId;
  private String name;
  private String phone;
  private String email;

// set/get
}


@Id :
An Entity Bean MUST have an Id, which can be a single attribute or several attributes ( composite primary key). The Id of the Entity Bea MUST be serializable.A field that is marked with @Id annotation will be treated as a primary key for the table by the persistent engine.In our case, since no two employee in the company have same ID so empId is an ideal candidate to be marked with @Id annotation.
Now our updated code will looks like.
@Entity
class Employee{

 @Id
 private int empId;
 private String name;
 private String phone;
 private String email;

// set/get
}
Now we can persist Employee class Object using JPA. @Entity and @Id annotation is sufficient for Entity bean for persisting using JPA.Following is the default values for table and column names in this case.
Table Name – EMPLOYEE
Column Names – [EMPID, NAME, PHONE and EMAIL]
If you are providing only @Entity annotation then by default table name will be java class name and column name will be variables name. In the above code it will be EMPID, NAME, PHONE and EMAIL.We can customize these values based on our requirement.


Customizing the Entity Bean :
In JPA defaults that are created and provided by a persistence engine are sufficient. However in certain situations you need to customize these values. The entity class can be customized with a set of annotations available in the javax.persistence package.
Change the default table name :
By default the table name will be name of the class. We can change the default table name with the help of @Entity annotation.Consider the code given below.
@Entity(name = "EMP_DB")
    public class Employee{
    ...
    }
Now, the table name is changed to EMP_DB
Customizing the Column default values(@Column Annotation):
The default name of the columns, their size, whether they can accept null values or not etc., can be customized using the @Column annotation.
Consider the code given below.
@Column(name = "EMP_NAME", nullable = false, length = 100)
private String name;

@Column(name = "EMP_MAIL" nullable = true, length = 100)
private String email;
The name property will override the default column name (which is the same as that of the field name in the Entity class). The nullable property tells that whether the column can accept null values. Length property is only applicable if the type of the column is String (or VARCHAR). There are also properties like scale and precision which is applicable only when the type of the column is NUMBER.


@GeneratedValue
This annotation provides the strategy to be used for the generation of the Id attributes of the Entity Bean. It is implementation specific and its behaviour is not defined on the specification. There are 4 possible choices :
§  auto
§  identity
§  table
§  sequence


EntityTransaction:
A Transaction represents a unit of work with the database.
Any kind of modifications via the EntityManager Object are placed within a transaction.An EntityManager object helps creating an EntityTransaction Object.


Query:
Persistent Objects are retrieved using a Query object.
Query objects allows using SQL or Java Persistence Query Language[JPQL] to retrieve the actual data from the database.
=====================================================================

Tools Required:

For this tutorial eclipse,EclipseLink and MySQL are needed, so if you are missing any of these this is the time to go and get them.Click on the below link to download the required software:
·         Eclipse
·         EclipseLink
·         mysql


EclipseLink Installation: Java Persistence (JPA) 2.0 Standards:

Download the "EclipseLink Installer Zip" implementation from the EclipseLink Download Site .
Extract the Zip file in your local drive.Extracted file contains several jar files.We need eclipselink.jar and javax.persistence_*.jar.

mysql database:

This example is using mysql as database.Download mysql and install it in your system.
We need MySQL Connector jar for the connection between mysql database and java application.

Oracle database: ojdbc6.jar



Directory Structure of the Application



Steps to implement this example

Following is the steps involved for developing this application

Step1: Create Java Project

Create a Java project "JPAEXAMPLE". Create a folder "lib" inside the project and place the required JPA jars and MySQL Connector jar into this folder. Add the jar files to classpath of the project.


Step2: Create Entity Bean

An Entity Bean is a light weight persistent domain object.The Entity Bean is a Java Object that has to follow a few rules Our Entity Bean will be called Employee.Java , and it is created inside the package com.example.pojo .
Employee.java

package com.example.pojo;
public class Employee {
    private Long id;
    private String name;
    private String email;
    private String department;

    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public String getDepartment() {
        return department;
    }
    public void setDepartment(String department) {
        this.department = department;
    }
   /* public String toString() {
        return "Employee [name=" + name + ", email=" + email + " , department="
                + department + "]";
    }
     */
      }


Step3: Create persistence.xml

Create a directory "META-INF" in "src" folder and create the file "persistence.xml" inside it and add following code into this file.
persistence.xml

<?xml version="1.0" encoding="UTF-8" ?>
<persistence xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence
http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
version="2.0" xmlns="http://java.sun.com/xml/ns/persistence">
<persistence-unit name="TestPersistence"
transaction-type="RESOURCE_LOCAL">
<class>com.example.pojo.Employee</class>
<properties>
<property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />
For oracle: value =” oracle.jdbc.driver.OracleDriver”
<property name="javax.persistence.jdbc.url"
value="jdbc:mysql://localhost:3306/jpadb" />
For Orcale: value=” jdbc:oracle:thin:@localhost:1521:xe”
<property name="javax.persistence.jdbc.user" value="root" />
<property name="javax.persistence.jdbc.password"
value="mukesh" />
<!-- EclipseLink should create the database schema automatically -->
<property name="eclipselink.ddl-generation" value="create-tables" />
After first time creating the table comment the above line.
<property name="eclipselink.ddl-generation.output-mode"
value="database" />
</properties>
</persistence-unit>
</persistence>
The persistence.xml file indicates that there is only one Persistence Unit is mapped with the name TestPersistence, the transaction type for this Persistence Unit is RESOURCE_LOCAL. There are two types of transaction:
§  JTA
§  RESOURCE_LOCAL
If you selects RESOURCE_LOCAL then the transaction will be managed by the JPA Provider Implementation in use. If JTA is specified then the transactions will be managed by the Application Server.
If one only wants to have JPA transactions then RESOURCE_LOCAL is a good choice. If one would like the transactions to contain other resources other than JPA, like EJBs,JMS then JTA is the correct choice.


Step4: Test your Program

Create the following Test class which will create a new entry every time it is called. After the first call you need to remove the property "eclipselink.ddl-generation" from persistence.xml otherwise you will receive an error as EclipseLink tries to create the database scheme again. Alternative you could set the property to "drop-and-create-tables" but this would drop your database schema at every run.
Add following code into Test.java
Test.java

package com.example.test;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.Query;
import com.example.pojo.Employee;
public class Test {
    private static final String PERSISTENCE_UNIT_NAME = "employee";
      private static EntityManagerFactory factory;
      public static void main(String[] args) {
        factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
        EntityManager em = factory.createEntityManager();
        // read the existing entries and write to console
        /* For reading from Database */
/* After creating table use the commented code.  */
 /*      Query q = em.createQuery("select e from Employee e");
        List<Employee> employeeList = q.getResultList();
        for (Employee employee : employeeList) {
          System.out.println(employee);
        }
        System.out.println("Size: " + employeeList.size());
*/
        // create new todo
        em.getTransaction().begin();
        Employee emp = new Employee();
        emp.setName("Mukesh");
        emp.setEmail("m@gmail.com");
        emp.setDepartment("Finance");
        em.persist(emp);
        em.getTransaction().commit();
        em.close();
      }
    }




Run you program several times to see that the database is filled.

 Download Libraries:
mysql.jar
eclipselink.jar
persistence.jar
ojdbc6.jar

JPQL



What is and Why use JPQL?
Before going into what is JPQL, lets talk about SQL. SQL is a query language used for managing data in a relational database. It an ANSI and ISO standard, but if you try running a medium to high complexity SQL query in two different relational databases from two different vendors, you are in for a big surprise. Why is that? Because some vendors create specific dialects of the SQL language.
Now imagine for a second you have a big application that you just developed and deployed, which is using a specific relational database, and for some reason you decide to migrate your data to another relational database from a different vendor. Well, if you wrote your queries in SQL, you are well advised to go and double check all of your queries for correctness against the new dialect.
JPQL to the rescue. JPQL is a query language, much like SQL (syntax wise and all that), but instead of querying over a relational database, it queries over the persistent schema of entities and relationships (which is almost the same thing, but not quite!). What this means is that, if the relational model of the new database is still represented by the schema of entities and relationships you have on your project, then your JPQL queries will be portable and will work as expected. Emphasys on Portability.
Select Statements
For our tutorial we'll use the model created for the One-To-Many Tutorial but we will extend it a bit more:


You can get the MySQL Workbench file for this from the model folder of the sample project. Along with the Workbench file there is a SQL script that creates the model and adds a few entries to it so we can test our queries:

INSERT INTO `jpa2tut-queries`.`company` (`name`, `address`, `created_year`) VALUES ('JBay Solutions', 'Somewhere in Portugal', 2009);
INSERT INTO `jpa2tut-queries`.`company` (`name`, `address`, `created_year`) VALUES ('JetBrains', 'Somewhere in Prague', 2000);
INSERT INTO `jpa2tut-queries`.`company` (`name`, `address`, `created_year`) VALUES ('Google', 'Somewhere in the US', 1998);
INSERT INTO `jpa2tut-queries`.`company` (`name`, `address`, `created_year`) VALUES ('Yahoo', 'Somewhere in the US',1994);
INSERT INTO `jpa2tut-queries`.`company` (`name`, `address`, `created_year`) VALUES ('MySQL', 'Somewhere in an Oracle Office', 1995);
Also, check the src/main/java folder of the project in order to check the Entity Objects we have. An understanding of these objects is core to understanding the JPQL code we will write next.

A Simple Select

As you can see from the previous SQL bits, we have a few entries on the company table. Lets query for the list of those entries:
SELECT c FROM CompanyEntity  AS  c
At this point you should be going like: Wait a second please... I understand this language!. Like said before, JPQL is very much like the regular SQL, except that we perform queries on the persistent schema of entities and relationships.
Lets run this in Java. Check the files inside the src/test/java folder of the sample project. You should be looking for the QueryTesting.java file at the method testSimpleQuery:

Query query = em.createQuery("SELECT c FROM CompanyEntity AS c");
for ( CompanyEntity ce : (List<CompanyEntity>)query.getResultList()) {
    System.out.println(" -> Company : " + ce.getName() );
}

We give it a run:
[...]
[EL Info]: connection: 2014-10-14 16:41:13.476 --ServerSession(1563886825)--file:/home/rui/projects/jpa2tut-queries/target/test-classes/_jpa2tut-test login successful
 -> Company : JBay Solutions
 -> Company : JetBrains
 -> Company : Google
 -> Company : Yahoo
 -> Company : MySQL

Simple, yes? We create a Query Object with the JPQL query and we call em.getResultList(); . We cast the result to List<CompanyEntity> , because we know that getResultList() returns a List (it does) and we also know that the query we created returns objects of CompanyEntity type.
We can also restrict the number of results being returned from the Query, by modifying the JPQL query used. Imagine we want to return all the companies that are called "JBay Solutions":

SELECT c FROM CompanyEntity AS c WHERE c.name='JBay Solutions'
And the code being (testSimpleQueryWhere method) :
Query query = em.createQuery("SELECT c FROM CompanyEntity AS c WHERE c.name='JBay Solutions'");
for ( CompanyEntity ce : (List<CompanyEntity>)query.getResultList()) {
    System.out.println(" -> Company : " + ce.getName() );
}

Give it a quick spin:
[EL Info]: connection: 2014-10-14 16:54:56.972--ServerSession(1861307614)--file:/home/rui/projects/jpa2tut-queries/target/test-classes/_jpa2tut-test login successful
 -> Company : JBay Solutions

Process finished with exit code 0
At this point one should be able to make pretty simple, but very useful queries, but some issues stand out:
·         We must be constantly casting the results
·         All our queries until now allow no input

Enter TypedQuery
In JPA2 a new type of Query object was introduced. The TypedQuery, which allows us to make a Query that is Typed. That is it, TypedQuery extends Query, allowing one to define straight away what is to be returned and avoid continuously Casting every single result.

Looking at our previous examples, we can modify them to use TypedQuery instead of Query by using an overloaded createQuery method from the EntityManager that also takes as a parameter the Type of the query result:

TypedQuery<CompanyEntity> query = em.createQuery("SELECT c FROM CompanyEntity AS c", CompanyEntity.class);

TypedQuery<CompanyEntity> query = em.createQuery("SELECT c FROM CompanyEntity AS c WHERE c.name='JBay Solutions'", CompanyEntity.class);
to perform the iteration now we do:
for (CompanyEntity ce : query.getResultList()) {
    System.out.println(" -> Company : " + ce.getName());
}

No casting, no nothing. For the complete code check both testSimpleTypedQuery and testSimpleTypedQueryWhere methods of the QueryTesting.java file on the test sources.
We still have the no input issue to work on.

Passing Input to the Queries
In a way, some examples we shown before could be easily modified in a way to allow inputs to be passed onto them. Check this out:

String inputName = "JBay Solutions";
TypedQuery<CompanyEntity> query = em.createQuery("SELECT c FROM CompanyEntity AS c WHERE c.name='" + inputName + "'", CompanyEntity.class);

Can you spot the problem with this? Well, simple, if you want to perform this query several times with different inputs, then you need to continuously re-create a new Query object. Not counting the performance impact it will have, it also leads to the JPQL not being readable at all. Imagine a query like the one presented but with several conditional expressions on the WHERE clause. What a nightmare.
To avoid these situations we have in JPQL:
·         Named Parameters
·         Positional Paramenters
Constructing a Query with any of these types of dynamic parameters allows a Query to be reused (and therefore avoid constant instantiation of Query objects) and makes the JPQL query much more readable.

Named Parameters
Lets look at the following example (from testSimpleTypedQueryWhereInputNamed method):
em.createQuery("SELECT c FROM CompanyEntity AS c WHERE c.name=:nameParam", CompanyEntity.class);
query.setParameter("nameParam", "JBay Solutions");
So, a named parameter is a parameter that is called out by name. It is defined using the :< param_name > notation:
·         They are defined using the ":" prefix
·         They are Case Sensitive
·         $ and _ chars are allowed,
·         ? is not allowed.
·         They can be used more than once on the Query string
·         They must be SET to something on execution
·         There is a list of reserved identifiers that cannot be used. Some of these reserved identifiers are : SELECT , WHERE, GROUP, TRUE, and many more, but you get the gist. These reserved identifiers are case insensitive, and non can be used as a named parameter.
The named parameters are then set on the Query using the setParameter method of the Query instance:

query.setParameter("nameParam", "JBay Solutions");
for (CompanyEntity ce : query.getResultList()) {
    System.out.println(" -> Company : " + ce.getName());
}
query.setParameter("nameParam", "Google");
for (CompanyEntity ce : query.getResultList()) {
    System.out.println(" -> Company : " + ce.getName());
}

What happens if you don't set one of the parameters? You get an IllegalStateException:
[...]
[EL Info]: connection: 2014-10-14 18:30:02.302--ServerSession(822392390)--file:/home/rui/projects/jpa2tut-queries/target/test-classes/_jpa2tut-test login successful

java.lang.IllegalStateException: Query argument nameParam not found in the list of parameters provided during query execution.
    at org.eclipse.persistence.internal.jpa.QueryImpl.processParameters(QueryImpl.java:498)
    [...]

Positional Parameter
Lets look at the following example (from testSimpleTypedQueryWhereInputNumbered method):

em.createQuery("SELECT c FROM CompanyEntity AS c WHERE c.name=?1", CompanyEntity.class);
query.setParameter(1, "JBay Solutions");

A positional parameter is defined using the ?< int > notation:
·         The parameters are numbered starting with 1
·         Positional parameter can appear more than once in the Query string.
·         The order by which they are set is irrelevant
So:
query.setParameter(1, "JBay Solutions");
query.setParameter(2, "Google");
will return the same as:

query.setParameter(2, "Google");
query.setParameter(1, "JBay Solutions");

Using Multiple Entities
Right, making JPQL queries that take into account several Entities is the next logical step. For that, take into account the following SQL script (that is part of the create-and-populate.sql file of the sample project):

INSERT INTO `jpa2tut-queries`.`employee` (`name`, `address`, `idcompany`, `birthday`) VALUES ('Rui Pereira', 'Lisbon',1 , '1981-06-27' );
INSERT INTO `jpa2tut-queries`.`employee` (`name`, `address`, `idcompany`, `birthday`) VALUES ('Gustavo Santo', 'Peniche',1, '1979-12-19');
INSERT INTO `jpa2tut-queries`.`employee` (`name`, `address`, `idcompany`, `birthday`) VALUES ('Maxim Shafirov', 'St.Petersburg, Russia',2, '1970-06-01');
INSERT INTO `jpa2tut-queries`.`employee` (`name`, `address`, `idcompany`, `birthday`) VALUES ('Valentin Kipiatkov', 'St.Petersburg, Russia',2, '1975-06-01');
[... many more entries]

This simply adds a few Employees to the previously created Companies. Now we have JBay Solutions with two employees and JetBrains with also two employees, and the remaining companies with also two employess (check file for the remaining entries).
Lets create a JPQL query that returns the employees of a particular company:

TypedQuery<EmployeeEntity> query =
    em.createQuery("SELECT employee FROM CompanyEntity AS company, EmployeeEntity as employee " +
    "WHERE company.name=:name " +
    "AND employee.company = company", EmployeeEntity.class);

Pretty simple, yes?
·         On the FROM clause we define two Entities with two Identifiers
·         we make use of a named parameter
·         we make use of the AND logical operator
Remember we said that JPQL perform queries on the persistent schema of entities and relationships. EmployeeEntity has a ManyToOne Relationship defined to CompanyEntity, and therefore we can use that relationship in our JPQL queries. The relationship is defined like this:

@ManyToOne
@JoinColumn(name = "idcompany", referencedColumnName = "idcompany")
private CompanyEntity company;

Therefore when we in JPQL do something like employee.company = SOMETHING, we call employee.company a Path Expression, and the result for this case should be pretty obvious, because the relationship maps to only one other Entity. We'll talk a bit more about Path Expressions in a bit.
Lets give a run at testMultipleEntities1 method on the QueryTesting.java, which runs this query like this:

query.setParameter("name", "JBay Solutions");
for (EmployeeEntity client : query.getResultList()) {
    System.out.println(" -> Q1 : " + client.getName());
}
query.setParameter("name", "JetBrains");
for (EmployeeEntity client : query.getResultList()) {
    System.out.println(" -> Q2 : " + client.getName());
}

And check the results:
[EL Info]: connection: 2014-10-14 20:58:59.383--ServerSession(1268465323)--file:/home/rui/projects/jpa2tut-queries/target/test-classes/_jpa2tut-test login successful
 -> Q1 : Rui Pereira
 -> Q1 : Gustavo Santo
 -> Q2 : Maxim Shafirov
 -> Q2 : Valentin Kipiatkov

Perfect!
Now, on the other side of the relationship, we'll have a OneToMany Relationship, like this:

 @OneToMany(mappedBy = "company")
private Collection<EmployeeEntity> employeeCollection;

And using that collection there on a JPQL query is also possible, but not as shown before. When we write something like this AND employee.company = company , the employee.company part as we said before is called a Path Expression, and one of the rules of Path Expressions is that it cannot be created from another Path Expression that evaluates to a collection. Lost you all there? In order words, check this next example.
Take this bit of code FROM EmployeeEntity as employee , we could create these Path Expressions :
·         employee.name : Evaluates to a String
·         employee.id : Evaluates to an int
·         employee.address : Evaluates to a String
·         employee.company : Evaluates to a Company object
·         employee.company.id : Evaluates to an int
·         employee.company.name : Evaluates to a String
·         employee.company.address : Evaluates to a String
·         employee.company.employeeCollection : Evaluates to a Collection
So you see, we created Path Expressions from other path expression in the way that employee.company.id is created from the employee.company Path Expression.
Since we have that rule of not being able to create from Path Expressions that evaluate to Collections, we can't do the following :
·         employee.company.employeeCollection.id : Illegal
·         employee.company.employeeCollection.name : Illegal
·         employee.company.employeeCollection.address : Illegal
·         employee.company.employeeCollection.company : Illegal
But Path Expressions that evaluate to Collections can be very useful in other ways. Lets give it another try at writting a JPQL query that makes use of this to achieve that same goal as the previous one:

 TypedQuery<EmployeeEntity> query =
    em.createQuery("SELECT employee FROM CompanyEntity AS company, EmployeeEntity as employee " +
    "WHERE company.name=:name " +
    "AND employee MEMBER OF company.employeeCollection ", EmployeeEntity.class);

See what we did there?
·         we made use of the MEMBER OF comparison operator
·         and we have a working knowledge of what is a Path Expression
There are a few more rules regarding Path Expressions, but a working knowledge is quite enough. If you feel you must know these right now, please check the JPA 2 Final Spec, which can be found at the bottom of this post in the References section.
About the MEMBER OF comparison operator, there are quite a few comparison operators, like BETWEEN, IN , EMPTY, EXISTS, LIKE, IS NULL and your well known = , >, <, <>, <=, and >= .
The MEMBER OF operator
The MEMBER OF operator is used to match or NOT the existence of something in a Collection. An example if we may :

 employee MEMBER OF company.employeeCollection

The employee section is a path expression that evaluates to an entity. It could evaluate to a simple value (like for example employee.id does) or an object (like employee.company), these are allowed.
The company.employeeCollection section is a path expression that will evaluate to a Collection of Employees. That is it.
To match the inexistence of something inside a Collection, we can use the NOT MEMBER OF, like this:

 employee NOT MEMBER OF company.employeeCollection

The NOT is a constant in the remaing comparison operators, like we will see next.

 The BETWEEN operator
Lets shift our focus now to the Company table and CompanyEntity bean. There is a field there that stores the year the Company was created. It is mapped as an Integer and cannot be Null.
For our next task we will write a JPQL query that will return the Companies that were created in 1995 or after and in 2005 or before:

 em.createQuery("SELECT company FROM CompanyEntity AS company " +
"WHERE company.createdYear >= 1995 " +
"AND company.createdYear <= 2005 ", CompanyEntity.class);

Easy, yes? But we could use the BETWEEN operator in the following way:

 "WHERE company.createdYear BETWEEN 1995 AND 2005 "

Which will return the exact same result.
Expanding on what we already know, we could actually just replace both the MIN and MAX arguments of the BETWEEN operator with named parameters and allow for them to be defined dynamically:

query = em.createQuery("SELECT company FROM CompanyEntity AS company"+
"WHERE company.createdYear BETWEEN :minimum AND :maximum ", CompanyEntity.class);
query.setParameter("minimum", 1995);
query.setParameter("maximum", 2000);
But BETWEEN can be used with other types of parameters, like Dates. Lets write a JPQL query that returns now all the Employees of any company that were born between 1979-01-01 and today:

 TypedQuery query =
    em.createQuery("SELECT employee FROM EmployeeEntity AS employee"+
    "WHERE employee.birthday BETWEEN :minimum AND :maximum ", EmployeeEntity.class);
query.setParameter("minimum", "1979-01-01");
query.setParameter("maximum", new Date());

To get the exclusion of those dates, you use the NOT :

 "WHERE employee.birthday NOT BETWEEN :minimum AND :maximum ", EmployeeEntity.class);

The LIKE operator
We know how to compare Strings, we have the regular = and <> comparison operators. Lets try them out in writing a JPQL query that returns all the companies that are named Google:
SELECT company FROM CompanyEntity AS company WHERE  company.name = 'Google'
But what about if on our database, that company was named "google" and not "Google"? Well, that query would fail, that's what.
And what if we want to return all the Companies that have "oo" (two 'o's) in their name? Well then we have to write a pattern and check which entries match that pattern.
For that we use the LIKE operator.
The LIKE works like this : on the left hand side you place a String or a path expression that evaluates to a String. On the right hand side you place a String that has a pattern to be matched.

So, if we do:

 WHERE  company.name LIKE 'google'

We would get a match for companies named:
·         google
·         Google
·         GOOGLE
·         GoOgLe
In order words: 5 letter words that have the same letters in that order, but are case insensitive.
Lets write a JPQL that matches all Companies with "oo" in their name, no matter where in their name:

 WHERE company.name LIKE '%oo%'

Here we go. Now we will match :
·         Google
·         Yahoo
·         oo.org
·         Boo
The % character is a wildcard character that matches any sequence of characters, including an emply one. The other wildcard charater we have is the underscore _ which matches exactly one character.
So, if we were to write:

 WHERE company.name LIKE '%oo_'

We would only match companies named like this:
·         Gooa
·         Goob
·         oox
·         ooy
·         yahooa
Why? Because the _ wildcard must match exactly 1 character, whatever character it is, but it must be 1!
As with the precious operator, if we want to get all the company names that don't match that pattern,
we use the NOT :

 WHERE  company.name NOT LIKE '%oo%'

And we get pretty much any company whose name doesn't have exactly two 'o's together.

 The EMPTY operator
The EMPTY operator simply evaluates if Collection expressed by a path expression is empty, or NOT EMPTY.
Lets write a JPQL query that returns all the companies that have no employees:

 SELECT company FROM CompanyEntity AS company WHERE company.employeeCollection IS EMPTY

The company.employeeCollection poins to a Collection of Employees. If we run this query against our test database, we get no returns, because all the companies on our DB have employees. So instead lets have a query that returns all the companies that have employees:

 SELECT company FROM CompanyEntity AS company WHERE company.employeeCollection IS NOT EMPTY

And now if we run that query we get returned all the Companies in our test DB.

 The NULL operator
Not that any of the operators are difficult to understand, but NULL is by far the easiest one. IS NULL is used to test if a give path expression or parameter is a NULL value , or NOT.
For completeness' sake lets write a JPQL query that returns all Employees that have their address NULL:

 SELECT employee FROM EmployeeEntity AS employee WHERE  employee.address IS NULL
And now, a query that returns all the Employees that don't have their address NULL:

 SELECT employee FROM EmployeeEntity AS employee WHERE  employee.address IS NOT NULL

In case you are wondering, a path expression that evaluates to a Collection cannot be NULL. It can however be EMPTY or NOT EMPTY. So, if you try something like this:

SELECT company FROM CompanyEntity AS company WHERE  company.employeeCollection IS NULL

                                                          Thanks for visiting the Blog.


Amarpreet Singh                                                                                              amarpreet@37gmail.com


Comments

Post a Comment