JPA : SQL Queries

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).


Input Parameters

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);

Range of Results

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.


Query Execution

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();

SQL Result Definition

By default, if you simply execute a native query and don't specify the result mapping, then when you execute getResultList() each row of the results will be an Object array. You can however define how the results are mapped to some result class for example. Let's give some examples of what you can do. If we have the following entities

@Entity
@Table(name="LOGIN")
public class Login
{
    @Id
    private long id;

    private String userName;
    private String password;

    public Login(String user, String pwd)
    {
        ...
    }
}

@Entity
@Table(name="LOGINACCOUNT")
public class LoginAccount
{
    @Id
    private long id;

    private String firstName;
    private String lastName;

    @OneToOne(cascade={CascadeType.MERGE, CascadeType.PERSIST}, orphanRemoval=true)
    @JoinColumn(name="LOGIN_ID")
    private Login login;

    public LoginAccount(long id, String firstName, String lastName)
    {
        ...
    }
}

The first thing to do is to select both LOGIN and LOGINACCOUNT columns in a single call, and return instances of the 2 entities. So we define the following in the LoginAccount class

@SqlResultSetMappings({
    @SqlResultSetMapping(name="LOGIN_PLUS_ACCOUNT", 
        entities={@EntityResult(entityClass=LoginAccount.class), @EntityResult(entityClass=Login.class)})

and we now execute the native query as

List<Object[]> result = em.createNativeQuery("SELECT P.ID, P.FIRSTNAME, P.LASTNAME, P.LOGIN_ID, L.ID, L.USERNAME, L.PASSWORD " +
    "FROM JPA_AN_LOGINACCOUNT P, JPA_AN_LOGIN L", "AN_LOGIN_PLUS_ACCOUNT").getResultList();
Iterator iter = result.iterator();
while (iter.hasNext())
{
    Object[] row = iter.next();
    LoginAccount acct = (LoginAccount)obj[0];
    Login login = (Login)obj[1];
    ...
}

Next thing to try is the same as above, returning 2 entities for a row, but here we explicitly define the mapping of SQL column to the constructor parameter.

@SqlResultSetMapping(name="AN_LOGIN_PLUS_ACCOUNT_ALIAS", entities={
            @EntityResult(entityClass=LoginAccount.class, fields={@FieldResult(name="id", column="THISID"), @FieldResult(name="firstName", column="FN")}),
            @EntityResult(entityClass=Login.class, fields={@FieldResult(name="id", column="IDLOGIN"), @FieldResult(name="userName", column="UN")})
        })

and we now execute the native query as

List<Object[]> result = em.createNativeQuery("SELECT P.ID AS THISID, P.FIRSTNAME AS FN, P.LASTNAME, P.LOGIN_ID, " +
    "L.ID AS IDLOGIN, L.USERNAME AS UN, L.PASSWORD FROM JPA_AN_LOGINACCOUNT P, JPA_AN_LOGIN L", "AN_LOGIN_PLUS_ACCOUNT_ALIAS").getResultList();
Iterator iter = result.iterator();
while (iter.hasNext())
{
    Object[] row = iter.next();
    LoginAccount acct = (LoginAccount)obj[0];
    Login login = (Login)obj[1];
    ...
}

For our final example we will return each row as a non-entity class, defining how the columns map to the constructor for the result class.

@SqlResultSetMapping(name="AN_LOGIN_PLUS_ACCOUNT_CONSTRUCTOR", classes={
           @ConstructorResult(targetClass=LoginAccountComplete.class,
               columns={@ColumnResult(name="FN"), @ColumnResult(name="LN"), @ColumnResult(name="USER"), @ColumnResult(name="PWD")}),
        })

with non-entity result class defined as

public class LoginAccountComplete
{
    String firstName;
    String lastName;
    String userName;
    String password;

    public LoginAccountComplete(String firstName, String lastName, String userName, String password)
    {
        ...
    }
    ...
}

and we execute the query like this

List result = em.createNativeQuery("SELECT P.FIRSTNAME AS FN, P.LASTNAME AS LN, L.USERNAME AS USER, L.PASSWORD AS PWD FROM " +
    "JPA_AN_LOGINACCOUNT P, JPA_AN_LOGIN L","AN_LOGIN_PLUS_ACCOUNT_CONSTRUCTOR").getResultList();
Iterator iter = result.iterator();
while (iter.hasNext())
{
    LoginAccountComplete acctCmp = (LoginAccountComplete)iter.next();
    ...
}

Named Native Query

With the JPA API you can either define a query at runtime, or define it in the MetaData/annotations for a class and refer to it at runtime using a symbolic name. This second option means that the method of invoking the query at runtime is much simplified. To demonstrate the process, lets say we have a class called Product (something to sell in a store). We define the JPA Meta-Data for the class in the normal way, but we also have some query that we know we will require, so we define the following in the Meta-Data.

    <entity class="Product">
        ...
        <named-native-query name="PriceBelowValue"><![CDATA[
        SELECT NAME FROM PRODUCT WHERE PRICE < ?1
        ]]></named-native-query>
    </entity>

or using annotations

@Entity
@NamedNativeQuery(name="PriceBelowValue", query="SELECT NAME FROM PRODUCT WHERE PRICE < ?1")
public class Product {...}

So here we have an SQL query that will return the names of all Products that have a price less than a specified value. This leaves us the flexibility to specify the value at runtime. So here we run our named query, asking for the names of all Products with price below 20 euros.

Query query = em.createNamedQuery("PriceBelowValue"); 
query.setParameter(1, new Double(20.0));
List results = query.getResultList();