Jump to: navigation, search

EclipseLink/UserGuide/JPA/Basic JPA Development/Querying/Native

< EclipseLink‎ | UserGuide‎ | JPA‎ | Basic JPA Development‎ | Querying
Revision as of 11:09, 18 June 2012 by James.sutherland.oracle.com (Talk | contribs) (Native SQL Queries)

EclipseLink JPA

Mailing ListForumsIRCmattermost
OpenHelp WantedBug Day
Browse Source

Elug api package icon.png Native API

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. 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 locked 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();
Query query = em.createNativeQuery("DELETE FROM EMPLOYEE");
int rowCount = query.executeUpdate();


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. Indxed parameter 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();


Version: 2.4 DRAFT
Other versions...