#javaprojects Employee Management System With MySQL In Java | CRUD Operation | Java + MySQL Project

hello everyone welcome to HJ programming Solutions so in today's video we are going to create employee management application and this time we are going to connect our application with the mySQL database so very first we will perform the crud operations we will cover how to create the employee then save in the database then how to show all the employees so all employee details will come from the database then how to update the employee and delete the employee so these are some crude operations we are going to cover then we will try to perform some file operations like we will import the employee data from file then we will export the employee data from our database to particular file okay then third we will see uh how to uh give some validations okay so we are going to perform some validations as well so these are some things we are planning to do but today we are going to focus on the crud operations okay so very first let's start with the video and create the project okay so how to create the project here you have to click on the file then new then project okay so here we are going to create the Java project and then if you want to change the version then you can change here okay after that you have to click on the next then next and here you have to give your project name okay and then click on this finish okay now here I have created EMP app project inside this project see we have only one folder Source okay so now let's create the package here so package will be com Dot HJ programming Dot EMP app okay this will be our package so inside this package we will create very first the main class okay so in this main class we will be having our main method okay now see here one main class is created now we have to create one Ojo class which will be our employee class okay so in this employee class we have to take some Fields like private int ID then private string name then private double salary and int first private then int age okay so these are some fields we want now we have to create the getter and Setter for all these fields so how to create the getter and Setter you just have to right click and then generate and here we have generate getter and Setter so click on that and for all the fields we want to generate the getter Setter so click on the okay okay so now our getter Setters are ready now one more thing we want to do that is the Constructor okay so Constructor with all the fields and then we have to create the default Constructor as well so let's create it so this is our default Constructor then one more thing we have to do that is the two string so same way just right click generate to string and click on the okay okay so now our employee class is ready then our main class is ready where we have our main method then what we have to create we have to create one interface okay so here I will write employee Dao interface so this is our interface okay sorry this is the class so let's write it as a interface okay so this will be our interface and then we will create one more class which will implement this interface so employee Dao impl and this will be implementing our employee now interface okay now almost our project structure is ready and one more class we have to create that will be DB connection okay so this class we are creating for uh the DB connection so from here we will get the database connection okay now so from here we are going to get the database connection okay so very first let's create the database connection so for that we need one jar that is our MySQL connector jar okay so how we can add that jar you have to click on the file then project structure and then you have to go to the modules and inside the model see here in this dependencies tab you have to click on this Plus and then select jars or directories okay then we have to select our jar so I have the jar downloaded here like MySQL [Music] connector jar this one okay then click on the open apply and okay okay so now we have added the jar here MySQL connector jar okay now what we have to do here we will create one method public static create DB connection okay so this is our method and this method will return the database connection okay so here very first let's create one connection con variable and this method is returning this connection so here we can write return con okay so now what this is saying non-static field corn cannot be referenced from a static context okay so this is our static method and this connection is not of the type static that's why we are getting this error so what we can do we can make it as a static okay so now here we have created the method create DB connection and inside this method we are returning the connection okay now first what we have to do we have to load the driver class okay and then we have to get the connection right so how we can load the driver class we can load the driver class class Dot for name and see here what we have to pass here we have to pass com Dot MySQL dot jdbc dot driver okay then see here what it is saying unhandled exception class not found exception okay so here we may get the class not found exception so what we have to do always we have to write this code in a try catch okay so try sketch exception ex and here we will just print the stack Trace so that we can get whatever we are getting okay now inside this try we can write class dot for name and this load driver comment also I will write here okay class dot for name and this is our driver class so now our driver class is loaded now what we have to do we have to get a connection so how we can get the connection we have get connection method inside our driver manager class okay so here we will write driver manager Dot get connection okay and see here what this get connection method is uh taking as a parameter it will take URL username and password okay so let's write here URL username and then password okay now we have to create these three variables first so let's create it string URL then string username so here username I have root then string password and password is root underscore pass underscore one two three four okay so here you just have to replace your MySQL password so mine is root pass underscore like one two three four so here just you have to enter your password okay now what is remaining URL okay so what will be the URL here jdbc colon MySQL colon slash slash localhost colon and here we have to give the by default MySQL Port okay then we have to give here the database so which database I am using employee DB okay so this that's it we have created here the URL username and password and this driver manager class is having the get connection method with these three parameters URL username password okay so we have passed this values here now what this driver manager class is returning if we go inside this get connection method see here this get connection method is returning the connection okay so we have to go to our ADB connection class and here we have to write con okay now what is remaining we have returned here a connection as well so I think our job for the DB connection is done here we have created the connection so this is our create DB connection static method okay now we are going to use this class name dot this method name because this is the static method okay now let's save this and let's go to our database so here I have this employee DB already created inside this employee DB we have to create the table so how to create the table right click on the table create table here we have to give the name so I will be giving my table name as a employee then here we have to give the columns like First Column is our ID second column is employee name third column is salary and the type here we have to select int and the last column was age so here also it will be age okay now we just have to click on the apply apply and close now inside this tables our employee databases sorry employee table is created with these columns ID name salary and age okay now if we are doing select star from this employee we will not now we will not get any result okay now let's go to our IntelliJ and inside our Dove implementation class here we have to write what are the crud operations we are going to perform okay so let's write not here actually in this interface so here we will write all the methods so what are the operations we are going to perform uh very first create employee then show all employee after that show employee based on ID then update the employee and last one will be our delete employee okay so these are five operations we are going to perform so let's write the first method public void create employee okay then second method oblique void show all employees this method will show all the employees okay then public while show employee based on ID and here we have to pass the ID okay now update the employee so public update employee here also we have to pass the ID for example let's suppose we have one employee with ID 1 then we will pass that ID and we can update the details for that employee okay so for example if we want to update the name then here we have to pass that name as well okay so here I am taking example of the name likewise you can update all the details of that employee and last method is to delete the employee so public delete employee here also we have to provide the employee ID okay so these are all operations we have now here we are getting some error why because this is our employed of implementation class which is implementing this employed of interface so here we have to implement or provide the implementation for all the methods which are written inside this employed of interface so let's Implement all the methods click on the okay okay so now this is just the empty implementation now we have to write the logic here okay so in our main class first what we have to write we have to create we are creating the console based application so here first just we will write welcome to employee management application okay and then here we are going to use do while loop so do while loop okay and inside this let's print the menu first okay so what will be the menu so here we will perform or we will write like first operation to create the employee or we can write it as add employee okay then slash in next line then second will be to show all employees so show all employees then slash in third operation will be show uh employee based on ID okay and then fourth will be to update the employee fifth will be to delete the employee that's it okay so this will be our menu let's save this and then here we have to write choice because here we are going to use the switch case for all these operations okay so here we will write int choice is equal to choice we are taking as an input from the user so here first create the scanner scanner AC is equal to new scanner and here we have to write system dot in okay now here SC dot next int and then switch here we have to pass the choice and inside this switch we will write all these five cases okay so let's write case 1 case 1 is for add employee okay so here we will write we have to access this add employee method okay or create employee method which is there in our employee DAV interface and the implementation is provided in our employee implementation okay so how we can access this method let's go to our main class and here first we have to create the object of our employed of interface tab is equal to new employed of implementation and now by using this Dao object our reference name we can access all the methods okay all these methods so go to our main and in switch first case we will write down DOT create employee okay and then we have to write break then second case or first let's complete the first case okay so here just I will write default uh is out in case anyone is a and it was anyone entered the invalid Choice then it will come to this default case okay so here we will write enter valid choice okay and then we will break and last one are one more case we will write here to exit from the application okay so case two will be to exit the application so before exit we will write just um thank you message thank you for using our application and then how to exit we have system class and inside this system class we have this exit method so exit with this zero okay so now if we run this application we will get our menu okay so see here we are getting welcome to employee management application and this is our menu with all the operations okay so here why we are not getting the message okay so here we have printed our menu and here we have written the choice so before this let's write one message enter choice okay so that it will be easy for because currently even if we enter the choice it will allow but just for our understanding we will write there nurses out statement okay so now our menu is ready now we have to write the implementation for this create employee method so let's go to our employed of implementation and here we have to create the employee okay so how we create the employee to create or to insert the data into our table what query we use we use insert into and then values and inside this values we will let's suppose here insert into our table name will be employee and what values you want to insert okay so here let's suppose I want to uh insert first ID then name and then we have salary and then ID of the employee okay so this way we can insert the records into our database okay so here first what we have to do first we have to get the connection and how we can get the connection we have this uh DB connection class inside this DB connection we have this create DB connection method this method is returning the connection okay so through this method we can get the connection so let's go to our implementation class and here now what we will write we need the connection so connection con is equal to what is the class name DB connection Dot create DB connection okay so this way we can get the connection now what I will do I will um create this connection here and then we will just use this Con okay now we have created the connection we got the connection here now let's write the query string query is equal to and here we have to write the query to insert the data into our table so what will be the query insert into employee employee is our table name okay insert into employee and then values here we have to provide four values we have four columns okay so this way this is our query okay so here we got the connection this is our query so actually here we want to insert the employee data so we should get here that employee as a parameter okay so we have to go to this interface and here also we will write employee employee okay so here what is the error we are getting now okay Dow dot create employee so here also we need one employee okay so here let's create one employee class employee is equal to new employee and this employee we are passing as a parameter to our create employee okay so whatever employee details you are passing here that employee details will be inserted into our employee table okay now this employee details we have to take from the user okay so for that here let's write 6 out and here enter ID okay then into ID is equal to SC dot next hint so here we got the ID then enter name of the employee string name is equal to ESC dot next then third parameter is enter salary and salaries of type double so double salary is equal to SC dot next Double and the last parameter is our age so enter age of the employee and here int age is equal to SC dot next int okay so all details we got here from the user now we have to or we have we have passed here the employee object so now we have to set all these details to our employee so how we can set EMP dot set ID and here we will pass the ID whatever we got from the user as an input then EMP dot set name here we will pass the name EMP dot set salary we will pass the salary here then EMP dot set h and here we will pass the edge okay so now all our employee details sent to this create employee class if we go to our create employee so here we got the connection we have created the query and now we have to insert or if we have to pass all the details here okay here where we have written the question marks so for that what we have to create we have to create the statement okay so here we will use prepared statement pstm is equal to how we can get the prepare statement we can got the prepare statement by using this connection so here we have to write con dot prepare statement and what it is expecting it is expecting a query okay so here we will pass this query okay so now whatever we are getting unhandled exception okay so whenever we are creating this statement we may get the exceptions so let's write this inside our try catch block so try catch and here we will write exception EX Dot print stack Trace okay inside this try we will create the statement object and then pstm dot set so here we have to set the first value what is the first value we have first value as a ID so set first comma here we will write EMP Dot get ID okay so this way here we uh we will pass this ID okay now pstm Dot set int no set string and the second parameter is name so here we have taken set strings second parameter and here we will pass employee dot get name then pstm dot set double third parameter and third parameter will be the salary so EMP Dot set salary sorry EMP dot get salary okay and then our last parameter is set int fourth parameter and we will get it from employee dot get H okay now we have created here a statement object we have set all the values now we have to write pstm dot execute update okay so if we go to this execute update what it is returning it is in returning the integer okay so we can go here and we can just write into account any variable name you can give and now our employee will insert here okay so here I will just print the message like employee inserted successfully so if it is returning other than 0 if count is not equal to 0 then it means our employees inserted successfully so here I will write the message employee inserted successfully okay because if we go to this execute update see here here we have like [Music] um it is returning the integer execute the SQL statement in this prepare statement object which must be a SQL data okay it will return either the row count for SQL data maniplication language or 0 for the SQL statement that returns nothing okay so if it is returning uh the other than 0 then it means our employee record is inserted successfully so let's go here okay now our first operation is ready so we can save this go to our main.java and we can run this application and let's test like our insertion operation is uh perfectly fine or not okay so enter a choice I want to enter first choice then see it is asking to enter the ID okay let's enter the ID as a one then name we can enter a resume the salary we can write it as a 45 000. and age let's keep it as a 25 okay now see here we are getting the message employee inserted successfully okay but here we are getting some warning as well establishing SSL connection without server identity T verification is not recommended okay okay so this is for the SSL connection so for this we can go to the DB connection and then in our URL you just have to write question mark use SSL is equal to false okay and save now if we rerun the application this warning will be gone so here 1 then let's enter the ID 2 name as um um Gita salary will be 56 000. and age will be 45 okay so employee inserted successfully now to show all employee we have not returned the logic so just to check like or verify is our employee added successfully to the database or not let's go to the DB okay and hit this query see here two records are inserted first one is for the mean second one is for the Gita okay so now our insertion operation is successfully done now let's go to the second operation second one is let's go to our main first and let's see what is the second one second one is show all employees okay so now let's write here a case uh two okay so two and this will be I think our case um six okay so case 2 is to show all the employee details okay so here we have to write down DOT here we have the method show all employee okay so we have to just call this method and then we have to write the break here now let's go to our implementation class and inside our implementation class we have to go to show all employee method and here we have to show all the employees okay so again what we have to do first we have to get the connection so how we can get the connection con is equal to DB connection Dot what is the method create DB connection and then we have to write the query here to get all the employees so how we can get all the employees if we go to our database if we are hitting this query then we are getting all the employee details so same query we just have to write in our intelligent so string query is equal to and here we will write the queries select star from employee okay now let's write the try catch block catch exception exception and then let's print the stack okay and here we have to create the prepare statement so this time we will not create prepare statement because this is not the dynamic query this is the static query so let's write it as a statement statement stmt is equal to con Dot create statement okay so here our statement is created then statement Dot execute query and this execute query is expecting a string SQL as a parameter so let's pass our query as a parameter here okay and if we go to this execute query what this execute query is returning this is returning the result set okay so let's go to our implementation class and here we can write result set because this execute query will return the result whatever result we are getting from this query okay so result set and result is equal to statement.execute query so in this result we got all the records okay now we want to print all this details so how we can print while result dot next what it means if we have anything inside this result then this Loop will execute if we don't find anything in the result then this Loop will not execute okay now go to this while and here we have to print the details so let's write system dot out Dot println and instead of println let's write the format so we can print this details in some format okay so what format first one will be for the digit because let's let's print all the details first like um result dot get int and First Column how we can get the data now result Dot get no no no oh just remove this and how we can get result dot get int and first parameter okay then result dot get string because second parameter is name okay let me enter here also here okay so this is our ID then this is our name second parameter okay then result dot get uh third parameter is our salary okay so get double and here we will write the third parameter or column index then result Dot get int last parameter is our age okay so this way we can uh we got all the details here now here let's print first one is integer so let's keep it as a percent D then tap then percent uh yes one tab then percent uh double okay so let's keep it as F4 you can keep it as a d on tablet tab on percent D okay so this way we can print this in some format so let's save this okay let me check if anything else is remaining nothing okay save this and uh rerun the application so here now let's see all the employees so for that we have to enter Choice as a second see if we are getting okay we are getting some exception what is the exception illegal format conversion exception D not Java length double okay um first integer string then double okay so let's write it as f and stop and run enter the choices at 2 and see here we are getting the data as first ID then name salary then age okay and okay here we have to write this slash in okay so here just write the slash in okay and Save and just rerun the application now we will get the in some good formats see here first record second record okay uh one more thing we can do I think we will print the headers as well like ID name um salary and uh age so here we will go above and just we will take the same and here we have to print that right it's out employee details as a follow or as below or just write employee details okay and here we will write system dot out dot format okay and here we have to write all the headers so for headers so everywhere we have to write string string and string first will be our ID second will be the name of the employee name of the employee and third will be the salary of the employee fourth will be the age of the employee okay now our insertion and show all employee operation is done if I rerun and just check so first one is already done the second one see here employee details ID name salary and age okay so here we need to give some more space we can give it by using the double tab okay but we got the details here two records we got okay now uh here I want to do one more thing let's write this way okay so it will be a good looking and maybe we can print the same and put here as well after the header and before the header okay that's it now our creation and uh this one and this one two operations are done now what is remaining the SEC third one is to show the employee based on ID okay so let's start with this uh go to our main and let's write the case three so case three and here we have to write down DOT show employee based on ID and here we have to pass the employee ID okay so employee ID now let's take this employee ID from the user so a South enter ID to show the details okay and then in employee ID is equal to SC Dot nextint so now we have passed this employee ID here go to our implementation class and we have to write the logic for this one okay so this will be pretty similar to the first one show employees okay just we have to update this query okay now let's go here and first get the connection so con is equal to DB connection dot create connection and then here we have to write the query so string query is equal to what will be the queries select star from employee where ID is equal to the ID which is passed here as a parameter okay now we have to create the statement object so try catch here we will be catching the exception and let's print the stack here okay now let's create the statement so statement statement is equal to con dot create statement and then con Dot sorry not con statement statement dot execute query and here we have to pass our query so this will return a result set so result set result is equal to this one now we just have to take the same thing copy it and paste here even this is not required so let's remove this okay and save now here we will get all the employees whose ID is the one which we have passed here okay now if we run the application okay first let's see uh how many employees we have here we have two employees okay one and two Let's uh see now the employee for the employee ID one okay so let's enter a choice as a 3 here and see okay Enter ID to show the details so which ID we will be entering we want to see the details for the Minal so enter the choice as a one and see here me null we are getting the details of this mineral okay M application is terminated maybe we have missed to enter the break statement here okay yes so here we have to write the break as well okay but now we got the details for the entered employee ID okay now if you want to check again just run the application enter the choice as a three enter ID to show the details I want to show or display the details for the second one so see here now I am getting the detail for the employee whose ID is 2 Okay so this way we are done with the creation of the employee then displaying all the employee details then show an employee based on ID now we want to update the employee and delete the employee okay so let's create the update employee first so minimize this go to our main and now we have to write the case for so case for will be to update the employee okay so here Dot update employee so here we want to update the name of the employee okay so for that we will pass here the employee ID and then the name of the employee okay so here uh s out enter ID to update the name or update the details okay and here into EMP ID is equal to SC Dot nextint so employee ID is already used so we can use the another name or we can just declare this variable globally okay so let's now just use the different name and ask user to enter the new name which they want to update okay so here string name is equal to SC dot next name is already used okay where we have used the name okay okay so this name let's declare it a bow it will be fine so here string name and int ID okay so here let's remove this ID and here also we can remove this okay now this name we have passed here now we want to update the employee let's suppose we are passing employee ID as a one then for that employee we want to update the name okay if you want to update any other detail you can pass all the parameters here or you can again write one switch case for like uh so here we have written five switch cases right one for add then delete update show employee like that so same way you can write the switch case you can give the different cases if case one will be to update the name of the employee case 2 to update the salary of the employee case 3 to update the ah age of the employee okay that way also you can manage so let's uh take the example to update the name only now we will go to update method here uh here okay this is our update employee now first take the connection uh DB connection we can use the same class DB connection dot create connection and now let's write the query here string query is equal to how we can update the detail so update then table name update employees set name is equal to where ID is equal to okay so this is our query here we got the ID we got the name now let's create the statement object before that let's write the catch uh okay now here let's create prepared statement pstm is equal to con dot prepare statement and pass the query now here we got the repair statements so prepare statement dot set int first parameter first parameter is name so let's write it as a set string and here we will pass the name okay then second parameter is end so set end second parameter and here we will pass the ID okay now last one is pstm dot we will ask to execute this one execute update okay now if we go here what it is returning it is returning the integer okay so here we can write into count okay and here we will write if count not equals to zero then print employee uh details updated successfully message okay if we save this and go to our main class and let me just rerun the application okay now let's add some more employees first okay currently we have only two employees let's add the one more employee ID we will keep it as a 4 and name will be era salary let's keep it as a 36 000. and age will be 45 so see here we are getting message employee inserted successfully now we should have three employees in our database so enter the choice as a two and see here we are getting one two and this is the four Miss three employees we have okay now see I want to update the name of the employee okay so for that we have the case four so enter here a choice as a four okay now which employee you want to update I want to have this uh employee ID 4 I want to update So currently the name is era okay now I want to change the name so enter ID to update the detail I have id4 and what name you want to give to this era I want to give her a name as a new name okay and see here employee details updated successfully again we missed to write the break statement here so break okay now if we run the application and if we see the employees see here the name of employee id4 is updated from era to new name okay so this way we can update the employee details okay now we are done with all these operations now only one is remaining which is delete the employee so here also uh we have to do the same thing so let's write the new case five and enter the ID to delete here we will write ID is equal to SC Dot nextint and now we have to call the method dot delete employee here we will pass the ID okay let's go to employ down implementation and write the implementation for this delete method so same thing even con is equal to dwayconnection dot create connection and then here let's write the query again string query is equal to now we want to delete the employee based on the sent ID so how we will delete delete from employee where ID is equal to this one okay let's write the dry catch exception and here ex dot print stack Trace now we will use prepare statement because again this is the dynamic query so prepare statement pstm is equal to connection dot prepare statement here we have to pass the query okay now we want to set this ID how we can set prepare statement dot set in first parameter and here we have to pass the ID okay now pstm dot execute update okay now it will again return the integer so you can write in count just to show the message like if count not equal to 0 if count is not equal to 0 then we will write the message here um employee deleted the employee deleted successfully okay even you can write the employee ID as well here now if I stop this and run the application again uh we are almost done with all the applications okay all the user operations now here let's um enter the choice as a 2 and see we have one two three three employees okay now I want to delete one employee okay let's delete this fourth one okay so five will be my choice and enter the ID to delete I want to delete the four so see here I got the message employee deleted successfully um go here and this one this is very important to write the break otherwise all the cases which we have after this will be executing okay now we have deleted the employee now if we run the application again and check all the employees see here we will be having only two employees and that new employee got deleted okay so now we are done with ADD employee show all employee show employee based on ID update the employee and delete the employee okay so these are some crud operations which we have performed today and then tomorrow or maybe in the next video what we are going to do whatever details we have inside our database let's suppose we are running this query So currently okay run this one we should have two records okay so now what we will be writing we will write the method to export the details from this database and put in some file okay then we will write one more method to import this employee details from the file okay so there we can get the understanding of the file operations and after that we will write some validations as well okay so that thing will be coming in our next video so if you like this video please share with your friends and don't forget to subscribe this channel so that whatever will be the next videos I am going to post you will get the notification for that thank you

Channel: SJ Programming Solutions

Views: 37,755

Keywords: sjprogrammingsolutions, javaprojects, finalyearminiprojects, employeemanagementapp, jdbc, mysql, java, howtoconnectjavawithmysql

Id: Svu6PMhZvEg

Length: 61min 58sec (3718 seconds)

Published: Wed Sep 21 2022