In this tutorial, you will learn how to run an SQL query in your project which uses Spring Data JPA Query Methods. You will learn how to annotate Spring Data JPA Query method so that you can use it to perform a custom SQL query to SELECT, UPDATE or DELETE a record from a database.

Apr 01, 2021 HQL to SQL converter. Convert sql query to postgresql online. Online SQL Conversion – SQL Scripts, DDL, Queries, support@sqlines.com / Download Tool, Target: Microsoft SQL Server, Oracle, IBM DB2, Hive, MySQL, MariaDB, PostgreSQL, Redshift, Sybase, Informix java -jar client-0.0.5.jar convert –output-format=postgresql file.sql output-dir. Source: www.sqlines.com - Download tools Target: Microsoft SQL Server Oracle IBM DB2 Hive MySQL MariaDB PostgreSQL Presto Redshift Snowflake Spark SQL Sybase Informix Teradata Greenplum Netezza Vertica.

To learn how to write a Native SQL Query with Spring Data JPA read this tutorial: Spring Data JPA Native SQL Query.

JPQL Query

I will begin with an example that uses JPQL to run an SQL query to select all users who have verified their email address. So my SQL query will look like this:

we could also write this query this way:

IMPORTANT:

In the above query:

UserEntity – is the name of the Entity class which you can find below in this tutorial. When we create SQL queries with JPQL, we query not the database table by its name but by an Entity class name. This is a very important difference between JPQL SQL Queries and the Native SQL queries. When creating a Native SQL query we would use a database name in the query but in the JPQL, we need to use an Entity class name and not the database table name.

emailVerificationStatus – is the name of the UserEntity class property. Joss cue serial number lookup number. If you look at the code of the below UserEntity class, you will see that it has an emailVerificationStatus property name of a String data type.

Since the entity class name is UserEntity my SQL query looks like the one above.

Below is the UserEntity @Entity class:

User Entity Class

once again, because this entity class is called UserEntity and because we use JPQL and not Native Queries to select all records from a database table of type UserEntity we will need to write our SQL query this way:

JPA Repository Interface

Because I am selecting all records from a database table and because there might be thousands of them, I am going to use Paginations and thus my initial JPA Repository will look like this:

the main difference between the regular CRUDRepositoty and the one I used above is that in the above repository I make my interface extend the PagingAndSortingRepository. If you do not need to support pagination, then you can use a regular CRUDRepository and JPQL will work.

JPQL Query to Select a Single Entity

Let’s first have a look at a very simple JPQL Query which selects only one record from our database which matches the value of user id we pass in as a named parameter.

A bit later in this tutorial, you will also find an example that selects all records from a database table with support for pagination.

Remember that when writing JPQL queries we use Entity name and Entity class fields instead of a database table name and table column names.

as you can see in the query, I am referencing a UserEntity rather than users database table name and I also give a UserEntity an alias u which is then needed to be used in all other parts of a query.

and here is a unit test to test this query.

JPQL SELECT Query with Pagination

Let’s add a Query method to select all users and add support for a pagination.

To perform JPQL SQL query I will need to:

Annotate my Spring Data JPA Query Method with a @Query annotation,
In the SQL query itself, I need to SELECT FROM <Entity Class Name>,
In the WHERE clause, I need to use <Entity Class Property Field Name>. Have a look at the above UserEntity class. It has a property field called “emailVerificationStatus” of a String data type.
The countQuery inside of @Query annotation is used only because the above example uses Pagination. For use cases that do not need to use Pageable request and Pagination, no need to provide the countQuery.

And this is it. To test the above code you can use the below JUnit test case:

Run JUnit Test Case to See How It Works

I hope this tutorial was helpful to you. To learn more about building RESTful Web Services that use Spring Data JPA please check my other tutorials following this link: RESTful Web Services with Spring MVC

And if you are interested in video lessons that teach Spring Data JPA and JPQL have a look at the below list of video courses:

JPQL Query

User Entity Class

JPA Repository Interface

JPQL Query to Select a Single Entity

JPQL SELECT Query with Pagination

Run JUnit Test Case to See How It Works

Sql To Jpql Converter Tool Mac

Related Posts:

Sql To Jpql Converter Tool Download