jQuery DataTable Paging Model Mapping

공통 Model을 통해 개발속도 향상을 기대할 수 있도록함.

예제에서는 queryDsl을 사용한다.

 

 

 

Front DataTable 구현부

 

프론트 검색 동작

// Datatable 검색
function fnSearch(){
    $('#dataTable').DataTable().ajax.reload();
}

 

프론트 페이징 사이즈 변경 후 검색

// 페이징 사이즈 변경
function fnChangePagingSize(el){
    $('#dataTable').DataTable().page.len($(el).val());
    fnSearch();
}

 

 

백엔드 페이징 요청 자동매핑 Model

@Getter
@NoArgsConstructor
@AllArgsConstructor
public class DataTablePagingModel {
    private int start = 0;
    private int length = 10;
    private String orderColumn;
    private String orderColumnName;
    private Order orderDir;
    private boolean useOrder = false;

    public void setStart(int start) {
        this.start = Math.max(start, 0);
    }
    public void setLength(int length){
        this.length = Math.max(length, 0);
    }

    public void setOrder(HttpServletRequest request) {
        if (request.getParameter("order[0][column]") != null && request.getParameter("order[0][dir]") != null) {
            this.orderColumn = request.getParameter("order[0][column]");
            this.orderColumnName = request.getParameter("columns[" + orderColumn + "][data]");

            String orderDirection = request.getParameter("order[0][dir]");
            if("asc".equals(orderDirection)){
                this.orderDir = Order.ASC;
            }else if("desc".equals(orderDirection)){
                this.orderDir = Order.DESC;
            }

            this.useOrder = true;
        }
    }

    public OrderSpecifier<?> orderBy(String columnName, Order order){
        if(this.isUseOrder()){
            return new OrderSpecifier(this.getOrderDir(), Expressions.stringPath(this.getOrderColumnName()));
        }else{
            return new OrderSpecifier(order, Expressions.stringPath(columnName));
        }
    }
}

 

DataTable Response Model

@Getter
public class DataTableModel {
    private long recordsTotal;
    private long recordsFiltered;
    private List<?> data;

    @Builder
    public DataTableModel(long recordsTotal, List<?> data) {
        this.recordsTotal = recordsTotal;
        this.recordsFiltered = recordsTotal;
        this.data = data;
    }
}

 

 

컨트롤러 구현

@ResponseBody
@RequestMapping(value = {"/list"})
public DataTableModel list(HttpServletRequest request, DataTablePagingModel paging
        , CompanyFilterModel companyFilter){

	// paging 객체 Set
    paging.setOrder(request);

	// 실제 데이터 조회 (Entity대신 Model로 반환한다)
    QueryResults<CompanyListModel> companyList = companyService.findCompanyList(paging, companyFilter);

	// 응답값 Set
    return DataTableModel.builder()
            .recordsTotal(companyList.getTotal())
            .data(companyList.getResults())
            .build();
}

 

서비스 구현

public QueryResults<CompanyListModel> findCompanyList(DataTablePagingModel paging, CompanyFilterModel companyFilter) {
    return companyRepository.findCompanyList(paging, companyFilter);
}

 

Repository 인터페이스 설정

QueryResults<CompanyListModel> findCompanyList(DataTablePagingModel paging, CompanyFilterModel companyFilter);

Repository 구현

@Override
public QueryResults<CompanyListModel> findCompanyList(DataTablePagingModel paging, CompanyFilterModel companyFilter) {
    return jpaQueryFactory.select(
            Projections.fields(CompanyListModel.class
                    , companyEntity.idx
                    , companyEntity.companyName
                    , companyEntity.representName
                    , ExpressionUtils.as(
                            JPAExpressions
                                    .select(usersEntity.count())
                                    .from(usersEntity)
                                    .innerJoin(userCompanyEntity)
                                    .on(userCompanyEntity.idxUser.eq(usersEntity.idx))
                                    .where(userCompanyEntity.idxCompany.eq(companyEntity.idx))
                            , "employeeNumber")
                    , Expressions.stringTemplate("function('date_format', {0}, '{1s}')", companyEntity.establishedDate, ConstantImpl.create("%Y.%m.%d")).as("establishedDate")
            )
    	).from(companyEntity)
            .where(startDate(companyFilter.getSearchDateFilter(), companyFilter.getStartDateFrom())
                    , endDate(companyFilter.getSearchDateFilter(), companyFilter.getEndDateTo())
                    , input(companyFilter.getInput()))
            .offset(paging.getStart())
            .limit(paging.getLength())
            .orderBy(paging.orderBy(companyEntity.idx.toString(), Order.DESC))
            .fetchResults();	// paging, list 쿼리를 알아서 호출해준다.
}