The aim of this tutorial is to outline the steps of developing a CRUD (Create Read Update Delete) User Management Web Application . We would primarily be using Jsp, Servlet and MySQL for this purpose . The front-end will also contain JSTL tags and Jquery .
REQUIREMENTS :
1. Eclipse IDE for Java EE Developers
Download Eclipse Version: Luna Release (4.4.0) from
2. Apache Tomcat ver 7.0
download from https://tomcat.apache.org/download-70.cgi
3. MySQL Community Server and MySQL Workbench (GUI Tool)
http://www.mysql.com/downloads/ link can be used for downloading .
4. MySQL Connector for Java
latest can be fetched from http://dev.mysql.com/downloads/connector/j/3.1.html
5. Jquery for front end interactions like datepicker etc
Download from http://jquery.com/download/
Database operations :
1. Manually create DB using MySQL command
create database UserRepo;
or use the MySQL workbench “create database” tool .
2. Set as default schema from MySQL workbench
3. grant prprivileges to ‘testuser’
grant all on UserRepo.* to 'admin'@'localhost' identified by 'testuser';
4. Create the table with required fields like unique id ( also serving as primary key ) , firstname , lastname , date of birth ( dob) , email . More fields can also be added .
CREATE TABLE UserRepo.`users` ( `userid` int(11) NOT NULL AUTO_INCREMENT, `firstname` varchar(45) DEFAULT NULL, `lastname` varchar(45) DEFAULT NULL, `dob` date DEFAULT NULL, `email` varchar(100) DEFAULT NULL, PRIMARY KEY (`userid`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
Eclipse operations :
1. Create new Web project from File menu.
File—>New—>Dynamic Web Project
2. Give the name as MVCprojectCRUD
3. Create four packages in the src folder.
-com.mvc.controller: servlets for direction handling from jsp (frontend) to dao (data access classes) .
-com.mvc.dao: classes containing logic for Data Access Object operations like create , read , update , delete even search
-com.mvc.model: contains the POJO (Plain Old Java Object). Usually every class in this package corresponds to a table in the database.
-com.mvc.util : classes for initiating database connection
4. Put the MySQL connector java jar , jstl jar and standard jar in web-INF/lib folder.
Model :
Create a class in com.mvc.model package named ‘User.java’. Every variables in this class relates to the fields ( columns) in ‘users’table in our database ‘UserRepo’.
package com.mvc.model;
import java.util.Date;
public class User {
private int userid;
private String firstName;
private String lastName;
private Date dob;
private String email;
public int getUserid() {
return userid;
}
public void setUserid(int userid) {
this.userid = userid;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public Date getDob() {
return dob;
}
public void setDob(Date dob) {
this.dob = dob;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Override
public String toString() {
return "User [userid=" + userid + ", firstName=" + firstName
+ ", lastName=" + lastName + ", dob=" + dob + ", email="
+ email + "]";
}
}
Util :
Create a java class ‘DbUtil.java’ in com.mvc.util package which will read a .properties file to handle the database connection to MySQL server.
package com.mvc.util;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class DbUtil {
private static Connection connection = null;
public static Connection getConnection() {
if (connection != null)
return connection;
else {
try {
Properties prop = new Properties();
InputStream inputStream = DbUtil.class.getClassLoader().getResourceAsStream("/db.properties");
prop.load(inputStream);
String driver = prop.getProperty("driver");
String url = prop.getProperty("url");
String user = prop.getProperty("user");
String password = prop.getProperty("password");
Class.forName(driver);
connection = DriverManager.getConnection(url, user, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return connection;
}
}
}
Property File :
The properties file ‘db.properties’ should be made and put under the src folder.
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/UserRepo
user=admin
password=testuser
Dao:
Make class in com.mvc.dao ‘UserDao.java’containing the logic for database operation.
Rember to import com.mvc.model.User and com.mvc.util.DbUtil packages classes.
package com.mvc.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.daniel.model.User;
import com.daniel.util.DbUtil;
public class UserDao {
private Connection connection;
public UserDao() {
connection = DbUtil.getConnection();
}
public void addUser(User user) {
try {
PreparedStatement preparedStatement = connection
.prepareStatement("insert into users(firstname,lastname,dob,email) values (?, ?, ?, ? )");
preparedStatement.setString(1, user.getFirstName());
preparedStatement.setString(2, user.getLastName());
preparedStatement.setDate(3, new java.sql.Date(user.getDob().getTime()));
preparedStatement.setString(4, user.getEmail());
preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void deleteUser(int userId) {
try {
PreparedStatement preparedStatement = connection
.prepareStatement("delete from users where userid=?");
preparedStatement.setInt(1, userId);
preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void updateUser(User user) {
try {
PreparedStatement preparedStatement = connection
.prepareStatement("update users set firstname=?, lastname=?, dob=?, email=?" +
"where userid=?");
// Parameters start with 1
preparedStatement.setString(1, user.getFirstName());
preparedStatement.setString(2, user.getLastName());
preparedStatement.setDate(3, new java.sql.Date(user.getDob().getTime()));
preparedStatement.setString(4, user.getEmail());
preparedStatement.setInt(5, user.getUserid());
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.setUserid(rs.getInt("userid"));
user.setFirstName(rs.getString("firstname"));
user.setLastName(rs.getString("lastname"));
user.setDob(rs.getDate("dob"));
user.setEmail(rs.getString("email"));
users.add(user);
}
} catch (SQLException e) {
e.printStackTrace();
}
return users;
}
public User getUserById(int userId) {
User user = new User();
try {
PreparedStatement preparedStatement = connection.
prepareStatement("select * from users where userid=?");
preparedStatement.setInt(1, userId);
ResultSet rs = preparedStatement.executeQuery();
if (rs.next()) {
user.setUserid(rs.getInt("userid"));
user.setFirstName(rs.getString("firstname"));
user.setLastName(rs.getString("lastname"));
user.setDob(rs.getDate("dob"));
user.setEmail(rs.getString("email"));
}
} catch (SQLException e) {
e.printStackTrace();
}
return user;
}
}
Controller :
Make a new Servlet inside the com.mvc.controller package called ‘UserController’
package com.mvc.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.mvc.dao.UserDao;
import com.mvc.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")){
int userId = Integer.parseInt(request.getParameter("userId"));
dao.deleteUser(userId);
forward = LIST_USER;
request.setAttribute("users", dao.getAllUsers());
} else if (action.equalsIgnoreCase("edit")){
forward = INSERT_OR_EDIT;
int userId = Integer.parseInt(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.setFirstName(request.getParameter("firstName"));
user.setLastName(request.getParameter("lastName"));
try {
Date dob = new SimpleDateFormat("MM/dd/yyyy").parse(request.getParameter("dob"));
user.setDob(dob);
} catch (ParseException e) {
e.printStackTrace();
}
user.setEmail(request.getParameter("email"));
String userid = request.getParameter("userid");
if(userid == null || userid.isEmpty())
{
dao.addUser(user);
}
else
{
user.setUserid(Integer.parseInt(userid));
dao.updateUser(user);
}
RequestDispatcher view = request.getRequestDispatcher(LIST_USER);
request.setAttribute("users", dao.getAllUsers());
view.forward(request, response);
}
}
Views :
1. Inside the WebContent folder, create ‘index.jsp’. It is the first / entry point of application . In this case, it will redirect the request to our servlet to list all the users in the database.
<%@ page language="java" contentType="text/html; charset=EUC-KR" pageEncoding="EUC-KR"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=EUC-KR"> <title>Insert title here</title> </head> <body> <jsp:forward page="/UserController?action=listUser" /> </body> </html>
2. Create the jsp ‘listUser.jsp’ to list all the users in the WebContent folder.we use JSTL ( not using scriplets here ) to connect the jsp and the servlet.
<%@ page language="java" contentType="text/html; charset=EUC-KR" pageEncoding="EUC-KR"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>Show All Users</title>
</head>
<body>
<table border=1>
<thead>
<tr>
<th>User Id</th>
<th>First Name</th>
<th>Last Name</th>
<th>DOB</th>
<th>Email</th>
<th colspan=2>Action</th>
</tr>
</thead>
<tbody>
<c:forEach items="${users}" var="user">
<tr>
<td><c:out value="${user.userid}" /></td>
<td><c:out value="${user.firstName}" /></td>
<td><c:out value="${user.lastName}" /></td>
<td><fmt:formatDate pattern="yyyy-MMM-dd" value="${user.dob}" /></td>
<td><c:out value="${user.email}" /></td>
<td><a href="UserController?action=edit&userId=<c:out value="${user.userid}"/>">Update</a></td>
<td><a href="UserController?action=delete&userId=<c:out value="${user.userid}"/>">Delete</a></td>
</tr>
</c:forEach>
</tbody>
</table>
<p><a href="UserController?action=insert">Add User</a></p>
</body>
</html>
3. Create ‘user.jsp’ to display the row values and edit / delete the same
<%@ page language="java" contentType="text/html; charset=EUC-KR" pageEncoding="EUC-KR"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<link type="text/css"
href="css/ui-lightness/jquery-ui-1.8.18.custom.css" rel="stylesheet" />
<script type="text/javascript" src="js/jquery-1.7.1.min.js"></script>
<script type="text/javascript" src="js/jquery-ui-1.8.18.custom.min.js"></script>
<title>Add new user</title>
</head>
<body>
<script>
$(function() {
$('input[name=dob]').datepicker();
});
</script>
<form method="POST" action='UserController' name="frmAddUser">
User ID : <input type="text" readonly="readonly" name="userid"
value="<c:out value="${user.userid}" />" />
First Name : <input
type="text" name="firstName"
value="<c:out value="${user.firstName}" />" />
Last Name : <input
type="text" name="lastName"
value="<c:out value="${user.lastName}" />" />
DOB : <input
type="text" name="dob"
value="<fmt:formatDate pattern="MM/dd/yyyy" value="${user.dob}" />" />
Email : <input type="text" name="email"
value="<c:out value="${user.email}" />" />
<input
type="submit" value="Submit" />
</form>
</body>
</html>
4. Make a folder js in WebContent and put Jquery files
5. Make folder css in WebContent and put CSS files
Deployment descriptor :
The web.xml file ( located in WebContent/WEB-INF) or the Deployment Descriptor section should be auto populated with decsriptions of servlets by Eclipse .
<?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" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5"> <display-name>SimpleJspServletDB</display-name> <welcome-file-list> <welcome-file>index.jsp</welcome-file> </welcome-file-list> <servlet> <description></description> <display-name>UserController</display-name> <servlet-name>UserController</servlet-name> <servlet-class>com.daniel.controller.UserController</servlet-class> </servlet> <servlet-mapping> <servlet-name>UserController</servlet-name> <url-pattern>/UserController</url-pattern> </servlet-mapping> </web-app>
Web server:
create a new instance of Apache Server ( preferably version 7.0 ) and add the project to run on it .
Apache—>Tomcat v7.0 Server
Output screens :
Computer Science and Engineering 