What's going on, guys, Assalamualaikum. Welcome to Amigoscode in this video. I'm going to teach you everything you need to know in order to start working with databases. In this video, I'm going to teach you about JDBC connection pools, how to set up your data sources, flyaway for database migration, as well as how to use JDBC template, which is a JDBC wrapper for interacting with your database. So I know a lot of you guys have issues when it comes to this kind of stuff, and also because I don't think the right way for you to learn how to store data and manipulate it through, for example, JPA and Hibernate, I don't think that's the best way for you to learn, and instead you should really know exactly how to do things from the ground up and understand exactly, for example, how road mappers works, connection pooling transactions and all of that good stuff. If you need to my channel, go ahead and give me a thumbs up. So literally take 2 seconds. D smash that like button, literally just take 2 seconds and smash that like button because it helps me to keep on recording videos like this. I had a comment of a guy saying, if you don't, if I don't smash the like button, then that means I can't basically record these videos and think of it as a battery. Right? So if your laptop runs out of battery, then you can't really turn it on. So the same thing with me. So if you show that you like this video by giving me a thumbs up, that gives me a boost to keep on producing content like this. If you're not part of the Amigo Score community, go ahead and join both the private Facebook group as well as Discord. The community is growing over 30K people combined, which is amazing. And I would love you to see you there without further Ado. Let's take off this video. So for this video, the requirements that I needed to have is the basics of Java. I also needed to have a basic knowledge of SQL Docker, how to use IntelliJ and how to use postman. So these are the essentials and really basic. But if you're not sure about these technologies, don't worry. I'm gonna go step by step and it should be really easy to follow. And also you can grab the source code for the entire project on my GitHub. So before we kick off coding and diving into IntelliJ, let me go ahead and give you the diagram which you can find in the repo. And I'm going to show you this in a second, but basically here I just want to explain exactly what we're going to be coding today. So in here the purpose of this video is really to show you how to connect to databases and how things work. So I want to understand, for example, data source. So what a data source is. Well, a data source provides a way for you to obtain a DBMS connection. Right. And this entry points to a connection pool. Now, when it comes to the Java world, you've got two options, mainly. So you've got a carp and Tomcat CP, but a car CP is one of the most popular ones due to its performance. So in here you can see that you have a data source in here. You can have one or more data sources because you might want to connect to multiple databases from your application. Now the data source points to a connection pool right here. And because obtaining a connection can be a really expensive operation. So what the connection pool gives you is a list of connections that you can pick from. So instead of the client each time that wants to perform, let's say, a query against your database, it doesn't have to open the connection, close the connection and all that stuff that goes in between. Right. Because it's very expensive. So instead it actually picks a free connection from the connection pool. And in here you can see that all of these on connection. So here I've got six connections that point to the database. And then I've got three right here which are free and three. So this is just an example. But you see that here, for example, three clients using the connection, and as soon as they are done, the connection becomes free. So here if I delete this, this then becomes free, just like this. So this is the power of connection pools. And then if you have another client, then it uses any of these free connections. So also what I forgot to mention is within the data source. Actually, you need to define the properties that allows you to connect to your database. So usually the URL, the username, the password and the driver class name and all the parameters that you need. Then basically you have the data source, you have the connection pool, then you have your database. So for this, we're going to use Docker to spin up a Postgres DB, and I already have the Yamo file for you. So you don't have to worry about none of this. Whether you are in Windows or Mac, this should work. Then in here what we're going to be using it fly away. So flyway. It's a database migration and more on it later. But basically have Flyway or like base. These are the most popular ones. But just imagine the scenario where you actually need to make changes on your database schema. Right. So instead of you having a large file and basically apply that yourself and manage that yourself. That could be quite challenging. So you have flyway, which pretty much takes care of it. Then on this side right here we have JDBC. So JDBC, it's the Java connectivity interface, which basically defines our client may access a database. So again, more on this later. I'm going to show you exactly what it is. But back in the day, people used to use raw JDBC to connect to databases. But these days we have a bunch of wrappers that basically facilitate the way that we connect to databases using JDBC. So for this video, we're gonna be using JDBC template. And basically it simplifies the use of JDBC and avoids common errors, which is really, really cool. Then we're going to have postman. So this will be our client. And I already have a backend which is almost written for you. And the only thing that we need to change is the Dao, which has a bunch of unimplemented method. So to be honest, this is it. So you can see that it's not that difficult. And hopefully by the end of this video, you'll have a full understanding of how all of this works. So for this video, I already have this repo, which you can find in the description of this video. But basically this repository right here already contained some code that basically helps you to get started. But in here you can see that I've got the read me as well as the diagram in here how to get started. And then we have an exercise at the end right here. So what I want to show you is I do have this branch right here called Start, and this is the branch that you should check out in order for you to follow along. So check out this branch right here called Start to follow along. Literally. I want you to follow along to make sure that you understand what we're doing. And basically I just want you to learn from this video. So go ahead and grab this branch right here. And for this video, I'm going to be using IntelliJ as my IDE of choice. So I highly recommend you to use IntelliJ as well to follow along. I'm going to be using the Ultimate Edition, but you can follow along with the community. It will also work. So go ahead and basically open up this repository with IntelliJ. And next let's go ahead and get started. Let me go ahead and walk you through the set up that I have for you in this project so that you can follow along as quick as possible. So in here, this is a Maven project. And if I open up the Pom XML, you should see that we have. So right here I'm actually using Java 16, and I've got a couple of dependencies. So in here let's just make this full screen. And basically these are like standard dependency for building a microservice and restful APIs with spring boot. So here we have spring boot start a web. We also have started test. This is for testing. But then this is what we are interested. So here I've got the Postgres dependency, and this is because we're going to be using Postgres as our database. And then we have spring boot start JDBC. So this is what allows us to interact with our database. So this is the JDBC wrapper. We also have fly away and this is for database migrations. So for flyway in here if I open a project and then going to source and then main and then resources of look, there is a folder called DB. And inside there is another one called Migration. And inside there is this V one and the score and the score. And then in the tables SQL. So this is where we're going to add our SQL to create the tables in a second. I also have the diagram right here for you. If you have the Draw IO plugin with an intelligent you can actually open up this diagram as well. So here if I open, this is actually black. So that's why I'm using VSCO to show you in white, but it doesn't look nice all black. So here let me just close this. Also we have the Docker compose Yamo. So in here this is what you need for getting a Postgres database up and running. So all you do just run this Docker compose and then you get a database as well as PG admin. So this is the GUI client in case you don't have the database integration within IntelliJ. So right here I've got the database integration, but I'm not going to be using this, but so that, you know, you can actually access it through Port. So local host 50 50. And then you should see the good client. I'm going to show you this in a second as well. Then also right here I've got some volume. So this is for storing the data. If you learn more about Docker, I've got a brand new course on Docker that teaches you Docker from start to finish, allowing you to fully master everything you need to know about Docker. So go ahead and check it out. Link will in the description of this video. All right. So this is the Docker compose. You've seen the Palm, you've seen the DB migration, and let me open up the Java in here and then inside. I already have some code. And this is really straightforward. And I've taught you a bunch of times how to build applications like this. So if you learn about spring boot, go ahead and check my website, a free course on spring boot teaching you how to build this app. But basically what I want to touch is so in here we have the controller. And basically within this controller, we kind of basically we have the API V one movie. So this is the path we also have get mappings for all the movies, getting a movie by ID, adding a movie and deleting movie. And then here there is a to do, which is for an exercise that I want to give you later. All right. Cool. So from the controller, we move into the service. So this is following the interior architecture. And then within the service, we have the movie Dao, which is an interface in this class movie service. So this is mainly for business logic. So deleting a movie getting a movie so on and so forth. Right. So this is really simple stuff. And if I move into the Dao, so this allows us to basically interact with our database. So this is an interface. And currently I have one implementation right here, which is this guy. So movie data access service. And this is pretty much where we're going to learn how to use JDBC template to connect to our database. You can see I've left unsupported operation exception not implemented. So we're going to implement all of this. And if I open a project, so here this is the exception. So not for an exception. And here we actually have full control of the response status, which is 404. And then here we have an actor and I'm going to touch on actors later. But basically this is for part of the exercise later. But this is a record and also here for movie. This is a record. So I'm using Java 16 and I can use the records instead of having all of these POJOs with getters and setters constructors on and so forth. I can just have the records and do ten. So if you can learn more about records, I've got a video teaching you about records. And the final thing I want to show you is the application Yamo. So here application Yamo. So this defines basically some configuration for connecting to our database. But here I'll actually pull everything you need in order to get up and running with your database. Also, we have the server error so that this is when we throw the exception, we actually see the error within the exception. So to be honest, this is it. Next, let's go ahead and get a database up and running. We need this database so that we can then build the data source that will have the connection pool. And then we can use JDBC template that will serve as a client to interact with our database. So I already done the hard part, which is basically giving you this Docker compose. So in here. So inside of this app folder right here we've got the Docker compose Yamo in here. So this gives us a database that we can use to connect our back end too. Now the reason why I'm using Docker is because if you're on Windows or Mac, this should just work. That's the benefit of using Docker instead of installing the database for the appropriate operating system. So even learn more about Docker. I've got this course right here Docker for DevOps engineers. And here I teach you everything about Docker. So what it is how it differs from a virtual machine, the architecture, Docker, images, containers, volumes, Docker, compose, image tagging, networking, debugging, security, best practices, and a bunch more. So I'm going to leave this course right here and the description of this video as well as with a coupon code which you can use if you want to learn about Docker. But Docker is a must for back end engineers these days because it's really important that you know how you can take your applications package up into an image and run containers from it. So in here I do have this Docker compose that you can see here. And what I'm going to do is within IntelliJ. I can just run this Docker compose file. So here I can pretty much just right click on the Docker compose file. And then I'm going to say run. Now this will give us a couple of things. So if you have a look at the logs, it's creating the network, the volume. So this is where we can store the data and then Postgres. Right here we have Postgres and then we have PG admin. So this is cool. Now we have a database. So also here you can see. So this is a Docker compose for our app PG admin, and then we have Postgres and then within the volumes. So here if I Scroll down, you should see that we have spring boot admin as well as Postgres. So this is good stuff. Now let's go ahead and open up this database tab in here. And if I lapse this, I want to open up this database tab and then new data source and then choose Postgres. And then here we can fill in all of this information. Now let me just go back for a second. And what I want to show you is within this Docker compose. Have a look. We have Postgres user and then the password and then we also have the Port. So 5432 from the host maps t05432 inside of the container. So let's just go and create your data source Postgres. And then here let's select database. Just leave as Postgres for now. And in here local host 5432. The user is Amigos code and then the password is password. And then let's just test the connection and there we go. Succeeded. Apply. Okay. And we are inside. So this is nice. So also if you want to use SQL, you can actually do it. So here if I clear the screen and then I can type psql and I can say for host local and a host. And then I think between you Amigos code, enter and then the password is password and I think I got it wrong. So let's just try again. There we go. So I'm inside and yeah. So you can see that this is awesome. So basically I can use all of these commands in here and you want to learn more about Postgres. I've got a course about 4 hours long for free teaching you about Postgres. But L gives me all the databases and we have Amigos code and Postgres in here. So this is cool. Now you saw that I've used psql and I've also used this database icon in here, but if you don't have neither. So in here. Luckily for you, what I was telling you is that within this Docker compose, we do have PG admin. So here if I open up my browser and then type local host 50 50 just like that, enter and there we go. If you see that we have to set a master password the first time we use it. So I'm going to say password. I just want to keep this simple. Okay. And then you can basically go and add a new server and then connect to your database. So also you can write your SQL so on and so forth. So in my case, I'm not going to use PG admin because I already have this database client in here, which is really nice. There we go. So now we have a database that we can connect to. Next. Let's write some Java code that will allow us to connect to our database. Okey dokey. So we have the database app running. Now let's basically learn how we can connect to our database from our spring boot back end. So we need to create a data source essentially. And also we have to define some configuration file for how to access the database. So the URL username password tones or four. So let's go back to IntelliJ and in here let me just collapse this for a on and here I'm going to expand project. And what I'm going to do first is to create a package. So inside of Com Amigos code, let's create a package and we're going to name this as config. There we go. And inside of config, let's create a new class and name this as data source and then config just like that. Now annotate this with add configuration. And inside what we need is we need to say public. And then here let's say a carry carry and then data source. And I'm going to say a carry data source. And this is basically the recommended data source that Spring book says that we should use. So the way that we use this is by saying return and then here I'm going to say data source and then builder. And then here I'm going to say dot and then create. And also we need to say dot and then type and the type is Akari DataSource dot and then class and then dot and then build. So there we go. Now. One thing that we need to do here is here let's annotate this with at and then we're going to say bean. I'm also going to say that this is the primary and I'm also going to say that at and then configuration properties. And then inside I can pass the prefix. So just like that and the prefix will be. So if I open up application Yamo have a look. We have app data source main. Right. So this is the prefix. So app data source and main. And there we go. Now the reason why I have a primary here is because we can actually have multiple data sources. Right. So you can imagine you can have one data source that connects to a different Postgres instance. Right. So here I'm actually saying that this is the main one. Then for configuration properties. So this code right here expects some configuration, and I'm taking the configuration from the application of Yamo in here. And this is the key and then the value data source. And then this also isn't nested. Right. So this is the syntax. And then basically I've set app data source main. And then I've got all of these properties and you can see the driver class name, the JDBC URL, the password pull size 30 and then the username. So let me just swap these around. So username and password just like that. So I think it's just easier. And then you see that here we have the database that we want to connect to. Now for you if you don't have the database called Amigos code. So here if I click on database in here and in here, you can see one of two. And if I click on that, you can see that we have. So the default is Postgres. And then I have Amigos code in here. So you can connect to the database that it has created with your username or Postgres, or you can even create your own database. And then let me close this once more. And to be honest, this is everything we need in order to configure the data source. Now, at this point, I don't want to run the application because it's not ready yet. So we still have to add the SQL to create the table for our application. If you have any questions, please do let me know. Otherwise, join me in the next one. All right. So we have the data source. And this also gives us the connection pool. And I think it was 30 connections that we initially set. And now what we need to do is to use Flyway to apply our database migrations. So here we can actually use Flyway or Liquid Base. So these are the most popular ones. But I've used Flyway in the past and I'm quite comfortable with it. So I'm just going to use Flyway. But you could also use Liquid Base now to use Flyway as follows. So here if I go back to IntelliJ, I said before that we have this folder. So DB dot migration. So you need to have this folder right here. So DB and then this is actually a nested folder. Right. So you have a folder called DB and then the inside you have another folder called Migration. Now I already have this for you right here. So V one underscore underscore in tables SQL. So if you're not familiar with Flyway, and how it works. Please let me know. And I can have a video dedicated just on flyway. But in a nutshell right here. This is their documentation. And if I click on getting started, I want to go to documentation actually. And then I think they have so right here. So how it works. And you can see that you basically start with a shiny database which is empty and then you have fly away. And then you apply all of these migrations into it. And then you have a database with a single empty table called Flyway schema history. And basically this contains the history of migrations. So you can see as your database or application evolves with time, you can keep on applying new changes to your database and fly away will keep track of it. So here you can see example. So initial setup and then you have version one, version two, so on and so forth. Again, if you're going to learn more about Flyaway, please do let me know. And I can have a video on Fly way. So what I want to show you is in migrations. I think they have yes. So in here they have this example here. And this is how you should basically name your migrations. So each version must be assigned a unique version. And if I Scroll down, I think they have an example here. So have a look SQL based migrations. So we kind of have to start with V and then the version and then underscore underscore the separator and then the description. This could be anything you want and SQL. If you want to undo, you can also undo by just saying you and then the version. And then if you want to have a repeatable migration, you can also do it. So I already have within IntelliJ inside of DB migration have a look V one and the score and the score in it and then tables. So let's click on it. And now we can type some SQL. So here let's just say create and then table. And then I'm going to say movie. And within this movie table, I want to have an ID and this will be big serial primary key. And this is so that this number auto increments. Also let me change this from generic SQL two and then Postgres just like that. And this is now highlighted. So I also want to have name. So this is the name of the movie. This will be text not. And then now this also have the release and the score date. And this will be date not. And then no. And finally I want to have the name to be unique. So I'm going to say unique. And then here I'm going to have name. All right. Nice and simple. And let's now end this with semi column. And there we go. Now. One thing that you need to understand with Fly Away is once this version is applied to your database. You should never edit this and I'll show you what I mean. But if I run the application and then this is successfully applied to our database. If you want to add a new table, you need to create a new version. So version two and then, for example, actor. So here we want to store actors for a movie. I'll show you later how to do it. But once this is applied to your database, do not edit this file in here. Otherwise you'll get a checksum mismatch. So to be honest, this is it. Next let's go ahead and run the application. We have the database app running. We have the data source right here. And we also configured our first migration, which creates a new table called Movie with the following column. So ID name, release name and the movie name must be unique. Now let's go ahead and basically start the application and see whether this works. So far we didn't start the application and that's because if we were to try to start the application, it would have failed because it would have expected the data source already existed. So now we have everything in place. So open up the demo application in here. And this contains the main function for running the Java application. So let's just click on Play and then run. And now if everything goes well, you can see that it worked. So let me just collapse this right here. And there we go. You see that it worked. And in here what we have is have a look. So I just want to show you the logs. So we have. So this is the starting application. And then in here we have both trapping spring data JDBC and then have a look. So Tom Cat initialize it for 80 80 and then flyway kicked in. And in the meantime, the car repool started completed. And then here we have So database and then Amigos code right here and then successfully validated one migration and you can see how long it took. So this was really fast. And also it created a new table called Flow a Schema history, and then we have zero was affected, and then current version it was an empty schema and then migrated to version one in it tables. Right. And then zero is affected as well. And then successfully applied one migration to Schema public. Now at version one. And also we can see that some cat started on at 80 right here. Which means that we are good to go. Now what I want to show you is if I open up the database in here and if I open a public so before we didn't have anything. So now if I refresh, we should have two tables in it and one sequence. So this sequence is 4D. So in here. So if I open up this V one in it tables is this ID big serial. So this gives us a sequence and then we have the movie table with three columns in here. So ID name and release date. And we also have the flyway Schema history. And this contains a bunch of information about the Schema history. So for now, we don't have to worry about this now. This is awesome. So what I was telling you is so let's say that you want to edit this file in here. Right. So let's say that you want to say this is, for example, movie name, for example, or let's just say names. Yeah. Just try to edit this file. Now, if I rerun the application, this will fail. Have a look. So migration checksum mismatch for migration version one. Either Revere the changes to migration or run a repair to update the Schema history. And this is because there is already a checksum against this SQL right here. And as I said, if you want to add this, you should really be creating a new file version two and then make the changes there. Add new tables on and so forth. As soon as it's applied, you cannot change it unless it's a repeatable migration. But this isn't a repeatable migration. Now let's go ahead. And what I want to do is if I open up the more controller and in here you can see that we can basically test whether this is working. So here you can see that we have this list movies and it's under API V one and then movies. Now let me go ahead and open a postman because not everybody should have the Ultimate Edition for IntelliJ. And in here I do have local host API view on movies. Or you can basically create a new request and you can say the URL. So here let me just send this request and then send and this doesn't work. And that's because we need to start the server. So let's just start the server and then send the request. And in here we should get not implemented right here. So this is the 500, which means that is correct. So in here have a look inside of the Movie data access service. We said unsupported exception not implemented. So we actually have to basically implement all of this. So hopefully you managed to get this far. If you have any questions, please let me know. Next. Let's go ahead and learn how to use JDBC and more specifically, JDBC template, so that we can basically run queries against our database. So from this point, we actually have everything set up. Next. Let's go ahead and then how to use JDBC template. As I said, JDBC is the Java database connectivity programming interface for defining how client access databases. Back in the days we used to use Raw JDBC. And here inside of the Oracle documentation, this is the official one where you can pretty much learn how to use Plane JDBC. So establishing a connection handling exceptions, setting up tables using the result set join result sets filtered. Basically, you can learn everything about JDBC in this page right here. So, for example, establishing a connection, you can see that you need to write a bunch of code in here. Right. So a connection. And then you get the properties, you get the username and password, and then you can basically connect using the driver manager, so on and so forth. But you can see that with spring boot, it was super easy. Right. So we just gave it the properties file, and then we have the data source, which was a couple of lines. And off we go. So now let's learn how to use JDBC template. So JDBC template is a wrapper, and it simplifies the use of JDBC, avoiding common errors. Let's go and open up IntelliJ and in here what I want to do is so let's open up project and inside of the config and then data source. What I want to do is let's have. So here I'm going to say at and then Bean and then here I'm going to say public and then JDBC and then template. And I'm going to say JDBC template. And in here what I want to do is so I'm just going to say return new JDBC and then template. Now the reason why I'm doing this. The reason why I'm doing this is because within the JDBC template, if I press command P, we can pass a data source, we can pass a data source. So here I could actually say, okay. So here I'm going to say care and then data source and an ear data source. And inside I can pause the ICAR data source. Right. So this is this data source and this is the primary one. Now again, the reason why I'm doing this is because I told you that we can have multiple data sources. Right. So if you want to have multiple JDBC acting on different data sources, this is how you do it. So now that we have the JDBC template, we can go in here and inside of the movie data access. So this is where we implement do. So movie do. We can say private and then final JDBC template JDBC template. And let's add this to constructor. And this will be this will be injected for. So this is using dependence injection. And from this point onwards, we can basically now run SQL queries against our database. So let me just restart this to make sure that things will work. And there we go. As expected, things still fine. So next, let's go ahead and learn how to insert a new movie into our database. So I just want to mention that JDBC template is one of many JDBC wrappers out there. So the reason why I'm teaching you JDBC template is because it comes out of the box with spring boot. And yeah, it just simplifies everything. But you've got others such as JD, I I think or DBI three, something like that. But there are plenty of others out there that helps you to do the exact same thing. So yeah, let me know if you've used any others in the past. I'll be curious to know. So from this point onwards, we're just going to use JDBC template. And in order for us to insert a new movie, let's go ahead and basically remove this. And anytime that you want to modify data in your database is as follows. So here I say JDBC template, and then we need to use update in here. Update and update because we want to add a new row into our table called movie. So if I delete this and then press control space, you can see that we have a bunch of other methods here. So have a look. We have update. We prepare a statement with orgs, we have execute query, so on and so forth. So I have a bunch of these methods and I'm going to show you how they work. But anytime that you want to insert a new record into your database, you say update. Now the update takes the SQL and then a list of arguments. So here let's together type SQL. And what I want to do first is so let's just say SQL in here and let's have so basically this returns the number of rows affected. So this is why we have an integer here. Now let's have the string and then SQL equals two and then three double quotes. And in here I can end this with semi column and an indent is just like that. Now the SQL is. So let's type together insert into and then movie. So you can see that I actually have auto completion there. So movie. And then inside what I want to add is the name release date. So I actually let me just say values here and then these parentheses. There we go. And what I want to show you is that I don't need to add the ID because this is order generated for us. So insert into movie and the name and then release date. Now here let's pass the question Mark twice. And this is because the update takes a list of. So here our first command P a list of so here we want movie dot and then name and then movie dot and then release date. So these are the arguments that we are passing. So let me just put this like this. There we go. So you can see properly. And also I like to invent my SQL just like this. And hopefully if I press yes. So if I invent this or format, you can see that this is nicely formatted now. So we have insert into movie values. Question Mark, question Mark. This belongs to the first question Mark. This belongs to the second question Mark, and this is the SQL itself. And then we actually returning the result. So this returns the number of rows affected. Now let's restart the application and let's try to add a new movie with postman. So here let me open a postman and let's try to send a post request. So here we're going to say we want to have a post request and then in the body. In here select draw and then text JSON. Blob. And here this has to be a JSON. Blob. So here we need to send the name and let's see that this is Avengers. And then we also need to pass the release date. So release and then date and this will be let's say 2020 and then zero three and then three. So the date doesn't really matter. So you can pick any date. But this is the year month and then date. So let me just say 20. So it makes more sense. So now let's try and send this request to our server and see what happens. So if I send this request, have a look. We have a 200 status code. So this is awesome. Now what I want to do is send a get request. So let's send a get request. And hopefully we should see that we have a list of movies. So if I send and it's not implemented. So it's fine. Right. So we haven't implemented this, which makes sense. So now if I go back to IntelliJ and in here let's open up the database and you can see that we have the movie. And in fact, I have this console right here. So let me say introspect database. And here I can say select store and then from and then movie. Let's run this by clicking on the play button. We have Avengers. So this is nice. Let's add another movie. So post and in here body and then let's say Spider and then Man. And let's say this was this year 2021 send. And now if I run this execute we have two movies. So this is cool. So we actually are storing data in our database and you can see how easy that was with JDBC template. Next, let's learn how to select data from our tables. So you saw before. If we try to get all movies we have not implemented. And this is because right here. So select all movies. It's not implemented. So in order for us to use JDBC template to get data from our database, we need to use. So in here we have JDBC. So JDBC template dot and then we have query. So the query is really interesting. It takes the SQL, but also it takes the row Mapper. Now in here, if I select the first one, you can see that there are a bunch of other different variations, but most of the time you're just going to use the SQL and the row Mapper. If you also want to pass for example arguments, you can't do it. But in here let's just say SQL. So again, we're going to create this in a second and we need a row Mapper. So here let's just say string or we can even say VAR. Sql equals two. And basically I want three of these. And this would save me column. And let me change this too far as well. There we go. In here. We need to pass the row Mapper. So here let me just press control space and I'm going to select this first one and then here within this set of curly brackets. We're going to add some code in a second. But here let's go ahead and say return. And then this will return the query. Currently it's red. But we're going to fix this in a second. So now we need to pass the SQL that we want. We can say select and then we want ID name and then release and this core date and then from date and then from and the movies. And it's always good practice to have a limit in here because if you have 1 million movies, you don't want to fetch all 1 million movies. You kind of want to have pagination. So here let's just say that we want to fetch 100 movies and there we go. So let me see if I can intend. This indentation is key for me. I like to have my code indented as much as possible. And we're good to go. So now that we have this SQL, let me also end this with same. I called here. So we have the SQL and this will give us where is the database two in here. So let's just click on console. So let's just run this. So this will give this ID name and release state. Now we need to take each of these rows and then map them into a Java class. So this is what it's known as the road Mapper. So here if I collapse this and the row Mapper works as follows. So here I'm going to say return and then we're going to return new and then movie. And then inside we need to pause the ID. So how do we get the ID? We say result set dot, get. And you can see that we can say get the column and the column is ID just like that. Then we also need to get the name. So here comma and let me just put this on a new line. Comma and then basically result set dot and then get and then string. And basically I'm just getting the data type for each of those. Right. And then this is name. And in here we also have. So let me just press command P. We have the list of actives. So for now I'm just going to send no there because we don't have anything. And in the release date. So I'm going to say local date dot and then parse and then D string. So the string comes from result set dot and then get and then string. The column label is release and then date. And to be honest, this is it. So now if I end this with semi column, you can see that the error went away and I can see that this is actually incorrect. They should be movie. And if I then this you can see that I can actually in line. So this right here. So this entire thing returns. So if I extract this to a variable returns a list of movies. Right. So here if I see movies, you can see that this returns a list of movie. Now one thing that you could do is you could actually take this role Mapper and put it on its own class. So here let's just create a new class. I'm going to say movie row Mapper and then implement row and then Mapper and the type of it will be movie. And then let's implement the methods. Let's go back here. Let's take this. So we're going to take this command C and then paste that in and you can see that this. Now it's much cleaner. Right. Which means that if I go back to move data access service instead of passing this here. Right. Instead of passing all of this like that, I can say new and the movie row Mapper. And the cool thing here is that we can reuse this road Mapper, whatever we need within this class and elsewhere. So also let me in line this and there we go. So you see that this is much cleaner. Now let's restart the application. And hopefully this should work. So let's go back to postman. And before we had a 500. Let's now send a request and check this out. So this is awesome. So you can see that we are getting the move is that we added previously in our database. So here we have a list of two movies in the Ray of two movies. So two objects right here. And this is working beautifully. So also what we could do is within the row Mapper. So let's just go to the row Mapper here. Let's just say list and then and then off. So an empty list. So let's just run this and then send it and you can see that we have an empty listing here. And obviously you want to basically get the list of actors. But this will be part of the exercise later. So if you have any questions on road mappers and how they work, please do. Let me know. But you can see that it's basically just a way of us getting access to the result set and then constructing the row. So the SQL row into a Java class and we're just getting so get it. This is because the ID type is an integer string for the name, an empty list here for the actors. And then we parse the local date in here and we get it as a string. So if you have any questions on this, please let me know. Otherwise. Let's move on. Okay. Okay. So our code is taking shape. Now let's implement the so in here we have. So let's implement select movie by ID, which should be very similar to what we've done before. So in here let's just so this returns an optional. And what we need to do is say JDBC template. And then in here we can have so we can say query. We can get a list if we want. We can also so in here we can say query. So query for object in here. So this will give us actually an object that weekend return. Now often people will use this query for object or the query which returns a list. And the reason is so if I select query for object and then go inside in here, let's just select the first one which takes the SQL and the road Mapper. And in here I want to show you the implementation and that is that this right here. You can see that it gives you basically a list. Right. So this gives you a list in here. And yeah. So basically, in a nutshell, it's actually using the query and then have a look data access exception. So it will throw that exception as well. And then here it says nullable Singleton list. And basically what it's doing is so if it's empty, it returns empty result data access exception. If it's bigger than one, then it says incorrect result data access. Otherwise it gives you the next element inside. And you can see that it throws this exception in here. So what we can do is since we know that it's using query. So what we can do is we can just use query. So we can say query. And in here let's just steal some code. So we're going to basically take this SQL in here. And then I'm going to paste it here. And the difference is that instead of saying limit, I'm going to say where and then ID equals. And then to question Mark. Now here we're going to pass the SQL. And then here we're going to pass the due and then movie row Mapper. So you see that now we can reuse that. And then also we want to pause the ID as the argument. So the ID will actually be filled. So it will be filled in here. So we'll be posting here. So this now returns a list. Right. So here I'm going to say movies. So now that we have this list, what we could do is we could say movies, dot and stream dot and then find and then first just like that. So this returns an optional. There we go. And I can say return and then basically the optional. So this returns an option right here. So what I like to do always is to invent this. So I just want to show you how the syntax looks. But then I like to invent this so just like that. So this looks much neater and there we go. So basically if it exists, then it gives us the movie. Otherwise it will be an empty optional right here. Which then if I go to the service. So movie service in here movie service. So the logic is so get movie if exists. Give the movie. Otherwise throw not found exception. Right. So let's restart the server and see what happens in here. So there we go up and running open a postman here we have the movie ID of one. Send. This works. We also have the movie ID of two. This works if I say three have a look four or four not found. So this is really cool. And you see that we didn't even use so in here we didn't even use query for object because we know that it uses a list. Right. And we can just stream and then find first and this will return an optional with the movie or an empty optional. Next, let's go ahead and implement the final method here, which is to delete a movie. So in order for us to delete a movie, let's use again JDBC template. So JDBC template dot and then query is for us to get a data from our table. And remember I said that if we want to modify we have to say update in here. So let's just say update. And here we're going to pause the SQL and we can also pass some. Right. So the R in here will be ID. There we go. And in here let's just have the VAR and then SQL equals two. And inside here let's just have the delete and then from and then movie on the new line where and then movie or actually ID. So where ID equals to and then question Mark. And it's as simple as this. So here this returns the number of rows affected. I can just say return and then JDBC template update. Now if I restart this and let's open a postman. So let's basically get here we're going to get we have two movies. Let's now delete movie movie and then two and then change this from get to delete and then send have a look 200. This work. If we get again we should have only one movie which is Avengers. Now if I try to delete the movie. So I think it was Spiderman. If I try to delete it, we have a four four movie with ID not found. So this is cool. Right. And this is happening because in here if I show you the movie service. So where with the lead in here. So we actually select the mode by ID. If present, then we can delete. Otherwise we just throw a note found exception. So this is awesome. Now let me go back in here and let's just delete one again. So basically we want to delete everything. Let's try and delete everything. So in here one delete. And now if I get to the movies, it's empty. Let's post in here. So I think we did post Spiderman. Let's also post. I think it was that was Spiderman. This was Spiderman. And this was yes by the man. Okay, we can change this to Avengers. There we go. And Oops Avengers. There we go. Send this. We have Avengers. Let's also say F nine four fast nine send. And if I get in here send, we should have three movies. Now have a look, Spiderman, Avengers and F nine. So there you have it. We successfully managed to implement this interface right here, which had these four methods. Select insert the lead and select movie buy. So we kind of have the cred without the update. But this is part of the exercise. If you have any questions for what we've done, we delete, which should be really straightforward. Please do let me know. Otherwise catch me on the next one. So before we end up this video, what I need to do is for you to go and try and attempt this exercise. So I want you to try and add the ability to edit movies. So that's one part of the exercise. And also I want you to add the actor table. So you saw that we have an actor record or an actor class, and I want you to basically associate them with movies. So for that, you need to create a new migration called V two, and then just say, for example, actor table SQL. And then here you can just grab the SQL and then just paste it. And then it should have a new table and then go and give that a go and also change the code so that you can have a joint. Right. So when, for example, you fetch a movie, you should also fetch all of the actors for that particular movie. I'm going to be leaving the solution very soon so you can basically see the way I've done it. But there is no on one specific way of doing things because at the end of the day, the way I think it's completely different than the way you think and the way you do things. Right. So go ahead and challenge yourself and also go ahead and join the community. So discord and both Facebook group. So basically there's always people online ready to help you if you have any questions. Next, let's wrap up this video. So this is everything for this video. Thanks for sticking around throughout this video. If you enjoyed what you saw, give me a thumbs up so I can keep on recording these videos. Also, let me know what you think about this video in general. Comment down below any suggestions that you may you might have or any new videos that you want to see from Amigos code. And yeah, let's just connect. So this is all for the this video. You can grab all of the source code from the repo, as I said. And also, if you're gonna basically get more courses like this, basically more professional courses, go ahead and check Amigos code. Com. There's plenty of free and paid courses that will help you to become a professional software engineer. This is all for now. I'll catch you on the next one. Assalamualaikum
