Spring Tips: JDBC

hi Springs and welcome to another installment of spring tips and listen summit wouldn't look at GBC yes the LBC the basic driver layer the database connectivity the java java database kick to delay that we can use to talk to secret based data sources and I mean everything right as my Mexico server oracle post graves my sequel the sequel light you know Microsoft Access I mean Informix Sybase h2h sequel Derby everything you can imagine ever wanting to talk to us in terms of a of sequel you can support to this obit kritis and very well known program model now while JDBC is youthful Roth's very powerful it is not of all the things that it is it is not a convenient they use and this has been true for 20 odd years so that's okay right there's a lot of ground above it on which to to provide simplicity and power and so the ecosystem has sought to fill that gap and when the earliest and still most diverse was almost too powerful entrance to that space I think is is spring Spring has I think some support that has actually been around since the very beginning of spring it's one of the earliest you know things that we did in spring was to simplify working with JDBC and for a lot of people including myself yours truly it is one of the first thing that we saw as so the you saw in spring as is representing utility that we would want to to need to spring a week for spring to be able to use right so JDBC is a an old topic that there is a seemingly no end of interest in it and naturally and there's a lot of really useful technologies out there that make it a powerful place to a powerful way to build applications and so today I'm gonna take up time and just look at a number of the different things that are supported in basic spring and and some of the ecosystem so I'm gonna build it spring the application we need to talk to my sequel I've got in my secret ladies running in my local machine here so we're using my circle use Lombok to make a short work of cleaning so DTL entity type records today or a plate am an application that uses the jb support and spring and spring boot itself so I'm gonna bring that in and that's it for now we need my sequel I'm using a few of the default check boxes here there are a few options here there's a you know nobody whether different J bases h2 is supported for example post-crisis is there as well let's see if Microsoft sequence over there it is as well so there's a number of different options that you could set there the box but that is by no means the exhaustive list of things are supported by spring obviously these are just a sort of useful things out of the box let's go ahead and throw the Downloads directory here I think I already have a duplicate directory so goodbye to all that yes good take to generate and there's my a zip file so it would open it up in my IDE and we can go ahead and go to some publication that talks to to my sequel now again I've got that in my local machine that's running under the host serum which is running on my local machine and so my sequel you see RM h h is equal to CM p and then serum p is the requires me to send a password right so if i do that i am gonna show tables drop table customers drop table orders alright so there we are so show tables nothing left now we're gonna build an application that can talk to my database and in order to do this i am i could create a data source manually right like this I could actually go into spring and create a beam of type of data source like so and this dailies could be you know it could be a data source that talks to the database now naturally I know for me to be able to do that may know that I have to get access to the types here so I'm going to get really get rid of the Maven runtime prefix I'll save data source and I need some sort of data source implementation well of course here I could use a spring framework a simple driver data source for example like this and this is great for development SDS whatever like that new simple driver data source and it will need in order to do its work it'll need the driver type so we need to try my sequel driver here so I'll say driver equals new driver and it will of course need in order for it to do its work it will need let's see it throws an exception they don't need some configuration as well so drive it out to set whatever connect your L properties blah blah blah and you know you put that in there driver and then you put the URL user password etc etc etc and then return SDS and if you get that to work you're still no one even close to doing it the correct way right so what we have here is a simple driver data source the driver did the drug data source is a data source a random driver a driver is what you normally it's the low-level way to talk to a sequel database a driver will get you a connection if you connect like this right so you go to Yale and give some properties and it'll give you a connection in Java juggs a sequel connection like that and those connections are expensive so this connections are things that you want to retain you don't have to recreate them each time this simple graph data source is just that it's simple it's meant for development really and the first thing you should note is that this is not a natural connection pool it does not actually pull connections it just serves as a simple replacement for a full-blown connection pool implementing the same standard interface but creating new connections that I recall the point is if you in a test your development this is a okay idea but as this documentation spells out very plainly if you need a real connection pool outside of a Java EE container consider using Apache comments dbcp or c-3po or you know the Patchett Tomcats got a connection pool or a call ECP so this has been around for a long time but it is not by any stretch what we're going to be using so we would want to configure something more robust like Hikari CP or Apache Tomcat Tomcats dbcp or whatever um no yet just let spring boot do it so again in spring boot will do it for us if we ask it nicely and give some properties if we had on the class path they had on the classpath h2 or si your H sequel it will automatically create those data sources for us so we don't have to do much to get that to work but since we we don't we're not using one of those invented ones we're going to create our own data source using properties so we doing here we can see that we've got properties here like spring that data source at URL equals and here we need to tell it where to find our our my sequel data source and we get give it a user name and password as well so springing that data source that URL user name data source dot user name okay so let's see password so it's CRM CRM and the URL is JDBC : my sequel colon forward slash forward slash localhost 3306 CRM and this part always gets me use SSL equals false they added this requirement a few years ago if you're for development mode if you're not using SSL connections and you should but if you're not for your local development then you have to say user to use SSL equals false use SSL equals false I need a host as well that's in the your other and we want to specify a cross name driver class name in the class name will be equal to call my sequel JDBC driver and behind the scenes a connection pool will configure this for us now in spring the prior to Toledo the connection pool is Apache Commons DB as it was a Patrick Tomcat connection pool which is a great choice but it we've sound a lot of users are having better luck with a car CP so we've made that the default in spring we think it's faster and it works better so you you will not notice this if anything you'll notice that your applications are faster right and that's good the other thing is that we want to initialize some data so we're going to give it some default schema and the default data sequel file so I'm gonna go to steam it up sequel and I did it at sequel file and spring to automatically initialize the application of the data source with these default files but we have to tell it to do so for every kind of data so it's not just the embedded types right so always embed it or so here we go I deleted those tables he saw me delete those tables so now we need to create them manually so I'm gonna create a sequel file called schema that sequel and well go to my desktop and you can see I've got copy that data sequel downloads JBC source main resources data that sequel copy schema that sequel downloads JDBC source main resources still my dog sequel okay so I'm just copying those two files here and you can see the tables that are created are very I'm very simple right the I've got two types I've got two tables of customers and orders the the two types of tables are related to each other in that a customer is a one-to-many side of a it's the one - a one-to-many side relationship orders is the main side of the one-to-many relationship so there are one customer and 0 to n orders that is monitored by the foreign key and you know that's it it's very straightforward I'm using my sequel there are some nuances with my sequel that are a little different in other databases perhaps so you might use serial and post cars for example all right so we have those things that spring boots gonna automatically click those tables for us is gonna learn that schema for us when that vacation starts up now this is probably not something that you want to happen in production for example so you might actually want to say you know you might want to put this attribute here in a you know application - dev that properties and then make sure that when you start up the application in your development environment you activate the spring profile called dev so that way it only gets run on dev and never in production back set because it'll - you know the schema will destroy all the existing data which is not a good idea indeed you know anybody step further you might want to take all that and then go step further and use something like the liquid base or flower both of which are database migration tools so as you evolve the schema you want to make sure that you have the traceability to see what steps what changes have been made to the schema and the corvo's has ever new basically one that you know migrations that go from one schema state to the next and you know that way you know your developer environment has schema up until step five but your production database only have scheme up to step two and so the Davis migrator will know that the current state and the production database is two and so when you deploy your application for the first time and apply steps you know or my goal or what they call migrations step three four five right the result will be that your system will be brought up to about up to two to the current state of the development code and that gets run every single time and so there's also nice you know some of them over reason why they give you ways to model rollback and so on so what a really good support there now that's perhaps a video for another time for our purposes today this will this will get us what we want now that said what do you want take advantage we're a puppet you should feel free to take advantage of your IDE support for different databases my entire jail has pretty good support here for working with different databases it even has a separate product a whole separate products that you can buy or I think there might be an open-source version I'm not sure but there's certainly a version that you can buy that will that will let you work with your different databases so I've got a let's see CRM password test good so now I'm connected and the result of that is I get a console here so I can say you know show tables run that you can see there's nothing there alright so let's run this application just run it doing nothing else just fund the application all right and when I go back to here to my sequel console database tools console tip and I want to run the out yeah create game there we go so I can see I've got those two two tables there and I've got some data right so if you just look at my schema I've got this the definition of the objects India and the database and data Ivan about what is it now one two three six records and I've got David I being written or is being written for myself Josh for Jane and for Bob and for Michelle so Michelle has three Bob has to obtain has two and Josh has three records okay so some of them have zero that's very important we'll come back to that later now what we want to do is we want to exercise you know to be an application that takes advantage of some of the basic support in Springfield itself so we're gonna start simple go with what we know and and walk our way up through more interesting more sophisticated more perhaps exotic offers options for the JDBC accessing spring get over when you do a lot of similar demos but we're gonna do you know I'm gonna duplicate I'm gonna write a lot of similar things in the same code page here so I'm gonna create a really neat ility here just to write out the line to try that alliance that we can see where the output we are so line and all we're gonna do is just use the log4j longer there I'll say it long touch info and I'll just print out a line all right there we are very simple now let's say that in our first example we're going to have a application runner that's a beam that will start up and it will log out information in the database we're gonna produce a report so I'm gonna create the customers and orders count and it's going to be an application runner I you know this should be it could be a unit test or something but we're just trying to demonstrate how different things work so our application runner is going to take advantage of this case the JDBC template now the JDBC template is the I think one the most interesting sort of objects in the JDBC is support and spring been around forever in the day and it makes short work of a lot of the things involved in working with sequels so let's there's a you know if you look at the spring documentation here spring that IO for side projects floor sash Spring Framework look at the data access documentation go to data access with JDBC you'll see this amazing rubric this table here that miss all the things that are required for any interaction with sequel with a JDBC sequel connection and resource when you're working with JDBC and all the things that you have to manage they have to define connection parameters open the connection specify the sequel primers or statement rather defined include declared parameters and provide parameter values prepare and execute the statement setup any set up and loop through the results of any do the work on each iteration processing exceptions and all transactions close the connection statement end result set not to mention the transaction you have to come in it so analyst stuff is stuff that has to be done well of course spring makes you know all but for those things go away and it calls you back or the possible for the things that you really should be involved with right so things like specifying the secret statement that's naturally very important that's the thing you're here to do right is to provide a custom query you're here to define parameters that go into the sequel proto statement fair enough right and we are here to do the look obviously the defining connection for each planners for the defining the connection parameters we've only done that like we did that here that was I'm not gonna say it was easy but it wasn't hard either like I mean you know the hard part was actually setting up the database itself in my sequel certainly not this right so we have a JDBC template and it's you know it's gonna do a lot of things that we don't want to do so let's say stream line utils dot line good and what we're gonna do is one of create all of the data in the database and generator of pork and the report will have information about each customer and Ali orders are assigned to them so public static class customer order report and we'll have the string name email private int order count and the long ID alright so that's the what is that the customer ID I guess we could call that to be very specific so they were putting a detail around this report data now in order to generate that query I'll have a query that looks like this select see that asterisk and I'm going to select the a sub query have sub quick that counts the the number of errors for that for that a customer so select count oh that ID from orders oh we're old at customer underscore FK equals C ID write as count so let's see what we get so now I'll end up sequel statement using the JDBC template so here TVC template that query we don't want to get we don't equate for the whole object there's a whole bunch of methods here by the way so I'm going to cover some of my favorites but most of the time I'm using query and sometimes I'll use update or execute but there's a whole bunch of variants at them so we'll talk about some them today so we'll put this in here and the second parameter is a real mapper now a real mapper is what real map is useful if the if the data that you're returning if the results of the query that you're returning map one-to-one to an object so if each record in the result set maps to an object then you should use a real mapper right because you can map them directly to objects so I'm gonna get back a collection of customer order reports like this and in order for me to get back get that back merit look at how smart until it is it's now aware of the fact that you know I'm making a query against my local resource so I can actually click on for example Oda customer to F ok or this kind of thing right here and it'll be smart up to see what I'm trying to do so what is it saying I don't lose all customers why do you think that is easy it's pretty smart Oh Kate a second I guess let's see close that down and we'll open them up again hmm well guess it's a I'm not sure it doesn't matter either wait musics pretty smart and you can actually come and click on tokens in the sequel statement and it'll it'll know what you're trying to do so now I'm given a callback each time it so it's gonna visit I'm gonna give the crate and then it's gonna loop through all the records and it's going to call my whim ever back this is an interface right and the interface is a back that's a each time I get a result set just in the past the result set plus the current row number and I can do it if I want with it but it wants me to map that into a custom order put so here I'm gonna say alright you know the first one the ID that comes back let's call this ID the idea that comes back and then the artists get string name and all that gets string email and then honest get wrong count all right so we can call this tally if you want doesn't matter but it has to match whatever we specified up here okay so with that done one thing to keep in mind is that this API is super old alright this API was look at this this was written originally 2001 so this is a 17 year old API and yet it works even better today than it did when it was first conceived you see these car back interfaces this is a template object right after the design pattern the template object and basically fill it you know you fill out the parts that matter to you and it does the rest including all the transaction management resource the initialization in that position and destruction all that kind of stuff is done for you and the result is that you just played in the parts that you care about well historically prior to Java 5 sorry Gadwal 8 historically that that would have been anonymous interfaces like this oh now these interfaces lend themselves to lambdas which i think is a really really convenient so what do we have here inte all right look at that it's a couldn't you know couldn't have ended up better I think it's just really nice that this API just works so nicely with this uh with lambdas in Java so now we have the reports and we're gonna say for each and we're just gonna print that each one all right I'm gonna use a log4j logger okay this is Lombok I'm using a loan book to to synthesize these fields at compile time and it's as though I've written them myself okay let's see what that gets us let's run this application all right didn't get this there's our six records alright so there we got the records we can see that's working we can see I can only count to three etc right good very simple you've probably seen something like that before let's go a little bit further and now let's do a one-to-many so this is actually pretty simple each record that we got back in the result set mapped to an object so that was simple now we're gonna do a one-to-many where each record might have to roll up into a aggregate our customers are going to get all the customers and all the errors and we don't want to do something inefficient we don't want to set all the customers and then visit each one in a loop and then do a select all in the database that has for all the records that max out that'll be very inefficient we do we went into the n plus one problem so rather than do that let's do a create customers and orders example without the count so we've said we're gonna actually get the oil the the orders themselves okay and same as before when you use the JDBC template okay and we're gonna use the JB's template to make the call up to the database so we want to say string utils lines okay and we're going to say this that Jamie's tell it that query select see that I D di as CID Oh that ID as oh I D and O that asterisk didn't want to see that asterisk why not see that asterisk from from customers see left join orders o on see that ID equals o dat customers FK alright so we're doing the left outer join here and we're gonna get back you know if you can imagine it we're gonna get back you know let's see we're gonna get back you know row you know Josh josh Josh um you know and then let's say one and we'll have the it'll be merged together with the results for the order so it'll be ABC and then you know OID will be equal to 1 etcetera so I'll have that and then the next line will be largely the same except this will be 2 and this will be d e f and the next line will be G you know F G H I etc so all the information from the customer is here it's on the Left column short of last two columns but everything after that is the order ID so what wouldn't do is women do a left outer join which means that we'll get at least the information from this once and this might be null if there's no orders if there are made in one order if there's more than one order that will have duplicate records on the left right all these all this information left is duplicate but we get their real information that we care about the orders here now as we get to another record like this then we'll start to see you know the primary you know this might be will loop what was it Jane I guess so Jane and Jane Jane calm write cetera so it'll be James information and she might have you know different orders or whatever so and there might be the same skill okay so in this case we're going to loop through all the records and whenever one of the records is whether whatever the ID of the customer is different as is here or break we're quite a new aggregate object and accumulate and end and the meantime as we're going through and the idea is the same we're going to cumulate all the records into the aggregate that we've created so i'm going to create an aggregate starting starting here aggregate i mean i'd get the king the credit the orders aggregate again and so on all right so when go down the line and tell eventually we have all the data in a in a collection of information well that means that we have to be able to iterate through the different records until we can meet a certain criteria which means that the is that the real map approach that we used before it doesn't quite work here we need something that gives us the ability to iterate over the data so here we use a result set extractor okay so i'm going to create a result set extractor to create a collection of customer records so we need a customer type i guess we can use this as a basis but it's not the final representation by any stretch can't customer and what just the ID and we need and so the talent we want a set of orders some orders equals new hash set [Applause] public static class order private string private long ID private string still skew all right good for that so okay we've got the customer we've got the order now now we've got those two things and what we want to do is when I could it was extractor that visits every single one of those records in influences logic that we just described here and is a little confusing but I think it's kind of easy to understand when you kind of see it in action so first things first we start the while loop right so let's type the while loop here Wow our dad has next let's visit each record now we have a condition we have to accumulate so let's get the current ID ID equals honest that get long and that'll be the customer ID sub C accumulate and now we're gonna say while our now stop next and then we have a condition we say if the current customer which we haven't defined yet so we're gonna create a current customer let's just say this one if current customer if the current customer should equal null or current customer get ID does not equal ID right and then for this to work we also need to order this by ID don't we so we need to see ordered by CID that way it's always gonna be in that order so our se is that we are okay and that gets us by the way this will give us a customer collection just as we want okay and there's our there's our and told you you can kind of confusing me take okay you know we have this and now we're saying if this has been null if it's null or current customer does not equal ID then we paid a current customer so current customer equals and we have to create a new customer out of that result sets our new customer and we're gonna give it a result set we have the information here so honest get long CID RS get string name RS get string email and then a new hash set for the orders I'm going to add things to that as we go through now current customer dot get orders well we have to map the current information if there is any for that record so when a we're going to create a order once we have our order so we have to actually get the order out of the current result and try and create it's our new order and the order is expecting an ID and a SKU so let's do some defensive coding here we're gonna say c RS get long o ID and string SKU equals R in stock get string X Q so now we're saying create the OID and skew so if all aidid is not equal null all right always true probably not always true all right I'll get long good throw an exception I suppose so either way we don't wanna worry about that but we want to add these things to the to the current collection so we wrap it in order and basically want to create it only if it's valid right so we're gonna say now current customer that get orders that add equals order right so they're moving through your data and we're making sure that as we add data that that when you accumulated into the orders collection then now I want to steal all the customers that we've got right because we're going to we're gonna eventually hit this condition where we have to reset the customer so I'm gonna save the customer often to a somebody get it later on so I'm going to stir it in terms of its in terms of the primary key for the customer there and we'll come back to it later so customers map equals new hash map remember to changes into a concurrent map if you ever have to make this multi-threaded and concurrent I can custom a map and if that's not normal blah blah good and then custom a map so this is gonna we're gonna hit the same path again and again multiple times so in theory want to call put those many orders is there all which might result in some we've done inputs but again it's by key so it'll never get it's not one to something we're gonna add you know more customers or the same keys so uh well take advantage of that later on alright so there you go there's our our current logic that's our you know and then funny to return the data I guess a return customer map dot values that gives us a collection of customers all right so what is it wrong with this while RS that next oh I don't know looks like it should look to me hmm well I don't know intelligent will have to disagree agree to disagree here my friend so we've got now this while loop and ah that's why it doesn't loop yeah you're right so get rid of that and we have now our simple logic our simple logic that uh well accumulate everything for us now again this is pretty tedious it's a little complicated but it sure beats um managing all the transactions and doing this right so at least we're getting some gain out of this and again this also lends itself to being a lambda so we write that a little bit there you go there's our result set extractor and we're gonna use the result six result set extractor here to get the data so now say customers that for each log info then again we left the logger and we get the logger by log4j to okay so there you got let's see what that gives us and Mariano start ordering these so that we can notice see the latest in greatest last since we're having multiple these application runners so let me go back and make this number one so it starts up first released reset the application and what did I do see I'm customers doesn't exist did I do that I don't know Oh kutiman haha okay good so intelligent wasn't crazy it's me I I this is now twice that I have second-guessed Eid tonight and I've been wrong that's not good orders well that one's clearly there let's see if I doing something silly here left joined orders oh oh let's try it again source main resources data its sequel schema that sequel sexy IDs see that I repeat orders OID okay left - an orzo uncie that ID goes oh that customers FK so data schema customer FK and my friend Lucas editor he's the creator of Jacobs the driver object-oriented query API I'm sure just laughing at me now saying that this is exactly why you should used typesafe sequel as as enable through Java or queue and he might have a good point there so that's a good reason to go see the other spring tip video I did on that and also my beetus so it's just another sequel centric or M type thing like my like a like Java OQ alright so see that ID a CID okay let's see if this gives us the results that we're expecting always use your tools when you can that looks like it's gonna work that should be fine so yeah that should be exactly what we expect so let's go ahead and now run this code again ha ha they cool so there you go those are there's the results that we expected except what happened there are is e so in the loop wait a minute where's my sequel statement here run this again yeah that looks right so we should have we should have oh I've done this twice no kidding okay somehow I did that twice in there okay so we were in this program one more time that she looks and when it was a little tease okay oh there we go look at that so now we have six records as we expect that some of them have data and some do not so orders as an order with to 0 and no why is that so when I say if Heidi's not equal to null and look at the results from this close that if the customer FK is no let's try that one why does it why is the IDE so sure that that should be none oh okay i D all right ah let it go much better okay so we can see empty orders empty orders but we have orders for the rest of them okay so now we have a very simple example we have a one-to-many relationship and yeah that was a little tedious it was a it took a little mental gymnastics to make that look but we understood what we're doing and then it's far cleaner than if you had to do it by ourselves manually so I'm glad we had the opportunity to do this because it does demonstrate what could be worse now that's said this is not the only way to do this actually there's actually some some room for improvement here even within this example so let's take this example one step further I'm gonna use a third-party ecosystem library called simple flat mapper okay know this let's see this example we're gonna name simple flat mapper let's be ordered three same basic domain model we're just gonna use a third-party library here called simple flat mapper and I don't have it on the cross path just yet and it is unfortunately not a checkbox for it now simple thought mapper is a product worth mentioning it's a third party project that um that allows you to map data via you know CSV data X and all data whatever sequel data and it integrates with a number of different interesting libraries out there so and you know one of those integrations of course is spring so what is it 3.1 8.0 I think there we are so we're gonna use this and we use a simple thought mapper to make all of this terrible code to go away right so it comes much simpler the code is now just a few short lines what we're gonna do is we're gonna replace all that logic all that manual accumulation logic with a result set extractor that's created for us using simplified never so RS e equals JDBC template Joosten was a mapper factory dot new instance dot add keys and the key is the ID that's the key that we want to break on right the accumulation key and then the new results that extractor will be of type not user customer duck class all right is that right looks right new instance is it referencing the right customer looks okay to me well it's an implicit okay it's it's from that one in particular is it not yeah result to extract her other term spring okay and basically I can I have to rewrite the quiz just a little bit here to reflect this you know to reflect the fact that it's being managed for us by this convention based thing but it's not a big deal so what we do is we say select CID as ID and let's write this create a little bit here sexy IDs ID see that name as name see that email as email old a tidy as orders so again the collection you know the one to many parts the many part is the collection orders ID of each order is called a ID so it's orders underscore ID o dot SKU as orders SKU and from customers see left join let's join orders o on C dot customer FK equals o ID order by see that ID alright next it looks fine to me let's try this now and we should have this give the same results as we had before let's see we're already wrong see oh sorry oh that Oh that equals see that I D get those things backwards good look at that not bad did I actually yeah I did so if you look at this we can see that in this case it wasn't so enough to create no order to empty out the rows right so that's a little bit of a bummer you can actually visit the records and and see if they're if they're no for example let's see left outer join does that make a difference I wonder if it makes a difference here now it gave us Knowles which is a bit unfortunate but again you can you get 99% that code there automatically and all you'd have to do is to visit the results and then send that back to the customer right so I could say customer thought stream dot map and you know see see that get orders stream you know you can replace it right so I'm gonna you're gonna filter each one like so then you're gonna say I'm C dot orders dot stream that has or count right you'll see any match oh oh that get ID should equal null right boolean has no values if has no values you know return not has no value so basically we're gonna say keep this filter actually we can even do a filter so if it's if it's not null then we keep it otherwise don't so we get the result is a stream that we can then collect collectors that to set put that into a set of update it's updated right and then that becomes the thing that we actually keep so here's the updated if you do that right no map has no older values okay let's see if has no order values then see that set orders is equal to new hash set there we go that's much better so now I just returned to c return c good so that's cleaner okay the result of course is now I have an updated customer set and we could even just name it customers that's a little dirty but again nobody would you know that would hide behind code that nobody else would see and it's still cheaper than doing an N plus 1 query that kind of thing there there so now empty ArrayList okay very very fast now we have a couple of different ways to write this data and to see the results that's fine and in this case there's very less complexity right the real meat of the processing is there and this is you know in some cases is actually faster then for example a handwritten query because it's doing a lot of up short compilation that kind of stuff so it's a you know reasonably efficient it's a when all things are to do that to cost benefit analysis they have to engage in to determine whether the practical gains are what the performance gains I hope you'll err on the side of activity of course now we have them we have read some data let's look at look at the look at reading data so here I'm going to use it we've read there let's not look at writing data so JDBC template changes in template customer service let's do JB Simplot writer there we go implements an application runner and here it's going to be a component or even better yet a configuration because we might create some other beans inside it'll be a configuration type have an order of what do we say the last one was 3 so this before and it'll have a logger alright so there's this and in order for us to do this we're going to need to inject the JDBC writer it's a private sir jvc template all right add the constructor arguments and here we want to write some data to the database I'm going to create a service that we can use to actually write data to the database so actually we can we want to create a service here in so public static class customer service okay and all we're gonna do is we're gonna read some date to the Davis and prove that it's working so public void insert string and you know we can even do this directly let's tease of jbz template directly first jvc template the constructor arguments and here what we'll do is when I say this but JDBC template dot yeah uh not execute right well I'll update some data so I want to update and here we have a number of different overloads but we want to put access to the generated unique key so we really can only use this third one here so we create a new prepared statement creator and then we use a generated key holder all right generated key holder okay and the key holder we access once the right has been done so here we're gonna create the prepared statement creator sake on that prepare a statement insert into customers name email values question mark question mark and I say what I write the statement and when I get the return when I return the generated keys and then we want to set the premise on that prepared statement so prepared statement is a compiled statement that's been compiled by the engine by the driver by the sequel server engine you know something is compiling this so that we don't have to so that the engine doesn't have to do all the syntax tree parsing of the sequel query right now like as you can imagine with any compiled language of with any language in general there's a preparation phase there's a compilation phase that looks at the string and turns it into you know ast is abstract syntax trees and then turns it into op codes and then turns out into bytecode and and then caches that somewhere right this guy could get disturbed somewhere so that they can be easily we've we went to memory and in this case it's stored in the sequel engine so we want to this statement this here it is is time there all the engine please cache this and you know that way we can just we issue the query but only provide new parameters now that we don't have to recompile everything so I'm gonna say prepared statements set string one and then the first string will be you know the data that will run right so again let's just imagine that we had a you know it's actually did we actually do that we can actually create a service method here public void or public customer and we want a customer here from let's just do this here we'll ignore our orders for now okay there's our customer type and want to have a public void sorry public customer insert now I'm going to say string name string email and we put this all in here and now we've got that generated key holder and we need we have we have it parameters here so the string the first string is the name right and then the second one is the email so there's this and then funny we return the prepared statement so we're creating the prepared statement creating the parameter setting the parameters and then returning it and this of course is a nicely written as lambda and what do we have when we do that well we don't need an actual update call we don't need the this is the number of rows affected we don't actually care about that what we care about is the result of this generated key holder so we get the key which is the number I'm going to turn that into a long value ID of new customer and then we can use that to look up the record or or do it every one right so with the usually you could easily just say you know return daviesi template query select C dot all from customers see where see that ID goes question mark and pass that in and then of course them the row mapper so new row mapper of type customer and we have a heavier arguments and the arguments are created for object and the parameters go here is it the I've never served as the second one or the third one okay so ID of your customer and you know same ones before right same as before so let's don't get long ID Horace target string name is that right yep and then artist art gets string email alright so there we go and that's a lambda nicely done okay so the results is that we can now make and now um red dates of a base and get the result back so let's try that out in here okay so stream dot of a B see that for each name and we can like did it today so I can say insert and log out the result of course insert name name at name.com alright so we'll just when we read this we should see then we should see what we should go to get all the data that comes back from the database and say this time JD's template that query select all from customers and you know at this point I've got two of these but I'll just reuse it so private real mapper customer all right good so now there's this customer role mapper and custom a real mapper that for each log info all right good let's run this and see what we get private final good ah you can see that right Earl and I did forget something quite important didn't I forgot the string utils line all right there we go so they there those are there we are our our existing six right there in no particular order it seems I'm seated what I have here what do we have customer order by ID that's better looks no it's not actually what's going on there we run the application and we have seven eight nine why don't we have seven eight nine twice oh because I'm printing out the inserts right so here I'm logging in the inserts we don't need to do that let's just get rid of that all right so we know the insert we know that it worked oh we there we go so there's the the nine that we expect just the nine and if I restore that code that we had before you can see that I was able to get access to the newly generated key the key that was generated from the other one commit column there as well so you know we could say a blog that info results whatever okay now there you go seven eight nine these are the newly generated IDs all right so that's working good so now that's a very simple example of using an insert so we're doing you know all the sub stuff around generating the results and confirming what we gotten you know you've seen that before right very simple example using the JTP template now the JDBC template is a like I said it has a nice way to write data but it's very much this whole API is centric around jb's template and you have to remember this recipe right this whole all of this all that this whole recipe for using the JDBC time but you have to repeat it each time you want to achieve the same thing with the JDBC template it's nice to be able to wrap the stuff inside of an object and then be able to repurpose that because most people don't to have to remember all of the different little things I had to do here to get this to work there's one build to instantiate something or call something and and get your your game at that current they get the data back that you are advertising is available and so for this spring has a slightly different approach there's actually two different hierarchies if you are choosing a approaches to two right using the using spring framework and the second strategy is around objects so we've been using the JDBC template which is in the which is in the spring framework JBC core package that's hard JDBC package let's see is it core Oh what Dave's template go it's here in jbz core right but there is a whole other package called object and that other package lives here and here you can see those wrappers around operations like query update so procedure sequel calls sequel functions sequel queries etc and these are basically you create objects that wrap the the incantation that you're trying to do the operation that you're trying to do and so you can create even factory these different objects and then reuse them so let's do that now okay so instead of doing this let's just copy the skeleton of our previous example here by the way are we still in four that's four okay now five and here this is going to be using T the object jvc object writer okay good paste that in there and everything else kind of the same except for the insert okay and the Creator that we're going to read and redo the query as well just to demonstrate two different ways of achieving the same thing here so that's some revisit all that in a second alright so now we've got the insert we need a query okay so the insert we're going to achieve using a simple JDBC insert and that's an object that's actually a thing that we need to to manage right so we can create it so private final simple JDBC inserts it's going to require data sources in it so I'm going to need this I'm going to say simple JDBC insert equals new simple JDBC insert GBC template data source or we could just check to the data source itself which table name is customers and we want to use generated key columns and the generate key column is going to be called ID all right no way to store a reference to this for we use letter that's the note that's the nice thing about this is that we can reuse these references right that's the these are meant to be persistent and reused over time so got that got that good now we addressed the sequel operations and the sequel operation is fairly straightforward actually it's really straightforward so we could have map containing the string and the object params equals new hash map name name and I always with Java 10 fixes that that can actually there's a builder for maps and other things but in the meantime I'm back in the past so name name email email and he's met the keys are the names of the columns in the sequel table all right and so what I'm going to do is I'm going to use the insert operation this got simple JDBC to insert I'm going to say execute and return key using these parameters and the result is the ID right so we can say long ID equals the long value and the result of that is what we're gonna return you know we're gonna use that to return the customer now let's talk about getting that customer let's talk about that now so we're going to use now a different way that's we know we can come back to this a second I suppose we're gonna get a we're gonna retrieve the customer amazing a sequel a mapping sequel query okay we're going to create a generic we're gonna sub class mapping sequel query to parameterize only the thing that matter to us we're gonna we have to use cases here where we want to get all the records want to get one by ID and when I get all the records after this like as we did before right to do all so we locate that subclass and that will allow us to to do that easily so private static class customer mapping sequel query implements the mapping sequel query from spring not from the simple flat mapper gonna map to a customer and in order to do this work we need to you know that is before new customer RS get long it's ID honestech is the strings name is to get strength email and that's it I suppose okay that's the first bit that we care about extends rather first bit that the camera is that but we have to also configure the datasource a sequel statement and some sequel primer so I'm gonna create a constructor that has all these things that we care about okay so datasource TS string sequel and sequel parameters params alright so it's set data source equals datasource sit sequel sequel sit parameters is equal to params and we need to declare the parameters there you go so for actually you know what it looks if I said set parameters but actual works so something like that and then funny we need to compile it right and this is actually usually done for you if you call after property set screen will call ft property set if you declare this as a beam right so maybe it's better just to do that but if you manually instantiate instances of this class and you don't then spring manage it you might forget to call after property set and that would be a mistake it in turn calls compile and you need to do that so I'm just gonna call this by itself directly but you know you'll probably you're probably met ''tis as mad as being as in spring so with that done now I can create them I create instances of them to return private that's a private final customer mapping sequel quiz all will be a new customer mapping sequel query this will just load all the data so select all from customers and the datasource we're going to inject as well so I guess we have to do this in the constructor and by ID this got all equals new customer mapping sequel query des and select all from customers all right and this that by ID equals new customer get data source select all from customers where I'd equals question mark I'm going to find a new sequel parameter here this is a wrapper around the type the declared type there so we give it a Java types is it yeah type step into drift and that's our implementation and that's the instance incident that we need the instance that we need and that's it that we can reuse those now most of the knowledge of calling those queries of running this quiz is now encapsulated in those objects those are command objects as well so we're going to stir that up here good and let's reuse that so here we say this dots by ID dot find object passing an ID good that was easy and for this we say this that all thought executes got for eat long info also very easy and you can again you can easily see how you could create you know for things that's slightly different you would probably even create some classes of this that already had the data sort you know the constructor just contained the data source and then the sequence statement and the parameters were just done for you automatically right so you'd have a customer mapping sequel that only needed a source you know one for all and one for a particular one for example and forgive it for for queries that are very different you definitely want to just hide that as much as possible so that way you just have objects that you can test easily all right now let's see what happens let's see what we get if we run this code again alright so again it's logging the data we don't want to do that again but it doesn't it doesn't hurt us to show that but it is redundant I think let's see if we get rid of this get rid of that extra parenthesis in the upper one as well already so you can see now we have ABC written again that makes sense right that's rule writing them again in the second application runner and that's working so we can see that the upper update worked the agenda BC object writer worked the query work thread the mapping sequel query worked obviously because we wouldn't be able to get this data and the first place to see in the console Ted didn't work so you know same same result but you notice that we didn't actually need the data source I used the data source to get reverence to the to the data source I use the JB's template rather together to get pointers to the M to the data source but I could have just injected that ardently I should have just heard you injected that directly right so yeah that's cleaner all the way much better alright so there we go much cleaner so this didn't need any of the JB's template at all you can encapsulate all that logic in these custom operations if you want operation objects and I I like that style I like this because it's you know you're encapsulating the business logic here as much as possible okay no so now what we have is pretty concise it's still a little bit more tedious than perhaps those who are accustomed to om software might expect so what we're gonna do now is we're going to take this up one step further and see if we can get the best balance between an OEM and our sort of low-level sequel based access we're going to use a brand new protocol spring data JDBC now this is by no means a GA project and I don't want to encourage you all to run out and start using it in here and your code right now but that's it it has been around for a while it's been a it's a project that was a incubated in the community and has been recently brought into the official spring data umbrella portfolio so we're gonna add this product to our our our apps are built here we're gonna add this product to our build the old-fashioned way using some dependencies here so SD so we need a few things we need the repository in order to use this so we'll add this here and then we need the version of the dependency we need the dependency itself here and requisite part of that is that the spring data released train itself has to be upgraded so we need to use a snapshot or at least a milestone we're just gonna snap track for our purposes we're using spring data lovelace so we're not using K anymore not springing out of K but spring that a level is the next release which hasn't yet been released so here we go Lovelace that'll do and with that we can now go ahead and build a repository so this is this should be very familiar if you've ever used spring data before repositories just take a object that handles the tedious you know tedious solonai letting any boring tedious a creation reading updating and deleting of up data and it's going to be a configuration class that we're going to have to configure first we'll bring in at enable JBC repositories and in here we can do a few things we load it we're going to create a a application that uses a repository so let's do that well create a repository at repository interface customer repository extends credit repository here when I have a custom top-level customer here so I'm gonna create a customer it'll be this top level one and they'll have IDs or private keys of type long and it's just copy any of these now I could you know I want to you know and it's consistent fashion with everything else else I've done here I would love to have all this stuff inside of an inter class but remember this is a bean that spring it's an interface that spring needs to find something better than just defined and so because that I keep it at the top level because it doesn't look for nested interfaces although it does look for nested components right spring will do that so we're gonna have this custom interface there and then custom repository and customer and and then that's it I guess we can actually create a beam of type application runner that will use this this data so let's see here class let's could this make this to configuration make this the JBC the application runner all right application runner and here we'll use the repository like so that component now what are we gonna do in a visit all the data that comes back and we can do this a couple ways we can actually let's do a well first what we let's create a custom query so let's do a custom query select all from customers C where C dot email equals you know collection of customer find by you know string email all right and our customer entity type a customer entity that we're gonna manage through a spring data you know look it's just like that except we're annotating what they had to bring data Commons ID annotation and that's it so let's see now let's go ahead and write them get it to the database here as before copy this and we're gonna say visit each one of those records and this that customer opposed to it save and save a new customer no like that alright and then I'll let the log4j bits a logger here I'm gonna say this customer Pastore that find by email and it's gonna be you know be at be calm and will visit the results okay then let's just confirm that finding everything should work as well like so they're cool all right we got it so now write the data out now we're gonna have an issue because the table doesn't match right so table see I'm a customer which is derived by the name of the type doesn't match anything in the database so we need to customize the naming strategy and here we can do that in the configuration like so naming strategy and we can just return to the fourth one save for one method which will override get table name okay I'm gonna say type dot get simple name drop to lowercase so fix it with s and return this like a semicolon okay now what's happening we need to provide the method name outside the primer name for a custom repository query fair enough so a plan email I think if we had done question mark and just omitted this it will worked as well based on positional proud you know positioning primer primer your positioning but um it's fine okay so there we go there's our result what do we say we want to look for and then I give this to write this is gonna be order what is it I don't know let's just say not seven I think it's seven five to skip one let's do six okay already what do we got so the last one is B right find by email oh yeah that's true cuz we've inserted later let's just try doing a unique one we've inserted three different ones in the course of this program but just to be sure save new customer null foo bar all right there we go so wait now fit up for foo or a brother bar you should get one record back hey hey look at that okay so they left that's a custom crave using spring data JDBC we still get full control of the quays I wonder if this works I wonder if it's smart enough to them and further query as it does with other spring data modules so let's remove the coil together and see if it if you figures it out based on the name of the method which is what a lot of the other spring data modules will do for you and guess not no creases yeah okay that's fine we can we don't want to give up quiz when we move to this nice you know paradigm of using queries so I'm good we now have looked at a number of different ways to use the low level support in spring for JDBC access and again this I think makes a fine alternatives particularly spring guide GBC particularly things like simple flat map but I think when you start to use this main level JDBC sees JDBC support in spring along with the the the JDBC template and projects like spring data JDBC you get a lot of your business you get a lot of speed you get a lot of the things that we want out of a data access technology without being encumbered with a lot of code I have written three hundred sixty three lines of code but again I've done six different versions of the same program right and it's still only three and sixty three lines of code you know it doesn't get much simpler than that I want to thank you for your time and hope you got something endless and as usual we'll see you next time you

Channel: SpringDeveloper

Views: 10,494

Rating: 4.7460318 out of 5

Keywords: Web Development (Interest), spring, pivotal, Web Application (Industry) Web Application Framework (Software Genre), Java (Programming Language), Spring Framework, Software Developer (Project Role), Java (Software), Weblogic, IBM WebSphere Application Server (Software), IBM WebSphere (Software), WildFly (Software), JBoss (Venture Funded Company), cloud foundry, spring boot, spring cloud, mysql, jdbc, postgresql, oracle, sql-server, SQL

Id: TUOwlaqZ0eo

Length: 80min 39sec (4839 seconds)

Published: Wed May 30 2018