Declare and use named queries, dynamic queries, and SQL (native) queries.

[Note]

Named Queries

Named queries are static queries expressed in metadata. Named queries can be defined in the Java Persistence query language or in SQL. Query names are scoped to the persistence unit.

The following is an example of the definition of a named query:

@NamedQuery(
	name="findAllCustomersWithName",
	query="SELECT c FROM Customer c WHERE c.name LIKE :custName"
)
					

The following is an example of the use of a named query:

@PersistenceContext
public EntityManager em;
	...
	customers = em.createNamedQuery("findAllCustomersWithName")
		.setParameter("custName", "Zaikin")
		.getResultList();
					

Query templates can be statically declared using the @NamedQuery and @NamedQueries annotations. For example:

@Entity
@NamedQueries({
	@NamedQuery(
		name="magsOverPrice",
		query="SELECT x FROM Magazine x WHERE x.price > ?1"
	),
	@NamedQuery(
		name="magsByTitle",
        query="SELECT x FROM Magazine x WHERE x.title = :titleParam"
	)
})
public class Magazine {
	...
}
					

These declarations will define two named queries called magsOverPrice and magsByTitle.

You retrieve named queries with the EntityManager.createNamedQuery(String name) method. For example:

EntityManager em = ...
Query q = em.createNamedQuery("magsOverPrice");
q.setParameter(1, 5.0f);
List<Magazine> results = (List<Magazine>) q.getResultList();
					
EntityManager em = ...
Query q = em.createNamedQuery("magsByTitle");
q.setParameter("titleParam", "JDJ");
List<Magazine> results = (List<Magazine>) q.getResultList();
					

Dynamic Queries

Example:

public List findWithName(String name) {
	return em.createQuery("SELECT c FROM Customer c WHERE c.name LIKE :custName")
		.setParameter("custName", name)
		.setMaxResults(10)
		.getResultList();
}
					

SQL Queries

Queries may be expressed in native SQL. The result of a native SQL query may consist of entities, scalar values, or a combination of the two. The entities returned by a query may be of different entity types.

When multiple entities are returned by a SQL query, the entities must be specified and mapped to the column results of the SQL statement in a @SqlResultSetMapping metadata definition. This result set mapping metadata can then be used by the persistence provider runtime to map the JDBC results into the expected objects.

If the results of the query are limited to entities of a SINGLE entity class, a simpler form may be used and @SqlResultSetMapping metadata is not required.

This is illustrated in the following example in which a native SQL query is created dynamically using the createNativeQuery method and the entity class that specifies the type of the result is passed in as an argument.

Query q = em.createNativeQuery("SELECT o.id, o.quantity, o.item " +
	"FROM Order o, Item i " +
	"WHERE (o.item = i.id) AND (i.name = 'widget')",
	com.acme.Order.class
);
					

When executed, this query will return a Collection of all Order entities for items named "widget". The same results could also be obtained using @SqlResultSetMapping:

Query q = em.createNativeQuery("SELECT o.id, o.quantity, o.item " +
	"FROM Order o, Item i " +
	"WHERE (o.item = i.id) AND (i.name = 'widget')",
	"WidgetOrderResults"
);
					

In this case, the metadata for the query result type might be specified as follows:

@SqlResultSetMapping(
	name="WidgetOrderResults",
	entities=@EntityResult(entityClass=com.acme.Order.class)
)
					

The following query and @SqlResultSetMapping metadata illustrates the return of MULTIPLE entity types and assumes default metadata and column name defaults:

Query q = em.createNativeQuery("SELECT o.id, o.quantity, o.item, " +
	"i.id, i.name, i.description " +
	"FROM Order o, Item i " +
	"WHERE (o.quantity > 25) AND (o.item = i.id)",
	"OrderItemResults"
);
					
@SqlResultSetMapping(
	name="OrderItemResults",
	entities={
		@EntityResult(entityClass=com.acme.Order.class),
		@EntityResult(entityClass=com.acme.Item.class)
	}
)
					

When an entity is being returned, the SQL statement should select ALL of the columns that are mapped to the entity object. This should include foreign key columns to related entities. The results obtained when insufficient data is available are undefined. A SQL result set mapping must NOT be used to map results to the non-persistent state of an entity.

The column names that are used in the SQL result set mapping annotations refer to the names of the columns in the SQL SELECT clause. Note that column aliases must be used in the SQL SELECT clause where the SQL result would otherwise contain multiple columns of the same name.

An example of combining multiple entity types and that includes aliases in the SQL statement requires that the column names be explicitly mapped to the entity fields. The @FieldResult annotation is used for this purpose:

Query q = em.createNativeQuery("SELECT o.id AS order_id, " +
	"o.quantity AS order_quantity, " +
	"o.item AS order_item, " +
	"i.id, i.name, i.description " +
	"FROM Order o, Item i " +
	"WHERE (order_quantity > 25) AND (order_item = i.id)",
	"OrderItemResults"
);
					
@SqlResultSetMapping(
	name="OrderItemResults",
	entities={
		@EntityResult(
			entityClass=com.acme.Order.class, 
			fields={
				@FieldResult(name="id", column="order_id"),
				@FieldResult(name="quantity", column="order_quantity"),
				@FieldResult(name="item", column="order_item")
			}
		),
		@EntityResult(
			entityClass=com.acme.Item.class
		)
	}
)
					

Scalar result types CAN be included in the query result by specifying the @ColumnResult annotation in the metadata.

Query q = em.createNativeQuery("SELECT o.id AS order_id, " +
	"o.quantity AS order_quantity, " +
	"o.item AS order_item, " +
	"i.name AS item_name, " +
	"FROM Order o, Item i " +
	"WHERE (order_quantity > 25) AND (order_item = i.id)",
	"OrderResults"
);
					
@SqlResultSetMapping(
	name="OrderResults",
	entities={
		@EntityResult(
			entityClass=com.acme.Order.class, 
			fields={
				@FieldResult(name="id", column="order_id"),
				@FieldResult(name="quantity", column="order_quantity"),
				@FieldResult(name="item", column="order_item")
			}
		)
	},
	columns={
		@ColumnResult(name="item_name")
	}
)					
					

The use of named parameters is not defined for native queries. Only positional parameter binding for SQL queries may be used by portable applications.

Support for joins is currently limited to single-valued relationships.

Professional hosting     Belorussian informational portal         Free SCWCD 1.4 Study Guide     Free SCDJWS 1.4 Study Guide     SCDJWS 1.4 Quiz     Free IBM Certified Associate Developer Study Guide     IBM Test 000-287. Enterprise Application Development with IBM WebSphere Studio, V5.0 Study Guide     Free Mock Exam Engine