Java Named Query or Native Query or Query 从性能的角度来看，哪个更好？

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

提问by Jekin Kalariya

Which one is better among following(EJB 3 JPA)

以下哪个更好（EJB 3 JPA）

//Query

//询问

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

一种）。getEntityManager().createQuery("select o from User o");

//Named Query where findAllUser is defined at Entity level

//命名查询，其中 findAllUser 在实体级别定义

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

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

//Native Query

//本机查询

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

C）。getEntityManager().createNativeQuery("SELECT * FROM TBLMUSER ");

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

请向我解释在哪种情况下哪种方法更好？

采纳答案by Wundwin Born

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.

1. 创建查询（）

   它应该用于动态查询创建。

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

   它就像一个可以通过名称重用的常量变量。您应该在常见的数据库调用中使用它，例如“查找所有用户”、“按 id 查找”等。

3. createNativeQuery()

   这将创建一个完全依赖于底层数据库的 SQL 脚本语言支持的查询。当需要复杂查询而 JPQL 语法不支持时，它很有用。

   但是，如果底层数据库从一个更改为另一个，它会影响您的应用程序并需要更多的工作。一个示例是，如果您的开发环境使用 MySQL，而您的生产环境使用 Oracle。此外，如果有多个结果，则返回的结果绑定可能会很复杂。

回答by Antoniossss

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)

命名查询与查询相同。它们被命名只是为了让它们可以重用+它们可以在不同的地方声明，例如。在类映射、conf 文件等中（因此您可以在不更改实际代码的情况下更改查询）

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.

本机查询只是本机查询，您必须执行 JPA 查询为您做的所有事情，例如。绑定和引用值等 + 他们使用 DBMP 独立语法（在您的情况下为 JPQL），因此更改数据库系统（让 saq 从 MySQL 到 Postgresql 或 H2）将需要较少的工作，因为它并不（并非总是）需要重写本机查询。

回答by NoDataFound

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.

对我来说，显然前两个更好，即 JPQL 查询 - 第二个意味着实体管理器将在加载持久性单元时编译查询（并验证它们），而第一个只会在执行时产生错误。

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

您还可以在某些 IDE 中获得支持，它支持对象表示法（例如select b from EntityA a left join a.entityB b：）和对象关系映射（如集合、索引等）引入的其他一些奇怪的东西。

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)

另一方面，在 JPQL 的极端情况下，最后使用 Native 查询（如窗口函数，例如select id, partition by (group_id) from table）

回答by Kirtan Patel

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.

本机 SQL 不一定比 Hibernate/JPA 查询更快。Hibernate/JPA Query 最终也被翻译成 SQL。在某些情况下，它可能会发生 Hibernate/JPA 不会生成最有效的语句，因此原生 SQL 可以更快 - 但是使用原生 SQL，您的应用程序会失去从一个数据库到另一个数据库的可移植性，因此通常最好调整 Hibernate/JPA查询映射和 HQL 语句生成更高效的 SQL 语句。另一方面，使用本机 SQL，您缺少 Hibernate 缓存 - 因此在某些情况下本机 SQL 可能比 Hibernate/JPA 查询慢。

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.

我不关心性能，在大多数情况下，如果您加载所有列或仅加载所需的列，性能无关紧要。在数据库访问中，搜索行时会丢失时间，而不是将数据传输到应用程序时。当您只阅读必要的列时。