JSP显示新闻

 JSP显示新闻

实验内容和要求：
在 mysql创建新闻表，用户登录时，用serverlet获取用户名密码，查询数据库是否存在，如果是正确的用户名密码，查询新闻表，将新闻数据传给JSP首页，JSP首页用EL表达式显示新闻标题列表。

1.在 mysql创建新闻表：
ID: 新闻的唯一标示
title：新闻的标题
content: 新闻的内容
created_at: 新闻添加的时间<br>types: 新闻的类型
image: 新闻的缩略图
author: 作者
view_count: 浏览量
is_valid: 删除标记

CREATE TABLE `news`(
`id` INT NOT NULL AUTO_INCREMENT,
`title` VARCHAR(200) NOT NULL,
`content` VARCHAR(2000) NOT NULL,
`types` VARCHAR(10) NOT NULL,
`image` VARCHAR(300) NULL,
`author` VARCHAR(20) NULL,
`view_count`INT DEFAULT 0,
`created_at` DATETIME NULL,
`is_valid` SMALLINT DEFAULT 1,
PRIMARY KEY(`id`)
)DEFAULT CHARSET ='UTF8'

INSERT INTO News VALUES (
'1','张伯礼院士：北京疫情估计7月中下旬清零','健康时报','06月25日 09:20'
);
INSERT INTO News VALUES (
'2','评《生态时代的文化反思》：人类历史到了“转弯”时刻','新浪文化','06月17日 13:57'
);
INSERT INTO News VALUES (
'3','87版《红楼梦》幕后故事：去污粉卸妆，花四年半作曲','中国新闻网','06月05日 10:40'
);
INSERT INTO News VALUES (
'4','ALAB×文化会客厅：每天给自己加一份从容','新浪文化','03月06日 10:33'
);

SELECT * FROM News;

2.Mysql新建用户信息表+插入相应数据：
CREATE DATABASE USERS;
USE USERS;

CREATE TABLE USERS_INFO (
UID VARCHAR(20) NOT NULL,
UNAME VARCHAR(20) NOT NULL,
UPWD VARCHAR(20) NOT NULL,
PRIMARY KEY(UID)
);

INSERT INTO USERS_INFO VALUES (
'111','John','123'
);
INSERT INTO USERS_INFO VALUES (
'222','Simon','123'
);
INSERT INTO USERS_INFO VALUES (
'333','Mike','123'
);

SELECT * FROM USERS_INFO;

2.用户登录时，用serverlet获取用户名密码，查询数据库是否存在：
首先我们要设计一个数据库,包含三个字段,id username password
(1).还要有一个包装数据的JavaBean(非常简单就不用说了)
(2).再就是用户的登录页面:

<form action = "LoginServlet" method= "post" name = "form1">
<table align = "center">
<tr><td><h3>用户登录</h3></td></tr>
<tr><td>账号：<input type = "text" name = "email"></td></tr>
<tr><td>密码：<input type = "password" name = "password"></td></tr>
<tr>
<td><input type = "submit" value = "登录">
</td>
</tr>

(3).DAO类 Dao.java 里面实现了一个在数据库中查找匹配的数据项：

public class Dao {

private Connection conn;
private PreparedStatement pstm;
String sql ="";

//用户登录
public UserInfo login(String email, String password){
MyDB myDB =new MyDB();
Connection conn = null;
Statement stms = null;
ResultSet rs = null;
UserInfo userinfo = null;

try{
conn=MyDB.getConnection();
stmt = conn.createStatement();
System.out.println(email);
System.out.println(password);
String sql = "select * from tb_userinfo where email = '"+email+"' and password ='"+password+"'";
rs = stmt.executeQuery(sql);
while(rs.next()){
userinfo = new UserInfo();
userinfo.setEmail(rs.getString("email"));
userinfo.setPassword(rs.getString("password"));
}
}catch (Exception e){
e.printStackTrace();
} finally {
myDB.closeDB(stmt, conn,rs);
}
return userinfo;
}
}

(4). servlet类 LoginServlet 实现页面的跳转：

protected void doPost(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException {
response.setContentType("text/html");
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");

String email = request.getParameter("email");
String password = request.getParameter("password");

Dao dao = new Dao();
UserInfo userinfo = dao.login(email, password); //login方法 在数据库中查找与email password匹配的项
String message = "用户名或密码错误";

if (userinfo == null){
request.setAttribute("message",mssage);
request.getRequestDispatcher("login_error.jsp").forward(request, response);
}else{
request.getRequestDispatcher("homepage.jsp").forward(request, response);
}
public void doGet(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOExcepion{
doPost(request,response);
}
public void init() throws ServletException{
}
}

3 查询新闻表，将新闻数据传给JSP首页，JSP首页用EL表达式显示新闻标题列表：
3.1 利用实体类来返回数据库的查询结果，这样使程序的可读性更高。

3.2返回数据库中的新闻查询结果
public List<News> findNews() throws SQLException {
String sql = "Select * from news";
ResultSet rs = SqlHelper.executeQuery(sql);
List<News> newsList = new ArrayList<News>();
while (rs.next()) {
News news = new News();
news.setNewsId(rs.getInt("NewID"));
news.setTitle(rs.getString("Title"));
news.setNewsContent(rs.getString("NewsContent"));
news.setAuthor(rs.getString("Author"));
news.setNewsDate(rs.getDate("NewsDate"));

newsList.add(news);
}
return newsList;
}
3.3 编写Servlet，显示查询结果至jsp中
request.setCharacterEncoding("utf-8");
NewsService newsService=new NewsService();

try {
List<News> newsList=newsService.findNews();
request.setAttribute("News",newsList);
request.getRequestDispatcher("ShowNewsList.jsp").forward(request,response);
} catch (SQLException e) {
e.printStackTrace();
}

3.5 EL表达式显示新闻标题列表
<div class="table_con">
<table>
<tr class="tb_title">
<td width="10%">ID</td>
<td width="30%">标题</td>
<td width="20%">内容</td>
<td width="15%">作者</td>
<td width="10%">时间</td>
<td width="15%">操作</td>
</tr>
<c:forEach var="n" items="${News}" >
<tr>
<td width="10%">${n.newsId}</td>
<td width="30%">${n.title}</td>
<td width="20%">${n.newsContent} </td>
<td width="15%">${n.author}</td>
<td width="10%">${n.newsDate}</td>
<td width="15%">
<a href="editServlet?newid=${n.newsId}" class="bj_btn">编辑</a>
<a href="viewServlet?newid=${n.newsId}" class="sj_btn">查看</a>
<a href="deleteServlet?newid=${n.newsId}" class="del_btn">删除</a>
</td>
</tr>
</c:forEach>
</table>
</div>