This is part two second of integrating a spring application with JDBC I have added all the classes now let us go through the classes and then start with the coding part. So in combat boot camp, I have got Spring Boot JDBC application that Java from here only I'm going to call all the methods of good service. So, here I need to add book services instance variable and auto wear it next book calm that book app dot v dot book dot java with what is that default parameters, some set of getter and setter methods and to string then I have got the service layer, main book service, these are the methods This is an interface, I have got the corresponding implementation class also book service I am PL book service I am PL is having book da was an instance variable. So, I need to use a great auto wired up address book service I am PL I need to add on Rate service annotation.

Next, I need to go into book da vo in book da vo I have the same method that is available in group service. This is an interface, I need to have an implementation class. This is the implementation class. This implementation class should be annotated with a great repository, because this is the class which is going to talk to the database. And this class will have JDBC template as the variable for book DIY MPs. So I need to add a direct auto wired annotation.

Okay, now, this JDBC template depends on data source. So I have to go into application dot properties file and I have to give it a value for driver name, URL username and password. So let me open application dot properties which is inside SRC main resources. Go to so here it is spring that day. Source dot driver hyphen class hyphen name similarly string dot data source dot URL username and password let me add the values I have added the properties I have given values for a driver URL username and password we are going to connect to my SQL database. Now I will go to this book dry EMP.

Here only we need to write the coding. I have got JDBC template using JDBC template the method that I need to use is JDBC template dot insert so far says I need to get sorry JDBC template dot update parent the first parameter is a C SQL query. The second parameter is the value that has to be inserted into the database table row. So let me add the query string SQL equal to I'll first add the query okay. The query is ready now I need to assign values to these sigma. So, let me create an object array object book array equal to I need to get the values right.

So, this has been the order of the question mark. So, it is book dot get title comma book dot get book Id let me add that part, I have created the book array which is retrieving the title, book ID category author and price. Remember, this has to be in the same order that you have gotten the book table. So, in my book table I have a title book ID category author and price in the same order only I have created this array. Next, I need to use JDBC template dot update the first parameter is the SQL query the second parameter is the object array. So, let me pass the SQL comma object arrays book correct.

If you see here we have not opened the connection we have not closed the connection We are not using any trycatch all these are taken care of by spring. So, this is what adding the book next let me go in first updating book all these are similar. So, we will do the DML statements first and then we will go in for this retrieving part. So, I will just copy the same code coming in for this update book what to say SQL query in a book set price equal to question mark tidy equal to question mark so, the book array is going to have values for book it and price here directly I can just copy Ctrl C Ctrl V comma price c Ctrl V these things I don't need. So, this is the Eric now again what how to call JDBC template dot update SQL come up with curry This is that similarly, let us do for delete book also.

Let me just copy the same coding control Ctrl V delete from books that book ID equal to question mark. Now what actually that is Wanda only price? Sorry, only book ID. So here I don't need it. Update SQL comma KD Yeah, it's a question mark. One thing there is a one mistake I have made over here is in this case book is the book array will have price first and then book ID second base on the question mark only you are supposed to give the values right.

So I have given it wrong. Now it is done. It means you save this. What is the next part I have to go in for guitar books, how to use guitar books. For guitar books. Again, I need the SQL query string SQL equal to select star from books.

Let me add the SQL first. Now, the query ready, I need to call JDBC template dot query method. The first parameter is the SQL. The second parameter is an interface row mapper. So you need to give an object of the class that implements row mapper instead of creating a separate class we can go in for an anonymous inner class. So let me just do that query.

The first parameter is SQL. Here I need to give new Roma but what actually this type is it is book round brackets curly brace, enter, this ended with a semicolon. What is the return type of JDBC template that's very it is list of books. So let me add that. This is that now within this, I have to override this macro method and are saying the values that I'm getting from the column to the instance variables. Here let me just go over edit, keep the cursor add and implement unimplemented Insert word macro.

So here result set RS argument this I will leave it now it has to be booked I will be creating the book object okay. So how to retrieve the values one by one thing title equal to Rs dot getstring of what is the column one and again you have to retrieve book ID category everything and then create a book object and assign it to the book objects first let me retrieve the values I have retrieved all the values from the column now I need to create a book object. If you want you can do it in a single line only creating the book object setting it by retrieving the value book book is equal to new book of new book then book dot set book ID set category said price although if you need to do so, let me do that part about the value assigned it to the instance variables.

Let me explain this method. Here we are trying to retrieve the data from the table for this query Select star from books here I am trying to use this JDBC template dot query method, the first parameter is t SQL. The second parameter is an anonymous class wherein you are implementing this map row method inside the map row method whatever you used to write in our start, next you have returned you are trying to retrieve the value from the results that are getting getting like that you are retrieving it and then creating a new object assigning it to the instance variables. You can just see the steps what we have done and I am returning a book what actually has happened the first first retrieve and it is assigned to one Java object, the second row is retrieved assigned to second Java object. All these Java objects are then added to the book list.

So, what actually I have to return I have to return the book list. Okay, now I have got good book by category How to use get book by category the same code only the only partners it is going to have one more parameter the search query. So, let me just copy this and then I will modify the code. Now select star from books that category equal to question mark and here object to category. And now I need to pass the second parameter SQL comma search comma neuroma, this is done. So this is fast get booked by category I have got three parameters, SQL search and new row mapper.

There is no change in the spark. That is why instead of adding it in each and every method, what it can do, you can create a separate class which implements this row mapper and foremost object of that class that also is fine and next day who brought gifts By Id get booked by ID what it is taking actually what is the query Select star from books that book ID equal to customer. Let me add the code. And then I will explain just select star from books where book ID equal to question mark, I have got the same search as book ID. Now the method is JDBC template dot query for objects, it is not returning in the status returning just a single object. So, that's what I'm getting new book and again using the same map row method implementing the same map row method returning a book the same book object is returned over here new book okay.

So, this is completed all the methods we have added the details for the data source in the properties file. Now I need to go in for the main class. So in the Spring Boot JDBC application here I need to auto wire book service at the rate auto wired book service. And again the class should implement coming Line runner let me add that and I need to override the method I will override it. Now from within this run method, let us try calling the methods of book service first let me try to call book service dot get all methods see, so, book service dot get all books, it is a list it has to return the list okay. And just keeping my fingers crossed, let us try running this application Spring Boot app Yeah, I got the output I got the output nicely you can just have a look JSP spring all the books whatever I have got is listed over here.

Can you see this? The application has run successfully. You can see that again here also, annotation config application context is getting open and closed. Thank you Let me just go back to the program I will minimize the console here. We will try to insert book also book service dot add book method. name I mean Title One 120 comma category tick comma author Bob come up price 900 Okay now I'm trying to add a book.

So here I need to import book yeah this is done. Let us run this again we will check whether it is added I will go back to this command line Select star from books just know we have added we be added. Okay, so similarly you can try retrieving get one book get book by category also, one more thing, let me just show you how to iterate and retrieve the list of books here. I'm not I'm going to use for each directly I'm going to use the stream spark book service dot get our books dot for each, which takes book as parameter lambda to book that has to be lowercase book. Actually this is just a variable okay. Book service dot get all books dot for each.

Yeah, if we save this, now here this part I will comment it and I will run the application. Okay, one second. I will just come and just add book. That is some years. Let me just check it out. See, let me run the application.

You can see the list of books which is shown over here. Can you see we have used the strings. And let me also show you get one book. Get by ID. I need to pass ID I'm just going to pass 120 The book that we have added save this let me run the application yeah maintaining it yeah here I am getting title as B. Yes Similarly, you can try retrieving get books by category by passing the category that should work fine here the same way also you can do like using streams and lambda.

Let me show you that also category of section. Let me save this now. Let me run it. and maximize console has core function. I have got three books. Okay.

You try using update book and delete book. This should work fine. One more thing. I'll go to this book eBay MPL. Here. We are trying to use what actually a new roadmap we are trying to create an anonymous class, instead of going in for an anonymous class, what it can do.

You can also go in for lamda. How to use lambda. I don't need this new roadmap. Right, I don't need to fill the spot, I need only the parameters. Now I have added it. Now it's giving me an error.

The reason is the same name, that's a category here, I'll just change it to cat. Okay, any other error I don't need these two lines. There's one more error and supposed to close this round bracket. Let me close the round brackets here. Okay, now let me explain this again. JDBC template that's very the first parameter to say SQL query search comma, I was using an anonymous class instead of that I'm going in for lambda.

So I have remote what and I have remote Actually I have remote this macro method name, return type, access specifier. I also removed all these steps. We are just passing the sizzle satara and arguments. You don't even need this result set and in the data type also is not needed. Directly you can use like this. Okay, then hyphens The bracket angle bracket curly brace and this was this part of the lamb sorry this part is the lambda part and I need to close this round bracket which I forgot.

So here I have closed it. Now also this should work fine okay. So, this is good book by category let us check this out I am getting the output perfectly. So, what actually we have learned this how to integrate a spring application to the database using JDBC for creating the spring application, we have gone for Spring Boot because it will make our work easier for adding the dependencies. We never bothered about the dependencies for JDBC, our logger, our my SQL database. We will also learn how to integrate a spring application to the database using hibernate that we will be doing while we are learning is spring MVC.

So this is about spring core and Spring Boot. Going forward you need to create spring applications only using spring boots. Thank you