第十二周作业

package dao;

import pojo.Msg;
import util.DBUtil;

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

public class MsgDao extends BaseDao{
    public List<Msg> getMailByReceiver(String name) {
        List<Msg> list = new ArrayList<Msg>();
        Connection conn = getConnection();
        String sql = "select * from mail where addressee=?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1, name);
            rs = ps.executeQuery();
            while (rs.next()) {
                Msg m = new Msg();
                int eid = rs.getInt("eid");
                String sender = rs.getString("sender");
                String addressee = rs.getString("addressee");
                String title = rs.getString("title");
                String content = rs.getString("content");
                Date sending_time = rs.getDate("sending_time");
                int state = rs.getInt("state");
                m.setEid(eid);
                m.setSender(sender);
                m.setAddressee(addressee);
                m.setTitle(title);
                m.setContent(content);
                m.setSending_time(sending_time);
                m.setState(state);
                list.add(m);
            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            closeAll(conn, ps, rs);
        }
        return list;
    }

    public void addMsg(Msg m) {
        Connection conn = getConnection();
        String sql = "insert into mail(sender,addressee,title,content,sending_time,state) values(?,?,?,?,?,?)";
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1, m.getSender());
            ps.setString(2, m.getAddressee());
            ps.setString(3, m.getTitle());
            ps.setString(4, m.getContent());
            ps.setDate(5, new java.sql.Date(new Date().getTime()));
            ps.setInt(6, m.getState());
            ps.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            closeAll(conn, ps, null);
        }

    }

    public void delMail(int id) {
        Connection conn = getConnection();
        String sql = "delete from mail where eid=?";
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            ps.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            closeAll(conn, ps, null);
        }

    }

    public Msg getMailById(int id) {
        Connection conn = getConnection();
        String sql = "select * from mail where eid=?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        Msg m = null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            while (rs.next()) {
                m = new Msg();
                int eid = rs.getInt("eid");
                String sender = rs.getString("sender");
                String addressee = rs.getString("addressee");
                String title = rs.getString("title");
                String content = rs.getString("content");
                Date sending_time = rs.getDate("sending_time");

                m.setEid(eid);
                m.setSender(sender);
                m.setAddressee(addressee);
                m.setTitle(title);
                m.setContent(content);
                m.setSending_time(sending_time);
                m.setState(readMail(id) == 1 ? 0 : 1);
            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            closeAll(conn, ps, rs);
        }
        return m;
    }

    public int readMail(int id) {
        int n = 0;
        Connection conn = getConnection();
        String sql = "update mail set state='0' where eid=?";
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            n = ps.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            closeAll(conn, ps, null);
        }
        return n;
    }
}

package dao;

import java.sql.*;

public class BaseDao {
    //获取连接
    protected Connection getConnection(){
        Connection conn=null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            // 2.建立连接
            conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost/mysqldb?useSSL=true&useUnicode=true&characterEncoding=utf8", "root", "root");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }




    //关闭连接
    protected void closeAll(Connection con, PreparedStatement ps, ResultSet rs){
        try {
            if(rs != null)
                rs.close();
            if(ps != null)
                ps.close();
            if(con != null)
                con.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

 

package dao;

import util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class UserDao extends BaseDao{
    public boolean login(String name,String pwd){
        boolean f=false;
        Connection conn= DBUtil.getCon();
        String sql="select * from users where uname=? and upwd=?";
        PreparedStatement ps;
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1, name);
            ps.setString(2, pwd);
            ResultSet rs=ps.executeQuery();
            if(rs.next())
                f=true;
            closeAll(conn, ps, rs);


        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return f;
    }
    public int reg(String uname, String upwd) {
        Connection conn = getConnection();
        int i=0;
        PreparedStatement ps = null;
        try {
            String sql = "insert into users(uname,upwd) values(?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, uname);
            ps.setString(2, upwd);
            i=ps.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            closeAll(conn, ps, null);
        }
        return i;
    }
}

 

<%@ page language="java" contentType="text/html; charset=utf-8"
         pageEncoding="utf-8"%>
<%@ page import="dao.MsgDao"%>
<%@ page import="pojo.Msg"%>
<%@ page import="java.util.List"%>
<%@ page import="java.util.ArrayList"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    <title>Insert title here</title>
    <style>
        table {
            border-collapse: collapse;
            width:400px;
        }

        table, table tr th, table tr td {
            border: 1px solid #000000;
        }
        table td{
            text-align:center;
        }
        img{
            width:30px;

        }
        a{
            text-decoration: none;
        }
        a:link{
            color:black;
        }
    </style>
</head>
<body>
<%String uname=(String)session.getAttribute("uname"); %>
首页！！欢迎你<%=uname %>
<table border="1">
    <tr>
        <td>发件人</td><td>主题</td><td>状态</td><td>时间</td><td>操作</td><td>操作</td>

        <%
            MsgDao md = new MsgDao();
            List<Msg> list = md.getMailByReceiver(uname);

            for (int i = 0; i < list.size(); i++) {
                out.print("<tr><td>"+ list.get(i).getSender() + "</td>");
                out.print("<td><a href=\"detail.jsp?id="+list.get(i).getEid() +"\">" + list.get(i).getTitle() + "</a></td>");
                if (list.get(i).getState() == 1) {
                    out.print("<td><img src=\"img/wd.png\"/></td>");
                } else {
                    out.print("<td><img src=\"img/yd.png\"/></td>");
                }

                out.print("<td>" + list.get(i).getSending_time() + "</td>");
                out.print("<td><a href=\"delete.jsp?id="+list.get(i).getEid()+"\">删除</a></td>");
                out.print("<td><a href=\"write.jsp?id="+list.get(i).getEid()+"&reply="+list.get(i).getSender()+"\">回复</a></td></tr>");
            }
        %>


    </tr>


</table>
</body>
</html>

 

<%@ page language="java" contentType="text/html; charset=utf-8"
         pageEncoding="utf-8"%>
<%@ page import="dao.MsgDao"%>
<%@ page import="pojo.Msg"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    <title>Insert title here</title>
</head>
<body>
<%
    int id=Integer.parseInt(request.getParameter("id"));
    MsgDao md = new MsgDao();
    md.readMail(id);
%>
<form action="dowrite.jsp" method="post">

    收件人:<input type="text" name="addressee"  value="<%=request.getParameter("reply")%>"><br>
    主题: <input  type="text" name="title" ><br>
    内容  <textarea rows="6" cols="20" name="content"></textarea>
    <br>
    <input type="submit" value="发送">
</form>
</body>
</html>

 

<%@ page language="java" contentType="text/html; charset=utf-8"
         pageEncoding="utf-8"%>
<%@ page import="pojo.Msg"%>
<%@ page import="dao.MsgDao"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    <title>Insert title here</title>
</head>
<body>
<%
    request.setCharacterEncoding("utf-8");
    String uname=(String)session.getAttribute("uname");
    String addressee=request.getParameter("addressee");
    String title=request.getParameter("title");
    String content=request.getParameter("content");

    Msg m=new Msg();
    m.setContent(content);
    m.setSender(uname);
    m.setAddressee(addressee);
    m.setTitle(title);

    MsgDao md=new MsgDao();
    md.addMsg(m);

    out.print("发送成功.....");
    response.setHeader("refresh", "5;url=main.jsp");


%>
</body>
</html>

 

<%@ page language="java" contentType="text/html; charset=utf-8"
         pageEncoding="utf-8"%>
<%@ page import="dao.MsgDao"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    <title>Insert title here</title>
</head>
<body>
<%

    int id=Integer.parseInt(request.getParameter("id"));
    MsgDao md=new MsgDao();
    md.delMail(id);
    response.sendRedirect("main.jsp");

%>
</body>
</html>

 

<%@ page language="java" contentType="text/html; charset=utf-8"
         pageEncoding="utf-8"%>
<%@ page import="dao.MsgDao"%>
<%@ page import="pojo.Msg"%>
<%@ page import="java.util.List"%>
<%@ page import="java.util.ArrayList"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    <title>Insert title here</title>
    <style>
        table {
            border-collapse: collapse;
            width:300px;
        }

        table, table tr th, table tr td {
            border: 1px solid #000000;
        }
        table td{
            text-align:center;
        }
        img{
            width:30px;

        }
        a{
            text-decoration: none;
        }
        a:link{
            color:black;
        }
    </style>
</head>
<body>
<table border="1">
    <tr>
        <td>发件人</td><td>主题</td><td>状态</td><td>时间</td><td>操作</td><td>操作</td>

        <%
            int id=Integer.parseInt(request.getParameter("id"));
            MsgDao md = new MsgDao();
            Msg m = md.getMailById(id);

            out.print("<tr><td>"+ m.getSender() + "</td>");
            out.print("<td><a href=\"detail.jsp?id="+m.getEid() +"\">" + m.getTitle() + "</a></td>");
            if (m.getState() == 1) {
                out.print("<td><img src=\"img/wd.png\"/></td>");
            } else {
                out.print("<td><img src=\"img/yd.png\"/></td>");
            }
            out.print("<td>" + m.getSending_time() + "</td>");
            out.print("<td><a href=\"delete.jsp?id="+m.getEid()+"\">删除</a></td>");
            out.print("<td><a href=\"write.jsp?reply="+m.getEid()+"\">回复</a></td></tr>");
        %>
        <form action="main.jsp" method="post">
            <input type="submit" value="返回主页">
        </form>



    </tr>


</table>
</body>
</html>