| 1. Database Table Structure |
EMPLOYEE TABLE:
CREATE TABLE `employee` (
`EMPLOYEE_KEY` bigint PRIMARY KEY,
`EMP_ID` varchar(100) DEFAULT NULL,
`NAME` varchar(100) DEFAULT NULL,
`DEPT_CODE` varchar(100) DEFAULT NULL,
`BASIC_SAL` decimal(8,2) DEFAULT NULL,
`DATE_OF_JOINING` date DEFAULT NULL,
`ADDRESS` varchar(500) DEFAULT NULL
) DEPENDANT:
CREATE TABLE `dependant` (
`DEPENDANT_KEY` bigint PRIMARY KEY,
`DEPENDANT_ID` varchar(100) DEFAULT NULL,
`DEPENDANT_NAME` varchar(100) DEFAULT NULL,
`EMP_ID` varchar(100) DEFAULT NULL,
`RELATION_WITH_EMP` varchar(100) DEFAULT NULL,
`AGE` decimal(5,2) DEFAULT NULL
) HOSPITALISATION:
CREATE TABLE `hospitalisation` (
`HOSPITALISATION_KEY` bigint PRIMARY KEY,
`DEPENDANT_ID` varchar(100) DEFAULT NULL,
`EMP_ID` varchar(100) DEFAULT NULL,
`ADMISSION_DATE` date DEFAULT NULL,
`RELEASE_DATE` date DEFAULT NULL,
`HOSPITAL_NAME` varchar(200) DEFAULT NULL,
`TREATMENT_DTLS` varchar(500) DEFAULT NULL,
`HOSPITAL_BILL_AMT` double(7,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
| 2. JAVA BEAN CLASSES (Getter and Setter
methods are not given to keep the document shorter.) |
Employee :
public class Employee {
Dependants dependants;
private String address;
private Double basicSal;
private Date dateOfJoining;
private String deptCode;
private String empId;
private Long employeeKey;
private String name;
} Employees:
public class Employees {
private List<Employee> employeeList = new ArrayList();
} Dependant:
public class Dependant {
Hospitalisations hospitalisations;
private String age;
private Long employeeKey;
private String dependantId;
private Long dependantKey;
private String dependantName;
private String empId;
private String relationWithEmp;
private String tableName;
}
Dependants:
public class Dependants {
private List<Dependant> dependantList = new ArrayList();
}
Hospitalisations:
public class Hospitalisations {
private List<Hospitalisation> hospitalisationList = new ArrayList();
}
Hospitalisation:
public class Hospitalisation {
private Long hospitalisationKey;
private String dependantId;
private String empId;
private Date admissionDate;
private Date releaseDate;
private String hospitalName;
private String treatmentDtls;
private Double hospitalBillAmt;
private String defaultstr;
}
|
| 3. DAO Class |
| |
DAO Code:
public class EmployeeDAO {
private static Logger log = LoggerFactory.getLogger(EmployeeDAO.class);
@Autowired
private JdbcTemplate jdbcTemplate;
public List<Employee> getEmplist(Employee employee) {
String str="";
str=str+"SELECT\n";
str=str+" EMPLOYEE.ADDRESS,\n";
str=str+" EMPLOYEE.BASIC_SAL,\n";
str=str+" EMPLOYEE.DATE_OF_JOINING,\n";
str=str+" EMPLOYEE.DEPT_CODE,\n";
str=str+" EMPLOYEE.EMP_ID,\n";
str=str+" EMPLOYEE.EMPLOYEE_KEY,\n";
str=str+" EMPLOYEE.NAME,\n";
str=str+" DEPENDANT.AGE,\n";
str=str+" DEPENDANT.DEPENDANT_ID,\n";
str=str+" DEPENDANT.DEPENDANT_KEY,\n";
str=str+" DEPENDANT.DEPENDANT_NAME,\n";
str=str+" DEPENDANT.EMP_ID,\n";
str=str+" DEPENDANT.RELATION_WITH_EMP,\n";
str=str+" HOSPITALISATION.ADMISSION_DATE,\n";
str=str+" HOSPITALISATION.DEPENDANT_ID,\n";
str=str+" HOSPITALISATION.EMP_ID,\n";
str=str+" HOSPITALISATION.HOSPITAL_BILL_AMT,\n";
str=str+" HOSPITALISATION.HOSPITAL_NAME,\n";
str=str+" HOSPITALISATION.HOSPITALISATION_KEY,\n";
str=str+" HOSPITALISATION.RELEASE_DATE,\n";
str=str+" HOSPITALISATION.TREATMENT_DTLS \n";
str=str+"FROM\n";
str=str+" EMPLOYEE,\n";
str=str+" DEPENDANT,\n";
str=str+" HOSPITALISATION \n";
str=str+"WHERE\n";
str=str+" 1 = 1 \n";
str=str+" AND EMPLOYEE.EMP_ID = DEPENDANT.EMP_ID \n";
str=str+" AND DEPENDANT.DEPENDANT_ID = HOSPITALISATION.DEPENDANT_ID \n";
str=str+" AND EMPLOYEE.BASIC_SAL < 10000\n";
return jdbcTemplate.query(str, new ResultSetExtractor<List
<Employee> >() {
@Override
public List<Employee> extractData(ResultSet rs) throws SQLException, DataAccessException {
List<Employee> list = new ArrayList<Employee>();
Map<Long, Employee> employeeKeyEmployeeMap = new HashMap<Long, Employee>();
Map<Long, Dependant> dependantKeyDependantMap = new HashMap<Long, Dependant>();
Map<Long, Hospitalisation> hospitalisationKeyHospitalisationMap = new HashMap<Long, Hospitalisation>();
while (rs.next()) {
Long employeeKey = rs.getLong("EMPLOYEE_KEY");
Employee employee = employeeKeyEmployeeMap.get(employeeKey);
if (employee == null) {
employee = new Employee();
list.add(employee);
employee.setEmployeeKey(employeeKey);
log.info("employeeKey=" + employee.getEmployeeKey());
employee.setAddress(rs.getString("ADDRESS"));
employee.setBasicSal(rs.getDouble("BASIC_SAL"));
employee.setDateOfJoining(rs.getDate("DATE_OF_JOINING"));
employee.setDeptCode(rs.getString("DEPT_CODE"));
employee.setEmpId(rs.getString("EMP_ID"));
employee.setEmployeeKey(rs.getLong("EMPLOYEE_KEY"));
employee.setName(rs.getString("NAME"));
}
Long dependantKey = rs.getLong("BOOK_KEY");
Dependant dependant = dependantKeyDependantMap.get(dependantKey);
log.info("dependantKeyML=" + dependantKey);
if (dependant == null) {
dependant = new Dependant();
if (employee.getDependants() == null)
employee.setDependants(new Dependants());
if (employee.getDependants().getDependantList() == null)
employee.getDependants().setDependantList(new ArrayList<Dependant>());
employee.getDependants().getDependantList().add(dependant);
dependantKeyDependantMap.put(dependantKey, dependant);
dependant.setDependantKey(dependantKey);
dependant.setAge(rs.getString("AGE"));
dependant.setDependantId(rs.getString("DEPENDANT_ID"));
dependant.setDependantKey(rs.getLong("DEPENDANT_KEY"));
dependant.setDependantName(rs.getString("DEPENDANT_NAME"));
dependant.setEmpId(rs.getString("EMP_ID"));
dependant.setRelationWithEmp(rs.getString("RELATION_WITH_EMP"));
}
Long hospitalisationKey = rs.getLong("BOOK_KEY");
Hospitalisation hospitalisation = hospitalisationKeyHospitalisationMap.get(hospitalisationKey);
log.info("hospitalisationKeyML=" + hospitalisationKey);
if (hospitalisation == null) {
hospitalisation = new Hospitalisation();
if (dependant.getHospitalisations() == null)
dependant.setHospitalisations(new Hospitalisations());
if (dependant.getHospitalisations().getHospitalisationList() == null)
dependant.getHospitalisations().setHospitalisationList(new ArrayList<Hospitalisation>());
dependant.getHospitalisations().getHospitalisationList().add(hospitalisation);
hospitalisationKeyHospitalisationMap.put(hospitalisationKey, hospitalisation);
hospitalisation.setHospitalisationKey(hospitalisationKey);
hospitalisation.setAdmissionDate(rs.getDate("ADMISSION_DATE"));
hospitalisation.setDependantId(rs.getString("DEPENDANT_ID"));
hospitalisation.setEmpId(rs.getString("EMP_ID"));
hospitalisation.setHospitalBillAmt(rs.getDouble("HOSPITAL_BILL_AMT"));
hospitalisation.setHospitalName(rs.getString("HOSPITAL_NAME"));
hospitalisation.setHospitalisationKey(rs.getLong("HOSPITALISATION_KEY"));
hospitalisation.setReleaseDate(rs.getDate("RELEASE_DATE"));
hospitalisation.setTreatmentDtls(rs.getString("TREATMENT_DTLS"));
}
}
return list;
}
});
}
}
|
|