Let's go inside

EC为我们提供的功能有：

过滤（filter）

排序（sort）

分页（pagination）

导出（export）

可是在实际应用中，EC自带的过滤功能往往不够强大。下面的文章就是如何将EC的过滤功能独立出来，放在一个单独的页面query.jsp中。

我们不再使用FilterSet, FilterQuery等等。而是将第1次的查询条件dto保存在session里面，接下来的sort, pagination, export均在这个dto基础之上进行。

为了减少类的个数。我将service和dao放在一起。使用struts做为应用的框架

action这样写。

(1)EC用action (红色部分为需要改动的地方)

    private static int defaultRowsDisplayed = Integer.parseInt(ConfigSet.

            getInstance().get("defaultRowsDisplayed"));

    public ActionForward execute(ActionMapping mapping,

                                 ActionForm actionForm,

                                 HttpServletRequest request,

                                 HttpServletResponse response) {

        HttpSession session = request.getSession();

        MapPoiActionForm form = (MapPoiActionForm) actionForm;

        String type = StringUtil.nvl(form.getType(),"display");

        if (type.equals("display")){

            // ----  构造查询条件  -----------

            MapPoiDto dto = (MapPoiDto) session.getAttribute("mp_dto");

            if (dto == null && form!=null) {

               dto = new MapPoiDto();

dto.setPoi_id(form.getPoi_id());

……

               session.setAttribute("mp_dto ", dto);               

}

            MapPoisDao dao = new MapPoisDao();

            int totalRows = dao.getBeanCount(dto);

            Context context = new HttpServletRequestContext(request);

            LimitFactory limitFactory = new TableLimitFactory(context,"ec_mp");

            Limit limit = new TableLimit(limitFactory);

            limit.setRowAttributes(totalRows, defaultRowsDisplayed);

            Collection mappois = dao.getBeans(limit.getSort(),

                                                limit.getRowStart(),

                                                limit.getRowEnd(), dto);

            request.setAttribute("mappois", mappois);

            request.setAttribute("totalRows", new Integer(totalRows));

            return mapping.findForward("display");

和以前的代码相比，

            Collection presidents = presidentsService.getPresidents(limit.

                    getFilterSet(), limit.getSort(), limit.getRowStart(),

                    limit.getRowEnd());

少了一个limit.getFilterSet(),  多了dto，意思是不使用EC的过滤功能，　而用自己的dto来构造，但保留其它功能。

Session中的dto必须在list.jsp的出口处被移除。比如index.jsp。最好是在query.jsp中也写上session.removeAttribute(“mp_dto”); 完全不用session的做法尚在研究中。

接下来看另一大块：DAO层如何写，　代码量比较多，但只有红色的部分需要重写，其它只需拷贝就可以了。

public class MapPoisDao {

    public final static String tableName = "MAP_POI";

    public final static String beanName = "lp.mappoi.dao.MapPoi";

    public MapPoisDao() {

    }

    private String startQuery() {

        return "SELECT * FROM ("

                + "SELECT " + EcUtil.getBeanPropertyAsString(beanName)

                + ", rownum AS my_rownum FROM (SELECT * FROM " + tableName;

    }

// service

    public int getBeanCount(MapPoiDto dto) {

        String query = "";

        query = "SELECT count(*) FROM " + tableName;     // init

        query = filterQuery(dto, query);            　　　　// where

        return getRowCount(query);

    }

    public Collection getBeans(Sort sort,

                                 int rowStart, int rowEnd, MapPoiDto dto) {

        String query = startQuery();                 // init

        query = filterQuery(dto, query);             // where

        query = sortQuery(sort, query);              // order by

        query = limitQuery(rowStart, rowEnd, query);　 // limit

        return getBeans(query);

    }

    private String filterQuery(MapPoiDto dto, String query) {

        StringBuffer sb = new StringBuffer(query);

        if (query.toUpperCase().indexOf("WHERE") == -1) {

            sb.append(" WHERE 1 = 1 ");

        }

        if (dto != null) {

        // ----------- 构造where子句 -------------------

          sb.append(SqlUtil.getLike("poi_id", dto.getPoi_id()));

          ……

           sb.append(SqlUtil.getDateLike("update_date",dto.getUpdate_date(),"yy-MM-dd"));

        }

        return sb.toString();

    }

    private String sortQuery(Sort sort, String query) {

        if (!sort.isSorted()) {

            String defaultSortOrder = null;

            defaultSortOrder = " ORDER BY update_date DESC";

            if (StringUtil.isNotBlank(defaultSortOrder)) {

                return query + defaultSortOrder;

            }

            return query;

        }

        String property = sort.getProperty();

        String sortOrder = sort.getSortOrder();

        StringBuffer result = new StringBuffer(query + " ORDER BY ");

        result.append(property + " " + sortOrder);

        return result.toString();

    }

    private String limitQuery(int rowStart, int rowEnd, String query) {

        return query += SqlUtil.getSqlEnd(rowEnd, rowStart);

    }

    private Collection getBeans(final String query) {

        Connection conn = null;

        PreparedStatement pstmt = null;

        ResultSet rs = null;

        try {

            conn = Sys.getConnection();

            pstmt = conn.prepareStatement(query);

            rs = pstmt.executeQuery();

            ArrayList table = new ArrayList();

while (rs.next()) {

// ------- 构造 POJO -------

                MapPoi mp = new MapPoi();

                mp.setPoi_id(rs.getString("poi_id"));

                     ……

                table.add(mp);

            }

            return table;

        }

        catch (Exception ex) {

            ex.printStackTrace();

        }

        finally {

            Sys.close(rs, pstmt, conn);

        }

        return null;

    }

    private int getRowCount(final String query) {

        return Sys.getDb().getRowCount(query);

    }