Jump to: navigation, search

Difference between revisions of "EclipseLink/UserGuide/JPA/Basic JPA Development/Querying/Native"

(Native SQL query examples)
(17 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
{{EclipseLink_UserGuide
 
{{EclipseLink_UserGuide
 
|info=y
 
|info=y
|toc=n
+
|toc=y
 
|eclipselink=y
 
|eclipselink=y
 
|eclipselinktype=JPA
 
|eclipselinktype=JPA
 
|api=y
 
|api=y
 
|apis=
 
|apis=
* [http://www.eclipse.org/eclipselink/api/latest/javax/persistence/ResultSetMapping.html ResultSetMapping]
+
* [http://www.eclipse.org/eclipselink/api/latest/javax/persistence/SqlResultSetMapping.html SqlResultSetMapping]
 +
* [http://www.eclipse.org/eclipselink/api/latest/javax/persistence/SqlResultSetMappings.html SqlResultSetMappings]
 +
* [http://www.eclipse.org/eclipselink/api/latest/javax/persistence/EntityResult.html EntityResult]
 +
* [http://www.eclipse.org/eclipselink/api/latest/javax/persistence/FieldResult.html FieldResult]
 +
* [http://www.eclipse.org/eclipselink/api/latest/javax/persistence/ColumnResult.html ColumnResult]
 
|nativeapi=y
 
|nativeapi=y
 
|nativeapis=
 
|nativeapis=
Line 14: Line 18:
  
 
JPA allows SQL to be used for querying entity objects, or data.  SQL queries are not translated, and passed directly to the database.
 
JPA allows SQL to be used for querying entity objects, or data.  SQL queries are not translated, and passed directly to the database.
SQL queries can be used for advanced queries that require database specific syntax, or by users more comfortable in the SQL language versus JPQL or Java.
+
SQL queries can be used for advanced queries that require database specific syntax, or by users who are more comfortable in the SQL language than JPQL or Java.
  
SQL queries are created from the <tt>EntityManager</tt> using the <tt>createNativeQuery</tt> API or via named queries.  A <tt>Query</tt> object is returned and executed the same as any other JPA query.  An SQL query can be created for an entity class, or return an object array of data.  If returning entities, the SQL query must return the correct column names that the entity's mappings expect, or a <tt>ResultSetMapping</tt> can be used.  A <tt>ResultSetMapping</tt> allows for the SQL result set to be mapped to an entity, or set of entities and data.
+
SQL queries are created from the <tt>EntityManager</tt> using the <tt>createNativeQuery</tt> API or via named queries.  A <tt>Query</tt> object is returned and executed the same as any other JPA query.  An SQL query can be created for an entity class, or return an object array of data.  If returning entities, the SQL query must return the correct column names that the entity's mappings expect, or an <tt>SqlResultSetMapping</tt> can be used.  An <tt>SqlResultSetMapping</tt> allows for the SQL result set to be mapped to an entity, or set of entities and data.
  
SQL queries can be to execute SQL or DML, for SQL queries that return results, <tt>getSingleResult</tt> or <tt>getResultList</tt> can be used, for SQL queries that don't return results, <tt>executeUpdate</tt> must be used.  SQL queries can be used to execute database operations and some stored procedures and functions.  Stored prcoedures that return output parameters, or complex stored procedure cannot be execute with SQL queries, but stored procedure queries can be used, see [[EclipseLink/UserGuide/JPA/Basic JPA Development/Querying/StoredProcedures|StoredProcedures]].
+
SQL queries can be used to execute SQL or DML, for SQL queries that return results, <tt>getSingleResult</tt> or <tt>getResultList</tt> can be used, for SQL queries that do not return results, <tt>executeUpdate</tt> must be used.  <tt>executeUpdate</tt> can only be used within a transaction.  SQL queries can be used to execute database operations and some stored procedures and functions.  Stored prcoedures that return output parameters, or certain complex stored procedures, cannot be execute with SQL queries.  EclipseLink supports stored procedures through stored procedure queries, for more information see [[EclipseLink/UserGuide/JPA/Basic JPA Development/Querying/StoredProcedures|Stored Procedures]].
  
 
Query settings and query hints that affect the generated SQL are not supported with SQL queries.
 
Query settings and query hints that affect the generated SQL are not supported with SQL queries.
 
Unsupported query hints include:
 
Unsupported query hints include:
*  
+
* <tt>eclipselink.batch</tt>
 +
* <tt>eclipselink.history.as-of</tt>
 +
* <tt>eclipselink.inheritance.outer-join</tt>
 +
* <tt>eclipselink.sql.hint</tt>
 +
* <tt>eclipselink.join-fetch</tt> - <tt>join-fetch</tt> is supported, but requires that the SQL select all of the joined columns.
 +
* <tt>eclipselink.fetch-group</tt> - <tt>fetch-group</tt> is supported, but requires that the SQL select all of the fetched columns.
 +
* <tt>eclipselink.pessimistic-lock</tt> - <tt>pessimistic-lock</tt> is supported, but requires that the SQL lock the result rows.
  
=== Using JpaEntityManager createQuery() API ===
+
=====''Native SQL query examples''=====
 
<source lang="java">
 
<source lang="java">
import javax.persistence.Query;
+
Query query = em.createNativeQuery("SELECT * FROM EMPLOYEE", Employee.class);
import org.eclipse.persistence.expressions.*;
+
List<Employee> result = query.getResultList();
import org.eclipse.persistence.queries.ReadAllQuery;
+
</source>
  
ExpressionBuilder builder = new ExpressionBuilder();
+
<source lang="java">
ReadAllQuery databaseQuery = new ReadAllQuery(Employee.class, builder);
+
Query query = em.createNativeQuery("SELECT SYSDATE FROM DUAL");
databaseQuery.setSelectionCriteria(builder.get("firstName").like("B%"));
+
Date result = (Date)query.getSingleResult();
databaseQuery.addOrdering(builder.get("firstName").toUpperCase());
+
</source>
  
Query query = ((JpaEntityManager)entityManager.getDelegate()).createQuery(databaseQuery);
+
<source lang="java">
List result = query.getResultList();
+
Query query = em.createNativeQuery("SELECT MAX(SALARY), MIN(SALARY) FROM EMPLOYEE");
 +
List<Object[]> results = query.getResultList();
 +
int max = results.get(0)[0];
 +
int min = results.get(0)[1];
 +
</source>
  
 +
<source lang="java">
 +
Query query = em.createNativeQuery("DELETE FROM EMPLOYEE");
 +
int rowCount = query.executeUpdate();
 
</source>
 
</source>
  
{{EclipseLink_Note
+
==Parameters==
|note=The '''JpaEntityManager''' API was added in EclipseLink 1.1.
+
Parameters to SQL queries are delimited using the <tt>?</tt> character.  Only indexed parameters are supported, named parameters are not supported.
 +
The index can be used in the delimiter, i.e. <tt>?1</tt>. Parameter values are set on the <tt>Query</tt> using the <tt>setParameter</tt> API.
 +
Indexed parameters start at the index <tt>1</tt> not <tt>0</tt>.
  
In EclipseLink 1.0, the JpaQuery method '''setDatabaseQuery()''' could be used.
+
=====''Native SQL query parameter example''=====
}}
+
<source lang="java">
 +
Query query = em.createNativeQuery("SELECT * FROM EMPLOYEE WHERE F_NAME = ? AND L_NAME = ?", Employee.class);
 +
query.setParameter(1, "Bob");
 +
query.setParameter(2, "Smith");
 +
List<Employee> result = query.getResultList();
 +
</source>
  
 +
==Named Native SQL Queries==
 +
Native SQL queries can be defined as named queries in annotations or XML using the <tt>NamedNativeQuery</tt> annotation or <code><named-native-query></code> XML element.  Named native SQL queries are executed the same as any named query.
 +
 +
=====''NamedNativeQuery example''=====
 +
<source lang="java">
 +
@NamedNativeQuery(name="findEmployeesByName", query="SELECT * FROM EMPLOYEE WHERE F_NAME = ? AND L_NAME = ?", resultClass=Employee.class)
 +
@Entity
 +
public class Employee {
 +
  ...
 +
}
 +
</source>
 +
 +
<source lang="java">
 +
Query query = em.createNamedQuery("findEmployeesByName");
 +
query.setParameter(1, "Bob");
 +
query.setParameter(2, "Smith");
 +
List<Employee> result = query.getResultList();
 +
</source>
 +
 +
==SQL Result Set Mapping==
 +
An <tt>SqlResultSetMapping</tt> can be used to map the results of an SQL query to an entity if the result column names do not match what the entity mappings expect.  It can also be used to return multiple entities, or entities and data from a single SQL query.  <tt>EntityResult</tt> and <tt>FieldResult</tt> are used to map the SQL query result column to the entity attribute.  <tt>ColumnResult</tt> can be used to add a data element to the result.
 +
 +
<tt>SqlResultSetMappings</tt> are defined through annotations or XML using the <tt>@SqlResultSetMapping</tt> annotation or <code><sql-result-set-mapping></code> XML element.  They are referenced from native SQL queries by name.
 +
 +
=====''SqlResultSetMapping example''=====
 +
<source lang="java">
 +
@SqlResultSetMapping(
 +
  name="archive-map"
 +
  entities={
 +
    @EntityResult(
 +
      entityClass=Employee.class,
 +
      fields={
 +
        @FieldResult(name="id", column="ARCHIVE_ID"),
 +
        @FieldResult(name="firstName", column="ARCHIVE_F_NAME"),
 +
        @FieldResult(name="lastName", column="ARCHIVE_L_NAME")
 +
  }
 +
)
 +
@NamedNativeQuery(name="findArchivedEmployees", query="SELECT * FROM ARCHIVE_EMPLOYEE", resultSetMapping="archive-map")
 +
@Entity
 +
public class Employee {
 +
  ...
 +
}
 +
</source>
  
 
{{EclipseLink_JPA
 
{{EclipseLink_JPA

Revision as of 08:17, 20 June 2012

EclipseLink JPA

Native SQL Queries

JPA allows SQL to be used for querying entity objects, or data. SQL queries are not translated, and passed directly to the database. SQL queries can be used for advanced queries that require database specific syntax, or by users who are more comfortable in the SQL language than JPQL or Java.

SQL queries are created from the EntityManager using the createNativeQuery API or via named queries. A Query object is returned and executed the same as any other JPA query. An SQL query can be created for an entity class, or return an object array of data. If returning entities, the SQL query must return the correct column names that the entity's mappings expect, or an SqlResultSetMapping can be used. An SqlResultSetMapping allows for the SQL result set to be mapped to an entity, or set of entities and data.

SQL queries can be used to execute SQL or DML, for SQL queries that return results, getSingleResult or getResultList can be used, for SQL queries that do not return results, executeUpdate must be used. executeUpdate can only be used within a transaction. SQL queries can be used to execute database operations and some stored procedures and functions. Stored prcoedures that return output parameters, or certain complex stored procedures, cannot be execute with SQL queries. EclipseLink supports stored procedures through stored procedure queries, for more information see Stored Procedures.

Query settings and query hints that affect the generated SQL are not supported with SQL queries. Unsupported query hints include:

  • eclipselink.batch
  • eclipselink.history.as-of
  • eclipselink.inheritance.outer-join
  • eclipselink.sql.hint
  • eclipselink.join-fetch - join-fetch is supported, but requires that the SQL select all of the joined columns.
  • eclipselink.fetch-group - fetch-group is supported, but requires that the SQL select all of the fetched columns.
  • eclipselink.pessimistic-lock - pessimistic-lock is supported, but requires that the SQL lock the result rows.
Native SQL query examples
Query query = em.createNativeQuery("SELECT * FROM EMPLOYEE", Employee.class);
List<Employee> result = query.getResultList();
Query query = em.createNativeQuery("SELECT SYSDATE FROM DUAL");
Date result = (Date)query.getSingleResult();
Query query = em.createNativeQuery("SELECT MAX(SALARY), MIN(SALARY) FROM EMPLOYEE");
List<Object[]> results = query.getResultList();
int max = results.get(0)[0];
int min = results.get(0)[1];
Query query = em.createNativeQuery("DELETE FROM EMPLOYEE");
int rowCount = query.executeUpdate();

Parameters

Parameters to SQL queries are delimited using the ? character. Only indexed parameters are supported, named parameters are not supported. The index can be used in the delimiter, i.e. ?1. Parameter values are set on the Query using the setParameter API. Indexed parameters start at the index 1 not 0.

Native SQL query parameter example
Query query = em.createNativeQuery("SELECT * FROM EMPLOYEE WHERE F_NAME = ? AND L_NAME = ?", Employee.class);
query.setParameter(1, "Bob");
query.setParameter(2, "Smith");
List<Employee> result = query.getResultList();

Named Native SQL Queries

Native SQL queries can be defined as named queries in annotations or XML using the NamedNativeQuery annotation or <named-native-query> XML element. Named native SQL queries are executed the same as any named query.

NamedNativeQuery example
@NamedNativeQuery(name="findEmployeesByName", query="SELECT * FROM EMPLOYEE WHERE F_NAME = ? AND L_NAME = ?", resultClass=Employee.class)
@Entity
public class Employee {
  ...
}
Query query = em.createNamedQuery("findEmployeesByName");
query.setParameter(1, "Bob");
query.setParameter(2, "Smith");
List<Employee> result = query.getResultList();

SQL Result Set Mapping

An SqlResultSetMapping can be used to map the results of an SQL query to an entity if the result column names do not match what the entity mappings expect. It can also be used to return multiple entities, or entities and data from a single SQL query. EntityResult and FieldResult are used to map the SQL query result column to the entity attribute. ColumnResult can be used to add a data element to the result.

SqlResultSetMappings are defined through annotations or XML using the @SqlResultSetMapping annotation or <sql-result-set-mapping> XML element. They are referenced from native SQL queries by name.

SqlResultSetMapping example
@SqlResultSetMapping(
  name="archive-map"
  entities={
    @EntityResult(
      entityClass=Employee.class,
      fields={
        @FieldResult(name="id", column="ARCHIVE_ID"),
        @FieldResult(name="firstName", column="ARCHIVE_F_NAME"),
        @FieldResult(name="lastName", column="ARCHIVE_L_NAME")
  }
)
@NamedNativeQuery(name="findArchivedEmployees", query="SELECT * FROM ARCHIVE_EMPLOYEE", resultSetMapping="archive-map")
@Entity
public class Employee {
  ...
}

Eclipselink-logo.gif
Version: 2.4 DRAFT
Other versions...