empmodel.java

This is module used JSP

/*
 * Created on Mar 13, 2009
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import Emp.Employee;
import config.db_config;

/**
 * @author VietHung Romano
 * 
 *         TODO To change the template for this generated type comment go to
 *         Window - Preferences - Java - Code Style - Code Templates
 */
public class EmpModel {
	static Connection conn;

	static PreparedStatement ps;

	static ResultSet rs;

	/*
	 * @param Employee Add employee information. Employee information will
	 * insert into database
	 */
	public static int Add(Employee emp) {
		int result = -1;
		try {
			conn = db_config.getConnection();
			ps = conn
					.prepareStatement("INSERT INTO Employee VALUES(?,?,?,?,?,?,?,?)");
			ps.setString(1, emp.getEmpNo());
			ps.setString(2, emp.getEmpName());
			ps.setString(3, emp.getBirthDate());
			ps.setString(4, emp.getAddress());
			ps.setString(5, emp.getPhone());
			ps.setString(6, emp.getEmail());
			ps.setString(7, emp.getJob());
			ps.setString(8, emp.getSalary());
			ps.execute();
			result=1;
			conn.close();
			ps.close();
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return result;
	}

	/*
	 * @param Employee @param empNo Edit employee information
	 */
	public static int Edit(Employee emp, String _empNo) {
		int result = -1;
		try {
			conn = db_config.getConnection();
			ps = conn
					.prepareStatement("UPDATE Employee SET EmpName=?,Birthdate=?,Address=?,Phone=?,Email = ?,Job=?,Salary=? WHERE EmpNo='"
							+ _empNo + "'");
			ps.setString(1, emp.getEmpName());
			ps.setString(2, emp.getBirthDate());
			ps.setString(3, emp.getAddress());
			ps.setString(4, emp.getPhone());
			ps.setString(5, emp.getEmail());
			ps.setString(6, emp.getJob());
			ps.setString(7, emp.getSalary());
			ps.executeUpdate();
			conn.close();
			ps.close();
			result = 1;
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return result;
	}

	/*
	 * @param empNo Remove employee
	 */
	public static int Delete(String _empNo) {
		int result = -1;
		try {
			conn = db_config.getConnection();
			Statement stmt = conn.createStatement();
			result= stmt.executeUpdate("DELETE FROM Employee WHERE EmpNo='" + _empNo
					+ "'");
			conn.close();
			stmt.close();			
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return result;
	}

	public String countEmp() {
		String count = null;
		try {
			conn = db_config.getConnection();
			Statement stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT COUNT(EmpNo) AS Cnt FROM Employee");
			while (rs.next()) {
				count = rs.getString("Cnt");
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return count;
	}

	/*
	 * Retrieve all employee @author VietHung Romano
	 * 
	 * TODO To change the template for this generated type comment go to Window
	 * - Preferences - Java - Code Style - Code Templates
	 */
	public static Vector<Employee> getEmployeeList() {
		Vector<Employee> Emp_vt = new Vector<Employee>();
		try {
			conn = db_config.getConnection();
			ps = conn
					.prepareStatement("SELECT EmpNo,EmpName,DATEPART(d,BirthDate) AS date, DATEPART(m,BirthDate) AS Month, DATEPART(yyyy,BirthDate) AS Year ,Address, Phone, Email, Job, Salary "
							+ " FROM employee");
			rs = ps.executeQuery();
			while (rs.next()) {
				Employee emp = new Employee();
				emp.setEmpNo(rs.getString("EmpNo"));
				emp.setEmpName(rs.getString("EmpName"));
				emp.setBirthDate(rs.getString("date") + "/"
						+ rs.getString("Month") + "/" + rs.getString("Year"));
				emp.setAddress(rs.getString("Address"));
				emp.setPhone(rs.getString("Phone"));
				emp.setEmail(rs.getString("Email"));
				emp.setJob(rs.getString("Job"));
				emp.setSalary(rs.getString("Salary"));
				Emp_vt.add(emp);
			}
			conn.close();
			ps.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return Emp_vt;

	}

	public static Employee getEmployeeByID(String _empNo) {
		Employee emp = new Employee();
		try {
			conn = db_config.getConnection();
			ps = conn
					.prepareStatement("SELECT EmpNo,EmpName,DATEPART(d,BirthDate) AS date, DATEPART(m,BirthDate) AS Month, DATEPART(yyyy,BirthDate) AS Year ,Address, Phone, Email, Job, Salary "
							+ " FROM employee WHERE EmpNo= ?");
			ps.setString(1, _empNo);
			rs = ps.executeQuery();
			if (rs.next()) {
				emp.setEmpNo(rs.getString("EmpNo"));
				emp.setEmpName(rs.getString("EmpName"));
				emp.setDate(rs.getString("date"));
				emp.setMonth(rs.getString("Month"));
				emp.setYear(rs.getString("Year"));
				emp.setAddress(rs.getString("Address"));
				emp.setPhone(rs.getString("Phone"));
				emp.setEmail(rs.getString("Email"));
				emp.setJob(rs.getString("Job"));
				emp.setSalary(rs.getString("Salary"));
				return emp;
			}
			conn.close();
			ps.close();
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		}
		return null;
	}
}