[JavaScript] Kendo UI Web Grid; Server Side Paging/Sorting/Filtering 구현

Kendo Web UI Grid에 사용되는 kendo.data.DataSource 오브젝트는 그 자체만으로 매우 편리하고 강력한 기능을 가지고 있다. 자체적으로 제공되는 페이징, 소팅, 필터링 기능을 통해 서버 사이드를 통한 데이터베이스로부터의 데이터 획득 기능이 상당히 편리해진다.

Spring MVC 3 기반의 내부 프로젝트에 사용할 목적으로 아래와 같이 kendo.data.DataSource의 요청에 대응할 수 있는 코드를 구현해봤다.

<Model: KendoUiParam.java>

import java.util.List;
import com.google.common.base.Objects;
import com.google.common.base.Strings;

public class KendoUiParam {
    private int page;
    private int pageSize;
    private int skip;
    private int take;
    private List<Sort> sort;
    private Filter filter;

    private String setValue(Object value) {
        if (value.toString().length() < 10) {
            return "'" + value + "'";
        }
        if (Objects.equal(value.toString().charAt(4), '-') && Objects.equal(value.toString().charAt(7), '-') && Objects.equal(value.toString().length(), 10)) {
            return "TO_DATE('" + value + "', 'YYYY-MM-DD')";
        }
        return "'" + value + "'";
    }

    public boolean hasWhere() {
        if (!Objects.equal(this.filter, null)) {
            return true;
        }
        return false;
    }

    public String getWhere() {
        if (Objects.equal(this.filter, null)) {
            return null;
        }
        StringBuilder where = new StringBuilder();
        where.append("(");
        for (int i = 0; i < this.filter.getFilters().size(); i++) {
            if (Strings.isNullOrEmpty(this.filter.getFilters().get(i).getValue())) {
                continue;
            } else if (Objects.equal(this.filter.getFilters().get(i).getOperator(), "eq")) {
                where.append(this.filter.getFilters().get(i).getField() + " = " + setValue(this.filter.getFilters().get(i).getValue()));
            } else if (Objects.equal(this.filter.getFilters().get(i).getOperator(), "neq")) {
                where.append(this.filter.getFilters().get(i).getField() + " != " + setValue(this.filter.getFilters().get(i).getValue()));
            } else if (Objects.equal(this.filter.getFilters().get(i).getOperator(), "lt")) {
                where.append(this.filter.getFilters().get(i).getField() + " < " + setValue(this.filter.getFilters().get(i).getValue()));
            } else if (Objects.equal(this.filter.getFilters().get(i).getOperator(), "lte")) {
                where.append(this.filter.getFilters().get(i).getField() + " <= " + setValue(this.filter.getFilters().get(i).getValue()));
            } else if (Objects.equal(this.filter.getFilters().get(i).getOperator(), "gt")) {
                where.append(this.filter.getFilters().get(i).getField() + " > " + setValue(this.filter.getFilters().get(i).getValue()));
            } else if (Objects.equal(this.filter.getFilters().get(i).getOperator(), "gte")) {
                where.append(this.filter.getFilters().get(i).getField() + " >= " + setValue(this.filter.getFilters().get(i).getValue()));
            } else if (Objects.equal(this.filter.getFilters().get(i).getOperator(), "startswith")) {
                where.append(this.filter.getFilters().get(i).getField() + " LIKE '%" + this.filter.getFilters().get(i).getValue() + "'");
            } else if (Objects.equal(this.filter.getFilters().get(i).getOperator(), "endswith")) {
                where.append(this.filter.getFilters().get(i).getField() + " LIKE '" + this.filter.getFilters().get(i).getValue() + "%'");
            } else if (Objects.equal(this.filter.getFilters().get(i).getOperator(), "contains")) {
                where.append(this.filter.getFilters().get(i).getField() + " LIKE '%" + this.filter.getFilters().get(i).getValue() + "%'");
            }
            where.append(" " + this.filter.getLogic() + " ");
        }
        where.append("1=1)");
        return where.toString();
    }

    public boolean hasOrderBy() {
        if (!Objects.equal(this.sort, null)) {
            if (!Objects.equal(this.sort.size(), 0)) {
                return true;
            }
        }
        return false;
    }

    public String getOrderBy() {
        if (Objects.equal(this.sort, null)) {
            return null;
        }
        StringBuilder orderBy = new StringBuilder();
        for (int i = 0; i < this.sort.size(); i++) {
            orderBy.append(this.sort.get(i).getField() + " " + sort.get(i).getDir());
            if (!Objects.equal(i, this.sort.size() - 1)) {
                orderBy.append(", ");
            }
        }
        return orderBy.toString();
    }

    public boolean hasPaging() {
        if (!Objects.equal(this.page, 0) || !Objects.equal(this.pageSize, 0)) {
            return true;
        }
        return false;
    }

    public String getPagingSql(String sql) {
        if (!hasPaging()) {
            return sql;
        }
        String prefix = "SELECT * FROM (SELECT ROWNUM AS ROW_NUMBER, COUNT(*) OVER() TOTAL_ROW_COUNT, A.* FROM ( ";
        String postfix = " ) A ) WHERE ROWNUM <= :PAGE_SIZE AND ROW_NUMBER > (:PAGE_NUMBER-1) * :PAGE_SIZE";
        return prefix + sql + postfix;
    }

    public int getPage() {
        return page;
    }

    public void setPage(int page) {
        this.page = page;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }

    public int getSkip() {
        return skip;
    }

    public void setSkip(int skip) {
        this.skip = skip;
    }

    public int getTake() {
        return take;
    }

    public void setTake(int take) {
        this.take = take;
    }

    public List<Sort> getSort() {
        return sort;
    }

    public void setSort(List<Sort> sort) {
        this.sort = sort;
    }

    public Filter getFilter() {
        return filter;
    }

    public void setFilter(Filter filter) {
        this.filter = filter;
    }

    @Override
    public String toString() {
        return "KendoUiParam [page=" + page + ", pageSize=" + pageSize + ", skip=" + skip + ", take=" + take + ", sort=" + sort + ", filter=" + filter + "]";
    }
}

<Model: Sort.java>

public class Sort {
    private String field;
    private String dir;

    public String getField() {
        return field;
    }

    public void setField(String field) {
        this.field = field;
    }

    public String getDir() {
        return dir;
    }

    public void setDir(String dir) {
        this.dir = dir;
    }

    @Override
    public String toString() {
        return "Sort [field=" + field + ", dir=" + dir + "]";
    }
}

<Model: Filter.java>

import java.util.List;

public class Filter {
    private List<Filters> filters;
    private String logic;

    public List<Filters> getFilters() {
        return filters;
    }

    public void setFilters(List<Filters> filters) {
        this.filters = filters;
    }

    public String getLogic() {
        return logic;
    }

    public void setLogic(String logic) {
        this.logic = logic;
    }

    @Override
    public String toString() {
        return "Filter [filters=" + filters + ", logic=" + logic + "]";
    }
}

<Model: Filters.java>

public class Filters {
    private String field;
    private String operator;
    private String value;

    public String getField() {
        return field;
    }

    public void setField(String field) {
        this.field = field;
    }

    public String getOperator() {
        return operator;
    }

    public void setOperator(String operator) {
        this.operator = operator;
    }

    public String getValue() {
        return value;
    }

    public void setValue(String value) {
        this.value = value;
    }

    @Override
    public String toString() {
        return "Filters [field=" + field + ", operator=" + operator + ", value=" + value + "]";
    }
}

<Controller>
    @RequestMapping(value = "/pr/ajax/readStkMtlMaster", method = RequestMethod.POST, produces = "application/json;charset=UTF-8")
    @ResponseBody
    public Map<String, Object> readStkMtlMaster(@RequestBody KendoUiParam param) {
        Map<String, Object> response = new HashMap<String, Object>();
        try {
            logger.info("==================== [MM-PUR] readStkMtlMaster() | Parameter='" + param.toString() + "'");
            List<Map<String, Object>> result = mmService.readStkMtlMaster(param);
            response.put("data", result);
            if (Objects.equal(result.size(), 0)) {
                response.put("total", 0);
            } else {
                response.put("total", result.get(0).get("TOTAL_ROW_COUNT"));
            }
            logger.info("==================== [MM-PUR] readStkMtlMaster() | Parameter='" + response.toString() + "'");
            return response;
        } catch (Exception ex) {
            Map<String, Object> errorResponse = JsonUtil.createErrorResponse(ex);
            return errorResponse;
        }
    }

<Service>

    public List<Map<String, Object>> readStkMtlMaster(KendoUiParam param) {
        logger.info("==================== [MM-PUR] readStkMtlMaster() | Parameter='" + param.toString() + "'");
        List<Map<String, Object>> result = mmDAO.readStkMtlMaster(param);
        logger.info("==================== [MM-PUR] readStkMtlMaster() | Return='" + result.toString() + "'");

        return result;
    }

<DAO>

    public List<Map<String, Object>> readStkMtlMaster(KendoUiParam param) {
        logger.info("==================== [MM-PUR] readStkMtlMaster() | Parameter='" + param.toString() + "'");
        MapSqlParameterSource paramSource = new MapSqlParameterSource();
        SqlBuilder q = new SqlBuilder();
        q.SELECT("PURCHASETYPE, FACTORYNAME, CONSUMABLESPECNAME, CONSUMABLESPECVERSION, DESCRIPTION, CONSUMABLETYPE, CONSUMEUNIT, IQAAQL, IQADCSPECNAME, CREATEUSER, CREATETIME");
        q.FROM("CONSUMABLESPEC");
        if (param.hasWhere()) {
            q.WHERE(param.getWhere());
        }
        if (param.hasOrderBy()) {
            q.ORDER_BY(param.getOrderBy());
        }
        if (param.hasPaging()) {
            paramSource.addValue("PAGE_NUMBER", param.getPage());
            paramSource.addValue("PAGE_SIZE", param.getPageSize());
        }
        List<Map<String, Object>> result = namedParameterJdbcTemplate.queryForList(param.getPagingSql(q.toString()), paramSource);
        logger.info("==================== [MM-PUR] readStkMtlMaster() | Return='" + result.toString() + "'");

        return result;
    }

<View: HTML>

<div>
  <blockquote>
    <h2>원부자재 PR 생성</h2>
  </blockquote>
</div>

<div class="alert alert-info">
  <strong>원부자재 기준정보 조회 조건 설정</strong>
</div>

<table class="table table-bordered table-hover">
  <tbody>
    <tr>
      <td style="width: 30%">PURCHASETYPE:</td>
      <td style="width: 70%"><a class="searchCriteria" data-type="text" id="purchaseType" name="purchaseType"></a></td>
    </tr>
    <tr>
      <td>CONSUMALBLESPECNAME:</td>
      <td><a class="searchCriteria" data-type="text" id="consumableSpecName" name="consumableSpecName"></a></td>
    </tr>
    <tr>
      <td>DESCRIPTION:</td>
      <td><a class="searchCriteria" data-type="text" id="description" name="description"></a></td>
    </tr>
    <tr>
      <td>CREATETIME:</td>
      <td><a class="searchCriteria" data-type="date" id="createTimeStart" name="createTimeStart"></a> ~ <a class="searchCriteria" data-type="date" id="createTimeEnd" name="createTimeEnd"></a></td>
    </tr>
  </tbody>
</table>

<table class="table">
  <tbody>
    <tr>
      <td style="width: 30%"></td>
      <td style="width: 70%"><button class="btn btn-primary input-medium" id="searchButton">조회</button>&nbsp;&nbsp; <button class=
      "btn btn-success input-medium" id="resetButton">초기화</button></td>
    </tr>
  </tbody>
</table>

<div class="alert alert-info">
  <strong>조회 결과</strong>
</div>

<div class="k-content" style="overflow: auto; width: 100%">
    <div id="stkMtlTable"></div>
</div>
<p></p>

<View: JavaScript>

var setSearchCriteria = function() {
    $('#purchaseType').editable({
      emptytext: '미입력'
    });
    $('#consumableSpecName').editable({
      emptytext: '미입력'
    });
    $('#description').editable({
      emptytext: '미입력'
    });
    $('#createTimeStart').editable({
      emptytext: '미입력',
      format: 'yyyy-mm-dd'
    });
    $('#createTimeEnd').editable({
      emptytext: '미입력',
      format: 'yyyy-mm-dd'
    });
    $('.searchCriteria').editable('setValue', '');
  };
setSearchCriteria();

var dataSource = new kendo.data.DataSource({
  serverPaging: true,
  serverSorting: true,
  serverFiltering: true,
  pageSize: 10,
  filter: {
    field: '1',
    operator: 'neq',
    value: '1'
  },
  transport: {
    read: {
      type: 'post',
      dataType: 'json',
      contentType: 'application/json;charset=UTF-8',
      url: '/pr/ajax/readStkMtlMaster'
    },
    parameterMap: function(data, operation) {
      return JSON.stringify(data);
    }
  },
  schema: {
    data: function(response) {
      return response.data;
    },
    total: function(response) {
      return response.total;
    }
  }
});

$('#stkMtlTable').kendoGrid({
  dataSource: dataSource,
  sortable: {
    mode: "multiple",
    allowUnsort: true
  },
  selectable: true,
  navigatable: false,
  pageable: {
    refresh: true,
    pageSizes: true
  },
  scrollable: true,
  columnMenu: false,
  autoBind: false,
  columns: [{
    field: 'PURCHASETYPE',
    title: 'PURCHASETYPE',
    width: 110
  }, {
    field: 'FACTORYNAME',
    title: 'FACTORYNAME',
    width: 110
  }, {
    field: 'CONSUMABLESPECNAME',
    title: 'CONSUMABLESPECNAME',
    width: 175
  }, {
    field: 'CONSUMABLESPECVERSION',
    title: 'CONSUMABLESPECVERSION',
    width: 185
  }, {
    field: 'DESCRIPTION',
    title: 'DESCRIPTION',
    width: 600
  }, {
    field: 'CONSUMABLETYPE',
    title: 'CONSUMABLETYPE',
    width: 140
  }, {
    field: 'CONSUMEUNIT',
    title: 'CONSUMEUNIT',
    width: 130
  }, {
    field: 'IQAAQL',
    title: 'IQAAQL',
    width: 90
  }, {
    field: 'IQADCSPECNAME',
    title: 'IQADCSPECNAME',
    width: 130
  }, {
    field: 'CREATEUSER',
    title: 'CREATEUSER',
    width: 120
  }, {
    field: 'CREATETIME',
    title: 'CREATETIME',
    width: 120,
    template: '#=Date.create(CREATETIME).format("{yyyy}-{MM}-{dd}")#'
  }]
});

$('#searchButton').click(function() {
  var searchCriteria = $('.searchCriteria').editable('getValue');
  var query = {
    page: 1,
    pageSize: 10,
    sort: {
      field: 'CREATETIME',
      dir: 'desc'
    },
    filter: [{
      field: 'PURCHASETYPE',
      operator: 'eq',
      value: searchCriteria.purchaseType
    }, {
      field: 'CONSUMABLESPECNAME',
      operator: 'contains',
      value: searchCriteria.consumableSpecName
    }, {
      field: 'DESCRIPTION',
      operator: 'contains',
      value: searchCriteria.description
    }, {
      field: 'CREATETIME',
      operator: 'gte',
      value: searchCriteria.createTimeStart
    }, {
      field: 'CREATETIME',
      operator: 'lte',
      value: searchCriteria.createTimeEnd
    }]
  };
  dataSource.query(query);
});

$('#resetButton').click(function() {
  $('.searchCriteria').editable('setValue', '');
  var query = {
    filter: {
      field: '1',
      operator: 'neq',
      value: '1'
    }
  };
  dataSource.query(query);
});

<View: 실행화면>