Spring JDBC Template Tutorial: Learn to build a full CRUD application in Spring Boot

what's up friends dan vega here and today we're going to talk about using jdbc template in your spring boot application so i wanted to put this together because a lot of the tutorials that i see out there are using spring data jpa as a way to get data store data whatever and so i wanted to make it folks aware that there is this jdbc template out there that allows you to write straight sql and you know that's perfectly fine these days there may be use cases for that and another reason i kind of wanted to get into this is i got a question over on the spring boot developers facebook group which i managed so if you're not a part of that please go ahead and head over there and sign up for that and join 30 000 of my closest friends on that group one of the questions i got was hey can you please mention why you would use jdbc most of the courses or tutorials i have taken you use the jpa repository was jdbc an old way to connect before hibernate became kind of mainstream do you recommend using it today if we build a app from scratch and so let's just break that down real quick so jdbc this is the java database connectivity and yeah this goes back to early java and how you would connect to a database so in the java dot sql java x dot sql packages you'll find a bunch of apis for managing connections to databases extracting data and inserting updating etc um yeah and then hibernate came around and hibernate became very popular and spring um built this library spring data jpa that really sits on top of hibernate and makes you know all of that very easy to use but um there is still a real use case for writing straight sql again maybe your team doesn't know hibernate or jpa maybe your team is made up of sql developers also there is something to say about having that control that level of control over the sql that's being written so would i recommend building a application from scratch if you just want to use jdbc template you know writing your own sql queries absolutely and it really depends on your team but make no mistake about it there is nothing wrong with writing sql to interact with the database every language framework out there does it so there's nothing wrong with it and there is certainly nothing wrong with hibernate jpa so it really matters you know what your team is made up of what do you prefer but i really like the simplicity of the jdbc template again you don't need to learn something new and so what we're going to do today is we're going to build an application from scratch it's going to be a small application on building out a course platform but what i want to do is kind of walk you through my steps and we'll take a look at the jdbc template and how it really makes interacting with databases uh really easy so with that let's go ahead and jump on in here over on my screen i am at github.com danviga hello jdbc and this is the url that you can grab all the code for today's demo if you want to you can start from scratch and follow along with me but the finished code will be here and i'll go ahead and update this readme at some point to contain a little more information so with that let's go ahead and jump on in i'm going to be using intellij today you can use whatever text editor id you want it shouldn't really matter so i'm going to go in i'm going to create a new project again if you don't have the ultimate edition of intellij i try to tell this every time i use this because i don't want people to think you can't use intellij but the ultimate edition of intellij has the spring initializer built into it the community edition does not that's okay head over to start.spring.io and you can generate a project from there and import it right into intellij and if you have questions about that please let me know below so what we're going to do is we're going to create a new spring initializer project i'm going to use java 11 for what we're doing today i think you know anything above nine should be okay maybe even eight so we're going to click next and i'm going to go ahead and enter some info here so i'm going to say the group is going to be dev.dan vega we're going to call this course platform this is maven java jar 11 demo of jdbc template and i want my main package to actually just be this so i'm going to click next and so for developer tools i'm going to choose springboard dev tools as far as web goes i'm going to use spring web and then down at sql we have all these options so that what we want to choose is spring data jdbc so again persist data in sql stores with plain jdbc using spring data so we want to use that and the database that i'm going to use today is just the h2 in memory database you can this will work with any of the databases listed here so that is all we need we're going to click next uh where are we going to store this um boot course platform that works and i'm going to go ahead and click finish and load this up in intellij all right so we've created our project the next thing we want to do is i like to usually start from the inside out what am i actually building today so i'm building a course platform with some basic properties of a course so we're going to be modeled around the domain of a course that's going to contain things like a course id and a title and a description and a link and so what i'm going to do is i'm going to actually start with the model so what i want to do is head over here and go into source and main and java and in my main package i'm going to create a new package and we're going to call this model and inside of model i'm going to create a new java class and we're going to call this course so let's go ahead and get rid of maven we don't need you and so first thing i'm going to do is create a few properties we're going to start with private int course id we're going to have a title we're gonna have a description and we're going to have a link so i'm going to generate a couple constructors i'm going to create a no r constructor because we're going to replace we're going to add a new constructor here and that will remove the default noir constructor so i'm also going to add a constructor that takes in a title description and link because ultimately in the database the id is going to be generated by the database so i don't need that in the constructor finally i'm going to go ahead and set up some getters and setters and i'm just going to generate these and i'm also going to generate a tostring okay now that i've got my course set up i'm going to head over and start to work on the database so i have what i want as to be represented in the database as an object which is a course so those properties so course id title description link those are going to go into a table called course and those will be the different columns in our course table so what i want to do is i want to head over to application.properties and i'm going to set up a datasource name so i'm going to say datasource.name and we're going to set that up as course platform i'm also going to um set up dennis data source generate unique name is equal to false i'll talk about that i'll link to another video where i cover this at some point but real quick if you don't set this it will generate like a unique jdbc url and you'll see it in the console so if you don't set this you'll see that it'll basically change every time so when you go to your h2 console and you want to kind of view your tables you won't be able to because that's changed every time we want that to be false because we want the jdbc url that we connect to to be the same every time and so finally because of that i also want the con the h2 console enabled so i'm going to go ahead and set that to true so that's all we need as far as properties go what we need now is some data so we need a schema and we need a data so i'm going to set a schema.sql here and again this is by convention when you're using something like spring data it's going to look for a schema.sql in a data.sql and what this will allow you to do is when the application starts up it's going to go ahead and run this schema.sql so it's going to create our table for us and we're using an h2 in memory database so it's basically going to be dropped every time and recreated for the purposes of this that is completely fine obviously if you wanted this to go into production that wouldn't be that wouldn't work for production because this data is going to basically be reset every time so i'm going to copy and paste this in just so we don't get boring with this this is just the schema to be able to create a new table called course and each of those columns match up with what i have in that course object that we created before so i'm going to save that now i'm going to come in here i'm going to create a new file called data dot sql and we're going to copy in that as well and so this is just five different courses that i have over on my website um these are courses of mine so i'm going to go ahead and save this and now what we have is data dots equals schema.sql and we have our h2 database if you again i guess i should have mentioned this earlier but if this is new to you when we selected that h2 database at the beginning when we were creating the project we get this h2 dependency here in our palm dot xml and because we have our spring data spring boot starter data jpc jdbc on there we're going to get that feature where it's going to look for those two files by convention and then kind of run them for us so that's a long way of saying if we run this we should have a table in our h2 database and we should have a five different rows in there as well so let's go ahead and run this i'm going to run the application and hopefully if we've done everything correctly it will start up without problems and we can check something out okay so here it is it did start up um so again look at this line here you can see h2 console is available at this database is available at this so this is something you want to copy again if you didn't set that generate unique name to false this is going to be different every time so you would have to come in and copy this and head over to the h2 console and paste that in i'm going to go over here i'm going to go localhost 8080 and h2 console and now i can paste that in and the username is going to be sa there is going to be no password we can test our connection that was good if i go over to courses and select run all of our courses are in there so so far so good and i'm going to go ahead and minimize this so we've got our course that models basically what's in our database we have our database working our different properties we have our schema and our data loading up again that no matter what we do to the database every single time that's going to go ahead and get refreshed so the next thing we need to do is set up our dao layer so what i want to do is come in here into my main package i'm going to create a new package called dao and the first thing i want to do is i want to define a contract an interface that any dao that is going to be created for for us here in our application needs to abide by and you'll see this a lot even in services i see a lot of of one-to-ones so if if you're gonna have a course dao you may have a course dao interface and i'm not a huge fan of this it adds a lot of ceremony i like to create one interface that any of my kind of service objects would implement at the end of the day in our case we're creating a dao that any jdbc class is going to implement so we're going to say this is a dao class this is going to be an interface and this can be of any type so what we can do here now is we can define the list of methods that we want any of our daos to supply so the first thing we'll do is say hey you're going to give me a list and we'll call that method list we'll also have create which we'll take in and type and return nothing i also want a get method that returns an optional of that type so that will return an id or that will take in an id we're going to have an update method that returns nothing that will take in a type and a id and finally we will have a delete method that takes in an id so now right away when we're starting off all we're going to do is create a course jdbc dao but as this application expands we may have other daos in the system and what we're saying is any dao that is going to be created in the system should fulfill this contract you should be able to list create git update and delete your basic crud functionality right so we're going to save that we're going to come in here and we're going to create a new java class and we're going to call this course jdbc dao let's actually go to jdbcdao and this is going to implement our dao and it is going to take a type of course so now when we're in here we have to because we are implementing that interface we need to implement all the methods defined in that interface and so what we're going to do is we're going to say implement those methods and just kind of stub all of these out for right now okay so the first thing i want to do is i'm going to just add a logger in here i have a quick live template that does this so that i can just get an instance of a logger next i want a jdbc template right so this is what we are here to talk about our jdbc template and we'll call this jdbc template and i'm going to explain this in just a second but i want to create a constructor and go ahead and take that in as an argument so what happens when spring data jdbc is on the class path which it is because we saw that in the palm.xml what's going to happen for us is spring is going to create an instance of the jdbc template so we don't need to new this up every time we only need one instance of this we you know if we had 10 daos we don't need to create 10 new instances of this we're just going to create one and the way that we're going to do this is spring is going to inject it into our constructor here because we've declared that as an argument now to make this work we just got to tell spring that hey this is a component i need you to go ahead and look at this and include this in dependency injection and also wire up any other dependencies we have here and so right away you see this come in and basically what this does is it creates a beam if you're new to spring world java world fancy way of saying an object it creates a bean of type jdbc template for us and it uses the properties that we've set in our application.properties and so now we have a new instance of jdbc template so that's awesome so not a lot to do if you've ever done jdbc work in the past you know that there's a lot of creating and managing connections you'll have to make sure they're closed at the end you're you have to create managed statements you there's a lot of lower level apis that you have to work with with the jdbc template that's kind of all managed for you which i'm a huge fan of all right so the first thing that we're going to do is get a list of courses from the database and to do this first i need to set up the sql that we're going to execute and so this is going to be a string we'll call it sql and we're going to select course id title description and link from course so that is the sequel and then what we're going to do is we're actually going to use a method called query on the jdbc template so i'd advise you to go to again go into jdbc template take a look around it at all the different methods that are available to you because depending on the scenario whether you want to do one thing or do a batch of things or get a list of things or get just a single thing like there are a bunch of different methods for different scenarios in this case we're going to use the method query and so we're going to use jdbc jdbc template.query you see that first one takes a sql statement to execute and then what's called a row mapper so we're going to take a look at that now so we're going to pass in our sql and then we need to take in a row mapper so let's find this row mapper class and if we look at row mapper it's actually a functional interface and it takes in it has a single method called mapro which takes in a result set and a row number and so this is basically what's going to map a row to an object in our system and before java 8 you had to like write your own class that implemented that row mapper and then you know implement that method today because it is a functional interface there's only a single method on that interface we can use a lambda expression here so what i want to do is go ahead and say that we're going to take in a results result set and a row number and that is going to do a few things so what we want to do in here is create a new course so we're going to say course course is equal to new course and so what we want to do is actually map a column from the result set into an instance variable in our course object so the way that we do that is we're going to come in here and say course dot set and the first thing we need to do is set our course id and that is going to come from the result set and on the result set there are a bunch of get um methods based on the type of data you're getting so in this case we're getting an int so result set oops did i not so rs.getint and then so we're getting the integer and we're going to pass it in a column name so in the database it's written as course underscore id um don't know why that's complaining we'll come back to that in a second course.set title and resultset.get so this is a string and this is going to be a the name of the column in there is title i think it might be complaining because we're actually not returning course yet so let's return course and okay that's what was going on so nothing to do with the method names themselves so next we gotta set the description and again that is going to be a string the column name is description and finally we need to set the link and this is going to be a string and the column name is just link so again it this is a way for us to map every column name to our object and this is going to work but there is going to be a problem and instead of going down the road and showing you the problem i'm just going to kind of walk through it now we're going to need this row mapper again what happens when we get a single course out of the database we're going to have to map that column each of those columns to the corresponding instance variables here in our course object so doing this in line like this is going to make some more work for us we're going to have to duplicate this code later on so what i want to do is actually come in here and take this out and what we can do is we can go ahead and just set up a row mapper of type course and we'll call this row mapper and it is equal to that code that we just wrote there right so now we can do is just use our row mapper instance and that will work there so we've just made that reusable so we can go ahead and use it again later so that looks like everything that we need so what i want to do now is head over to our main application and here in the main app i am just going to come down and what we want to do is actually get an instance of the dao that we've set up so the first thing i want to do is actually set up a variable for my dao and this will be of type dao course cause dao i'm actually going to create a constructor that should take that in and now that that gets um injected for us now we can go ahead and use that so i'm going to go ahead and set up just a little let's say so now what i want to do is i want to get a list of course and we'll call this courses and we're just going to use that dao and we're going to call that list method and so with that we should be able to say courses dot for each and we can just go ahead and use a method reference here and just say i just want you to print each of those out so it's basically going to loop over those use the method reference and call the tostring on it and print it out so let's go ahead and save that and run that stop and rerun and hopefully if we didn't screw anything up it should print out five courses here in the console all right good so that is the first thing that we wanted to do was to be able to list out all the rows in our database all right so the next thing we're going to do is start to work on our crud methods so create read update and delete so let's start with create so we're going to start again with some sql that we need to execute and this is like normal insert statement that you would write so we're going to insert into course we're going to take in the title the description and the link and again the id is generated for us so we don't need that and we're going to say values and we need some way to denote that these are basically going to be dynamic parameters that are going to be bound to the sql query later on so the way we do that is with a question mark so we have three values coming in so we're going to say question mark for each of those and that is our sql statement so in the jdbc template there is an update method that takes sequel there's also one that takes sequel and then a var args of objects so this could be one object or this could be many objects and this is the one that we actually want to use here so we're going to enter in our sql to execute and then for each argument that we want to bind in our sql statement here we need to supply that so what is going to be the title the title is going to be whatever course that git title is the description is going to be course dot get description and the link is going to be course dot get link so that is all our statement is going to do so the one thing i want to do here is i want to talk about the update method so the update method is going to return an integer and that integer is basically going to tell us how many rows were affected so i'm going to say integer let's just call it insert is equal to that and what i'm going to do is in here go if insert is equal to one let's go ahead and just log something here that says new course created and this would be the course dot get title okay so that should go ahead and work so if we go back over to our main application i want to go ahead and above this i'm going to do something similar to this and i'm going to say create course and so all we're going to do here is we're going to create a new course we're going to say course spring view is equal to new course and we actually have a constructor that takes in a title a description and a link so i'm going to say spring boot plus view and we'll say new course and this is just going to be okay so there's our new course so now what we've got to do is call dao dot create and we'll pass in our spring view and so let's go ahead and run this again and what we're going to do is we should see in the council that we have a new course created but we'll also hop over to the um what's it looking for the h2 console and check that there so we do see our create course we see new course created spring boot plus view so let's head back over to here we'll load up our h2 console again and you're gonna have to log in again because every time it resets and if we look at our course we should now see that sixth one which we created so so far so good all right so the next method that we need to write is the get method so this is when we want to get just a single course out of the database and so what we want to do is the interface here defines an optional course return type and that's because if we try to return a course that doesn't exist say we enter the id of 99 we don't want to return null we want to return that optional with an empty without a course in there so that'll give us the ability to like not have to deal with exceptions and things of that nature so that's exactly what optionals are used for so let's start with the sql so we're going to say we want to select the course id title description [Music] if i could type a link from course where course id is equal to and this is where we're going to use that question mark as a parameter holder and that's our sql so what i want to do is set our course equal to null and now what we can do is go ahead and try something so what we're going to try is we're going to set that course equal to the jdbc template and we're going to use a different method this time last time we used a query which allowed us to return a list of results now we want to do is return just a single result right so there's a method for that query for object so what we're going to do is there's a couple different there's a couple different overloaded methods for this and one of which is let's go ahead and pass the sequence and you can see there's another one in here oops let me see if i can bring that back up so sql and then you can pass in an object array of args so the different arguments so in this case that'll be the course id and then you can see one of them there takes the row mapper so we can do is we can say all right we're going to pass in the sql the object array all we're doing is passing in a single one which is going to be the id that is passed in and this is why we did the this is why we refactored that row mapper from before is because now we can just take advantage of that row mapper that we've already created so we can just use our row mapper here and that should satisfy that so what we are going to catch here is a data access exception and this is basically going to be when we can't find that particular course so we're going to go ahead and log that we'll just log in info for now say course not found and then we'll go ahead and add the id for that one so that will do that now what we need to do is we need to say what are we returning here and so what we're going to use is the optional dot of nullable and so we'll go ahead and pass in our course here and so if we look at this method this returns an optional describing the given value if non-null otherwise returns an empty optional so that'll allow you to basically check to see if it's present or if it's empty and if it's not empty you'll be able to go ahead and get it so that's what we're using there so that is the get i'm just going to finish these out here in the dao and then we'll go back to the main application and kind of test these out so next is the update so the update is going to look very similar to the create so almost so similar i'm not going to copy that so let's just do string sql is equal to update course set title equal to and then we're going to use that question mark description is equal to question mark link is equal to question mark where course id is equal to question mark so this is where we're gonna remember the so the update method i didn't mention this before but it sounds like update should be just to update something but it's not update is actually used for inserting updating and deleting so we're gonna do that we're going to use that update a method again and remember it returns back to us a integer so i'm going to say jdbc template dot update and we're going to pass in the sql and then we need to pass in every argument that we set there so we set the title description link and the course id so i'm going to go ahead and say course.get title of course that get description course dot get link and then the id is actually the last parameter there so again if update is equal to one i'll go ahead and just log out of course updated okay and then delete is going to look very similar but i'm going to actually just write this in one line so we can look at this we could have been doing this before i like to be a little more explicit with things when i'm kind of introducing them the first time but now that we're down to delete i can just say return jdbc template we're not actually returning anything sorry sometimes i like to return that if you're if you wanted to you could return like a boolean from create and update so sometimes i'll say return whatever that integer that's getting returned back from the update method say hey if that equals 1 we're going to return true else we would return false but not doing that here so we're just going to say update and then we're going to delete from course where course id is equal to the parameter we're going to pass that in and that's all there is to it so now let's go back over here and what we're going to do is just do a quick check on updating and are actually getting updating and deleting so let's go ahead and get this particular course so we're going to do something similar to this oops so slashing one course and actually let me just copy that i hate when i have like crazy um okay so let's get a course um i think the let's just course um first one is equal to um gao.git and we'll say the id is one and so okay so this is actually remember we're not returning our course we're returning an optional so we can say create a local variable and this is going to return an optional of a course and then if we want to we can actually get it by first one dot get and so that will return that so that's checking so we create one we get one we list all of them let's go ahead and update this one that we created up here in the create course so this is spring view let's just change the description learn to build a few apps that talk to spring boot okay and then what we can do is we can just say dao dot update so the course is spring view and the id we know is actually six that's the last one that we create because we start with 5 and then we enter it basically just increments the id so we know that one will be 6. and then finally i'm going to go ahead and delete one let's say number four so let's go ahead and run this and hopefully everything will work so just to recap we're creating a course a new one so it's going to be number six we're gonna get that first course and print that out we're gonna update the six course description and then we're gonna delete number four and then we're gonna print them all out so we can see that number four was deleted in here we can also see the description for number six was updated and if we want to get one course we can see that the first course we we were able to get so let's just head back over to here again we know that what we're seeing in the console there is from the database but i just like to show the h2 console so you can see the actual database results so here we go we have five of them because number four we actually deleted and here's that description that got updated and i think that is it so that is all the basic methods that you would write in a dao and we just tested it out here right in our normal main application class but in a normal app what we would do is we would create some rest endpoints that then took advantage of the dao so if you're interested in continuing on this journey i'm going to take this example that we built here today and we'll build out a full rest api that talks to this da up so if you're interested in that please make sure you subscribe to the channel and i will let you know when that drops as always if you found some value in this video today friends please go ahead and give me a thumbs up that really helps with supporting me in this channel and i was always friends happy there we coding [Music]

Channel: Dan Vega

Views: 33,529

Keywords: spring, framework, java, jdbc, spring boot, spring boot tutorial, spring jdbc, spring jdbctemplate, spring boot jdbc example, spring boot jdbctemplate, spring framework, dan vega, spring boot crud with h2 database

Id: 0uLqdBpYAVA

Length: 40min 59sec (2459 seconds)

Published: Sun Oct 11 2020