十二周作业
1.实现昨天讲过的 删除 回复邮件
2.实现阅读邮件功能：在main.jsp中点击任意邮件的标题，进入到detail.jsp显示邮件详情，
包括发件人，主题，内容，时间。同时需要把邮件状态修改为已读。
1 package com.sk.dao;
2
3 import java.sql.Connection;
4 import java.sql.DriverManager;
5 import java.sql.PreparedStatement;
6 import java.sql.ResultSet;
7 import java.sql.SQLException;
8
9 public class BaseDao {
10 public Connection conn = null;
11 PreparedStatement ps = null;
12 ResultSet rs = null;
13
14 //获取连接
15 protected Connection getConnection(){
16
17 try {
18 //加载驱动
19 Class.forName("com.mysql.jdbc.Driver");
20 //建立连接
21 conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","123456");
22 } catch (Exception e) {
23 // TODO Auto-generated catch block
24 e.printStackTrace();
25 }
26 return conn;
27 }
28
29 //关闭连接
30 protected void closeAll(Connection con, PreparedStatement ps, ResultSet rs){
31 try{
32 if(rs != null)
33 rs.close();
34 if(ps != null)
35 ps.close();
36 if(con != null)
37 con.close();
38 }catch (SQLException e) {
39 e.printStackTrace();
40 }
41 }
42 }
1 package com.sk.dao;
2
3 import java.sql.Connection;
4 import java.sql.PreparedStatement;
5 import java.sql.ResultSet;
6 import java.sql.SQLException;
7
8 import com.sk.entity.Users;
9
10 public class UsersDao extends BaseDao {
11 //关于用户的增删改查
12
13 //登录
14 public boolean login(String uname,String upwd){
15 boolean f = false;
16 Connection conn = getConnection();
17 String sql = "select * from users where uname=? and upwd=?";
18 PreparedStatement ps = null;
19 ResultSet rs = null;
20 try {
21 ps = conn.prepareStatement(sql);
22 ps.setString(1, uname);
23 ps.setString(2, upwd);
24 rs = ps.executeQuery();
25 if(rs.next()) //查到结果
26 f = true;
27 } catch (SQLException e) {
28 // TODO Auto-generated catch block
29 e.printStackTrace();
30 }finally{
31 closeAll(conn,ps,rs);
32 }
33 return f;
34 }
35 //注册
36 public int reg(Users u){
37 int i = 0;
38 Connection conn = getConnection();
39 PreparedStatement ps = null;
40 String sql = "insert into users(uname,upwd) values (?,?)";
41 try {
42 ps = conn.prepareStatement(sql);
43 ps.setString(1, u.getUname());
44 ps.setString(2, u.getUpwd());
45 i = ps.executeUpdate();
46 } catch (SQLException e) {
47 // TODO Auto-generated catch block
48 e.printStackTrace();
49 }finally{
50 closeAll(conn, ps, null);
51 }
52 return i;
53 }
54 }
1 package com.sk.dao;
2
3 import java.sql.Connection;
4 import java.sql.PreparedStatement;
5 import java.sql.ResultSet;
6 import java.sql.SQLException;
7 import java.util.ArrayList;
8 import java.util.Date;
9 import java.util.List;
10
11 import com.sk.entity.Msg;
12
13 public class MsgDao extends BaseDao {
14 //关于邮件的增删改查
15 public static void main(String[] args) {
16 MsgDao md = new MsgDao();
17 Msg m= md.details(11);
18
19 System.out.println(m.getSendto());
20 }
21 //添加邮件(写邮件、回复邮件都调用，邮件状态为未读=0，时间为系统时间)
22 public void addMsg(Msg m){
23 Connection conn = getConnection();
24 String sql = "insert into msg(username,title,msgcontent,state,sendto,msg_create_date) value(?,?,?,0,?,?)";
25 PreparedStatement ps = null;
26 try {
27 ps = conn.prepareStatement(sql);
28 ps.setString(1, m.getUsername());
29 ps.setString(2, m.getTitle());
30 ps.setString(3, m.getMsgcontent());
31 ps.setString(4, m.getSendto());
32 ps.setDate(5, new java.sql.Date(new Date().getTime()));//获取系统当前时间
33 ps.executeUpdate();
34 } catch (SQLException e) {
35 // TODO Auto-generated catch block
36 e.printStackTrace();
37 }finally{
38 closeAll(conn, ps, null);
39 }
40 }
41
42 //删除邮件(根据id)
43 public void delMsg(int id){
44 Connection con = getConnection();
45 String sql = "delete from msg where msgid=" + id;
46 PreparedStatement ps = null;
47 try {
48 ps = con.prepareStatement(sql);
49 ps.executeUpdate();
50 } catch (SQLException e) {
51 // TODO Auto-generated catch block
52 e.printStackTrace();
53 }finally{
54 closeAll(con, ps, null);
55 }
56 }
57 //查看邮件详情 修改邮件状态(根据id 已读 = 1)
58 public void updateState(int msgid){
59 Connection con = getConnection();
60 String sql = "update msg set state=1 where msgid=?";
61 PreparedStatement ps = null;
62 try {
63 ps = con.prepareStatement(sql);
64 ps.setInt(1, msgid);
65 ps.executeUpdate();
66 } catch (SQLException e) {
67 // TODO Auto-generated catch block
68 e.printStackTrace();
69 }finally{
70 closeAll(con, ps, null);
71 }
72 }
73 public Msg details(int msgid){
74 Msg m = new Msg();
75 Connection con = getConnection();
76 String sql = "select * from msg where msgid=?";
77 PreparedStatement ps = null;
78 ResultSet rs = null;
79 try {
80 ps = con.prepareStatement(sql);
81 ps.setInt(1, msgid);
82 rs = ps.executeQuery();
83 while(rs.next()){
84 m.setMsgid(rs.getInt(1));
85 m.setUsername(rs.getString(2));
86 m.setTitle(rs.getString(3));
87 m.setMsgcontent(rs.getString(4));
88 m.setState(rs.getInt(5));
89 m.setSendto(rs.getString(6));
90 m.setMsg_create_date(rs.getDate(7));
91 }
92 } catch (SQLException e) {
93 // TODO Auto-generated catch block
94 e.printStackTrace();
95 }finally{
96 closeAll(con, ps, rs);
97 }
98 return m;
99 }
100 //查看邮件(根据 收件人username)
101 public List<Msg> getMailByReceiver(String uname){
102 List<Msg> list = new ArrayList<Msg>();
103 Connection con = getConnection();
104 String sql = "select * from msg where username=?";
105 PreparedStatement ps = null;
106 ResultSet rs = null;
107 try {
108 ps = con.prepareStatement(sql);
109 ps.setString(1, uname);
110 rs = ps.executeQuery();
111 while(rs.next()){
112 //每读取一行，创建一个msg对象，对象放在list中
113 Msg m = new Msg();
114 m.setMsgid(rs.getInt(1));
115 m.setUsername(rs.getString(2));
116 m.setTitle(rs.getString(3));
117 m.setMsgcontent(rs.getString(4));
118 m.setState(rs.getInt(5));
119 m.setSendto(rs.getString(6));
120 m.setMsg_create_date(rs.getDate(7));
121 list.add(m);
122 }
123 } catch (SQLException e) {
124 // TODO Auto-generated catch block
125 e.printStackTrace();
126 }finally{
127 closeAll(con, ps, rs);
128 }
129
130 return list;
131 }
132
133 //查看未读邮件(根据 收件人username)
134 public List<Msg> getUnReadMailByReceiver(String uname){
135 List<Msg> list = new ArrayList<Msg>();
136 Connection con = getConnection();
137 String sql = "select * from msg where username=? and state=0";
138 PreparedStatement ps = null;
139 ResultSet rs = null;
140 try {
141 ps = con.prepareStatement(sql);
142 ps.setString(1, uname);
143 rs = ps.executeQuery();
144 while(rs.next()){
145 //每读取一行，创建一个msg对象，对象放在list中
146 Msg m = new Msg();
147 m.setMsgid(rs.getInt(1));
148 m.setUsername(rs.getString(2));
149 m.setTitle(rs.getString(3));
150 m.setMsgcontent(rs.getString(4));
151 m.setState(rs.getInt(5));
152 m.setSendto(rs.getString(6));
153 m.setMsg_create_date(rs.getDate(7));
154 list.add(m);
155 }
156 } catch (SQLException e) {
157 // TODO Auto-generated catch block
158 e.printStackTrace();
159 }finally{
160 closeAll(con, ps, rs);
161 }
162
163 return list;
164 }
165 }
1 package com.sk.entity;
2
3 public class Users {
4 private int id;
5 private String uname;
6 private String upwd;
7
8
9 public Users() {
10 super();
11 }
12 public Users(String uname, String upwd) {
13 super();
14 this.uname = uname;
15 this.upwd = upwd;
16 }
17 public int getId() {
18 return id;
19 }
20 public void setId(int id) {
21 this.id = id;
22 }
23 public String getUname() {
24 return uname;
25 }
26 public void setUname(String uname) {
27 this.uname = uname;
28 }
29 public String getUpwd() {
30 return upwd;
31 }
32 public void setUpwd(String upwd) {
33 this.upwd = upwd;
34 }
35
36
37 }
1 package com.sk.entity;
2
3 import java.util.Date;
4
5 public class Msg {
6 private int msgid;
7 private String username;
8 private String title;
9 private String msgcontent;
10 private int state;
11 private String sendto;
12 private Date msg_create_date;
13
14 public int getMsgid() {
15 return msgid;
16 }
17 public void setMsgid(int msgid) {
18 this.msgid = msgid;
19 }
20 public String getUsername() {
21 return username;
22 }
23 public void setUsername(String username) {
24 this.username = username;
25 }
26 public String getTitle() {
27 return title;
28 }
29 public void setTitle(String title) {
30 this.title = title;
31 }
32 public String getMsgcontent() {
33 return msgcontent;
34 }
35 public void setMsgcontent(String msgcontent) {
36 this.msgcontent = msgcontent;
37 }
38 public int getState() {
39 return state;
40 }
41 public void setState(int state) {
42 this.state = state;
43 }
44 public String getSendto() {
45 return sendto;
46 }
47 public void setSendto(String sendto) {
48 this.sendto = sendto;
49 }
50 public Date getMsg_create_date() {
51 return msg_create_date;
52 }
53 public void setMsg_create_date(Date msg_create_date) {
54 this.msg_create_date = msg_create_date;
55 }
56
57 }
1 <%@page import="com.sk.dao.MsgDao"%>
2 <%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
3 <%
4 request.setCharacterEncoding("UTF-8");
5
6 int id = Integer.parseInt(request.getParameter("id"));
7 MsgDao md = new MsgDao();
8 md.delMsg(id);
9
10 response.sendRedirect("main.jsp");
11 %
1 <%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
2 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
3 <html>
4 <head>
5 <title>send</title>
6 </head>
7 <body>
8 <div align="center">
9 <h3>编写你要发送的邮件</h3>
10 <h3><a href="main.jsp" >返回上一页</a></h3>
11 <form action="dosend.jsp" method="post">
12 <table>
13 <tr>
14 <td>收件人：</td>
15 <td><input type="text" name="username" value="<%=request.getParameter("reply")%>"></td>
16 </tr>
17 <tr>
18 <td>标题：</td>
19 <td><input type="text" name="title"></td>
20 </tr>
21 <tr>
22 <td>内容：</td>
23 <td><textarea rows="6" cols="18" name="msgcontent"></textarea></td>
24 </tr>
25 </table>
26 <table>
27 <tr>
28 <td><input type="submit" value="提交"> </td>
29 <td> </td>
30 <td><input type="reset" value="重置"></td>
31 </tr>
32 </table>
33 </form>
34 </div>
35 </body>
36 </html>
1 <%@page import="com.sk.dao.MsgDao"%>
2 <%@page import="com.sk.entity.Msg"%>
3 <%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
4 <%
5 request.setCharacterEncoding("UTF-8");
6
7 String uname = (String)session.getAttribute("uname");//发件人在session中获取
8 String title = request.getParameter("title");
9 String username = request.getParameter("username");
10 String msgcontent = request.getParameter("msgcontent");
11
12 Msg m = new Msg();
13 m.setTitle(title);
14 m.setMsgcontent(msgcontent);
15 m.setSendto(uname);
16 m.setUsername(username);
17
18 MsgDao md = new MsgDao();
19 md.addMsg(m);
20
21 out.print("发送成功，即将跳回首页......");
22 response.setHeader("refresh", "3;url=main.jsp");
23
24 %>
1 <%@page import="com.sk.entity.Msg"%>
2 <%@page import="com.sk.dao.MsgDao"%>
3 <%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
4
5
6 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
7 <html>
8 <head>
9 <title>邮件详情</title>
10 </head>
11 <body>
12 <div align="center">
13 <%
14 int msgid = Integer.parseInt(request.getParameter("id"));
15 MsgDao md = new MsgDao();
16 md.updateState(msgid);
17 Msg m = new Msg();
18 m = md.details(msgid);
19 %>
20 <h3>邮件详情</h3>
21 <h3><a href="main.jsp">返回上一页</a></h3>
22 <table border="1">
23 <tr>
24 <th>发件人</th>
25 <th>标题</th>
26 <th>时间</th>
27 <th>内容</th>
28 <th colspan="2">操作</th>
29 </tr>
30 <tr>
31 <td><%=m.getSendto()%></td>
32 <td><%=m.getTitle()%></td>
33 <td><%=m.getMsg_create_date()%></td>
34 <td><%=m.getMsgcontent()%></td>
35 <td><a href="send.jsp?reply=<%=m.getSendto()%>">回复邮件</a></td>
36 <td><a href="del.jsp?id=<%=m.getMsgid()%>">删除邮件</a></td>
37 </tr>
38 </table>
39 </div>
40 </body>
41 </html>
1 <%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
2 <%
3 session.invalidate();
4 out.print("退出成功，即将跳回登录页......");
5 response.setHeader("refresh", "1;url=login.jsp");
6 %>
