jsp 短信息收发系统 带SQL数据库(2005)

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.Messages; public class MessageDao { int i = 0; Connection con = null; PreparedStatement pstm = null; ResultSet rs = null; Messages mess = null; List list = new ArrayList(); BaseDao bd = new BaseDao(); //发短信 public int sendMessage(Messages m){ String sql = "insert into messages values(?,?,?,?,?,?,?)"; con = bd.getCon(); try { PreparedStatement pstm = con.prepareStatement(sql); pstm.setString(1, m.getSender()); pstm.setString(2, m.getSendtime()); pstm.setString(3, m.getReplyer()); pstm.setString(4, m.getContent()); pstm.setInt(5, m.getMark()); pstm.setInt(6, m.getSendelete()); pstm.setInt(7, m.getReplydelete()); i = pstm.executeUpdate(); System.out.println("发送成功!"); } catch (SQLException e) { System.out.println("发送失败!"); e.printStackTrace(); } finally { bd.closeCon(con); } return i; } /* public static void main(String[] args){ MessageDao md = new MessageDao(); List list = new ArrayList(); list = md.queryReply("ss"); for(int i=0; i<list.size(); i++){ Messages mess = (Messages)list.get(i); System.out.println(mess.getId()); System.out.println(mess.getSender()); System.out.println(mess.getSendtime()); System.out.println(mess.getReplyer()); System.out.println(mess.getContent()); System.out.println(mess.getMark()); } } */ //查询收到的所有短信 public List queryReply(String replyer){ String sql = "select * from messages where replyer=? and replydelete=? order by sendtime desc"; con = bd.getCon(); try { pstm = con.prepareStatement(sql); pstm.setString(1, replyer); pstm.setInt(2, 0); rs = pstm.executeQuery(); while(rs.next()){ mess = new Messages(); mess.setId(rs.getInt(1)); mess.setSender(rs.getString(2)); mess.setSendtime(rs.getString(3).substring(0, 19)); mess.setReplyer(rs.getString(4)); mess.setContent(rs.getString(5)); mess.setMark(rs.getInt(6)); mess.setSendelete(rs.getInt(7)); mess.setReplydelete(rs.getInt(8)); list.add(mess); } } catch (SQLException e) { e.printStackTrace(); } finally { bd.closeCon(con); } return list; } //根据回复人和未读标记和是否已被删除查找所有未读短信 public List queryNotYet(String replyer, int mark){ String sql = "select * from messages where replyer=? and mark=? and replydelete=? order by sendtime desc"; con = bd.getCon(); try { pstm = con.prepareStatement(sql); pstm.setString(1, replyer); pstm.setInt(2, mark); pstm.setInt(3, 0); rs = pstm.executeQuery(); while(rs.next()){ Messages mess = new Messages(); mess.setId(rs.getInt(1)); mess.setSender(rs.getString(2)); mess.setSendtime(rs.getString(3).substring(0, 19)); mess.setReplyer(rs.getString(4)); mess.setContent(rs.getString(5)); mess.setMark(rs.getInt(6)); mess.setSendelete(rs.getInt(7)); mess.setReplydelete(rs.getInt(8)); list.add(mess); } } catch (SQLException e) { e.printStackTrace(); } finally { bd.closeCon(con); } return list; } //根据发信人显示所有发出的短信 public List querySend(String sender){ String sql = "select * from messages where sender=? and sendelete=? order by sendtime desc"; con = bd.getCon(); try { pstm = con.prepareStatement(sql); pstm.setString(1, sender); pstm.setInt(2, 0); rs = pstm.executeQuery(); while(rs.next()){ mess = new Messages(); mess.setId(rs.getInt(1)); mess.setSender(rs.getString(2)); mess.setSendtime(rs.getString(3).substring(0, 19)); mess.setReplyer(rs.getString(4)); mess.setContent(rs.getString(5)); mess.setMark(rs.getInt(6)); mess.setSendelete(rs.getInt(7)); mess.setReplydelete(rs.getInt(8)); list.add(mess); } } catch (SQLException e) { e.printStackTrace(); } finally { bd.closeCon(con); } return list; } //根据ID显示对应短信的详细信息 public List queryById(String ID){ List list = new ArrayList(); String sql = "select * from messages where id=? order by sendtime desc"; con = bd.getCon(); try { pstm = con.prepareStatement(sql); pstm.setString(1, ID); rs = pstm.executeQuery(); if(rs.next()){ mess = new Messages(); mess.setId(rs.getInt(1)); mess.setSender(rs.getString(2)); mess.setSendtime(rs.getString(3).substring(0, 19)); mess.setReplyer(rs.getString(4)); mess.setContent(rs.getString(5)); mess.setMark(rs.getInt(6)); mess.setSendelete(rs.getInt(7)); mess.setReplydelete(rs.getInt(8)); list.add(mess); } } catch (SQLException e) { e.printStackTrace(); } finally { bd.closeCon(con); } return list; } //改变为已读标记 public void setMarkById(String ID){ String sql = "update messages set mark=1 where id=?"; con = bd.getCon(); try { pstm = con.prepareStatement(sql); pstm.setString(1, ID); int update = pstm.executeUpdate(); System.out.println("成功更改标记为已读！"); } catch (SQLException e) { e.printStackTrace(); } finally { bd.closeCon(con); } } //改变收信箱删除标记为已删除 public int deletereplyMessage(String ID){ String sql = "update messages set replydelete=1 where id=?"; con = bd.getCon(); try { pstm = con.prepareStatement(sql); pstm.setString(1, ID); i = pstm.executeUpdate(); System.out.println("成功删除第"+i+"行的短信！"); } catch (SQLException e) { e.printStackTrace(); } finally { bd.closeCon(con); } return i; } //改变发信箱删除标记为已删除 public int deletesendMessage(String ID){ String sql = "update messages set sendelete=1 where id=?"; con = bd.getCon(); try { pstm = con.prepareStatement(sql); pstm.setString(1, ID); i = pstm.executeUpdate(); System.out.println("成功删除第"+i+"行的短信！"); } catch (SQLException e) { e.printStackTrace(); } finally { bd.closeCon(con); } return i; } }