小模块练习-分页技术

一、所需要的技术

使用eclipse作为IDE环境、jsp、servlet，jstl技术，mysql数据库，纯JDBC技术。

二、项目的目录结构

1	包名	描述	MVC层次
2	Org.buer.pojo	实体层，存放javaBean，只有属性和geter、seter方法	域层
3	Org.buer.dao	数据访问层接口、只有定义，没有具体实现	数据访问层
4	Org.buer.dao.impl	dao层的具体实现，处理与数据库交互的任务
5	Org.buer.service	service层接口，定义业务
6	Org.buer.service.impl	service层具体的实现，实现具体的业务	服务层
7	Org.buer.controller	控制层	控制层
8	Org.buer.utils	自定义工具层
9	Org.buer.test	测试层

三、项目的jar包，以及说明

commons-beanutils-1.9.2.jar apache下一个JavaBean工具

commons-collections-3.2.2.jarbeanutils依赖包

commons-logging-1.1.3.jar beanutils依赖包

mysql-connector-java-5.1.38.jar mysql连接驱动

四、分层代码

1、实体层

privateString id;

privateString name;

privateString gender;

privateDate birthday;

privateString cellphone;

privateString preference;

privateString email;

privateString type;

privateString dwescription;

数据库中表结构也如此。

--指定存储引擎和字符集--

createtable `test`.`users`(

idint primary key,

namevarchar(40),

passwordvarchar(40),

emailvarchar(60),

birthdaydate

)engine=InnoDBdefault CHARSET = utf8;

--修改用户表增加字段 --

altertable `test`.users add gender varchar(4);

altertable `test`.users add cellphone varchar(20);

altertable `test`.users add preference varchar(100);

altertable test.users add type varchar(40);

altertable test.users add description varchar(255);

--

altertable test.users rename test.customer;

altertable test.customer modify id varchar(40);

2、数据访问层（连接工具jdbcutil、dao、daoimpl）

jdbcutil：

1、src目录下创建db.properties文件

内容如下：

driver=com.mysql.jdbc.Driver #驱动

url=jdbc:mysql://localhost:3306/test?useSSL=true&useUnicode=true&characterEncoding=UTF-8 #连接，附加属性是统一数据库和程序的字符集，不加的话中文会出现乱码

username=lxj

password=123456

2、代码如下

privatestaticProperties props= newProperties();

static{

try{

/*

* 每次连接数据都要加载数据库连接属性，所以将其变为类属性，可以只在类加载的时候加载一次

* 这种方式不行，使用getClass方式不能获得相关的东西

*必须用getClassLoader()来获取相关的属性-java.lang.ExceptionInInitializerError这个错是

* 没有获取db.properties文件

*/

// props.load(JdbcUtils.class.getClass().getResourceAsStream("db.properties") props.load(JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties"));

System.out.println(props.getProperty("driver"));

Class.forName(props.getProperty("driver"));

}catch(IOException e){

//TODOAuto-generated catch block

e.printStackTrace();

}catch(ClassNotFoundException e){

//TODOAuto-generated catch block

e.printStackTrace();

}

}

// 获取连接

publicstaticConnection getConnection() throwsSQLException {

return DriverManager.getConnection(props.getProperty("url"),props.getProperty("username"),props.getProperty("password"));

}

// 关闭连接s

publicstaticvoidclose(Connection conn,Statement st,ResultSet rs){

/*

* 按顺序关闭rs、st、conn

*/

if(rs!= null){

try{

rs.close();

}catch(SQLException e){

//TODOAuto-generated catch block

e.printStackTrace();

}

rs= null;

}

if(st!= null){

try{

st.close();

}catch(SQLException e){

//TODOAuto-generated catch block

e.printStackTrace();

}

st=null;

}

if(conn!= null){

try{

conn.close();

}catch(SQLException e){

//TODOAuto-generated catch block

e.printStackTrace();

}

}

conn= null;

}

Dao：纯粹接口，方法默认的是abstract类型的

publicinterfaceCustomerDao {

publicabstractvoidadd(Customer c);

publicabstractvoiddelete(String id);

publicabstractvoidupdate(Customer c);

publicabstractCustomer findById(String id);

publicabstractList<Customer> findAll();

publicabstractQuerryResult findPages(QuerryInfo qf);

}

DaoImpl：放大的具体实现，因为主要练习分页，所以目前只做出增加查询相关方法

add：

publicvoidadd(Customer c){

Connectionconn= null;

PreparedStatementps= null;

ResultSetrs= null;

try{

conn= JdbcUtils.getConnection();

Stringsql= "insertinto customer(id,name,gender,birthday,cellphone,email,preference,type,description)values(?,?,?,?,?,?,?,?,?)";

ps= conn.prepareStatement(sql);

ps.setString(1,c.getId());

ps.setString(2,c.getName());

ps.setString(3,c.getGender());

ps.setDate(4,newDate(c.getBirthday().getTime()));

ps.setString(5,c.getCellphone());

ps.setString(6,c.getEmail());

ps.setString(7,c.getPreference());

ps.setString(8,c.getType());

ps.setString(9,c.getDwescription());

inti= ps.executeUpdate();

System.out.println("插入了"+i+"条");

}catch(Exception e){

thrownewDaoException(e);

}finally{

JdbcUtils.close(conn,ps,rs);

}

}

findById（）：

publicCustomer findById(String id){

Connectionconn= null;

PreparedStatementst= null;

ResultSetrs= null;

try{

conn= JdbcUtils.getConnection();

Stringsql= "select* from customer where id=?";

st= conn.prepareStatement(sql);

st.setString(1,id);

rs= st.executeQuery();

if(rs.next()){

Customerc= newCustomer();

c.setBirthday(rs.getDate("birthday"));

c.setCellphone(rs.getString("cellphone"));

c.setDwescription(rs.getString("description"));

c.setEmail(rs.getString("email"));

c.setGender(rs.getString("gender"));

c.setId(rs.getString("id"));

c.setName(rs.getString("name"));

c.setPreference(rs.getString("preference"));

c.setType(rs.getString("type"));

returnc;

}

returnnull;

}catch(Exception e){

thrownewDaoException(e);

}finally{

JdbcUtils.close(conn,st,rs);

}

}

findAll（）：

publicList<Customer> findAll() {

Connectionconn= null;

PreparedStatementst= null;

ResultSetrs= null;

try{

conn= JdbcUtils.getConnection();

Stringsql= "select* from customer";

st= conn.prepareStatement(sql);

rs= st.executeQuery();

List<Customer>list= newArrayList<Customer>();

while(rs.next()){

Customerc= newCustomer();

c.setBirthday(rs.getDate("birthday"));

c.setCellphone(rs.getString("cellphone"));

c.setDwescription(rs.getString("description"));

c.setEmail(rs.getString("email"));

c.setGender(rs.getString("gender"));

c.setId(rs.getString("id"));

c.setName(rs.getString("name"));

c.setPreference(rs.getString("preference"));

c.setType(rs.getString("type"));

list.add(c);

}

returnlist;

}catch(Exception e){

thrownewDaoException(e);

}finally{

JdbcUtils.close(conn,st,rs);

}

}

querryResult：

publicQuerryResult findPages(QuerryInfo qf){

Connectionconn= null;

PreparedStatementst= null;

ResultSetrs= null;

QuerryResultqr= newQuerryResult();

try{

conn= JdbcUtils.getConnection();

Stringsql= "select* from customer limit ?,?";

st= conn.prepareStatement(sql);

st.setInt(1,qf.getStartIndex());

st.setInt(2,qf.getPageSize());

rs= st.executeQuery();

List<Customer>list= newArrayList<Customer>();

while(rs.next()){

Customerc= newCustomer();

c.setBirthday(rs.getDate("birthday"));

c.setCellphone(rs.getString("cellphone"));

c.setDwescription(rs.getString("description"));

c.setEmail(rs.getString("email"));

c.setGender(rs.getString("gender"));

c.setId(rs.getString("id"));

c.setName(rs.getString("name"));

c.setPreference(rs.getString("preference"));

c.setType(rs.getString("type"));

list.add(c);

}

// 获取所有记录条数

qr.setTotal(findAll().size());

// 获取结果

qr.setList(list);

returnqr;

}catch(Exception e){

thrownewDaoException(e);

}finally{

JdbcUtils.close(conn,st,rs);

}

}

3、service层：只列出具体的实现方法

privateCustomerDao dao= newCustomerDaoImpl();

@Override

publicvoidaddCustomer(Customer c){

dao.add(c);

}

@Override

publicvoidupdateCustomer(Customer c){

dao.update(c);

}

@Override

publicvoiddeleteCustomer(String id){

dao.delete(id);

}

@Override

publicCustomer findCustomer(String id){

returndao.findById(id);

}

@Override

publicList<Customer> getAllCustomer() {

returndao.findAll();

}

@Override

publicQuerryBean findPage(QuerryInfo qi){

/*

这个方法是分页查询的

*/

QuerryResult qr= dao.findPages(qi);

QuerryBeanqb= newQuerryBean();

qb.setList(qr.getList());

qb.setTotal(qr.getTotal());

qb.setCurrentPage(qi.getCurrentPage());

qb.setPageSize(qi.getPageSize());

returnqb;

}

3、控制层

protectedvoiddoGet(HttpServletRequest request,HttpServletResponse response)throwsServletException, IOException {

request.setCharacterEncoding("utf-8");

QuerryInfoqi= WebUtils.request2Bean(request,QuerryInfo.class);

// List<Customer>cus= bs.getAllCustomer();

// request.setAttribute("cus",cus);

QuerryBeanqb= bs.findPage(qi);

request.setAttribute("qb",qb);

request.getRequestDispatcher("/WEB-INF/pages/listcustomer.jsp").forward(request,response);;

}

方法说明：

WebUtils.request2Bean(request,QuerryInfo.class);这个方法是将数据转化成javabean的，但是必须保证属性名和javabean的相同。

具体如下：

publicclassWebUtils {

publicstatic<E> E request2Bean(HttpServletRequest request,Class<E> beanClass){

try{

Ebean= beanClass.newInstance();

//得到request里面所有数据

Map<String,String[]> map= request.getParameterMap();

//注册一个转换器

ConvertUtils.register(newConverter() {

@Override

public<T> T convert(Class<T> type,Object value){

if(value==null){

returnnull;

}

Stringstr= (String)value;

if(str.trim().equals("")){

returnnull;

}

SimpleDateFormatdf= newSimpleDateFormat("yyyy-MM-dd");

try{

return(T)df.parse(str);

}catch(ParseException e){

thrownewRuntimeException(e);

}

}

},Date.class);

BeanUtils.populate(bean,map);//map{name=aa,password=abc,birthday=1990-10-09} bean(name=aa,password=abc,birthday=Date)

returnbean;

}catch(Exception e){

thrownewRuntimeException(e);

}

}

//产生全球唯一的id

publicstaticString generateID() {

returnUUID.randomUUID().toString();//UUID算法根据你系统的网卡的xx地址、CPU、机器的型号等等生成一个128位长的字符串，可以确保是全球唯一的。

}

}

4、UI说明：

index.jsp框架

<framesetrows="20%,*">

<framename="head"src="${pageContext.request.contextPath}/head.jsp">

<framename="main"src="#">

</frameset>

head.jsp

<bodystyle="text-align:center;">

<h1>XXX客户关系管理系统</h1>

<br/>

<!--target="main"意思是结果显示到name为main的frame中 -->

<ahref="${pageContext.request.contextPath}/AddCustomerServlet"target="main">添加客户</a>

<ahref="${pageContext.request.contextPath}/ListCustomerServlet"target="main">查看客户</a>

</body>

添加界面：

<%@pagelanguage="java"contentType="text/html;charset=UTF-8"

pageEncoding="UTF-8"%>

<%@tagliburi="http://java.sun.com/jsp/jstl/core"prefix="c"%>

<!DOCTYPEhtmlPUBLIC"-//W3C//DTDHTML 4.01 Transitional//EN""http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<metahttp-equiv="Content-Type"content="text/html;charset=UTF-8">

<linkrel="stylesheet"

href="${pageContext.request.contextPath}/css/calendar.css">

<!--注意：ShowCalendar.js所在文件夹应放在WebRoot里，WEB-INF外！！！！！！！！-->

<scripttype="text/javascript"

src="${pageContext.request.contextPath}/js/ShowCalendar.js"></script>

<scripttype="text/javascript"

src="${pageContext.request.contextPath}/js/jquery-1.9.1.min.js"></script>

<scripttype="text/javascript"

src="${pageContext.request.contextPath}/js/calendar.js"></script>

<title>添加用户的视图</title>

</head>

<bodystyle="text-align:center;">

<br/>

<formid="form"

action="${pageContext.request.contextPath}/AddCustomerServlet"

method="post"onsubmit="returnmkpre()">

<tableborder="1"width="50%"align="center">

<tr>

<td>客户姓名</td>

<td><inputtype="text"name="name"></td>

</tr>

<tr>

<td>客户性别</td>

<!--<td>

<inputtype="radio" name="gender" value="男">男

<inputtype="radio" name="gender" value="女">女

<inputtype="radio" name="gender" value="人妖">人妖

</td>-->

<td><c:forEachvar="gender"items="${genders}">

<inputtype="radio"name="gender"value="${gender}">${gender}

</c:forEach></td>

</tr>

<tr>

<td>生日</td>

<td><inputtype="text"name="birthday"class="birthday"id="birthday"value="">

<!--<input type="text" name="birthday"id="birthday" title="点击选择"onclick="showCalendar(this.id)">-->

</td>

</tr>

<tr>

<td>手机</td>

<td><inputtype="text"name="cellphone"></td>

</tr>

<tr>

<td>邮箱</td>

<td><inputtype="text"name="email"></td>

</tr>

<tr>

<td>爱好</td>

<!-- <td>

<inputtype="checkbox"name="pre"value="唱歌">唱歌

<inputtype="checkbox"name="pre"value="跳舞">跳舞

<inputtype="checkbox"name="pre"value="逛夜店">逛夜店

</td>-->

<td><c:forEachvar="pre"items="${preferences}">

<inputtype="checkbox"name="pre"value="${pre}">${pre}

</c:forEach></td>

</tr>

<tr>

<td>客户类型</td>

<!-- <td>

<inputtype="radio" name="type" value="VIP客户">VIP客户

<inputtype="radio" name="type" value="重点客户">重点客户

<inputtype="radio" name="type" value="一般客户">一般客户

</td>-->

<td><c:forEachvar="type"items="${types}">

<inputtype="radio"name="type"value="${type}">${type}

</c:forEach></td>

</tr>

<tr>

<td>客户备注</td>

<td><textarearows="5"cols="60"name="dwescription"></textarea>

</td>

</tr>

<tr>

<td><inputtype="reset"value="重置"></td>

<td><inputtype="submit"value="添加客户"></td>

</tr>

</table>

</form>

<scripttype="text/javascript">

$(".birthday").Calendar({filter:function(time){

vardate = newDate(time);

//这个是日期插件

returntrue;

}});

functionmkpre(){

varpres = document.getElementsByName("pre");

varpreference = "";

for(vari=0;i<pres.length;i++) {

varinput = pres[i];

if(input.checked==true){

preference= preference + input.value + ",";

}

}

preference= preference.substring(0, preference.length-1); //preference="唱歌,跳舞,夜生活";将喜爱拼接后在来传输到后台

//在form表单创建一个input隐藏输入项，如：<inputtype="hidden" name="preference"value="唱歌,跳舞,夜生活,...">

varform = document.getElementById("form");

varinput = document.createElement("input");

input.type= "hidden";

input.name= "preference";

input.value= preference;

form.appendChild(input);

returntrue;

}

</script>

</body>

</html>

说明在注册界面中使用了日期插件，需要导入相关的js文件，并且添加和显示的jsp文件是在WEN-INF下pages文件中，只能通过servlet访问。

另外，喜爱和客户各自维护了一个文件。

publicclassGlobals {

//一般用枚举来表示性别

publicstaticString[] genders= {"男","女","人妖"};

//在实际开发中，在数据库里面应有一个爱好表，爱好是由一个表来维护的。但在此案例中，不会设计的这么复杂，所以在程序里写死来维护

publicstaticString[] preferences= {"唱歌","跳舞","桑拿","打麻将","看凤姐","夜生活","爬山"};

publicstaticString[] types= {"VIP客户","重点客户","普通客户"};

}

显示界面说明：

<%@pagelanguage="java"contentType="text/html;charset=UTF-8"

pageEncoding="UTF-8"%>

<%@tagliburi="http://java.sun.com/jsp/jstl/core"prefix="c"%>

<%@tagliburi="/itcast"prefix="itcast"%>

<!DOCTYPEhtmlPUBLIC"-//W3C//DTDHTML 4.01 Transitional//EN""http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<metahttp-equiv="Content-Type"content="text/html;charset=UTF-8">

<title>列出所有客户</title>

<styletype="text/css">

.even{background-color:#FFF000}

.odd{background-color:#FFCCFF}

tr:HOVER{background-color:#FF99FF}

</style>

</head>

<bodystyle="text-align:center;">

<tableframe="border"border="1"width="85%">

<tr>

<td>客户姓名</td>

<td>性别</td>

<td>生日</td>

<td>手机</td>

<td>邮箱</td>

<td>爱好</td>

<td>类型</td>

<td>备注</td>

<td>操作</td>

</tr>

<c:forEachvar="c"items="${requestScope.qb.list}"varStatus="status">

<trclass="${status.count%2==0?'even':'odd'}">

<td>${c.name}</td>

<td>${c.gender}</td>

<td>${c.birthday}</td> <!--birthday是Date类型的对象，虽然取出来的是Date类型的对象，但el表达式非常聪明，它会自动调用此对象的toString()方法，将其变成一个字符串，并且它内部会根据你到底是哪个国家和地区而去选用相对应的toString()方法-->

<td>${c.cellphone}</td>

<td>${c.email}</td>

<td>${itcast:sub({c.preference})}</td>

<td>${c.type}</td>

<td>${itcast:sub({c.dwescription})}</td>

<td>

<ahref="#">修改</a>

<ahref="#">删除</a>

</td>

</tr>

</c:forEach>

</table>

<br/>

共[${qb.totalPages}]条记录，每页显示<inputtype="text"id="pagesize"value="${qb.pageSize}"onchange="changesize(this.value,${qb.pageSize})"style="width:30px"maxlength="2">条，

共[${qb.totalPages}]页，

当前[${qb.currentPage}]&nbsp;&nbsp;&nbsp;&nbsp;

<c:iftest="${qb.currentPage!=1 }">

<ahref="javascript:void(0)"onclick="gotopage(${qb.previousPage})">上一页</a>

</c:if>

<c:iftest="${qb.currentPage!=qb.totalPages}">

<ahref="javascript:void(0)"onclick="gotopage(${qb.nextPage})">下一页</a>

</c:if>

<inputtype="text"id="pagenum"style="width:30px">

<inputtype="button"value="GO"onclick="gotopage(document.getElementById('pagenum').value)">

<scripttype="text/javascript">

functiongotopage(currentpage) {

if( currentpage < 1 || currentpage != parseInt(currentpage) ||currentpage > ${qb.totalPages} ) {

alert("请输入有效值！！！");

document.getElementById("pagenum").value='';

}else{

varpagesize = document.getElementById("pagesize").value;

window.location.href= '${pageContext.request.contextPath}/ListCustomerServlet?currentPage='+currentpage+'&pageSize='+pagesize;

}

}

functionchangesize(pagesize, oldvalue) {

if( pagesize < 0 || pagesize != parseInt(pagesize) ) {

alert("请输入合法值！！！");

document.getElementById("pagesize").value= oldvalue;

}else{

window.location.href= '${pageContext.request.contextPath}/ListCustomerServlet?pageSize='+pagesize;//当前页currentpage就不传过去了，也即一改变页面大小，由于没带当前页过去，所以就从第1页开始显示

}

}

</script>

</body>

</html>

五、分页说名

我们要分页，必须知道，total、totalPages、currentPage、pageSize、startIndex这些信息。

1、sql中的查询信息参数

publicclassQuerryInfo {

privateintcurrentPage=1;

privateintpageSize= 5;

privateintstartIndex;

publicintgetCurrentPage() {

returncurrentPage;

}

publicvoidsetCurrentPage(intcurrentPage){

this.currentPage= currentPage;

}

publicintgetPageSize() {

returnpageSize;

}

publicvoidsetPageSize(intpageSize){

this.pageSize= pageSize;

}

publicintgetStartIndex() {

//指明开始节点

this.startIndex= (currentPage-1)*pageSize;

returnthis.startIndex;

}

publicvoidsetStartIndex(intstartIndex){

this.startIndex= startIndex;

}

}

2、存放查询结果的

publicclassQuerryResult {

privateListlist;

privateinttotal;

publicListgetList() {

returnlist;

}

publicvoidsetList(Listlist){

this.list= list;

}

publicintgetTotal() {

returntotal;

}

publicvoidsetTotal(inttotal){

this.total= total;

}

}

3、分页bean

publicclassQuerryBean {

privateListlist;

privateinttotal;

privateinttotalPages;

privateintpageSize;

privateintcurrentPage;

privateintpreviousPage;

privateintnextPage;

publicListgetList() {

returnlist;

}

publicvoidsetList(Listlist){

this.list= list;

}

publicintgetTotal() {

returntotal;

}

publicvoidsetTotal(inttotal){

this.total= total;

}

publicintgetTotalPages() {

if(total%pageSize==0){

this.totalPages= total/pageSize;

}else{

this.totalPages= (int)total/pageSize+1;

}

returntotalPages;

}

publicvoidsetTotalPages(inttotalPages){

this.totalPages= totalPages;

}

publicintgetPageSize() {

returnpageSize;

}

publicvoidsetPageSize(intpageSize){

this.pageSize= pageSize;

}

publicintgetCurrentPage() {

returncurrentPage;

}

publicvoidsetCurrentPage(intcurrentPage){

this.currentPage= currentPage;

}

publicintgetPreviousPage() {

if(this.currentPage-1<0) {

this.previousPage= currentPage;

}else{

this.previousPage= this.currentPage-1;

}

returnpreviousPage;

}

publicvoidsetPreviousPage(intpreviousPage){

this.previousPage= previousPage;

}

publicintgetNextPage() {

if(this.currentPage+1<totalPages){

this.nextPage= currentPage;

}else{

this.nextPage= this.currentPage+1;

}

returnnextPage;

}

publicvoidsetNextPage(intnextPage){

this.nextPage= nextPage;

}

}

说句实话，总结写的真菜！！