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 = persistence.getEntityManager().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];
}

If a single column or aggregate function is selected, the result list will contain these values directly:

Query query = persistence.getEntityManager().createNativeQuery(
        "select count(*) from SEC_USER where login = #login");
query.setParameter("login", "admin");
long count = (long) query.getSingleResult();

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();

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 DBMS in use:

  • HSQLDB – String

  • PostgreSQL – UUID

  • Microsoft SQL Server – String

  • Oracle – String

  • MySQL – String

Parameters of this type should also be passed either as UUID or using their string representation, depending on the DBMS. To ensure that your code does not depend on the DBMS specifics, use DbTypeConverter. It provides methods to convert data between Java objects and JDBC parameters and results.

Native queries support positional and named parameters. Positional parameters are marked in the query text with ? followed by the parameter number starting from 1. Named parameters are marked with the number sign (#). See the examples above.

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.