web分页的两种基本方式
大致有两种方案
方案1
从本地数据库读取出所有的数据，然后封装成一个model链表，根据需要对list进行分片（subList（fromIndex， toIndex））
优点：简单
缺点：每次分页都要对数据库查询结果的所有内容进行访问，内存和时间开销都很大,用户体验较差
- 建议：不建议使用
方案2
利用数据库查询结果优化，如Mysql的limit语句
优点：内存和时间开销都小，用户体验较好
缺点：每次分页都要对sql语句进行修改（这根本不是事）
建议：建议使用
代码示例
- 1.先建立Pager模板类
package com.Android.bean;
import java.io.Serializable;
import java.util.List;
public class Pager<T> implements Serializable {
private static final long serialVersionUID = -8741766802354222579L;
private int pageSize; // 每页显示多少条记录
private int currentPage; //当前第几页数据
private int totalRecord; // 一共多少条记录
private int totalPage; // 一共多少页记录
private List<T> dataList; //要显示的数据
public Pager(){
}
public Pager(int pageSize, int currentPage, int totalRecord, int totalPage,
List<T> dataList) {
super();
this.pageSize = pageSize;
this.currentPage = currentPage;
this.totalRecord = totalRecord;
this.totalPage = totalPage;
this.dataList = dataList;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public int getCurrentPage() {
return currentPage;
}
public void setCurrentPage(int currentPage) {
this.currentPage = currentPage;
}
public int getTotalRecord() {
return totalRecord;
}
public void setTotalRecord(int totalRecord) {
this.totalRecord = totalRecord;
}
public int getTotalPage() {
return totalPage;
}
public void setTotalPage(int totalPage) {
this.totalPage = totalPage;
}
public List<T> getDataList() {
return dataList;
}
public void setDataList(List<T> dataList) {
this.dataList = dataList;
}
}
- 2.建立数据库连接池，优化数据库连接所造成的资源消耗
package com.Android.util;
import java.beans.PropertyVetoException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public final class ConnectionManager {
// 表示定义数据库的用户名
private static String USERNAME;
// 定义数据库的密码
private static String PASSWORD;
// 定义数据库的驱动信息
private static String DRIVER;
// 定义访问数据库的地址
private static String URL;
// 使用单利模式创建数据库连接池
private static ConnectionManager instance;
private static ComboPooledDataSource dataSource;
static {
// 加载数据库配置信息，并给相关的属性赋值
loadConfig();
}
public static void loadConfig() {
try {
InputStream in = ConnectionManager.class
.getResourceAsStream("/JDBCConfig.properties");
Properties properties = new Properties();
properties.load(in);
USERNAME = properties.getProperty("jdbc.username");
PASSWORD = properties.getProperty("jdbc.password");
DRIVER = properties.getProperty("jdbc.driver");
URL = properties.getProperty("jdbc.url");
} catch (Exception e) {
throw new RuntimeException("加载数据库配置文件异常！！！！", e);
}
}
private ConnectionManager() throws SQLException, PropertyVetoException {
dataSource = new ComboPooledDataSource();
dataSource.setUser(USERNAME); // 用户名
dataSource.setPassword(PASSWORD); // 密码
dataSource.setJdbcUrl(URL);// 数据库地址
dataSource.setDriverClass(DRIVER);
dataSource.setInitialPoolSize(5); // 初始化连接数
dataSource.setMinPoolSize(1);// 最小连接数
dataSource.setMaxPoolSize(10);// 最大连接数
dataSource.setMaxStatements(50);// 最长等待时间
dataSource.setMaxIdleTime(60);// 最大空闲时间，单位毫秒
}
public static final ConnectionManager getInstance() {
if (instance == null) {
try {
instance = new ConnectionManager();
} catch (Exception e) {
e.printStackTrace();
}
}
return instance;
}
public synchronized final Connection getConnection() {
Connection conn = null;
try {
conn = dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
}
- 3.建立数据库工具类，方便统一管理与控制
package com.Android.util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class JdbcUtil {
// 定义数据库的链接
private Connection connection;
// 定义sql语句的执行对象
private PreparedStatement pstmt;
// 定义查询返回的结果集合
private ResultSet resultSet;
public JdbcUtil() {
//从数据库获取数据库连接
connection = ConnectionManager.getInstance().getConnection();
}
/**
* 执行更新操作
*
* @param sql
* sql语句
* @param params
* 执行参数
* @return 执行结果
* @throws SQLException
*/
public boolean updateByPreparedStatement(String sql, List<?> params)
throws SQLException {
boolean flag = false;
int result = -1;// 表示当用户执行添加删除和修改的时候所影响数据库的行数
pstmt = connection.prepareStatement(sql);
int index = 1;
// 填充sql语句中的占位符
if (params != null && !params.isEmpty()) {
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(index++, params.get(i));
}
}
result = pstmt.executeUpdate();
flag = result > 0 ? true : false;
return flag;
}
/**
* 执行查询操作
*
* @param sql
* sql语句
* @param params
* 执行参数
* @return
* @throws SQLException
*/
public List<Map<String, Object>> findResult(String sql, List<?> params)
throws SQLException {
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
int index = 1;
pstmt = connection.prepareStatement(sql);
if (params != null && !params.isEmpty()) {
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(index++, params.get(i));
}
}
resultSet = pstmt.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int cols_len = metaData.getColumnCount();
while (resultSet.next()) {
Map<String, Object> map = new HashMap<String, Object>();
for (int i = 0; i < cols_len; i++) {
String cols_name = metaData.getColumnName(i + 1);
Object cols_value = resultSet.getObject(cols_name);
if (cols_value == null) {
cols_value = "";
}
map.put(cols_name, cols_value);
}
list.add(map);
}
return list;
}
/**
* 执行查询操作
*
* @param sql
* sql语句
* @param params
* 执行参数
* @return
* @throws SQLException
*/
public List findResultToBeanList(String sql, List<?> params, Class<?> cls )
throws SQLException {
List<?> list = null;
int index = 1;
pstmt = connection.prepareStatement(sql);
if (params != null && !params.isEmpty()) {
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(index++, params.get(i));
}
}
resultSet = pstmt.executeQuery();
try {
list = GetData.resultSetToList(resultSet, cls);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
/**
* 执行查询操作
*
* @param sql
* sql语句
* @param params
* 执行参数
* @return
* @throws SQLException
*/
public int findResultTotalNumber(String sql, List<?> params )
throws SQLException {
int totalNumber = 0;
int index = 1;
pstmt = connection.prepareStatement(sql);
if (params != null && !params.isEmpty()) {
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(index++, params.get(i));
}
}
resultSet = pstmt.executeQuery();
try {
if(resultSet.next()){
totalNumber = resultSet.getInt(1);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return totalNumber;
}
/**
* 释放资源
*/
public void releaseConn() {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
JdbcUtil jdbcUtil = new JdbcUtil();
try {
List<Map<String, Object>> result = jdbcUtil.findResult(
"select * from newsTable", null);
for (Map<String, Object> m : result) {
System.out.println(m);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
jdbcUtil.releaseConn();
}
}
}
- 4.建立dao层，MVC模式
package com.Android.DAO;
import java.util.ArrayList;
import java.util.List;
import com.Android.bean.*;
import com.Android.util.JdbcUtil;
public class NewsDAO {
private final static int pageSize = 8;
public Pager getNews(int newsType, int currentPage){
List<ShowNewsBean> newsList = null;
Pager pager = null;
try{
String sql = "SELECT * FROM newsTable WHERE newsState = 1 AND newsType = ? limit ?,?;";
List<Object> params = new ArrayList<Object>();
params.add(newsType);
params.add((currentPage-1)*pageSize);
params.add(currentPage*pageSize);
JdbcUtil jdbcUtil = new JdbcUtil();
pager = new Pager();
newsList = jdbcUtil.findResultToBeanList(sql, params, ShowNewsBean.class);
jdbcUtil.releaseConn();
}catch(Exception e){
System.out.println(e.toString());
}
return pager;
}
public Pager getNews(int currentPage){
List<ShowNewsBean> dataList = null;
Pager pager = null;
try{
String sql1 = "SELECT * FROM newsTable WHERE newsState = 1;";
String sql2 = "SELECT * FROM newsTable WHERE newsState = 1 limit ?, ?;";
//获得数据
dataList = getPageData(sql2, currentPage, pageSize);
JdbcUtil jdbcUtil = new JdbcUtil();
jdbcUtil.releaseConn();
//获取一共有多少条记录
int totalRecord = getTotalRecord(sql1);
//一共有多少页
int totalNum = (totalRecord%pageSize == 0) ? totalRecord/pageSize : totalRecord/pageSize + 1;
//返回pager对象
pager = new Pager<ShowNewsBean>(pageSize, currentPage, totalRecord, totalNum,
dataList);
}catch(Exception e){
System.out.println(e.toString());
}
return pager;
}
public List<ShowNewsBean> getPageData(String sql, int currentPage, int pageSize){
List<ShowNewsBean> newsList = null;
try{
//设置参数
List<Object> params = new ArrayList<Object>();
params.add((currentPage-1)*pageSize);
params.add(pageSize);
JdbcUtil jdbcUtil = new JdbcUtil();
newsList = jdbcUtil.findResultToBeanList(sql, params, ShowNewsBean.class);
System.out.println(sql+params.toString());
jdbcUtil.releaseConn();
}catch(Exception e){
System.out.println(e.toString());
}
return newsList;
}
public int getTotalRecord(String sql){
int totalRecord = 0;
try{
JdbcUtil jdbcUtil = new JdbcUtil();
totalRecord = jdbcUtil.findResultTotalNumber(sql, null);
jdbcUtil.releaseConn();
}catch(Exception e){
System.out.println(e.toString());
}
return totalRecord;
}
public static void main(String[] args){
NewsDAO newsDAO = new NewsDAO();
newsDAO.getNews(1);
}
}
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
