Introduction
There are several ways in which to execute JPQL (Java Persistence Query Language). I'll introduce the "old way" first, with JDBC and direct connections to the database, before branching off into the ways used by ORMs (Object Relational Mapping).
Each way has its advantages and disadvantages.
The old way:
- direct database access
- database connection pool
Using an ORM
- hql in java code
- named queries in annotations
- named queries in xml
- Criterion API
- retrieving the object by its primary key
The Old Way
Direct Database Access
This is an example of direct database connection with jdbc. Setting it up, sending the query, reading the result, close the resultset and shutting down the connection.
public Macro runQuery() throws SQLException, InstantiationException,
ClassNotFoundException, IllegalAccessException
{
Macro result = null;
try
{
Class.forName(Constants.dbjdbcclass).newInstance();
String theUrl = Constants.dburl + "://" + Constants.dbhost
+ Constants.dbdomain + "/" + Constants.dbname + "?user="
+ Constants.dbuser + "&password=" + Constants.dbpasswd;
theConnection = DriverManager.getConnection(theUrl);
PreparedStatement statGetMacro = null;
statGetMacro = theConnection.prepareStatement(sqlGetMacro);
statGetMacro.setString(2, macroname);
res = statGetMacro.executeQuery();
if (res != null && res.next())
{
result = new Macro(res.getString("macroname"), res.getString("contents"));
res.close();
}
statGetMacro.close();
} catch (Exception e)
{
throw new MudDatabaseException(Constants.DATABASECONNECTIONERROR, e2);
}
finally
{
if (theConnection != null) {theConnection.close();}
}
return result;
}
Advantages:
- complete control
- very little libraries required
Disadvantages:
- sql syntax spread throughout the java code
- lots of error-prone boiler plate code required
- expensive in setting up new database connections for every query
- sytax of query not checked, until method called
- changing the query requires source code change, recompilation, repackaging, etc
Database Connection Pool
This is example of database connection with jndi resource pool, where the only thing required is the jndi name. In this case it is irrelevant which connection we receive from the connection pool. The actual theConnection.close() does nothing, but returns the connection back to the pool.
public void runQuery() throws Exception
{
Connection con=null;
ResultSet rst=null;
PreparedStatement stmt=null;
try
{
Context ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("jdbc/mmud");
con = ds.getConnection();
stmt=con.prepareStatement("select * from links where type = 1 order by creation");
rst=stmt.executeQuery();
while(rst.next())
{
out.println("<li><A HREF=\"" + rst.getString("url") + "\">" +
rst.getString("linkname") + "</A> (<i>" +
rst.getString("name") + "</i>)<P>");
}
rst.close();
stmt.close();
}
finally
{
if (rst != null) {try {rst.close();} catch (Exception e){}}
if (stmt != null) {try {stmt.close();} catch (Exception e)}}
if (con != null) {try {con.close();} catch (Exception e){}}
}
}
Advantages:
- database connection resource pool
- no need to open and close connection, connections are reused
Disadvantages:
- sql syntax spread throughout the java code
- still a lot of error-prone boiler plate code required
- sytax of query not checked, until method called
- if connections/resources not properly released, the pool will run out of connections/resources
- changing the query requires source code change, recompilation, repackaging, etc
Using an ORM
hql in java code
@Override
public void deleteAddress()
{
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.append("DELETE Address address ");
queryBuilder.append("WHERE address.country = 'Italy' ");
queryBuilder.append("AND NOT EXISTS (SELECT '' ");
queryBuilder.append(" FROM Customer cust ");
queryBuilder.append(" WHERE cust.address = address ");
queryBuilder.append(" ) ");
Query query = em.createQuery(queryBuilder.toString());
query.executeUpdate();
}
Unfortunately, the code above is what I encounter frequently in the source of my employers current software product.
Advantages:
Disadvantages:
- a StringBuffer is created every time the method is called, at the very least this should be a constant
- jpql is created and parsed and compiled every time the method is called
- sql/hql/jpql syntax spread throughout the java code
- sytax of query not checked, until method called
- changing the query requires source code change, recompilation, repackaging, etc
Named queries in annotations
Queries can be defined in the annotations on top of Entity classes. The annotation would look a little like the one below.
@NamedQueries({
@NamedQuery(name = "Character.findAll", query = "SELECT c FROM Character c"),
@NamedQuery(name = "Character.findByName", query = "SELECT c FROM Character c WHERE c.name = :name")})
One of the problems often heard is that the annotation has to be on an Entity class, when you perhaps would prefer to have it someplace else. In that case, it might be an idea to define the constants as strings in a different part, like a Service or a DAO or what have you, and refer to them in the NamedQuery annotations. Like in the two-part example below.
As the name of a named query is global within the persistent provider, it is essential to create unique names within the application. That is why in most cases, the name of a named query is prefixed with the class name.
@Stateless
@Remote(GameBeanRemote.class)
public class GameBean implements GameBeanRemote
{
public static final String character = "Character";
public static final String FINDALLCHARS_QUERYNAME = character + ".findAll";
public static final String FINDALLCHARS_QUERY = "SELECT c FROM Character c";
public static final String FINDCHAR_QUERYNAME = character + ".findByName";
public static final String FINDCHAR_QUERY = "SELECT c FROM Character c WHERE c.name = :name";
@PersistenceContext(unitName = "datasource")
private EntityManager em;
@Entity
@Table(name = "mm_usertable")
@NamedQueries({
@NamedQuery(name = GameBean.FINDALLCHARS_QUERYNAME, query = GameBean.FINDALLCHARS_QUERY),
@NamedQuery(name = GameBean.FINDCHAR_QUERYNAME, query = GameBean.FINDCHAR_QUERY)})
public class Character implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@Basic(optional = false)
@NotNull
@Size(min = 1, max = 20)
@Column(name = "name")
private String name;
Using the named queries in code is as simple as getting the entity manager and telling him the name of the NamedQuery you wish to use, add the parameter values, and request the result. You can refer to the NamedQueries by name, hence the name, get it?
@Override
public CommandOutput retrieveCharacter(String name)
{
itsLog.entering(this.getClass().getName(), "retrieveCharacter");
Query q = em.createNamedQuery("Character.findByName", Character.class);
q.setParameter("name", name);
Character c = (Character) q.getSingleResult();
if (c == null)
{
itsLog.exiting(this.getClass().getName(), "retrieveCharacter");
return new CommandOutput("", "", "");
}
CommandOutput co = new CommandOutput(c.getName(), c.getArm(), c.getBeard());
itsLog.exiting(this.getClass().getName(), "retrieveCharacter ", co);
return co;
}
The great part of NamedQueries is that they are checked and pre-compiled by Hibernate when the server starts, so you get instant messages if you screwed up the syntax.
Advantages:
- the Persistence provider might be able to precompile them, and this can help catch bugs before deployment
- sql syntax no longer spread throughout the java code
- sql queries externalised to the java code
- there's a central place for queries, making them easy to find and easy to re-use.
Disadvantages:
- named queries in annotations can only be added to the Entity classes or the SuperMapClass. This is especially awkward when you have a query spanning multiple entities.
- named queries are referenced using a key, which is global to the application.
- named queries are a little difficult to debug, as a change requires an application server reboot.
- changing the query in the annotation requires source code change, recompilation, repackaging, etc
In the examples below, JBoss is used in combination with Hibernate. The first example is the jboss log when things go wrong, and the second example is when things go right.
09:19:23,635 ERROR [SessionFactoryImpl] Error in named query: FullAddress.findAddressesOfCustomers
org.hibernate.QueryException: could not resolve property: entitynr of: mrbear.entity.Address [DELETE Address address WHERE address.country = 'Italy' AND address.entitynr > 1000 AND NOT EXISTS (SELECT '' FROM Customer cust WHERE cust.address = address) ]
at org.hibernate.persister.entity.AbstractPropertyMapping.propertyException(AbstractPropertyMapping.java:67)
09:23:10,946 INFO [AnnotationBinder] Binding entity from annotated class: mrbear.entity.Address
09:23:10,948 INFO [QueryBinder] Binding Named query: FullAddress.findAddressesOfCustomers => DELETE Address address WHERE address.country = 'Italy' AND address.entityNr > 1000 AND NOT EXISTS (SELECT '' FROM Customer cust WHERE cust.address = address)
Let me point you especially to the fact that HQL (or JPQL for that matter), contrary to SQL, is case-sensitive (for the most part). It would have to be, as it has a direct link to java classes and these are also case-sensitive.
In the example above, the getter and setter for entity number was specified as getEntityNr and setEntityNr, hence the problem with the casing in the first example.
- According to Gavin King [1]:
- We leave it up to you if you want to utilize the named query feature. However, we consider query strings in the application code (except if they're in annotations) to be the second choice; you should always externalize query strings if possible.
Named queries in xml
If you do not like to have your JPQL in your code at all, there is always the possibility of putting the queries into the xml files that are a part of the configuration of the jar file.
There is no reason why you cannot have both, both named queries in your annotations and queries in your xml persistence configuration files.
One of the great things is that the named queries in the persistence xml files take precedence over the named queries in the annotations. This means, that, when deployed in a specific production environment, it is possible to change the named queries, without having to touch/recompile/build/package the java source code. So, you can do convention over configuration.
<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
<persistence-unit name="mmud" transaction-type="JTA">
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<jta-data-source>mmud</jta-data-source>
<!-- Named JPQL queries per entity, but any other organization is possible -->
<mapping-file>META-INF/jpql/Character.xml</mapping-file>
<exclude-unlisted-classes>false</exclude-unlisted-classes>
<properties/>
</persistence-unit>
</persistence>
<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings version="1.0" xmlns="http://java.sun.com/xml/ns/persistence/orm"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm http://java.sun.com/xml/ns/persistence/orm_1_0.xsd "
>
<named-query name="Character.findByName">
<query>
SELECT
c
FROM
Character c
WHERE
c.password = :name
OR
c.name = 'Midevia'
</query>
</named-query>
</entity-mappings>
So in the code example that uses annotations, when using these two persistence xml files displayed above, suddenly "Midevia" is retrieved from the database, instead of the name you requested.
Advantages:
- the Persistence provider might be able to precompile them, and this can help catch bugs before deployment
- sql syntax no longer anywhere in the java code
- there's a central place for queries, making them easy to find and easy to re-use.
- named queries can be spread through many different xml files, making them more easy to categorise.
Disadvantages:
- named queries are referenced using a key, which is global to the application.
- named queries in xml, can be changed, if you don't mind hacking your way through xml.
- named queries are a little difficult to debug, as a change requires an application server reboot.
Criterion API
@Override
public CommandOutput retrieveCharacter(String name)
{
itsLog.entering(this.getClass().getName(), "retrieveCharacter");
CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery criteriaQuery = criteriaBuilder.createQuery();
Root<Character> from = criteriaQuery.from(Character.class);
CriteriaQuery<Character> select = criteriaQuery.select(from);
Predicate predicate = criteriaBuilder.equal(from.get("name"), name);
criteriaQuery.where(predicate);
TypedQuery<Character> typedQuery = em.createQuery(select);
List<Character> resultList = typedQuery.getResultList();
if (resultList == null || resultList.size() != 1)
{
itsLog.exiting(this.getClass().getName(), "retrieveCharacter");
return new CommandOutput("", "", "");
}
CommandOutput co = new CommandOutput(
resultList.get(0).getName(),
resultList.get(0).getArm(),
resultList.get(0).getBeard());
itsLog.exiting(this.getClass().getName(), "retrieveCharacter ", co);
return co;
}
Advantages:
- the Persistence provider might be able to precompile them, and this can help catch bugs before deployment
- sql syntax no longer anywhere in the java code
- the query is dynamic, making it easy to change depending on input from the user
- it is java, so every tool and compiler can aid in the development, like syntax checking and type checking and the like
Disadvantages:
Retrieving the object by its primary key
Ideally, we do not wish to use SQL or HQL or the Criteria API at all. If possible, we just want to tell the Entity Manager to get the objects we need. In most cases, this is not easy. But for some concepts, like getting an object based on its primary key (so we are always sure there's just one (or zero) available) there's a much more straightforward way.
@Override
public CommandOutput retrieveCharacter(String name)
{
itsLog.entering(this.getClass().getName(), "retrieveCharacter");
Character c = em.find(Character.class, name);
if (c == null)
{
itsLog.exiting(this.getClass().getName(), "retrieveCharacter");
return new CommandOutput("", "", "");
}
CommandOutput co = new CommandOutput(c.getName(), c.getArm(), c.getBeard());
itsLog.exiting(this.getClass().getName(), "retrieveCharacter ", co);
return co;
}
Advantages:
- no SQL/HQL/JPSQL of any kind required
- the JPA implementation can cache the object and retrieve is quickly.
Disadvantages:
- only for specific uses, like retrieving objects by primary key.
Conclusion
I hope to have demonstrated the different uses that exist in retrieving data from a database using any kind of ORM, compatible with the JPA. As you are no doubt aware from this piece, I strongly consider the last four examples to be good practices of creating JPQL queries in your application.
As always, each has its strong and weak points, and therefore are applicable to different situations. Evaluate the situation and pick the one that is best suited for the job.
References
- [1] Java Persistence with Hibernate
- Christian Bauer, Gavin King
- [2] chapter 2.3 - Hibernate Community Documentation
- http://docs.jboss.org/hibernate/annotations/3.5/reference/en/html_single/
- [3] Organize Your Named JPQL Queries
- http://eubauer.de/kingsware/2011/03/25/organize-your-named-jpql-queries/
- [4] Hibernate: Criteria vs. HQL
- http://stackoverflow.com/questions/197474/hibernate-criteria-vs-hql
- [5] Are Hibernate named HQL queries (in annotations) optimised?
- http://stackoverflow.com/questions/2641997/are-hibernate-named-hql-queries-in-annotations-optimised
- [6] GlassFish Project - Java Persistence Example
- http://glassfish.java.net/javaee5/persistence/persistence-example.html
- [7] Use string constants in annotations
- http://javahowto.blogspot.com/2009/04/use-string-constants-in-annotations.html
- [8] Java Persistence 2.0 Public Draft: Criteria API
- http://blogs.oracle.com/ldemichiel/entry/java_persistence_2_0_public1
- [9] Dynamic, typesafe queries in JPA 2.0
- http://www.ibm.com/developerworks/java/library/j-typesafejpa/
- [10] Where to put named queries in JPA?
- http://jdevelopment.nl/put-named-queries-jpa/
- [11] Java Glossary
- http://randomthoughtsonjavaprogramming.blogspot.com/p/glossary.html
