Home Servlet Servlet CRUD tutorial Insert,Update,Delete

Servlet CRUD tutorial Insert,Update,Delete

19 min read
0
0
87

This Java web developement tutorial will teach you how to do basic database functions that are CREATE RETIEVE UPDATE and DELETE . using Mysql Database. The INSERT, SELECT, UPDATE and DELETE statements can be used in any database system, because this is support by all relational database systems.

We will learn how to INSERT, SELECT, UPDATE and DELETE in database by writing code to manage the records table in the database named lba.lba table consist of following columns eid,fname,lname

Index.html

In this page system shall be able to record the employee details then system shall be able to retrieve employee details.

<html>
    <head>
        <title>Employee Details</title>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
    </head>
    <body>
      
        <form method="POST" action="employee">
            
            <table width="400px" border="1">
                <tr>
                    <td colspan="2"><h1>Employee Registation</h1> </td>
                    
                </tr>

                <tr>
                    <td>Employee ID</td>
                    <td><input type="text" name="empid" id="empid"></td>
                </tr>
                
                <tr>
                    <td>Firstname</td>
                    <td><input type="text" name="fname" id="fname"></td>
                </tr>
                
                <tr>
                    <td>Lastname</td>
                    <td><input type="text" name="lname" id="lname"></td>
                </tr>
                
                <tr>
                    <td colspan="2"> <input type="submit" value="submit"></td>
                 
                </tr>
                
                
            </table>
 
            
        </form>
        <p><a href="viewemployee">View Employee</a></p>

    </body>
</html>

After fill the form click submit button form will be goes to the action=”employee”  page

you have create the page .

employee.java

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


@WebServlet("/employee")

public class employee extends HttpServlet {
    
    Connection con;
    PreparedStatement pst;
    int row;
    
    public void doPost(HttpServletRequest req,HttpServletResponse rsp ) throws IOException,ServletException
    {
        
        rsp.setContentType("text/html");
        PrintWriter out = rsp.getWriter();
        
        
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost/lba","root","");
            String empid = req.getParameter("empid");
            String empfname = req.getParameter("fname");
            String emplname= req.getParameter("lname");
            
            pst = con.prepareStatement("insert into employee(id,fname,lname)values(?,?,?) ");
            pst.setString(1, empid);
            pst.setString(2, empfname);
            pst.setString(3, emplname);
            row = pst.executeUpdate();
            
            out.println("<font color='green'>  Record Addedddd   </font>");
 
            
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(employee.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
           
             out.println("<font color='red'>  Record Failed   </font>");
        } 
    }
  
}

Display the record in to the table

viewemployee.java

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet("/viewemployee")
public class viewemployee extends HttpServlet {
    
    Connection con;
    PreparedStatement pst;
    ResultSet rs;
    int row;

    public void doGet(HttpServletRequest req,HttpServletResponse rsp ) throws IOException,ServletException
    {
        
        rsp.setContentType("text/html");
        PrintWriter out = rsp.getWriter();
        
        
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost/lba","root","");
           
            String sql;
            
            sql = "select * from employee";
            Statement stmt = con.createStatement();
            rs = stmt.executeQuery(sql);
            
            out.println("<table cellspacing='0' width='350px' border='1'>");
            out.println("<tr>");
            out.println("<td> EmpID </td>");
            out.println("<td> Firstname </td>");
            out.println("<td> Lastname </td>");
            out.println("<td> Edit </td>");
            out.println("<td> Delete </td>");
            
            out.println("</tr>");
            
            while(rs.next())
            {
             out.println("<tr>");
             out.println("<td>"  + rs.getString("id")   +  "</td>");
             out.println("<td>"  + rs.getString("fname")   +  "</td>");  
             out.println("<td>"  + rs.getString("lname")   +  "</td>");  
             
             out.println("<td>"  + "<a href='Editreturn?id=" +  rs.getString("id")  + "'> Edit </a>" + "</td>");
             out.println("<td>"  + "<a href='Delete?id=" +  rs.getString("id")  + "'> Delete </a>" + "</td>");
             out.println("</tr>");

            }
            
            out.println("</table>");
 
            
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(employee.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
           
             out.println("<font color='red'>  Record Failed   </font>");  
        }
    }  
}

Select the row of the table which row need to be edit click the edit link of the row . After click the link  will be goes to the action=”Editreturn”  page

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet("/Editreturn")
public class Editreturn extends HttpServlet {    
    
    Connection con;
    PreparedStatement pst;
    ResultSet rs;
    int row;

    public void doGet(HttpServletRequest req,HttpServletResponse rsp ) throws IOException,ServletException
    {
        
        rsp.setContentType("text/html");
        PrintWriter out = rsp.getWriter();
        
        String eid = req.getParameter("id");
        
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost/lba","root","");
           
           pst = con.prepareStatement("select * from employee where id = ?");
           pst.setString(1, eid);
           rs = pst.executeQuery();
           
           while(rs.next())
           {
               out.print("<form action='EditServlet' method='POST'");
                out.print("<table");
               
                out.print("<tr> <td>EmpID</td>    <td> <input type='text' name ='empid' id='empid' value= '" + rs.getString("id") + "'/> </td> </tr>");
                 out.print("<tr> <td>Firstname</td>    <td> <input type='text' name ='fname' id='fname' value= '" + rs.getString("fname") + "'/> </td> </tr>");
                 out.print("<tr> <td>Firstname</td>    <td> <input type='text' name ='lname' id='lname' value= '" + rs.getString("lname") + "'/> </td> </tr>");
                  out.print("<tr>  <td colspan ='2'> <input type='submit'  value= 'Edit'/> </td> </tr>");
               out.print("</table");
               out.print("</form");
               
           }
            
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(employee.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
           
             out.println("<font color='red'>  Record Failed   </font>");
 
        }
    }

}

Matching record has been passing to  the edit return for edit the records. after make the changes you will be able to click Edit button

EditServlet.java

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet("/EditServlet")
public class EditServlet extends HttpServlet  {
    
    
    Connection con;
    PreparedStatement pst;
    ResultSet rs;
    int row;

    public void doPost(HttpServletRequest req,HttpServletResponse rsp ) throws IOException,ServletException
    {
        
        rsp.setContentType("text/html");
        PrintWriter out = rsp.getWriter();

        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost/lba","root","");
           
             String empid = req.getParameter("empid");
             String fname = req.getParameter("fname");
             String lname = req.getParameter("lname");
             
             pst = con.prepareStatement("update employee set fname = ?, lname = ? where id = ?");
             pst.setString(1, fname);
             pst.setString(2, lname);
             pst.setString(3, empid);
             row = pst.executeUpdate();
             
              out.println("<font color='green'>  Record Updateeeedd   </font>");
   
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(employee.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
           
             out.println("<font color='red'>  Record Failed   </font>");
 
        }

    }
  
}

Delete.java

select the row of the table which record need to delete click the delete link of the row.

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet("/Delete")
public class Delete extends HttpServlet {
    
    Connection con;
    PreparedStatement pst;
    ResultSet rs;
    int row;
    
    
    
    public void doGet(HttpServletRequest req,HttpServletResponse rsp ) throws IOException,ServletException
    {
        
        rsp.setContentType("text/html");
        PrintWriter out = rsp.getWriter();
        
        String empid = req.getParameter("id");
        
        
        try {
             Class.forName("com.mysql.jdbc.Driver");
             con = DriverManager.getConnection("jdbc:mysql://localhost/lba","root","");
             pst = con.prepareStatement("delete from employee where id = ?");
             pst.setString(1, empid);
             row = pst.executeUpdate();
             
             out.println("<font color='green'>  Record Deletedddddd   </font>");

            
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(employee.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
           
             out.println("<font color='red'>  Record Failed   </font>");

        }
    
    
    }
    
}

i have attached the video link below. which will do this tutorials step by step.

 

 

 

 

 

 

 

Load More Related Articles
Load More By admin
Load More In Servlet

Leave a Reply

Your email address will not be published.

Check Also

Fish Shop Inventory App in Flutter

This tutorial will teach you to make a small Fish shop Inventory system in Flutter. The fo…