Difference Between Query, Native Query, Named Query and Typed Query

Difference between query, native query, named query and typed query

Query

Query refers to JPQL/HQL query with syntax similar to SQL generally used to execute DML statements(CRUD operations).

In JPA, you can create a query using entityManager.createQuery(). You can look into API for more detail.

In Hibernate, you use session.createQuery()"

NativeQuery

Native query refers to actual sql queries (referring to actual database objects). These queries are the sql statements which can be directly executed in database using a database client.

JPA : entityManager.createNativeQuery()
Hibernate (Non-JPA implementation): session.createSQLQuery()

NamedQuery

Similar to how the constant is defined. NamedQuery is the way you define your query by giving it a name. You could define this in mapping file in hibernate or also using annotations at entity level.

TypedQuery

TypedQuery gives you an option to mention the type of entity when you create a query and therefore any operation thereafter does not need an explicit cast to the intended type. Whereas the normal Query API does not return the exact type of Object you expect and you need to cast.

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

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.

Performance of native sql query and JPA

The effective SQL executed on the DB should be the same. So you should exactly watch out for what you measure and what you want to use the result for.

JPA is designed for updating and storing object oriented data models into relational data structures, so you don't have to implement the mapping of Collections and relations manually.

Therefore the result of your JPA query is managed in the EntityManager and changes to your results can easily be stored back. Where the native query is just a select with a projection into a result Object, which is not managed by your EntityManager. So you remove the EntityManager overhead with the native query, you don't have optimized SQL.

So the first question is not "what is faster?", but "what is your usecase?". If you want to display read only results, you can go with the native query and save some overhead, which reduces execution time over all. If you want to store changes to your results back to the DB, you should go with the JPA query and you might also want to measure the store operation.

Depending on the usecase, it is also a valid option to go for the simpler code.

Native query with named parameter fails with Not all named parameters have been set

Named parameters are not supported by JPA in native queries, only for JPQL. You must use positional parameters.

Named parameters follow the rules for identifiers defined in Section 4.4.1. The use of named parameters applies to the Java Persistence query language, and is not defined for native queries. Only positional parameter binding may be portably used for native queries.

So, use this

Query q = em.createNativeQuery("SELECT count(*) FROM mytable where username = ?1");
q.setParameter(1, "test");

While JPA specification doesn't support named parameters in native queries, some JPA implementations (like Hibernate) may support it

Native SQL queries support positional as well as named parameters

However, this couples your application to specific JPA implementation, and thus makes it unportable.

Difference between @NamedQuery and @NamedNativeQuery in JPA-EclipseLink

A native query isn't JPQL. A regular query is converted by the persistence provider into something the underlying persistence system understands (such as SQL SELECT, or a NoSQL retrieval). A native query, while not portable, is written directly in the native language of the store so that you can use special features (like PostgreSQL's IP address manipulation) at the cost of portability.

How to pass parameters in a native query JPA

There are two ways around that when you use Spring Data JPA

1) Named Parameters

public interface CarRepository extends JpaRepository<TRace, String> {
        @Query(nativeQuery = true,
               value = "select *" +
                       "from car_records" +
                        "where carVinNo = :vinNo and carSerialNo >= :serialNo")
     }
     List<Car> retrieveCars(@Param("vinNo") Long vinNo,@Param("serialNo") Long serialNo); 
    }

spring doc for named parameters

2) Indexed Parameters

public interface CarRepository extends JpaRepository<TRace, String> {
    @Query(nativeQuery = true,
           value = "select *" +
                   "from car_records" +
                    "where carVinNo = ?1 and carSerialNo >= ?2")
 }
 List<Car> retrieveCars(Long vinNo, Long serialNo); 
}

example for index parameter from spring doc

Then from your service class you call it

carRepository.retrieveCars(vinNo, serialNo);

Both cases will work the same for you.

---

Related Topics