In this session, let us learn how to integrate a spring application with a database using JDBC. For this, we are going to use spring JDBC module. Here we are going to create the spring application using Spring Boot. What is advantage of using spring JDBC module if you want to integrate a spring application with the database using spring module spring will take care of the low level details to connect to the database using JDBC. Let me just explain this with the tabular form. I have given the actual the work done by spring and the work done by you define connection parameters.

You want to specify to which database you want to connect what is the URL what is the driver name, username and password. Once you do this spring will establish a connection with the database next Specify the SQL statements, you need to specify what work you want to do, whether you want to insert or whether you want to perform DML statement I mean DML operation are you want to retrieve data from the database. So, you want to find a SQL statement, declare parameters and provide parameter values. If you are inserting or if you are retrieving based on a particular criteria, you need to give the values I hope you remember this TF dot set string of tf.sh into the EU values for each and every column. Right so the information should be provided by you prepare and execute the statement who's going to do this work spring will do the work next set up a no fly thread through the results.

Why not have that next that we were doing it? So here who is going to do spring is going to do the work? Do the work for each iteration inside the loop what exactly you want to do to which are Jake, so with instance variable the value should be added. So these kinds of information you have to provide process any exception who's going to take care of spring, tighter caring taking care of the transaction, closing the connection statement and Russell said, all this is done by spring. So, if you are going to create a Java application that is connecting with the database, as a developer, you will be giving the database details, you will be establishing a connection, you will be writing the SQL statements together with that setting the values for the parameters, then if you are retrieving the values, you have to write this while our start next I trading through the rules are fading the values of the columns to the instance variables, and you have to handle the exception, you have to handle the transaction.

Finally closing the database. All these steps you are doing now with spring, spring takes care of opening and closing the connection. It takes care of the exception and transaction but it takes care of the iteration part, it is preparing the statement and executing the statement. So, the lower level details are taken care of springs and the high level details are taken care by you, as a developer, you are just going to provide the information to string so that it can do the work for you. Let us see the approaches used by spring JDBC module to connect to the database. These are the three approaches using JDBC template named parameter JDBC template simple JDBC call we are going to learn how to use JDBC template to connect to the database the packages from spring JDBC module which we are going to use our data source objects and support.

Now let us understand about this JDBC template JDBC template is the main class from the spring core modules or from the spring core package. It is responsible For opening and closing a connection, then it is responsible for statement creation and execution like you give the data source details JDBC template will establish a connection with the database then you specify the query you will specify the parameters which has to be set in that particular query the values for the parameters okay. Once you have given that the JDBC template will create the prepared statements execute the prepared statements and also JDBC template can work on stored procedure calls. If you want to work with stored procedures or if you want to talk to a get current to a stored procedure, you need to use callable statement, the callable statement will be created by JDBC template depending upon the stored procedure name you have given it is going to execute that taking care of transaction handling exceptions performing iteration over a result sets.

So these kinds of things are taken care by JDBC template. So if you Want to connect a spring application with a database using JDBC you can use JDBC template with which we'll be doing all these lower level activities. Now, let us also see the callback interfaces that is used by the JDBC template prepared statement creator It is used to create a prepared statement and executed, callable statement created it is used for creating callable statement Ray called backhand lips. What is the purpose of this when you are isolating through the result set you will be getting individual rows after getting the individual row that dress should be mapped to a Java object that is done with the help of rope callback handler. These are callback interfaces which will be used by a JDBC template. So this is about this JDBC template.

Now let us learn how to configure JDBC tip template you know what are the layers? You are you have a plan from the clients you are asking awaiting service from the service your auto wiring Da, da was responsible for connecting to the database. So now in the CMR MPL class, you need to auto wire JDBC template. So that's what I have given over here, auto wire JDBC template, this JDBC template, again, the path a dependency of the data source, what is the username you want? What is the database you are using? What is the driver name, what is the URL username and password all these information can be given in an XML.

When you are working with spring boots, you don't use an XML insert, we will be using the properties file. Using the properties file, we can view the details about the database. So in the application dot properties file, because we are using spring boots, we need to do the details like this using spring dot data source dot driver hyphen class a file name and again for URL username password are these are given an application dot properties file, just understand the slope, I have a client, the client connects to the service the service connects to the do the D, I mean the DA will layer the DA will have a dependency on JDBC templates, the JDBC template has a dependency on the data source, so that it can connect to the appropriate database. So, back to you the details about the data source that you have to give in this application dot properties file.

Now, let us discuss about the methods of JDBC template class. What are the operations you need to perform the database DML operations like insert, update, delete and querying the database to retrieve one row or many rows based on a criteria for performing DML operation you need to use update method wherein the first parameter is the SQL query. The second parameter is the value set as we are saying For the SQL query, let me just show you with the example insert using JDBC template, see the syntax here I have got insert into spring customer values, I have got four customers it means that I have to give values for these four question marks. So, what I can do, I can just put it in an array, I have just given it in an array see customer dot get name get customer ID get it get city. So these are the four values which is available in the array customer.

So in update methods, I can pass the first parameter as the query and the second parameter is the custom array. Once you give this automatically JDBC template will call prepared statement creator interface to create the prepared statement and set values for that particular column using these values, and then finally, execute the prepared statement so that the row is getting inserted in the day database table. Similarly, for update for update feature update string customer set city equal to question mark where customer ID equal to question mark, I need to give values for city and customer ID, I can put it in the form of an array, second parameter the server errors, right? then delete delete is also the same thing in this case is only one value so I'm just passing on one value directly second parameter whereas it's not an array, it's a variable size array.

Next let us see how to use query query is used for query multiple objects are for multiple rows are for retrieving one row. Let me just explain this here I was query string SQL comma row mapper. What is the purpose of this row mapper. This row mapper is an interface basically does having only one method map Pro. map the rope into a particular objects that describe meaning, when we were working with rapid statements what we used to do, why not start Next, we will be retrieving individual columns for a particular row, then we will be attaining it to work instead of appending it to an object, the same thing we are going to do over here, the iteration will be done by JDBC template that painting of the column values to the instance variables will be done by you. So, let me just explain it here.

This is the method JDBC template dot query. The first parameter is the SQL query Select star from string customers. Comma the second parameter is an interface. So you need to create a class which implements the interface either you can create a class separately and pass an object over here or you can go in for an anonymous class, I have created an anonymous class new row mapper customer and I have to implement this macro method which is from this role. But interface, map row map row to where Java objects take the values of individual columns and assign to this Java object instance variables fleet doesn't know which column should be assigned to which instance variables that part has to be given by you. So, here if you see map row I have got result set and row number I have created a customer object, I am trying to retrieve name and appending it to the customer's property name, retaining age column and appending it to the age instance variable, retrieving the city column and appending it to the city instance variable finally returning the customer object similarly all the customer objects will be created and added to our list.

So what actually I'm returning you see customer list I'm not returning this customer, this is one customer. Similarly the second iteration will happen iteration is done by JDBC templates. It is hydrating for the whole table, creating one by one Customer objects and adding it to wellness, what is the final time happy customer list can you see JDBC template that query is returning is this basically you have done it within this within this while RS dot next method, but now iteration is done by template here you are just doing this, I mean attaining the values. Similarly, querying for one domain object, if we want to query for only one domain object you need to go in for the methods query for object. The first parameter is SQL. The second parameter is the ID with which you want to query Select star from spring customers, our customer ID equal to customer I need to give value for this face.

So, it will it will be coming from the parent parameter of the method. I'm passing it over here and that is taken care again by spring only. You are passing three parameters SQL, the value that is the search query and then creating an anonymous class. Either you can go in for anonymous clap or you can use lambda because we are working with Java eight, I will also show you how to use lambda. Now, if I'm having if I want to query for multiple objects based on a criteria, that also is possible, you can just go in for query string SQL objects array and the third parameter is Roma. Similarly, if you want to retrieve a string, I can go in first query for objects, the first parameter the C SQL query, the second parameter is a search string.

And the third parameter is what data type you are trying to retrieve from. We are querying for a string for the string class, can you see this select name from spring customer where customer ID equal to minute return multiple rows? No, it is just going to return one value if I do select star from customer that customer ID equal to question mark with one row. Here I'm trying to retrieve only one name. So the return type system now what we will do We will go back to eclipse we will try to create a spring application and integrated with the database using JDBC we will be using JDBC template, we are going to use Spring Boot for creating the spring application so that we can select JDBC SP dependency we don't have any we will not be having any problem with the pom dot XML for adding the dependencies.

Let me go back to eclipse and Eclipse now, we will be doing this application in two parts in the first part, let us do the setup and in the second part we will be doing the coding. So for this let me create the spring starter project file new spring starter project to the name Spring Boot JDBC. package name is comm dot boot cap. Click Next. Now I need to select the dependencies. What is the dependency JDBC driver JDBC I'm going to use my SQL.

So from here itself I can select my SQL. These are the two dependencies which I want Now, earlier when we were working with the Maven project whenever we wanted my SQL we need to go and search and identify the version and we have to add it. Now we don't have to do because it is done by spring automatically Spring Boot automatically. Click Finish. Now the application is getting created. application is ready.

Let me open Spring Boot JDBC palm dot XML and go to this palm dot XML part. Here's spring boots starter JDBC my SQL connector Java both are added. So the first work is done. Next, let me go inside the SRC main Java comm dot backup about Spring Boot JDBC application dot java, this is C class. Okay, now we need to have a beam we need to have a service layer service layer and we need to have a Veolia, what I'm going to do is I'm just going to copy the application which we have done when we were working with Java, then we are going to modify it using spring JDBC that using spring. So let me first copy it to the different layers, book the Java book interface, which will which I will change it to be able, and I'm having the corresponding implementation class, book service and book service imp.

And, of course, there are few errors, don't bother about it. We'll see the coding in the next session.