案例演示:硬分页显示新闻列表
1,创建Web项目PagingNews
配置web服务
在WEB-INF目录里创建lib子目录,添加MYSQL数据库驱动程序jar包
2,在web目录里创建META-INF目录。在里面创建数据库配置文件context.xml
context.xml代码
<?xml version="1.0" encoding="utf-8" ?>
<Context>
<Resource
name="jdbc/news" auth="Container"
type="javax.sql.DataSource"
maxAlive="100" maxIdle="30" maxWait="10000"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/news"
username="root" password="root"/>
</Context>
3,在src里创建net.wlq.news.bean包,在里面创建新闻实体类News
News代码:
package net.wlq.news.bean;
import java.sql.Timestamp;
public class News {
private int nid;
private int ntid;
private String ntname;
private String ntitle;
private String nauthor;
private Timestamp ncreatedate;
private String npicpath;
private String ncontent;
private Timestamp nmodifydate;
private String nsummary;
public int getNid() {
return nid;
}
public void setNid(int nid) {
this.nid = nid;
}
public int getNtid() {
return ntid;
}
public void setNtid(int ntid) {
this.ntid = ntid;
}
public String getNtname() {
return ntname;
}
public void setNtname(String ntname) {
this.ntname = ntname;
}
public String getNtitle() {
return ntitle;
}
public void setNtitle(String ntitle) {
this.ntitle = ntitle;
}
public String getNauthor() {
return nauthor;
}
public void setNauthor(String nauthor) {
this.nauthor = nauthor;
}
public Timestamp getNcreatedate() {
return ncreatedate;
}
public void setNcreatedate(Timestamp ncreatedate) {
this.ncreatedate = ncreatedate;
}
public String getNpicpath() {
return npicpath;
}
public void setNpicpath(String npicpath) {
this.npicpath = npicpath;
}
public String getNcontent() {
return ncontent;
}
public void setNcontent(String ncontent) {
this.ncontent = ncontent;
}
public Timestamp getNmodifydate() {
return nmodifydate;
}
public void setNmodifydate(Timestamp nmodifydate) {
this.nmodifydate = nmodifydate;
}
public String getNsummary() {
return nsummary;
}
public void setNsummary(String nsummary) {
this.nsummary = nsummary;
}
@Override
public String toString() {
return "News{" +
"nid=" + nid +
", ntid=" + ntid +
", ntname='" + ntname + '\'' +
", ntitle='" + ntitle + '\'' +
", nauthor='" + nauthor + '\'' +
", ncreatedate=" + ncreatedate +
", npicpath='" + npicpath + '\'' +
", ncontent='" + ncontent + '\'' +
", nmodifydate=" + nmodifydate +
", nsummary='" + nsummary + '\'' +
'}';
}
}
4,创建net.wlq.news.dbutil包,在里面创建数据库连接管理类ConnectioManager
ConnectioManager.java代码
package net.wlq.news.dbutil;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
/**
* 功能:数据库连接管理类
* 作者:wlq
* 日期:2019年11月11日
*/
public class ConnectionManager {
/**
* 私有化构造方法,拒绝实例化
*/
private ConnectionManager() {
}
/**
* 获取数据库连接静态方法
*
* @return 数据库连接
*/
public static Connection getConnection(){
Connection conn = null;
//
try {
Context ctx = new InitialContext();
//
DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/news");
//
conn = ds.getConnection();
} catch (NamingException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
/**
* 关闭数据库连接静态方法
*
* @param conn 数据库连接
*/
public static void colseConn(Connection conn) {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
5,、创建net.hw.news.dao包,在里面创建新闻数据访问类NewsDao
NewsDao代码:
package net.wlq.news.dao;
import net.wlq.news.bean.News;
import net.wlq.news.dbutil.ConnectionManager;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* 功能:新闻数据访问类
* 作者:wlq
* 日期:2019年11月14日
*/
public class NewsDao {
public List<News> findAllNews() {
//
List<News> newsList = new ArrayList<>();
//
Connection conn = ConnectionManager.getConnection();
//
String strSQL = "select* from news";
//
try {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(strSQL);
while (rs.next()) {
News news = new News();
// 将当前记录字段值去设置新闻实体属性
news.setNid(rs.getInt("nid"));
news.setNtid(rs.getInt("ntid"));
news.setNtitle(rs.getString("ntitle"));
news.setNauthor(rs.getString("nauthor"));
news.setNcreatedate(rs.getTimestamp("ncreatedate"));
news.setNpicpath(rs.getString("npicpath"));
news.setNcontent(rs.getString("ncontent"));
news.setNmodifydate(rs.getTimestamp("nmodifydate"));
news.setNsummary(rs.getString("nsummary"));
// 将当前新闻实体添加到新闻列表
newsList.add(news);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
//
ConnectionManager.colseConn(conn);
}
//
return newsList;
}
/**
* 获取总记录数
*
* @return 总记录数
*/
public int getCount() {
// 返回总记录数
return findAllNews().size();
}
/**
* 按页获取新闻列表
*
* @param pageIndex 当前页码
* @param pageSize 每页记录数
* @return 当前页新闻列表
*/
public List<News> findNewsByPage(int pageIndex,int pageSize){
//
List<News> newsList = new ArrayList<>();
//
Connection conn = ConnectionManager.getConnection();
//
String strSQL = "select * from nws limit ?,?";
//
try {
PreparedStatement pstmt = conn.prepareStatement(strSQL);
//
pstmt.setInt(1,pageSize * (pageIndex -1));
pstmt.setInt(2,pageSize);
//
ResultSet rs = pstmt.executeQuery();
//
while (rs.next()) {
//
News news = new News();
//
news.setNid(rs.getInt("nid"));
news.setNtid(rs.getInt("ntid"));
news.setNtitle(rs.getString("ntitle"));
news.setNauthor(rs.getString("nauthor"));
news.setNcreatedate(rs.getTimestamp("ncreatedate"));
news.setNpicpath(rs.getString("npicpath"));
news.setNcontent(rs.getString("ncontent"));
news.setNmodifydate(rs.getTimestamp("nmodifydate"));
news.setNsummary(rs.getString("nsummary"));
//
newsList.add(news);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
//
ConnectionManager.colseConn(conn);
}
//
return newsList;
}
}
7,在web目录里创建不分页显示全部新闻页面allnews.jsp
<%@ page import="net.wlq.news.dao.NewsDao" %>
<%@ page import="java.util.List" %>
<%@ page import="net.wlq.news.bean.News" %><%--
Created by IntelliJ IDEA.
User: 49天
Date: 2019/11/17
Time: 19:05
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>不分页显示全部新闻列表</title>
</head>
<body>
<%
//创建新闻数据访问对象
NewsDao newsDao = new NewsDao();
//获取全部新闻列表
List<News> newsList = newsDao.findAllNews();
//在页面上显示数据
out.print("<table border='1' cellpadding='10'>");
out.print("<tr><th>编号</th><th>标题</th><th>创建时间</th></tr>");
//遍历新闻列表
for (News news: newsList) {
out.print("<tr><td>" + news.getNid() + "</td>");
out.print("<td>" + news.getNtitle() + "</td>");
out.print("<td>" + news.getNcreatedate() + "</td></tr>");
}
out.print("<table>");
%>
</body>
</html>
不分页运行结果如下: