Welcome back. In this video, we'll understand about database endpoint. We'll see how to configure database connector and how to configure database select operation and fire dynamic and parameterised queries. Let's get started. So I'll be working on this project database endpoint start project okay. So before I get started actually there is SQL given to you called as mule training DB dot SQL in the lab documentations.

If I open this here, this is creating a database called as mule training DB and creating a table called as product and then inserting some products inside it. So before we start off, I want to execute this dot SQL file. Okay, so I have my secret workbench install on my mission, I'm opening this MySQL Workbench I'm having MySQL eight version. Okay, let me connect to my SQL Server. Yep. It's opening.

So, is there a database already for me with nimble train DB s tables, if I see product table I can see six products actually have already executed the given mule training.db here and I can see this table got created under six products. Now what I want to do is I want to find a query on this table using database endpoint. So let us actually there is no database module added I have to click on Add modules and they have to add the data Every module to the left side. So, database module got added. And in database a lot of operations which we can do, select, I will do, I'll just drag and drop select. Firstly, what I want to do is I want to drag and drop HTTP listener.

Okay. So I'll configure this listener as usual. at Port 8081. I'll take all the defaults. Okay. pod as slash product Okay, now, when when a request comes to slash products, I want to hit the database endpoint and for our select query, now I'll go to database operations and select it was in module and select select module, drag and drop.

Now I have to configure the Select operation of database module. Okay, here it is asking me to configure the database connector configuration. I will click on plus here and to configure the JDBC driver. Basically what type of connection I want. Since I'm using MySQL, I'll select my SQL connection. And here it is asking me to configure the driver.

I'll click on configure and select Add Maven dependency Okay, By default, actually, what I can do is search in Maven central just search for MySQL, colon MySQL. It'll search in the Maven central repository MySQL colon, I just say yep, I can see that there is my SQL called MySQL connector Java. I'll select it. Okay, so again, if I click on modify, modify Maven dependency here we can select, you can see that the dependency MySQL connector dependencies added as a finish. So basically, MySQL dependency is added as a Maven artifact. And the corresponding jar gets added here in the project Now I'll how to configure what is the host at which my database is running.

I'll use the host as localhost. port is 3306 by default, username for my database is root and password also is root. And database to which I want to connect is mule training DB is a database and I'll click on Test Connection. If everything is correct, I can give them success. This connection is successful. Oh, okay.

Okay. No, I need to tell what is the query to fire What is my table name? Actually? product is a table name. I won't select all products right now. Select star from product table.

Later I'll parameterize it. That's that is it right now, the output after select component will be list of rows is it. Basically what this select Query SELECT operation will do is after firing a query, calculating a lot of rows and columns, right? For each row, if you create one map, key to the map is column name and the value is a column value. So for one row, one map if there are 10 rows, list of 10 maps. So basically, after select operation, the payload will be a list of maps But I want it to be converted to JSON.

So what I will do is simply I'll go to my favorites and I will drag and drop transform message. And I will configure my transform message just output metadata as Jason and here are the expressions in player type, payload. Hey, this dash dash dash should not be remote v careful, payload and simply right. Okay. Now, let me run this and check. So it'll restart.

I'll pause the video and race and then start again. It got started. Let me give a request to localhost 881 slash products. Yeah localhost 881 slash products. I'll send it Yeah, next week, yep, see, I got all the products as a list of Jason is it? So now you understood how to fire a simple select query, which will retrieve all rows.

But I want to change my requirement. What I want is I'm expecting product name as a query parameter like this in the URL. Question mark, as a product name is equals to hp. So now I wanted to display only HP related products is it actually I want to query the products by brand name. Okay, so I will say product name is equals to hitch capital P actually according to my database. So what I'll do is I'll go here to select component double click and here at a modify my query Add their brand underscore name is equals to colon I'll say Benin is it that column name is it banners codename a brand underscore name correct.

Okay. So I gave the name is it. So I need to pass some input parameter collars beanie to map what I can do is see here, there is another tab for input parameters. If I if I click on this icon it will show me the graphical view. So this is a graphical view. So on the right side you can see the name.

I want to populate it from query parameter on the left side. I'll drag and drop query parameters to be name here. Yes, I'd code generator to the expression now I will say query params.id What is a query parameter I'm expecting? product name, insert. Okay, so I say product name. That's it.

So now I configured the value for B name by extracting extracting it from query parents. Okay done. And I will save this Oh, let me run I'll restart better. Okay. It was a video until it starts a bit got started. Now let me give a request for for Name is equals to HP I'll send a request.

Yep, I got a list of size only one. Only one HP product as a product name is equals to Apple. Yep, I got a list of products whose brand name is Apple. So I was able to successfully configure a select operation to fire a plain query as well as parameterised query and you know how to get value for this parameters. But the problem right now is you can see the XML in the XML there is a global element called as DB colon config you can see DB called config and here the user name and password is hard coded and your DB select operation is referencing to that connector configuration. I don't want to hard code my username and password like this it is not a good practice we It is good to externalize these values to external file.

Basically we prefer YAML files why ml files in mule we'll see how to actually externalize in my next video, before you complete this video, there is an exercise. What you can do is in the exercises there is 06 database endpoint document given to you please open that and complete the exercises. I'll see you in next video where I'll talk about externalizing the contractor ation of database See you in next video.