JavaWeb之高级分页查询

分页的原理:
**假分页/逻辑分页/内存 分页: 一次性把所有数据全部查询出来存放到内存中,每次翻页都从内存中去截取指定数量的数据.
优点:每次翻页比较快,简单; 缺点:若数据过大,可能造成内存溢出问题.
真分页/物理分页/数据库分页: 每次翻页的时候都从数据库中取出指定数量的数据(MySQL: LIMIT ?,?语句).
优点:若数据过大,不会造成内存溢出问题; 缺点:复杂,每次翻页较慢.**
所以一般我们需要进行分页,肯定是每次翻页的时候去数据库中取出指定数量数据再按照每页的数据数量进行分页.LIMIT?,?语句

 1):查询符合条件的结果集.
    SELECT * FROM 表名 [WHERE 条件1 AND 条件2] [ORDER BY .....] LIMIT ?,?;
       第一个?:当前页从哪一个索引开始截取数据:start/begin/beginIndex(从0开始).
                beginIndex = (currentPage - 1) * pageSize;
       第二个?:截取多少条数据:pageSize;
 2):查询符合条件的结果总数.
    SELECT COUNT(*) FROM 表名 [WHERE 条件1 AND 条件2];

高级分页查询的本质就是多条件筛选
而对其操作的实质就是拼SQL的查询条件.
SELECT * FROM 表名 WHERE 条件1 AND 条件2 ….

以下不多说直接附上代码,进行一个高级分页查询
和商品的CRUD操作的综合Demo.

domian层

package com._jerry._domain;

import java.math.BigDecimal;

import lombok.Data;

@Data
public class Product {
   
    private String productName;
    private Long id;
    private BigDecimal salePrice;
    private BigDecimal costPrice;
    private Double cutoff;
    private String supplier;
    private String brand;
    //封装分类对象
    private ProductDir productDir;
}

package com._jerry._domain;

import lombok.Data;

@Data
public class ProductDir {
   
    private Long id;
    private String dirName;
    private Long parent_id;
}

DAO层

package com._jerry._Interface;

import com._jerry._domain.Product;
import com._jerry._result.PageResult;

public interface IproductDAO {
   

    //  List<Product> query(IQuery qo);

    /**
     * 对象集合查询结果
     * @return
     */
    PageResult pageQuery(IQuery qo, String tableName);

    /**
     * 定义CRUD的操作  除了查询操作意外
     */
    Product get(Long id);

    void delete(Long id);

    int update(Product newProduct);

    void save(Product product);

}

package com._jerry._Interface;

import java.util.List;

import com._jerry._domain.ProductDir;


public interface IProductDirDAO {
   
    List<ProductDir> dirs();
}

package com._jerry._implement;

import java.util.List;
import com._jerry._Interface.IQuery;
import com._jerry._Interface.IResultSetHandler;
import com._jerry._Interface.IproductDAO;
import com._jerry._domain.Product;
import com._jerry._jdbcTemplate.JdbcTemplate;
import com._jerry._result.PageResult;
import com._jerry._result.ResultSetHandler;
import com._jerry._util.QueryUtil;

@SuppressWarnings("all")
public class ProductDAOImpl implements IproductDAO {
   
    private IResultSetHandler<List<Product>> rsh = new ResultSetHandler();
    /*public List<Product> query(IQuery qo) {
        String sql = "SELECT * FROM product"+qo.getQuery();
        return JdbcTemplate.query(sql, rsh,qo.getParameters().toArray());
    }*/

    //高级查询和分页查询综合方法
    public PageResult pageQuery(IQuery qo, String tableName) {
        PageResult pageResult = QueryUtil.doQuery(qo, tableName, rsh);
        return pageResult;
    }

    //crud操作
    public void delete(Long id) {
        String sql = "DELETE  FROM product WHERE id = ?";
        JdbcTemplate.upDate(sql, id);
    }

    public int update(Product newProduct) {
        String sql = "UPDATE product SET productName=?,dir_id=?,salePrice=?," +
                "supplier=?,brand=?,cutoff=?,costPrice=? WHERE id = ?";
        Object[] params = { newProduct.getProductName(),
                newProduct.getProductDir().getId(), newProduct.getSalePrice(),
                newProduct.getSupplier(), newProduct.getBrand(),
                newProduct.getCutoff(), newProduct.getCostPrice(),
                newProduct.getId() };
        int num = JdbcTemplate.upDate(sql, params);
        return num;
    }

    public void save(Product obj) {
        String sql = "INSERT INTO product (productName,dir_id,salePrice,supplier,brand,"
                + "cutoff,costPrice) VALUES(?,?,?,?,?,?,?)";
        Object[] params = { obj.getProductName(), obj.getProductDir().getId(),
                obj.getSalePrice(), obj.getSupplier(), obj.getBrand(),
                obj.getCutoff(), obj.getCostPrice() };
        JdbcTemplate.upDate(sql, params);
    }

    public Product get(Long id) {
        //用来做更新操作的时候 取出指定id的对象
        String sql = "SELECT * FROM product WHERE id = ?";
        List<Product> product = JdbcTemplate.query(sql, rsh, id);
        return product.size() == 1 ? product.get(0):null;
    }

}

package com._jerry._implement;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com._jerry._Interface.IProductDirDAO;
import com._jerry._Interface.IResultSetHandler;
import com._jerry._domain.ProductDir;
import com._jerry._jdbcTemplate.JdbcTemplate;


public class ProductDirDAOImpl implements IProductDirDAO{
   
    private IResultSetHandler<List<ProductDir>> rsh = new ProductDirResultHandler();
    public ProductDir getDir(Long id){
        String sql = "SELECT * FROM productdir WHERE id = ?";
        List<ProductDir> list = JdbcTemplate.query(sql, rsh, id);
        if (list != null) {
            return list.get(0);
        }
        return null;

    }
    public List<ProductDir> dirs() {
        String sql = "SELECT * FROM productdir";
        List<ProductDir> list = JdbcTemplate.query(sql, new ProductDirResultHandler());
        return list;
    }
    class ProductDirResultHandler implements IResultSetHandler<List<ProductDir>>{
   

        public List<ProductDir> handler(ResultSet rs) throws SQLException {
            List<ProductDir> list = new ArrayList<>();
            while (rs.next()) {
                ProductDir productDir = new ProductDir();
                list.add(productDir);
                productDir.setId(rs.getLong("id"));
                productDir.setDirName(rs.getString("dirName"));
                productDir.setParent_id(rs.getLong("parent_id"));
            }
            return list;
        }

    }
}

service层

package com._jerry._servlet;

import java.io.IOException;
import java.math.BigDecimal;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com._jerry._Interface.IProductDirDAO;
import com._jerry._Interface.IproductDAO;
import com._jerry._domain.Product;
import com._jerry._domain.ProductDir;
import com._jerry._implement.ProductDAOImpl;
import com._jerry._implement.ProductDirDAOImpl;
import com._jerry._implement.ProductQueryObject;
import com._jerry._result.PageResult;
import com._jerry._util.StringUtil;

@WebServlet("/query")
@SuppressWarnings("all")
public class QueryServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
    private IproductDAO dao;
    private IProductDirDAO dirDAO;

    public void init() throws ServletException {
        dao = new ProductDAOImpl()