What is the difference between JDBC Client and Spring Data JDBC?

welcome back everyone Dan Vega here spring developer Advocate at broadcom today we are answering a question from one of you and before we jump into that I want to talk about some options for getting your questions answered so you can always send me an email danve gmail.com probably the best way to get a hold of me if your question is relevant to one of the videos I'm making go ahead and leave your comments on YouTube and I will try to get to them there if you're on the Bird app over on Twitter you can go ahead and reach me at theal Dan Vega go ahead and and uh follow me there send me a direct message I will see if I can answer it there finally a really good option is spring office hours. I'm not sure if you're aware of it yet but this is a weekly live stream that we've turned into a podcast where we try and keep you up to date with everything that's going on in the spring ecosystem as well as answer your questions so if you want to get your questions answered live please come attend one of our live streams or check us out on your favorite podcast asked player with that said we're going to answer today's question and the question is what is the difference between the new jdbc client in Spring boot 3.2 and spring data jdbc I thought this was a really great question uh especially if you're kind of new and not sure what spring data jdbc is or spring data in general this is a really good time to talk about this so what I want to do today is create a new application we'll use spring we'll use Java 21 doesn't really matter which version of java you use I guess uh I I guess in this case using spring boot 3.2 is important because we're going to take a look at the jdbc client but what I want to do is go through kind of a progression of how we might read some data from a database using plain old Java we'll talk about that we'll move up the stack to Spring uh we'll take a look at something called the jdbc template which has been there since 1.1 we'll move on to the jdbc client and see what problems that solves and finally we'll round It Out by taking a look at Spring data jwc so I hope you're excited I hope you learned something today and with that let's write some code all right so I will share this repository in the description below but you can find all the code that we're going to walk through today here as well as a nice read me that kind of guides you through this um so here's the question uh here's what we can do to get started you can create your own project over at start. spring.io or you can click this link this handy little link here and it will basically populate everything for you so today we're Crea a new Java project using Maven using spring boot 3.2 we filled in some metadata here we're using Java 21 although again it doesn't really matter I think you can go ahead and choose Java 17 if you wanted to from a dependency standpoint we're going to start by building a web application so I'm going to choose spring web we just need the jdbc API to start with this is the database connectivity API that defines how a client might might connect and query a database now there are a couple different routes that you can take here if you're not comfortable with Docker and using Docker desktop if you don't have that installed then you could just use an H2 database that will be fine you may need to alter the uh the ddl script there but in this case I'm going to use postgress SQL so I'm going to say uh I need the postgress SQL driver and then I'm going to choose the docker compos support now I've done videos on this in in the past I would try to leave it Link in the description to those uh but this just will kind of allow me to automate some of that it will create a composed file for me with a postrest database service it will automatically start that up and it will automatically inject those properties into a property Source at runtime so I don't have to do any of that so this just makes downloading this project uh very easy to do you can come over here and get clone run this particular repo without having to set anything up so with that I'm going to go ahead and generate this project I'm going to uh open this up in my favorite IDE which is intellig you can open it up in whatever text editor or ID you're most productive in with that let's write some code all right so in this application we are going to be creating a simple blog application modeled around the idea of blog posts so the first thing we need to do is talk about the database that we're connecting to again in my example I chose the docker compos support so we're going to connect to a database here called uh I'm going to name it blog I'm going to give it a username and password of user and password so I'll say user password and I'm just going to do something else here so this port uh in Docker compose this is basically going to give us a dynamic Port now uh and I want to be able to connect to this using my database tools here in intell or whatever other database tools you're using to inspect the database so I'm going to go ahead and change this to map the port 5432 on the container to my local Port of 5432 so it's not using a dynamic Port I now can connect to the database here in intell so I should be able to go ahead and run the application here it sees the composed. yo by convention goes through creates a service and starts it up and now the application has started up so before we do anything I'm going to go over to the database I'm going to create a connection to that data source so we're going to come in here and say postgress and we'll have a username of user a password of password we're running on 5432 and the database name is blog so let's go ahead and hit apply and hit okay and it looks like we have a connection there is no data in there yet there's no table yet uh but that's okay we can go ahead and do that now so the next thing I'll do is I'll come into resources and I'll create a new file and I'll say this is called schema.sql and we'll go ahead and and drop some schema in here uh so this is going to create a new table you'll notice my comment here uh I'm doing this for demo purposes just to kind of act like H2 and restart uh kind of clean everything out on restart but obviously for you if this is going into production don't do that so I create a new table called post it has things like ID title slug the date time how how long it took to read uh some tags and then a version which is kind of specific to an example we'll look at later and then I just use this to insert into post I insert a single value this is our first post we should probably have a body in here I'm just noticing that but that is okay so we have a a post in the database and then we should be able to look at it now to run this uh you'll need to set one setting because we go ahead and look at this by convention so we're looking for schema.sql if it's an embedded database we will automatically run this script but if it's not embedded like ours postgress SQL we need to go ahead and say uh okay it's okay to go ahead and anit that so I'll say spring. SQL doit mode equals always and then I'll go ahead and rerun this application and what I'm hoping now is it starts up and we can come over to our database tools we can refresh this and we see a table here it is post and if I double click on that we see our one post so this gives us a datab a connection to a database we have a database we're able to see that one record in there this gives us a foundation for everything that I want to walk through in this tutorial all right so the first thing I'm going to do is create a new package I'm going to call this post and I'm going to need a new post record in here so I'm going to say new Java class we'll call this post this is going to be of type record I'll go ahead and put these on separate lines so I'm going to have a string for an ID I'm going to have a title I'm going to have a slug and again this is kind of lining up with that schema that we just created right so I'm going to say a local date for our date a time to read and then finally um tags so this is everything that is in the schema except for version so again we'll come back to that when we get to Spring data but this us what we need to basically have an object that represents a post in our system so now that I have a record in the database I need a way to read it and I'm going to go ahead and create a Java class I'm going to call this post service we're going to mark this with the at service annotation so spring kind of picks it up for us now the first route that we're going to go is by using plain Java so we're going to talk about data access in Java um when it comes to connecting to a data source and accessing data using jdk there are two primary packages that you're going to look into uh java.sql and javax.sql so the first thing we're not going to go through every single step I don't this could this would get very exhaustive if we just talked about some of the Java low-level stuff but one of the one of the things we'll actually need to make this happen is something called a data source so if we look at data source that's in javax.sql uh this is the um Factory for connections to a physical data source that this data source object represents now fortunately for us that has been created by Spring already so we don't have to do all the way lowlevel what's happening in Java we have access to that data source so I'm going to say uh private final data source data source we'll get this through Constructor injection and then spring will go ahead and wire one in for for us so I want a method to list out all of the blog posts in our database so I'm going to say list of posts we'll call this find all and inside of here we need to use our data source to go ahead and get a connection so I'm going to say data source. git connection what is that going to create that creates a connection so this is another one of those classes here you could see this has been around since Java 1.1 this is in the java. SQL package again this is the these are the apis for working with databases in Java they've been around forever uh so we'll go in here and we'll add this exception so to do this I'm going to go ahead and paste some code in it doesn't make a lot of sense to talk through all of this today uh we could do that if you want in another video but I just want to show you that hey to make this happen we need to we needed a data source connection we need to get that connection using that connection we we can make a prepared statement uh prepared statement is part of java.sql from there you can use a prepared statement to execute it and what that will return is something called a result set now with the result set you can iterate over that and you can use that result set to get at the different columns like hey I need to get a string because that is what the ID is in in the database it's a string so get a string here's the column and uh that'll work out so so we can see that we don't have a version yet so let's go ahead and just get rid of that for now and then everything should be okay so I create an empty array list of posts and then for each one in the database I go ahead and add it to that array list and I just return that so this should uh go ahead and work if we wanted to go ahead and test this out we can do so in our application class I could create a new Bean so this could be up type command line Runner the command line Runner will go ahead and execute after the application is started up and the application context is ready which means we have access to any of the beans or components defined in our system that means I can get access to the post service so I could say Post Service uh return ARS yes and then inside of here we could go ahead and get those posts so I'm going to say list of post posts is equal to post service. findall we should get all of those and and let's just go ahead and print them out to the council so I'm going to go ahead and rerun this application and if everything worked okay we should see our posts and we do there's only one it printed it out to the council so this works uh but again if you're if you take spring out of the the picture then it gets a little bit harder right there's a lot more things that you got to manage spring managing just this data source for us uh and the connection pools did a lot for us but still the pro the process of having to access data and persist data to a database is not the easiest thing to do because of the apis the apis have been around forever uh they are backwards compatible so um they need a little bit of love right um we need to find an easier way to interact with our databases and this is where we will bring in Spring and talk about some of those different options all right so I'm going to go ahead and stop this I want to just uh rip some of this out let's say let's rip this piece out we're still going to have a findall method right now this is just going to return null and we will go ahead and delete all of this so we're going to remove that and we're going to take a look at the jdbc template so what is the jdbc template let's go ahead and open that up in here so if we look for jdbc template we will see this uh this is the new C this is the central delegate in the jdbc court package it can be used directly for many data access purposes uh supporting any kind of jdbc operation essentially this is a wrapper this is something that is this is an abstraction this is going to help us with everything jdbc related uh It also says in here I believe um yes this class executes SQL queries or updates uh initiating iteration over result sets result sets and catching jdbc exceptions and translate them into common uh exception hierarchy so there's a lot of things that this class does to really help us out if you take a look at the structure um there's a whole bunch of things in here like hey F I need to get the max rows or if I want to execute if I want to get a query there is just a lot going on in here all right this is a very large class so if you take a look through here you can see what this API will do for us uh but this is going to help us simplify some things so because we already brought in the appropriate dependencies when we started this we said hey we're going to bring in that spring boot starter jdbc not data jdbc just jdbc the jdbc API that would give us uh an instance of the jdbc template so now we can say private final jdbc template jdbc template we can get that through Constructor injection and now we can go ahead and use the jdbc template here so what does that mean for us that means we can say jtbc template Dot and then query we can see there's a whole bunch of overloaded methods there's a lot going on in this API and that's one of the reasons we'll talk about some other abstractions today is because it can be a little bit confusing on what method do I use which overloaded method is right for me in whatever situation I'm in it can be a little bit confusing uh I'm right there with you but we're going to use this first one uh this first first one says hey I accept a string uh that is called SQL so this is the SQL that we want to execute and then the second argument is a row mapper so if we look at that uh we can see the row mapper here and the row mapper is an interface used by the jdbc template for mapping rows of a result set on a per row basis we had to map every one of those columns like we had to say give a string for the ID because it's a string so that's what we're going to have to create here uh to do so uh I'm going to go ahead and say um query select star from post and then uh the second argument is going to be that row mapper so because it's a functional interface we can just use a Lambda expression here so I'm going to say uh the Lambda expression and then we'll say new post and then this is where we will basically do the same thing we did before so I'm just going to kind of copy this in here U because we're just using a way to uh get at each of the columns so um I think yeah so we'll probably finish that off there and then uh that will probably finish that off there and there we go so now we've kind of simplified that clo I know there's a lot going on here but we're not dealing with things like prepared statement and connections and data sources and result sets and trying to iterate over them this is vastly improved that code that we just looked at before so now if we go ahead and restart this application uh because it's all weared up the same this should just go ahead and work and output our single post which it does all right so that was good but again uh we can see that if we start to build out this entire API here for the Post Service like our find all our fine um we're going to use row mappers all over the place um when we when we start to get into like creating updating and deleting it can be a little little bit confusing on which methods to use which overloaded method to use so we get a little bit pretty ver both here as well using the jdbc template now the jdbc template's been around uh probably since the beginning of sping spring and it was that abstraction on top of everything that we looked at in the jdk so let's see if we can improve this we are running on Spring boot 3.2 so we have access to the jdbc client again the jdbc client is new in Spring boot 3.2 but if you're using that we have autoc configuration for that so we should be able to just say jdbc client jdbc client get this through Constructor injection and now here we can say jdbc client Dot and notice that once I hit Dot I'm not giving this long list of a 100 or 200 methods with all these different overloaded options I'm simply presented with a DOT SQL method so that SQL method is saying hey what is the SQL that you want to execute this is because we have this nice fluent Builder style API we're going to use SQL and then based on that then there are some other operations that we can perform so I'm going to say. SQL and all I want to do is Select star from post or even uh yeah we could get more specific but I'm fine with this for now um whoops and then what do I want to do um there are a bunch of uh methods here for doing different things like if I had to pass a Pam in I could uh if I wanted to run a query on that I could uh here's the nice bit now uh if we look at this St query method there are a couple different ones you can say you can pass in a class which is a mapped class like hey I want to map the results of this to this particular class or you could pass in a r mapper so if you want to pass in a r mapper the same way that you did with the jdbc template you could but I like this idea of using a mapped class this means that if everything lines up correctly I can just pass in this post record that I have and that should work finally all I want to do is turn this into a list and that is going to return me a list of posts I don't know about you but this is significantly simplified especially when we get into building larger applications and we have more than one kind of service here this is really easy on the eyes so I'm going to go ahead and restart this I want to verify that this still works and outputs our single post and it does all right right so we've seen some different options here and now I want to take a look at Spring data and more specifically spring data jdbc so if we go over to spring.io and go under projects you will see spring data here so you can click on this to learn a little bit more about spring data this is the umbrella project there are a bunch of different modules when it comes to Spring data spring data jdbc being one of them but spring data as a whole the mission is to provide a familiar and consistent spring-based programming model for data access while retaining the special traits of the underlying data store that is because different data sources different databases do different things and we want to make sure that we kind of retain those so I'll let you dig through the documentation what the features are what the modules are if you want to learn more about what jdbc is you can kind of dig in here this is just kind of showing the progression and I want to show it to you through an example okay so we are going to come in here and remove this I am going to remove this so the first thing we need to do is go into our pom.xml uh instead of just using spring boot starter jtbc I'm going to use spring boot data jtbc right so this is the data starter so I want to bring in that support so with that that simple change that is going to give me what I need uh the first thing I want to do is go into my post uh again this isn't going to be a tutorial on Spring data jdbc I have other tutorials on the channel for that there's also some really great guides uh and documentation over on the spring IO site you can check that out there here I'm going to add an at ID annotation that just marks that as the ID I'm going to add an at version annotation and I'm going to add a new column here uh integer version and that is just going to help us deal with when is this going to to be a new record or when is this going to be updating an existing R record and so again dig through the documentation to learn a little bit more about that so with that in place now what I can do I can go ahead and create something called a repository so we're going to say this is a post repository did I spell that right I sure didn't post repository and this is going to be an interface that extends something called the list cred repository it takes in a type and an ID so in this case the type we're working with is a post the type of ID that is in post is a string so we'll say string and that's it now we have a post repository so this will allow us to get all of the crud functionality we need without having to write any of the SQL so if we dig into list crud repository we can see there are three methods here one for finding all that return a list find find all by ID that returns a list and save all that returns a list the rest of the crud methods will be in the crud repository uh the reason for the list cred repository is it just overwrites these um find all methods that return iterables because we're used to working with lists let's return a list um instead of that iterable if you want to return the iterable then you can just extend the crud repository so that's going to give us all the functionality we need out of the box so now what I'm going to do is actually go into my schema.sql here I'm going to comment this out so that and we'll change some data so this is not not going to insert a post now I'm going to insert a post uh for me and the way that I can do this is I'm going to say I actually don't even need the post service anymore I'm going to say hey I need that post repository let's call that a repository and what I'll do now is I'm going to insert a new post I'll say repository. saave so again I'm I haven't wrote written a save method that will persist a new post into the databas that's part of spring data now so now I can say hey I need a new post I'm going to give it an ID of one a title of hello world the slug is hello world uh next is I believe the date local date now we'll give it a time to read of 10 minutes and then I believe the tags which are spring Boot and Java and then we need a version we're just going to set this to null it first so uh is that right so that should be right uh new post oh that's right we made this a string so let's just say one two three four and now we should have a um new Post in the database now we can check this because we can say I need to get a list of all the posts in the database I'll call this post but I'm going to use that repository the repository is a find method find all method that will return on the post and then I can go ahead and P print those out so I'm going to rerun the application hopefully we see one in there so it'll be a little bit different that time to read is now 10 minutes instead of five so we know that we are inserting a new record here that's where a lot of the power of spring data comes in it solves a lot of these problem for us it lets us think about other things instead of having to worry about uh all the kind of setup and crud functionality we need to do uh for each of the types in our system remember we've been working off an example with one type a post this can get very uh verbose if we start to do it um for all the different types in our system if we're using uh plain old Java or the jtbc template so now I'm going to show you one more thing the post repository this is a spring data abstraction the Repository you can go ahead and Define your own custom queries too so what if I wanted to get a particular post by its slug I can say optional post find by and you'll see I get this uh intellisense here looking at all the different properties of my post record and I'm going to say find by Slug and uh I think that will say also ignore case because I don't care about the case so then I'm going to pass in the slug and that's it I'm just provid the uh method signature I'm not providing an implementation of it so now over here once that's saved I can say um repository. find by slug ignoring case and I'll just say hello world that it's going to return a post and let's just go ahead and say by Slug doget and we'll go ahead and output that there so let's go ahead and run this and there you go so we print out our one and then we get it by its Slug and we were able to print that out there so that's really it that's what I wanted to go through today I hope um I first off I hope I answered the question what's the difference between a jdbc client and spring data jdbc um I hope that question was answered with that I hope you were also able to see kind of the progression that we've gone through over the years when it comes to data access in Java and squel uh if you're interested in more of a deep dive into some of this please let me know I'd be happy to do it uh but I hope you learned something new today if you did Friends do me a big favor leave me a thumbs up subscribe to the channel and as always happy [Music] coding

Channel: Dan Vega

Views: 9,860

Keywords: dan vega

Id: qLDrfebeXS0

Length: 28min 53sec (1733 seconds)

Published: Thu Dec 07 2023