文档章节

分页查询

bharals
 bharals
发布于 2017/05/16 07:04
字数 917
阅读 18
收藏 0

分页查询实例

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;  
    }  
}  

本文转载自:

共有 人打赏支持
bharals
粉丝 0
博文 26
码字总数 46307
作品 0
私信 提问

暂无文章

Integer使用双等号比较会发生什么

话不多说,根据以下程序运行,打印的结果为什么不同? Integer a = 100;Integer b = 100;System.out.println(a == b);//print : trueInteger a = 200;Integer b = 200;System.out.pr...

兜兜毛毛
25分钟前
0
0
CockroachDB

百度云上的CockroachDB 云数据库 帮助文档 > 产品文档 > CockroachDB 云数据库 > 产品描述 开源NewSQL – CockroachDB在百度内部的应用与实践 嘉宾演讲视频及PPT回顾:http://suo.im/5bnORh ...

miaojiangmin
37分钟前
1
0
I2C EEPROM驱动实例分析

上篇分析了Linux Kernel中的I2C驱动框架,本篇举一个具体的I2C设备驱动(eeprom)来对I2C设备驱动有个实际的认识。 s3c24xx系列集成了一个基于I2C的eeprom设备at24cxx系列。at24cxx系列芯片包...

yepanl
38分钟前
2
0
设计模式之工厂模式

本篇博文主要翻译这篇文章: https://www.journaldev.com/1392/factory-design-pattern-in-java 由于翻译水平有限,自认为许多地方翻译不恰当,欢迎各位给出宝贵的建议,建议大家去阅读原文。...

firepation
今天
6
0

中国龙-扬科
今天
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部