Notice: this Wiki will be going read only early in 2024 and edits will no longer be possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.
Difference between revisions of "EclipseLink/UserGuide/JPA/Basic JPA Development/Querying/Native"
(→Native SQL Queries) |
|||
| Line 21: | Line 21: | ||
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 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 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| | + | 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|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-fetch</tt> | + | * <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> - join-fetch is supported, but requires that the SQL select all of the joined columns. | ||
| + | * <tt>eclipselink.fetch-group</tt> - fetch-group is supported, but requires that the SQL select all of the fetched columns. | ||
| + | * <tt>eclipselink.pessimistic-lock</tt> - pessimistic-lock is supported, but requires that the SQL locked the result rows. | ||
| − | ====='Native SQL query examples'===== | + | =====''Native SQL query examples''===== |
<source lang="java"> | <source lang="java"> | ||
| − | Query query = | + | Query query = em.createNativeQuery("SELECT * FROM EMPLOYEE", Employee.class); |
List result = query.getResultList(); | List result = query.getResultList(); | ||
| + | </source> | ||
| + | |||
| + | <source lang="java"> | ||
| + | Query query = em.createNativeQuery("DELETE FROM EMPLOYEE"); | ||
| + | int rowCount = query.executeUpdate(); | ||
</source> | </source> | ||
Revision as of 10:40, 18 June 2012
EclipseLink JPA
| EclipseLink | |
| Website | |
| Download | |
| Community | |
| Mailing List • Forums • IRC • mattermost | |
| Issues | |
| Open • Help Wanted • Bug Day | |
| Contribute | |
| Browse Source |
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 more comfortable in the SQL language versus 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 a ResultSetMapping can be used. A ResultSetMapping 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, getSingleResult or getResultList can be used, for SQL queries that don't 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 complex stored procedure cannot be execute with SQL queries, but stored procedure queries can be used, 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 result = query.getResultList();
Query query = em.createNativeQuery("DELETE FROM EMPLOYEE"); int rowCount = query.executeUpdate();
