Named Query Or Native Query or Query Which one is better in performance point of view?

Question

Which one is better among following(EJB 3 JPA)

//Query

a). getEntityManager().createQuery("select o from User o");

//Named Query where findAllUser is defined at Entity level

b). getEntityManager().createNamedQuery("User.findAllUser");**

//Native Query

c). getEntityManager().createNativeQuery("SELECT * FROM TBLMUSER ");

Please explain me which approach is better in which case?.

Answer 1

1. createQuery()

   It should be used for dynamic query creation.

   //Example dynamic query
   StringBuilder builder = new StringBuilder("select e from Employee e");
   if (empName != null) {
       builder.append(" where e.name = ?");
   }
   getEntityManager().createQuery(builder.toString());
   

2. createNamedQuery()

   It is like a constant variable which can be reused by name. You should use it in common database calls, such as "find all users", "find by id", etc.

3. createNativeQuery()

   This creates a query that depends completely on the underlying database's SQL scripting language support. It is useful when a complex query is required and the JPQL syntax does not support it.

   However, it can impact your application and require more work, if the underlying database is changed from one to another. An example case would be, if your development environment is in MySQL, and your production environment is using Oracle. Plus, the returned result binding can be complex if there is more than a single result.