记jsp+servlet+jdbc实现的新闻管理系统

1.工具：eclipse+SQLyog
2.介绍：实现的内容就是显示新闻的基本信息，然后一个增删改查和分页的操作。
3.数据库表设计

列名	中文名称	数据类型	长度	非空
newsId	文章ID	int	11	√
newsTitle	文章标题	varchar	20	√
newsContent	文章内容	text
newsStatus	是否审核	varchar	10
newsType	文章类型	varchar	20
createtime	创建时间	varchar	20

news表(newsId为主键)4.冗余代码比较多，后期再来修改。
5.功能实现:

主页

在这里插入图片描述

添加页

在这里插入图片描述

修改页

6.代码:
News.java

package com.entity;

/*
 * 新闻表
 */
public class News {

	private Integer newsId;
	private String newsTitle;
	private String newsContent;
	private String newsStatus;
	private String newsType;
	private String createTime;

	public Integer getNewsId() {
		return newsId;
	}

	public void setNewsId(Integer newsId) {
		this.newsId = newsId;
	}

	public String getNewsTitle() {
		return newsTitle;
	}

	public void setNewsTitle(String newsTitle) {
		this.newsTitle = newsTitle;
	}

	public String getNewsContent() {
		return newsContent;
	}

	public void setNewsContent(String newsContent) {
		this.newsContent = newsContent;
	}

	public String getNewsStatus() {
		return newsStatus;
	}

	public void setNewsStatus(String newsStatus) {
		this.newsStatus = newsStatus;
	}

	public String getNewsType() {
		return newsType;
	}

	public void setNewsType(String newsType) {
		this.newsType = newsType;
	}

	public String getCreateTime() {
		return createTime;
	}

	public void setCreateTime(String createTime) {
		this.createTime = createTime;
	}

	@Override
	public String toString() {
		return "News [newsId=" + newsId + ", newsTitle=" + newsTitle + ", newsContent=" + newsContent + ", newsStatus="
				+ newsStatus + ", newsType=" + newsType + ", createTime=" + createTime + "]";
	}

	public News(Integer newsId, String newsTitle, String newsContent, String newsStatus, String newsType,
			String createTime) {
		this.newsId = newsId;
		this.newsTitle = newsTitle;
		this.newsContent = newsContent;
		this.newsStatus = newsStatus;
		this.newsType = newsType;
		this.createTime = createTime;
	}

	public News() {

	}

}

index.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>新闻主页</title>
</head>
<script type="text/javascript">
	location.href="NewsServlet";
</script>
<body>
</body>
</html>

NewsServlet.java

package com.servlet;

import java.io.IOException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.dao.NewsDao;
import com.entity.News;

@WebServlet("/NewsServlet")
public class NewsServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

	private NewsDao newsDao;

	@Override
	public void init() throws ServletException {
		newsDao = new NewsDao();
	}

	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doPost(request, response);
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		request.setCharacterEncoding("utf-8");
		response.setContentType("text/html;charset=utf-8");
		String oprate = request.getParameter("oprate");
		// 如果该次请求是添加新闻
		if ("addNews".equals(oprate)) {
			String newsTitle = request.getParameter("newsTitle");
			String newsType = request.getParameter("newsType");
			String newsContent = request.getParameter("newsContent");
			int row = newsDao.addNews(newsTitle, newsType, newsContent);
			response.sendRedirect("NewsServlet");
			
		} else if("delete".equals(oprate)) {
			Integer id = Integer.valueOf(request.getParameter("id"));
		    int row = newsDao.deleteNewsById(id);
		    response.sendRedirect("NewsServlet");
		} else if("allDelete".equals(oprate)) {
			String[] deleteId = request.getParameterValues("deleteId");
			for (String string : deleteId) {
				Integer did = Integer.valueOf(string);
				//循环一次删一次
				newsDao.deleteNewsById(did);
			}
			response.sendRedirect("NewsServlet");
		} else if("allPass".equals(oprate)) {
			String[] deleteId = request.getParameterValues("deleteId");
			for (String string : deleteId) {
				Integer did = Integer.valueOf(string);
				newsDao.passNews(did);
			}
			response.sendRedirect("NewsServlet");
		} else if("edit".equals(oprate)) {
			Integer id = Integer.valueOf(request.getParameter("id"));
			News news = newsDao.getNews(id);
			request.setAttribute("news", news);
			request.getRequestDispatcher("edit.jsp").forward(request, response);
		} else if("editNews".equals(oprate)) {
			Integer id = Integer.valueOf(request.getParameter("id"));
		    String newsTitle = request.getParameter("newsTitle");
		    String newsType = request.getParameter("newsType");
		    String newsContent = request.getParameter("newsContent");
		    int row = newsDao.updateNews(id, newsTitle, newsType, newsContent);
		    response.sendRedirect("NewsServlet");
		} else {
			Integer pageNumber = 1;
			String pn = request.getParameter("pageNumber");// 传过来的页码
			if (pn != null) {
				pageNumber = Integer.valueOf(pn);
			}
			if (pageNumber <= 1) {
				pageNumber = 1;
			}
			int count = newsDao.getCount();
			Integer pageSize = 5;// 每页显示五条
			int pageCount = count % pageSize == 0 ? count / pageSize : count / pageSize + 1;
			if (pageNumber > pageCount) {
				pageNumber = pageCount;
			}
			// 调用查询新闻集合的方法
			List<News> newsList = newsDao.getNewsList(pageNumber, pageSize);
			// 添加到request作用域中
			request.setAttribute("newsList", newsList);
			// 添加当前显示的页码
			request.setAttribute("pageNumber", pageNumber);
			// 显示最后一页的页码
			request.setAttribute("pageCount", pageCount);
			// 转发到newList页面
			request.getRequestDispatcher("newList.jsp").forward(request, response);
		}
	}
}

newsDao.java

package com.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.entity.News;

public class NewsDao {
	/*
	 * 查询所有新闻集合
	 * 
	 * @pageNumber 页码
	 * 
	 * @pageSize 每页显示几条
	 */
	public List<News> getNewsList(Integer pageNumber, Integer pageSize) {
		List<News> newsList = new ArrayList<News>();
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			// 1.加载驱动
			Class.forName("com.mysql.jdbc.Driver");
			// 2.驱动管理器获取数据库连接
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/news?useUnicode=true&characterEncoding=utf-8", "root", "123456");
//			System.out.println(connection);
			// 3.获取statement，执行sql
			statement = connection.createStatement();
			// 4.执行sql返回结果集并解析
			int startSize = (pageNumber - 1) * pageSize;
			resultSet = statement.executeQuery("select * from news limit " + startSize + "," + pageSize);
			while (resultSet.next()) {
				int newsId = resultSet.getInt("newsId");
				String newsTitle = resultSet.getString("newsTitle");
				String newsContent = resultSet.getString("newsContent");
				String newsStatus = resultSet.getString("newsStatus");
				String newsType = resultSet.getString("newsType");
				String createTime = resultSet.getString("createTime");
				News news = new News(newsId, newsTitle, newsContent, newsStatus, newsType, createTime);
				newsList.add(news);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}
				if (statement != null) {
					statement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return newsList;
	}

	/*
	 * 查询新闻总条数的方法
	 */
	public int getCount() {
		int count = 0;
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			// 1.加载驱动
			Class.forName("com.mysql.jdbc.Driver");
			// 2.驱动管理器获取数据库连接
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/news?useUnicode=true&characterEncoding=utf-8", "root", "123456");
			// 3.获取statement，执行sql
			statement = connection.createStatement();
			resultSet = statement.executeQuery("select count(*) from news");
			if (resultSet.next()) {
				count = resultSet.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}
				if (statement != null) {
					statement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return count;
	}

	/**
	 * 添加新闻的方法
	 * 
	 * @param newsTitle   新闻标题
	 * @param newsType    新闻类型
	 * @param newsContent 新闻正文
	 * @return
	 */
	public int addNews(String newsTitle, String newsType, String newsContent) {
		int row = 0;
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			// 1.加载驱动
			Class.forName("com.mysql.jdbc.Driver");
			// 2.驱动管理器获取数据库连接
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/news?useUnicode=true&characterEncoding=utf-8", "root", "123456");
			// 3.获取statement，执行sql
			String sql = "insert into news(newsTitle,newsContent,newsStatus,newsType,createtime) values(?,?,?,?,?)";
			statement = connection.prepareStatement(sql);
			statement.setObject(1, newsTitle);
			statement.setString(2, newsContent);
			statement.setString(3, "未审核");
			statement.setString(4, newsType);
			statement.setString(5, "2020-02-09");
			row = statement.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}
				if (statement != null) {
					statement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return row;
	}

	public int deleteNewsById(Integer id) {
		int row = 0;
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			// 1.加载驱动
			Class.forName("com.mysql.jdbc.Driver");
			// 2.驱动管理器获取数据库连接
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/news?useUnicode=true&characterEncoding=utf-8", "root", "123456");
			String sql = "delete from news where newsId =?";
			statement = connection.prepareStatement(sql);
			statement.setObject(1, id);
			row = statement.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}
				if (statement != null) {
					statement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return row;
	}

	/**
	 * 审核新闻
	 * 
	 * @param id
	 * @return
	 */
	public int passNews(Integer id) {
		int row = 0;
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			// 1.加载驱动
			Class.forName("com.mysql.jdbc.Driver");
			// 2.驱动管理器获取数据库连接
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/news?useUnicode=true&characterEncoding=utf-8", "root", "123456");
			String sql = "update news set newsStatus = '已审核' where newsId = ?";
			statement = connection.prepareStatement(sql);
			statement.setObject(1, id);
			row = statement.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}
				if (statement != null) {
					statement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return row;
	}

	/**
	 * 根据新闻主键查询新闻对象
	 * 
	 * @param id
	 * @return
	 */
	public News getNews(Integer id) {
		News news = null;
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			// 1.加载驱动
			Class.forName("com.mysql.jdbc.Driver");
			// 2.驱动管理器获取数据库连接
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/news?useUnicode=true&characterEncoding=utf-8", "root", "123456");
//			System.out.println(connection);
			// 3.获取statement，执行sql
			String sql = "select * from news where newsId = ?";
			statement = connection.prepareStatement(sql);
			statement.setInt(1, id);
			// 4.执行sql返回结果集并解析
			resultSet = statement.executeQuery();
			if (resultSet.next()) {
				int newsId = resultSet.getInt("newsId");
				String newsTitle = resultSet.getString("newsTitle");
				String newsContent = resultSet.getString("newsContent");
				String newsStatus = resultSet.getString("newsStatus");
				String newsType = resultSet.getString("newsType");
				String createTime = resultSet.getString("createTime");
				news = new News(newsId, newsTitle, newsContent, newsStatus, newsType, createTime);

			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}
				if (statement != null) {
					statement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return news;
	}
	/**
	 * 修改新闻
	 * @param id
	 * @param newsTitle
	 * @param newsType
	 * @param newsContent
	 * @return
	 */
	public int updateNews(Integer id,String newsTitle,String newsType,String newsContent) {
		int row = 0;
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			// 1.加载驱动
			Class.forName("com.mysql.jdbc.Driver");
			// 2.驱动管理器获取数据库连接
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/news?useUnicode=true&characterEncoding=utf-8", "root", "123456");
			String sql = "update news set newsTitle = ?,newsType = ?,newsContent = ?where newsId = ?";
			statement = connection.prepareStatement(sql);
			statement.setObject(1, newsTitle);
			statement.setObject(2, newsType);
			statement.setObject(3, newsContent);
			statement.setObject(4, id);
			row = statement.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}
				if (statement != null) {
					statement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return row;
	}
}

newsList.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>新闻列表</title>
<script type="text/javascript">
	function quanxuan(qx) {
		var deleteId = document.getElementsByName("deleteId");
		for (var i = 0; i < deleteId.length; i++) {
			deleteId[i].checked = qx.checked;
		}
	}
	
	function changeForm(){
		document.getElementById("oprate").value="allPass";
		document.forms[0].submit();
	}
</script>
</head>
<body>
	<form action="NewsServlet" method="post">
		<input type="submit" value="批量删除" />
		<input type="button" value="批量审核" onclick="changeForm()"/>
		<input type="hidden" name="oprate" id="oprate" value="allDelete"/>
		<table width="1000px" cellpadding="0px" border="1px"
			style="border: 1px solid gray; border-collapse: collapse">
			<tr>
				<th><input type="checkbox" name="qx" onclick="quanxuan(this)" /></th>
				<th>文章ID</th>
				<th>文章标题</th>
				<th>所属栏目</th>
				<th>创建时间</th>
				<th>是否审核</th>
				<th>操作</th>
			</tr>
			<c:forEach items="${newsList}" var="news">
				<tr>
					<td><input type="checkbox" name="deleteId"
						value="${news.newsId }" /></td>
					<td>${news.newsId }</td>
					<td>${news.newsTitle }</td>
					<td>${news.newsType }</td>
					<td>${news.createTime }</td>
					<td>${news.newsStatus }</td>
					<td>
					<a href="NewsServlet?oprate=edit&id=${news.newsId }">编辑</a>
					 | <a
						href="NewsServlet?oprate=delete&id=${news.newsId }">删除 |</a> <a
						href="addNews.jsp">增加</a></td>
				</tr>
			</c:forEach>
			<tr>
				<td colspan="7"><center><a href="NewsServlet?pageNumber=1">首页</a> <a
					href="NewsServlet?pageNumber=${pageNumber-1 }">上一页</a> <a
					href="NewsServlet?pageNumber=${pageNumber+1 }">下一页</a> <a
					href="NewsServlet?pageNumber=${pageCount }">尾页</a></center></td>
			</tr>
		</table>
	</form>
</body>
</html>

addNews.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>添加新闻</title>
<script type="text/javascript" src="kindeditor-4.1.10/kindeditor.js"></script>
<script type="text/javascript">
	KindEditor.ready(function(K) {
		K.create('#content', {
			uploadJson : 'kindeditor-4.1.10/jsp/upload_json.jsp',
			fileManagerJson : 'kindeditor-4.1.10/jsp/file_manager_json.jsp',
			allowFileManager : true
		});
	});

</script>
</head>
<body>
	<form action="NewsServlet" method="post">
		<input type="hidden" name="oprate" value="addNews"/>
		<input type="text" name="newsTitle" placeholder="请输入标题"/><br/>
		新闻栏目
		<select name="newsType">
			<option value="JQuery">jQuery</option>
			<option value="Java">Java</option>
			<option value="MySQL">MySQL</option>
		</select><br/>
		<textarea id="content" name="newsContent" rows=" 8" cols="70"></textarea><br/>
		<input type="submit" value="添加"/>
	</form>
</body>
</html>

editNews.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>修改新闻</title>
<script type="text/javascript" src="kindeditor-4.1.10/kindeditor.js"></script>
<script type="text/javascript">
	KindEditor.ready(function(K) {
		K.create('#content', {
			uploadJson : 'kindeditor-4.1.10/jsp/upload_json.jsp',
			fileManagerJson : 'kindeditor-4.1.10/jsp/file_manager_json.jsp',
			allowFileManager : true
		});
	});

</script>
</head>
<body>
	<form action="NewsServlet" method="post">
	    <input type="hidden" value="${news.newsId }" name="id"/>
		<input type="hidden" name="oprate" value="editNews"/>
		<input type="text" name="newsTitle" value="${news.newsTitle }" placeholder="请输入标题"/><br/>
		新闻栏目
		<select name="newsType">
			<option <c:if test="${news.newsType==\"jQuery\" }">selected</c:if> value="JQuery">jQuery</option>
			<option <c:if test="${news.newsType==\"Java\" }">selected</c:if> value="Java">Java</option>
			<option <c:if test="${news.newsType==\"MySQL\" }">selected</c:if> value="MySQL">MySQL</option>
		</select><br/>
		<textarea id="content" name="newsContent" rows=" 8" cols="70">${news.newsContent }</textarea><br/>
		<input type="submit" value="确定修改"/>
	</form>
</body>
</html>

原文链接：https://blog.csdn.net/weixin_42635052/article/details/104239585