Spring Data JPA Native Query Examples
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
👋 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.
🛠️ 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.
✅ 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.
🔍 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
💡JPQL (Java Persistence Query Language)
💡Spring Data JPA
💡Repository Interface
💡Annotation
💡Pagination
💡Entity
💡Update Query
💡SQL Statement
💡Product Table
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.
Transcripts
hi welcome to the video spring data jpi
native query examples with me nam
hamming from code server net
in this video you will learn what is
native query when using native query
and how to use native queries with
spring data api i'll show you some core
examples about select native query
update native query and how to use
imagination with native query
so what is native query you know a
native query is the sql statement that
is specific to a particular database
for example mysql
and a native query syntax default
slightly from jpql or java persistence
query language which is used by spring
data jba by default
and generally we should use native query
when jpql does not work in other words
the query is syntax not supported by
jpql then we need to use native query
now i'd like to show you how to code a
native query for select operation
okay you see this here springboard
project you can see
i'm using
subbrain data jpi
and
mysql
jdbc driver
and in this project i have the product
entity class that maps to the products
table in the database
and you can see in the database my sql
i have the data in the product table
like this you see
okay and you see a
standard
repository interface
following
jpi you see
it extends the jpa or repository
interface
and also i have a retest class for
testing the product or repository
interface you see
now i want to
write a native query that selects only
products
with price
less than 1000 usd
to do so i update the product repository
interface here
i
declare new method public list
list collection
list of product
read
products
with
minimum price
minimum
and in this method i
specify the
parameter is the minimum price
i use
integer
mean price
and to write a
custom relative query use the annotation
query
specify the query in the value attribute
resist
and you also need to specify the
attribute native query equal to
to trial spring that is api that
this query is native it is not jpql
and now
we should try a
sql statement
select and ask the list
select all
from the
products table know that this is sql
statement so i use the table name
which products instead of entity name
product
so
with native query
we should use the
table name in the database
it is not the entity name because the
query is native
where
price
greater than sorry
whereby less than the
argument
here so i can use name parameter
mean price to select only products
having the price
less than a specified amount
sorry i changed the method to
max price
max price is
here okay
and then i update the test class to test
the
method
that products with masterize
it is using a native query as you can
see
okay i
write another test method
test
public void test
red
products
max price
and i
specify
value for the max
price is 1000 usd
and call repo
calls a method
annotated with the native query
products with mass
price and it returns the list
products
list products
and i bring each product in the return
collection
list products
for each and i use
laminar expression
okay to bring each product object in the
list collection
okay now
run this test method
run next on the unit test
you see the test is successful and it
prints the
product host price less than 1000 usd
you see
you see in the database we have some
products always priced greater than 1000
usd yeah
and
they are not included in the result here
okay
so that's a very
simple example
about writing
and executing a native query with
supporting data jpi you need to use the
query annotation and specify the native
query
sql statement in the value attribute and
know that you must specifies attribute
native query equal to
and in the native query you can also
specify name parameter like this
and this is a perfect example
of using a native query
you see
the search method is annotated
with the query annotation and this year
native query for full text search
it is
specific to
my sql database because the syntax match
field names and awareness here
is
specific to mysql database only
that's why
we need to use a native query and
specify the attribute native query equal
to here
and you can see that
here i used
name
sorry indexed parameter
the first parameter here uh so bring
that data api we replaced the actual
value of the first
parameter in the method here keyword
okay and you can also see i
used bassinetion by passing the second
parameter of thai
pressure ball here
and you see
this is a service class
where the search method is used here you
see
it creates a principle object
specifies the best number as a
number of results per best or number of
items per page engine core repo dot
search here
passing the values of keyword and
preservable object
now let me start this springboard
application
to test the search query
i want to print the sql statement
social
here okay
okay the application
has started
and let me access is in chrome browser
okay
show me
and and the keyword to search for
example iphone
you see the search result appears here
and another keyword oh
let me check the console
let me search for the query
match
a rinsed
okay you see this is a native query
you see select asterisk from products
where enable you could choose and match
against
and you can see it appends the limit
class
for pagination you see
now let me type another keyword
foreign
now let's come back to this example and
let me show you how to use uh
presentation for this native query
so just
specify the second parameter of thai
pleasurable or like this
measurable from
this package
measurable
and update the code for testing here
before calling the method i need to
create a new measurable object like this
measurable equal based request
and specifies the page number and
the page size
base number
zero indicating the first best and the
number of items purposed is five
and passing the principal object in the
method call here
variable
okay
and
run the test
okay you see now it prints only the
first best that contains five products
you see
this is a first patch
and i can change the
parameter
argument here
number one to
show the items on the second page
and run the test again
and you see the products in the second
press
and now that you can
specify the cal query which is used for
the presentation in the annotation here
for example uh specify the cal query
query here which is used
for the
impersonation
but by default you don't need to specify
the card query and
bring it as api that's the
pagination
under the scenes
okay
so this is account query
and run the test again
you see the same result
next i'd like to show you how to
code and execute a native update query
okay so i want to
code queries that updates the price of
the products here
so i declare a new method here public
void update
price
with a parameter
integer
it is a
amount
and i used a
query
use a query annotation
and specify the native query in the
gb value
and specify
that it is the native query equal to
and this is a update statement update
products set
price
i want to increase the
price my z amount
amount
okay you see
and
right unit test here
test update price
in this amount i want to increase
the price of
each product by
two
okay and call repo
update price passing the
amount number and run the test
of course this is a
very simple example
you can do that
with
jpql okay we've got an error here
you see
is because for the update query
we need to use
an additional annotation
it is the
modifying annotation
for the method and run the test again
i think something wrong in the query
it should be
myself
okay and run the test again
okay so therefore you see it prints the
update sql statement here
and let me check the database
you see all products
price have been updated
okay so that's
some examples
of
coding using executing a native query
with a spring data jpi
okay so far you have learned what native
query is when using a deep query and see
some good examples using native query
with spring itash api i hope you found
this video helpful
please subscribe to my channel like
comment and share this video thanks for
watching
関連動画をさらに表示
SQL 十四分鐘速成班!沒錯不要懷疑,資料庫語法比中午決定吃什麼還要簡單!
Dream Report: Acquiring Data from SQL Server
Dream Report: Acquiring Data using SQL Statements
How To Connect to PostgreSQL Database in Visual Studio Code and Run SQL Queries (2024)
Membuat Dashboard di Google Sheet | Belajar Fungsi Query | Indonesia
🔥Mongodb Aggregation Tutorial | Aggregation Functions in Mongodb | Mongodb Tutorial | Simplicode
5.0 / 5 (0 votes)