分页查询
博客专区 > bharals 的博客 > 博客详情
分页查询
bharals 发表于9个月前
分页查询
  • 发表于 9个月前
  • 阅读 11
  • 收藏 0
  • 点赞 0
  • 评论 0

新睿云服务器60天免费使用,快来体验!>>>   

分页查询实例

package com.hao.bookstore.dao.impl;  
  
import java.sql.Connection;  
import java.sql.PreparedStatement;  
import java.sql.ResultSet;  
import java.sql.Statement;  
import java.util.List;  
  
import org.apache.commons.dbutils.QueryRunner;  
import org.apache.commons.dbutils.handlers.BeanHandler;  
import org.apache.commons.dbutils.handlers.BeanListHandler;  
import org.apache.commons.dbutils.handlers.ScalarHandler;  
  
import com.hao.bookstore.dao.DAO;  
import com.hao.bookstore.db.JDBCUtils;  
import com.hao.bookstore.utils.ReflectionUtils;  
  
  
public class BaseDAO<T> implements DAO<T> {  
      
    private QueryRunner queryRunner = new QueryRunner();  
  
    private Class<T> clazz;  
      
    public BaseDAO() {  
        clazz = ReflectionUtils.getSuperGenericType(getClass());  
    }  
      
    @Override  
    public long insert(String sql, Object... args) {  
          
        long id = 0;  
          
        Connection connection = null;  
        PreparedStatement preparedStatement = null;  
        ResultSet resultSet = null;  
          
        try {  
            connection = JDBCUtils.getConnection();  
            preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);  
              
            if(args != null){  
                for(int i = 0; i < args.length; i++){  
                    preparedStatement.setObject(i + 1, args[i]);  
                }  
            }  
              
            preparedStatement.executeUpdate();  
              
            //获取生成的主键值  
            resultSet = preparedStatement.getGeneratedKeys();  
            if(resultSet.next()){  
                id = resultSet.getLong(1);  
            }  
              
        } catch (Exception e) {  
            e.printStackTrace();  
        } finally{  
            JDBCUtils.release(resultSet, preparedStatement);  
        }  
          
        return id;  
    }  
  
    @Override  
    public void update(String sql, Object... args) {  
        Connection connection = null;  
          
        try {  
            connection = JDBCUtils.getConnection();  
            queryRunner.update(connection, sql, args);  
        } catch (Exception e) {  
            e.printStackTrace();  
        }  
    }  
  
    @Override  
    public T query(String sql, Object... args) {  
          
        Connection connection = null;  
          
        try {  
            connection = JDBCUtils.getConnection();  
            return queryRunner.query(connection, sql, new BeanHandler<>(clazz), args);  
        } catch (Exception e) {  
            e.printStackTrace();  
        }  
          
        return null;  
    }  
  
    @Override  
    public List<T> queryForList(String sql, Object... args) {  
        Connection connection = null;  
          
        try {  
            connection = JDBCUtils.getConnection();  
            return queryRunner.query(connection, sql, new BeanListHandler<>(clazz), args);  
        } catch (Exception e) {  
            e.printStackTrace();  
        }   
        return null;  
    }  
  
    @Override  
    public <V> V getSingleVal(String sql, Object... args) {  
        Connection connection = null;  
          
        try {  
            connection = JDBCUtils.getConnection();  
            return (V)queryRunner.query(connection, sql, new ScalarHandler(), args);  
        } catch (Exception e) {  
            e.printStackTrace();  
        }  
          
        return null;  
    }  
  
    @Override  
    public void batch(String sql, Object[]... params) {  
        Connection connection = null;  
          
        try {  
            connection = JDBCUtils.getConnection();  
            queryRunner.batch(connection, sql, params);  
        } catch (Exception e) {  
            e.printStackTrace();  
        }  
          
    }  
  
}  
package com.hao.bookstore.dao;  
  
import java.util.Collection;  
import java.util.List;  
  
import com.hao.bookstore.domain.Book;  
import com.hao.bookstore.domain.ShoppingCartItem;  
import com.hao.bookstore.web.CriteriaBook;  
import com.hao.bookstore.web.Page;  
  
  
public interface BookDAO {  
  
    /** 
     * 根据 id 获取指定 Book 对象 
     * @param id 
     * @return 
     */  
    public abstract Book getBook(int id);  
    /** 
     * 根据传入的 CriteriaBook 对象返回对应的 Page 对象 
     * @param cb 
     * @return 
     */  
    public abstract Page<Book> getPage(CriteriaBook cb);  
    /** 
     * 根据传入的 CriteriaBook 对象返回其对应的记录数 
     * @param cb 
     * @return 
     */  
    public abstract long getTotalBookNumber(CriteriaBook cb);  
    /** 
     * 根据传入的 CriteriaBook 和 pageSize 返回当前页对应的 List  
     * @param cb 
     * @param pageNo 
     * @param pageSize 
     * @return 
     */  
    public abstract List<Book> getPageList(CriteriaBook cb,int pageSize);  
    /** 
     * 返回指定 id 的 book 的 storeNumber 字段的值 
     * @param id 
     * @return 
     */  
    public abstract int getStoreNumber(Integer id);  
    /** 
     * 根据传入的 ShoppingCartItem 的集合,  
     * 批量更新 books 数据表的 storenumber 和 salesnumber 字段的值 
     * @param items 
     */  
    public abstract void batchUpdateStoreNumberAndSalesAmount(  
            Collection<ShoppingCartItem> items);  
}  
package com.hao.bookstore.dao.impl;  
  
import java.util.ArrayList;  
import java.util.Collection;  
import java.util.List;  
  
import com.hao.bookstore.dao.BookDAO;  
import com.hao.bookstore.domain.Book;  
import com.hao.bookstore.domain.ShoppingCartItem;  
import com.hao.bookstore.web.CriteriaBook;  
import com.hao.bookstore.web.Page;  
  
  
public class BookDAOImpl extends BaseDAO<Book> implements BookDAO {  
  
    @Override  
    public Book getBook(int id) {  
        String sql = "SELECT id, author, title, price, publishingDate, " +  
                "salesAmount, storeNumber, remark FROM mybooks WHERE id = ?";  
        return query(sql, id);  
    }  
  
    //3.   
    @Override  
    public Page<Book> getPage(CriteriaBook cb) {  
        Page<Book> page = new Page<>(cb.getPageNo());  
          
        page.setTotalItemNumber(getTotalBookNumber(cb));  
        //校验 pageNo 的合法性  
        cb.setPageNo(page.getPageNo());  
        page.setList(getPageList(cb, 3));  
          
        return page;  
    }  
  
    //1.   
    @Override  
    public long getTotalBookNumber(CriteriaBook cb) {  
        String sql = "SELECT count(id) FROM mybooks WHERE price >= ? AND price <= ?";  
        return getSingleVal(sql, cb.getMinPrice(), cb.getMaxPrice());   
    }  
  
    //2.   
    /** 
     * MySQL 分页使用 LIMIT, 其中 fromIndex 从 0 开始。  
     */  
    @Override  
    public List<Book> getPageList(CriteriaBook cb, int pageSize) {  
        String sql = "SELECT id, author, title, price, publishingDate, " +  
                "salesAmount, storeNumber, remark FROM mybooks " +  
                "WHERE price >= ? AND price <= ? " +  
                "LIMIT ?, ?";  
          
        return queryForList(sql, cb.getMinPrice(), cb.getMaxPrice(),   
                (cb.getPageNo() - 1) * pageSize, pageSize);  
    }  
  
    @Override  
    public int getStoreNumber(Integer id) {  
        String sql = "SELECT storeNumber FROM mybooks WHERE id = ?";  
        return getSingleVal(sql, id);  
    }  
  
    @Override  
    public void batchUpdateStoreNumberAndSalesAmount(  
            Collection<ShoppingCartItem> items) {  
        String sql = "UPDATE mybooks SET salesAmount = salesAmount + ?, " +  
                "storeNumber = storeNumber - ? " +  
                "WHERE id = ?";  
        Object [][] params = null;  
        params = new Object[items.size()][3];  
        List<ShoppingCartItem> scis = new ArrayList<>(items);  
        for(int i = 0; i < items.size(); i++){  
            params[i][0] = scis.get(i).getQuantity();  
            params[i][1] = scis.get(i).getQuantity();  
            params[i][2] = scis.get(i).getBook().getId();  
        }  
        batch(sql, params);  
    }  
  
  
}  
package com.hao.bookstore.web;  
  
import java.util.List;  
  
public class Page<T> {  
    private int pageNo;//当前第几页  
    private List<T> list;//当前页的 List  
    private int pageSize=3;//每页显示多少条记录  
    private long totalItemNumber;//共有多少条记录  
      
    public Page(int pageNo){  
        super();  
        this.pageNo=pageNo;  
    }  
  
    public int getPageNo() {  
        if(pageNo < 0)  
            pageNo = 1;  
          
        if(pageNo > getTotalPageNumber()){  
            pageNo = getTotalPageNumber();  
        }  
        return pageNo;  
    }  
  
    public void setPageNo(int pageNo) {  
        this.pageNo = pageNo;  
    }  
  
    public List<T> getList() {  
        return list;  
    }  
  
    public void setList(List<T> list) {  
        this.list = list;  
    }  
  
    public int getPageSize() {  
        return pageSize;  
    }  
  
    public void setPageSize(int pageSize) {  
        this.pageSize = pageSize;  
    }  
  
    public long getTotalItemNumber() {  
        return totalItemNumber;  
    }  
  
    public void setTotalItemNumber(long totalItemNumber) {  
        this.totalItemNumber = totalItemNumber;  
    }  
      
    //获取总页数  
    public int getTotalPageNumber(){  
        int totalPageNumber = (int)totalItemNumber/pageSize;  
        if(totalItemNumber % pageSize != 0){  
            totalPageNumber++;  
        }  
        return totalPageNumber;  
    }  
    public boolean isHasNext(){  
        if(getPageNo()<getTotalPageNumber()){  
            return true;  
        }  
        return false;  
    }  
    public boolean isHasPrev(){  
        if(getPageNo() > 1){  
            return true;  
        }  
        return false;  
    }  
    public int getPrevPage(){  
        if(isHasPrev()){  
            return getPageNo() - 1;  
        }  
          
        return getPageNo();  
    }  
    public int getNextPage(){  
        if(isHasNext()){  
            return getPageNo() + 1;  
        }  
          
        return getPageNo();  
    }  
}  
  
  
<pre name="code" class="java">package com.hao.bookstore.web;  
  
public class CriteriaBook {  
    private float minPrice = 0;  
    private float maxPrice = Integer.MAX_VALUE;  
      
    private int pageNo;  
  
    public float getMinPrice() {  
        return minPrice;  
    }  
  
    public void setMinPrice(float minPrice) {  
        this.minPrice = minPrice;  
    }  
  
    public float getMaxPrice() {  
        return maxPrice;  
    }  
  
    public void setMaxPrice(float maxPrice) {  
        this.maxPrice = maxPrice;  
    }  
  
    public int getPageNo() {  
        return pageNo;  
    }  
  
    public void setPageNo(int pageNo) {  
        this.pageNo = pageNo;  
    }  
  
    public CriteriaBook(float minPrice, float maxPrice, int pageNo) {  
        super();  
        this.minPrice = minPrice;  
        this.maxPrice = maxPrice;  
        this.pageNo = pageNo;  
    }  
}  
  • 打赏
  • 点赞
  • 收藏
  • 分享
共有 人打赏支持
粉丝 0
博文 26
码字总数 46307
×
bharals
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: