How To Connect Oracle Database from Spring Boot App to perform CRUD Operations | Spring Data JPA

[Music] hi guys welcome to some different Tech and programming so in today's session we are going to check how we can connect Oracle database with the spring boot microservice that can perform the crude operations along with Ras API calls and spring data GPA hibernate so we will perform crude operations with the employee data which we will execute via man and we will see how the data interacts with the database and micros service so in the agenda today so we will see uh like how we can set up Oracle along with the developer then we will see how we can set up the Oracle Connection in springboard micros service how we can set up M and then uh we will create Java classes and uh we will make the connection with the Oracle so we'll perform uh Cod operations that is insert update delete and select operations and then how we can can visualize data from SQ developer so for this like I'm using my blog so that is a simplifying Tien programming so blog link is provided into a video description in this example we are creating crude operations and exposing them to through rest API calls so that UI clients can invoke these operations and it enables the client to modify the employee records in the database so the purpose of this example to show the data flows uh from the database to Spring boot microservice which makes this interaction possible so like below is the system design we will use Postman client as a input and database as output and in between there is a spring boot microservice along with the GPA and hynet so inside that like there are four entities we can divide the do database access layer and controller layer so request will come to controller first then entity so which is a pojo like employee CL pojo and then that so repository entity will be interacting with the service and the service along with this toer so interacting with the database okay so this is a basic uh flow of the system so here we will be using uh the stable version of Oracle that is Oracle NY then Java 18 eclipse and M so if you want to know how to install Oracle then uh you can visit the following video so we have already uploaded Oracle Series in that uh so we have covered how to download and install or at the 19c database along with developers so you can visit here so to continue further so let's dive in into handson like uh how we can set up the project and how we can interact with the database okay so in order to create the project so we will use spring IO so go to Spring IO here you can see there are multiple configurations defined so we need to select the M project and we will select Java language spring boot version 3:1 so here uh we will give the project metadata that is a project name so to the friction and uh package name will be okay packaging as the jar Java version is a 17 then we need to add some dependencies so first is a spring web okay then we need a Oracle Oracle driver then we need a spring jpa spring data jpa okay that's it so let's let's say generate okay so our uh project is downloaded so let's copy into Eclipse so this is the downloaded project so copy to workspace and then extract it here okay so we have extracted this project so now import from Eclipse here we can say go to file and let's say import then existing M Pro so next give the path let's say finish so let's verify if all dependencies are added correctly yeah so you can see the Oracle dependency is added as ogc 11 and it is a run time and other than that this is a spring good started wave along with the jpa so this is fine so moving further so we will do springboard connection with the Oracle database so in order to connect the database you must config the data source via orm tool that is a object relationship mapping tool that is a hynet so we are using the Oracle database so respective properties are used so couple of more properties to show jpa and Oracle Dil so I will tell you how it works so go to application. properties and past it here so we need three things in order to connect with the database first basically so we need URL so this is the by default we are using Thin Client of jdbc and this is a default instance of Oracle that is S ID then we we need uh the default uh user that is a CDV and this is a password then we need this driver class name along with this port number port number to run the spring boot application so we need this spring jpa hibernate ddl Auto equal to update so in order to create and drop the tables so we need we are setting the value as a update because we are performing the cude operations so that's it so let's save it and uh now we will create the entity and repository for the hynet configuration so these are steps to work with the data in the database so that is a model uh so if you consider the MVC architecture so then we need to create the model so it's a structure that is uh known as a jpa entity classes and create the repository interfaces between them okay so let's create this uh entity class that is a employee so go to this and uh create new Java class give package name as a NT employee let say finish okay so Cy this so now we need to import the packages so you can we can do control shift o in order to import the packages so now you can see like we are using the spring boot version three so so you can see like uh spring boot 3 is a supporting Java Enterprise Edition 10 so here it is migrated to Jakarta package so from javax like earlier version of spring boot is a supporting javax package so here we are using charda package so you can you have to Define this charda persistance table this one okay that's it so what we are doing here so we are just like creating the omm mapping here so we need to define the table name so we are creating table name as a employee then we need to Define this annotation that is a adate entity so which will specify the class as a entity and uh we are defining like uh some field names that is a column names in the table so this is like a first name last name and the email along with the EMP ID as a primary key and the generated value so it will generate the automatic values as a primary key okay so we need to implement this uh getor and setus so go here and go to sources and let's see generate G and stus so you need to select all and let's say generate let's generate the two string also so Source okay so now let let's create the supporting interface so that is a employee repository so just copy this repository so create new interface give package as a [Music] com dot reposit reposit interface name employee repository and let's see in order to import the packages so what this uh repository is doing so it is a interface and it is extending this jpa repository uh along with the mapping the our uh Java objects to the database objects for the employee class so for fing the data so we are defining one method so that is a find by first name so if you provide the name of the employee that is the first name of the employee then it will F the data from this uh omm mapping okay so so this is about repository so repository will interact with the service layer so so we can implement this service layer into controller itself but generally we will create the new class in order to implement the service so let's create the service class also give the package name as a service finish okay so copy this code let's say control shift o you order to import the packages so this this list will be Java U list and rest is the same okay so before explaining the service class so let's create the controller first so go here and uh let's create this class controller class okay so this is the rest controller class so import the packages say control shift o in order to import the packages so we need this list as a Java U list that's it okay so we are annotating this controller uh to know spring boot as a act as a controller so we are defining this uh annotation that is a rest controller and then we are implementing the crude operations uh eras API calls so this controller is like directly Auto wire to employee service that is a like this controller will be interacting with the service class so we are aaring this employee surveys so what are this rest CS so earlier we defined right so we need this arrest AP calls or spring data GP arrest calls in order to interact with the database rather than like a simple insert commands like jpa calls so we need to uh suppose like uh we need to create the employees so we are defining this as a add employee okay so we are are doing the post mapping so by using post mapping so we are posting data to database by using this service or this rest call okay so if I show you so this is the postman so from where like we are executing our rest calls so you can see there are different calls so this is the gate post put so gate means F data post means we need to add some data into database put means update data and delete means delete data from database so this we are implementing via controller class so this is the post mapping so this is for inserting data into database we are defining another post mapping so that is ADD employees so suppose like we want to insert the bulk data then we we we are using this add employees so we are uh sending the list of data so let's focus on uh this first API only so we are like a creating so this is the rest call and we are def we are reading request body from the postman so for this so we have defined this employee structure so this is the structure of request from this controller so we are invoking the service method so in the service there there is implementation of save employee so go to service method so you can see this uh employee save employee so we are taking the input from the Postman and uh we are invoking this repository in order to save this data so as mentioned in this diagram so like controller will invoke this service service will invoke this repository and uh this repository will insert data into database so F Dow layer is responsible in order to like connect with the race call and in order to insert data into database so that is happening here so service do save employee and uh this is directly saving this data to database in the employee table same way like there is a add employees like uh we instead of uh giving input as a single employee inserting data into bulk so that are that is a list of employee so which is calling the same employees like this in employee service so we are giving input into the list format this list we are sending to this repository and this is like inserting data into database let's see how like uh this data flow happens after running this program so now we will focus to first two API on so that is a create part of this uh cude operations okay so before starting application so we will check like if our database services are running so go to start panel and go to Services option that or you can go to run and type services.nc so go to service and uh let's go let's type O in order to check Oracle service so you can see this is the Oracle service so that is a running up and this is up and running so our Oracle service is started so this is our SQL Developer in order to interact with our Oracle so these are the connection details uh where we are connecting to this or that is a default Sid or service name okay and usern as a c as a c role as a CP okay so we are connecting here so if you can see like uh we don't have this employee table yet created so these are system tables in the default uh service ID so we are loading this spring boot configuration once we invoke this so our or mapping or our table is created so like this you can see this employee so this employee will be created once this is started okay so you can see the hynet configuration is loading okay so our application is started so now let's verify if our table is created okay so go to tables and search for employee so you can see the employees uh created so let's see DCM so we are describing uh the structure of the table so you can see employee ID email first name and last name okay so it is created by hnet by default is created once our application is started so we have defined this employee ID first name last name email okay so EMP first name last name email so you might be getting question how it is created in capital so Oracle is like show all the field names in the capital only so if you can check if there is a data into this table oh so there is a no data Okay so this table is just now created we will execute some arrest API calls and let's see if our apis are able to interact with the database or not so go to this Postman so if you don't have Postman so you can directly download from the like a website and uh you can directly install this as a Chrome extension also so here in the new collection we we are creating new request so here we have to mention as a post request because if you can see this employee controller so we are mentioning this as a post request so what we are writing here so HTTP Port 8094 SL add employee so if you can see our configuration then we we Define server as a 8094 that's why I given the port 8094 and name of the API so now we need to define the input or what is the employees structure so we need to define the body okay so body inside body so we will give this as a raw and inside raw we have to select the Json because we are inserting data into Json format so now we need data so how we can get the data so for this we are using chart JP ask chart jpt so in order to give the data pleas give me sample Json data okay so we are telling CH to provide some data sample G data for the employee structures that's it so it is generating the data for us it gave me this data now like uh let's take the first record and Gove to postman and so this is the basic sample data so so here the employee ID is one first name is John last name is do and email is this okay so we will invoke the r API call that's it so we got the we we got the status as a 200 so our employee is inserting into database okay so let's check if our database operation is successful so go here uh toq developer and execute this select statement again so you can see see this record is inserted so John do so this record okay so this is Success so now let's insert one more to see if it is working so get this that's the same okay so we inserted one more record with that 200 status so let's verify again okay so we got two record okay so what it is doing so it is coming to this controller so post mapping so here it is coming this Json and it is invoking this save employee and this save employee saving data into database via repository so now uh there is a similar post call but uh it will insert the bulk records in the list format so what we can do so we will give multiple inputs okay so we will select three records here okay so what we are doing here so we are giving bulk records in order to insert to the database so we need to change this Epi name as ADD employees copy this add employees and we are giving the data into this format so let's say send Okay so we got the status as a 200 so let's verify so you can Michael Emily and David so right now we don't have Michael Emily and David so let's see if it is a success okay so we got the three records as a bul operation Michael and and David so we are done with the create operations so now uh we will do the read operations that is the phage operations so you can see uh so for reading the data from database so we have three apis so one is to get the all employees so it will find the all employees then we can able to fetch data as per employee ID and we can fish data by sending the first name also okay so let's start with uh fing all the data from database so here is the API so you can see we are using this gate API so by using gate API so we can fetch all data so get all employees send the request okay so we got all the data from database so you can see there are five records and we got the fire records with the status 200 okay so now we will try to F only single record by using employee ID so copy this and uh from this database get employee ID and we will Fage 106 suppose we want to fch this David Williams then we will fet 106 send the request so we got 200 okay success and we got this employee ID 106 that is a David Williams so you can see in the database also it is uh 106 okay so now we will search by using first name employee first name so uh in the console there is nothing is getting printed so just add one log statement that is a c out and we will print uh first name whatever coming into the request okay so just type here okay so application is started so let's use this API called as a employee and from database like uh we will fetch uh something so we will fetch this Michael Johnson so we'll give first name as a Michael okay so this is Michael let's send this request yeah so we got this response so we got this particular record with the first name Michael okay Michael Johnson with status code 200 this way we can able to read the data from database so now moving further so we have this update operation so we have create read that is done now update and delete is pending so we will perform update now so in order to do update so we have to send this whole record to update so just we have to change whatever uh you want to update from this Json and uh we can update that record so we will update this record David Williams so create new request so here you have to select this put because we are updating so we need this put request then uh we need this uh so in the API name so we need this update okay so type updd update and we need to send all this on so go to the body of request then draw and here select the just one okay paste it here and suppose instead of David Williams so we want to change the surname of David to the David Johnson okay so like David Williams will replace by David johnon okay so uh this is the update statement let's say send okay so here we got this error as a internal server 500 internal server error so what is this error so we need to mention the employee ID as a 106 okay so mention this 106 and hit the hit it again okay so it is updated now so we got status code 200 so like 106 David Johnson so instead like here if you can see David Williams is there so you can pin this request and we can execute this again so you can see David Johnson is it's uh last name is changed to Johnson so moving further so we we are pending with delete operation this this last one is delete operation so in order to delete so we need one more request so go here and create new request here we have to select as a delete method and then we have to what we have to do so we have to do delete and then we have to mention the ID so we will delete this uh Jan Smith that is 103 okay so let's hit this so it will delete this 103 so now there are total five records it will remain as a four records okay so employee removed 103 we got the status code as a 200 so let's see here yeah so 103 record is deleted and uh we are left with four records so delete is successful okay so I mentioned like uh here like we have added this is out employee. first name so we have printed the first name so before coming to this error so it is a printed as a employee first name as a Michael so uh at the time of read operations so we have executed this API with and this is printed with this data so this error because like we have entered wrong employee ID so that we already discussed okay we have completed with the create read update and delete all the four operations by using rest calls and uh spring data GPA so to summarize this in this session we learned how we can connect with the Oracle database server and how we can integrate hibernate and spring data GPA along with the eclipse and how we can perform the create insert update and select operations by using spring data jpa and rest calls okay so you can download the source code at this GitHub link so this link is provided in the video description also and in the blog also and stay tuned for further sessions and do subscribe to this Channel and uh please like share and comment on the videos and uh thanks a lot for watching

Channel: Simplifying Tech

Views: 299

Keywords: spring boot with oracle database connection, spring boot with oracle db, how to connect spring boot with oracle, oracle, spring boot, spring boot with oracle project, spring boot connection with oracle, configuring spring boot with oracle, spring boot with oracle example, crud operation with spring boot, crud operation, crud, spring boot with oracle database crud operation, jdbc, jdbc connection, jdbc connection in java, jdbc connectivity, how to perform jdbc

Id: jo5H9DNJJio

Length: 27min 49sec (1669 seconds)

Published: Fri Dec 29 2023