package org.comp.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.comp.model.Employee;
public class EmployeeDAO {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
int st;// status
public int insert(Employee employee) {
con = ConnectionFactory.getConnection();
try {
String query = "insert into employee(name,department,designation,salary) " + "values(?,?,?,?)";
ps = con.prepareStatement(query);
ps.setString(1, employee.getName());
ps.setString(2, employee.getDepartment());
ps.setString(3, employee.getDesignation());
ps.setBigDecimal(4, employee.getSalary());
st = ps.executeUpdate();
System.out.println("inserted employee " + st);
} catch (Exception e) {
st = -2;
e.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return st;
}
public int update(Employee employee) {
con = ConnectionFactory.getConnection();
try {
String query = "update employee set name=?,department=?,designation=?,salary=? where id=?";
ps = con.prepareStatement(query);
ps.setString(1, employee.getName());
ps.setString(2, employee.getDepartment());
ps.setString(3, employee.getDesignation());
ps.setBigDecimal(4, employee.getSalary());
ps.setLong(5, employee.getId());
st = ps.executeUpdate();
System.out.println("Updated employee " + st);
} catch (Exception e) {
st = -2;
e.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return st;
}
public List<Employee> fetchAll() {
List<Employee> employeeList = new ArrayList<Employee>();
con = ConnectionFactory.getConnection();
try {
String query = "select * from employee order by name";
ps = con.prepareStatement(query);
rs = ps.executeQuery();
while (rs.next()) {
Employee employee = new Employee();
employee.setId(rs.getLong("id"));
employee.setName(rs.getString("name"));
employee.setDepartment(rs.getString("department"));
employee.setDesignation(rs.getString("designation"));
employee.setSalary(rs.getBigDecimal("salary"));
employeeList.add(employee);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return employeeList;
}
public Employee fetch(long id) {
Employee employee = null;
con = ConnectionFactory.getConnection();
try {
String query = "select * from employee where id=?";
ps = con.prepareStatement(query);
ps.setLong(1, id);
rs = ps.executeQuery();
while (rs.next()) {
employee = new Employee();
employee.setId(rs.getLong("id"));
employee.setName(rs.getString("name"));
employee.setDepartment(rs.getString("department"));
employee.setDesignation(rs.getString("designation"));
employee.setSalary(rs.getBigDecimal("salary"));
System.out.println(employee);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return employee;
}
public int delete(Long id) {
con = ConnectionFactory.getConnection();
try {
String query = "delete from employee where id=?";
ps = con.prepareStatement(query);
ps.setLong(1, id);
st = ps.executeUpdate();
System.out.println("Deleted employee " + st);
} catch (Exception e) {
st = -2;
e.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return st;
}
public int count() {
int count = 0;
con = ConnectionFactory.getConnection();
try {
String query = "select count(*) from employee";
ps = con.prepareStatement(query);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
count = rs.getInt(1);
}
System.out.println("Total employees " + count);
} catch (Exception e) {
st = -2;
e.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return count;
}
}