jsp项目--论坛短消息

/** * */ package s2jsp.sg.dao.impl; import java.sql.*; import java.text.SimpleDateFormat; import java.util.Date; import java.util.ArrayList; import java.util.List; import s2jsp.sg.dao.BaseDao; import s2jsp.sg.dao.MessageDao; import s2jsp.sg.entity.Message; /** * @author Administrator * */ public class MessageDaoImpl extends BaseDao implements MessageDao { private Connection conn = null; private PreparedStatement pstmt = null; private ResultSet rs = null; // 删除短信新 public int delete(int id) { // TODO Auto-generated method stub String sql = "delete from TBL_MESSAGE where id=" + id; return this.executeSQL(sql, null); } // 根据id查询一条短信息 public Message findMessage(int id) { // TODO Auto-generated method stub Message message = null; String sql = "select * from TBL_MESSAGE where id=?"; try { conn = this.getConn(); pstmt = conn.prepareStatement(sql); pstmt.setInt(1, id); rs = pstmt.executeQuery(); while (rs.next()) { message = new Message(); message.setId(rs.getInt("id")); message.setNote(rs.getString("note")); message.setSendUname(rs.getString("sendUname")); message.setReceiveUname(rs.getString("receiveUname")); message.setPostTime(rs.getString("postTime")); message.setReadSign(rs.getInt("readSign")); } } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { this.closeAll(conn, pstmt, rs); } //更改读标记 int sign=message.getReadSign(); if(sign==0){ this.update(message); } return message; } // 根据用户名查询用户收到的所有短信息列表 public List listByRecevieUname(String uname) { // TODO Auto-generated method stub List list = new ArrayList(); String sql = "select * from TBL_MESSAGE where receiveUname=? order by readSign,postTime desc "; try { conn = this.getConn(); pstmt = conn.prepareStatement(sql); pstmt.setString(1, uname); rs = pstmt.executeQuery(); while (rs.next()) { Message message = new Message(); message.setId(rs.getInt("id")); message.setNote(rs.getString("note")); message.setSendUname(rs.getString("sendUname")); message.setReceiveUname(rs.getString("receiveUname")); message.setPostTime(rs.getString("postTime")); message.setReadSign(rs.getInt("readSign")); list.add(message); } } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { this.closeAll(conn, pstmt, rs); } return list; } //按页显示，每页显示5个 public List listByPageRecevieUname(int page,String uname) { // TODO Auto-generated method stub List list = new ArrayList(); int rowBegin=0; if(page>1) rowBegin=5*(page-1); String sql = "select top 5 * from TBL_MESSAGE where receiveUname= ? and id not in ( select top " + rowBegin + " id from TBL_MESSAGE where receiveUname= ? order by readSign,postTime desc ) order by readSign,postTime desc "; try { conn = this.getConn(); pstmt = conn.prepareStatement(sql); pstmt.setString(1, uname); pstmt.setString(2, uname); rs = pstmt.executeQuery(); while (rs.next()) { Message message = new Message(); message.setId(rs.getInt("id")); message.setNote(rs.getString("note")); message.setSendUname(rs.getString("sendUname")); message.setReceiveUname(rs.getString("receiveUname")); message.setPostTime(rs.getString("postTime")); message.setReadSign(rs.getInt("readSign")); list.add(message); } } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { this.closeAll(conn, pstmt, rs); } return list; } // 增加保存短信息 public int save(Message message) { // TODO Auto-generated method stub String sql = "insert into TBL_MESSAGE(note,sendUname,receiveUname,readSign) " + "values(?,?,?,0)"; String time = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss") .format(new Date()); // 取得日期时间 message.setPostTime(time); String param[] = { message.getNote(), message.getSendUname(), message.getReceiveUname() }; return this.executeSQL(sql, param); } // 修改一条短信息的读标记 public int update(Message message) { // TODO Auto-generated method stub String sql = "update TBL_MESSAGE set readSign=1 where id=" + message.getId(); return this.executeSQL(sql, null); } }