Named Query Or Native Query or Query Which one is

问题:

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?.

回答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 constant variable and reusable, and you should use it in common db calls like; find all users, find by id, etc.

3. createNativeQuery()

   It totally depends on underlying database supported sql script.

   It is useful when a complex query is required and JPQL syntax not supported it.

   But it can impact your application and need more work, if the underlying database is changed from one to another. Example case like, if your development env is using MySQL, and your production env is using Oracle. + And returned result binding can be complex if more than single result.

回答2:

Native SQL is not necessarily faster than Hibernate/JPA Query. Hibernate/JPA Query finally also is translated into SQL. In some cases it can happen Hibernate/JPA does not generate the most efficient statements, so then native SQL can be faster - but with native SQL your application loses the portability from one database to another, so normally is better to tune the Hibernate/JPA Query mapping and the HQL statement to generate more efficient SQL statements. On the other side with native SQL you're missing the Hibernate cache - as a consequence in some cases native SQL can be slower than Hibernate/JPA Query.

I am not with performance, in most cases for the performance it is irrelevant if your load all columns or only the needed columns. In database access the time is lost when searching the row, and not when transferring the data into your application. When you read only the necessary columns.

回答3:

Named queries are the same as queries. They are named only to let them be reusable + they can be declared in various places eg. in class mappings, conf files etc(so you can change query without changing actaul code)

Native queries are just native queries right, you have to do all the things that JPA Queries do for you eg. Binding and quoting values etc. + they use DBMP independent syntax (JPQL in your case) so changing database system (lets saq from MySQL to Postgresql or H2) will require less work as it does not (not always) require to rewrite native queries.

回答4:

For me, the better is obviously the first two one, that is JPQL Queries - the second meaning the entity manager will compile the queries (and validate them) while loading the persistence unit, while the first would only yield errors at execution time.

You can also get support in some IDE, and it support the object notation (eg: select b from EntityA a left join a.entityB b) and some other oddities introduced by the object-relational mapping (like collections, index, etc).

On the other hand, use Native queries in last resort in corner case of JPQL (like window function, such as select id, partition by (group_id) from table)