| 
The JPA specification defines its interpretation of SQL, for selecting objects from the datastore. To provide a simple example,
this is what you would do
Query q = em.createNativeQuery("SELECT p.id, o.firstName, o.lastName FROM Person p, Job j " +
"WHERE (p.job = j.id) AND j.name = 'Cleaner'");
List results = (List)q.getResultsList();
This finds all "Person" objects that do the job of "Cleaner". The syntax chosen has to be runnable on the RDBMS
that you are using (and since SQL is anything but "standard" you will likely have to change your query when moving to
another datastore).
In JPQL queries it is convenient to pass in parameters so we dont have to define the same query for
different values. Here's an example
Numbered Parameters :
Query q = em.createQuery("SELECT p FROM Person p WHERE p.lastName = ?1 AND p.firstName = ?2");
q.setParameter(1, theSurname);
q.setParameter(2, theForename);
So we have parameters that are prefixed by
?
(question mark) and are numbered starting at 1.
We then use the numbered position when calling
Query.setParameter()
. With SQL queries we can't use named
parameters. This is known as
numbered
parameters.
DataNucleus also supports use of
named
parameters where you assign names just like in JPQL.
This is not defined by the JPA specification so dont expect other JPA implementations to support it.
Let's take the previous example and rewrite it using
named
parameters, like this
Named Parameters :
Query q = em.createQuery("SELECT p FROM Person p WHERE p.lastName = :firstParam AND p.firstName = :otherParam");
q.setParameter("firstParam", theSurname);
q.setParameter("otherParam", theForename);
With SQL you can select the range of results to be returned. For example if you have a web page and you are paginating
the results of some search, you may want to get the results from a query in blocks of 20 say, with results
0 to 19 on the first page, then 20 to 39, etc. You can facilitate this as follows
Query q = em.createNativeQuery("SELECT p FROM Person p WHERE p.age > 20");
q.setFirstResult(0);
q.setMaxResults(20);
So with this query we get results 0 to 19 inclusive.
There are two ways to execute a SQL query. When you know it will return 0 or 1 results you call
Object result = query.getSingleResult();
If however you know that the query will return multiple results, or you just don't know then you would call
List results = query.getResultList(); |
|
