package com.syxx.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.syxx.entity.Msg;
import com.syxx.util.JDBCUtils;
public class MsgDao {
//1 提供添加方法
public boolean insert(Msg msg) {
Connection con = null;
Statement stmt = null;
try {
//1创建连接对象
con = JDBCUtils.getCon();
//2 获取执行SQL语句的对象
stmt = con.createStatement();
//3执行sql语句
java.util.Date birthday = msg.getMsg_create_date();
String sqlBirthDay = String.format("%tF", birthday);
String sql = "insert into msg(username,title,msgcontent,state,sendto,msg_create_date)"+"values('"
+msg.getUsername()+"','"
+msg.getTitle()+"','"
+msg.getMsgcontent()+"','"
+msg.getState()+"','"
+msg.getSendto()+"','"
+sqlBirthDay+"'"
+")";
//System.out.println(sql);
int row = stmt.executeUpdate(sql);
if(row>0) {
return true;
}
}catch(Exception e){
e.printStackTrace();
}finally {
JDBCUtils.release(null, stmt, con);
}
return false;
}
//2根据name查询信息
public List<Msg> findMsgUsername(String username) {
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
//1创建连接对象
con = JDBCUtils.getCon();
//2 获取执行SQL语句的对象
String sql = "select * from msg where username =?";
stmt = con.prepareStatement(sql);
//3执行sql语句
stmt.setString(1,username);
//System.out.println(sql);
rs = stmt.executeQuery();
//遍历 rs
List<Msg> list = new ArrayList<Msg>();
while(rs.next()) {
//一行数据对应一个对象 获取每一行数据，就设置一个user对象
Msg msg = new Msg();
msg.setMsgid(rs.getInt("msgid"));
msg.setUsername(rs.getString("username"));
msg.setTitle(rs.getString("title"));
msg.setMsgcontent(rs.getString("msgcontent"));
msg.setState(rs.getInt("state"));
msg.setSendto(rs.getString("sendto"));
java.sql.Date msgDate = rs.getDate("msg_create_date");
msg.setMsg_create_date(msgDate);
list.add(msg);
}
return list;
}catch(Exception e) {
throw new RuntimeException(e);
}finally {
JDBCUtils.release(rs, stmt, con);
}
}
//3、根据id来删除Msg
public boolean delectMsg(int id) {
Connection con = null;
PreparedStatement stmt = null;
try {
//1创建连接对象
con = JDBCUtils.getCon();
//2 获取执行SQL语句的对象
String sql = "delete from msg where msgid =?";
stmt = con.prepareStatement(sql);
stmt.setInt(1,id);
//3执行sql语句
int row = stmt.executeUpdate();
if(row>0) {
return true;
}
}catch(Exception e) {
throw new RuntimeException(e);
}finally {
JDBCUtils.release(null, stmt, con);
}
return false;
}
}
package com.syxx.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import com.syxx.util.JDBCUtils;
public class UsersDao {
//登录操作
public boolean login(String uname,String upwd) {
//建立连接的对象
Connection con = null;
//执行SQL语句的对象
PreparedStatement stmt = null;
//返回结果集的对象
ResultSet rs = null;
try {
//1、建立连接
con = JDBCUtils.getCon();
//2、获取执行SQL语句的对象
String sql = "select * from user where name=? and password=?";
stmt = con.prepareStatement(sql);
stmt.setString(1, uname);
stmt.setString(2, upwd);
rs = stmt.executeQuery();
if(rs.next()) {
return true;
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtils.release(rs, stmt, con);
}
return false;
}
}
package com.syxx.entity;
import java.util.Date;
public class Msg {
int msgid;
String username;
String title;
String msgcontent;
int state;
String sendto;
Date msg_create_date;
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;
}
public int getMsgid() {
return msgid;
}
public void setMsgid(int 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 int getState() {
return state;
}
public void setState(int state) {
this.state = state;
}
public String getSendto() {
return sendto;
}
public void setSendto(String sendto) {
this.sendto = sendto;
}
}
package com.syxx.entity;
public class Users {
String username;
String password;
String email;
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
}
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>登录界面</title>
</head>
<body>
<form action="dologin.jsp" method="post">
用户名:<input type="text" name="uname" value="小明" /><Br>
密码 ：<input type="password" name="upwd" value="123456"/><br>
<input type="submit" value="登录">
</form>
</body>
</html>
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%@page import="com.syxx.entity.Users"%>
<%@page import="com.syxx.dao.UsersDao"%>
<%
//设置编码集
request.setCharacterEncoding("utf-8");
//获取uanme 与 upwd
String uname = request.getParameter("uname");
String upwd = request.getParameter("upwd");
UsersDao ud = new UsersDao();
if (ud.login(uname, upwd)){
//登录成功,创建User对象,并放入session
Users u=new Users();
u.setUsername(uname);
u.setPassword(upwd);
session.setAttribute("user", u);
//转发
request.getRequestDispatcher("main.jsp").forward(request, response);
}
else{
//重定向
response.sendRedirect("index.jsp");
}
%>
<%@page import="com.syxx.dao.MsgDao"%>
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Insert title here</title>
</head>
<body>
<%
int id = Integer.parseInt(request.getParameter("id"));
MsgDao msgd = new MsgDao();
if(msgd.delectMsg(id)){
request.getRequestDispatcher("main.jsp").forward(request, response);
}
%>
</body>
</html>
<%@page import="com.syxx.entity.Users"%>
<%@page import="com.syxx.entity.Msg"%>
<%@page import="com.syxx.dao.MsgDao"%>
<%@page language="java" import="java.util.*" pageEncoding="utf-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>主页面</title>
</head>
<body>
欢迎页面!!!欢迎你!!!
<%
Users u = (Users) session.getAttribute("user");
String name = u.getUsername();
out.print(name);
MsgDao msgd = new MsgDao();
List<Msg> list = msgd.findMsgUsername(name);
out.print(list.size());
%>
<a href="">写邮件</a>
<table border="1" width="1000">
<tr>
<td>邮件id</td>
<td>发件人</td>
<td>标题</td>
<td>收件人</td>
<td>状态</td>
<td>时间</td>
<td> </td>
<td> </td>
</tr>
<%
for (int i = 0; i < list.size(); i++) {
%>
<tr>
<td><%=list.get(i).getMsgid()%></td>
<td><%=list.get(i).getUsername()%></td>
<td><a href="detail.jsp?id=<%=list.get(i).getMsgid()%>"><%=list.get(i).getTitle()%></a></td>
<td><%=list.get(i).getSendto()%></td>
<td>
<%
if (list.get(i).getState() == 1) {
%> <img
src="images/sms_unReaded.png"></img> <%
} else {
%> <img
src="images/sms_readed.png"></img> <%
}
%>
</td>
<!-- 0已读,1未读 -->
<td><%=list.get(i).getMsg_create_date()%></td>
<td><a href="">回复</a></td>
<td><a href="delete.jsp?id=<%=list.get(i).getMsgid()%>">删除</a></td>
</tr>
<%} %>
</talbe>
</body>
</html>
package com.syxx.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCUtils {
//获取连接的方法
public static Connection getCon() throws Exception {
//1注册和加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/keshe","root","root");
return con;
}
//关闭资源 释放资源
public static void release(ResultSet rs,Statement stmt,Connection con) {
if(rs!=null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
rs = null;
}
if(stmt!=null) {
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
stmt = null;
}
if(con!=null) {
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
con = null;
}
}
}
