Difference Between Named Query and Named Native Query

by

In JPA (Java Persistence API), the difference between Named Query and Named Native Query annotations (@NamedQuery and @NamedNativeQuery). These annotations allow us to define queries directly in the JPA Entity class and execute them by name. However, they differ in syntax, use cases, and how they interact with the database. These differences between Named Query and Named Native Query help us select the right annotation among these two based on the application’s requirements.

This topic explains the difference between Named Query and Named Native Query, with practical examples and best practices.

Difference Between Named Query and Named Native Query

What is the Named Query in JPA?

A Named Query(@NamedQuery) annotation allows us to define a JPQL query directly in the JPA Entity class.

Key Features of Named Query

  • Written in JPQL, making it portable across databases.
  • Operates on entity attributes.
  • Validated at application startup.
  • Automatically maps results to entities.

Example of @NamedQuery

@NamedQuery(
name = "findOrdersByStatus",
query = "SELECT o FROM Orders o WHERE status = :status"
)

Exexution of @NamedQuery

Query query = entityManager.createNamedQuery("findByStatus");
query.setParameter("status", "PENDING");
List<Order> orders = query.getResultList();

What is a Named Native Query in JPA?

A Named Native Query(@NamedNativeQuery) annotation allows us to write raw SQL queries directly. It bypasses JPQL and interacts with database tables, columns, and features directly.

Key Features of Named Native Query

  • Written in raw SQL, making it database-specific.
  • Operates on database tables and columns.
  • Validated at runtime when executed.
  • Can map results to entities or custom DTOs.

Example of @NamedNativeQuery

@NamedNativeQuery(
name = "findOrdersByStatusNative",
query = "SELECT * FROM orders WHERE status = :status",
resultClass = Order.class
)

Execution of @NamedNativeQuery

Query query = entityManager.createNamedQuery("findOrdersByStatusNative");
query.setParameter("status", "PENDING");
List<Order> orders = query.getResultList();

Key Difference Between Named Query and Named Native Query

Feature      Named Query            Named Native Query
SyntaxWritten in JPQL, uses entity attributes.Written in raw SQL, uses database-specific syntax.
PortabilityPortable across multiple databases.  Database-specific, not portable.
ValidationValidated at application startup.Validated at runtime during execution.
Result MappingAutomatically maps results to entities.Requires explicit result mapping for entities or DTOs.
Database FeaturesLimited to JPQL capabilities (e.g., no native functions).Can leverage all database-specific features and functions.
PerformanceMay have overhead due to JPQL-to-SQL translation.Directly executed in the database for better performance.
FlexibilityBest for simple, portable queries.Ideal for complex, performance-critical queries.

When to Use Named Query?

  • Portability: When the application needs to support multiple databases.
  • Simple Queries: For straightforward queries that align with JPQL capabilities.
  • Entity-Based Operations: When working primarily with entity attributes.
  • Ease of Validation: When you want queries validated at application startup.

Example Use Case for Named Query

Fetching all orders with a specific status:

@NamedQuery(
name = "Order.findByStatus",
query = "SELECT o FROM Order o WHERE o.status = :status"
)

When to Use Named Native Query?

  • Complex Queries: For advanced queries involving joins, aggregations, or subqueries.
  • Database-Specific Features: When leveraging database-specific functions (e.g., LIMIT, ROWNUM).
  • Performance Optimization: When optimizing queries for a specific database.
  • Existing SQL: When using pre-written SQL queries from legacy systems.

Example Use Case for Named Native Query

Fetching the top 5 most expensive orders:

@NamedNativeQuery(
name = "Order.findTop5ExpensiveOrders",
query = "SELECT * FROM orders ORDER BY price DESC LIMIT 5",
resultClass = Order.class
)

Practical Comparison

Use CaseNamed QueryNamed Native Query
Fetching all orders by statusSimple JPQL query based on entity attributes.Complex SQL query using database-specific syntax.
Fetching top 5 ordersCannot use JPQL for LIMIT.SQL query with ORDER BY and LIMIT.
Using database-specific functionsNot possible, as JPQL does not support them.Possible, as raw SQL directly interacts with the database.

Best Practices

For Named Queries:

  • Use descriptive names (e.g., findByStatus).
  • Test queries against different databases for portability.
  • Keep queries simple to align with JPQL’s capabilities.

For Named Native Queries:

  • Use parameters (:param) to prevent SQL injection.
  • Optimize queries for the target database.
  • Use explicit result mapping for complex queries or custom DTOs.

Conclusion

Named Query and Named Native Query serve different purposes in JPA. 

  • Use Named Query for portability, simplicity, and when working with entity attributes. 
  • Opt for Named Native Query when dealing with complex, database-specific requirements or performance-critical operations.

Leave a Comment