Hi Guys..
Recently I have got question from my friend who is preparing for java test. He asked me to develop simple Java database project to retrieve and display table records from a particular table.
Here I have used MySql as the DB and used JSTL as well. To run this project java mysql driver and jstl jar files should be available in the WEB-INF/lib folder.
Here is DAO class
package tc.com.dao;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import tc.com.db.ConnectionFactory;
import tc.com.dto.Employee;
public class EmployeeDAOImpl {
Connection connection;
Statement stmt;
public EmployeeDAOImpl() { }
private static Connection getConnection()
throws SQLException,
ClassNotFoundException
{
Connection con = ConnectionFactory.
getInstance().getConnection();
return con;
}
public List viewAllEmployees(){
String query = "select SQL_CALC_FOUND_ROWS * from employee ";
List list = new ArrayList();
Employee employee = null;
try {
connection = getConnection();
stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
employee = new Employee();
employee.setEmployeeId(rs.getInt("emp_id"));
employee.setEmployeeName(rs.getString("emp_name"));
employee.setSalary(rs.getDouble("salary"));
employee.setDeptName(rs.getString("dept_name"));
list.add(employee);
}
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}finally
{
try {
if(stmt != null)
stmt.close();
if(connection != null)
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
}
Here is Connection Factory class
package tc.com.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class ConnectionFactory {
//static reference to itself
private static ConnectionFactory instance = new ConnectionFactory();
String url = "jdbc:mysql://localhost/tc";
String user = "root";
String password = "root";
String driverClass = "com.mysql.jdbc.Driver";
//private constructor
private ConnectionFactory() {
try {
Class.forName(driverClass);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static ConnectionFactory getInstance() {
return instance;
}
public Connection getConnection() throws SQLException,
ClassNotFoundException {
Connection connection =
DriverManager.getConnection(url, user, password);
return connection;
}
}
Here is Employee DTO class
package tc.com.dto;
public class Employee {
private int employeeId;
private String employeeName;
private double salary;
private String deptName;
public int getEmployeeId() {
return employeeId;
}
public void setEmployeeId(int employeeId) {
this.employeeId = employeeId;
}
public String getEmployeeName() {
return employeeName;
}
public void setEmployeeName(String employeeName) {
this.employeeName = employeeName;
}
public double getSalary() {
return salary;
}
public void setSalary(double salary) {
this.salary = salary;
}
public String getDeptName() {
return deptName;
}
public void setDeptName(String deptName) {
this.deptName = deptName;
}
}
Here is EmployeeServlet class
package tc.com.servlets;
import java.io.IOException;
import java.util.List;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import tc.com.dao.EmployeeDAOImpl;
import tc.com.dto.Employee;
/**
* Servlet implementation class EmployeeServlet
*/
public class EmployeeServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
EmployeeDAOImpl dao = new EmployeeDAOImpl();
List list = dao.viewAllEmployees();
//int noOfRecords = dao.getNoOfRecords();
//int noOfPages = (int) Math.ceil(noOfRecords * 1.0 / recordsPerPage);
request.setAttribute("employeeList", list);
//request.setAttribute("noOfPages", noOfPages);
//request.setAttribute("currentPage", page);
RequestDispatcher view = request.getRequestDispatcher("WEB-INF/jsp/displayEmployee.jsp");
view.forward(request, response);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
}
}
WebContent/index.jsp file
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<!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=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
<p><a href="employee.do">Show Employees</a></p>
</body>
</html>
WEB-INF/jsp/displayEmployee.jsp
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!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=ISO-8859-1">
<title>Employees</title>
</head>
<body>
<table border="1" cellpadding="5" cellspacing="5">
<tr>
<th>Emp ID</th>
<th>Emp Name</th>
<th>Salary</th>
<th>Dept Name</th>
</tr>
<c:forEach var="employee" items="${employeeList}">
<tr>
<td>${employee.employeeId}</td>
<td>${employee.employeeName}</td>
<td>${employee.salary}</td>
<td>${employee.deptName}</td>
</tr>
</c:forEach>
</table>
</body>
</html>
Finally the 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>DataShow</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>
<description></description>
<display-name>EmployeeServlet</display-name>
<servlet-name>EmployeeServlet</servlet-name>
<servlet-class>tc.interview3.servlets.EmployeeServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>EmployeeServlet</servlet-name>
<url-pattern>/employee.do</url-pattern>
</servlet-mapping>
</web-app>
Comments