Using JPQL in Your Java Applications
- Selecting Instances from a Single Entity
- Binding Parameters to a JPQL Query
- Integrating JPQL into Your Application
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
If you've been following my last few articles, you'll notice they provide a starting point for using Java's later technologies to improve rapid application building and overall functionality. JSF (Java Server Faces) is Java's latest for building applications using the Model View and Controller architecture. It provides a much richer framework, especially for users in the Presentation layer of your application. Using JSF with managed beans and JPA for persisting data has been the way to go recently. However, there is still one more level to go in order to get Java's top-notch development technologies for multiple devices. You may have heard of it[md]JavaFX[md]and in later articles, I would like to show you how to use JavaFX with web services using JPA.
For now though, it is important to become familiar with the Java Persistence Query Language (JPQL). You can use JPQL across most architectures with JPA, and that makes it highly beneficial to learn, and gives you flexibility across applications that use and do not use web services. So, let's start with the basics and move on from there.
The main benefit of JPQL is that it is object-oriented. You form queries in OO syntax against your Entities, not your application's database. JPQL is independent of the database because you're manipulating data through your Entities, which are mapped to the application's persistent layer using a persistence context (persistence.xml). If you do not understand these concepts, please review my last couple articles (namely, “Mapping Objects to Database Tables with the Java Persistence API” and “Learn How to Use JPA in Your Java Applications by Using Entity Manager”) before continuing.
Selecting Instances from a Single Entity
Because JPQL is based on SQL, you still have the most common statements for interacting with the database, such as SELECT, DISTINCT, DELETE, FROM, IN, UPDATE, WHERE, ORDER BY, GROUP BY and more.
For example, say we had an entity named User, and wanted to return all User information from that entity. The JPQL syntax for doing this follows:
Select u FROM User u
In the above statement, the FROM clause is used to assign an alias to the Entity. The alias u is mapped to the User entity. With traditional SQL, you always had to specify the table name before the alias, but is not necessary with JPQK. The query will return zero or more User instances.
To filter results or find a specific record, use the WHERE clause, just like with an SQL statement:
Select u FROM User u WHERE u.login = 'tomj'
The above statement will return the user record having the 'tomj' as the login id. If you want return a selected column (or better referred to as attribute) in the Entity, then use the following statement to return all record data for the first name attribute:
Select u.firstName FROM User u
To get more advanced, if you've ever worked with Microsoft's Transact SQL (TSQL) or Oracle's PSQL, then you'll see how JPQL makes use of similar conditional operators with the following statement:
SELECT CASE u.password WHEN 'alligator1'
THEN u.access = 'Administrator'
ELSE u.access = 'User'
END
FROM User u
The statement above makes the user logging in with the password 'alligator1' an Administrator, while everyone else is given the User role.
To access information across entities that are bound together (associated in JPA terms), we only have to look at the parent Entity. For example, let's say we had an entity called User (the parent) and an entity called Address (the child), then the following statement will return the user's country code:
SELECT u.address.countrycode FROM User u
To remove duplicate entries from an entity, you can use the DISTINCT keyword:
SELECT DISTINCT u.firstName FROM User u
The above statement will return records having no duplicate first names.
You can also use date/time operators:
SELECT u FROM User u WHERE u.datecreated = CURRENT_DATE
Use the IN statement to get users from only certain countries:
SELECT FROM User u WHERE u.address.country IN ('USA','Norway')
Subqueries use much the same SQL syntax as traditional SQL:
SELECT u FROM User u WHERE u.age = (SELECT MIN(user.age) FROM User user)
The above query returns the youngest user.