Spring Data JPA Native Query Examples

Summary

This video tutorial, presented by Nam Hamming from Code Server Net, provides an in-depth guide on using native SQL queries with Spring Data JPA. The video covers key concepts such as what native queries are, when to use them, and how to implement select and update operations using native queries. It explains the difference between JPQL and native queries, demonstrates how to write native SQL queries for MySQL databases, and showcases testing methods for querying and updating data. Additionally, the tutorial covers pagination and query optimization with native SQL in a Spring Boot application.

Takeaways

• 🔍 Native queries are SQL statements specific to a database, used when JPQL doesn't work or doesn't support the required query syntax.
• 💻 Spring Data JPA supports native queries through the `@Query` annotation, where you must specify `nativeQuery=true` to indicate it's an SQL statement.
• 📊 Native queries use table names directly from the database, not entity names, since the SQL statement targets the database structure.
• 🛠 The example provided demonstrates a native query to select products with prices less than a given value using MySQL.
• 🔄 Pagination can be implemented with native queries by passing a `Pageable` object and appending SQL `LIMIT` clauses for result sets.
• 📋 For update operations, a native query must be annotated with both `@Query` and `@Modifying`, which allows modification of database records like updating prices.
• 🧪 Unit testing for native queries can be done using simple methods that call the repository and verify returned data or modifications.
• 📈 A count query can be specified in a native query annotation for efficient pagination, though Spring Data JPA handles this by default.
• ⚠️ When using `@Modifying` with update queries, ensure correct SQL syntax is followed to avoid errors during execution.
• ✅ Native queries are useful when database-specific features or SQL syntax need to be used, especially for more complex operations like full-text search.

Q & A

What is a native query in Spring Data JPA?

A native query is an SQL statement specific to a particular database. It differs from JPQL (Java Persistence Query Language), which Spring Data JPA uses by default.

When should a native query be used instead of JPQL in Spring Data JPA?

A native query should be used when JPQL does not support the required query syntax. For example, if a database-specific SQL feature or syntax is needed, native queries are used.

How do you declare a native query in Spring Data JPA?

To declare a native query, use the `@Query` annotation with the `nativeQuery=true` attribute. The SQL statement should be passed as the value of the annotation.

What is the key difference between a native query and JPQL?

In native queries, table names from the database are used, while in JPQL, entity names are used. This is because native queries directly use SQL syntax.

How do you pass parameters in a native query?

You can pass parameters using named parameters in the query, such as `:minPrice`, and provide the value of the parameter when calling the method.

What are the steps to write a native query for selecting products with a price less than a specified value?

First, declare a method in the repository interface, annotate it with `@Query`, and write the SQL statement using the table name. Then, use the `nativeQuery=true` attribute and pass the price parameter.

How do you handle pagination in a native query?

To implement pagination in a native query, you can pass a `Pageable` object as a parameter. This allows Spring Data JPA to append the appropriate SQL syntax for pagination (such as `LIMIT` in MySQL).

What additional annotation is needed for executing a native update query?

In addition to `@Query`, the `@Modifying` annotation is required for native update queries in Spring Data JPA.

How do you execute an update query to modify product prices?

You declare a method with an `@Query` annotation, specify the SQL update statement, and use `@Modifying`. Then, the method is called, passing the new price value.

What happens if you don't use the `@Modifying` annotation for an update query?

If the `@Modifying` annotation is not used, Spring Data JPA will throw an error because it expects a select query but finds an update query instead.