Getting started with the EJB 3.0 Query Language (EJBQL 3.0) using GlassFish reference implementation

This article is the sequel to EJB 3.0 Persistence – Introducing EJBQL 3.0, an introduction to EJB QL
from an historical, strategic and functional perspective. In this post
I want to show you the goods: what does the EJB QL code look like with
EJB 3.0 Persistence for doing Native Queries, for aggregations and
subqueries, for projections and inheritance. We will take a quick look
around with the main features in EJB QL 3.0 and the way to use them in
code. Note that all code is J2SE – so out of container – and makes use
of the GlassFish Reference Implementation of EJB 3.0 Persistence. It
should be effortless – only a small change in the persistence.xml file
– to switch the code to the Hibernate or TopLink implementation of EJB
3.0.

I will use JDeveloper 10.1.3 EA as my IDE – that is not very
relevant by the way, since all code and the libraries in my Classpath
are portable and standard – nothing is Oracle or JDeveloper specific. I
only mention it because it gives you some background for the
screenshots. It also means that you can import the entire application
workspace that you can download under Resources into a JDeveloper
installation. If you do not use an IDE or at least another IDE, you can
of course use the source files but you have to create your own project
in your own IDE.

The case we will work with is the AMIS Library
System or ALS. It consists of only five tables – Books, Authors,
Publishers, Copies (of Books) and the intersection table Authorships.
We have been looking at (a subset of) this same system in a previous
article: EJB 3.0 Persistence – ManyToMany relations or the objectification of the intersection table.

Steps to get started

These
are the steps I took to get started. They are most certainly not
required to work with EJB QL 3.0; I only mention these steps for those
of you that like to exactly retrace my steps – that may include myself
later on…

1. Install the ALS database
schema (see the ejb30als.zip file under Resources). Under JDeveloper’s
Database Diagramming tools, the database schema looks like

2. Fire up JDeveloper; create a new Application Workspace and a new project.

3. Set the Project properties as described in the post Using GlassFish Reference Implementation of EJB 3.0 Persistence with JDeveloper 10.1.3EA.
This boils down to including the GlassFish libraries and setting the
correct Run/Debug settings – the right JVM and the correct Java Runtime
options.

4. Run the New Gallery, Business Tier, EJB node, option
CMP Entity Beans from Tables. This wizard will create Entities – Domain
objects or simply POJOs – that map to our tables. The classes will
include the ‘default’  mapping annotations. We may revisit and refine
them, but for the purpose of analyzing EJB QL, the default mapping is
probably good enough.

The EJB Diagram looks like this:

Note
that a number of relationships that are available in the code, properly
annotated and all, do not show up on the diagram. I presume that this
Early Access release has some hickups in the EJB diagrammer that will
be straightened out in the final production release.

I have
taken the liberty to refining a number of annotations – mainly to do
with the relationships – where the code generated by the Wizard did not
fly successfully with the compiler.

5. Create a new
persistence.xml file in the project_home\src\META-INF directory – again
as described in the post mentioned above. Note: you can download the
entire setup in a single zip-file under Resources.

6.
To see whether the setup so far is okay, I create a very simple test
LibraryClient class that will access an EntityManager and attempts to
perform some basic operations on these Entitities. Note that I have not
mapped the ALS_AUTHORSHIPS table as pure Intersection table – mapped as
a @ManyToMany relation – since I have assigned a characteristic to this
table: column contributorType that indicates the role an Author had
with regard to a specific book.

package nl.amis.ejb30.als;

import java.util.Collection;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.Query;

public class LibraryClient {
    public LibraryClient() {

        EntityManagerFactory emf =
            Persistence.createEntityManagerFactory("pu1");
        // create EntityManager
        EntityManager em = emf.createEntityManager();
        this.setEntityManager(em);
    }

    private EntityManager _entityManager;

    public EntityManager getEntityManager() {
        return _entityManager;
    }

    public void setEntityManager(EntityManager entityManager) {
        _entityManager = entityManager;
    }

    public List<Book> findAllBook()  {
        return getEntityManager().createQuery("select object(o) from Book o").getResultList();
    }

    public static void main(String[] args) {
       LibraryClient libraryClient = new LibraryClient();

        Collection<Book> books = libraryClient.findAllBook();
        for (Book b : books) { 
          System.out.println(b.getTitle() + " has "+b.getAuthorshipCollection().size()+" author(s)");
        }        
 
        Book jsf = libraryClient.getEntityManager().find(Book.class, new Long(22));
        EntityTransaction tx = libraryClient.getEntityManager().getTransaction();
        tx.begin();
        jsf.setTitle(jsf.getTitle()+"!"); // change the title
        libraryClient.getEntityManager().merge(jsf); // persist the change - merge will eventually result in an update
        libraryClient.getEntityManager().flush(); // we enforce that the update is done at this point
        tx.commit(); // complete the transaction; the EntityManager can now decide to do the update any time it fancies
        jsf = libraryClient.getEntityManager().find(Book.class, new Long(22)); // retrieve the updated JSF entity
        System.out.println("New title for JSF: "+jsf.getTitle()+" - it is published by "+jsf.getPublisher().getName());
    }

}

It
is really not very useful: querying all books and updating a single
book’s title. But at least the output demonstrates it works. So the
basic mapping is available to get started.

[TopLink Info]: 2006.01.08 10:35:54.102--ServerSession(3779465)--TopLink, version: Oracle TopLink Essentials - 10g release 4 (10.1.4.0.0) (Build 051215Dev)
[TopLink Info]: 2006.01.08 10:35:56.115--ServerSession(3779465)--file:/C:/glassfish/ejb30_se/ejb30QL/classes-pu1 login successful
JavaServer Faces Programming!! has 1 author(s)
Optimizing Oracle Performance has 2 author(s)
The Data Warehouse Toolkit has 2 author(s)
Java Tools for eXtreme Programming has 2 author(s)
SVG Essentials has 1 author(s)
J2EE Design and Development has 1 author(s)
CSS, Pocket Reference has 1 author(s)
JUnit in Action has 2 author(s)
Building Oracle XML Applications has 1 author(s)
Core Java 2, Volume I: Fundamentals has 2 author(s)
XSLT Cookbook, Solutions and Examples for XML and XSLT Developers has 1 author(s)
Core J2EE Patterns: Best Practices and Design Strategies has 3 author(s)
New title for JSF: JavaServer Faces Programming! - it is published by McGrawHill

Exploring EJB QL in EJB 3.0

Now for some real stuff.

A
query is performed using the Query interface. A Query instance can be
acquired from the EntityManager. We have seen the first examples of the
query interface in the first incarnation of our LibraryService:

    public List<Book> findAllBooks() {
        return getEntityManager().createQuery("select object(o) from Book o").getResultList();
    }

While
this query does nothing spectacular, it is interesting all the same: we
specify the query in terms of Entities. We do not refer to any table-
or column names. The results of the query are objects, Book entities in
this case. So SQL free – and certainly database specific SQL
implementation free – query writing.

Of course the EJB QL is
much richer than what this example can show. Let’s spice things up just
a little with a simple query to find all Books with Java in the title:

    public List<Book> findAllJavaBooks() {
        return getEntityManager().createQuery("select object(o) from Book o where lower(o.title) like '%java%'") .getResultList();
    }

And now for Publishers who publish a book with Java in the title:

SELECT object(o) from Publisher o, IN (o.bookCollection) AS allBooks WHERE lower(allBooks.title) like '%java%'

Look for all Publishers without any Books in their Portfolio:

SELECT object(o) from Publisher o where o.bookCollection IS EMPTY

Find the books that have an author whose firstname is Steve:

SELECT b from Book AS b, in (b.authors) AS author where author.firstName ='Steve'

Find all authors who have worked on a book that was published by a Publisher with a website whose url contains the string man

public List<Author> findManAuthors() {
        return getEntityManager().createQuery("select object(o) from Author AS o JOIN o.books As b join b.publisher p where lower(p.website) like '%man%' ").getResultList();
    }

Let’s
do even more object navigation stuff in our query. Let’s try to find a
Publisher who publishes a book that has been co-written by an author
who also participated on a book that has Oracle in the title:

SELECT object(p) FROM Publisher AS p
, in (p.bookCollection) AS books
, in (books.authors) authors 
, in (authors.books) books2 
WHERE lower(books2.title) like '%oracle%'

Until
now, each query has returned only single-flavored entities: all results
are either Books or Publishers or Authors. However, EJB QL 3.0 can also
return multiple entity types in a single query. So for example, in the
last query, we can show not only the Publisher but also the Author in
question.

 public List findSpecialPublishers() {
    return getEntityManager().createQuery("SELECT object(p), object(authors)  from Publisher AS p, in (p.bookCollection) AS books, in (books.authors) authors , in (authors.books) books2"+
     " where lower(books2.title) like '%oracle%'  ").getResultList();
}

In the LibraryClient we can deal with the result of this query as follows:

        System.out.println("List all Publishers that have published a book for an author who has written a book with oracle in its name:");
        Collection publishers = library.findSpecialPublishers();
        for (Iterator iterator = publishers.iterator(); iterator.hasNext(); ){
           Object[] resultElement = (Object[])iterator.next();
           Publisher publisher = (Publisher)resultElement[0];
           System.out.println(publisher.getName());
           Author author = (Author)resultElement[1];
           System.out.println(author.getLastName());
        }

EJB
QL queries not only return Entities. They can also return singular,
scalar values. They can even return objects that are constructed as
part of the query. Something like:

 public Object findBookSummary() {
        return getEntityManager().createQuery("SELECT max(b.publishYear), min(b.publishYear), count(b) from Book b").getSingleResult();
    }

The results can be used in the client like this:

Object[] summary = (Object[])library.findBookSummary();
System.out.println("Most recent book is from "+summary[0]);
System.out.println("Oldest book is from "+summary[1]);
System.out.println("There are "+summary[2]+" books altogether.");

So
far, all queries have been fixed: they have no variable parts. In
reality, queries often depend on the situation. The search criteria
entered by the user for example. This situational context is fed into
the query using Named Parameters. Let’s make the query for all Java
books a little more generic by allowing the subject we are looking for
to be passed in as a parameter:

    public List<Book> findAllBooksOn( String subject) {
        Query query = getEntityManager().createQuery("select object(o) from Book o where lower(o.title) like :subject") ;
        query.setParameter("subject", '%'+subject+'%');
        return query.getResultList();
    }

The client can make use of this method like this:

        Collection<Book> books = library.findAllBooksOn("xml");
        for (Book book : books) { 
          System.out.println(book.getTitle()+ " published by "+book.getPublisher().getName());
          for (Author author: book.getAuthors()) {
              System.out.println(" * "+ author.getFirstName()+ " " + author.getLastName());
          }
        }

Queries
can be defined dynamically, as we have just seen, by passing a string
that contains an EJB QL statement. However, queries can also be
pre-defined using annotations. We can build up a library of queries by
specifying these queries in the Entity.

For example, a NamedQuery to find all books on Java can be specified as follows in the Entity Book:

import javax.persistence.NamedQuery;

@Entity
@Table(name="ALS_BOOKS")

@NamedQuery(name="javaBooks", 
 queryString="select object(o) from Book o where lower(o.title) like '%java%'")
public class Book {
…

To make use of a named query, we can do something like this:

    public List<Book> findJavaBooks() {
        return getEntityManager().createNamedQuery("javaBooks").getResultList();
    }

Our
last Query related subject in this section is the Native Query. When
all else fails, when EJB QL does not give us what we want and need, we
can resort to the Native Query. This feature allows us to write plain
SQL that is passed straight through to the underlying database. It
breaks database portability – as we do not use the EJB QL that is
translated into database specific SQL. But the whole point is that we
want to leverage what our specific database can do – and EJB QL cannot
do for us. For Oracle databases, you think of features like Analytical
Functions, Scalar Subqueries, Table Functions, Connect By operations,
User Defined Aggregations etc.

This next example leverages the Regular Expression feature in the Oracle 10g database:

     public List<Book> findAllBooksOn( String subject) {
        Query q = getEntityManager().createNativeQuery("select * from als_books where regexp_like(title,'"+subject+"{1}','ci')", Book.class);
        return q.getResultList();
    }

As an example, let us query the most recent books for each Publisher

public List<Book> findRecentBooks() {
      return getEntityManager().createNativeQuery(
           "select * from 
                     ( select book.title, book.id, book.publish_year, 
                       book.pbr_id
                       ,      row_number() over ( partition by pbr_id 
                                                  order by publish_year 
                                                  ,        publish_month desc
                                                ) rn 
                       from ALS_BOOKS book
                      ) 
            where rn =1  ", nl.amis.ejb30.als.Book.class).getResultList();
    }

The Client could use this service like this:

Collection<Book> books = library.findRecentBooks();
        for (Book book : books) { 
          System.out.println(book.getTitle()+ "published in "+book.getPublishYear());
          for (Author author: book.getAuthors()) {
              System.out.println(" * "+ author.getFirstName()+ " " + author.getLastName());
          }
        }

NativeQueries
can also be specified using annotations. Furthermore, we can define
special SqlResultSetMappings that instruct the EntityManager on mapping
the result of a Native Queries onto Entities and other objects. This is
used when a native query returns more than a single Entity in each row.

Note:
I was under the impression that NativeQueries can take named
parameters. However, when I tried to do so, I failed quite miserably.

Resources

Download the DDL scripts to create the ALS tables and demo data: ejb30als.zip.

Hibernate Annotations, Reference Guide, 3.1 beta 4
– a very useful guide, even though still beta and with several TODOs
decorating the text; it provides many examples of using the EJB QL 3.0,
as well as the O/R Mapping annotations used by the EntityManager for
persisting and retrieving entities. This document was the best in
showing me the way around EJB QL.

class SQLResultSetMappingTestSuite
– a test-class from the GlassFish project. This class contains useful
code examples of using EJB QL 3.0 – for doing SQLResultSetMapping for
Native Queries as well as using locking, bind parameters (named and
positional), inheritance  with Native Queries.

Getting Started with EJB 3.0 Persistence out-of-container using the Reference Implementation (GlassFish) – introduction to getting EJB 3.0 Persistence up and running with the GlassFish Reference Implementation. Also: Using GlassFish Reference Implementation of EJB 3.0 Persistence with JDeveloper 10.1.3EA – a description of doing EJB 3.0 Persistence with JDeveloper 10.1.3 EA.