This article aims at providing a walk through on building Data Access Object (DAO) [1] components using Java Persistence API (JPA 1.0) [2], since the latter is now fully supported in the 1.1 release of Demoiselle Framework [3].
The reference source codes used in the following sections were retrieved from Auction5 [4], a sample web application specifically designed to exemplify JPA usage along with Demoiselle.
i. Introducing support elements
The main characters on the new Demoiselle persistence handling mechanism are IDAO [5] and JPAGenericDAO [6].
IDAO is a simple interface containing data manipulation and retrieval methods. It is, above all, a markup interface intended to be implement by whoever class playing a DAO role. Therefore, no matter whether the final class will handle objects from a DBMS or an XML file, using pure JDBC, Hibernate or JPA, it must necessarily implement IDAO.
On the other hand, JPAGenericDAO is an abstract class intented to leverage productivity by providing the derived class with a bunch of persistence-related ready-to-use methods such as: findById(), findByJPQL(), insert(), update(), and remove().
ii. Defining entities
Entity classes are usually the first elements to be created on a Java application powered by JPA persistence.
In the class diagram below the whole application entities considered in the examples are depicted.
iii. Designing interfaces
In order to build DAO artifacts on Demoiselle, one needs to previously create their respective interfaces. Besides, these interfaces must extend IDAO by providing the targeted entity class in the generics brackets (e.g. <Auction>).
The signatures of additional needed methods must be indicated on each interface, remembering that all methods provided by IDAO are already implicitly included.
For instance, for the Auction entity, there must be created IAuctionDAO interface by extending IDAO and declaring additional listing methods. Its final code is shown below:
public interface IAuctionDAO extends IDAO<Auction> {
public Auction findById(Long id);
public List<Auction> listOpenAuctionsByCategory(Category category);
public List<Auction> listNewestAuctions(int quantity);
public List<Auction> listMostOfferedAuctions(int quantity);
public List<Auction> listEndingSoonAuctions(int seconds, int quantity);
public List<Auction> listCheapestPriceAuctions(int quantity);
public List<Auction> listOpenAuctionsByItem(Item item);
public List<Auction> listOpenEndedAuctions(Date timestamp);
}
Likewise, interfaces for Bid, Category, Item, and Order entities suggest the structures below:
public interface IBidDAO extends IDAO<Bid> {
public List<Bid> listLastBidsForAuction(Auction auction, int quantity);
}
public interface ICategoryDAO extends IDAO<Category> {
public Category findById(Short id);
public List<Category> listAvailableCategories();
public List<Category> listAllCategories();
}
public interface IItemDAO extends IDAO<Item> {
public Item findById(Integer id);
public List<Item> listByCategory(Category category);
}
public interface IOrderDAO extends IDAO<Order> {
public Order findById(Long id);
public List<Order> listOrdersByLogin(String login);
}
On an application powered by Demoiselle all those interfaces are usually placed on a subpackage named "persistence.dao" inside the main package tree (e.g. "br.gov.demoiselle.sample.auction5.persistence.dao").
iv. Designing implementation classes
After creating the DAO interfaces, the next step is to build their respective implementation classes. For each interface, there must be created a concrete class declared with a reference to that interface.
In addition, in order to boost development time and yet help the developer with several coding facilities, these new classes should extend JPAGenericDAO superclass described previously.
Thus, DAO implementation classes should contain the declarations below:
public class AuctionDAO extends JPAGenericDAO<Auction> implements IAuctionDAO { ... }
public class BidDAO extends JPAGenericDAO<Bid> implements IBidDAO { ... }
public class CategoryDAO extends JPAGenericDAO<Category> implements ICategoryDAO { ... }
public class ItemDAO extends JPAGenericDAO<Item> implements IItemDAO { ... }
public class OrderDAO extends JPAGenericDAO<Order> implements IOrderDAO { ... }
As a result of "implements", every method declared in an interface must be implemented and fulfilled with actual code by the developer in the class. However, there are exceptions: methods belonging to IDAO are already implemented in JPAGenericDAO, and therefore do not need to be reimplemented.
Demoiselle Framework suggests hosting these classes onto a subpackage named "implementation" right inside the subpackage containing the interfaces (e.g. "br.gov.demoiselle.sample.auction5.persistence.dao.implementation").
v. Implementing classes methods
In the next sections there will be explored several ways of implementing the methods declared in the interfaces, by using existing code, by issuing JPQL queries and even through SQL native queries.
1. Superclass methods
The first alternative on building persistence methods in the DAO is the simplest one: to directly invoke existing methods of JPAGenericDAO superclass.
For example, a listAllCategories() method designed to bring all categories could be coded in CategoryDAO using the following contents:
public List<Category> listAllCategories() {
return findAll();
}
If the method name is to be kept, one should use the "super" reference variable to prevent infinite loops, as illustrated below in findById() method for CategoryDAO:
public Category findById(Short id) {
return super.findById(id);
}
2. JPQL fixed string
As an evolution to EJB QL, JPA 1.0 introduces a query language called JPQL (Java Persistence Query Language). This language's synthax is similar to Hibernate's HQL and offers the same purpose: to send instructions to the DBMS by using a language closer to object-oriented world.
It is made available to the JPAGenericDAO class a method called findByJPQL(), which internally creates a JPQL query according to a given string, executes the query, builds a list of entities and finally returns the latter in a type-safe fashion.
When coding a DAO method on Demoiselle, we could use fixed JPQL queries whenever there is no need to give parameters to them. The CategoryDAO implementation class exhibits the usage of fixed string JPQL queries through findByJPQL():
public List<category> listAvailableCategories() {
return findByJPQL("select c from Category c where c.active = true order by c.name");
}
3. JPQL with positional parameters
As most of our real-world queries involve passing parameters to them, JPQL also offers the possibility of doing this. Actually, there are two ways of passing parameters to a query: using positional (numbered) or named parameters.
Positional parameters will be explored in the current section. They are expressed in the JPQL string with the "?" character followed by an integer number starting from 1.
On JPAGenericDAO the method findByJPQL() is overloaded so that it can handle positional parameters as well. The AuctionDAO implementation class exhibits the usage of JPQL queries with positional parameters on listAllAuctionsByItem() method:
public List<Auction> listAllAuctionsByItem(Item item) {
String jpql = "select a from Auction a where a.item = ?1";
List<Auction> result = findByJPQL(jpql, item);
return result;
}
Demoiselle Framework introduces pagination mechanisms through Page and PagedResult classes. In order to use them, one needs to invoke an other overloaded version of findByJPQL(), which accepts a Page object as the second argument. The BidDAO implementation class comes with listLastBidsForAuction() method, which exemplifies pagination of results using the classes described and yet accepts a positional parameter:
public List<Bid> listLastBidsForAuction(Auction auction, int qty) {
Page page = new Page(qty);
String jpql = "select b from Bid b where b.auction.id = ?1 order by b.timestamp desc";
PagedResult<Bid> pr = findByJPQL(jpql, page, auction.getId());
return (List<Bid>) pr.getResults();
}
4. JPQL with named parameters
There are some situations where numbered parameters in a query might be a confusing or not preferred alternative. In order to handle it, JPA specification foresees named parameters on JPQL queries.
According to JPA, a named parameter in JPQL is indicated with the ":" character followed by an unique string which specifies it.
Inside Demoiselle, using named parameters consists of a map instantiation and the execution of another overloaded findByJPQL(), this time receiving in addition a Map object as argument. The AuctionDAO implementation class exhibits the usage of JPQL queries with bound named parameters:
public List<Auction> listOpenAuctionsByCategory(Category category) {
String jpql =
"select a from Auction a " +
"where a.status = :status " +
" and a.deadline > :deadline " +
" and a.item.category = :category " +
"order by a.item.description";
Map<String, Object> params = new HashMap<String, Object<();
params.put("status", Status.OPEN);
params.put("deadline", new Date());
params.put("category", category);
List<Auction> result = findByJPQL(jpql, params);
return result;
}
5. Named JPQL query
As an alternative introduced by Hibernate, JPA brings a feature called named queries, which aims at writing complex JPQL statements inside entity classes in the form of annotations. These so-called annotated queries are then referenced back when issuing JPQL queries.
JPAGenericDAO comes handy once again by providing another method: findByNamedQuery(). This method receives a string containing the name of a JPQL query whose statement is already on an entity. For instance, let the Item entity with a @NamedQuery annotation right above its class declaration:
@NamedQuery(
name = "itemsByCategory",
query = "select i from Item i where i.category.id = ?1 order by i.description"
)
public class Item implements IPojoExtension { ... }
Then, the respective ItemDAO implementation class should make use of JPAGenericDAO's findByNamedQuery() as shown in the code belonging to listByCategory() method:
public List<Item> listByCategory(Category category) {
return findByNamedQuery("itemsByCategory", category.getId());
}
Note that in the previous example a positional parameter was placed. But what if we need named parameters alongside named queries? The answer is: an overloaded version of findByNamedQuery() might take place! For example, the Auction entity presents a @NamedQuery according to the code below:
@NamedQuery(
name = "openAuctionsByItem",
query = "select a from Auction a where a.status = :status and a.deadline > :deadline and a.item = :item"
)
public class Auction implements IPojoExtension { ... }
Observe that named parameters on a named JPQL query are indicated in the annotation as if they were to be used by findByJPQL() method. And then, in the same manner, we need to create a map object in order to invoke the method, which this time is findByNamedQuery(). Take a look at the contents of AuctionDAO, particularly the listOpenAuctionsByItem() method:
public List>Auction< listOpenAuctionsByItem(Item item) {
Map<String, Object> params = new HashMap<String, Object<();
params.put("status", Status.OPEN);
params.put("deadline", new Date());
params.put("item", item);
List<Auction> result = findByNamedQuery("openAuctionsByItem", params);
return result;
}
6. Native SQL query
So, are we constrained to JPQL statements and object-oriented queries when adopting JPA persistence mechanism on a Java application? Not at all!
There are some cases where performance matters most of everything and the only possibility of achieving it is by executing native SQL instructions, usually made through JDBC in the Java world. This particular approach can boost up SQL execution time, especially for those who prefer to create their own database stored procedures or functions.
Despite this development behavior can lead to a given DBMS dependency and thus compromising application portability, the architect still might choose to create native SQL queries. JPA specification was developed with a rich set of components (i.e. interfaces and annotations) concerning the invocation of native SQL instructions.
In Demoiselle, once again, JPAGenericDAO can help the developer with a pre-built method: findByNativeQuery(). Take the example of the listMostOfferedAuctions() method inside AuctionDAO, which contains SQL directed to PostgreSQL DBMS:
public List<Auction> listMostOfferedAuctions(int quantity) {
String sql =
"select d.* from ( " +
" select b.auction_id, count(b.id) " +
" from bids b " +
" inner join auctions a on (a.id = b.auction_id) " +
" where a.status = ?1 and a.mode <> ?2 " +
" group by b.auction_id " +
" order by count(b.id) desc " +
" limit ?3) c " +
"join auctions d on (d.id = c.auction_id) " +
"where d.bestbid_id is not null " +
"order by c.count desc";
List<Auction> result = findByNativeQuery(sql,
Status.OPEN.ordinal(), Mode.SELLING.ordinal(), quantity);
return result;
}
Another method of AuctionDAO, listEndingSoonAuctions(), makes use of native SQL query and positional parameters:
public List<Auction> listEndingSoonAuctions(int seconds, int quantity) {
String sql =
"select * " +
"from auctions " +
"where status = ?1 " +
" and deadline - current_timestamp between '0 s' and cast(?2 as interval) " +
"order by deadline " +
"limit ?3";
List<Auction> result = findByNativeQuery(sql,
Status.OPEN.ordinal(), seconds + " s", quantity);
return result;
}
But now another issue is raised: what if we need native SQL query along with named parameters? The answer might be simple: just merge both techniques into one, right? Well, partially.
According to JPA 1.0 specification, a named parameter is an identifier that is prefixed by the ":" symbol, and its use applies just to JPQL, therefore not being defined for native queries. Thus, only positional parameter binding may be portably used for native queries.
Despite that lack of portability, JPA implementations usually offer named parameters on native queries. Unfortunately there is no common sense yet, so that implementations consider "?" or ":" symbols when defining named parameters in a native SQL string. This is better illustrated in the code below, taken from AuctionDAO's listCheapestPriceAuctions() method, which is prepared to run on Hibernate EntityManager JPA implementation:
public List<Auction> listCheapestPriceAuctions(int quantity) {
// WARNING: prepared for Hibernate - use "?" instead of ":" for TopLink or EclipseLink
String sql =
"select d.* from (" +
" select a.id, coalesce(b.amount, a.startingprice, a.sellingprice) as price " +
" from auctions a left join bids b on (b.id = a.bestbid_id) " +
" where a.status = :status " +
" order by 2 asc " +
" limit :quantity) c " +
"join auctions d on c.id = d.id " +
"order by c.price";
Map<String, Object> params = new HashMap<String, Object<();
params.put("status", Status.OPEN.ordinal());
params.put("quantity", quantity);
List<Auction> result = findByNativeQuery(sql, params);
return result;
}
Thus, portability is compromised in the previous example, as we need to take special precautions when switching to another JPA implementation, say EclipseLink or Apache OpenJPA.
7. Named native SQL query
As with JPQL, it is possible to store the entire native query statement onto an entity class and then reference it as needed. In order to do that, JPA defines another annotation, @NamedNativeQuery, which has the same purpose of @NamedQuery. After setting the named native query, one could invoke findByNamedQuery() method to execute it and possibly bring back the results. Note that this very same method fits for both JPQL and native SQL queries.
So, consider the named native query "newestAuctions", which is defined on Auction entity file. There, in the @NamedNativeQuery annotation three parameters must be passed: the query unique identifier, the statement itself, and a class indicating the result type. The referenced code is shown below:
@NamedNativeQuery(
name = "newestAuctions",
query = "select * from auctions where status = ?1 order by creation desc limit ?2",
resultClass = Auction.class
)
public class Auction implements IPojoExtension { ... }
At DAO implementation class AuctionDAO this named native query is called inside listNewestAuctions() method, as illustrated in the following code:
public List<Auction> listNewestAuctions(int quantity) {
List<Auction> result = findByNamedQuery("newestAuctions",
Status.OPEN.ordinal(), quantity);
}
8. LIQUidFORM tool
JPA 1.0 specification does not provide a Criteria API [7], a feature introduced by Hibernate designed to help developers on building HQL queries. With that we avoid string concatenation and rather we declaratively define the statement, thus reducing the risk of errors in its synthax.
Fortunately JPA 2.0 was conceived with a Criteria API, thus soon JPA implementations will run after that. Meanwhile, a lot of alternatives were developed to fill in such requirement in JPA. Amongst them, one particular tool was employed in the sample code: LIQUidFORM [8].
LIQUidFORM is actually a set of classes and interfaces designed to compose JPQL statements in a type-safe and refactoring proof style. It might be strange at first sight, as it contains methods called select(), from(), and where(), but it is indeed a very interesting idea.
The key to LIQUidFORM is to make use of static imports in the class header, namely the lines below:
import static com.google.code.liquidform.LiquidForm.*;
import static com.google.code.liquidform.Parameters.*;
OrderDAO implementation class exhibits the usage of LIQUidFORM tool for creating a JPQL query inside listOrdersByLogin() method, as shown below:
public List<Order> listOrdersByLogin(String login) {
Order o = alias(Order.class, "o");
String jpql =
select(o).
from(Order.class).as(o).
where(eq(o.getLogin(), param(1, String.class))
).toString();
return findByJPQL(jpql, login);
}
vi. Conclusion
In this article we have explored some benefits of building the persistence layer of a Java application by adopting Java Persistence API (JPA 1.0) inside an architecture composed by DAO-based interfaces and implementation classes.
In addition, we have seen the usage of Demoiselle Framework structural IDAO interface and JPAGenericDAO support class when building methods designed to manipulate data through JPA. Those methods also served to exemplify the existing JPA approaches by introducing JPQL and native SQL queries, positional and named parameters, JPQL and SQL named queries, and LIQUidFORM tool.
vii. References
[1] Core J2EE Patterns - Data Access Object
[2] JPA 1.0 (JSR 220)
[3] Demoiselle Framework
[4] Auction5 Application Sample
[5] IDAO interface
[6] JPAGenericDAO class
[7] Criteria API
[8] LIQUidFORM
