Spring Boot JDBC using JdbcTemplate

welcome back aliens my name is EV ready and in this video we'll talk about spring boot jdbc the thing is if you want to connect your application with database in Java you have to use something called jdbc which stands for Java database connectivity and you will say we know that right so basically you can use jdbc few steps and then you can connect your application with database easily but the problem is as a developer you want to reduce your work right and also you have to make make sure that the application is optimized so let's talk about the optimization part here see the thing is every time you send a request from your application to database it creates a new request and then if you have multiple connections imagine you already have few connections and now you are submitting a new request now you do have this connections right can't we just reuse the same connection instead of getting a new one now that's something you have to manually do or you can use something called a data data source here now data source will have a configuration and also it provides you something called a connection pooling so basically it will already have few connections and you can use those connections and again you can do this by using any external uh data source or connection pulling concept and you can make it work so that's one thing the second is about developers work so you have to write those beautiful seven or six steps to achieve jdbc and you can do that but the only problem is the more code you write you are basically increasing the number of lines which will have a bug so you know every new line is a potential bug right so reduce the number of lines which you write and use certain tools or Frameworks which will do it for you and these Frameworks and tools are been tested by everyone so if it works it works for you as well now that's where we have to use a template now there's something called jdbc template now jdbc template will help you with data source that means the connection pulling concept will be done and it will also help you to write your your code in an efficient way and all these things are provided in Spring boot jdbc now why spring boot is because if you want to build Enterprise application and if you want to follow all the design patterns best practices and a framework where you can get the libraries and everything working together spring is one of the best framework and that's why we're going to use spring boot jdbc is because under the spring umbrella we have lot of projects and this is one of it so let's get started with the code where we're going to talk about how do you build an application and we'll learn by building something right so it will make much more sense so let's start the code the thing is if you want to build this project you need a ID now you can choose any ID which you want maybe you can use Eclipse intellig vs code your choice I'm going to use intell idea and that to a community version because that's for free and it works so we'll be using that so to get the project for spring if you're using a spring tools on vs code or Eclipse you can directly get the project from there and if you're using the ultimate version of intell idea you can create the project directly from your IDE but since we are using a community version I'm going to use the spring in risor so you can head to the start. spring.io and you can get your project from here the base skeleton of the project so the first thing you will mention is a project type I'm going to go with mavin uh language I will choose Java of course that's what we're doing now and then the spring boot version I'm selecting 3. to next you have to mention the group ID so I'm going to select I'm going to type here com. telesco the project since I'm working with spring jdbc demo that's the project name I will give and will not change anything here the packaging I want it to be type of jar and the version for Java I'm using in my machine is 21 now based on which version you have you can either just choose 21 or you can select 177 uh dependencies so basically we have to add a two things only one is the jdbc API now this is your springboard jdbc and you have to add a database now which database we going to use of course you can choose any dbms which you want now we will be using a embedded one so if you have post mql Oracle mssql you can use that the only thing is you have to do some additional configuration and I will tell you where to do that configuration but we are going to use a inm database here and by default when you use in memory like H2 it will be pre-configured so you don't have to do any configuration for that so this these are the two libraries we need and that's it you can click on generate it will give you a project so as you can see I got the project downloaded which is spring jdbc demo I will just unzip it and open in any ID which you want I'm going to use intj so let me do that quickly okay so basically I've opened that project in my IDE and it will take some time for it to resolve the dependencies now we have added two which is jdbc and the H2 and we don't have to manually download the H2 driver because the moment you add H2 it will also have a driver installed so if I open the pom.xml you can see we do have H2 here and we got spring boot starter jdpc this is what we want and now we can get the work started so we will go back here to main Java and spring jdbc demo application and we can start the coding here okay so one I mentioned before right if you want to use po or Mas or any other dbms veryable to the configuration so if you can see we have a resources folder here and if you open application. properties this is where you have to specify those properties so what properties I'm talking about you have to specify the URL you have to specify the username and password and Driver now basically this variable belongs to spring. dat source. URL and you can set the value for the URL whichever you want so that's how you do it but now we are not going to to use M or pus we are going to use H2 which is in memory reason I'm doing using H2 here is because you can directly take this project and run on your machine if I use pqu or MySQL then you have to also install those softwares you have to configure it and there will be some issues with that so let's go stick to H2 okay so what we want to do we want to basically store some data in the database and fetch it from database so we do have database ready so the moment you run this application you will be having Edge to database behind the scen running for you but then what you want to store so in that case what I will do is I will create a simple class which will represents a particular table so normally you can have a relation between a table and your class now you will say how is is it possible see the thing is if you think about a table table will have a table name table will have a columns let's say we are working with aliens so every programmer is alien right so a programmer or alien need an ID a alien needs a name and the alien needs a favorite technology so let's say for me my favorite technology is Java blockchain AI for you it might be something different so basically every alien will need a technology as well so three columns in the table and then this columns or this table will also have some data let's say one1 which is ID and naven Technology Java let's say two Gan technology blockchain so basically you can have this data in the same way if if you try to relate this with a class now in a class B basically what you'll have is you will have a class of course a class will have a name a class will have properties right variables now if you try to match the class name and the table name look similar properties of this class which is ID name and Tech matches with the column names right and the objects for this alien class will be the rows each object will be one row now that's how basically you map between the class and the object and then if you're using any spring orm technology let's say hynet in that case the table creation happens automatically now since we're not using hynet here we have to create the table by ourself yeah that's something you have to do okay let's create a class here and I want to create a class for the let's say alien okay so when I say alien class what I want to do is I want to basically create few variables so I will say private int ID and then private string name and private string Tech so basically I need this variables and for these three variables I also need gets and Setters so we right click here and we say generate Getters and Setters I will just choose these three variables done I also need a two string method if I want to print the data itself so I will just right click here say generate uh two string method and done I will do one more thing I will move this particular class the alien class to a different package so this should be into a package of model so every class should belong to their own packages so alien is a model class so I will just move that to a model package and since we don't have a model package I will say hey move to model package in the SRC yes and you can see it is moved now so that's done but if I want my spring framework to manage this particular bean I have to use add component now if you're not sure how spring boot Works uh you you can check out the video link in the description I will make sure that you have your spring boot video there okay so basically what we are doing with ADD component is we are asking spring framework to handle this object creation object managing and object destroying by Spring framework I don't want to do that okay so basically when I say I don't have to create the object how spring will do it so basically what I will do is this run method so I will go back to the main class so main class is your spring jdbc demo application and in this the you can see we calling a run method run Returns the object of application context so I will say application context context now why I'm doing this is because I want want to get the hold on a spring container again I'm assuming that you know spring boot here because if I start teaching you spring boot then it will take it will make the video length of fire hours I don't want to do that so again you'll find the link in description you can check it out now let me create the object for alien so I will say alien I will say alien one is equal to okay we have to basically import the package for alien which is in the model package done and now I want to create the object the way you can create the object by saying alien right but I don't want to create this object by myself I just want this to be done by Spring framework so I will say context. get bean I want the object for alien. class right uh but the thing is by default the type of this alien component is Singleton so basically the ban is Singleton I want to make sure this is prototype so I will say scope prototype so what will happen is for different request it will create new objects for you otherwise you will have multiple alien object or multiple alien references with the same object so you will have the same data doesn't matter how many data you have in your table okay so that's done done and for this alien what I will do is I will add some values so let's say the ID I want to add is 11 one and I want to say not get ID I want to say set ID alien do set name I want to set the name as let's say naven and alien dot alien 1. set Tech I want to set the tech as let's say Java now basically I'm adding this values and now I got my object ready I want to store this object in database how will I do that maybe I can simply say save this data in database of course this will not work so what you have to do and you know why this will not work is because you have not subscribed the channel yet so if this works on your machine that means you have already subscribed okay I'm just kidding so this will not work right you have to write Java code there now how will you make this work now basically this is where you have to write extra classes see what happens is in normal application you'll be having multiple layers you'll be having a controller so if you're building a web application you'll be having a controller layer you'll be having a service layer you'll be having a repository layer now this repository layer is responsible to connect with the database of course I can write my jdbc steps here in the main because that's a normal main method I can write all the beautiful jdbc steps here but then we don't want to do that that's why we are doing spring board jdbc here and this is not a good idea to write your jdbc code inside main so this should be a part of a separate file now instead of creating a service layer I will be skipping that I I will directly jump into a repository layer so I will just create here repository layer so I will say new class and I will this I will say this is a alien repo or you can also say da if you want now repo is responsible or repository is responsible to connect with database okay now this is where you will do this stuff but first of all uh we need to have some methods here maybe I want to have a method like public void save so it will save the object Which object the alien object alien alien into the database okay so we have to import the package I will also make sure that this belongs to a service uh the repo package move to repo yeah okay so here what I will do is I will say at this point I'm not writing all the codes I will say added and then I also need a method which will return the least of aliens so let's say if I want to fetch all the aliens from the database now in this case I have I need a method called maybe find all methan name you can change as you want I'm just saying find all here import the package and here let me return a new at a list of aliens okay and I'm I'm just wrting an empty object but we'll we'll quote this in some time the idea is I want this to work first of all and since I want spring to manage it I will write add component so instead of writing add component what makes sense is to use add repository for different layers we have different Studio annotations for repository use repository go back here now if you want to store this data in the database I need object of alien repo I will say repo is equal to instead of saying new repo or new alien repo we will be using context. get bean the alien repo do class this is I want now once we got the object I can simply say repo dot I want to save the object let's say I want to save alien object alien one and maybe I want to print all the objects so get all the objects from the table or all this all the aliens from the table and print it here we have not done any changes yet and we have not written any code to connect to database yet but let's see if this works right click here and say run okay taking some time because I'm running this for the first time and you can see it says uh empty why is not calling oh I said added I wanted to print added what's wrong with me okay so let's run this and now you know why it has not worked before why I said SL slash added is because I realized you don't like the videos for me anyway so you can see we got added here and then we got the square brackets which is empty basically empty at least now let's make this work so basically we have to make sure that we write a code in the alien repo this is the only file you have to work with now we have to write a code where we can store this data in the database so how exactly we going to do that it's very simple basically we have have to use a jdbc template now this is a part of your spring boot jdbc so what you to do is you have to get hold on say something called jdbc template you just have to create the object of this I will say this is template okay so you have to get hold on the jdbc template for this this is a property right and this is the inbuilt stuff if you can see jdbc template belongs to springf framework. jdbc Doc okay and and you will see how easy it is to connect and all the all the stuff right you don't have to write connection C driver manager remember in jdbc we do that and we don't have to prepare this statement all this thing will be happening behind the scene but for this I need data sets I can just right click here say generate data Setters for this J template that's done and I want this object to be injected so because see we are just creting a reference of it right where is the object so spring will take care of it you don't have to worry because if you go to JB template we can create object of this class but again for that you have to do all the configuration you can see we have to pass the object of object of data source I don't want to do that I just want my spring framework to manage it and the way you can do that is by just writing autowire your job is done you got the jdbc template object Now using that object let's say I want to I want to add this I want to add the AL object it's very simple now you can simply use the template now this template which is a jdbc template object you have multiple methods now in normal jdbc if you want to execute a statement now based on which statement is is it a select query or is it other queries like delete update now if you're working with select query which is the reading you basically use execute query as a method if you want to change something let's say update insert delete you use execute update here we want to save it right so we'll be using execute update execute update takes two parameters in fact multiple parameters depend upon how you're doing it the first one is the query of course right you need a query with you so I say string St Str or string SQL is equal to what is a query now here I'm going to use a query like insert into what is my table name nowhere we have mentioned a table name right let me make it similar to the class name let's say alien and then how many fields I have so I have let's say ID I got name and I got Tech okay and then I want to specify values as well now where are the values so you tell me where are the values values are here inside this area object you got the values we just have to pass this values here now if you remember the prepared statement we don't directly pass the values what we do is we put question marks there so that we can replace this question mark later so we are using prare statement by default just pass the query here or pass the SQL here after passing the SQL you have to also replace this question marks with the actual values so those are the remaining parameters you have so after mentioning the query just say alien. get ID comma alien. getet name comma alien doget Tech that's it this will basically save it but will it work that's a question right so let's see what happens if you run this and we'll try to solve that later right click here and say run I'm expecting error but let's see what errors you get okay we got the error and the error is very simple it says the table alien not found of course right H2 will give you database not the table table is something you have to create by yourself now how will I create the table and that's where we have to add a schema in the resource folder so you have to add two things actually one is schema for the structure and what if you want some default values maybe I know every time you create a table you want certain values there to test it for that you have to create a file called data tosql and we'll do that in this so let's make it work so what we are missing here is we are missing this table and we have after told solution we have to create a schema file because H2 when it loads we don't have a table there right now to create that file it's very simple you can right click on resources and say new and create a file called schema.sql and say enter now this is where you will write your script now the script to create your table so the script for creating a table or the SQL query is create table and we'll give the table name as alien and then you have to mention the Properties or the the the columns so the column which I want here is the first one is ID now the ID will be of type uh int and we'll say this is a primary key uh then you'll be having a name name will be of type Ware and I will say the Ware is 20 in fact you know maybe we have done this before so normally in 20 characters you will name will be there but then if you talk about South Indian names I'm a South Indian so I have a very big name no no is not my full name my full name is tumar B I know that's huge so maybe I will need 50 characters and then here I will say marks and again marks will be of type in and here we'll be having Tech and Tech will be of again of my Ware and I will say Ware 20 because Technologies can be shorten up if you even if you say artificial intelligence it is AI so let's keep it there so basically this is the file for the schema now we also need a file for the data again not compulsory but then I want to have some data so I will have some insert statement here so I will say insert into alien and I want to insert three columns ID name and Tech and what are the values I'm going to have here so I will say value is one1 then name in single code let's say kidon and let's say technology is blockchain that's one and maybe I want two more so I will just copy and paste it here so this is 102 and this is hsh and let technology is [Music] ai103 and this is social let's say IOD okay so basically we got this uh three rows already created and now if you run this code so we have created two files schema and data esql I will just go back here to my main application and run this and let's see what happens now so when you run this let's see in the taking some time again okay so you can see there's no problem there's no error and we got uh empty here so so the reason it got empty is because if you see the repo we are still wrting the empty data so we're not rning data from the table okay so the data is created how do I know that data is created what I will do is uh you know this update returns you the number of rows affected so you can see we got int so what I will do here is I will say rows and I want to see how many rows are affected and then I will print the number of rows affected so I will say rows plus space rows affected okay so it will print how many rows got affected now if I run this code of course we'll still return the empty add least but at least I want to see is it getting stored in the database that's important so it says one row or rows affected here that's good so that means it is we are able to insert but now I want to fetch it how do I fetch it now to fetch the data again we can use our jdbc template because if you see the template dot there's one more method here which is called query now query returns you the data from table right so I will say query but what is my query so I will say string SQL basically I want to fetch all the records I will say select star from Alien so what it will do is it will return all the rows and then okay let's see what happens so if I say SQL it will return me the rows right but in which format now basically the query you can see we have multiple methods of query to fetch the data so we can use string which is the SQL and then you can use result set extractor or you can say row call back Handler or you can use we can use R mapper for our example R mapper makes sense now what R mapper will do is I I will show you one more time so we have to basically pass the object of R mapper so what happens is when you get all the data in your result set you want to fetch one row at a time right so row mapper will help you to give you one row at a time to do that we have to create the object of row mapper so let's create one so I will say row mapper and row mapper will be working with a type alien let's say this is mapper equal to now how do we create object of row mapper so we can say new row mapper but the problem is if you go to R mapper it is a interface this is a functional interface which has only one method called map row and which takes two parameters one is a result set now this result set will be given by your query and then it also gives you a row number so in result set you might be having multiple rows right and you want to work with one row at a time so this row number will take care of it okay so basically I have to create object of row mapper which has a method called map row and then I have two things here which is z set and row number now basically this is the interface right right so we have to create a Anonymous in a class to do that or we can also create a separate class which implements row mapper then create the object of it again that's your choice but here I'm using Anonymous in a class and slowly we'll convert that into Lambda as well so that it will be short of code but let's say we have this object here which is mapper and this is our Anonymous in our class so basically uh we have to do something here so what we have to do is this result set will have all the records right we have to take one record at a time so what map R does is it takes the entire result set and it executes map row multiple times depending upon how many rows you have so if you have four rows the map row will execute four times and it will give you one object at a time so basically we'll take that we'll create object of alien here I will say alien a is equal to new alien and then this alien object is empty right as of now and I've written the same object so you can see R mapper needs object of alien so I'm getting the object here and then I'm returning it but then this is an empty object right so it will not have values the values are there in result set so I can simply say a do set ID and now get the value from result set so you can say get in so how do you get data from result set you have to say get in and pass the column number so either you can say column number or you can say column name both work so here you can also mention ID a do set name this will be coming from the second row so get string I will say two here and then a do set Tech rs. get string 3 as I mentioned before you can also use a number or you can use a name of the column and that's it once you got the data from result set you are giving it to the alien object and then you are returning it so basically R mapper will take care of the data one by one now the same object which is of mapper you have to pass it here so you're not just passing a query you are also specifying what to do with the result set so if you click on query it returns you the object of list of aliens so the type is T alien so we have to accept the list of alien I will say aliens is equal to this so basically this query will return the list of aliens and that's what you are going to return not the new at Leist so what we did is we fired the query we got the data it's just that we have to map it we have to convert that data from result into the list of aliens and that's what we have done with the help of mappa and that's it I hope this will work let's see let me remove some extra spaces there so with this if I run this code and again I hope it will work let's run okay can you see that it worked so basically alien 101 name kidan technology blockchain 102 hsh AI 103 soal iot and then the new entry which we have added 111 naven and Java this is what we have added right so basically these things are working out that's how you work with the spring boot with your jdbc and yeah that's it in fact you can do some more changes here if you want uh since this is a function interface we can use Lambda expression here so we can remove from here to here cut we can also remove one curly brackets here and you have you just have to put the arrow now let's remove the SQL exception let's put the arrow and then in Lambda we don't even need the type so we can remove both the types here and you know one more thing the row number is not not getting used right so we can even use R there or any other variable if you want let's say a again it will not make it readable right because a is already there let's say row number or we can say row okay so this is the shorter code now you can also replace this mapper with this code again this is optional if you want to do it you can try to use Lambda not compulsory so we don't need to create the reference here we can directly use this right so that's how we can do it in fact you can also remove this part you can directly write this particular statement in return even that works so if I run this code once again so you can see it works so yeah that's your spring Boot jdbc and if you observed everything is happening with the help of J template so you don't have to basically do all this configuration by yourself writing the connection statement creating the statement everything will be taken care by the template you just have to specify the query and file theery so that's it from this video where we talked about spring Boot jdbc and if you like the video please hit that like button and also subscribe if you want to see this type of videos more in future bye-bye everyone

Channel: Telusko

Views: 50,379

Keywords: telusko, navin, reddy, tutorial, Java, Blockchain

Id: Nc9NmS5kEjU

Length: 29min 39sec (1779 seconds)

Published: Wed Dec 13 2023