第十一周作业

1.建库，建表2个
用户表（id,用户名，密码）
邮件表（id,发件人，收件人，标题，内容，发送时间，状态）

2.建model层
entity,dao包

3.登陆，注册，登陆后显示全部邮件

 

package dao;

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

public class BaseDao {
    // 获取连接
    protected Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            // 2.建立连接
            conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/test", "root", "123456");
        } 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 java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import entity.Msg;

public class MsgDao extends BaseDao {
    public List<Msg> listAll(String username) throws SQLException{
        List<Msg> list =new ArrayList<Msg>();
        Connection conn =getConnection();
        String sql="select * from msg where username=?";
        PreparedStatement pred =null;

        ResultSet rest=null;
        try {
            pred=conn.prepareStatement(sql);
            pred.setString(1, username);
            rest=pred.executeQuery();
            while (rest.next()) {
                Msg msg=new Msg();
                msg.setMsgid(rest.getInt("msgid"));
                msg.setUsername(rest.getString("username"));
                msg.setTitle(rest.getString("title"));
                msg.setMsgcontent(rest.getString("msgcontent"));
                msg.setState(rest.getInt("state"));
                msg.setSendto(rest.getString("sendto"));
                msg.setMsg_create_date(rest.getDate("msg_create_date"));
                list.add(msg);
            }
        } catch (Exception e) {
            // TODO: handle exception
        }
        finally{
            closeAll(conn, pred, rest);
        }
        return list;
    }

}

package dao;

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

public class UsersDao extends BaseDao {
    public boolean login(String uname, String upwd) throws SQLException {
        boolean f = false;
        Connection conn = getConnection();
        PreparedStatement pred = null;
        ResultSet rest = null;
        String sql = "select * from users where uname=? and upwd=?";
        try {
            pred = conn.prepareStatement(sql);
            pred.setString(1, uname);
            pred.setString(2, upwd);
            rest = pred.executeQuery();
            while (rest.next()) {
                f = true;
            }
        } catch (Exception e) {
            // TODO: handle exception
        } finally {
            closeAll(conn, pred, rest);
        }
        return f;
    }

    public boolean id(Integer id) throws SQLException {
        boolean f = false;
        Connection conn = getConnection();
        PreparedStatement pred = null;
        ResultSet rest = null;
        String sql = "select * from users where id=?";
        try {
            pred = conn.prepareStatement(sql);
            pred.setInt(1, id);
            rest = pred.executeQuery();
            while (rest.next()) {
                f = true;
            }
        } catch (Exception e) {
            // TODO: handle exception
        } finally {
            closeAll(conn, pred, rest);
        }
        return f;
    }

    public void zhuce(Integer id, String uname, String upwd) {
        Connection conn = getConnection();
        PreparedStatement pred = null;
        try {
            String sql = "insert into users(id,uname,upwd) values(?,?,?)";
            pred = conn.prepareStatement(sql);
            pred.setInt(1, id);
            pred.setString(2, uname);
            pred.setString(3, upwd);
            pred.executeUpdate();

        } catch (Exception e) {
            // TODO: handle exception
        } finally {
            closeAll(conn, pred, null);
        }
    }

}

package entity;

import java.util.Date;

public class Msg {
    private Integer msgid;
    private String username;
    private String title;
    private String msgcontent;
    private Integer state;
    private String sendto;
    private Date msg_create_date;

    public Integer getMsgid() {
        return msgid;
    }
    public void setMsgid(Integer msgid) {
        this.msgid = msgid;
    }
    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public String getTitle() {
        return title;
    }
    public void setTitle(String title) {
        this.title = title;
    }
    public String getMsgcontent() {
        return msgcontent;
    }
    public void setMsgcontent(String msgcontent) {
        this.msgcontent = msgcontent;
    }
    public Integer getState() {
        return state;
    }
    public void setState(Integer state) {
        this.state = state;
    }
    public String getSendto() {
        return sendto;
    }
    public void setSendto(String sendto) {
        this.sendto = sendto;
    }
    public Date getMsg_create_date() {
        return msg_create_date;
    }
    public void setMsg_create_date(Date msg_create_date) {
        this.msg_create_date = msg_create_date;
    }

}

package entity;

public class Users {
    private Integer id;
    private String uname;
    private String upwd;
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getUname() {
        return uname;
    }
    public void setUname(String uname) {
        this.uname = uname;
    }
    public String getUpwd() {
        return upwd;
    }
    public void setUpwd(String upwd) {
        this.upwd = upwd;
    }


}

 

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%
    request.setCharacterEncoding("utf-8");
    response.setCharacterEncoding("utf-8");
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>My JSP 'zhuce.jsp' starting page</title>
</head>
<body>
    <script type="text/javascript">
        function dozhuce() {
            if (loginform2.id.value == "") {
                alert("没有输入id");
                return;
            }
            if (loginform2.uname.value == "") {
                alert("没有输入用户名");
                return;
            }
            if (loginform2.upwd.value == "") {
                alert("没有输入密码");
                return;
            }
            if (loginform2.upwd2.value == "") {
                alert("没有确认密码");
                return;
            }
            loginform2.submit();
        }
    </script>
    <form action="dozhuce.jsp" name="loginform2" method="post">
        id: <input type="number" name="id" /><br>
         用户名:<input type="text" name="uname" /> <br>
         密码:<input type="password" name="upwd" /> <br>
         确认密码:<input type="password" name="upwd2" /><br>
        <input type="button" value="注册" onclick="dozhuce()" />
    </form>
</body>
</html>

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@page import="dao.UsersDao"%>
<%
    request.setCharacterEncoding("utf-8");
    response.setCharacterEncoding("utf-8");
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>My JSP 'dozhuce.jsp' starting page</title>
</head>
<body>
    <%
        String uid = request.getParameter("id");
        Integer id = Integer.parseInt(uid);
        String uname = request.getParameter("uname");
        String upwd = request.getParameter("upwd");
        String upwd2 = request.getParameter("upwd2");
        UsersDao usersDao = new UsersDao();
        if (upwd.equals(upwd2)) {
            if (usersDao.id(id)) {
                out.print("已经有这个id了，即将跳回注册页.....");
                response.setHeader("refresh", "5;url=zhuce.jsp");
            } else {
                usersDao.zhuce(id,uname, upwd);
                out.print("注册成功，即将跳回登录页.....");
                response.setHeader("refresh", "5;url=login.jsp");
            }
        } else {
            out.print("两次密码不一致，即将跳回注册页.....");
            response.setHeader("refresh", "5;url=zhuce.jsp");
        }
    %>
</body>
</html>

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@page import="sun.security.jgss.LoginConfigImpl"%>
<%@page import="dao.UsersDao"%>
<%
    request.setCharacterEncoding("utf-8");
    response.setCharacterEncoding("utf-8");
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>My JSP 'dologin.jsp' starting page</title>
</head>

<body>
    <%
        String uname = request.getParameter("uname");
        String upwd = request.getParameter("upwd");
        UsersDao ud = new UsersDao();
        if (ud.login(uname, upwd)) {
            session.setAttribute("uname", uname);
            request.getRequestDispatcher("index.jsp").forward(request,
                    response);
        } else {
            out.print("登录失败,5s后跳转登录页面");
            response.setHeader("refresh", "5;url=login.jsp");
        }
    %>
</body>
</html>

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>My JSP 'login.jsp' starting page</title>
</head>

<body>
    <script type="text/javascript">
        function dologin() {
            if (loginform.uname.value == "") {
                alert("没有输入用户名");
                return;
            }
            if (loginform.upwd.value == "") {
                alert("没有输入密码");
                return;
            }
            loginform.submit();
        }
    </script>
    <form action="dologin.jsp" name="loginform" method="post">
        用户名:<input type="text"  name="uname" /> <br>
        密码:<input type="password"  name="upwd" /> <br>
        <input type="button" value="登录" onclick="dologin()" />
        <a href="zhuce.jsp">没有账号?立即注册</a>
    </form>
</body>
</html>

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@page import="java.util.List"%>
<%@page import="dao.MsgDao"%>
<%@page import="entity.Msg"%>
<%@page import="sun.security.jgss.LoginConfigImpl"%>
<%@page import="dao.UsersDao"%>
<%
    request.setCharacterEncoding("utf-8");
    response.setCharacterEncoding("utf-8");
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>My JSP 'index.jsp' starting page</title>
</head>
<body>
    <%
        String uname = (String) session.getAttribute("uname");
        if (uname == null) {
            out.print("您未登录，即将跳回登陆页.....");
            response.setHeader("refresh", "5;url=login.jsp");
        } else {
    %>
    欢迎你<%=uname%>



    <%
        Msg msg = new Msg();
            MsgDao msgDao = new MsgDao();
            List<Msg> list = new ArrayList<Msg>();
            list = msgDao.listAll(uname);
    %>
    邮件列表:
    <table width="600px" border="2px solid blue">
        <tr>
            <th>id</th>
            <th>发件人</th>
            <th>标题</th>
            <th>内容</th>
            <th>已读未读状态</th>
            <th>收件人</th>
            <th>发送时间</th>
        </tr>
        <%
            if (list.size() == 0) {
                    out.print("你没有邮件");
                } else {
                    for (Msg msg1 : list) {
        %>
        <tr>
            <td>
                <%
                    out.print(msg1.getMsgid());
                %>
            </td>
            <td>
                <%
                    out.print(msg1.getUsername());
                %>
            </td>
            <td>
                <%
                    out.print(msg1.getTitle());
                %>
            </td>
            <td>
                <%
                    out.print(msg1.getMsgcontent());
                %>
            </td>
            <td>
                <%
                    int state = msg1.getState();
                                if (state == 1) {
                %> <img src="image/yidu.png" /> <%
     ;
                 } else {
 %> <img src="image/weidu.png" /> <%
     ;
                 }
 %>
            </td>
            <td>
                <%
                    out.print(msg1.getSendto());
                %>
            </td>
            <td>
                <%
                    out.print(msg1.getMsg_create_date());
                %>
            </td>
        </tr>
        <%
            }
                }
            }
        %>
    </table>
</body>
</html>