jsp的分页查询的代码mysql数据库



jsp的分页查询的代码mysql数据库。下面是连接mysql数据库的一个javabean的代码：

package data;
import java.sql.*;

public class LoginData{
Connection conn=null;
public LoginData(){
this.connect();
}

public Connection getConn(){
return this.conn;
}
public boolean connect(){
try{
//使用JDBC桥创建数据库连接
Class.forName(“org.gjt.mm.mysql.Driver”).newInstance();

//使用DriverManager类的getConnection()方法建立连接
//第一个参数定义用户名，第二个参数定义密码
this.conn=java.sql.DriverManager.getConnection(“jdbc:mysql://localhost:3306/logindemo?useUnicode=true&characterEncoding=gb2312″,”root”,”123456″);
}catch(Exception ex){
ex.printStackTrace();
return false;
}
return true;
}
}

//下面是一个javabean的处理mysql数据库的分页显示查询的代码

package data;
import java.sql.*;
import java.util.*;
public class strongSplitPage
{
private Connection conn=null;
private Statement stmt=null;
private ResultSet rs=null;
private ResultSetMetaData rsmd=null;
//sql 查询语句
private String sqlStr;
//总纪录数目
private int rowCount;
//所分得逻辑页数
private int pageCount;
//每页显示的纪录数目
private int pageSize;
//定义表的列数目
private int columnCount;
private int irows;
public void initialize(String sqlStr,int pageSize,int showPage)
{
this.sqlStr=sqlStr;
this.irows=pageSize*(showPage-1);
this.pageSize=pageSize;
try
{
LoginData loginData=new data.LoginData();
this.conn=loginData.getConn();
this.stmt=this.conn.createStatement();
this.rs=this.stmt.executeQuery(this.sqlStr);
this.rsmd=this.rs.getMetaData();
if(this.rs!=null)
{
this.rs.last();
this.rowCount=this.rs.getRow();
this.rs.first();
this.columnCount=this.rsmd.getColumnCount();
this.pageCount=(this.rowCount-1)/this.pageSize+1;
this.rs.close();
this.stmt.close();
}
this.sqlStr=this.sqlStr+” limit “+this.irows+”,”+this.pageSize;
this.stmt=this.conn.createStatement();
this.rs=this.stmt.executeQuery(this.sqlStr);
}catch(Exception ex)
{
ex.printStackTrace();
}
}
public Vector getPage()
{
Vector vData=new Vector();
try
{
if(this.rs!=null)
{

while(this.rs.next())
{
String[] sData=new String[this.columnCount];
for(int j=0;j<this.columnCount;j++)
{
sData[j]=this.rs.getString(j+1);
}
vData.addElement(sData);
}
this.rs.close();
this.stmt.close();
this.conn.close();
}
}catch(Exception ex)
{
ex.printStackTrace();
}
return vData;
}

//获得页面总数
public int getPageCount()
{
return this.pageCount;
}
//获得数据表中总纪录数
public int getRowCount()
{
return this.rowCount;
}
}

//下面是jsp显示分页页面

<%@ page contentType=”text/html; charset=gb2312″ language=”java” import=”java.sql.*” errorPage=”" %>
<%@ page import=”java.io.*” %>
<%@ page import=”java.util.*” %>
<%@ page import=”data.*”%>
<jsp:useBean id=”pages” scope=”page” />
<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd“>
<%!
//显示每页的纪录数
int pageSize=10;
String sqlStr=”";
//当前页
int showPage=1;
%>

<%
sqlStr=”select * from userinfo order by id “;
String strPage=null;
//获得跳转到的页面
strPage=request.getParameter(“showPage”);
if(strPage==null){
showPage=1;
pages.initialize(sqlStr,pageSize,showPage);
}else{
try{
showPage=Integer.parseInt(strPage);
pages.initialize(sqlStr,pageSize,showPage);
}catch(NumberFormatException ex){
showPage=1;
pages.initialize(sqlStr,pageSize,showPage);
}
if(showPage<1){
showPage=1;
pages.initialize(sqlStr,pageSize,showPage);
}
if(showPage>pages.getPageCount()){
showPage=pages.getPageCount();
pages.initialize(sqlStr,pageSize,showPage);
}
}
//取得要显示的数据集合
Vector vData=pages.getPage();
%>
<html xmlns=”http://www.w3.org/1999/xhtml“>
<head>
<meta http-equiv=”Content-Type” content=”text/html; charset=gb2312″ />
<title>分页显示</title>
</head>

<body bgcolor=”#ffffff” text=”#000000″>
<h1 align=center>个人基本信息</h1>
<div align=center>
<table border=”1″ cellspacing=”0″ cellpadding=”0″ width=”80%”>
<tr>
<th width=”20%”>编号</th>
<th width=”40%”>学号</th>
<th width=”40%”>姓名</th>
</tr>
<%
for(int i=0;i<vData.size();i++)
{
//显示数据数
String[] sData=(String[])vData.get(i);
%>
<tr>
<td><%=sData[0]%></td>
<td><%=sData[1]%></td>
<td><%=sData[2]%></td>
</tr>
<%
}
%>
</table>
<p>
<form action=”word_list_javabean.jsp” method=”get” target=”_self”>
<p>共<font color=red><%=pages.getRowCount()%></font>条&nbsp;<%=pageSize%>条/页&nbsp;&nbsp;第<font color=red><%=showPage%></font>页/共<font color=red><%=pages.getPageCount()%></font>页&nbsp;&nbsp;[<a href="word_list_javabean.jsp?showPage=1" target="_self">首页</a>]&nbsp;
<%
//判断“上一页”链接是否要显示
if(showPage>1){
%>
[<a href="word_list_javabean.jsp?showPage=<%=showPage-1%>" target="_self">上一页</a>]&nbsp;
<%
}
else{
%>
[上一页]&nbsp;
<%
}
//判断“下一页”链接是否显示
if(showPage<pages.getPageCount())
{
%>
[<a href="word_list_javabean.jsp?showPage=<%=showPage+1%>" target="_self">下一页</a>]&nbsp;
<%
}
else{
%>
[下一页]&nbsp;
<%
}
%>

[<a href="word_list_javabean.jsp?showPage=<%=pages.getPageCount()%>" target="_self">尾页</a>]&nbsp;转到
<select name=”select”>
<%
for(int x=1;x<=pages.getPageCount();x++)
{
%>
<option value=”<%=x%>”
<%
if(showPage==x){
out.println(“selected”);
}
%> ><%=x%></option>
<%
}
%>
</select>
页&nbsp;&nbsp;&nbsp;
<input type=”submit” name=”go” value=”提交” />
</p>
</form>
</p>
</div>
</body>
</html>

jsp+MySQL的查询结果分页显示[实例]

已验证可以执行的代码。

初学，谨作为个人学习记录。

请大家给出意见或建议。谢谢！

<%@ page contentType=”text/html; charset=gb2312″ language=”java” import=”java.sql.*” %>
<!DOCTYPE html PUBLIC “-//W3C//DTD HTML 4.01 Transitional//EN” “http://www.w3.org/TR/html4/loose.dtd”>

<%
//驱动程序名，比较旧了，如果你用mysql5,自己改。
String driverName=”com.mysql.jdbc.Driver”;
String userName=”root”;//数据库用户名
String userPasswd=”12345″;//密码

String dbName=”ziyuan”;//数据库名

String tableName=”zyd_user”; //表名

//连接字符串
String url=”jdbc:mysql://localhost/”+dbName+”?user=”+userName+”&password=”+userPasswd;
Class.forName(driverName).newInstance();
Connection connection=DriverManager.getConnection(url);
Statement statement = connection.createStatement();


//每页显示记录数
int PageSize = 4; //每页显示记录数
int StartRow = 0; //开始显示记录的编号
int PageNo=0;//需要显示的页数
int CounterStart=0;//每页页码的初始值
int CounterEnd=0;//显示页码的最大值
int RecordCount=0;//总记录数;
int MaxPage=0;//总页数
int PrevStart=0;//前一页
int NextPage=0;//下一页
int LastRec=0;
int LastStartRecord=0;//最后一页开始显示记录的编号

//获取需要显示的页数，由用户提交
if(request.getParameter(“PageNo”)==null){ //如果为空，则表示第1页
if(StartRow == 0){
PageNo = StartRow + 1; //设定为1
}
}else{
PageNo = Integer.parseInt(request.getParameter(“PageNo”)); //获得用户提交的页数
StartRow = (PageNo – 1) * PageSize; //获得开始显示的记录编号
}

//因为显示页码的数量是动态变化的，假如总共有一百页，则不可能同时显示100个链接。而是根据当前的页数显示
//一定数量的页面链接

//设置显示页码的初始值!!
if(PageNo % PageSize == 0){
CounterStart = PageNo – (PageSize – 1);
}else{
CounterStart = PageNo – (PageNo % PageSize) + 1;
}

CounterEnd = CounterStart + (PageSize – 1);
%>

<html>
<head>
<title>分页显示记录</title>
<link rel=”stylesheet” href=”style.css” type=”text/css”>
<style type=”text/css”>
<!–
.STYLE13 {font-family: “幼圆”}
.STYLE18 {font-size: 14px}
–>
</style>
</head>
<%

//获取总记录数
ResultSet rs = statement.executeQuery(“select count(*) from zyd_user” );
rs.next();
RecordCount = rs.getInt(1);

//rs = statement.executeQuery(“SELECT usercode,username,password,comcode,flag_level,flag_status FROM zyd_user ORDER BY usercode DESC LIMIT “+StartRow+”, “+PageSize);
rs = statement.executeQuery(“SELECT usercode,username,password FROM zyd_user ORDER BY usercode LIMIT “+StartRow+”, “+PageSize);

//获取总页数
MaxPage = RecordCount % PageSize;
if(RecordCount % PageSize == 0){
MaxPage = RecordCount / PageSize;
}else{
MaxPage = RecordCount/PageSize+1;
}
%>
<body class=”UsePageBg STYLE13 STYLE18″>
<table width=”100%” border=”0″ bordercolor=”#000000″ class=”InternalHeader”>
<tr>
<td width=”24%”><span class=”STYLE13″>分页显示记录</span></td>
<td width=”76%”>
<span class=”STYLE13″><%=”总共”+RecordCount+”条记录 – 当前页：”+PageNo+”/”+MaxPage %> </span></td>
</tr>
</table>

<br>
<table width=”100%” border=”0″ bordercolor=”#000000″ class=”NormalTableTwo”>
<tr>
<td width=”18%” align=”center” valign=”middle” bordercolor=”#000000″ class=”InternalHeader STYLE13 ” >代码</td>
<td width=”20%” align=”center” valign=”middle” bordercolor=”#000000″ class=”InternalHeader STYLE13 ” >姓名</td>
</tr>

<%
int i = 1;
while (rs.next()) {
int bil = i + (PageNo-1)*PageSize;
%>
<tr>
<td align=”center” valign=”middle” bordercolor=”#000000″ class=”NormalFieldTwo” ><span class=”STYLE13″><%=rs.getString(1)%></span></td>
<td align=”center” valign=”middle” bordercolor=”#000000″ class=”NormalFieldTwo” ><span class=”STYLE13″><%=rs.getString(2)%></span></td>
</tr>
<%
i++;
}%>
</table>
<br>
<table width=”100%” border=”0″ class=”InternalHeader”>
<tr>
<td class=”STYLE13″><div align=”center”>
<%
out.print(“<font size=4>”);
//显示第一页或者前一页的链接
//如果当前页不是第1页，则显示第一页和前一页的链接
if(PageNo != 1){
PrevStart = PageNo – 1;
out.print(“<a href=main_data.jsp?PageNo=1>第一页 </a>: “);
out.print(“<a href=main_data.jsp?PageNo=”+PrevStart+”>前一页</a>”);
}
out.print(“[");

//打印需要显示的页码
for(int c=CounterStart;c<=CounterEnd;c++){
if(c <MaxPage){
if(c == PageNo){
if(c %PageSize == 0){
out.print(c);
}else{
out.print(c+" ,");
}
}else if(c % PageSize == 0){
out.print("<a href=main_data.jsp?PageNo="+c+">"+c+"</a>");
}else{
out.print("<a href=main_data.jsp?PageNo="+c+">"+c+"</a> ,");
}
}else{
if(PageNo == MaxPage){
out.print(c);
break;
}else{
out.print("<a href=main_data.jsp?PageNo="+c+">"+c+"</a>");
break;
}
}
}

out.print("]“);;

if(PageNo < MaxPage){ //如果当前页不是最后一页，则显示下一页链接
NextPage = PageNo + 1;
out.print(“<a href=main_data.jsp?PageNo=”+NextPage+”>下一页</a>”);
}

//同时如果当前页不是最后一页，要显示最后一页的链接
if(PageNo < MaxPage){
LastRec = RecordCount % PageSize;
if(LastRec == 0){
LastStartRecord = RecordCount – PageSize;
}
else{
LastStartRecord = RecordCount – LastRec;
}

out.print(“:”);
out.print(“<a href=main_data.jsp?PageNo=”+MaxPage+”>最后一页</a>”);

}
out.print(“</font>”);
%>
</div></td>
</tr>
</table>
<span class=”STYLE13″>
<%
rs.close();
statement.close();
connection.close();
%>
</span>
</body>
</html>
</style>

连接mysql的javabean实例+简单分页_jsp技巧

<%@ page contentType=”text/html;charset=GBK” language=”java” import=”java.sql.*,java.util.*”%>
<html>
<head>
<title>学习分页</title>
<link href=”../css.css” rel=”stylesheet” type=”text/css”>
</head>
<body>
<jsp:useBean id=”mypage” class=”ckstudio.db.mysqlconn” scope=”page”/>
<table width=”1024″><tr>
<td>rs.getString(user_id)|</td>
<td>rs.getString(user_name)|</td>
<td>rs.getString(user_mail)|</td>
<td>rs.getString(user_adds)</td>
</tr>
<br>
<tr>
<td style=”hight:8px:width:5;background-color:red;”>
<td style=”hight:8px:width:5;background-color:red;”>
<td style=”hight:8px:width:5;background-color:red;”>
<td style=”hight:8px:width:5;background-color:red;”>
</tr>
<%!
int Cint(String cint)
{
try {
int n;
n= Integer.parseInt(cint);
return n;
}
catch (NumberFormatException e) {
return 0;
}
}
%>
<%
int PageSize=5; //设置每页显示的记录条数
int ShowPage=1; //设置想要显示的页数
int RowCount = 0; //ResultSet的记录数目 初始值
int PageCount = 0; //ResultSet分页后的总页数 初始值
int duoyu=0; //设置分页最后一页的记录数 初始值
//以上是对这些数据的初始化 pagesize可自行设置
String sql=”select * from member order by user_id desc”;
ResultSet rs=mypage.executeQuery(sql);
//建立ResultSet(结果集)对象，并执行SQL语句
%>
<%if(rs.next())
{
rs.last(); //将指标移至最后一笔记录
RowCount=rs.getRow();
duoyu=RowCount % PageSize;
if(duoyu==0)
{
PageCount=RowCount/PageSize;
}
else{
PageCount=(RowCount-duoyu)/PageSize;
PageCount++;
}
// else里面 如果除不尽 侧页数为商加一;
//计算显示的页数
String ToPage = request.getParameter(“ToPage”);
if(ToPage==”"){ToPage=”1″;}
ShowPage = Cint(ToPage); //取得指定显示的分页页数
//下面的if语句将判断用户输入的页数是否正确
if(ShowPage > PageCount)
{ //判断指定页数是否大于总页数, 否则设置显示最后一页
ShowPage = PageCount;
}
else if(ShowPage <= 0)
{ //若指定页数小于0, 则设置显示第一页的记录
ShowPage = 1;
}
}
rs.absolute((ShowPage – 1) * PageSize+1); //计算欲显示页的第一个记录位置
%>
<!–下面是分页–>
<%
//利用For循环配合PageSize属性输出一页中的记录
int i=1;
rs.previous();
//String user_name,user_email,user_adds;
while(rs.next()&i<=PageSize)
{
i++;
//user_id=rs.getInt(“user_id”);
//user_name=rs.getString(“user_name”);
//user_mail=rs.getString(“user_mail”);
//user_adds=rs.getString(“user_adds”);

%>
<tr>
<td><%=rs.getString(1)%></td>
<td><%=rs.getString(2)%></td>
<td><%=rs.getString(3)%></td>
<td><%=rs.getString(4)%></td>
</tr>
<%}
%>
</table>
<table>
<br>
<br><form aciont=”page.jsp”>
<tr><td width=”1024″ height=”23″>
<p align=”right”>第<%=ShowPage%>页/共<%=PageCount%>页,<%=RowCount%>个用户
第
<input name=”ToPage” type=”text” size=”2″ maxlength=”2″>页&nbsp;<input type=”submit” value=”GO”>&nbsp;&nbsp;<%if(ShowPage!=1){%>
<a href=”page.jsp?ToPage=1″>首页</a>
<%}
else{
%>
首页
<%}if(ShowPage>1) {%>
<a href=”page.jsp?ToPage=<%=(ShowPage-1)%>”>上一页</a>
<%}
else {
%>
上一页
<%}
if(ShowPage<PageCount) {
%>
<a href=”page.jsp?ToPage=<%=(ShowPage+1)%>”>下一页</a>
<%}
else {
%>
下一页
<%}if(ShowPage!=PageCount){%>
<a href=”page.jsp?ToPage=<%=PageCount%>”>末页</a>
<%}
else{%>
末页
<%}%>
</font></p></form>
</table>
<%
rs.close();
%> 连接mysql的javabean实例+简单分页_jsp技巧

连接mysql的javabean实例+简单分页

contentType=”text/html;charset=GBK” language=”java” import=”java.sql.*,java.util.*”%>
<html>
<head>
<title>学习分页</title>
<link href=”../CSS.css” rel=”stylesheet” type=”text/css”>
</head>
<body>
<jsp:useBean id=”mypage”http://www.knowsky.com/article.asp?typeid=172″ style=”text-decoration: none; color: black; line-height: 12pt; font-family: 宋体; background-color: rgb(255, 255, 255);”>MySQLconn” scope=”page”/>
<table width=”1024″><tr>
<td>rs.getString(user_id)|</td>
<td>rs.getString(user_name)|</td>
<td>rs.getString(user_mail)|</td>
<td>rs.getString(user_adds)</td>
</tr>
<br>
<tr>
<td style=”hight:8px:width:%25;background-color:red;”>
<td style=”hight:8px:width:%25;background-color:red;”>
<td style=”hight:8px:width:%25;background-color:red;”>
<td style=”hight:8px:width:%25;background-color:red;”>
</tr>
<%!
int Cint(String cint)
{
try {
int n;
n= Integer.parseInt(cint);
return n;
}
catch (NumberFormatException e) {
return 0;
}
}
%>
<%
int PageSize=5; //设置每页显示的记录条数
int ShowPage=1; //设置想要显示的页数
int RowCount = 0; //ResultSet的记录数目 初始值
int PageCount = 0; //ResultSet分页后的总页数 初始值
int duoyu=0; //设置分页最后一页的记录数 初始值
//以上是对这些数据的初始化 pagesize可自行设置
String sql=”select * from member order by user_id desc”;
ResultSet rs=mypage.executeQuery(sql);
//建立ResultSet(结果集)对象，并执行SQL语句
%>
<%if(rs.next())
{
rs.last(); //将指标移至最后一笔记录
RowCount=rs.getRow();
duoyu=RowCount % PageSize;
if(duoyu==0)
{
PageCount=RowCount/PageSize;
}
else{
PageCount=(RowCount-duoyu)/PageSize;
PageCount++;
}
// else里面 如果除不尽 侧页数为商加一;
//计算显示的页数
String ToPage = request.getParameter(“ToPage”);
if(ToPage==”"){ToPage=”1″;}
ShowPage = Cint(ToPage); //取得指定显示的分页页数
//下面的if语句将判断用户输入的页数是否正确
if(ShowPage > PageCount)
{ //判断指定页数是否大于总页数, 否则设置显示最后一页
ShowPage = PageCount;
}
else if(ShowPage <= 0)
{ //若指定页数小于0, 则设置显示第一页的记录
ShowPage = 1;
}
}
rs.absolute((ShowPage – 1) * PageSize+1); //计算欲显示页的第一个记录位置
%>
<!–下面是分页–>
<%
//利用For循环配合PageSize属性输出一页中的记录
int i=1;
rs.PRevious();
//String user_name,user_email,user_adds;
while(rs.next()&i<=PageSize)
{
i++;
//user_id=rs.getInt(“user_id”);
//user_name=rs.getString(“user_name”);
//user_mail=rs.getString(“user_mail”);
//user_adds=rs.getString(“user_adds”);

%>
<tr>
<td><%=rs.getString(1)%></td>
<td><%=rs.getString(2)%></td>
<td><%=rs.getString(3)%></td>
<td><%=rs.getString(4)%></td>
</tr>
<%}
%>
</table>
<table>
<br>
<br><form aciont=”page.jsp“>
<tr><td width=”1024″ height=”23″>
<p align=”right”>第<%=ShowPage%>页/共<%=PageCount%>页,<%=RowCount%>个用户
第
<input name=”ToPage” type=”text” size=”2″ maxlength=”2″>页&nbsp;<input type=”submit” value=”GO”>&nbsp;&nbsp;<%if(ShowPage!=1){%>
<a href=”page.jsp?ToPage=1″>首页</a>
<%}
else{
%>
首页
<%}if(ShowPage>1) {%>
<a href=”page.jsp?ToPage=<%=(ShowPage-1)%>”>上一页</a>
<%}
else {
%>
上一页
<%}
if(ShowPage<PageCount) {
%>
<a href=”page.jsp?ToPage=<%=(ShowPage+1)%>”>下一页</a>
<%}
else {
%>
下一页
<%}if(ShowPage!=PageCount){%>
<a href=”page.jsp?ToPage=<%=PageCount%>”>末页</a>
<%}
else{%>
末页
<%}%>
</font></p></form>
</table>
<%
rs.close();
%>