分页查询及新增
首先，先创建一个Web Project项目，然后再建一个dao层，在dao层中建一个BaseDao类，代码如下：
package cn.nicai.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
public class BaseDao {
private Connection conn=null;
private PreparedStatement ps = null;
private ResultSet rs = null;
private Connection getConn(){
try {
Context cnt = new InitialContext();
DataSource ds = (DataSource) cnt.lookup("java:comp/env/jdbc/messageboard");
conn = ds.getConnection();
} catch (NamingException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public void closeAll(ResultSet rs,PreparedStatement ps,Connection conn){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(ps!=null){
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(getConn()!=null){
try {
getConn().close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public int executeUpdate(String sql,Object[] params){
int result = 0;
conn = getConn();
try {
ps=conn.prepareStatement(sql);
if(params!=null){
for(int i=0;i<params.length;i++){
ps.setObject(i+1, params[i]);
}
}
result = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
public ResultSet executeQuery(String sql,Object... params){
conn = getConn();
try {
ps=conn.prepareStatement(sql);
if(params!=null){
for(int i=0;i<params.length;i++){
ps.setObject(i+1, params[i]);
}
}
rs = ps.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
}
然后再在dao层建一个接口——MessageDao，代码如下：
package cn.nicai.dao;
import java.util.List;
import cn.nicai.entity.Message;
public interface MessageDao {
//分页查询
public List<Message> getMessagePage(int pageNo,int pageSize);
//新增
public int getMessageAdd(Message mess);
//总记录数
public int getTotalCount();
}
在dao层或impl中建立一个类，继承BaseDao，实现MessageDao 接口中的方法
package cn.nicai.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import cn.nicai.entity.Message;
public class MessageDaoImpl extends BaseDao implements MessageDao {
//分页查询
public List<Message> getMessagePage(int pageNo, int pageSize) {
String sql = "SELECT * FROM messageboard LIMIT ?,?";
ResultSet rs = this.executeQuery(sql, (pageNo-1)*pageSize,pageSize);
List<Message> list = new ArrayList<Message>();
try {
while(rs.next()){
Message mess = new Message();
mess.setId(rs.getInt("id"));
mess.setName(rs.getString("name"));
mess.setContent(rs.getString("content"));
mess.setDate(rs.getDate("date"));
list.add(mess);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
this.closeAll(rs, null, null);
}
return list;
}
//新增
public int getMessageAdd(Message mess) {
String sql = "INSERT INTO messageboard (`name`,content) VALUES (?,?)";
int result =0;
try {
Object[] params = {mess.getName(),mess.getContent()};
result = this.executeUpdate(sql, params);
} catch (Exception e) {
e.printStackTrace();
} finally {
this.closeAll(null, null, null);
}
return result;
}
//总记录数
public int getTotalCount() {
String sql = "SELECT COUNT(1) FROM messageboard";
int count = -1;
ResultSet rs = this.executeQuery(sql);
try {
rs.next();
count = rs.getInt(1);
} catch (SQLException e) {
e.printStackTrace();
} finally {
this.closeAll(rs, null, null);
}
return count;
}
}
对了，还有实体类Message和Page
package cn.nicai.entity;
import java.sql.Date;
public class Message {
private int id;
private String name;
private String content;
private Date date;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
}
_______________________________________________
package cn.nicai.entity;
import java.util.List;
public class Page {
private int currPageNo;//当前页数
private int totalCount;//总记录数
private int totalPageCount;//总页码=总记录数/页面内容大小
private int pageSize=3;//页面内容大小
private List lists;//集合
public int getCurrPageNo() {
return currPageNo;
}
public void setCurrPageNo(int currPageNo) {
if(currPageNo>0){
this.currPageNo = currPageNo;
}
}
public int getTotalCount() {
return totalCount;
}
public void setTotalCount(int totalCount) {
if(totalCount>0){
this.totalCount = totalCount;
this.totalPageCount = this.totalCount%this.pageSize == 0 ?
this.totalCount/this.pageSize :
this.totalCount/this.pageSize+1;
} else {
totalCount=0;
}
}
public int getTotalPageCount() {
return totalPageCount;
}
public void setTotalPageCount(int totalPageCount) {
this.totalPageCount = totalPageCount;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
if(pageSize>0){
this.pageSize = pageSize;
}
}
public List getLists() {
return lists;
}
public void setLists(List lists) {
this.lists = lists;
}
}
业务层：接口与实现类
package cn.nicai.service;
import java.util.List;
import cn.nicai.entity.Message;
public interface MessageService {
public List<Message> findMessagePage(int pageNo,int pageSize);
public int findMessageAdd(Message mess);
public int findTotalCount();
}
_________________________________________________________________________________
package cn.nicai.service;
import java.util.List;
import cn.nicai.dao.MessageDaoImpl;
import cn.nicai.entity.Message;
public class MessageServiceImpl implements MessageService {
public List<Message> findMessagePage(int pageNo, int pageSize) {
return new MessageDaoImpl().getMessagePage(pageNo, pageSize);
}
public int findMessageAdd(Message mess) {
return new MessageDaoImpl().getMessageAdd(mess);
}
public int findTotalCount() {
return new MessageDaoImpl().getTotalCount();
}
}
控制层——MessageServlet（不要忘了配置web.xml）
package cn.nicai.servlet;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import cn.nicai.entity.Message;
import cn.nicai.entity.Page;
import cn.nicai.service.MessageService;
import cn.nicai.service.MessageServiceImpl;
public class MessageServlet extends HttpServlet {
private MessageService messageService = new MessageServiceImpl();
public MessageServlet() {
super();
}
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
PrintWriter out = response.getWriter();
String opr = request.getParameter("opr");
if("limit".equals(opr)){
String pageIndex= request.getParameter("pageIndex");
if(pageIndex==null){
pageIndex = "1";
}
int currPageNo = Integer.parseInt(pageIndex);
int totalCount = messageService.findTotalCount();
Page pages= new Page();
pages.setTotalCount(totalCount);
pages.setPageSize(3);
int totalPageCount = pages.getTotalPageCount();
if(currPageNo<0){
currPageNo=1;
}else if(currPageNo>pages.getTotalPageCount()){
currPageNo=totalPageCount;
}
pages.setCurrPageNo(currPageNo);
List<Message> list = messageService.findMessagePage(pages.getCurrPageNo(), pages.getPageSize());
pages.setLists(list);
request.setAttribute("pages", pages);
request.getRequestDispatcher("/index.jsp").forward(request, response);
} else if("add".equals(opr)){
String name = request.getParameter("name");
String content = request.getParameter("content");
Message mess = new Message();
mess.setName(name);
mess.setContent(content);
messageService.findMessageAdd(mess);
request.getRequestDispatcher("/index.jsp").forward(request, response);
}
out.flush();
out.close();
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}
接下来是jsp页面
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%
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>留言板</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">
-->
<link rel="stylesheet" href="css/mes.css" type="text/css"></link>
</head>
<body>
<div id="maxs">
<h2>留言板</h2>
<div id="one">
<ul id="txts">
<c:forEach var="mess" items="${pages.lists }">
<li>
<div class="two">作者：${mess.name }</div>
<span class="three">1#</span>
<p class="four">${mess.content }</p>
<span class="five">发表于: ${mess.date }</span>
</li>
</c:forEach>
</ul>
<p id="ps">
当前页面：${pages.currPageNo }/${pages.totalPageCount }
<a href="MessageServlet?opr=limit&pageIndex=1">首页</a>
<a href="MessageServlet?opr=limit&pageIndex=${pages.currPageNo-1 }">上一页</a>
<a href="MessageServlet?opr=limit&pageIndex=${pages.currPageNo+1 }">下一页</a>
<a href="MessageServlet?opr=limit&pageIndex=${pages.totalPageCount }">末页</a>
</p>
</div>
<br>
<form action="MessageServlet?opr=add" method="post" id="myForm">
<p>用户名<input type="text" name="name" placeholder="请输入你的姓名"></p><br>
<p>留言信息<br><textarea rows="3" cols="60" name="content"></textarea></p><br>
<p id="btns"><input onclick="addMess()" type="submit" id="add" value="提交"></p>
</form>
</div>
</body>
</html>
一个简单的分页查询与新增就完成了。
浙公网安备 33010602011771号