If you are following the tutorials here you must be dying to do some queries using JPA. So, before we go any further into other aspects of JPA, lets query the model! For this task, instead of using SQL we will be using the Java Persistence Query Language or also known as JPQL. Exciting, yes?
Sample Project
About the sample project provided: it is a Maven project, we do not require an App Server at this point and the database used is a MySQL database. Check the JPA2 Tutorials page for links of recommended software.
You can get the project at GitHub.
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);Moving on.
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:
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 :
- 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
Well... that just isn't going to work.
But there is more!
What about Subqueries? Any more comparision operators? And Update and Delete statements? What about the Criteria API?
We'll take a break here, because this post is getting big and we'll continue on the next post! We'll update this one with a link as soon as the next post comes out.