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
|
We are using Oracle database and in our table primary key is non-numeric(VARCHAR2). How should we implementation pagination.?
Reply;
Alwyas we may not have the numeric column as primary key. Our pagination purpose is just to fetch only 200 records (page size) from the database instead of fetching all the matching records. Pagination depends on which database we are using. 1.) If we are using Oracle database, we can use rownum pseudo column to fetch only a page size (200) records. 2.) If we are using sqlserver, we can use" set rowcount 200" command to restrict to fetch only 200 records. 3.) If we are using MySQL, we can use 'LIMIT' keyword to fetch 200 records. example. - SELECT * FROM TABLE_NAME LIMIT 200