4.4.4.5. Running SQL Queries

ORM enables execution of SQL queries returning either the lists of individual fields or entity instances. To do this, create a Query or TypedQuery object by calling one of the EntityManager.createNativeQuery() methods.

If individual columns are selected, 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 specifics, use DbTypeConverter.

If the resulting entity class is passed to EntityManager.createNativeQuery() along with the query text, TypedQuery is returned, and ORM attempts to map the query results to entity attributes. 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 persistence context is similar to that of JPQL queries described above.