Creating and Executing Queries

Use

JPA supports dynamic queries, native SQL queries, and named queries. JPQL also supports bulk update and deletes and subqueries. All queries are polymorphic by default. Queries accept named parameters, denoted by the : operator, for example :employeeName . You create queries in business methods in session beans.

Caution

The default FlushModeType for queries is AUTO . This means that each time a query is executed, the persistence context is flushed to the database. To perform this operation, the database locks all table rows to be changed until a commit or rollback of the transaction occurs.

Prerequisites

You have obtained an entity manager instance.

For more information, see Obtaining an Entity Manager Instance .

Procedure

Creating Dynamic Queries

A dynamic query is a query that passes parameters dynamically supplied to the container.

To create a dynamic query, you use the createQuery method of the Entity Manager (EM).
You bind the named parameter with the setParameter method.

You retrieve the results with getSingleResult (if the query returns a single record) or getResultList (if the query returns a list of records).

Sample Code

                        public List<Employee> getEmployeesWithGivenIds(Set<Integer> ids) {
        if (ids == null || ids.size() == 0) {
                return Collections.EMPTY_SET;
        }

        // create the query text

        StringBuilder queryText = new StringBuilder("SELECT e FROM Employee e WHERE e.id IN (?1");
        for (int i = 2; i <= ids.size(); i++) {
                queryText.append(", ?");
                queryText.append(i);
        }
        queryText.append(")");

        // prepare the query

        Query query = em.createQuery(queryText.toString());

        // bind the parameters

        int j = 1;
        for (Integer id : ids) {
                query.setParameter(j, id);
                j++;
        }

        // execute the query

        return query.getResultList();
}

Creating Named Queries

A named query is a JPQL query that is predefined and identified against a given name.

To define a named query, you add the @NamedQuery annotation and supply values for its name and query attributes. All of the named queries are placed inside the @NamedQueries annotation and before the class declaration.

Sample Code

                        //named queries

@NamedQueries( { @NamedQuery(name = "findAllEmployees", query = "SELECT e FROM Employee e"),
        @NamedQuery(name = "findAllDepartments", query = "SELECT d FROM Department d"),
        @NamedQuery(name = "findAllProjects", query = "SELECT p FROM Project p"),
        @NamedQuery(name = "findAllSkills", query = "SELECT s FROM Skill s") })

The name attribute identifies the query against a string value. The query attribute defines the query to be executed by the EM. To execute a named query, you use the createNamedQuery() method of the EM:

Sample Code

                        public List<Department> getAllDepartments() {
        List<Department> departments = em.createNamedQuery("findAllDepartments").getResultList();
        return departments;
}
public List<Employee> getAllEmployees() {
        List<Employee> employees = em.createNamedQuery("findAllEmployees").getResultList();
        return employees;
}
public List<Skill> getAllSkills() {
        List<Skill> skills = em.createNamedQuery("findAllSkills").getResultList();
        return skills;
}

Note

The SAP NetWeaver Developer Studio provides validation of named queries in the following manner:

If you have defined your application tables in Java Dictionary, and if you have associated your entities' development project with a Java Dictionary connection profile, the Developer Studio validates named queries against the table definitions and the JPQL grammar.
In case that you are not using Java Dictionary, but a native database, the Developer Studio validates named queries against the JPQL grammar only.

You can see validation errors, if any, in the Problems view.

Creating Native SQL Queries

By default, native JPQL queries return a single type of entity, include all the columns that correspond to all the fields or properties of the entity returned, and use column names that correspond to the field or property names.

To create a native SQL query, you use the createNativeQuery() method.
You retrieve the results with getSingleResult() (if the query returns a single record) or getResultList() (if the query returns a list of records).

More Information

Queries