Posted In: CRUD, JDBC, JEE

Create Registration form Servlet + Oracle + Tomcat

Steps

1. Create USER_MASTER table in Oracle DB.
2. Create USER_MASTER_SEQ sequence in Oracle DB.
3. Add Datasource Setup in Tomcat. Refer Datasource Setup
4. Create Eclipse web project
5. Create User value object with attributes.
6. Create Servlet
7. Update web.xml path to access Servlet
8. Add DAO to do CRUD operations
9. Create JSP to display user list and buttons to perform actions
10. Test by accessing application. Perform ADD/UPDATE/DELETE operations
11. Buttons will trigger form submit and data will be posted to Servlet
12. Servlet will call appropriate Dao methods to do ADD/UPDATE/DELETE

Note : Known drawbacks

This is an example application and provided for learning purpose. You should apply this and use java frameworks like Spring or CSS frameworks like BootStrap or JS frameworks like Jquery, Angular. There are known issues in this application that you should be aware of
1. No single Controller
2. Password is stored without encryption
3. Connection handling should be delegated to the framework
4. No transaction management
5. May not work in different browsers
6. No data validation
7. No dependency management (e.g. MAVEN)
8 No testing integration
9. No pagination
10. May be targeted for XSS security vulnerability

1. Oracle Database Setup

Create table

CREATE TABLE USER_MASTER(
   USER_ID NUMBER(10),
   USER_LOGIN_ID varchar2(20),
   USER_PASSWORD varchar2(20),
   FIRST_NM VARCHAR2(50),
   LAST_NM VARCHAR2(50),
   DT_OF_BIRTH DATE,
   EMAIL_ID  VARCHAR2(50),
   PRIMARY KEY( USER_ID )
);

Create sequence

CREATE SEQUENCE USER_MASTER_SEQ
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

2. Tomcat Setup

Tomcat datasource setup

<Resource name="jdbc/javausecase_oracledb" auth="Container" 
type="javax.sql.DataSource"
maxTotal="50" maxIdle="30" maxWaitMillis="10000"
username="hr" password="mypwd"
driverClassName="oracle.jdbc.driver.OracleDriver" 
url="jdbc:oracle:thin:@localhost:1521:xe"/>

3. Source code

package com.company.app;

import java.io.Serializable;
import java.util.Date;

public class User implements Serializable {
	private static final long serialVersionUID = 65007985219089594L;
	private int userId;
	private String userLoginId;
	private String userPassword;
	private String firstName;
	private String lastName;
	private Date dateofBirth;
	private String emailId;
	public int getUserId() {
		return userId;
	}
	public void setUserId(int userId) {
		this.userId = userId;
	}
	public String getUserLoginId() {
		return userLoginId;
	}
	public void setUserLoginId(String userLoginId) {
		this.userLoginId = userLoginId;
	}
	public String getUserPassword() {
		return userPassword;
	}
	public void setUserPassword(String userPassword) {
		this.userPassword = userPassword;
	}
	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 getDateofBirth() {
		return dateofBirth;
	}
	public void setDateofBirth(Date dateofBirth) {
		this.dateofBirth = dateofBirth;
	}
	public String getEmailId() {
		return emailId;
	}
	public void setEmailId(String emailId) {
		this.emailId = emailId;
	}
	@Override
	public String toString() {
		return "User [userId=" + userId + ", userLoginId="
		        + userLoginId + ", userPassword=" + userPassword
		        + ", firstName=" + firstName + ", lastName="
		        + lastName + ", dateofBirth=" + dateofBirth
		        + ", emailId=" + emailId + "]";
	}
}
package com.company.app;

import java.io.IOException;
import java.util.ArrayList;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

public class UserServlet extends HttpServlet {
	private static final long serialVersionUID = 4971144548847149832L;
	private static final Logger LOGGER = LogManager
	        .getLogger(UserServlet.class);
	private UserDao userDao = new UserDao();

	public UserServlet() {
		super();
	}

	public void init(ServletConfig config) throws ServletException {
		userDao.setDataSource(getDataSource());
		LOGGER.debug("datasource setup to userDao is complete");
	}

	protected void doGet(HttpServletRequest request,
	        HttpServletResponse response)
	        throws ServletException, IOException {
		doProcess(request, response);
	}

	protected void doPost(HttpServletRequest request,
	        HttpServletResponse response)
	        throws ServletException, IOException {
		doProcess(request, response);
	}

	private void doProcess(HttpServletRequest request,
	        HttpServletResponse response)
	        throws ServletException, IOException {
		String dateFormat[] = { "yyyy-MM-dd" };
		try {
			User param = new User();
			String userId = request.getParameter("userId");
			if (StringUtils.isNotBlank(userId)) {
				param.setUserId(NumberUtils.createInteger(userId));
			}
			param.setFirstName(request.getParameter("firstName"));
			param.setLastName(request.getParameter("lastName"));
			param.setEmailId(request.getParameter("emailId"));
			param.setUserLoginId(request.getParameter("userLoginId"));
			param.setUserPassword(request.getParameter("userPassword"));
			String dateofBirth = request.getParameter("dateofBirth");
			if (StringUtils.isNotBlank(dateofBirth)) {
				param.setDateofBirth(
				        DateUtils.parseDate(dateofBirth, dateFormat));
			}
			String actionContext = request
			        .getParameter("actionContext");
			if (StringUtils.equals(actionContext, "insert")) {
				userDao.insert(param);
			} else if (StringUtils.equals(actionContext, "update")) {
				userDao.update(param);
			} else if (StringUtils.equals(actionContext, "delete")) {
				userDao.delete(param);
			}
			ArrayList<User> userList = userDao.list();
			request.setAttribute("userList", userList);
		} catch (Exception e) {
			LOGGER.error(e, e);
		}
		request.getRequestDispatcher("/jsp/user.jsp").forward(request,
		        response);

	}

	private DataSource getDataSource() {
		DataSource ds = null;
		try {
			Context ctx = new InitialContext();
			ds = (DataSource) ctx.lookup(
			        "java:comp/env/jdbc/javausecase_oracledb");
		} catch (Exception e) {
			LOGGER.error(e, e);
		}
		return ds;
	}
}
<?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_2_5.xsd"
	id="WebApp_ID" version="2.5">
	<display-name>Example20175</display-name>
	<servlet>
		<servlet-name>UserServlet</servlet-name>
		<servlet-class>com.company.app.UserServlet</servlet-class>
	</servlet>
	<servlet-mapping>
		<servlet-name>UserServlet</servlet-name>
		<url-pattern>/user</url-pattern>
	</servlet-mapping>
</web-app>
package com.company.app;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import javax.sql.DataSource;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

public class UserDao {
	private static final Logger LOGGER = LogManager
	        .getLogger(UserDao.class);
	private DataSource dataSource;

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	public ArrayList<User> list() throws Exception {
		ArrayList<User> userList = new ArrayList<User>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = getConnection();
			stmt = con.prepareStatement(
			        "SELECT USER_ID, FIRST_NM, LAST_NM, DT_OF_BIRTH, "
			        + "EMAIL_ID, USER_LOGIN_ID "
			        + "FROM USER_MASTER");
			rs = stmt.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setUserId(rs.getInt("USER_ID"));
				user.setFirstName(rs.getString("FIRST_NM"));
				user.setLastName(rs.getString("LAST_NM"));
				user.setDateofBirth(rs.getDate("DT_OF_BIRTH"));
				user.setEmailId(rs.getString("EMAIL_ID"));
				user.setUserLoginId(rs.getString("USER_LOGIN_ID"));
				userList.add(user);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			closeConnection(con, stmt, rs);
		}
		return userList;
	}

	public void insert(User param) throws Exception {
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = getConnection();
			ps = con.prepareStatement(
			        "INSERT INTO USER_MASTER "
			        + "(USER_ID, FIRST_NM, LAST_NM, DT_OF_BIRTH, "
			        + "EMAIL_ID, USER_LOGIN_ID, USER_PASSWORD) "
			        + "VALUES (USER_MASTER_SEQ.NEXTVAL,?,?,?,?,?,?)");
			ps.setString(1, param.getFirstName());
			ps.setString(2, param.getLastName());
			ps.setDate(3, new java.sql.Date(
			        param.getDateofBirth().getTime()));
			ps.setString(4, param.getEmailId());
			ps.setString(5, param.getUserLoginId());
			ps.setString(6, param.getUserPassword());
			ps.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			closeConnection(con, ps, null);
		}
	}

	public void update(User param) throws Exception {
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = getConnection();
			ps = con.prepareStatement(
			        "UPDATE USER_MASTER SET "
			        + "FIRST_NM = ?, LAST_NM = ?, DT_OF_BIRTH = ?, "
			        + "EMAIL_ID = ? "
			        + "WHERE USER_ID = ?");
			ps.setString(1, param.getFirstName());
			ps.setString(2, param.getLastName());
			ps.setDate(3, new java.sql.Date(
			        param.getDateofBirth().getTime()));
			ps.setString(4, param.getEmailId());
			ps.setLong(5, param.getUserId());
			ps.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			closeConnection(con, ps, null);
		}
	}

	public void delete(User param) throws Exception {
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = getConnection();
			ps = con.prepareStatement(
			        "DELETE FROM USER_MASTER WHERE USER_ID = ?");
			ps.setLong(1, param.getUserId());
			ps.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			closeConnection(con, ps, null);
		}
	}

	private Connection getConnection() throws Exception {
		return dataSource.getConnection();
	}

	private void closeConnection(Connection connection,
	        Statement stmt, ResultSet rs) {
		try {
			if (rs != null) {
				rs.close();
			}
			if (stmt != null) {
				stmt.close();
			}
			if (connection != null) {
				connection.close();
			}
		} catch (Exception e) {
			LOGGER.error(e, e);
		}
	}
}
<%@ page import="com.company.app.User"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<jsp:useBean id="userList" class="java.util.ArrayList"
  scope="request" />
<html>
<head>
<script>
function submitForm(insUpdDel,rownum)
{
	if(insUpdDel == 'insert')
	{
		document.frm.firstName.value =  document.getElementById('firstNameIsrt').value;	
		document.frm.lastName.value =  document.getElementById('lastNameIsrt').value;	
		document.frm.dateofBirth.value =  document.getElementById('dateofBirthIsrt').value;	
		document.frm.emailId.value =  document.getElementById('emailIdIsrt').value;	
		document.frm.userLoginId.value =  document.getElementById('userLoginIdIsrt').value;	
		document.frm.userPassword.value =  document.getElementById('userPasswordIsrt').value;	
	}
	else if(insUpdDel == 'delete')
	{
		document.frm.userId.value =  document.getElementById('userId'+rownum).value;	
		document.frm.firstName.value =  '';	
		document.frm.lastName.value =  '';	
		document.frm.dateofBirth.value =  '';	
		document.frm.emailId.value =  '';
		document.frm.userLoginId.value =  '';
		document.frm.userPassword.value =  '';
	}
	else if(insUpdDel == 'update')
	{
		document.frm.userId.value =  document.getElementById('userId'+rownum).value;
		document.frm.firstName.value =  document.getElementById('firstName'+rownum).value;	
		document.frm.lastName.value =  document.getElementById('lastName'+rownum).value;	
		document.frm.emailId.value =  document.getElementById('emailId'+rownum).value;	
		document.frm.dateofBirth.value =  document.getElementById('dateofBirth'+rownum).value;	
	}
	document.frm.actionContext.value =  insUpdDel;  
	document.frm.action = "<%=request.getContextPath()%>/user";
	document.frm.method = "post";
	document.frm.submit();
}
</script>
</head>
<body>
<form name='frm' >
<table bgcolor="#EFEFEF" border="1">
  <tr>
    <td><Strong>Login ID</Strong></td><td><input type='text' id='userLoginIdIsrt' name='userLoginIdIsrt' size='20' /></td>
  </tr><tr>  
    <td><Strong>Password</Strong></td><td><input type='password' id='userPasswordIsrt' name='userPasswordIsrt' size='20' /></td>
  </tr><tr>  
    <td><Strong>First Name</Strong></td><td><input type='text' id='firstNameIsrt' name='firstNameIsrt' size='20' /></td>
  </tr><tr>  
    <td><Strong>Last Name</Strong></td><td><input type='text' id='lastNameIsrt' name='lastNameIsrt' size='20' /></td>
  </tr><tr>  
    <td><Strong>Date of Birth (yyyy-MM-dd)</Strong></td><td><input type='text' id='dateofBirthIsrt' name='dateofBirthIsrt' size='20' /></td>
  </tr><tr>  
    <td><Strong>Email ID</Strong></td><td><input type='text' id='emailIdIsrt' name='emailIdIsrt' size='20' /></td>
  </tr><tr>  
    <td><input type='button' value='Insert' onclick="submitForm('insert')" /></td>
  </tr>
</table>
<br/>
<table bgcolor="#EFEFEF" border="1">
  <tr>
    <td><Strong>User ID</Strong></td>
    <td><Strong>Login ID</Strong></td>
    <td><Strong>First Name</Strong></td>
    <td><Strong>Last Name</Strong></td>
    <td><Strong>Date of Birth</Strong></td>
    <td><Strong>Email ID</Strong></td>
    <td><Strong>&nbsp;</Strong></td>
    <td><Strong>&nbsp;</Strong></td>
  </tr>
  <c:forEach var="user" items="${userList}" varStatus="counter">
    <tr>
      <td><input type='text' name='userId${counter.index}' id='userId${counter.index}' 
        value='${user.userId}' size='20' disabled="disabled" /></td>
      <td><input type='text' name='userLoginId${counter.index}' id='userLoginId${counter.index}' 
        value='${user.userLoginId}' size='20' disabled="disabled" /></td>
      <td><input type='text' name='firstName${counter.index}' id='firstName${counter.index}'
        value='${user.firstName}' size='20' /></td>
      <td><input type='text' name='lastName${counter.index}' id='lastName${counter.index}'
        value='${user.lastName}' size='20' /></td>
      <td><input type='text' name='dateofBirth${counter.index}' id='dateofBirth${counter.index}'
        value='${user.dateofBirth}' size='20' /></td>
      <td><input type='text' name='emailId${counter.index}' id='emailId${counter.index}'
        value='${user.emailId}' size='20' /></td>
      <td><input type='button' value='Delete'  onclick="submitForm('delete',${counter.index})" /></td>
      <td><input type='button' value='Update'  onclick="submitForm('update',${counter.index})" /></td>
    </tr>
  </c:forEach>
</table>
<input type='hidden' name='userId' id='userId'/>
<input type='hidden' name='firstName' id='firstName'/>
<input type='hidden' name='lastName' id='lastName'/>
<input type='hidden' name='dateofBirth' id='dateofBirth'/>
<input type='hidden' name='emailId' id='emailId'/>
<input type='hidden' name='userLoginId' id='userLoginId'/>
<input type='hidden' name='userPassword' id='userPassword'/>
<input type='hidden' name='actionContext' id='actionContext'/>
</form>
</body>
</html>

Download example source and eclipse project

Tags: , , , , ,

by , on January 23rd, 2017

  • Categories