1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
|
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package com.bestray.transtracking.dao.impl;
import com.bestray.transtracking.dao.EmployeeDao;
import com.bestray.transtracking.dao.mapper.EmployeeResultSetExtractor;
import com.bestray.trastrack.domain.Address;
import com.bestray.trastrack.domain.Employee;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import com.bestray.transtracking.dto.EmployeeSearchResultDTO;
/**
*
* @author Sumit
*/
public class EmployeeDaoImpl implements EmployeeDao{
private DataSource ds;
private Address address;
private static final String EMPLOYEE_QUERY = "select * from employee_master ";
private static final String ORDER_BY = "order by id desc";
private static final String EMPLOYEE_RESULT_QRY = "select emp.id,emp.emp_name,co.name,ad.address1,ad.city,emp.contact_1,emp.open_balance,emp.lock "
+ "from employee_master emp, code co,address ad where emp.category_id = co.id and emp.prsnt_address_id = ad.id order by emp.id desc";
private static final String EMPLOYEE_NEW_RESULT_QUERY="SELECT employee_master.`id` AS id, "+
"employee_master.`emp_name` AS emp_name, "+
"(SELECT code.`name` FROM code WHERE code.`id`=employee_master.`category_id`) AS categoryname, "+
"employee_master.`contact_1` AS contact_1, "+
"(SELECT address.`address1` FROM `address` WHERE address.`id` = employee_master.`prsnt_address_id`) "+
"AS 'address1', employee_master.`open_balance` AS 'open_balance' , employee_master.`lock` "+
"FROM `employee_master` employee_master order by id desc ";
public EmployeeDaoImpl(DataSource ds) {
this.ds = ds;
}
public void addEmployee(Employee employee) throws Exception{
JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
String sql = "INSERT INTO employee_master (`emp_name`,`emp_shortname`,`category_id`,`marital_status`,`birth_date`,`gender`,`nationality`,`hire_date`,`father_name`,`prmnt_address_id`,`prsnt_address_id`,`contact_1`,`contact_2`,`open_balance`,`balance_mode`,`lock`,`mother_name`) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
jdbcTemplate.update(sql,employee.getEmployee_Name(),employee.getEmployee_ShortName(),employee.getCategoryCodeId(),employee.getMarital_Status(),employee.getBirth_date(),employee.getGender(),employee.getNationality(),employee.getHire_date(),employee.getEmployee_father_Name(),employee.getPermanentAddress()!= null?employee.getPermanentAddress().getId():null,employee.getPresentAddress()!= null?employee.getPresentAddress().getId():null,employee.getContact1(),employee.getContact2(),employee.getOpen_Balance(),employee.getBalance_Type(),employee.getLock(),employee.getEmployee_mother_Name());
}
public List<Employee> findAllEmployees() throws Exception{
JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
StringBuffer queryString = new StringBuffer(EMPLOYEE_QUERY).append("order by emp_name asc");
List<Employee> employeeList = (List<Employee>) jdbcTemplate.query(queryString.toString(), new EmployeeResultSetExtractor());
return employeeList;
}
public List<EmployeeSearchResultDTO> findAllEmployeesList() throws Exception{
JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
List<EmployeeSearchResultDTO> employeeList = (List<EmployeeSearchResultDTO>) jdbcTemplate.query(EMPLOYEE_NEW_RESULT_QUERY, new EmployeeSearchResultSetExtractor());
return employeeList;
}
public Employee findEmplRegistraion(Long employeeId) throws Exception{
JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
StringBuffer queryString = new StringBuffer(EMPLOYEE_QUERY)
.append("where id = ? ")
.append(ORDER_BY);
List<Employee> employeeList = (List<Employee>) jdbcTemplate.query(queryString.toString(), new Object[]{employeeId}, new EmployeeResultSetExtractor());
return employeeList.size()>0?employeeList.get(0):null;
}
public void updateEmployee(Employee employee)throws Exception{
JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
String sql = "update employee_master set `emp_name` =?,`emp_shortname` =?,`category_id` =?,`marital_status` =?,`birth_date` =?,`gender` =?,`nationality` =?,`hire_date` =?,`father_name` =?,`prmnt_address_id` =?,`prsnt_address_id` =?,`contact_1` =?,`contact_2` =?,`open_balance` =?,`balance_mode` =?,`lock` =?,`mother_name` =? WHERE id = ? ";
jdbcTemplate.update(sql, new Object[]{employee.getEmployee_Name(), employee.getEmployee_ShortName() ,employee.getCategoryCodeId() , employee.getMarital_Status(), employee.getBirth_date(), employee.getGender(), employee.getNationality(), employee.getHire_date(), employee.getEmployee_father_Name(),employee.getPermanentAddress()!=null && employee.getPermanentAddress().getId()>0?employee.getPermanentAddress().getId():null, employee.getPresentAddress()!=null && employee.getPresentAddress().getId()>0?employee.getPresentAddress().getId():null, employee.getContact1(), employee.getContact2(), employee.getOpen_Balance(), employee.getBalance_Type(), employee.getLock(),employee.getEmployee_mother_Name(),employee.getId()});
}
public void delete(Long employeeId) throws Exception{
JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
jdbcTemplate.update("DELETE FROM employee_master WHERE id = ?", new Object[]{employeeId});
}
class EmployeeSearchResultSetExtractor implements ResultSetExtractor<Object>{
public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
List <EmployeeSearchResultDTO> emplist = new ArrayList<EmployeeSearchResultDTO>();
while (rs.next()) {
EmployeeSearchResultDTO employee = new EmployeeSearchResultDTO();
employee.setEmployeeId(rs.getString("id"));
employee.setEmployeeName(rs.getString("emp_name"));
employee.setEmpleoyeeCategory(rs.getString("categoryname"));
employee.setAddress(rs.getString("address1"));
// employee.setCity(rs.getString("city"));
employee.setContactNbr(rs.getString("contact_1"));
employee.setOpeningBalance(rs.getString("open_balance"));
employee.setLock(rs.getString("lock"));
emplist.add(employee);
}
return emplist;
}
}
}
|
girijabapi
View complete history
Commits RSS feed for