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
- 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.
* Connects to the database using an url. The url looks something like
* "jdbc:mysql://localhost.localdomain/mud?user=root&password=". Uses the
* classname in Constants.dbjdbcclass to get the right class for interfacing
* with the database server. If the database used is changed (or to be more
* specific the jdbc driver is changed) change the constant.
*
* @throws InstantiationException
* happens when it is impossible to instantiate the proper
* database class, from the class name as provided by
* Constants.dbjdbcclass.
* @throws ClassNotFoundException
* happens when it is impossible to find the proper database
* class, from the class name as provided by
* Constants.dbjdbcclass.
* @throws IllegalAccessException
* happens when it is not possible to create the database class
* because access restrictions apply.
* @throws SQLException
* happens when a connection to the database Server could not be
* established.
*/
public Macro runQuery() throws SQLException, InstantiationException,
ClassNotFoundException, IllegalAccessException
{
Macro result = null;
try
{
Class.forName(Constants.dbjdbcclass).newInstance();
// jdbc:mysql://[host][,failoverhost...][:port]/[database]
// [?propertyName1][=propertyValue1][&propertyName2][=propertyValue2]...
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;
}
- complete control
- very little libraries required
- 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.
{
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
- 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
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:- none
- 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.
@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.
* Game bean.
* @author Mr. Bear
*/
@Stateless
@Remote(GameBeanRemote.class)
public class GameBean implements GameBeanRemote
{
public static final String character = "Character";
/**
* label for the find all characters query.
*/
public static final String FINDALLCHARS_QUERYNAME = character + ".findAll";
/**
* query for the find of all characters query.
*/
public static final String FINDALLCHARS_QUERY = "SELECT c FROM Character c";
/**
* label for the find character based on name query.
*/
public static final String FINDCHAR_QUERYNAME = character + ".findByName";
/**
* query for the find of character based on name query.
*/
public static final String FINDCHAR_QUERY = "SELECT c FROM Character c WHERE c.name = :name";
@PersistenceContext(unitName = "datasource")
private EntityManager em;
* The Character entity.
* @author Mr. Bear
*/
@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?
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.
- 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.
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,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.
<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>
<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.
- 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
public CommandOutput retrieveCharacter(String name)
{
itsLog.entering(this.getClass().getName(), "retrieveCharacter");
// setup criteria builders/queries
CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery criteriaQuery = criteriaBuilder.createQuery();
// add from
Root<Character> from = criteriaQuery.from(Character.class);
CriteriaQuery<Character> select = criteriaQuery.select(from);
// add predicate
Predicate predicate = criteriaBuilder.equal(from.get("name"), name);
criteriaQuery.where(predicate);
// create the select query
TypedQuery<Character> typedQuery = em.createQuery(select);
// get the results
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;
}
- 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
- fairly unreadable
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.
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;
}
- no SQL/HQL/JPSQL of any kind required
- the JPA implementation can cache the object and retrieve is quickly.
- 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
