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.

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

More information: Obtaining an Entity Manager Instance

Procedure

Creating Dynamic Queries

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

...

       1.      To create a dynamic query, you use the createQuery method of the Entity Manager (EM).

       2.      You bind the named parameter with the setParameter method.

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

public List<Employee> getEmployeesWithGivenIds(Set<Integer> ids) {

    if (ids == null || ids.size() == 0) {

        returnCollections.EMPTY_SET;

    }

    // create the query text

    StringBuilder queryText = newStringBuilder("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

    returnquery.getResultList();

}

Creating Named Queries

A named query is an JPQL query that is pre-defined and identified against a given name.

...

       1.      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.

//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") })

       2.      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:

publicList<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;

    }

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.

...

       1.      To create a native SQL query, you use the createNativeQuery() method.

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