Where to put named queries in JPA?

7 September 2010, by: Development

JPA provides multiple ways to obtain entities. There is a very simple programmatic API that allows us to get an entity by ID, there’s a more elaborate one called the Criteria API and then there’s a query language called JPQL (Java Persistence Query Language).

JPQL is an object oriented query language that is based on the simplicity of SQL, but works directly on objects and their properties. The problem with such a language that’s used inside another language (Java) is where to store the query definition. Traditionally there have been 2 solutions:

  1. Store the definitions in annotations on some entity.
  2. Construct strings holding the definitions inline in your Java code.

The first solution is called a Named Query in JPA, it looks like this:


@NamedQueries(value={
   @NamedQuery(
      name = "Website.getWebsiteByUserId",
      query="select website from Website website where website.userId = :userId")
   @NamedQuery(...)
})
@Entity
public class Website { ... }

The advantages of this method are twofold: JPA checks your query is valid at startup time (no runtime surprises) and the query definition is parsed only once and re-used afterwards. As an extra bonus, it also strongly encourages to used named parameters. The disadvantage however is that its location is just plain awkward. The entity is typically not the location where we wish to store this kind of logic. It gets even more awkward when the query is about multiple entities, yet you have to choose a single one to store the query definition on. Instead, a DAO, Service or whatever code is used to interact with the entity manager is a much more logical place to store a query definition. Unfortunately, @NamedQuery only works on entities. Neither Enterprise beans nor any other kind of managed bean in Java EE supports them.

This thus brings us to the second solution, which looks like this:


public foo() {
    // some code
    Website website = entityManager.createQuery(
      "select website from Website website where" website.userId = :userId", Website.class)
      .setParameter("userId", userId)
      getSingleResult();

This is arguably a much better location, though still not ideal. If the query is long, we have to concatenate strings which makes the query hard to read and hard to maintain. It has the major disadvantage that the query is only checked at runtime and has to be re-parsed over and over again. There are some limited opportunities for reusing a Query object obtained by createQuery(), but since this object is only valid as long as the persistence context in which it was created is still active, those opportunities are really rather limited. Additionally, this style of query definition can make it tempting for developers to build their queries dynamically, giving rise to some nasty potential injection holes.

So having the choice between those two, which one do we choose? Actually, it appears there is a third solution, which is for some reason quite often overlooked by many people:

  1. Store the query text in XML (mapping) files.

In addition to annotations, JPA (and pretty much every API in Java EE that uses annotations) allows you to define the same thing or occasionally a little more in XML. Of course we don’t want one huge XML file with all our queries, but as it turns out JPA simply allows us to use as many files as we need and organize them in whatever way we want. We could for example put all queries related to some entity in one file anyway, or put all financial queries in one file and all core queries in another file, etc. The mechanism is actually quite similar to using multiple faces-config.xml files in JSF. The XML based solution looks as follows.

persistence.xml:


<?xml version="1.0" encoding="UTF-8"?>
<persistence 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_1_0.xsd"
	version="1.0">

	<persistence-unit name="somePU">
		<jta-data-source>java:/someDS</jta-data-source>

		<!-- Named JPQL queries per entity, but any other organization is possible  -->
		<mapping-file>META-INF/jpql/Website.xml</mapping-file>
		<mapping-file>META-INF/jpql/User.xml</mapping-file>
        </persistence-unit>
</persistence>

Website.xml:


<?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="Website.getByUserId">
		<query>
			SELECT 
				website
			FROM 
				Website website
			WHERE
				website.userId = :userId
		</query>
	</named-query>

	<named-query name="Website.foobar">
		<query>
			...
		</query>
	</named-query>

</named-query>
</entity-mappings>

Finally, using such query definitions in code is exactly the same as if they would have been defined using annotations, i.e. by calling entityManager.createNamedQuery().

Each XML file can contain as few or as many queries as you like. It might make sense to put a really complicated and huge query in one file, but to group several smaller related queries in another file. Do note that query names are part of a global namespace and are not automatically put in any namespace based on the file they are defined in. In the example above queries are pre-fixed with “Website.”, which happens to be the name of the entity but you can choose anything you want here. In the example META-INF/jpql was used as the directory to store queries, but any location on the class path including storing queries in jars will do.

As mentioned, for some reason this XML method seem to be often overlooked by many people. I’ve personally met multiple persons who build themselves a management system for storing JPQL queries in files, loading them, substituting parameters, etc while such a mechanism is in fact readily available in JPA (and has been since JPA 1.0!). Of course, the home grown systems don’t have the startup-time validation of queries nor do they do any pre-parsing and pre-compilation of queries.

Arjan

4 comments to “Where to put named queries in JPA?”

  1. Nelson Gadea says:

    Regards. i’m using EclipseLink JPA and JSF on Netbeans.

    I went to META-INF folder and there i created an xml file where i defined my queries, but when i run the proyect the project is not deployed and the output says there is not a mapping-file for the path “META-INF/my-mapping-file.xml”

    so where am i supposed to create the xml file and how to access there?

    in adition to that i just want to have queries there in the xml file.

  2. Giriraj Sharma says:

    Can u please provide an example to use the named queries stored in XML?

    Actually, I am storing all the queries in a single XML. Please provide me a way to access them.

    Thanks in advance.

  3. Nikolay Nikolov says:

    Thank you for your post – it is very useful. I did a post based on it including some modifications and adding some more details. You can see it by clicking on the link included with my name.

  4. Rupert Smith says:

    You can also put named queries on your DAO classes, by using the @MappedSuperClass annotation:

    @MappedSuperClass
    @NamedQueries(value={
    @NamedQuery(
    name = “Website.getWebsiteByUserId”,
    query=”select website from Website website where website.userId = :userId”)
    @NamedQuery(…)
    })
    public class SomeDAO {

    Then add SomeDAO to the list of classes in persistence.xml or by adding it as an annotated class to Hibernate config, or through Spring, or whatever.

    I quite like this way of doing things as it feels more natural to me that the queries are part of the DAO logic, rather than part of the entity itself.

Type your comment below:


seven × = 7

css.php best counter