JSP Servlet JDBC MySQL CRUD Example Tutorial | Java Guides

[Music] hey hi everyone this is Ramesh from Java gates so in this video tutorial we are going to implement JSP so village ABC Massaquoi cool example tutorial so what we are going to build so in this video tutorial we are going to build a simple user management web application that manages a collection of users so we basically develop simple basic features in our user management application that is create user or update user delete user or it reuse all right let's stop all the users so these are the very simple basic pictures that we are going to implement in our user management web application alright so look at here the screens so this is the list of users page and here basically the all the users from the database tables are listed here and you can see here add new user button so we can use this button to add a new user to the list and here our editor but delete button so we can use the edit button to edit the particular user and you can use a delete button to delete a particular user from the list okay and this is the header and here is the users link so we can click on this this link to list out all the users so you are in a add new user page then you once you click on users link then this link will be navigate to the lift up users page okay so this is the add new user page so we can use this page to add a new user to the user list and again look at here the users link so once we click on this link it will navigate to the desktop users page so here is the edit user page so we can also edit the user so these are the crude operations that we are going to implement in this video tutorial so add new user edit new edit user delete user and they stop the users so these are the good operations all right so we are going to use a mu C design pattern to develop this web application so I would like to give you and bleep you up mu citizen pattern so mu C stands for Model View controller and Model View controller is a pattern using software engineering to separate the application logic from the user interface as the name implies the Model View controller pattern has three layers so model layer defines the business lyric application and the controller layer manages the plops application and the view layer defines the presentation layer of the application so we are going to you know create these layers in our web applications alright so that's why I hope I'm going to give an overview of P mu C so that you will get an idea while developing the I know I will miss you have application so this is the high level architecture of PMAC design pattern so you get here's a web browser controller Model View so these are the components involved in modal view controllers and pattern so once we hit the URL in your browser so based on the URL the corresponding controller will get removed controllers consist of servlets all right and the views consist of GS peas and modern consist of Java classes so once the request comes to the controller or the servlet so surrogate will purchase that request and it will add some validations if necessary and then that data will be again forwarded to the model so we can write any business logic in a model and also controller will get the model data and it will forward to the GSP page and this B page will get that model data in that we on of your browsers but this is a very simple flow so it's important to understand the tools and technology that we'll be using so let's have a look into the tools and technology that will that will be using so we use JSP and the Eclipse ID and JDK 1.8 or later next we use Apache Tomcat 8 file for you to deploy our application and we use a soul late and we use Mexico connector Java JDBC driver to connect to the my square that I was using 0 C API so these are the tools and technology that we'll be using let's have a look into the development steps so I really like this slide because it outlines what are the steps that we are going to implement in this video so the very first is we create a Eclipse dynamic web project so once we clear the project then we need to add all the required char dependencies next we create a project structure next we set up the database and next we create a Java bin Jabr in class that is a user and next we create our user to our class so user to our class has all the JDBC code and next we create a user's wallet so this already will handle all the web requesters and next we create a user a space that is user hyper list or JSP page next we create a user from JSP page that is user hi performed or JSP page and next we also create our energies per page and finally we deploy and test our application and we will see the devil so these are the development steps that we are going to implement so first step is create our Eclipse Titanic web project in Eclipse ID so let's open the clips ID and let us start you know developing this web application so I highly suggest you guys to start coding with me so that you will get a hands-on experience on coding all right let's get started so let's open the Eclipse IDE and let's start developing the web application so I am in the Eclipse IDE so let's create it is dynamic web project so go to the file new and choose dynamic web project and let's do a project name as a JSP solid JDBC my sequel crude example alright so once you are happy with the project name hit finish so here we go so we created our Eclipse dynamic web project alright let us see the next step so the next step is we need to add the required jar dependencies so just let me copy the jar dependencies that I required and let me paste you know Lib folder so here we go so we are using JSP so it and the division muncipal a level so these are the four Japanese's are enough to develop this web application so you can get these jar files from my github repository so i will be hosting source code of this tutorial on my github repository so that you can able to get the jar files from there alright now let us see the next step next step is we need to create a project structure so let's create a packaging structure or a project things project structures so right-click on cell c4 the new choose package and let's do a project name let's do a package name as net dot java works toward user management dot model next let's create a one-note package and let's name it ad or Dow and let's go ahead and let's create one more package and let me kill name as a web alright so under model package we keep all the models or Java bin classes under double package we keep all the Java classes and under web packages we keep all the controllers that is the servlets alright so this is the simple packaging structure for our real application now let us see the next step so the next step is we need to set up the database alright so we'll be using my sequel server so I'm going to use my sequel workbench as a client for my sequel database server alright so let me open my see cover page here and I will create a database I'll name it as a demo so let's just write the statement create database demo it will create a new database yeah here we go and I have a detail script here so we are going to create a user stable it has ID name email country these are the columns and look at here we are at work lamenting the primary key that is the ID and just select the database here demo database and you have good this DDL script and let me refresh yeah here we go users table is created great now let's have a look into the next step the next step is we need to create a Java build that is user class alright so again let us switch to the Eclipse IDE let's create a user class so right click on model package new and choose class and given name as user and let's create a private face private entirely private string name private string email private string country all right so these are the four fields we required so to keep it simple I'm going to create only these fields so you are free to use in if you know I know any fluids for this user management web application so let me create a cutter second matrix we access these private fields all right so let me also create a parameterised constructor right click source constructor using face select all alright and let me create a one more constructor without ID so we are simply going to meet in the primary key so let's create one more constructor without ID field great so now we have created user Java build class now let's have a look you to the next step so next step is we need to create a user Dao class so right click on our package new and choose class and your class name is a user down so this is the user to our class it has all the JDBC little stuff so we'll be creating a group database operations inside this user Dao class all right so Java dowse basically stands for data access object it is a design pattern to separate all the database related stuff into a separate class all right so let's go and let's develop a group database operations inside this class so so let me write the comments here so that it will be easy for you to understand so this tau class provides a crew database operations for users table in a database all right so let's create our variables to store JDBC whatever username and password so here I have defined JDBC while is Russia using password and this is the mass equal to ABC link and mo is the name of the database and username password now let's create a static SQL statement to insert delete update and select users so let me quickly write the code for creating the static SQL statements so here we go so this is the insert user's SQL statement here we use this SQL statement to use certain users you need to use a stable and this is the Select user by ID SQL statement so here we are writing the SQL select statement to retrieve our user from the users table by ID and this is the select all users SQL statement so this will select all the users from the users table and this is the delete users SQL statement so did this will delete all the users from the users table and this is the updated show statement so this will update our particular user by ID okay so here we can update our user with the name email country by using by ID alright so these are the SQL statements that we need in our code all right let's query let's define crude methods now for the user so create user create user or insert user next update user select user body next select users delete delete user so these are the database operations and we are writing a separate method for each database operations all right so let's we're in let's write a separate method for each database operations so before that let's create a get connection method which will create a you know JDBC connection with a mask or database so let me quickly write the method here so look at here this is the get connection method so here we basically pass the JDBC URL username password to the get connection method and we get a connection object and we will be using this get connection method in all the respective methods all right so we are basically is writing a separate common method and we are using this method inside the remaining methods that I will show you bit later so let's create a is insert user method public wide in so - user and just pass user object as parameter and here let's use a trial through so statement to automatically close the JDBC resources let's first get a connection call the grid connection later to get the connection object and let's create a clipper statement object I'll pass the insert SQL statement here and what is the compilation here yeah we need to throw the school exception all right so look at the insert uses SQL statement here are the placeholder so we need to fill these placeholders by setting the values right so here we are using the pay statements so we can use the first admin dot set string and one and here just passed the new and it's because that means dots at string and just pass two and you just get the email address if I step in dot set string pass three and finally we need to pass the country so country spelling is take here so what we'll do we'll rename the country variable okay let's execute in this statement in X call the expert update statement here in order to exit this paper statement executes update all right time let's write a catch block and just print this option here that's it so what we have done we have created in sort user method which will insert a user into users table so let's similarly create our update user so let me quickly create a update user method so here you can see update user method it has the users user object as a parameter and again we are performing the same steps like we are in getting the connection object we are creating a paper statement and we are passing the ability SQL statement here and we are just providing the values for these placeholders and we are exhibiting this execute update method and if the records are greater than zero then we return this updated account so this is very similar to insert user method all right so let's worry let's write the method to get a user by ID so let me quickly write this method and let me explain you so here is the select user method so this will select a user by ID all right I mean look at here we're following the same step so we first established a connection and then we create a proper statement and here we are passing this select user by ID statement and we are providing the placeholder for ID and we are exhibiting this prefer statement so in case of inside line good we are using the exhibit object method but here we are using exhibit query method because we want a result set right so that's why we need to call this a good query method it will return our result set so look at here and once we got the reason set then we can extract the values or arose from this viscosity and let just iterate a result set in a while you know find we can get a column MLS here and we create a user object time we pass name email country to the user object and then we return this user object pretty simple right great now let's write a method for select all users so let me just copy this code here so which is almost similar let me zoom it let me Romanian rename this method select all users you need to pass in EAD so let's create ArrayList list user users : you are released and we add users to the ArrayList here and finally we return users and provide all this top user as a return type now we need to get our ID column value here and get int just pass ID great now we have developed select all users method which will return only stop users from users table now it's time to create a delete user method right so let me quickly write that delete user method and let me explain you so here is the delete user method and we need to pass the ID and again we are performing the same steps we first get a connection object and then we create a purpose statement object and we pass the escrow statement here that is the delete I uses SQL statement and we set the values for place holder and we are calling execute update method and this will return a number of rows deleted it's pretty simple I was trying it's almost similar to insert and update users method so this completes our or Dao user Dao implementation so what we have done we have created insert user update user select user by ID select all users and delete user so these are the database code operations for users table great so let us have a look into the next step so next step is what we need to so next step we create a user salat and we define although all the you know crude methods which will handle a web request that is create user or update user or delete user and select all all user alright let us see how to implement it so right-click on web package new and create a servlet and let's name it as a user so late and notice here we are using at web Soviet innovation so this is the default URL pattern for this solid so I will remove this because I want to handle all the requests in a single solid so I will explain a bit later next let me remove this all the unwanted code okay now we'll be you know calling user Dowell methods so let's create object up the user Dao class here private user now user down and then let's create this object inside a constructor great now what we'll do we'll handle all the requests in a single method that is anon doget method okay let me define this method a bow and what I will do I will call this doget method and let's handle all the requests you know to get method itself okay so there are different kind of actions like a new great new user or update user or edit user delete user list user so let me quickly create a a switch statement for these actions so here I have created a switch case statement with a skeleton to handle all the requests such as slash new insert delete edit update switch these are the requests we are going to handle and will write a separate method to handle these requests then we get an action from the request object by using get some red path alright let's go ahead and let's first handle a new request so let me quickly create a powered method here with show new form and the inside and show new form method we are just creating a request dispatcher object and we are forwarding requests to the user - form dot JSP page it's very simple so whenever we whenever we call this URL it will forward no requests to the user - form object page we can call this matter from this case and here we need to pass the request and response objects so now we have handled new web requests now it's time to write up code to handle insert web requests so I will quickly write the code to handle this insert web request and I will explain you okay so here is the insert a user method private method and and we are just extracting the user details such as using name email country from the request object and we are creating a user object here and we are passing a name email country to the constructor and we are calling insert user method of user Dao object and we just call this insert user method to save this user to the database and then we we redirect to the list request here if the list is not there we and then we write in a default section here so I will show you that a bit later so this is the method to handle insert user request so it is a pretty simple so just do we need to suggest oh we need to know rique leave all the userform details from the request object and we create a user object and we pass that user object to the insert user method pool save this user object included is pretty simple so next is we need to write up code to handle the delete request so let me quickly write the code and let me explain you so I hear is that delete user or method which we just need to get the ID from the request object and we call the corresponding digit user method in order to delete this user from the database pretty simple so once the user is deleted from the users table then we redirect to the users list page so this is a whole we can handle or delete requests and just pass here the request object and response then just surrounded by try with this was to try and catch block now let's handle the edit web request so in case I had it just we need to return or we need to just dispatch request to the user from JSP page so let me quickly write the code and let me explain you so here is the show edit form so first we get a user object by using select user method and then we we pass this user object to the request object using set a little bit and we forward this user object to the user - former JSP page so so we pass this visual object for editing on this page so from this page we can edit the user and we can you know submit the user and that will be again updated in our users table so it's pretty simple let's look at here we get the idea from the request object and we got a user object by ID and we create a required special object and we forward that user object to the user - found RJ's page it's really simple let me call this method form / edit action and today we need to pass the request and response objects let me and close you know try and catch block now let's implement update so let me quickly write the poor and let me explain so here is the update user so in case of update we are doing the same thing like we are retrieving all the user details from the request object using getparameter method I'm going again creating a user object and just we are using or we are calling update user method and we are passing this user object to the update user method and this update user method will update the user you know details to the users table and then we read out to the list page pretty simple and let me call this method from the action here inside a switch case statement the requestor scones no let's implement our default so if the actions are not present in a switch case case then it will go to the default statement so in a default section will basically forward request to the users list page so here is the list to use on method so basically we fetch all the users from the users table and then we set this list of users to the list user key using setattribute method of request object and we just follow this request to the user happen list or J speech and it's pretty simple list user method and just recall from the default section I request a response we great now we have no developed all the logic to handle a web request in a single solid now let's have a look you to the next step so the next step is we need to create JSP pages that is user list dot J's page and use the founder JSP page and in the error page so let's go and let's create this because quickly so notice here inside a solid we have parsley here JSP page names but we have not at JSP pages are created let's worry let's create a JSP pages so right click on web content folder new and select CSV file and give a name as user - for JSP and that's going let's create one more JSP page and name it adds use a little juice package and let's create a error gorgeous because now the error dot GSP and let me replace this unit or JSP page code with the exception you not get message so if there are any exceptions occur then that exception will be shown on this page and this exception object is an implicit object in a JSP so we need to give this error page you know configuration in web protection let me show you how to do that so let me open the we have dot XML file so basically why creating this a clip dynamic web project we need to you know choose the option for web products Emma but in our case we haven't chosen the web protection of no option so don't worry just create a web dot XML file inside of a planner folder like this and inside a web protection now we need to configure the arrow face like this ok so any exceptions occur you know our application then that will be the exception will be I know populated on this J's page simple right so let's design our user form dot JSP page now so let me quickly write the code on let me explain you so here is the code so I don't want to you know take a much after time so I have just written the code here and let me explain you so anyways I am going to host those products to the on my github repository so you can able to get the port from there so look at here we are using bootstrap CSS framework and the version for both rap is 4.3 find one idea we are using CDN link and this is the head door so inside a header we are just you know providing a link users link so whenever we click on users then it will call the list and this list will be handled in a servlet and this is the user forum and this user forum will be we provided two functionalities one for in certain and therefore object update so if the user object is null then the form ax as insert user forum and the user object is not null then we can use this form for update so based on the condition we are just showing the text here so I believe user object is not null then we show edit user text if the user equal to null then we show the admin user text and this is very simple form fields like for a username in user email user country and here we are using you know bootstrap CSS classes like form control and form group etc great so here we can enter username user email user country and we submit the form then this request will be sent to the servlet based on the action this request will be handled in a solid for example in the user solid if the action is update then this here update user request will be hundred here and if the request is if the action is inside then this will be hindered here alright so this is pretty simple flow so let's design I use a list dot JSP page quickly so here is the JSP code for user - list of JSP page so look at here the same logic we have written we have included boostrap min dot CSS fine here we have provided C in the link so you can also download Bushra up to CSS files from the opposite of shopsite locally and you can keep the bootstrap CSS files you know project and you can give a path ok so to quickly demonstrate this application I am just using CDN links here and this is the header so inside later we we can use a bush up you know bush have CSS classes in order to design the header and we can provide a links like here so this is the user link so once we click on users link it will navigate to the users list page and this is at the table so this is the both Shack table so we are applying the CSS classes to this table here and inside table will have columns ID name email country and actions and here are the rows our value for the columns for example here is the and we are iterating oralist user and we are showing the users here and this look at here is this is the action so anytime the lead and we are passing our values to the ID like this okay and we are using gstl for each tag here in order to iterate over list of users so this list user we got from the servlet so at a sublet so look at here so we fetch all the users from the listener it is table and then we keep these users in a list user key you know request object and then we get these values from the request object in a JSP okay so we can use this jo-jo syntax to get a list of user saying we can iterate and we can show the list of users like this petty simple user list JSP page now let us see how to deploy this application and let us see that demo so in order to deploy this application so first you need to configure apache tomcat server in Eclipse ID and just right click on the project run as Reynolds over and it finish so let's wait for a moment it will take couple of seconds to reply for effect now our application is up and running let me just copy this link and let's open a browser and in a new tab just eat this link yeah here we go look at here the screen and click on add new user and just enter the form here user name is Ramesh for today email name is 24 for the title at the rep gmail.com and you select country India and sale so here we go select me ID one more yourself Tony Stark Tony at the regiment at home the US so second record is added let me add one more record I heard John and email it is John at 30 minute at home my country as US and save it yeah so we hide it three users to the user sleet now let's wedding let's see how to edit so just click on edit button here and let me edit this user so I do just image their image and I update the email id and I will just you Ramesh at the red gmail.com and I save it yeah here we go so this Ramesh user is updated successfully and let's worry little eight this user yeah and let me delete this this one also so this is how we can add the new user we can edit the user we can delete the user and you can lift out the users alright so I hope you got some idea who to develop a web application using GSP so that JDBC and my sequel database so let us see the next step so blog postings host code on github so I have already eaten right up tutorial on this topic and you can find the blog post on my website and now so I have host the source code of this tutor on my guitar repository so I'll be providing a link for blog post as well as the source code on github a report so that anytime we can check it out I hope you found this tutorial useful and subscribe to our Channel whenever I will publish a new videos you will get updated thanks for watching I will see you in the next video

Channel: Java Guides

Views: 112,668

Keywords: jsp, servlet, jdbc, mysql, java, javaguides

Id: RqiuxA_OFOk

Length: 46min 59sec (2819 seconds)

Published: Sat Jan 25 2020