ORM allows running SQL queries to the database returning either the lists of individual fields or entity instances. For this,
it is necessary to create a Query or TypedQuery object by calling one of the methods of EntityManager.createNativeQuery().
If individual columns are selected within a table, the resulting list will include the rows as Object[]. For example:
Query query = em.createNativeQuery("select ID, NAME from SALES_CUSTOMER where NAME like ?1");
query.setParameter(1, "%Company%");
List list = query.getResultList();
for (Iterator it = list.iterator(); it.hasNext(); ) {
Object[] row = (Object[]) it.next();
UUID id = (UUID) row[0];
String name = (String) row[1];
}
Keep in mind when using SQL that the columns corresponding to entity attributes of UUID type are returned as UUID or as String, depending on the used DBMS and JDBC driver:
-
HSQLDB –
String -
PostgreSQL, driver
postgresql-8.3-603.jdbc4.jar–String -
PostgreSQL, driver
postgresql-9.1-901.jdbc4.jar–UUID -
Microsoft SQL Server, driver
jtds-1.2.4.jar–String -
Oracle –
String
Parameters of this type should also be defined either as UUID or using their string representation, depending on the DBMS
and JDBC driver. To ensure that your code does not depend on the DBMS used, it is recommended to use DbTypeConverter.
If the resulting entity class is passed along with the query text, TypedQuery is returned, and the attempt to map the query results to entity attributes is performed. For example:
TypedQuery<Customer> query = em.createNativeQuery(
"select * from SALES_CUSTOMER where NAME like ?1",
Customer.class);
query.setParameter(1, "%Company%");
List<Customer> list = query.getResultList();
Behavior of SQL queries returning entities and modifying queries (update, delete), in relation to the current persistent context is similar to that of JPQL queries described above.
