JSP实现从mysql读取数据并实现分页功能

时间:2022-09-20 20:10:42

        最近学习JSP。结合之前的jdbc写了一个从数据库读取数据并分页在页面中显示的程序。

1、login界面

<html>
<head>
<meta http-equiv="content-type",content="text/html;charset=gb2312"/>
<title>Welcome to the TEST!</title>
</head>
<body>
<center>
<h1>Please check in!</h1>
<hr>
<form action="check.jsp" method="post">
<table border="2">
<tr>
<td colspan="2"><center>Users Login</center></td>
</tr>
<tr>
<td>User ID:</td>
<td><input type="text" name="id"></td>
</tr>
<tr>
<td>User Name:</td>
<td><input type="text" name="name"></td>
</tr>
<tr>
<td>User Password:</td>
<td><input type="password" name="password"></td>
</tr>
<tr>
<td><input type="submit" value="submit"></td>
<td><input type="reset" value="reset"></td>
</tr>
</table>
</form>
</center>
</body>
</html>

2、在check.jsp页面中进行判断,是否由此用户。

<%@page contentType="text/html" pageEncoding="gb2312" %>
<%@page import="java.sql.*" %>
<html>
<head>
<title>check</title>
</head>
<body>
<%!
public static final String DBDRIVER="org.gjt.mm.mysql.Driver";
public static final String DBURL="jdbc:mysql://localhost:3306/client";
public static final String DBUSER = "root";
public static final String DBPASSWORD = "pass";

%>
<%
Connection conn = null;
String sql = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
Boolean flag = false;
String name_get = request.getParameter("name");

try{
Class.forName(DBDRIVER);
conn = DriverManager.getConnection(DBURL,DBUSER,DBPASSWORD);
sql = "select name from client where id=? AND password=?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1,request.getParameter("id"));
pstmt.setString(2,request.getParameter("password"));
rs =pstmt.executeQuery();
if(rs.next()){
name_get = rs.getString(1);
flag=true;
}
}catch(Exception e){

}finally{
try{
rs.close();
pstmt.close();
conn.close();
}catch(Exception e){}
}
%>
<%
if(flag){
%>
<jsp:forward page="success.jsp">
<jsp:param name="get" value="<%=name_get%>"/>
</jsp:forward>

<%
}else{
%>
<jsp:forward page="failure.jsp">
<jsp:param name="get" value="<%=name_get%>"/>
</jsp:forward>
<%
}
%>
</body>
</html>

3、登陆失败,跳转到failure.jsp页面

<%@page contentType="text/html" pageEncoding="gb2312" %>
<html>
<head>
<title>Login failed!</title>
</head>
<body>
<%
String name = request.getParameter("get");
%>
<center>
<h1>Login failed!</h1><br/>
<h2>Name <font color="red"><%=name%></font> doesn't exist!</h2>
<h2>Please Click <a href="login.html">here</a> to login again!</h2>
</center>
</body>
</html>

4、登陆成功,跳转到success.jsp页面。在此页面中实现分页功能

<%@page contentType="text/html" pageEncoding="gb2312" %>
<%@page import="java.sql.*"%>
<html>
<head>
<title>Login successful!</title>

</head>
<body>
<%
String name = request.getParameter("get");
%>
<center>
<h1>Hello ! Dear <font color="blue"><%=name%></font>~! </h1>
<br/>
<%!
public static final String DBDRIVER = "org.gjt.mm.mysql.Driver";
public static final String DBURL = "jdbc:mysql://localhost:3306/client";
public static final String DBUSER = "root";
public static final String DBPASS = "pass";

public static final int PAGEITEMS = 5;
%>
<%
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = null;
String empno = null;
String ename = null;
String job = null;
double sal = 0;
int pagenum = 0;
String currentpage_tmp = null;
int currentpage = 1;
String flag_tmp = null;
int flag = 0;
%>
<hr>
<caption><h3>empire list</h3></caption>
<table border = "2" width="400">
<tr>
<td>empno</td>
<td>ename</td>
<td>job</td>
<td>sal</td>
</tr>
<%

Class.forName(DBDRIVER);
conn = DriverManager.getConnection(DBURL,DBUSER,DBPASS);
pstmt = conn.prepareStatement("select * from data",ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
rs = pstmt.executeQuery();
rs.last();
pagenum = rs.getRow()/PAGEITEMS==0?(rs.getRow()/PAGEITEMS):(rs.getRow()/PAGEITEMS+1);

currentpage_tmp = request.getParameter("currentpage");
flag_tmp = request.getParameter("flag");
if(currentpage_tmp == null){
currentpage_tmp = "1";
}
if(flag_tmp == null){
flag_tmp = "0";
}
currentpage = Integer.parseInt(currentpage_tmp);
flag = Integer.parseInt(flag_tmp);
if(flag==2){
currentpage++;
}else if(flag==1){
currentpage--;
}
if(currentpage == 0){
currentpage = 1;
}else if(currentpage == pagenum+1){
currentpage = pagenum;
}
sql = "select empno,ename,job,sal from data limit "+(currentpage-1)*5+","+PAGEITEMS;

pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
rs = pstmt.executeQuery();
while(rs.next()){
empno = rs.getString(1);
ename = rs.getString(2);
job = rs.getString(3);
sal = rs.getDouble(4);
%>
<tr>
<td><%=empno%></td>
<td><%=ename%></td>
<td><%=job%></td>
<td><%=sal%></td>
</tr>
<%
}
%>

</table>
<a href="success.jsp?currentpage=1&flag=0&get=<%=name%>">FP</a>  
<a href="success.jsp?currentpage=<%=currentpage%>&flag=1&get=<%=name%>">Back</a> 
<a href="success.jsp?currentpage=<%=currentpage%>&flag=2&get=<%=name%>">Forw</a> 
<a href="success.jsp?currentpage=<%=pagenum%>&flag=0&get=<%=name%>">LP</a>
</center>
</body>
</html>

对分页功能的分析:

        首先要知道的是,分页功能便是服务器端跳转的实现。在最后的四个超链接中,分别传递了currentpage和flag两个参数。

        在此介绍一下超链接传值的语法格式:

             <a href="跳转的界面"?参数名=参数值&参数名=参数值...>  </a>

        为了实现分页,即跳转到本页。currentpage是记录当前页的参数,flag是用来判断到底页码+1还是页码-1的参数。在本页面加载之前利用jsp内置对象进行接收,如此循环。

        还有就是,要注意这两个参数在第一次循环运行时的空指针报错。因此要加上null时的if处理语句。

总体来说还是蛮简单的。