Servlets JDBC using MySQL

JAVA CRUD WEB APPLICATION USING SERVLETS & MYSQL

 

 

e-trainings-servlets-jdbc
 

Every web application needs some backend to manage data. In java Sevlets allows you to connect with sevaral databases one of them is MySQL which is opensource and versatile. In this tutorial we are going to learn how to connect with MySQL database using Servlets. We are using eclipse IDE to create a Java Web Application. and also you are going to learn how to include bootstrap in servlets while showing the dynamic content on a webpage. 

So First 

Create a Dynamic Web Application in Eclipse. And create the files given below.

MOVETOP↑

 


In user package

  1. User.java
  2. UserDao.java


In default package 

  1. Addservlet.java
  2. ViewServlet.java
  3. EditServlet.java
  4. EditServlet2.java
  5. DeleteServlet.java

In webapp folder

  1. index.html 

Queries:

 

--
-- Database: `usersdb`
--

 

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `username` varchar(25) NOT NULL,
  `password` varchar(25) NOT NULL,
  `email` varchar(25) NOT NULL,

  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;

 

Project Structure:

GOTOP↑

 

User.java

It is a simple POJO class. User class have some member variables and getter setter methods.

User objects are  user UserDao class methods. These methods sometimes takes userobject  as an argments ,  and also some times returns a user object/ all user objects.

 

package user;

public class User {  
private int id;  
private String username,password,email;  
public int getId() {  
    return id;  
}  
public void setId(int id) {  
    this.id  =  id;  
}  
public String getUsername() {  
    return username;  
}  
public void setUsername(String name) {  
    this.username  =  name;  
}  
public String getPassword() {  
    return password;  
}  
public void setPassword(String password) {  
    this.password  =  password;  
}  
public String getEmail() {  
    return email;  
}  
public void setEmail(String email) {  
    this.email  =  email;  
}  
 
}

 

span style="font-size: medium;">UserDao.java

This file contains methods to deal with database. servlets creates an object of this class and call an appropriate methods.

package user;
import java.util.*;  
import java.sql.*;  
  
public class UserDao {  
  
    public static Connection getConnection(){  
        Connection con = null;  
        try{  
          
        	 Class.forName("com.mysql.jdbc.Driver");  
              con = DriverManager.getConnection("jdbc:mysql://localhost:3306/usersdb","root","");  
       
               
        }catch(Exception e){System.out.println(e);}  
        return con;  
    }  
    public static int add(User userObj){  
        int status = 0;  
        try{  
            Connection con = UserDao.getConnection();  
            PreparedStatement ps = con.prepareStatement(  
                   "insert into users(username,password,email) values (?,?,?)");  
            ps.setString(1, userObj.getUsername());  
            ps.setString(2, userObj.getPassword());  
            ps.setString(3, userObj.getEmail());  
              
            status = ps.executeUpdate();  
              
            con.close();  
        }catch(Exception ex){ex.printStackTrace();}  
          
        return status;  
    }  
    public static int update(User userObj){  
        int status = 0;  
        try{  
            Connection con = UserDao.getConnection();  
            PreparedStatement ps = con.prepareStatement(  
                         "update users set username = ?,password = ?,email = ?  where id = ?");  
            ps.setString(1, userObj.getUsername());  
            ps.setString(2, userObj.getPassword());  
            ps.setString(3, userObj.getEmail());  
           
            ps.setInt(4, userObj.getId());  
              
            status = ps.executeUpdate();  
              
            con.close();  
        }catch(Exception ex){ex.printStackTrace();}  
          
        return status;  
    }  
    public static int delete(int id){  
        int status = 0;  
        try{  
            Connection con = UserDao.getConnection();  
            PreparedStatement ps = con.prepareStatement("delete from users  where id = ?");  
            ps.setInt(1,id);  
            status = ps.executeUpdate();  
              
            con.close();  
        }catch(Exception e){e.printStackTrace();}  
          
        return status;  
    }  
    public static User getUserById(int id){  
        User userObj = new User();  
          
        try{  
            Connection con = UserDao.getConnection();  
            PreparedStatement ps = con.prepareStatement("select * from users where id = ?");  
            ps.setInt(1,id);  
            ResultSet rs = ps.executeQuery();  
            if(rs.next()){  
                userObj.setId(rs.getInt(1));  
                userObj.setUsername(rs.getString(2));  
                userObj.setPassword(rs.getString(3));  
                userObj.setEmail(rs.getString(4));  
                 
            }  
            con.close();  
        }catch(Exception ex){ex.printStackTrace();}  
          
        return userObj;  
    }  
    public static List<User> getAllUsers(){  
        List<User> list = new ArrayList<User>();  
          
        try{  
            Connection con = UserDao.getConnection();  
            PreparedStatement ps = con.prepareStatement("select * from users");  
            ResultSet rs = ps.executeQuery();  
            while(rs.next()){  
                User userObj = new User();  
                userObj.setId(rs.getInt(1));  
                userObj.setUsername(rs.getString(2));  
                userObj.setPassword(rs.getString(3));  
                userObj.setEmail(rs.getString(4));  
                 
                list.add(userObj);  
            }  
            con.close();  
        }catch(Exception e){e.printStackTrace();}  
          
        return list;  
    }  
}

 

TOP↑

 

index.html 

<!DOCTYPE html>  
<html>  
<head>  
<meta charset = "ISO-8859-1">  
<title>Coding-Zon user - Servelet CRUD App</title>  
 
    <link rel="stylesheet" href=
"https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css">
  
    <script src=
"https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js">
    </script>
  
    <script src=
"https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js">
    </script>
  
    <script src=
"https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js">
    </script>
 
</head>  
<body>  
  
<h1>Add New User</h1>  
<form action ="AddServlet" method ="post">  
<table>  
<tr><td>User Name:</td><td><input type ="text" name ="username"  class='form-control' value="" /></td></tr>  
<tr><td>Password:</td><td><input type ="password" name ="password" class='form-control' value="" /></td></tr>  
<tr><td>Email:</td><td><input type ="email" name ="email" value="abc@gmail.com" class='form-control'/></td></tr>  
  
<tr><td colspan ="2"><input type ="submit" value ="Add User" class='btn btn-primary'/></td></tr>  
</table>  
</form>  
  
<br/>  
<a href ="ViewServlet">View Users</a>  
  
</body>  
</html>

 

UP↑

 

 

 Addservlet.java

 

import java.io.IOException;  
import java.io.PrintWriter;  
  
import javax.servlet.ServletException;  
import javax.servlet.annotation.WebServlet;  
import javax.servlet.http.HttpServlet;  
import javax.servlet.http.HttpServletRequest;  
import javax.servlet.http.HttpServletResponse;  
import user.*;

@WebServlet("/AddServlet")  

public class AddServlet extends HttpServlet {  
    /**
	 * 
	 */
	private static final long serialVersionUID = 1L;

	protected void doPost(HttpServletRequest request, HttpServletResponse response)   
         throws ServletException, IOException {  
        response.setContentType("text/html");  
        PrintWriter out = response.getWriter(); 
 
    /**	Reading Form data */ 
 String username = request.getParameter("username");  
        String password = request.getParameter("password");  
        String email =   request.getParameter("email");  
         
        User userObj = new User();  
        userObj.setUsername(username);  
        userObj.setPassword(password);  
        userObj.setEmail(email);  
        
        int status = UserDao.add(userObj);  
        if(status>0){  
            out.print("<p>Record added successfully!</p>");  
            request.getRequestDispatcher("index.html").include(request, response);  
        }else{  
            out.println("Sorry! unable to add record");  
        }  
          
        out.close();  
    }  
  
} 
 
 
 

 

MOVE↑

 

 ViewServlet.java

 

import java.io.IOException;  
import java.io.PrintWriter;  
import java.util.List;  
import user.*;
  
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("/ViewServlet")  

public class ViewServlet extends HttpServlet {  
    protected void doGet(HttpServletRequest request, HttpServletResponse response)   
               throws ServletException, IOException {  
        response.setContentType("text/html");  
        PrintWriter out = response.getWriter(); 
 
      /**	Lining Boostrap  CDN 	 */
 
        out.print("<link rel='stylesheet' href=");
        out.print("'https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css'>"); 
        out.println("<script src=");
        out.println("'https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js'>");
        out.println("</script>");
        
   
        out.println("<h1 class='text-center'>Servlet and MySQL Users  Management App</h1>");  
          
        		 
        out.println("<a class='btn btn-primary' href = 'index.html'>Add New User </a><br><br>"); 
 
 
       /**	Creating Users List array and storing all the users */
 
 
         List<User> list = UserDao.getAllUsers();  
          
        out.print("<table  width = '80%' class='table table-bordered'");  
        out.print("<tr><th>Id</th><th>UserName</th><th>Password</th><th>Email</th>"+
                 "<th>Edit</th><th>Delete</th></tr>"); 
 
     /**	Displaying all the users in a table using for loop */
 
     for(User e:list){   
         out.print("<tr><td>"+e.getId()+"</td><td>"+e.getUsername()+"</td><td>"+e.getPassword()+"</td>"+
         		 "<td>"+e.getEmail()+"</td> "+
        		 "<td><a class='btn btn-success' href = 'EditServlet?id="+e.getId()+
        		 "'>edit</a></td>"+  
                 "<td><a class='btn btn-danger' href = 'DeleteServlet?id="+e.getId()+"'onClick='return confirm(\"Are you sure you want to delete?\")'>delete</a></td></tr>");  
        }  
        out.print("</table>");  
          
        out.close();  
    }  
}

 

GO↑

 

 

EditServlet.java

import java.io.IOException;  
import java.io.PrintWriter;  
import javax.servlet.ServletException;  
import javax.servlet.annotation.WebServlet;  
import javax.servlet.http.HttpServlet;  
import javax.servlet.http.HttpServletRequest;  
import javax.servlet.http.HttpServletResponse;
import user.*;
@SuppressWarnings("serial")
@WebServlet("/EditServlet")  

public class EditServlet extends HttpServlet {  
    protected void doGet(HttpServletRequest request, HttpServletResponse response)   
           throws ServletException, IOException {  
    	
    
        response.setContentType("text/html");  
        PrintWriter out = response.getWriter();  
        
        out.println("<h1>Update User Details</h1>");  
      
     
    	 String sid = request.getParameter("id"); 
    	 int id = Integer.parseInt(sid); 
      
        User e = UserDao.getUserById(id);

        out.print("<link rel='stylesheet' href=");
        out.print("'https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css'>"); 
        out.print("<form action = 'EditServlet2' method = 'post'>");  
        out.print("<table>");  
        out.print("<tr><td></td><td><input type = 'hidden' class='form-control' name = 'id' value = '"+e.getId()+"'/></td></tr>");  
        out.print("<tr><td>Name:</td><td><input type = 'text' class='form-control' name = 'username' value = '"+e.getUsername()+"'/></td></tr>");  
        out.print("<tr><td>Password:</td><td><input type = 'password' class='form-control' name = 'password' value = '"+e.getPassword()+"'/></td></tr>");  
        out.print("<tr><td>Email:</td><td><input type = 'email' class='form-control' name = 'email' value = '"+e.getEmail()+"'/></td></tr>");  
        out.print("</td></tr>");  
        out.print("<tr><td colspan = '2'><input type = 'submit' class='btn btn-primary' value = 'Edit'/></td></tr>");  
        out.print("</table>");  
        out.print("</form>");  
          
        out.close();  
    }  
}

 

 

 

EditServlet2.java

 

import java.io.IOException;  
import java.io.PrintWriter;
import user.*;
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("/EditServlet2")  

public class EditServlet2 extends HttpServlet {  
    protected void doPost(HttpServletRequest request, HttpServletResponse response)   
          throws ServletException, IOException {  
        response.setContentType("text/html");  
        PrintWriter out = response.getWriter();  
          
        String sid = request.getParameter("id");  
        int id = Integer.parseInt(sid);  
        String username = request.getParameter("username");  
        String password = request.getParameter("password");  
        String email = request.getParameter("email");  
         
        User userObj = new User();  
        userObj.setId(id);  
        userObj.setUsername(username);  
        userObj.setPassword(password);  
        userObj.setEmail(email);  
          
        int status = UserDao.update(userObj);  
        if(status > 0){  
            response.sendRedirect("ViewServlet");  
        }else{  
            out.println("Sorry! unable to update record");  
        }  
          
        out.close();  
    }  
  
}

 

 

DeleteServlet.java 

import java.io.IOException;  
import user.*;

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("/DeleteServlet")  
public class DeleteServlet extends HttpServlet {  
    protected void doGet(HttpServletRequest request, HttpServletResponse response)   
             throws ServletException, IOException { 
 
        String sid = request.getParameter("id");  
        int id = Integer.parseInt(sid);  
        UserDao.delete(id);  
        response.sendRedirect("ViewServlet");  
    }  
}

 

 

Menu TOP↑

 

 web.xml

 All the servlets register in web.xml and save it.

<?xml version = "1.0" encoding = "UTF-8"?>
<web-app xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance" xmlns = "http://java.sun.com/xml/ns/javaee" xsi:schemaLocation = "http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id = "WebApp_ID" version = "3.0">
	<display-name>JavaWeb</display-name>
	<welcome-file-list>
		<welcome-file>index.html</welcome-file>
		<welcome-file>index.htm</welcome-file>
		<welcome-file>index.jsp</welcome-file>
		<welcome-file>default.html</welcome-file>
		<welcome-file>default.htm</welcome-file>
		<welcome-file>default.jsp</welcome-file>
	</welcome-file-list>
	<servlet>
		<servlet-name>Deleteservlet</servlet-name>
		<servlet-class>DeleteServlet</servlet-class>
	</servlet>
	
	<servlet-mapping>
		<servlet-name>DeleteServlet</servlet-name>
		<url-pattern>/DeleteServlet</url-pattern>
	</servlet-mapping>
	
	<servlet>
		<servlet-name>EditServlet</servlet-name>
		<servlet-class>EditServlet</servlet-class>
	</servlet>
	
	<servlet-mapping>
		<servlet-name>EditServlet</servlet-name>
		<url-pattern>/EditServlet</url-pattern>
	</servlet-mapping>
	
	
	<servlet>
		<servlet-name>EditServlet2</servlet-name>
		<servlet-class>EditServlet2</servlet-class>
	</servlet>
	
	<servlet-mapping>
		<servlet-name>EditServlet2</servlet-name>
		<url-pattern>/EditServlet2</url-pattern>
	</servlet-mapping>
	
	
	
	<servlet>
		<servlet-name>AddServlet</servlet-name>
		<servlet-class>AddServlet</servlet-class>
	</servlet>
	
	<servlet-mapping>
		<servlet-name>AddServlet</servlet-name>
		<url-pattern>/AddServlet</url-pattern>
	</servlet-mapping>
	
	
    <servlet>
		<servlet-name>ViewSservlet</servlet-name>
		<servlet-class>ViewServlet</servlet-class>
	</servlet>
	
	<servlet-mapping>
		<servlet-name>ViewServlet</servlet-name>
		<url-pattern>/ViewServlet</url-pattern>
	</servlet-mapping>
	
		
	</web-app> 
 
 

 Java Tutorial Home

 

Comments

Post a Comment

Popular posts from this blog

Using javascript pass form variables to iframe src

Image
   Open PDF in Bootstrap Modal Window   This tutorial shows How to pass a JavaScript/jQuery variable to bootstrap modal view and sets it to iframe  src attribute. It passes value dynamically to src attribute of the iframe. When the user clicks a link, it opens the URL in a popup window. This  code shows the PDF in a Model without download option by hiding the toolbar.  To show PDF toolbar in modal, just add #toolbar=1   in the above script. index.php <!DOCTYPE html> <html lang= "en" > <head> <title>Bootstrap Model with PDF Example - coding-zon</title> <meta charset= "utf-8" > <meta name= "viewport" content= "width=device-width, initial-scale=1" > <link rel= "stylesheet" href= "https://cdn.jsdelivr.net/npm/bootstrap@4.6.2/dist/css/bootstrap.min.css" > <script src= "https://cdn.jsdelivr.net/npm/jquery@3.6.1/dist/jquery.slim.
Read more

Creating a new PDF by Merging PDF documents using TCPDF

Image
Merging two PDFs in PHP What is PDF? The Portable Document Format. PDF stands for "Portable Document Format". Essentially, the format is used when you need to save files that cannot be modified but still need to be easily shared and printed. PDF is one of the most commonly used file types around. PDFs generally contain shareable data, such as text or photo documents etc., and are accessible to anyone who has a computer or smartphone. Unlike a Word file, which requires Microsoft Word to view or edit, most browsers will open a PDF. How to merge PDFs? You can merge several documents into one. In PHP, you have a library called TCPDF which allows you to combine one or more PDF documents to one single document. In this article, we will create a new PDF by Merging PDF documents using TCPDF. Download the PDFMerger-tcpdf.zip here. Unzip it and copy it to the PHP server. Copy the PDF files into the same folder that you want to combine. Open the file: merge-pdf-example.php Provide
Read more

Import excel file into mysql in PHP

Image
  Upload Excel file and Import  it into MySQL in PHP  Create a table in some database with given code.   Download Library - extract it, rename the folder to library . Create index.php file with given code in a server. Create excelUpload.php with given code. Create db_config.php . Create one Excel file with some sample data as shown in the below image of Excel file. Create ' uploads ' folder in the same directory. Now start server, run the index.php. Upload a .xls file. If Everything is fine, Excel data inserted in the above created MySQL table - tbl_products . PHP Version:   5.5.11 Folder Structure      Query: CREATE TABLE IF NOT EXISTS `tbl_products` ( `id` int ( 11 ) NOT NULL AUTO_INCREMENT, `productname` varchar ( 100 ) DEFAULT NULL , `description` varchar ( 100 ) DEFAULT NULL , PRIMARY KEY (`id`) )     Top index.php <!DOCTYPE html> <html> <head> <title> coding-zon Demo - Upload Excel to MySQL in PHP </title> <lin
Read more