Spring Boot 3.2's Secret Weapon: Auto-Configured JDBC Client for Effortless Database Integration!

welcome back friends my name is Dan Vega spring developer Advocate at VMware and today we're taking a first look at the new jdbc client in Spring Frameworks 6.1 in Spring boot 3.2 before we do so I thought it'd be a great opportunity for us to take some time to talk about how we got here communicating with the database reading and persisting data in Java has never been the easiest thing to do right there's a lot of things that you need to think about you have to be able to construct gwc URLs you have to think about how do I open a connection when I'm done with it how do I close it when you get into a real world application you need to think about things like connection pools there's a lot going on underneath the hood to be able to make this happen right thankfully we have spring and some of the abstractions in Spring make this a lot easier for the longest time we've had the jdbc template in Spring and this has given us a nice abstraction to be able to talk to communicate with the database right the downside to this is that building out simple crud Services based on a resource can get pretty verbose you have to understand all the methods that you need to be able to communicate with the database you have to understand things like row mappers and how do you map a column to a field and things like that so it can be a little bit complex and this is just for a single resource when we build out entire applications this can get pretty verbose the nice thing is though you have complete control of the SQL and this is something that a lot of folks really enjoy that's the jdbc template the jdbc client will come along and improve some things for us first of all similar to the rest client that we talked about in a previous video it will give us this nice fluent API it's very easy to understand and read if you've never used it before when we look at the code today be okay yeah I understand exactly what's going on there so from an ease of use standpoint and a readability standpoint I'm a big fan of the jdbc client also this is auto configured for us in springboot 3.2 so we can just go ahead and ask for a bean in our application in our service wherever we want to use it and we can get an instance of it so that's very nice so here's what we're are going to do today we're going to create a brand new application we're going to set up some database access to a local H2 database and what we'll do is we'll start with the jdbc template we're not going to write out every single method together that will kind of get a lot you know verbose but we'll kind of paste some code in we'll take a look at what that code is doing and then we'll move on to the jdbc client we'll talk about how we can get an instance of that gwc client we'll look at the API and we'll use this to build out basically a crud service that talks to a database so I think this is going to be a lot of fun I'm a huge fan of this new jdbc client I think you will be too what are we waiting for let's write some code all right we're going to start here at start.spring.io and we're going to choose a maven project using Java and we're going to use the latest Milestone of 3.2.0 again if you're watching this in the future just use whatever the available Milestone is or if you're watching this when it's been released use spring.3.2.0 or later so we're going to fill in some metadata here Dev dot Dan Vega we'll call this the jdbc client demo and what we're going to do from a dependency standpoint is we're just going to pick web so I'm building out a web project and I'm also going to choose the jdbc API that will give us the spring boot starter jdbc and then I need a database to talk to I'm just going to choose H2 that's all we need to get started I'm going to go ahead and generate that project which will create a zip file I will go ahead and open that up in my favorite ID IDE IntelliJ Ultimate Edition but please feel free to open it up in whatever ID your text editor you're most productive in all right so I need to build out an application here I'm going to start with a package of post I'm going to be creating a post tracker that represents like a blog post in our application here so I'm going to say new job Java class we'll call this a post and this will be a record so this record is going to have a string ID string title slug local date time to read tags yeah that looks pretty good copilot thank you very much so that is a representation of a post in our system next we're going to create a simple post controller so I'm going to come in here and say Java class we need a post controller and this will be a class and I will say hey we need this to be a rest controller we want this to respond to a request mapping of Slash API slash posts and we need to go ahead and fill this in I think I have a nice live template for this so I'm going to say jdbc client post controller and all this is going to do is wire in a post service and then fill out some methods for like hey I want to find all of them I want to find one I want to create if you've been following the channel we've done this in a similar pattern before right now we don't have a post service and this is where we're going to delegate to a post service to talk to a database now we don't have this post service and I'm actually going to create two different implementations of the post service one that uses a jdbc template one that uses the new jdbc client just so we can compare and contrast to do so I'm going to create an interface this interface is going to represent what a post service looks like so we'll go ahead and call this post service and then we'll use this as an interface so now what I need to do is kind of fill in these methods and I think I have another live template for that and so now any post service that implements this interface will need to Define these following methods a find all find by ID create update and delete all right so what we're going to do is we're going to start with um the actually let's let's start here let's just make sure we can connect to a database so to do so I'm going to create a schema file so I'm going to say schema.sql and we have a post schema for this so this is defining because we we are not using something like jpa the table is not getting created for us we need to Define that table and this is just lining up with that record we created right so we have ID title slug date time to read tags version and I don't even think we need version because we're not using spring data jdbc here cool so that's everything for that from an application property standpoint I'm just going to fill in a few properties here and this is to not generate a unique name here's my data source name which is blog and hey I want you to enable that H2 console so I can look at the database okay so width of that in place and this looks like we don't have the jdbc template yet so that won't work so now what we can do is we can create a new Java class we can call this the jdbc actually let's just call this template post service so template post service is a class it's going to implement the post service right so once we do that now it's going to complain it's going to say hey you need to implement these methods okay we can do that let's Implement those methods I'm going to update one thing here this is a template Post Service um so with that I think uh everything is good we just need to mark this as a service and then all of our errors go away but now we need to implement these methods in the jdbc template post service so this is where you would go about writing each of these methods right you got to implement the find all how do we implement the find all well you need to bring in a jdbc template and you need to write the specific SQL for that and then you need to add a row mapper to map each of the columns in the database table to The Entity or in this case our post record so I'm going to fill this in as I said at the beginning of this I don't want to do this jdbc template by hand it will be very verbose I have other videos on the channel if you hit on walk through a jdbc template crud example you can do so so I'm going to do that and then this will give us the code that we need to look at so first we get a gdbc template might not have been initialized oh and that is because we changed the name here to to template Post Service okay so same thing here template Post Service all right so we get a logger we have a we've declared a final variable called G of type jdbc template uh gdp's template and this will get Auto wired in for us because of the auto configuration so when we choose that spring boot starter jdbc this is something that gets created for us now when we use that to do something like find all we select all the columns so ID title slug Etc from post we use the jdbc template.query method and again this is similar to kind of the rest template right it's a nice abstraction but there's still a lot going on in there if you look at the jdbc template and we jump in there and we look at the structure of this there are a lot of methods here and there are a lot of overrated overloaded methods so it can be a little bit confusing on which method do I choose for this situation in this case we can query we can pass in the SQL for that but then we also need to pass in a row mapper and what is a row mapper if we look at this a row mapper is an interface used by jdbc template for mapping rows of a result set on a per row basis so for every row it will map again the columns to whatever uh you know in our case a record type right so this is also a functional interface so it is a candidate for Lambda that's why you kind of see this Lambda expression here and all we're saying is hey for the result set and the row number I want you to go ahead and create a new post and the new post is we're mapping you know the post takes a certain number of arguments and for each argument we're saying hey the ID you're going to get that from the column ID the title you're going to get that from the column of title and so on so this is how we map that and you need to pass that into jdbt templates query method so then as you go on again you'll see similar things going on here there's a create method I actually created a batch create in here which we don't need so I'll get rid of that there's an update and a delete so not a ton of code a lot easier than working with jdbc and Java by yourself right so this is a nice level of abstraction but still a lot of verbose and again the jdbc template has so many methods I'm often confused on which one to go ahead and reach for so um this should work though we should be able to create a new uh Post in our system so let's make sure that that works so let's go over to uh application here and what I want to do is just copy in something copy pasta so we can go ahead and say hey here's a command line Runner I don't need a qualifier yet because I only have one Post Service in the system we'll talk about what that qualifier is in a second but hey I'm saying hey use a post service because that's getting created and put into the application context the command line Runner runs after that happens so we have an instance of our post service and we say create a new one and that create method is the one that is persisting it off to a database so let's see if we can get a connection to a database here and insert a row and then go off to our post controller and see if that is there so we see the H2 console is available so what we can do now is fire up a terminal here and you can just go back to the browser and go to localhost 8080 if you want I'm going to use HTTP HTTP and go to 8080 and we should go to API post and that will give us all the posts in the system and of course we only have that one post that we just inserted so it looks like uh for the time being everything is good our post service is working we've done our job so far so what I want to do now is I want to simplify this post service we have this template Post Service I want to create a new client post service so this post surface is going to implement our post service and when we do so we need to implement those methods and instead of copy and pasting in a whole bunch of code we can go ahead and write this together all right so before we got an instance of the jdbc template now we're going to get an instance of the jdbc client so I'm going to say jdbc client jdbc client I can get that through a Constructor parameter and now that is wired in for us so springboot 3 tattoos Auto configuration to clear that for us something is available in the application context we just asked for an instance of it so now that we have a jdbc client how can we find all of the records so this should be pretty easy we're going to say gdbc client jdbc client dot SQL so now we're going to pass in some SQL here it's saying copilot is suggesting this for me because I've written this code before and it's saying hey grab this is the sequel to grab all the The Columns from the post table we're going to run a query and when we do so let's go ahead and take a look at this so proceed towards execution of a mapped query with several options available in The Returned query specification so the nice thing is the target we're passing in a param here and this is the mapped class this is the target class to apply a row mapper for either a simple value type for a single column mapping or a Java Bean slash record class so the really nice thing about this is that all we're doing is saying hey this is what we want to map it to and the map is the post so we don't have to write the row mapper anymore uh spring underneath the hood is able to look at that record and go okay I can create a row mapper based on that and based on the select statement that or the sequel that you gave me so this alone is really nice there is a way I don't have an example of it here there is a way to write a custom romapper if you need to dip down and get some that level of granularity a customization you can but this is really nice right out of the box you just get a way to get a row mapper without having to write it once that mapping's done I just want to say hey list it out so I'm just getting a list of all of the posts in the system that's really great so let's just go ahead and what I'm going to do is I'm going to write all these out and then we'll go back to the post controller and we'll go ahead and replace the jdbc template instance of the post service with the um with the jdbc client version so that's how we can find all how about how about the ability to find a single one so here we are saying hey we want to return an optional post let's go ahead and remove that and we'll say jdbc client dot SQL so what is the SQL that we're going to write here let's see if copilot is on it is and then what we're going to do is we're saying hey select all these fields from post where ID is equal to and you see we have a named parameter here so we're passing in that param we're saying the name of the parameters ID here's the parameter that I want you to use we're saying query again we're mapping this to a post record and we're also saying hey if you can't if you can't find one please return an optional so that's really great that is just again the ability to look at this even if you've never used it this fluent API it's very readable it's simple I like it okay so next up is create so we're going to say we're going to say jdbc client dot SQL and we're going to pass in some SQL here so this one is yeah so you can do I guess that would work let's see so we're inserting into post here are the uh columns that we're inserting into here are the values so you could probably use name parameters here I think I'm just using uh non-name parameters here and then I'm saying params so you can pass in a list of so I'm saying list of and here are all the params that I'm passing in and then I'm calling update what does update return we can see that it returns an integer same as jdbc template so in this case if I wanted to I could say let's go ahead and assert the state and if up if uh update is equal to one failed to in fail to create post and here is the post title um so yeah that'll work because again if we if update is equal to one failed to create post actually I think that's backwards right if the update returned one we of the expression okay if it's not equal to one right so that's right so um there's our create method and then I think update and delete are going to look pretty similar so for these I will go ahead and just paste some code in all right so we have an update again we're passing in that SQL string here's a list of parameters here's update uh we're just asserting some State here same with delete so if you're not feeling familiar with the jdbc template anytime you are updating creating deleting we are calling the update method and so every time we do that it will return an integer of how many rows got changed so in the case of creating or updating or deleting we should be just affecting one row if everything works out good so there is our service now I think we are we getting any errors in here I think I have some things going on um I think that's because we're just kind of calling it The Client post service here I don't know what happened there um client post service is never used um is never used okay we don't care about that so our errors are gone now we have a client post service so the first thing I want to do is in the post controller just change the post service that is getting wired in here so you could say Post Service here and if you do that then you need a qualifier so you're going to say qualifier and I want you to use the client post service right so that will work now back in our application we need to insert that one record so I want to set a qualifier here as well so I'm saying qualifier is equal to the client post service right okay and we cannot find a bean with the qualifier client post service so let's go back to client posters and uh yes we need to mark this as an ad service so now if we go back to the application client post service this should work let's go ahead and try and run this so we'll run our application we have an error parameter zero of the post control required a single beam but two were found consider marking one of the beans primary updating the consumer to accept multiple beans or using the qualifier annotation I thought that we did that did we not oh that's why um I don't want that there sorry okay let's try and rerun that and everything looks good there all right so what I'm going to do is go back to the um actually let's go over to the browser and make sure our uh rest controller is working all right so if I go to local list 8080 nothing but if we go to API posts we can see a collection of all the posts if I go to um posts one two three four then we could see that one if I go to one that doesn't exist then we're just going to get a null so obviously we want to handle some not found errors there but I think that's really what I wanted to cover today the um post the client Post Service the ability to use the jdbc client in Spring framework 6.1 spring boot 3.2 again Auto configuration makes it available for us and just the ease of use of understanding how to use this API this fluent API I call SQL pass in my SQL hey this is what I want you to map it to and oh yeah just give me a list of those back this is just very easy to read I think even if somebody got dropped into this project and wasn't really sure what the jdbc client is they could probably make heads and tales of what is going on here so big fan of the new clients in Spring boot 3.2 the rest client we did a video on I'll leave a link to that in the description below and now the jdbc client and as always I'll go ahead and leave a link to the GitHub repository if you want to go ahead and explore that so hey I hope you found this interesting if you did Friends please do me a big favor leave me a thumbs up subscribe to the channel and as always happy coding thank you [Music]

Channel: Dan Vega

Views: 13,230

Keywords: dan vega, spring framework, spring boot, spring boot 3.2, spring boot jdbc, spring jdbc, spring jdbc client, spring jdbc template

Id: JBu5GibEJ4k

Length: 21min 54sec (1314 seconds)

Published: Mon Sep 11 2023