@Query | Spring Data JPA | Spring Boot | Part 1 | Dev2Prod Coding

[Music] hi all welcome to dev to broad coding this is mohit and in this video we are going to talk about at the rate query annotation of spring data gpa with the help of other carrier notation we will be executing both jpql and native sql queries but before that let's talk about jpql and native sql query so jpql stands for java persistent query language defined in jpa specification so jpql is developed based on sql syntax basically so it is used to create queries against entities to store entity related data into our database so we can now directly execute them on db as these are specific to jpa specifications and not to the database right so whereas native sql queries are the sql statements which can be directly executed in database so with that uh let's uh start our coding so in order to demonstrate this at the right query annotation functionality i have created a spring boot project so in this spring boot project i have added spring web dependency spring data jp dependency for auto generation of data and setup methods i will be using lombok dependency and i will be connecting my spring boot application to mysql database so for that i have used mysql connector so to execute our queries i will be using apis so for that i have created a controller employee controller so this employee controller i have annotated with at the rate rest controller so in order to have entity i have declared this entity with that the right entity annotation at the rate data to generate getter setters to string methods and we have constructor no argument constructor so in this employee entity we have couple of properties so one of them is employee id so which will be automatically generated by jpa then we have name age active active to determine if my employee is active or inactive the phone number so this phone number i am generating a random 10 digit phone number then the designation and the salary so again the salary i will be generating uh randomly so this is my repository employee repository which is associated to my employee entity and the data type for my primary key is long so this employee repository extends to jpa repository i have added couple of properties in my application properties to establish a connection to my mysql database so i have already inserted a few records in my database so let's go and check those so this is my spring data jpa db schema so in this you can see we have an employee employee table an employee table we have total five records so we have john robert smith adam and designation and all the related information if we saw the active employees so we have john robert and bob as an active employees whereas smith and adam is non-active so with that let's use at the right query annotation and generate a simple query so very first thing i will be creating an endpoint so i am calling that end point as get employees it will return a list of employees so now let's create a method in a service class now in this service class i have auto wired this employee repository so we will be using this employee repository so before using that let's go to my repository and create a query so the very first thing is at the rate qri annotation in this i will be writing query so let's say i just want to fetch the list of all the employees so this is my jpql query so where i am selecting all the employees from my employee table and one more thing that we have to note here is so whenever you of write query and you use from so this form employee should match with the name of your entity class so here the name of my entity class is employee and i'm using the same in my repository if i by mistake if i change it to employee then um then it won't work all right or else what you can do in the entity you can define the name so for example name you have defined as employee so you can directly use this method uh this table name in our repository as well but it is not recommended it is recommended to use the name of your entity as it is in your repository so for now let's keep it employ let's write a method to it which will return me a list of employee so i am calling it find employees and i'm adding curie to it just to avoid any kind of confusion i'm just writing this method so that we know that this is a custom method that we have created so let's save it and use this method in our service class so directly here from here we will return employee repository dot this done so let's boot up our application and try to access the records via postman so now my application is started let's go to my postman so this is my endpoint that we have just created get employees let's try to fetch the data so here you can see it is returning me all the data that is present in my db so that means our sql is working fine so here in console you can see the executed statement as well now let's see a query with the name parameters so we can also pass method parameters to the query by using name parameters so let's so see uh records you can see i have five records suppose there is a requirement where i want a list of all the manager developer employees whose active state is true so so first let's check the data so here you can see for developers we have total three developers and one manager out of which one developer is non-active so let's run this and see the output now here you can see a list of all the employees within manager and developer whose active state is true so let's try to achieve the same sql in our repository so let me write that here so for name parameters so we have to so here we have to use our notation called param in this param we have to pas we have to map this name parameters make sense now so we have to map this we will be receiving this as a boolean from a service method same for a designation list of designations again at the red param and map this name parameter so i want to receive a list of string set as image list so that's it so here instead of list you can use collection also if you are not sure whether you will be getting list or hashtag so in that case you can use collection as well so for now let's keep it list so now let's go to our controller and let's create an endpoint to receive all these parameters so here let's read this active state and designation as a path variable boolean active state and here designation so we have to pass multiple designations uh so so so in the end point i will be passing a comma separated value so let's have them in a path variable as a list of string so let me rename it to a list designation list okay now let's return it from service so let's create this method and in that method we will pass this active state and designation list so let's create it okay now from here we have to just use our repository method so let's return so let's use this one so this is the one that we have created in our repository just pass in the active state comma list of all the designation that we want in our sql in the controller fine so now the this method is ready in my control and the repository is also set so let's restart my application and test it via postman now my application is restarted so let's go to my postman okay so this is the endpoint that we have created so for example we want active true with manager and developers true manager and developer comma separated values manager and developer so let's try to run this okay so here you can see we got total three records so for employee id 11 12 and 14 so let's go to our db and check so let me run this again so here you can see 11 12 and 14 so that means our query is executing right so instead of true if i make it false false and send so here you can see adam the employee 15 adam with employee id 15 so it's working for inactive employees as well till now we have seen at the red query annotation for simple select query from one of our database table and another with different where conditions using name parameters so in the next video we will be checking how to modify your data using this query annotation and we will see that with the help of native sql so that's it for this video thank you

Channel: Dev2Prod Coding

Views: 5,669

Keywords: Spring Boot, Spring JPA, Spring Data JPA, JPA, Example, @Query

Id: u2yrPDW32eQ

Length: 13min 5sec (785 seconds)

Published: Sun Jul 25 2021