//实体类
package entity;
public class note {
private int id;
private String title;
private String author;
private String content;
public note(){}
public note(String title,String author,String content)
{
this.title=title;
this.author=author;
this.content=content;
}
public note(int id,String title,String author,String content)
{
this.id=id;
this.title=title;
this.author=author;
this.content=content;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
}
//连接数据库的基类
package dao;
import java.sql.*;
public abstract class BaseJdbcDao {
private static final String DBDRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
private static final String DBURL = "jdbc:sqlserver://localhost:1433;DataBaseName=notetest";
private static final String DBUSER="sa";
private static final String DBPASS="sa";
protected Connection conn=null;
protected Statement stmt=null;
protected PreparedStatement pstmt=null;
protected ResultSet rst=null;
public Connection getConn()
{
try{
Class.forName(DBDRIVER);
conn=DriverManager.getConnection(DBURL,DBUSER,DBPASS);
// System.out.println("连接成功");
}catch(ClassNotFoundException e)
{
System.out.println("没有找到驱动");
e.getMessage();
}catch(SQLException e)
{
System.out.println("数据库联接失败");
e.getMessage();
}finally
{
return conn;
}
}
public void CloseAll()
{
if(rst!=null)
{
try{
rst.close();
}catch(SQLException e)
{
e.toString();
}
}
if(pstmt!=null)
{
try{
pstmt.close();
}catch(SQLException e)
{
e.toString();
}
}
if(stmt!=null)
{
try{
stmt.close();
}catch(SQLException e)
{
e.toString();
}
}
if(conn!=null)
{
try{
conn.close();
}catch(SQLException e)
{
e.toString();
}
}
}
}
//业务类
package dao;
import java.sql.*;
import java.util.*;
import entity.note;
public class noteDao extends BaseJdbcDao{
int count=0;
//得到所有记录数
public int getNoteCount()
{
String sql1="select count(*) from note";
int pageCount=0;
conn=super.getConn();
try{
pstmt=conn.prepareStatement(sql1);
rst=pstmt.executeQuery();
rst.next();
count=rst.getInt(1);
}catch(SQLException e)
{
e.toString();
}finally
{
super.CloseAll();
}
return count;
}
//分页显示
public List ShowNotesByPage(int page,int pageSize)
{
List listnote=new ArrayList();
note nn=null;
int preCount = pageSize*(page-1);
int pageCount=0;
String sql="select top "+pageSize+" * from note where id not in (select top "+preCount+" id from note order by id desc) order by id desc";
conn=super.getConn();
try{
if(count%pageSize==0){
pageCount=count/pageSize;
}
else
{
pageCount=count/pageSize+1;
}
pstmt=conn.prepareStatement(sql);
rst=pstmt.executeQuery();
while(rst.next())
{
nn=new note();
nn.setId(rst.getInt("id"));
nn.setTitle(rst.getString("title"));
nn.setAuthor(rst.getString("author"));
nn.setContent(rst.getString("content"));
listnote.add(nn);
}
}catch(SQLException e)
{
e.toString();
}finally
{
super.CloseAll();
}
return listnote;
}
}
//页面中的代码
<%@ page language="java" import="java.util.*,entity.*,dao.*" pageEncoding="gbk"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'showListNotes.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
</head>
<body>
<h1>所有留言</h1>
<%
List list=new ArrayList();
note nn=null;
noteDao notedao=new noteDao();
int count=notedao.getNoteCount();
int pageSize =5;
int currentPage = 1;
int pagecount;
//得到总共的页数。
if(count%pageSize==0)
{
pagecount=count/pageSize;
}
else{
pagecount=count/pageSize+1;
}
String pager = request.getParameter("page");
if(pager!=null)
{
currentPage = Integer.parseInt(pager);
}
//给上一页（prepage），下一页（nextpage)赋值。保障传递的page不是-1，-2，等等不符合条件的值。
int prepage=currentPage;
int nextpage=currentPage;
if(currentPage>1)
{
prepage=currentPage-1;
}
if(currentPage<pagecount)
{
nextpage=currentPage+1;
}
list=notedao.ShowNotesByPage(currentPage,pageSize);
%>
<table border="1">
<tr>
<td>标题</td>
<td>作者</td>
<td>内容</td>
</tr>
<%
for(int i=0;i<list.size();i++)
{
nn=(note)list.get(i);
%>
<tr>
<td><%=nn.getTitle() %></td>
<td><%=nn.getAuthor() %></td>
<td><%=nn.getContent() %></td>
</tr>
<%
}
%>
</table>
//传递page参数
<a href="showListNotes.jsp?page=<%=prepage %>">上一页</a>
<a href="showListNotes.jsp?page=<%=nextpage %>">下一页</a>
</body>
</html>
