Java Web学习交流网(模仿百度贴吧)一、实验目的
1、连接MySQL数据库
2、读取数据库到web页面
二、实验要求
1、设计一个贴吧使用户可以注册、提问、回答
三、实验步骤1、实验准备
a安装mysql
b安装navicat
c把MySQL的jar包引入MyEclipse下
2、功能结构图
3、设计数据库
a 定义三个表
user(uid、name)
question(qid、uid、title、content、time)
answer(aid、qid、uid、content、time)
b 通过e-r图分析三者之间的关系
c 数据库代码
CREATE DATABASE TB; USE TB; //创建表格 CREATE TABLE USER ( UID INT(10) NOT NULL AUTO_INCREMENT, NAME VARCHAR(20) NOT NULL, PASSWORD VARCHAR(10) NOT NULL, PRIMARY KEY(UID) )ENGINE=INNODB DEFAULT CHARSET=gb2312; CREATE TABLE QUESTION ( QID INT(10) NOT NULL AUTO_INCREMENT, UID INT(10), TITLE VARCHAR(20), CONTENT VARCHAR(100) NOT NULL, TIME VARCHAR(15) NOT NULL, FOREIGN KEY(UID) REFERENCES USER(UID), PRIMARY KEY(QID) )ENGINE=INNODB DEFAULT CHARSET=gb2312; CREATE TABLE ANSWER ( AID INT(10) NOT NULL AUTO_INCREMENT, QID INT(10), UID INT(10), CONTENT VARCHAR(200) NOT NULL, TIME VARCHAR(15) NOT NULL, FOREIGN KEY(QID) REFERENCES QUESTION(QID), FOREIGN KEY(UID) REFERENCES USER(UID), PRIMARY KEY(AID,QID) )ENGINE=INNODB DEFAULT CHARSET=gb2312; //插入数据 INSERT INTO USER(UID,NAME,PASSWORD) VALUES (1,'张三','111'), (2,'李四','222'), (3,'王五','333'); INSERT INTO QUESTION(QID,UID,TITLE,CONTENT,TIME) VALUES (1,1,'计算','1+1=?','2014-3-2'), (2,1,'天气','今天晴天吗?','2014-3-7'), (3,2,'日期','明天星期几?','2014-3-9'), (4,3,'温度','后天多少度?','2014-4-5'); INSERT INTO ANSWER(AID,QID,UID,CONTENT,TIME) VALUES (1,2,2,'今天晴天','2014-3-7'), (2,2,3,'今天晴天','2014-3-7'), (3,1,2,'1+1=2','2014-3-10'), (4,1,3,'1+1=2','2014-3-17'), (5,3,2,'今天星期二','2014-3-11'), (6,4,1,'后天20度','2014-4-5'); SELECT * FROM USER SELECT * FROM QUESTION SELECT * FROM ANSWER
4、设计java web页面
a 建立三个jsp页面(register.jsp login.jsp show.jsp question.jsp answer.jsp)
b 具体实现思路:
1)首先设计register.jsp注册页面,
2)当用户注册成功后直接进入login.jsp,
3)成功登录的进入index.jsp页面。index.jsp其主要功能是显示数据库中保存的问题,在“管理”一栏中有“查看”或者“浏览”(超链接)。
4)当点击"查看"时页面跳转到question. jsp页面(跳转页面时带有question的id参数),此页面中显示问题的标题、发布时间、具体内容和提问者用户信息,问题下面有一个查看回答的连接。
5)当点击回答按钮时页面跳转到answer.jsp(带着Question的id),answer.jsp页面中第一个任务是:显示指定的这个问题的回答次数,第二个任务是罗列回答的内容、回答者信息、回答时间;第三个任务是,在最下方设计添加一个回答的表单,其action=“addAnswer.jsp”。
尝试记着上次的输入数据)。
注意:同时每个页面都可以返回到主页。
(可参考课本项目6.3—图书管理系统)
c 实验结果:
1)输入用户名和密码(张三:111 李四:222 王五:333),如果输入正确会提示“登陆成功”,否则提示“注册用户”。(可参考index.jsp)
2)输入正确后跳转到show.jsp。点击“提出问题”时,用户可以添加自己的问题;点击“查看”时,用户可以查看当前问题
3)当点击“查看”时,可以查看问题的标题、时间、内容和提问者的信息。点击返回时可以返回到show.jsp界面,同时也可以点击查看回答信息
4)当点击查看回答信息时,可以查看回答的时间、内容、回答者。点击返回时可以返回到show.jsp界面,同时可点击回答
5)当点击回答时可以添加自己的回答信息。点击添加跳转到查看回答信息界面
6)当在show.jsp中点击提问问题时
d 主要代码:
index.jsp
<%@ page language="java" import="java.sql.*" pageEncoding="UTF-8"%><%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> <title>登录</title> </head> <body> <center> <h1>登录界面</h1> <form action="" method="post"> 用户名:<input type="text" name="name"><br> 密码:<input type="password" name="pwd"><br> <input type="submit" name="submit" value="登录"> <input type="submit" name="register" value="注册"> </form> <% request.setCharacterEncoding("utf-8"); String name=request.getParameter("name"); String pwd=request.getParameter("pwd"); String submit=request.getParameter("submit"); String register=request.getParameter("register"); int biaoji=0; Class.forName("com.mysql.jdbc.Driver"); Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/TB","root","123"); Statement stmt=con.createStatement(); String s="select * from user"; ResultSet rs=stmt.executeQuery(s); if(submit!=null&®ister==null) { while(rs.next()) { int uid=rs.getInt(1); if(rs.getString(2).equals(name)&&rs.getString(3).equals(pwd)) { session.setAttribute("sessionname",uid); out.println("<script language='javaScript'>alert('登陆成功,单击确定跳到主页!')</script>"); response.setHeader("refresh", "1;url=show.jsp"); biaoji=1; break; } } if(biaoji==0) { out.println("<script language='javaScript'>alert('没有此用户,单击确定注册新用户!')</script>"); } } if(register!=null&&submit==null){ session.setAttribute("sessionname", name); session.setAttribute("sessionpwd", pwd); String sql="insert into user(name) values('"+name+"')"; int i=stmt.executeUpdate(sql); if(i==1) { out.println("<script language='javaScript'>alert('注册成功,单击确定跳到主页!')</script>"); response.setHeader("refresh", "1;url=index.jsp"); } } %> </center> </body></html>
show.jsp
<%@ page language="java" import="java.sql.*" pageEncoding="UTF-8"%><%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> <title>My JSP 'show.jsp' starting page</title> </head> <body> <center> <caption><a href='addquestion.jsp'>提出问题</a></caption> <table align="center" width="30%" border=1> <tr><th>标题</th><th>管理</th></tr> <% Class.forName("com.mysql.jdbc.Driver"); Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/TB","root","123"); Statement stmt=con.createStatement(); String s="select * from question"; ResultSet rs=stmt.executeQuery(s); while(rs.next()) { int qid=rs.getInt(1); out.println("<tr><td>"+rs.getString(3)+"</td><td><a href='question.jsp?qid="+rs.getString(1)+"'>查看</a></td></tr>"); } rs.close(); stmt.close(); con.close(); %> </table> </center> </body></html>
question.jsp
<%@ page language="java" import="java.sql.*" pageEncoding="UTF-8"%><%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> </head> <body> <center> <% Class.forName("com.mysql.jdbc.Driver"); Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/TB","root","123"); Statement stmt=con.createStatement(); String qid=request.getParameter("qid"); String s="SELECT QID,NAME,TITLE,CONTENT,TIME FROM QUESTION,USER WHERE USER.UID=QUESTION.UID and qid="+qid; ResultSet rs=stmt.executeQuery(s); rs.next(); %> <form action=question.jsp method="post"> <table align="center" width="20%" border=1> <caption>查看问题信息</caption> <tr><td>标题:<%=rs.getString(3)%> 时间:<%=rs.getString(5)%></td></tr> <tr><td>内容:<input name="qcontent" type="text" value="<%=rs.getString(4)%>"></td></tr> <tr><td>提问者:<input name="quser" type="text" value="<%=rs.getString(2)%>"></td></tr> <tr><td><a href='show.jsp'>返回</a><a href='answer.jsp?qid=<%=rs.getString(1) %>'> 查看回答内容 </a></td></tr> </table> </form> <% rs.close(); stmt.close(); con.close(); %> </center> </body></html>
answer.jsp
<%@ page language="java" import="java.sql.*" pageEncoding="UTF-8"%><%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> </head> <body> <center> <table align="center" width="50%" border=1> <% Class.forName("com.mysql.jdbc.Driver"); Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/TB","root","123"); Statement stmt=con.createStatement(); String s="select name,qid,answer.content,answer.time from USER,answer where user.uid=answer.uid and qid="+request.getParameter("qid"); ResultSet rs=stmt.executeQuery(s); %> <form action=answer.jsp method="post"> <table align="center" width="20%" border=1> <caption>查看回答内容</caption> <%while(rs.next()) {%> <tr><td> 时间:<%=rs.getString(4)%></td></tr> <tr><td>内容:<input name="acontent" type="text" value="<%=rs.getString(3)%>"></td></tr> <tr><td>回答者:<input name="auser" type="text" value="<%=rs.getString(1)%>"></td></tr> <tr><td><a href='show.jsp'>返回</a><a href='addanswer.jsp?qid=<%=rs.getString(2)%>'> 回答 </a></td></tr> <%} %> </table> </form> <% rs.close(); stmt.close(); con.close(); %> </table> </center> </body></html>
addanswer.jsp
<%@ page language="java" import="java.sql.*" pageEncoding="UTF-8"%><html> <head> <title>添加回答</title> </head> <body> <% request.setCharacterEncoding("UTF-8"); String qid=request.getParameter("qid"); %> <form action="addanswer.jsp" method="post"> <table align="center" width="40%" border=1> <caption>添加回答内容</caption> <tr> <th>回答内容</th> <td> <textarea name="myanswer" row="5" cols="60"> </textarea> <input type="hidden" name="qid" value="<%=qid%>"> </td> </tr> <tr> <th colspan="2"> <input type="submit" name="submit" value="添加"> <input type="reset" value="重置"> </tr> </table> </form> <% String content=request.getParameter("myanswer"); //String time=request.getParameter("time"); String submit=request.getParameter("submit"); Class.forName("com.mysql.jdbc.Driver"); Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/TB","root","123"); Statement stmt=con.createStatement(); if(submit!=null&&!submit.equals("")) { String s="insert into answer(qid,uid,content,TIME) values("+qid+","+session.getAttribute("sessionname")+",'"+content+"','2014')"; int i=stmt.executeUpdate(s); if(i==1) { out.println("<script language='javascript'>alter('添加成功,单击确定跳转到回答页面');</script>"); response.setHeader("refresh", "1;url=answer.jsp?qid="+qid); } else{ out.println("<script language='javascript'>alter('添加失败,单击确定跳转到添加页面');</script>"); response.setHeader("refresh", "1;url=addanswer.jsp"); } } stmt.close(); con.close(); %> </body></html>
addquestion.jsp
<%@ page language="java" import="java.sql.*" pageEncoding="UTF-8"%><html> <head> <title>添加回答</title> </head> <body> <% request.setCharacterEncoding("UTF-8"); String qid=request.getParameter("qid"); %> <form action="addanswer.jsp" method="post"> <table align="center" width="40%" border=1> <caption>添加回答内容</caption> <tr> <th>回答内容</th> <td> <textarea name="myanswer" row="5" cols="60"> </textarea> <input type="hidden" name="qid" value="<%=qid%>"> </td> </tr> <tr> <th colspan="2"> <input type="submit" name="submit" value="添加"> <input type="reset" value="重置"> </tr> </table> </form> <% String content=request.getParameter("myanswer"); //String time=request.getParameter("time"); String submit=request.getParameter("submit"); Class.forName("com.mysql.jdbc.Driver"); Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/TB","root","123"); Statement stmt=con.createStatement(); if(submit!=null&&!submit.equals("")) { String s="insert into answer(qid,uid,content,TIME) values("+qid+","+session.getAttribute("sessionname")+",'"+content+"','2014')"; int i=stmt.executeUpdate(s); if(i==1) { out.println("<script language='javascript'>alter('添加成功,单击确定跳转到回答页面');</script>"); response.setHeader("refresh", "1;url=answer.jsp?qid="+qid); } else{ out.println("<script language='javascript'>alter('添加失败,单击确定跳转到添加页面');</script>"); response.setHeader("refresh", "1;url=addanswer.jsp"); } } stmt.close(); con.close(); %> </body></html>
旁观者的姓名永远爬不到比赛的计分板上。