分页设计 与 高级查询 的 结合设计

一:高级查询+分页查询

操作步骤:

        0):把pageSize和currentPage封装到QueryObject对象中(任何查询对象都得接受用户传入的这两个数据).

        1):在IProductDAO接口中定义高级查询+分页查询方法.

        2):在ProductDAOImpl中实现该方法.

        3):测试代码.

解决翻页是丢失高级查询数据的问题:

        造成的原因:翻页时,会重新发一次请求,该请求和高级查询表单没有关系.

        解决方案:使用JS解决:在翻页的时候:同时提交高级查询表单参数 和 当前第几页(currentPage).

        <a href="javascript:go(${1})">首页</a>
        <a href="javascript:go(${pageResult.prevPage})">上页</a>
        <a href="javascript:go(${pageResult.nextPage})">下页</a>
        <a href="javascript:go(${pageResult.totalPage})">末页</a>

        跳转的函数:
        <script type="text/javascript">
            //提交表单
            function go(pageNo){
                //把需要跳转的页码设置到<input type="number" name="currentPage"/>上
                document.getElementById("currentPage").value = pageNo;
                //提交表单
                document.forms[0].submit();
            }
        </script>

DAO

public interface IProductdirDAO {
    List<Productdir> list();
}

public interface IProductDAO {
    PageResult query(ProductQueryObject qo);
}

Impl

public class BaseDAO {
    public PageResult query(Class<?> calssType,IQuery qo) {
        //默认把类名作为表名
        String tableName = calssType.getSimpleName();
        Table table = calssType.getAnnotation(Table.class);
        if(table!=null){
            tableName = table.value();
        }
        //查询结果总数
        String sqlCount = "SELECT count(*) from  " + tableName + qo.getQuery();
        Integer totalCount = JdbcTemplate.query(sqlCount, new ResultSetHandler<Long>() {
            public Long handle(ResultSet rs) throws Exception {
                if (rs.next()) {
                    return rs.getLong(1);
                }
                return 0L;
            }
        },qo.getParameters().toArray()).intValue();
        if(totalCount == 0){
            return PageResult.empty(qo.getPageSize());
        }
        System.out.println("sqlCount:"+sqlCount);
        System.out.println("参数="+qo.getParameters());
        //====================================================================================
        //查询结果集
                String sqlList = "SELECT * from  " + tableName + qo.getQuery()+ " LIMIT ?,?";
                qo.getParameters().add((qo.getCurrentPage() -1)*qo.getPageSize());
                qo.getParameters().add(qo.getPageSize());
                List listdata = JdbcTemplate.query(sqlList, new BeanListHandler<>(calssType), qo.getParameters().toArray());
                System.out.println("sqlList:"+sqlList);
                System.out.println("参数="+qo.getParameters());
        return new PageResult(qo.getCurrentPage(),qo.getPageSize(),listdata,totalCount);
    }
}

public class ProductDAOImpl extends BaseDAO implements IProductDAO {
    public PageResult query(ProductQueryObject qo) {
        return super.query(Product.class, qo);
    }
}

query

//表示查询对象的规范,约束查询对象应该具有的方法
public interface IQuery {

    String getQuery();

    List<Object> getParameters();

    Integer getPageSize();

    Integer getCurrentPage();

}

//封装了结果集的对象
@Getter
public class PageResult {
    private List listData;//结果集数据;通过SQL查询
    private Integer totalCount;//结果总条数;通过SQL查询

    private Integer currentPage = 1;//当前页;用户传入
    private Integer pageSize = 5;//每页条数;用户传入

    private Integer beginPage = 1;//首页 
    private Integer prevPage;//上页
    private Integer nextPage;//下页
    private Integer totalPage;//末页

    private List<Integer> pageItems = Arrays.asList(3,5,8,10);

    public static PageResult empty(Integer pageSize){
        return new PageResult(1,pageSize,Collections.EMPTY_LIST,0);
    }

    public PageResult( Integer currentPage, Integer pageSize,List listData, Integer totalCount) {
        this.listData = listData;
        this.totalCount = totalCount;
        this.currentPage = currentPage;
        this.pageSize = pageSize;
        //-------------------------------------
        this.totalPage = totalCount % pageSize == 0 ? totalCount / pageSize : totalCount / pageSize + 1;
        this.prevPage = currentPage - 1 >= 1 ? currentPage - 1 : 1;
        this.nextPage = currentPage + 1 <= totalPage ? currentPage + 1 : totalPage;
    }
}

//封装了商品对象的查询条件,并且它继承了currentPage,pageSize的封装
@Getter
@Setter
public class ProductQueryObject extends QueryObject{
    private String name;
    private BigDecimal minSalePrice; 
    private BigDecimal maxSalePrice;
    private Long dir_id = -1L;
    private String keyword;
    protected void customizedQuery() {
        if(StringUtils.isNotBlank(name)){
            super.addQuery("productName LIKE ?", "%"+name+"%");
        }
        //最低价格
        if(minSalePrice != null){
            super.addQuery("salePrice >= ?", minSalePrice);
        }
        //最高价格
        if(maxSalePrice != null){
            super.addQuery("salePrice <= ?", maxSalePrice);
        }
        if(dir_id != -1){
            super.addQuery("dir_id = ?", dir_id);
        }
        if(StringUtils.isNotBlank(keyword)){//因为AND优先级大于OR,得使用()括起来
            super.addQuery(" (productName LIKE ? OR brand LIKE ? )", "%"+keyword+"%","%"+keyword+"%");
        }
    }
}

//封装了商品对象的查询条件
@Getter
@Setter
public class ProductdirQueryObject extends QueryObject{
    private String name;
    private Long parent_id = -1L;

    //自身的订制查询
    public void customizedQuery() {
        //商品分类名称
        if (StringUtils.isNotBlank(name)) {
            super.addQuery("productdir LIKE ?", "%" + name + "%");
        }
        //父分类
        if (parent_id != -1) {
            super.addQuery(" parent_id = ?", parent_id);
        }
    }
}

public class QueryObject implements IQuery{

    @Getter@Setter
    private Integer currentPage = 1;//当前页;用户传入
    @Getter@Setter
    private Integer pageSize = 5;//每页条数;用户传入

    private List<String> conditions = new ArrayList<>();
    //封装占位符参数
    private List<Object> parameters = new ArrayList<>();

    private boolean isBuild = false;//是否已经构建SQL/封装参数

    //初始化操作
    public void init(){
        if(!isBuild){
            isBuild = true;
            this.customizedQuery();
        }
    }

    //返回查询条件,如:WHERE productName LIKE ? AND salePrice >= ?
    public String getQuery() {
        this.init();
        StringBuilder sql = new StringBuilder();
        if (conditions.size() == 0) {
            return "";
        }
        String queryString = StringUtils.join(conditions, " AND ");
        return sql.append(" Where ").append(queryString).toString();
    }
    //返回查询条件中的占位符参数值
    public List<Object> getParameters() {
        this.init();
        return parameters;
    }
    //暴露给子类,让子类编写自个的查询方式
    protected void customizedQuery(){

    }
    //暴露给子类:让子类在customizedQuery中调用,添加自己的查询条件和参数
    protected void addQuery(String condition,Object...param){
        this.conditions.add(condition);
        this.parameters.addAll(Arrays.asList(param));
    }
}

Servlet

@WebServlet("/product")
public class ProductServlet extends HttpServlet {

    private static final long serialVersionUID = 1L;

    private IProductDAO dao;
    private IProductdirDAO dao_dir;

    public void init() throws ServletException {
        dao = new ProductDAOImpl();
        dao_dir = new ProductdirDAOImpl();
    }

    //列表操作
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");
        //1:接受请求参数,封装对象
        //2:调用业务方法处理请求
        ProductQueryObject qo = new ProductQueryObject();
        this.request2Object(req,qo);
        req.setAttribute("qo",qo);
        PageResult pageResult = dao.query(qo);
        req.setAttribute("pageResult", pageResult);

        req.setAttribute("dir", dao_dir.list());
        //3:控制界面跳转
        req.getRequestDispatcher("/WEB-INF/views/product/product.jsp").forward(req, resp);
    }
    //下面这段代码可以用commons-beanUtils 可以实现对象拷贝
    private void request2Object(HttpServletRequest req, ProductQueryObject qo) {
        String name = req.getParameter("name");
        String minSalePrice = req.getParameter("minSalePrice");
        String maxSalePrice = req.getParameter("maxSalePrice");
        String dir_id = req.getParameter("dir_id");
        String keyword = req.getParameter("keyword");
        String currentPage = req.getParameter("currentPage");
        String pageSize = req.getParameter("pageSize");
        if(StringUtils.isNotBlank(name)){
            qo.setName(name);
        }
        if(StringUtils.isNotBlank(minSalePrice)){
            qo.setMinSalePrice(new BigDecimal(minSalePrice));
        }
        if(StringUtils.isNotBlank(maxSalePrice)){
            qo.setMaxSalePrice(new BigDecimal(maxSalePrice));
        }if(StringUtils.isNotBlank(dir_id)){
            qo.setDir_id(Long.valueOf(dir_id));
        }if(StringUtils.isNotBlank(keyword)){
            qo.setKeyword(keyword);
        }if(StringUtils.isNotBlank(currentPage)){
            qo.setCurrentPage(Integer.valueOf(currentPage));
        }if(StringUtils.isNotBlank(pageSize)){
            qo.setPageSize(Integer.valueOf(pageSize));
        }
    }
}

jsp前端页面

<form action="/product" method="post">
        商品名称:<input type="text" name="name" value="${qo.name}"/>
        商品价格:<input type="text" name="minSalePrice" value="${qo.minSalePrice}"/>到
        <input type="text" name="maxSalePrice" value="${qo.maxSalePrice}"/>
        商品种类:<select name="dir_id">
                    <option value="-1">全部商品</option>
                <c:forEach items="${dir}" var="d">

                    <option value="${d.id}" ${d.id == qo.dir_id? "selected":""} >${d.name}</option>
                </c:forEach>
                </select>
            关键字查询:<input type="text" name="keyword" placeholder="商品名称或商品品牌" value="${qo.keyword}"/>

        <input type="submit" value=" 提交  " style="background-color: orange;"/>  

    <table border="1" width="80%" cellpadding="0" cellspacing="0">
        <tr style="background-color: orange">
            <th>id</th>
            <th>productName</th>
            <th>brand</th>
            <th>supplier</th>
            <th>salePrice</th>
            <th>costPrice</th>
            <th>cutoff</th>
            <th>dir_id</th>
        </tr>
        <c:forEach items="${pageResult.listData}" var="p" varStatus="s">
            <tr style='background-color:${s.count % 2 == 0? "gray":""}'>
                <td>${p.id}</td>
                <td>${p.productName}</td>
                <td>${p.brand}</td>
                <td>${p.supplier}</td>
                <td>${p.salePrice}</td>
                <td>${p.costPrice}</td>
                <td>${p.cutoff}</td>
                <td>
                    <c:choose>
                        <c:when test="${p.dir_id == 1}">无线手机</c:when>
                        <c:when test="${p.dir_id == 3}">游戏手机</c:when>
                        <c:when test="${p.dir_id == 5}">有线手机</c:when>
                    </c:choose>
                </td>
            </tr>
        </c:forEach>
            <tr >
                <td colspan="8" align="center">
                    <%@include file="/WEB-INF/views/commons/commons_page.jsp" %>
                </td>
            </tr>
    </table>
</form>

这段代码是重构出来的代码,可以重复利用

        <script type="text/javascript">
            //提交表单
            function go(pageNo){
                //把需要跳转的页码设置到<input type="number" name="currentPage"/>上
                document.getElementById("currentPage").value = pageNo;
                //提交表单
                document.forms[0].submit();
            }
        </script>

        <a href="javascript:go(${1})">首页</a>
        <a href="javascript:go(${pageResult.prevPage})">上页</a>
        <a href="javascript:go(${pageResult.nextPage})">下页</a>
        <a href="javascript:go(${pageResult.totalPage})">末页</a>
        当前第${pageResult.currentPage}/${pageResult.totalPage}页,
        一共${pageResult.totalCount}条数据,
        跳转到<input type="number" min="1" max="${pageResult.totalPage}" 
        id="currentPage" style="width: 50px" name="currentPage" value="${pageResult.currentPage}"/>页
        <input type="button" value="GO" onclick="go()"/>

        每页
            <select name="pageSize" onchange="go();">
                <c:forEach items="${pageResult.pageItems}" var="item">
                    <option ${item == pageResult.pageSize? "selected":""}>${item}</option>
                </c:forEach>
            </select>
        条数据

domain

 * 商品对象 
@Data
@Table("product")
public class Product {
    private Long id;
    //@Comlumn("name2")当列名是name2的时候
    private String productName;
    private String brand;
    private String supplier;
    private BigDecimal salePrice;
    private BigDecimal costPrice;
    private Double cutoff;
    private Long dir_id;//分类编号
}

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.TYPE)
public @interface Table {
    String value() default "";
}

@Data
public class Productdir {
    private String name;
    private Long id;
    private Long parent_id;
}   

JDBC重构设计类

public class JdbcTemplate {

    private JdbcTemplate() {

    }

    /**
     * 
     *@param sql   DML各自的SQL,由调用者决定
     *@param params      DML操作需要的参数，由调用者决定
     *@return     受影响的行数
     */
    public static int update(String sql, Object... params) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JdbcUtil.INSTANCE.getConn();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
            }
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.INSTANCE.close(conn, ps, null);
        }
        return 0;
    }

    public static <T>T query(String sql, ResultSetHandler<T> rsh ,Object... params) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtil.INSTANCE.getConn();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
            }
            rs = ps.executeQuery();
            return rsh.handle(rs);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.INSTANCE.close(conn, ps, rs);
        }
        return null;
    }
}

//表示结果集中的一行数据,封装成一个对象,专门针对结果集中只有一行数据的情况.
public class BeanHandler<T> implements ResultSetHandler<T>{
    private Class<T> classType;

    public BeanHandler(Class<T> classType){
        this.classType = classType;
    }

    /**
     * 规范:
     *      1:规定表中的列名必须和对象中的属性名相同.
     *      2:规定表中列名的类型必须和java中的类型要匹配. decimal  --->BigDecimal/bigint --->Long 
     */
    public T handle(ResultSet rs) throws Exception {
        //1):创建对应的一个对象
        T obj = classType.newInstance();
        //2):取出结果集中当前光标所在行的某一列的数据.
        BeanInfo beanInfo = Introspector.getBeanInfo(classType,Object.class);   
        PropertyDescriptor[] pds = beanInfo.getPropertyDescriptors();
        if(rs.next()){
            for (PropertyDescriptor pd : pds) {
                String columnName = pd.getName();//获取对象的属性名称,属性名和列名相同
                Object val = rs.getObject(columnName);
                //3):调用该对象的setter方法,把某一列的数据,设置进去.
                pd.getWriteMethod().invoke(obj, val);
            }
        }
        return obj;
    }

}

//表示把结果集中的多行数据,封装成一个对象的集合(List<xx>),针对于结果集中有多行数据的.
public class BeanListHandler<T> implements ResultSetHandler<List<T>> {
    private Class<T> classType;

    public BeanListHandler(Class<T> classType) {
        this.classType = classType;
    }

    public List<T> handle(ResultSet rs) throws Exception {
        List<T> list = new ArrayList<>();
        while(rs.next()) {
            //每一行封装成一个对象
            T obj = classType.newInstance();
            BeanInfo beanInfo = Introspector.getBeanInfo(classType, Object.class);
            PropertyDescriptor[] pds = beanInfo.getPropertyDescriptors();
            for (PropertyDescriptor pd : pds) {
                //获取对象的属性名称,属性名和列名相同
                String columnName = pd.getName();
                Object val = rs.getObject(columnName);
                //3):调用该对象的setter方法,把某一列的数据,设置进去.
                pd.getWriteMethod().invoke(obj, val);
                //把每一行对应的对象,存储到List集合中.
            }
            list.add(obj);
        }
        return list;
    }
}

//定义一个专门的约束处理结果集的接口：ResultSetHandler：结果集处理器
public interface ResultSetHandler<T> {

    T handle(ResultSet rs) throws Exception;
}