分页-准备工作

89次阅读

共计 3865 个字符，预计需要花费 10 分钟才能阅读完成。

建库，键表

drop database customers;
CREATE DATABASE IF NOT EXISTS customers;
USE customers;
CREATE TABLE t_customer(cid CHAR(32) PRIMARY KEY,
	cname VARCHAR(40) NOT NULL,
	gender VARCHAR(6) NOT NULL, 
	birthday CHAR(10),
	cellphone VARCHAR(15) NOT NULL,
	email VARCHAR(40),
	description VARCHAR(500)
);

分页 - 准备工作

c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
	<!-- 这是默认配置信息 -->
	<default-config> 
		<!-- 连接四大参数配置 -->
		<property name="jdbcUrl">jdbc:mysql://localhost:3306/customers</property>
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="user">root</property>
		<property name="password">poi</property>
		<!-- 池参数配置 -->
		<property name="acquireIncrement">3</property>
		<property name="initialPoolSize">10</property>
		<property name="minPoolSize">2</property>
		<property name="maxPoolSize">10</property>
	</default-config>
	
	<!-- 专门为 oracle 提供的配置信息 -->
	<named-config name="oracle-config"> 
		<property name="jdbcUrl">jdbc:mysql://localhost:3306/mydb1</property>
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="user">root</property>
		<property name="password">poi</property>
		<property name="acquireIncrement">3</property>
		<property name="initialPoolSize">10</property>
		<property name="minPoolSize">2</property>
		<property name="maxPoolSize">10</property>
	</named-config>

</c3p0-config>

Customer.java

public class Customer {/*
	 * 对应数据库表
	 */
	private String cid;// 主键 
	private String cname;// 客户名称 
	private String gender;// 客户性别 
	private String birthday;// 客户生日 
	private String cellphone;// 客户手机 
	private String email;// 客户邮箱 
	private String description;// 客户的描述 

	public String getCid() {return cid;
	}

	public void setCid(String cid) {this.cid = cid;
	}

	public String getCname() {return cname;
	}

	public void setCname(String cname) {this.cname = cname;
	}

	public String getGender() {return gender;
	}

	public void setGender(String gender) {this.gender = gender;
	}

	public String getBirthday() {return birthday;
	}

	public void setBirthday(String birthday) {this.birthday = birthday;
	}

	public String getCellphone() {return cellphone;
	}

	public void setCellphone(String cellphone) {this.cellphone = cellphone;
	}

	public String getEmail() {return email;
	}

	public void setEmail(String email) {this.email = email;
	}

	public String getDescription() {return description;
	}

	public void setDescription(String description) {this.description = description;
	}

	@Override
	public String toString() {return "Customer [cid=" + cid + ", cname=" + cname + ", gender="
				+ gender + ", birthday=" + birthday + ", cellphone="
				+ cellphone + ", email=" + email + ", description="
				+ description + "]";
	}
}

CustomerTest.java

public class CustomerTest {@Test
	public void fun1() {CustomerDao dao = new CustomerDao();
		for(int i = 1; i <= 300; i++) {Customer c = new Customer();
			
			c.setCid(CommonUtils.uuid());
			c.setCname("cstm_" + i);
			c.setBirthday("2014-07-13");
			c.setGender(i%2==0?"男":"女");
			c.setCellphone("139" + i);
			c.setEmail("cstm_" + i + "@163.com");
			c.setDescription("我是客户");
			
			dao.add(c);
		}
	}
}

CustomerDao.java

public class CustomerDao {private QueryRunner qr = new QueryRunner(new ComboPooledDataSource());

	/**
	 * 添加客户
	 * 
	 * @param c
	 */
	public void add(Customer c) {try {String sql = "insert into t_customer values(?,?,?,?,?,?,?)";
			Object[] params = { c.getCid(), c.getCname(), c.getGender(),
					c.getBirthday(), c.getCellphone(), c.getEmail(),
					c.getDescription()};
			qr.update(sql, params);
		} catch(SQLException e) {throw new RuntimeException(e);
		}
	}
}

CommonUtils.java

public class CommonUtils {/**
	 * 返回一个不重复的字符串
	 * @return
	 */
	public static String uuid() {return UUID.randomUUID().toString().replace("-", "").toUpperCase();}
}

第 N 页 / 共 M 页首页上一页 1 2 3 4 5 6 7 8 9 10 下一页尾页

分页的优点：只查询一页，不用查询所有页！

页面的数据都是由 Servlet 传递过来的

那么 Servlet 需要提供给当前页面的数据有哪些呢？

当前页页码（pageCode,pc）：Servlet 提供；

pc：如果页面没有传递当前页码，那么 Servlet 默认是第一页，或者按页面传递的来准！
共几页（totalPage,tp）：Servlet 提供；

tp= 总记录数 / 每页记录数

select count(*) from t_customer;
总记录数：totalpages,tp
每页记录数：业务记录数或叫系统数据！10 行！
当前页数据（beanList）：Servlet 提供；

public class PageBean<T> {private List<T> beanList;// 当前页记录数, 需要传递 
	private int tr;// 总记录数, 需要传递 
	private int pc;// 当前页码, 需要传递 
	private int ps;// 每页记录数, 需要传递 
	private int tp;// 总页数, 计算 
    // 其他的提供 get/set 方法 
    // 但 tp 只提供 get 方法 
    public int getTp(){
        tp=tr/ps;
        return tr%ps==0?tp:tp+1;
    }
}