Jsp, Servlet, JSTL and MySQL Simple CRUD Application

This is a simple CRUD (Create Read Update Delete) User Management Web Application using Jsp, Servlet, JSTL and MySQL created using NetBeans IDE.


First, create a database and table for User using the following SQL scripts:

CREATE TABLE `users` (
  `uname` varchar(10) NOT NULL,
  `password` varchar(10) NOT NULL,
  `email` varchar(50) default NULL,
  `registeredon` date default NULL,
  PRIMARY KEY  (`uname`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Now create a project in netbeans with the following project structure.

Create four packages in the src folder.

com.bari.controller: contains the servlets(UserController.java)
com.bari.dao: contains the logic for database operation(UserDao.java)
com.bari.model: contains the POJO (Plain Old Java Object).(User.java)
com.bari.util : contains the class for initiating database connection(Database.java)

User.java

package com.bari.model;
import java.util.Date;

public class User {
   String uname, password, email;
   Date registeredon;

    //put getter and setter here  
}

Database.java

package com.bari.util;
import java.sql.Connection;
import java.sql.DriverManager;
public class Database {
      public static Connection getConnection() {
          try  {
              Class.forName("com.mysql.jdbc.Driver");
              Connection con = DriverManager.getConnection
                      ("jdbc:mysql://localhost:3306/dbname",
                      "root","dbpass");
              return con;
          }
          catch(Exception ex) {
              System.out.println("Database.getConnection() Error -->" + ex.getMessage());
              return null;
          }
      }

       public static void close(Connection con) {
          try  {
              con.close();
          }
          catch(Exception ex) {
          }
      }
}

UserDao.java

package com.bari.dao;

import java.sql.*;
import java.util.*;
import com.bari.model.User;
import com.bari.util.Database;

public class UserDao {

    private Connection connection;

    public UserDao() {
        connection = Database.getConnection();
    }

    public void checkUser(User user) {
        try {
            PreparedStatement ps = connection.prepareStatement("select uname from users where uname = ?");
            ps.setString(1, user.getUname());
            ResultSet rs = ps.executeQuery();
            if (rs.next()) // found
            {
                updateUser(user);
            } else {
                addUser(user);
            }
        } catch (Exception ex) {
            System.out.println("Error in check() -->" + ex.getMessage());
        } 
    }
    public void addUser(User user) {
        try {
            PreparedStatement preparedStatement = connection.prepareStatement("insert into users(uname, password, email, registeredon) values (?, ?, ?, ? )");
            // Parameters start with 1
            preparedStatement.setString(1, user.getUname());
            preparedStatement.setString(2, user.getPassword());
            preparedStatement.setString(3, user.getEmail());            
            preparedStatement.setDate(4, new java.sql.Date(user.getRegisteredon().getTime()));
            preparedStatement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void deleteUser(String userId) {
        try {
            PreparedStatement preparedStatement = connection.prepareStatement("delete from users where uname=?");
            // Parameters start with 1
            preparedStatement.setString(1, userId);
            preparedStatement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void updateUser(User user) {
        try {
            PreparedStatement preparedStatement = connection.prepareStatement("update users set password=?, email=?, registeredon=?"
                    + "where uname=?");
            // Parameters start with 1
            System.out.println(new java.sql.Date(user.getRegisteredon().getTime()));
            preparedStatement.setString(1, user.getPassword());
            preparedStatement.setString(2, user.getEmail());
            preparedStatement.setDate(3, new java.sql.Date(user.getRegisteredon().getTime()));
            preparedStatement.setString(4, user.getUname());
            preparedStatement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public List<User> getAllUsers() {
        List<User> users = new ArrayList<User>();
        try {
            Statement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery("select * from users");
            while (rs.next()) {
                User user = new User();
                user.setUname(rs.getString("uname"));
                user.setPassword(rs.getString("password"));
                user.setEmail(rs.getString("email"));
                user.setRegisteredon(rs.getDate("registeredon"));
                users.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return users;
    }

    public User getUserById(String userId) {
        User user = new User();
        try {
            PreparedStatement preparedStatement = connection.prepareStatement("select * from users where uname=?");
            preparedStatement.setString(1, userId);
            ResultSet rs = preparedStatement.executeQuery();

            if (rs.next()) {
                user.setUname(rs.getString("uname"));
                user.setPassword(rs.getString("password"));
                user.setEmail(rs.getString("email"));
                user.setRegisteredon(rs.getDate("registeredon"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return user;
    }
}

UserController.java

package com.bari.controller;

import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.bari.dao.UserDao;
import com.bari.model.User;

public class UserController extends HttpServlet {
    private static final long serialVersionUID = 1L;
    private static String INSERT_OR_EDIT = "/user.jsp";
    private static String LIST_USER = "/listuser.jsp";
    private UserDao dao;

    public UserController() {
        super();
        dao = new UserDao();
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String forward="";
        String action = request.getParameter("action");

        if (action.equalsIgnoreCase("delete")){
            String userId = request.getParameter("userId");
            dao.deleteUser(userId);
            forward = LIST_USER;
            request.setAttribute("users", dao.getAllUsers());    
        } else if (action.equalsIgnoreCase("edit")){
            forward = INSERT_OR_EDIT;
            String userId = request.getParameter("userId");
            User user = dao.getUserById(userId);
            request.setAttribute("user", user);
        } else if (action.equalsIgnoreCase("listUser")){
            forward = LIST_USER;
            request.setAttribute("users", dao.getAllUsers());
        } else {
            forward = INSERT_OR_EDIT;
        }

        RequestDispatcher view = request.getRequestDispatcher(forward);
        view.forward(request, response);
    }

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        User user = new User();
        user.setUname(request.getParameter("uname"));
        user.setPassword(request.getParameter("pass"));
        try {
            Date reg = new SimpleDateFormat("yyyy/MM/dd").parse(request.getParameter("dob"));
            System.out.println("rrrrrrrrrrr"+ reg);
            user.setRegisteredon(reg);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        user.setEmail(request.getParameter("email"));
        String userid = request.getParameter("uname");
//        if(userid == null || userid.isEmpty())
//        {
//            dao.addUser(user);
//        }
//        else
//        {
            user.setUname(userid);
            dao.checkUser(user);
//        }
        RequestDispatcher view = request.getRequestDispatcher(LIST_USER);
        request.setAttribute("users", dao.getAllUsers());
        view.forward(request, response);
    }
}

Now, it’s time to create three jsp pages.

index.jsp

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>CRUD Example</title>
    </head>
    <body>
        <jsp:forward page="/UserController?action=listuser" />
    </body>
</html>

user.jsp

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Add new user</title>
    </head>
    <body>
        <form method="POST" action='UserController' name="frmAddUser">
            <% String action = request.getParameter("action");
                System.out.println(action);
            %>
            <% if (action.equalsIgnoreCase("edit")) {%>
            User Name : <input type="text" name="uname"
                               value="<c:out value="${user.uname}" />" readonly="readonly"/> (You Can't Change this)<br /> 
            <%} else {%>
            User Name : <input type="text" name="uname"
                               value="<c:out value="${user.uname}" />" /> <br />
            <%}%>
            Password : <input
                type="password" name="pass"
                value="<c:out value="${user.password}" />" /> <br /> 
            Email : <input
                type="text" name="email"
                value="<c:out value="${user.email}" />" /> <br /> 

            <% if (action.equalsIgnoreCase("edit")) {%>
            Registration : <input
                type="text" name="dob"
                value="<fmt:formatDate pattern="yyyy/MM/dd" value="${user.registeredon}" />" readonly="readonly"/>(You Can't Change this)  <br />
            <%} else {%>
            Registration : <input
                type="text" name="dob"
                value="<fmt:formatDate pattern="yyyy/MM/dd" value="${user.registeredon}" />" />(yyyy/MM/dd)  <br /> 
            <%}%>
            <input  type="submit" value="Submit" />
        </form>
    </body>
</html>

listuser.jsp

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Show All Users</title>
</head>
<body>
    <table border=1>
        <thead>
            <tr>
                <th>User Name</th>
                <th>Email</th>
                <th>Registration Date</th>
                <th colspan=2>Action</th>
            </tr>
        </thead>
        <tbody>
            <c:forEach items="${users}" var="user">
                <tr>
                    <td><c:out value="${user.uname}" /></td>
                    <td><c:out value="${user.email}" /></td>
                    <td><fmt:formatDate pattern="dd MMM,yyyy" value="${user.registeredon}" /></td>
                    <td><a href="UserController?action=edit&userId=<c:out value="${user.uname}"/>">Update</a></td>
                    <td><a href="UserController?action=delete&userId=<c:out value="${user.uname}"/>">Delete</a></td>
                </tr>
            </c:forEach>
        </tbody>
    </table>
    <p><a href="UserController?action=insert">Add User</a></p>
</body>
</html>

Finally configure your web.xml file.

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
    <servlet>
        <servlet-name>UserController</servlet-name>
        <servlet-class>com.bari.controller.UserController</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>UserController</servlet-name>
        <url-pattern>/UserController</url-pattern>
    </servlet-mapping>
    <session-config>
        <session-timeout>
            30
        </session-timeout>
    </session-config>
    <welcome-file-list>
        <welcome-file>index.jsp</welcome-file>
    </welcome-file-list>
</web-app>

That’s it. Run the project. These are some screenshots.

jsp_crud

jsp_crud_edit

 

admin

Hello! I am Md. Abdul Bari; founder and admin of javaknowledge; currently providing training as a J2EE faculty in The Computers Ltd. Dhaka, Bangladesh under an IT Scholarship project provided by IDB-BISEW. I am a self learner and passionate about training and writing. I am always trying my best to share my knowledge through my blog.

More Posts - Website

Follow Me:
TwitterFacebookGoogle Plus

135,180` Total Views

22 comments

  • Dosn´t work at all

  • When you created the project, did you use Netbeans?, whay kind of project it was, did you use a framework? Thanks in advance

    • Yes, this project is created with NetBeans(I have provided a project structure that you have not noticed). There is no framework, used only JSTL.
      The first line of the article describes what type of project it is.-Thanks

  • I tried to run the project on netbeans 7.2 and mysql 5, but i get this error when I run it

    org.apache.jasper.JasperException: java.lang.NullPointerException org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:549) org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:470) org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390)
    org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:722) org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:393)

    Thank you in advance

  • suppppppppppppppppppper how can you design flying bird over a web page

  • Says “can’t resolve file UserController” and marks it red in index.jsp. (Project was inserted in Intelij IDEA with Maven). What i’m doing wrong?

  • I tried to run the project on netbeans 7.4 and mysql 5, but i get this error when I run it

    HTTP Status 500 – Internal Server Error

    type Exception report

    messageInternal Server Error

    descriptionThe server encountered an internal error that prevented it from fulfilling this request.

    exception

    org.apache.jasper.JasperException: java.lang.NullPointerException
    root cause

    java.lang.NullPointerException
    note The full stack traces of the exception and its root causes are available in the GlassFish Server Open Source Edition 4.0 logs.

    Please any one help me………………………..

  • Arundas kerala,ind

    Good tutorial thank u and thank google
    your crud app is working but sorry to say that i took this tutorial for
    get rid of servlet which is not a standard for jsp these days, this tutorial contains Scriptlets in the ‘user.jsp’ apart that this was one of the good tutorials i met so far… best regards… man

  • Hi !
    I am getting following kind of exception

    java.lang.NullPointerException
    at com.aniket.dao.UserDao.getAllUsers(UserDao.java:88)
    at com.aniket.controller.UserController.doGet(UserController.java:58)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:743)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
    at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:225)
    at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:127)
    at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:283)
    at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:175)
    at weblogic.servlet.internal.RequestDispatcherImpl.invokeServlet(RequestDispatcherImpl.java:499)
    at weblogic.servlet.internal.RequestDispatcherImpl.forward(RequestDispatcherImpl.java:245)
    at weblogic.servlet.jsp.PageContextImpl.forward(PageContextImpl.java:148)
    at jsp_servlet.__index._jspService(__index.java:98)
    at weblogic.servlet.jsp.JspBase.service(JspBase.java:34)
    at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:225)
    at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:127)
    at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:283)
    at weblogic.servlet.internal.ServletStubImpl.onAddToMapException(ServletStubImpl.java:391)
    at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:309)
    at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:175)
    at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3214)
    at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
    at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:121)
    at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:1983)
    at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:1890)
    at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1344)
    at weblogic.work.ExecuteThread.execute(ExecuteThread.java:209)
    at weblogic.work.ExecuteThread.run(ExecuteThread.java:181)

    • In the beginning of the exercise, where the author put a screenshot of the file structure for this program, you will need to look at the Libraries folder. If you compiled your code without adding the JSTL and MySQL libraries, then you’ll get this error nullpointer error. To fix it you’ll need to re-compile or redo the entire program and add these libraries before hitting build.

  • The post is more than awesome . Liked It very much.. Thanks admit for such clean code

  • I am facing this kind of problem. Warning: Could not find file C:\Users\user\Desktop\SimpleJspServletCrud\${libs.jstl11.classpath} to copy.

  • Hi
    I am really surprised to see your content and understand your content fullly.
    Thanks buddy.

  • Hi, i can’t register, the email with password fails…. I want download the project.

    help me, please.

    Thanks.

  • Radhanath Mohapatra

    write proper code for forgot password link page in my project.using oralce 10g.tabale name
    employ having datafield (e_name,e_id,password,s_question,s_answer,email,mbno,).

  • This is an awesome post and very useful. What design pattern you used here in this app?

  • I can’t register me… help me.. i need the project !! please help me.
    thanks.

  • Hi…
    in CLasse UserDao you did created a method getUserById
    and i want to see this method working using a text input to search on BD
    can you create a jsp page to do this?

  • You sir, are my savior!!!

  • it s not work

Leave a Reply

Your email address will not be published. Required fields are marked *


× nine = 45