JSP+mysql简单分页

小林信仁

浏览: 53595 次
性别:
来自: 上海

最近访客更多访客>>

kxjh-2003

meirc

yongmeixin

JMS_Exception

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

2014-05 ( 12)
2014-03 ( 18)
2014-02 ( 20)
更多存档...

自己一直以来用的最多的就是mysql数据库了，小巧精悍，而且是Freesoftware。其实，mysql也可以用于企业级的应用，他完全能够胜任一般的应用开发了。写个JSP+mysql分页程序，性能没有考虑。主要用他的limit方法分页的。

我的mysql表结构几数据:
/*
MySQL Data Transfer
Source Host: localhost
Source Database: job_db
Target Host: localhost
Target Database: job_db
Date: 2007-4-28 20:10:09
*/

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for cjob
-- ----------------------------
Create TABLE `cjob` (
`jobid` int(4) NOT NULL auto_increment,
`cid` varchar(20) NOT NULL,
`specialty` varchar(50) default NULL,
`job` varchar(20) default NULL,
`emolument` varchar(50) default NULL,
`ptime` datetime default NULL,
`atime` datetime default NULL,
`other` varchar(200) default NULL,
PRIMARY KEY (`jobid`),
KEY `cid` (`cid`),
CONSTRAINT `cjob_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `company` (`cid`) ON Delete CASCADE ON Update CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

-- ----------------------------
-- Records
-- ----------------------------
Insert INTO `cjob` VALUES ('2', 'sdju', '计算机', '软件工程师', '2500', '2007-04-13 00:00:00', '2007-12-02 00:00:00', '精通计算机');
Insert INTO `cjob` VALUES ('3', 'sdju', '国际金融', '会计师', '3000', '2007-04-13 00:00:00', '2007-12-02 00:00:00', '精通会计');
Insert INTO `cjob` VALUES ('4', 'sdju', '国际贸易', '国际采购', '5000', '2007-04-13 00:00:00', '2007-12-02 00:00:00', '善于国际交流和英语');
Insert INTO `cjob` VALUES ('5', 'sdju', '法律', '公司律师代表', '5000', '2007-04-13 00:00:00', '2007-12-02 00:00:00', '公司律师代表');
Insert INTO `cjob` VALUES ('6', 'microsoft', '计算机', '.Net工程师', '5000', '2007-04-13 00:00:00', '2007-12-02 00:00:00', '.Net工程师');
Insert INTO `cjob` VALUES ('7', 'microsoft', '计算机', 'MSN研发经理', '6000', '2007-04-13 00:00:00', '2007-12-02 00:00:00', 'MSN研发经理');
Insert INTO `cjob` VALUES ('8', 'discuz', '计算机', 'PHP开发工程师', '3000', '2007-04-13 00:00:00', '2007-12-02 00:00:00', 'PHP开发工程师');
Insert INTO `cjob` VALUES ('9', 'guomei', '服务行业', '门店店长', '2500', '2007-04-14 00:00:00', '2007-12-02 00:00:00', '门店店长');
Insert INTO `cjob` VALUES ('10', 'guomei', '服务行业', '储备干部', '2000', '2007-04-14 00:00:00', '2007-12-02 00:00:00', '储备干部');

JSP code:

<%@ page contentType="text/html;charset=GBK" pageEncoding="GBK" language="java" %>
<html>
<head>
<title>Mysql简单分页</title>
</head>
<style type="text/css">
body,html{
padding:0px;
margin:0px;
}
#PageList {

margin:auto;

}
#PageList ul {
padding:0px;
margin:4px;
margin-top:0px;
margin-bottom:0px;
clear:both;
}
#PageList ul li{
padding-left:8px;
padding-right:8px;
margin:0px;
margin-top:0px;
margin-bottom:0px;
float:left;
}
</style>
<body>
<%@ page import="java.sql.*" %>
<%

Class.forName("com.mysql.jdbc.Driver").newInstance();
String URL = "jdbc:mysql://localhost:3306/job_db?useUnicodeEncoding='GBK'";
String username = "root";
String password = "gongjunbi";
Connection conn = DriverManager.getConnection(URL,username,password);
Statement stmt = conn.createStatement();

%>
<%
int intRowCount; //总行数
int intPageCount; //总页数
int intPageSize; //页面行数
int intPage; //页码数
String strPage; //页面传递参数
int i,k;
intPageSize = 3; //定义每页显示3条记录
strPage = request.getParameter("page");
if(strPage == null){//初始化页面大小
intPage = 1;
}else{
intPage = Integer.parseInt(strPage);
if(intPage < 1)
intPage = 1;
}
String sqlQuery = "select count(jobid) from cjob order by jobid asc";
ResultSet rsCount = stmt.executeQuery(sqlQuery);
rsCount.next(); //游标指向第一行
intRowCount = rsCount.getInt(1); //取得总行数
intPageCount = (intRowCount + intPageSize - 1) / intPageSize;//计算出总页数
if(intPage > intPageCount)
intPage=intPageCount;
i = (intPage -1)*intPageSize;
String resultQuery = "select * from cjob order by jobid asc limit " + i + "," + intPageSize;
ResultSet rs=stmt.executeQuery(resultQuery);
%>
<div id="PageList">
<ul id="title">
<li>ID</li>
<li>Specialty</li>
<li>Job</li>
<li>Emolument</li>
<li>Ptime</li>
</ul>
<%
while(rs.next()){
%>
<ul id="title">
<li><%=rs.getString("jobid")%></li>
<li><%=rs.getString("specialty")%></li>
<li><%=rs.getString("job")%></li>
<li><%=rs.getString("Emolument")%></li>
<li><%=rs.getString("ptime").substring(0,10)%></li>
</ul>
<% i++;} %>
<ul id="showPage">
<li>共:[<%=intRowCount%>]记录</li>
<li>第:[<%=intPage%>]页</li>
<li><%if(intPage > 1){%><a href="PageList.jsp?page=<%=intPage - 1%>">上一页</a><%}else{%><a href="PageList.jsp?page=<%=intPage%>">首页</a><%}%></li>
<li><%if(intPage < intPageCount){%><a href="PageList.jsp?page=<%=intPage + 1%>">下一页</a><%}else{%><a href="PageList.jsp?page=<%=intPage%>">末页</a><%}%></li>

<li></li>
</ul>
</div>
</body>
</html>