Simple Java Persistence API (JPA) Demo: JPA Query

JPA Query Language

I don’t know if this series still qualifies as simple but I thought I add some basic information about queries in JPA. In part II we looked at the EntityManager and more specifically the simple operations that it enables like persist, find, merge, remove and refresh. In addition to these operations, JPA comes with its own query language that allows you to create custom queries over your data set.

JPA abstracts the developer and the application away from the details of how data is represented in the data stores (more likely a rational database) and this abstraction effectively marries the relational and OO paradigms. However one of the corner stones of the relational paradigm is its query capabilities which has so far been unmatched by any software paradigm to date. The query facilities in the OO model are limited in as far as handling a large amount of data. While there are attempts at developing ORDBMS (Object Relational Database Management Systems) data stores, these have never truly caught on in the enterprise and so the bulk of enterprise data remain stored in relational databases. With every other application build on top of a relational database, it becomes important to build query capabilities into abstractions layers such as the JPA.

The default query language in relational paradigm is the Structured Query Language or simply SQL. SQL has a number of standards defined which every vendor of a relational database implements in slightly different manner thus making it a tricky language to adopt as the basis of an abstraction layer like the JPA that is expected to work across multiple relational database products without resulting to expensive and complex workarounds.

The Java Persistence API Query Language (JPA QL) is the result of attempts to abstract the query facilities of a relational paradigm. It borrows from the EJB QL but also fix the weaknesses that have plagued EJB QL. The specifics of what was borrowed from EJB QL and what was fixed are beyond the scope of this post. JPA provides the ability to retrieve JPA mapped entities, sorting them as well as filtering them. If you are familiar with SQL, then you have some degree of familiarity with JPA QL as it is syntax is closely modeled on SQL’s syntax.

Specifying a Query

There are three main ways of specifying JPA queries:

  • createQuery Method of the EntityManager: with this option you compose the query at run time and execute it there and then. The most immediate aspect of this approach to creating queries is that your queries are not checked/parced at deployment time which means that obvious errors are only discovered when the code is executed.
  • Named Queries: Named queries are defined along with the corresponding entity beans. Several named queries can be defined for each entity thus enabling filtering and sorting using various properties of the entity. Unlike with runtime queries, these queries are parsed at deployment time which means that any errors are discovered before code is executed that depends on your named queries.
  • Native Queries: this gives you the ability to define queries using SQL instead of EJB QL. You can create Native Named Queries as well.

Querying

Retrieving data

The most common query operation is the select operation which returns all or a subset of records in the database. With JPA QL, the select operation returns mapped collection of zero or more mapped entities. The operation can also return properties of a mapped entity. A simple select query looks as follows.

SELECT h FROM Hotel h

 

SELECT h.name FROM Hotel h

Notice how you select from the entity and not from a table as you would in SQL but the syntax of the query is not different from what you would write using SQL. The query returns zero or many Hotel entities from the database. The Hotel entity was defined in the first installment of this demo series. The second query in the above sample selects a property of the hotel entity.

Lazy vs Eager Loading: FETCH JOIN

When you design your entity classes with associations and relationships, loading and accessing these relationships at run time becomes important. For example, a hotel has rooms and you can decide if you want the rooms associated with each hotel to be loaded when the hotel entity is retrieved (eager loading) or when you explicitly access (lazy loading) the associated rooms. During the definition of the association between entities you can declare whether you can lazy or eager loading but JQL also allows you to load the objects in an association.

SELECT h FROM Hotel h JOIN FETCH h.rooms

With the above query, all the hotel objects returned will have their associated rooms loaded as well. This gives you eager loading without specifying it in the relationship between the Hotel and Room entities.

Filtering & Sorting

It is not always the aim of any data retrieval operation to return every last record in a database; some times we are interested in only a few of those records that meet a particular criteria for the purpose of our operations at hand. Within the context of the simple app setup for this series, we may just be interested in hotels that are in a particular town. The name of the town in question would form our filtering criteria. The sample below gives a JPA QL query that would enable us to retrieve a collection of Hotel entities that with a particular town property.

//Filtering

SELECT h FROM Hotel h WHERE h.name = “Nairobi”

//Sorting

SELECT h FROM Hotel h ORDER BY h.name

//Filter and Sort

SELECT h FROM Hotel h WHERE h.name = “Nairobi” ORDER BY h.name

Once again notice the similarity to an SQL statement that would return rows that meet the provided sort and filtering parameters. So far these are just simple queries that don’t show much of JPA QL capabilities but a necessary step in appreciating how JPA QL queries are written.

Of greater importance is showing how these queries can possibly be composed within the context of Java code.

Query q = em.createQuery(“SELECT h FORM Hotel h ORDER BY h.name”);

List<Hotel> results = q.getResultList();

A further example of using queries to filter

Query q = em.createQuery(“SELECT h FROM Hotel h WHERE h.name = :hotelName”);

q.setParameter(“hotelName”, hotelName);

List<Hotel> results = q.getResultList();

Something that may be a bit tricky for first time users of JPA is composing queries using the LIKE operator to filter

Query q = em.createQuery(“SELECT h FROM Hotel WHERE h.name LIKE :name”);

StringBuilder sb = new StringBuilder();

sb.append(“%”);

sb.append(name);

sb.append(“%”);

q.setParameter(“name”, sb.toString());

List<Hotel> results = em.getResultList();

Assume for a moment that you want a list of all hotels with a particular number of rooms (say more than 20 rooms for example) … here is how you go about formulating such a query:

Query q = em.createQuery(“SELECT h FROM Hotel h WHERE size(h.rooms) > 20 ORDER BY h.name”);

List<Hotel> results = q.getResultList();

This concludes this look at JPA QL. This is not a complete examination of the power of JPA QL but a glimpse at what is possible.

Advertisements

  1. browse around these guys
  2. southern ontario
  3. certified financial planning course
  4. this post
  5. i need to make more money
  6. sktmapo1.dothome.co.kr
  7. klara-klok.no
  8. Look At This
  9. blackberry bold white
  10. my website

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: