| 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
) |
| 2. JAVA BEAN CLASSES |
Employees:
public class Employees {
private List<Employee> employeeList = new ArrayList();
} 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;
} Dependants:
public class Dependants {
private List<Dependant< dependantList = 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;
}
|
| 3. DAO Class |
DAO Code:
package jdbc.example.pagintion;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Repository;
import jdbc.example.dependant.bean.Dependant;
import jdbc.example.dependant.bean.Dependants;
import jdbc.example.employee.bean.Employee;
@Repository("getEmpListApiDAO")
public class GetEmpListApiDAO {
private static Logger log = LoggerFactory.getLogger(GetEmpListApiDAO.class);
private int pageSize = 2;
@Autowired
private JdbcTemplate jdbcTemplate;
public List<Employee> getEmpListWithPagenation(Employee employee, String lastRecordKeyValue) {
String pageMinKey;
String pageMaxKey;
List<Object> minMaxKyList = getMinMaxKeyFromHeaderTableForAPage(employee, lastRecordKeyValue);
if (minMaxKyList.isEmpty())
return new ArrayList<Employee>();
pageMinKey = "" + minMaxKyList.get(0);
pageMaxKey = "" + minMaxKyList.get(1);
String extendedWhereClause = "";
List<Object> paramList = new ArrayList();
String str = "";
str = str + "SELECT\n";
str = str + " EMPLOYEE.*,\n";
str = str + " DEPENDANT.*\n";
str = str + "FROM\n";
str = str + " EMPLOYEE,\n";
str = str + " DEPENDANT \n";
str = str + "WHERE\n";
str = str + " 1 = 1 \n";
str = str + " AND EMPLOYEE.EMP_ID = DEPENDANT.EMP_ID AND EMPLOYEE.BASIC_SAL > 1000 \n";
String getEmpListApiQueryWithPagenation = str;
String orderBy = " ASC ";
extendedWhereClause = extendedWhereClause + " AND EMPLOYEE.NAME >= '" + pageMinKey + "' AND EMPLOYEE.NAME <= '"
+ pageMaxKey + "' ";
extendedWhereClause = extendedWhereClause + " ORDER BY EMPLOYEE.NAME " + orderBy;
getEmpListApiQueryWithPagenation = getEmpListApiQueryWithPagenation + extendedWhereClause;
System.out.println("getEmpListApiQueryWithPagenation=" + getEmpListApiQueryWithPagenation);
return jdbcTemplate.query(getEmpListApiQueryWithPagenation, paramList.toArray(),
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>();
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.setEmployeeKey(rs.getLong("EMPLOYEE_KEY"));
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.setName(rs.getString("NAME"));
}
Long dependantKey = rs.getLong("DEPENDANT_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.setDependantKey(rs.getLong("DEPENDANT_KEY"));
dependant.setAge(rs.getString("AGE"));
dependant.setDependantId(rs.getString("DEPENDANT_ID"));
dependant.setDependantName(rs.getString("DEPENDANT_NAME"));
dependant.setRelationWithEmp(rs.getString("RELATION_WITH_EMP"));
}
}
return list;
}
});
}
public List<Object> getMinMaxKeyFromHeaderTableForAPage(Employee employee, String lastRcordKeyValue) {
List<Object> paramList = new ArrayList();
if (lastRcordKeyValue == null)
lastRcordKeyValue = "0";
String str = "";
str = str + "SELECT\n";
str = str + " EMPLOYEE.*,\n";
str = str + " DEPENDANT.*\n";
str = str + "FROM\n";
str = str + " EMPLOYEE,\n";
str = str + " DEPENDANT \n";
str = str + "WHERE\n";
str = str + " 1 = 1 \n";
str = str + " AND EMPLOYEE.EMP_ID = DEPENDANT.EMP_ID EMPLOYEE.BASIC_SAL > 1000 \n";
String getEmpListApiQueryWithPagenation = str;
String orderBy = " ASC ";
String extendedWhereClause = " ";
extendedWhereClause = extendedWhereClause + " AND EMPLOYEE.NAME >= '" + lastRcordKeyValue + "' ";
extendedWhereClause = extendedWhereClause + " ORDER BY EMPLOYEE.NAME " + orderBy + " LIMIT " + pageSize + " ";
getEmpListApiQueryWithPagenation = getEmpListApiQueryWithPagenation + extendedWhereClause;
System.out.println("minMaxSql=" + getEmpListApiQueryWithPagenation);
return jdbcTemplate.query(getEmpListApiQueryWithPagenation, paramList.toArray(),
new ResultSetExtractor<List<Object>>() {
@Override
public List<Object> extractData(ResultSet rs) throws SQLException, DataAccessException {
Object minKey = null;
Object maxKey = null;
List<Object> list = new ArrayList<Object>();
int count = 1;
while (rs.next()) {
if (count == 1) {
minKey = rs.getString("NAME");
count++;
}
maxKey = rs.getString("NAME");
}
if (minKey == null)
return list;
list.add(minKey);
list.add(maxKey);
return list;
}
});
}
}
|
| 4. Service Class |
Service Code:
package jdbc.example.pagintion;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.TransactionException;
import jdbc.example.employee.bean.Employee;
@Service("getEmpListApiService")
public class GetEmpListApiService {
@Autowired
private GetEmpListApiDAO getEmpListApiDAO;
private static Logger log = LoggerFactory.getLogger(GetEmpListApiService.class);
public List<Employee> getEmplistWithPagenation(Employee employee, String lastRecordKey)
throws TransactionException {
try {
return getEmpListApiDAO.getEmpListWithPagenation(employee, lastRecordKey);
} catch (Exception ex) {
throw ex;
}
}
}
|
Here only service, bean classes and DAO code is given. Entire
example code will be available for download very soon.
Please provide your feedback here
|