1. Introduction
In this article you will learn how to use the JPA @NamedQuery and @NamedNativeQuery annotations. We will start by introducing some general aspects about named queries, then creating simple examples, using both JPQL and native SQL queries, passing parameters to named queries and using vendor-specific query hints. You will then learn how to execute named queries and use optional elements to make use of vendor-specific query hints.
For developing the application presented in this article we used NetBeans IDE 8.1, Payara 4.1.1.154 (Full Java EE) application server and Apache Derby Server 10.11.1.2 (that comes bundled with Payara), but you can also change to use GlassFish application server instead of Payara. You will also need a JDK environment, 1.7 or 1.8.
The complete application can be downloaded from here.
2. What is a named query and why using them?
The first thing to keep in mind is that the Java Persistence API defines two types of named queries, JPQL named queries and native SQL named queries.
A JPQL named query is any JPQL query defined using the @NamedQuery annotation or using the named-query element in a mapping file. A native SQL named query is any SQL query defined using the @NamedNativeQuery annotation or using the named-native-query element in a mapping file.
The @NamedQuery and @NamedNativeQuery annotations can be used individually or can coexist on the class definition for an entity. The annotations defines the name of the query, as well as the query text. In addition, when using native SQL named queries, if the result type is an entity, the resultClass element or the result-class attribute of a named-native-query element may be used to indicate the entity class.
In a real application, you will probably need multiple named queries defined on an entity class. For this, you will need to place multiple @NamedQuery annotations inside a @NamedQueries annotation. Similarly, multiple @NamedNativeQuery annotations are placed inside a @NamedNativeQueries annotation to define multiple native SQL named queries on an entity class.
Why should you consider using named queries? There are a few advantages, listed below:
- one consist in the fact that named query annotations are compiled and validated at startup time
- the annotations will be processed only once, then executed at runtime in query form
- the application avoids continuously parsing JPQL and generating SQL
- named queries are easier to maintain than string literals embedded in your code
- you can override existing named queries defined as an annotation using XML mapping files
3. How can I write named queries?
As mention before, there are two types of named queries, JPQL named queries and native SQL named queries. Moreover, each of them can be defined via annotations or in a mapping file. In this section, we will explore these four use-cases.
3.1 Writing JPQL named queries
A JPA @NamedQuery annotation requires two elements, name and query, as shown below:
package eclipselink.apachederby.entity;
// imports
@Entity
@Table(name = "atp_players")
@NamedQuery(
name = "Player.findAllUnordered",
query = "SELECT p FROM Player p")
public class Player implements Serializable {
...
}The name element is used to refer to the query when using the EntityManager methods to create the query. The query element is used to specify a query string in the Java Persistence query language. The name of the query is scoped to the entire persistence unit and it must be unique. In order to respect this rule, a common practice is to prefix the name of the query with the name of the entity that is being returned, separated by a dot.
Multiple JPQL named queries can be specified using the @NamedQueries annotation, as follows:
package eclipselink.apachederby.entity;
// imports
@Entity
@Table(name = "atp_players")
@NamedQueries({
@NamedQuery(
name = "Player.findAllUnordered",
query = "SELECT p FROM Player p"),
@NamedQuery(
name = "Player.findAllOrderedByName",
query = "SELECT p FROM Player p ORDER BY p.name")
})
public class Player implements Serializable {
...
@Column(name = "player_name")
private String name;
...
}A JPQL named query can also be defined in a mapping file using the named-query element, as follows:
<?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="Player.findAllUnorderedFromXML">
<query>SELECT p FROM Player p</query>
</named-query>
</entity-mappings>The name of the query is specified as an attribute of the named-query element. The query string is specified within the query subelement of named-query. The name of the query must be unique, unless we want to override the query that was defined using an annotation.
When overriding a query, you have to be careful to override it only with a query that has the same result type, or the code that executes the query and processes the result might break.
To define multiple JPQL named queries in a mapping file, you can add multiple named-query elements, as follows:
<?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="Player.findAllUnorderedFromXML">
<query>SELECT p FROM Player p</query>
</named-query>
<named-query name="Player.findAllOrderedByNameFromXML">
<query>SELECT p FROM Player p ORDER BY p.name</query>
</named-query>
</entity-mappings>The XML mapping file is usually located in the /META-INF application folder and must be specified in the persistence.xml file, as follows:
<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
<persistence-unit name="EclipseLink_NamedQuery-ejbPU" transaction-type="JTA">
<provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
<mapping-file>META-INF/queries.xml</mapping-file>
<class>eclipselink.apachederby.entity.Player</class>
...
</persistence-unit>
</persistence>3.2 Writing native SQL named queries
A JPA @NamedNativeQuery annotation requires two elements, name and query, as shown below:
package eclipselink.apachederby.entity;
// imports
@Entity
@Table(name = "atp_players")
@NamedNativeQuery(
name = "Player.countAllwithSQL",
query = "SELECT COUNT(*) AS total FROM atp_players")
public class Player implements Serializable {
...
}The name element is used to refer to the query when using the EntityManager methods to create the query. The query element is used to specify the native SQL query. The name of the query must be unique. If the result type is an entity, the resultClass element can be used to indicate the entity class:
package eclipselink.apachederby.entity;
// imports
@Entity
@Table(name = "atp_players")
@NamedNativeQuery(
name = "Player.findAllUnorderedWihSQL",
query = "SELECT * FROM atp_players",
resultClass = Player.class)
public class Player implements Serializable {
...
}Multiple native SQL named queries can be specified using the @NamedNativeQueries annotation, as follows:
package eclipselink.apachederby.entity;
// imports
@Entity
@Table(name = "atp_players")
@NamedNativeQueries({
@NamedNativeQuery(
name = "Player.countAllwithSQL",
query = "SELECT COUNT(*) AS total FROM atp_players"),
@NamedNativeQuery(
name = "Player.findAllUnorderedWihSQL",
query = "SELECT * FROM atp_players",
resultClass = Player.class)
})
public class Player implements Serializable {
...
}A native SQL named query can also be defined in a mapping file using the named-native-query element, as follows:
<?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-native-query name="Player.countAllwithSQLfromXML">
<query>SELECT COUNT(*) AS total FROM atp_players</query>
</named-native-query>
</entity-mappings>The name of the query is specified as an attribute of the named-native-query element. The query string is specified within the query subelement of named-native-query. The name of the query must be unique, unless we want to override the query that was defined using an annotation. If the result type is an entity, the result-class attribute of a named-native-query element can be used to indicate the entity class.
To define multiple native SQL named queries in a mapping file, you can add multiple named-native-query elements, as follows:
<?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-native-query name="Player.countAllwithSQLfromXML">
<query>SELECT COUNT(*) AS total FROM atp_players</query>
</named-native-query>
<named-native-query name="Player.findAllUnorderedWihSQLfromXML"
result-class="eclipselink.apachederby.entity.Player">
<query>SELECT * FROM atp_players</query>
</named-native-query>
</entity-mappings>You are free to create one or multiple mapping files for your named queries, as long as you specify each of them in the persistence.xml file. For example, you can choose to create a mapping file for the JPQL named queries and another mapping file for the native SQL named queries, or you can define all named queries in the same mapping file.
4. How can I execute a named query?
Once defined with an annotation or in a mapping file, named queries are created by passing the name of the query to the EntityManager createNamedQuery() method. Depending on the result of the query, we can then call the getSingleResult() method or the getResultList() method defined in the Query interface. The following examples creates and executes the queries defined in the previous section:
package eclipselink.apachederby.ejb;
// imports
@Named("bean")
@Stateless
public class SampleBean {
@PersistenceContext(unitName = "EclipseLink_NamedQuery-ejbPU")
private EntityManager em;
private List players;
...
@SuppressWarnings("unchecked")
public void findAllUnordered() {
players = em.createNamedQuery("Player.findAllUnordered")
.getResultList();
}
@SuppressWarnings("unchecked")
public void findAllUnorderedFromXML() {
players = em.createNamedQuery("Player.findAllUnorderedFromXML")
.getResultList();
}
@SuppressWarnings("unchecked")
public void findAllOrderedByName() {
players = em.createNamedQuery("Player.findAllOrderedByName")
.getResultList();
}
@SuppressWarnings("unchecked")
public void findAllOrderedByNameFromXML() {
players = em.createNamedQuery("Player.findAllOrderedByNameFromXML")
.getResultList();
}
@SuppressWarnings("unchecked")
public void findAllUnorderedWihSQL() {
players = em.createNamedQuery("Player.findAllUnorderedWihSQL")
.getResultList();
}
@SuppressWarnings("unchecked")
public void findAllUnorderedWihSQLfromXML() {
players = em.createNamedQuery("Player.findAllUnorderedWihSQLfromXML")
.getResultList();
}
public int countAllwithSQL() {
int total = (int) em.createNamedQuery("Player.countAllwithSQL")
.getSingleResult();
return total;
}
public int countAllwithSQLfromXML() {
int total = (int) em.createNamedQuery("Player.countAllwithSQLfromXML")
.getSingleResult();
return total;
}
...
}The code is self-explanatory, each time a named query is executed, the result is kept in either a list of players, or into an int variable, when counting the number of players.
5. Using query parameters and query hints in named queries
JPA supports both named and positional parameters for JPQL queries. A JPQL named query with a named parameter can be defined as follows:
package eclipselink.apachederby.entity;
// imports
@Entity
@Table(name = "atp_players")
@NamedQuery(
name = "Player.findByAge",
query = "SELECT p FROM Player p WHERE p.age = :age")
public class Player implements Serializable {
...
@Column(name = "player_age")
private int age;
...
}To pass the necessary parameter and execute the query, we can then use:
...
@SuppressWarnings("unchecked")
public void findByAge() {
players = em.createNamedQuery("Player.findByAge")
.setParameter("age", 27)
.getResultList();
}
...A JPQL named query that uses positional parameters can be defined as follows:
package eclipselink.apachederby.entity;
// imports
@Entity
@Table(name = "atp_players")
@NamedQuery(
name = "Player.findBetweenAge",
query = "SELECT p FROM Player p WHERE p.age BETWEEN ?1 AND ?2")
public class Player implements Serializable {
...
@Column(name = "player_age")
private int age;
...
}To pass the necessary parameters and execute the query, we can then use:
...
@SuppressWarnings("unchecked")
public void findBetweenAge() {
players = em.createNamedQuery("Player.findBetweenAge")
.setParameter(1, 24)
.setParameter(2, 28)
.getResultList();
}
...Additional query hints can also be specified to a named query. A hint is simply a string name and object value and each query may be associated with any number of hints. Query hints are specific to the persistence provider, EclipseLink for example, and are usually used to enable different performance optimizations for queries.
To specify a query hint for a JPQL or SQL named query defined using an annotation, we can use:
package eclipselink.apachederby.entity;
// imports
@Entity
@Table(name = "atp_players")
@NamedQuery(
name = "Player.findBetweenAge",
query = "SELECT p FROM Player p WHERE p.age BETWEEN ?1 AND ?2",
hints = {
@QueryHint(name = QueryHints.QUERY_RESULTS_CACHE, value = HintValues.FALSE)
})
@NamedNativeQuery(
name = "Player.findAllUnorderedWihSQL",
query = "SELECT * FROM atp_players",
resultClass = Player.class,
hints = {
@QueryHint(name = QueryHints.QUERY_RESULTS_CACHE, value = HintValues.FALSE)
})
public class Player implements Serializable {
...
@Column(name = "player_age")
private int age;
...
}Query hints can also be specified for named queries defined in a mapping file, as follows:
...
<named-query name="Player.findAllUnorderedFromXML">
<query>SELECT p FROM Player p</query>
<hint name="eclipselink.query-results-cache" value="false"/>
</named-query>
<named-native-query name="Player.findAllUnorderedWihSQLfromXML"
result-class="eclipselink.apachederby.entity.Player">
<query>SELECT * FROM atp_players</query>
<hint name="eclipselink.query-results-cache" value="false"/>
</named-native-query>
...6. Testing the application
For this article, we have developed an EAR application, called NamedQuery_EclipseLink_and_ApacheDerby, which aims to illustrate the use-case presented in the previous section. The application contains two modules, an EJB module in which we will develop our EJB beans and entities and a WAR module needed to simply display our data in a web page.
Now let’s have a quick look to the WAR module. We will use the JavaServer Faces technology for the presentation layer of our application. There is nothing fancy here, there are no managed beans, just a simple .xhtml page which looks like below:
<?xml version='1.0' encoding='UTF-8' ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"
xmlns:h="http://java.sun.com/jsf/html">
<h:head>
<title>JPA @NamedQuery Examples</title>
</h:head>
<h:body>
<h3>JPA @NamedQuery Examples (EclipseLink and ApacheDerby)</h3>
<h:form>
<h:commandButton action="#{bean.persistAction()}"
value="Populate database"/><br/>
<h:commandButton action="#{bean.findAllUnordered()}"
value="findAllUnordered()"/>
<h:commandButton action="#{bean.findAllUnorderedFromXML()}"
value="findAllUnorderedFromXML()"/>
<h:commandButton action="#{bean.findAllUnorderedWihSQL()}"
value="findAllUnorderedWihSQL()"/>
<h:commandButton action="#{bean.findAllUnorderedWihSQLfromXML()}"
value="findAllUnorderedWihSQLfromXML()"/><br/>
<h:commandButton action="#{bean.findAllOrderedByName()}"
value="findAllOrderedByName()"/>
<h:commandButton action="#{bean.findAllOrderedByNameFromXML()}"
value="findAllOrderedByNameFromXML()"/>
<h:commandButton action="#{bean.findByAge()}"
value="findByAge()"/>
<h:commandButton action="#{bean.findBetweenAge()}"
value="findBetweenAge()"/>
</h:form>
<p>countAllwithSQL(): <h:outputText value="#{bean.countAllwithSQL()}"/></p>
<p>countAllwithSQLfromXML(): <h:outputText
value="#{bean.countAllwithSQLfromXML()}"/></p>
<h:dataTable value="#{bean.loadData()}" var="t" border="1"
rendered="#{facesContext.postback and bean.loadData().size() > 0}">
<h:column>
#{t.name}
</h:column>
<h:column>
#{t.surname}
</h:column>
<h:column>
#{t.age}
</h:column>
<h:column>
#{t.birth}
</h:column>
</h:dataTable>
</h:body>
</html>Notice that CDI is used in order to reference #{bean} to the SampleBean session bean located in the eclipselink.apachederby.ejb package of our EJB module. When you press the “Populate database” button, it will call the persistAction() method and use a helper class (the Helper class inside the eclipselink.apachederby.helper package) in order to randomly generate some data and insert several players into the database.
Then, pressing the “findAllUnordered()” button will search for our list of players and display something like below:
Similarly, you can test the rest of the application. After each request is fired, when the page is rendered, it will also call the SampleBean.countAllwithSQL() and SampleBean.countAllwithSQLfromXML() methods and display the total number of players found in the database.
The persistence.xml file used for this application contains several configurations specific to Apache Derby that are highlighted below:
<?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="EclipseLink_OneToOne-ejbPU" transaction-type="JTA"> <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider> <mapping-file>META-INF/queries.xml</mapping-file> <class>eclipselink.apachederby.entity.Player</class> <exclude-unlisted-classes>true</exclude-unlisted-classes> <properties> <property name="javax.persistence.jdbc.driver" value="org.apache.derby.jdbc.EmbeddedDriver"/> <property name="javax.persistence.jdbc.url" value="jdbc:derby:memory:sampleDB;create=true"/> <property name="javax.persistence.schema-generation.database.action" value="drop-and-create"/> <property name="javax.persistence.schema-generation.create-source" value="metadata"/> <property name="javax.persistence.schema-generation.drop-source" value="metadata"/> </properties> </persistence-unit> </persistence>
These configurations specifies that the necessary tables will be created on the default schema (named sun-appserv-samples) when running our application. You can explore them by navigating to the Services tab in NetBeans IDE and connecting to the sun-appserv-samples database:
7. Conclusion
In this article we learned how to use the JPA @NamedQuery and @NamedNativeQuery annotations. Starting with some general aspects about named queries, we also write simple examples, using both JPQL and native SQL queries, passing parameters to named queries and use vendor-specific query hints. As a final step, we learned how to execute named queries and use optional elements to make use of vendor-specific query hints.
You can download the full source code of this example here: JPA Named Query Example.
