Nov 152010
 

Steps
1. Create Web application folders and add JDBC, jstl jar
2. Create value object (POJO) to pass data.
3. Create DAO class
4. Create Servlet
5. Add servlet to web.xml
6. Create JSP view
7. Create and start test database
8. Run

1: Add following jars /WEB-INF/lib/

commons-lang-2.4.jar
hsqldb.jar
jstl.jar
standard.jar

2: com/company/servlet/Employee

package com.company.servlet;

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

public class Employee implements Serializable
{
  private Long empId;
  private String firstName;
  private String lastName;
  private Date dateofBirth;
  private Date hireDate;
  public Date getDateofBirth() {
    return dateofBirth;
  }
  public void setDateofBirth(Date dateofBirth) {
    this.dateofBirth = dateofBirth;
  }
  public Long getEmpId() {
    return empId;
  }
  public void setEmpId(Long empId) {
    this.empId = empId;
  }
  public String getFirstName() {
    return firstName;
  }
  public void setFirstName(String firstName) {
    this.firstName = firstName;
  }
  public Date getHireDate() {
    return hireDate;
  }
  public void setHireDate(Date hireDate) {
    this.hireDate = hireDate;
  }
  public String getLastName() {
    return lastName;
  }
  public void setLastName(String lastName) {
    this.lastName = lastName;
  }
}

3: com/company/servlet/EmployeeDao

package com.company.servlet;

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

public class EmployeeDao {
  private Connection getConnection() throws Exception {
    Connection con = DriverManager
        .getConnection("jdbc:hsqldb:hsql://localhost:9001/trupti_db");
    return con;
  }

  private void closeConnection(Connection con, Statement stmt, ResultSet rs)
      throws Exception {
    try {
      if (rs != null)
        rs.close();
      if (stmt != null)
        stmt.close();
      if (con != null)
        con.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }

  public ArrayList list() throws Exception {
    ArrayList employeeList = new ArrayList();
    Connection con = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
      con = getConnection();
      stmt = con
          .prepareStatement("SELECT EMP_ID, FIRST_NM, LAST_NM, DT_OF_BIRTH, HIRE_DATE FROM EMPLOYEE_DTL");
      rs = stmt.executeQuery();
      while (rs.next()) {
        Employee employee = new Employee();
        employee.setEmpId(rs.getLong("EMP_ID"));
        employee.setFirstName(rs.getString("FIRST_NM"));
        employee.setLastName(rs.getString("LAST_NM"));
        employee.setDateofBirth(rs.getDate("DT_OF_BIRTH"));
        employee.setHireDate(rs.getDate("HIRE_DATE"));
        employeeList.add(employee);
      }
    } catch (Exception e) {
      throw e;
    } finally {
      closeConnection(con, stmt, rs);
    }
    return employeeList;
  }

  public void insert(Employee param) throws Exception {
    Connection con = null;
    PreparedStatement ps = null;
    try {
      con = getConnection();
      ps = con
          .prepareStatement("INSERT INTO EMPLOYEE_DTL (EMP_ID, FIRST_NM, LAST_NM, DT_OF_BIRTH, HIRE_DATE) VALUES (NEXT VALUE FOR EMPLOYEE_SEQ,?,?,?,?)");
      ps.setString(1, param.getFirstName());
      ps.setString(2, param.getLastName());
      ps.setDate(3, new Date(param.getDateofBirth().getTime()));
      ps.setDate(4, new Date(param.getHireDate().getTime()));
      ps.executeUpdate();
    } catch (Exception e) {
      throw e;
    } finally {
      closeConnection(con, ps, null);
    }
  }

  public void update(Employee param) throws Exception {
    Connection con = null;
    PreparedStatement ps = null;
    try {
      con = getConnection();
      ps = con
          .prepareStatement("UPDATE EMPLOYEE_DTL SET FIRST_NM = ?, LAST_NM = ?, DT_OF_BIRTH = ?, HIRE_DATE = ? WHERE EMP_ID = ?");
      ps.setString(1, param.getFirstName());
      ps.setString(2, param.getLastName());
      ps.setDate(3, new Date(param.getDateofBirth().getTime()));
      ps.setDate(4, new Date(param.getHireDate().getTime()));
      ps.setLong(5, param.getEmpId());
      ps.executeUpdate();
    } catch (Exception e) {
      throw e;
    } finally {
      closeConnection(con, ps, null);
    }
  }

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

4: com/company/servlet/Servlet3

package com.company.servlet;

import java.util.ArrayList;

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

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.math.NumberUtils;
import org.apache.commons.lang.time.DateUtils;

public class Servlet3 extends HttpServlet {
  public void doGet(HttpServletRequest request, HttpServletResponse response)
      throws javax.servlet.ServletException, java.io.IOException {
    process(request, response);
  }

  public void doPost(HttpServletRequest request, HttpServletResponse response)
      throws javax.servlet.ServletException, java.io.IOException {
    process(request, response);
  }

  public void process(HttpServletRequest request, HttpServletResponse response)
      throws javax.servlet.ServletException, java.io.IOException {
    System.out.println("Inside Servlet2::process()");
    EmployeeDao dao = new EmployeeDao();
    String dateFormat[] = { "yyyy-MM-dd" };
    try {
      Employee param = new Employee();
      String empId = request.getParameter("empId");
      if (StringUtils.isNotBlank(empId)) {
        param.setEmpId(NumberUtils.createLong(empId));
      }
      param.setFirstName(request.getParameter("firstName"));
      param.setLastName(request.getParameter("lastName"));
      String dateofBirth = request.getParameter("dateofBirth");
      String hireDate = request.getParameter("hireDate");
      if (StringUtils.isNotBlank(dateofBirth)) {
        param.setDateofBirth(DateUtils.parseDate(dateofBirth, dateFormat));
      }
      if (StringUtils.isNotBlank(hireDate)) {
        param.setHireDate(DateUtils.parseDate(hireDate, dateFormat));
      }
      String actionContext = request.getParameter("actionContext");
      if (StringUtils.equals(actionContext, "insert")) {
        dao.insert(param);
      } else if (StringUtils.equals(actionContext, "update")) {
        dao.update(param);
      } else if (StringUtils.equals(actionContext, "delete")) {
        dao.delete(param);
      }
      ArrayList employeeList = dao.list();
      request.setAttribute("employeeList", employeeList);
    } catch (Exception e) {
      e.printStackTrace();
    }
    request.getRequestDispatcher("/jsp/employee_maintenance.jsp").forward(
        request, response);
  }
}

5: /WEB-INF/web.xml

<?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>Example14</display-name>
  <welcome-file-list>
    <welcome-file>index.html</welcome-file>
  </welcome-file-list>
  <servlet>
    <display-name>JDBC Servlet</display-name>
    <servlet-name>Servlet3</servlet-name>
    <servlet-class>com.company.servlet.Servlet3</servlet-class>
  </servlet>

  <servlet-mapping>
    <servlet-name>Servlet3</servlet-name>
    <url-pattern>/Servlet3</url-pattern>
  </servlet-mapping>
</web-app>

6: /WEB-INF/jsp/employee_maintenance.jsp

<%@ page import="com.company.servlet.Employee"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<jsp:useBean id="employeeList" 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.hireDate.value =  document.getElementById('hireDateIsrt').value;  
  }
  else if(insUpdDel == 'delete')
  {
    document.frm.empId.value =  document.getElementById('empId'+rownum).value;  
  }
  else if(insUpdDel == 'update')
  {
    document.frm.empId.value =  document.getElementById('empId'+rownum).value;
    document.frm.firstName.value =  document.getElementById('firstName'+rownum).value;  
    document.frm.lastName.value =  document.getElementById('lastName'+rownum).value;  
    document.frm.dateofBirth.value =  document.getElementById('dateofBirth'+rownum).value;  
    document.frm.hireDate.value =  document.getElementById('hireDate'+rownum).value;  
  }
  document.frm.actionContext.value =  insUpdDel;  
  document.frm.action = "<%=request.getContextPath()%>/Servlet3";
  document.frm.method = "post";
  document.frm.submit();
}
</script>
</head>
<body>
<form name='frm' >
<table bgcolor="#EFEFEF" border="1">
  <tr>
    <td><Strong>Employee Id</Strong></td>
    <td><Strong>First Name</Strong></td>
    <td><Strong>Last Name</Strong></td>
    <td><Strong>Date of Birth</Strong></td>
    <td><Strong>Hire Date</Strong></td>
    <td><Strong> </Strong></td>
    <td><Strong> </Strong></td>
  </tr>
  <c:forEach var="employee" items="${employeeList}" varStatus="counter">
    <tr>
      <td><input type='text' name='empId${counter.index}'
        value='${employee.empId}' size='20' readonly /></td>
      <td><input type='text' name='firstName${counter.index}'
        value='${employee.firstName}' size='20' /></td>
      <td><input type='text' name='lastName${counter.index}'
        value='${employee.lastName}' size='20' /></td>
      <td><input type='text' name='dateofBirth${counter.index}'
        value='${employee.dateofBirth}' size='20' /></td>
      <td><input type='text' name='hireDate${counter.index}'
        value='${employee.hireDate}' 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>
  <tr>
    <td> </td>
    <td><input type='text' name='firstNameIsrt' size='20' /></td>
    <td><input type='text' name='lastNameIsrt' size='20' /></td>
    <td><input type='text' name='dateofBirthIsrt' size='20' /></td>
    <td><input type='text' name='hireDateIsrt' size='20' /></td>
    <td><input type='button' value='Insert' onclick="submitForm('insert')" /></td>
    <td> </td>
  </tr>
</table>
<input type='hidden' name='empId'/>
<input type='hidden' name='firstName'/>
<input type='hidden' name='lastName'/>
<input type='hidden' name='dateofBirth'/>
<input type='hidden' name='hireDate'/>
<input type='hidden' name='actionContext'/>
</form>
</body>
</html>

Step 7: Create and start HSQLDB database
To configure and run HSQLDB follow this link

[Hssqldb running server mode jdbc connection]

Create table EMPLOYEE_DTL

CREATE CACHED TABLE PUBLIC.EMPLOYEE_DTL(EMP_ID INTEGER PRIMARY KEY,FIRST_NM VARCHAR(15),LAST_NM VARCHAR(15),DT_OF_BIRTH DATE,HIRE_DATE DATE)

Create sequence CREATE SEQUENCE employee_seq START WITH 1 INCREMENT BY 1 NO CYCLE;

Create test data

EMP_ID FIRST_NM LAST_NM DT_OF_BIRTH HIRE_DATE
------ -------- ------- ----------- ----------
1      Donald   Duck    1976-01-02  2009-08-12
2      Micky    Mouse   1976-01-02  2008-08-12
3      Pluto    Dog     1990-05-06  2008-08-12
7      Bat      Man     1990-05-06  2009-08-12
8      Bat      Man     1990-05-06  2009-08-12

To run it in Tomcat follow this link

[Setting context path tomcat 6 and mapping to external folder]

Now launch employee maintenance page
URL http://localhost:8080/Example14/Servlet3

 Posted by at 8:05 am