Spring Data JPA Native Query Examples

Code Java

18 Feb 202216:39

Summary

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

Outlines

00:00

👋 Introduction to Native Queries with Spring Data JPA

In this opening segment, the presenter introduces the video, which focuses on using native queries with Spring Data JPA. It explains what a native query is, distinguishing it from JPQL (Java Persistence Query Language) by its specificity to certain databases like MySQL. The video intends to teach when and how to use native queries, especially in scenarios where JPQL falls short. The presenter also sets the agenda for the video, promising examples of select and update native queries, and usage of native queries for pagination.

05:03

🛠️ Example of Writing a Select Native Query

Here, the presenter walks through coding a native query for selecting products from a MySQL database. The example uses a Spring Boot project with a 'Product' entity mapped to a database table. A native SQL query is demonstrated, selecting products priced less than $1000 using a custom method in a repository interface. The method is annotated with `@Query`, and the SQL uses the table name rather than the entity name. After writing the query, the presenter runs a test to confirm it successfully returns only products under the specified price.

10:06

✅ Testing the Select Native Query with Max Price

This section focuses on testing the native query created in the previous paragraph. The presenter writes and runs a test case where the maximum price is set to 1000 USD, retrieving products that fall under this price range. The test confirms the correct execution of the query, showing the products below $1000 and excluding those above. The presenter highlights the importance of using native queries for database-specific functionality, such as MySQL's syntax, which is not supported by JPQL.

15:08

🔍 Using Pagination with Native Queries

This paragraph introduces pagination with native queries. The presenter explains how to implement pagination using the `Pageable` object in Spring Data JPA, creating requests that define the page number and size. They demonstrate how to retrieve the first set of products from the query and change parameters to display products from subsequent pages. The paragraph also covers using `@Query` with pagination and the `countQuery` to handle large datasets efficiently. The tests confirm that pagination works by showing a different set of results per page.

📝 Implementing Native Update Queries

This segment demonstrates how to write a native update query. The presenter codes a query to increase the price of all products in the database by a certain amount. The query is annotated with `@Modifying` since it involves modifying data. After coding, a unit test is written to confirm the successful execution of the update, showing that all product prices are updated in the database. The example highlights the difference between update queries and select queries, emphasizing the need for additional annotations.

📈 Conclusion: Mastering Native Queries with Spring Data JPA

The final segment recaps the key points of the video. It reviews what native queries are, when they should be used, and the benefits they offer, especially for database-specific features that JPQL doesn’t support. The presenter highlights the examples covered, including the select query, update query, and pagination. The video ends with a call to action for viewers to subscribe, like, and share the content.

Mindmap

Keywords

💡Native Query

A native query refers to an SQL statement that is specific to a particular database. In this video, it is contrasted with JPQL, the default query language in Spring Data JPA. Native queries are used when JPQL cannot handle specific database syntax, as seen in the example where a native query is written for MySQL to select products priced under 1000 USD.

💡JPQL (Java Persistence Query Language)

JPQL is a query language used in JPA (Java Persistence API) for accessing and manipulating data in a relational database. It is the default query language in Spring Data JPA. The video explains that while JPQL works in most cases, native queries are used when JPQL does not support a specific database syntax.

💡Spring Data JPA

Spring Data JPA is a part of the Spring Framework that provides an abstraction over JPA, making it easier to interact with relational databases. The video demonstrates how to use Spring Data JPA for native queries, showcasing examples like selecting products with specific conditions using custom native queries.

💡Repository Interface

A repository interface in Spring Data JPA is used to define methods for database operations. The video shows a repository interface extending JpaRepository, which allows custom queries, including native queries, to be written for selecting and updating data in a database.

💡Annotation

Annotations in Spring Data JPA are used to define the behavior of certain methods or entities. The video explains the use of the @Query annotation to define a native query, and the @Modifying annotation to handle update operations in the database.

💡Pagination

Pagination refers to dividing large sets of data into smaller pages for efficient handling and display. In the video, pagination is demonstrated using a Pageable object in a native query to limit the number of products retrieved from the database, such as showing only five items per page.

💡Entity

An entity is a class in JPA that maps to a database table. In the video, the 'Product' entity class is discussed, showing how it maps to the 'products' table in a MySQL database. The native query uses the table name in the database instead of the entity name.

💡Update Query

An update query is used to modify existing data in a database. The video demonstrates writing a native update query in Spring Data JPA to increase the price of all products by a specific amount. The @Modifying annotation is necessary for this type of operation.

💡SQL Statement

An SQL statement is a query used to interact with a database, written in SQL (Structured Query Language). The video illustrates the use of SQL statements within native queries in Spring Data JPA, showing how SQL syntax differs from JPQL, particularly when selecting or updating records.

💡Product Table

The product table refers to the table in the MySQL database that stores product information, such as price. The video uses this table in examples of native queries, showing how to select products based on price conditions and how to update product prices using native SQL.

Highlights

Introduction to native queries in Spring Data JPA, explaining when and why to use them.

Explanation that a native query is an SQL statement specific to a particular database, like MySQL, and differs from JPQL.

Native queries should be used when JPQL does not support a specific query syntax, necessitating the use of SQL.

Demonstrating how to code a native query to select products with a price less than a specified value in MySQL.

Important distinction that in native queries, table names from the database must be used, not entity names.

Explanation of how to write a custom native query using the @Query annotation in Spring Data JPA.

Showcasing how to test native queries with Spring Data JPA using JUnit, including the example of selecting products priced below $1000.

Demonstrating how pagination can be implemented with native queries, using the Pageable object in Spring Data JPA.

Explaining the use of named parameters in native queries and how they are substituted with actual values at runtime.

Highlighting the process of testing pagination with native queries, showing products on different pages.

Explanation of how to use count queries with native SQL for pagination in Spring Data JPA.

Walkthrough of creating and executing a native update query to increase product prices in the database.

Explanation of using the @Modifying annotation in Spring Data JPA when executing update or delete native queries.

Common pitfalls discussed, such as the need for the @Modifying annotation when running an update query.

Demonstration of successfully updating product prices using a native query, including checking the database for updated records.