Show TOC

Procedure documentationCreating and Executing Queries Locate this document in the navigation structure

 

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

End of the caution.

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.

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

    Example Example

    1. 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();
}
    End of the code.
Creating Named Queries

A named query is a JPQL query that is predefined 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.

    Example Example

    1. //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") })
    End of the code.

  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:

    Example Example

    1. 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;
}
    End of the code.

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

End of the note.
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).

More Information

Queries