论坛短消息-JSP编写

package com.xaccp.dao; import java.sql.SQLException; import java.util.ArrayList; import com.xaccp.entity.Message; public class MessageDAO extends BaseDAO { /** * 增加短消息 * @param message * @return */ public int save(Message message){ openDB(); String sql="insert into tbl_message values ('"+message.getNote()+"',"+message.getSendUname()+","+ message.getReceiveUname()+",default,"+message.getReadSign()+")"; String str="select @@identity from tbl_message"; int id=0; try { stm=conn.createStatement(); stm.executeUpdate(sql); rs=stm.executeQuery(str); while (rs.next()) { id=rs.getInt(1); } } catch (SQLException e) { logger.debug(e.getMessage()); }finally{ closeAll(); } return id; } /** * 根据Id删除一条短消息 * @param id * @return */ public int delete(int id) { openDB(); String sql="delete from tbl_message where id="+id; int num=0; try { stm=conn.createStatement(); num=stm.executeUpdate(sql); } catch (SQLException e) { logger.debug(e.getMessage()); }finally{ closeAll(); } return num; } /** * 修改一条短消息的读标记 * @param message * @return */ public int update(Message message){ openDB(); String sql="update tbl_message set readSign=1 where id="+message.getId(); int num=0; try { stm=conn.createStatement(); num=stm.executeUpdate(sql); } catch (SQLException e) { logger.info(e.getMessage()); }finally{ closeAll(); } return num; } public Message findMessage(int id) { openDB(); String sql="select * from tbl_message where id="+id; Message message=new Message(); try { stm=conn.createStatement(); rs=stm.executeQuery(sql); while(rs.next()){ message.setId(rs.getInt(1)); message.setNote(rs.getString(2)); message.setSendUname(rs.getInt(3)); message.setReceiveUname(rs.getInt(4)); message.setPostTime(rs.getTimestamp(5)); message.setReadSign(rs.getInt(6)); } return message; } catch (SQLException e) { logger.info(e.getMessage()); return null; }finally{ closeAll(); } } public ArrayList<Message> listByReceiveUname(String uname,int numPage){ openDB(); //String sql="select top 20*("+numPage+") * from tbl_message where receiveuname="+(new UserDAO()).getUserId(uname); String sql=String.format("select top 20 * from tbl_message inner join tbl_user on tbl_message.receiveuname=tbl_user.Id where uname='%s' and" + " tbl_message.id not in (select top %d id from tbl_message order by postTime desc) order by postTime desc", uname,20*(numPage-1)); ArrayList<Message> lst=new ArrayList<Message>(); try { stm=conn.createStatement(); rs=stm.executeQuery(sql); while(rs.next()){ Message message=new Message(); message.setId(rs.getInt(1)); message.setNote(rs.getString(2)); message.setSendUname(rs.getInt(3)); message.setReceiveUname(rs.getInt(4)); message.setPostTime(rs.getTime(5)); message.setReadSign(rs.getInt(6)); lst.add(message); } return lst; } catch (SQLException e) { logger.info(e.getMessage()); return null; }finally{ closeAll(); } } public int getAllCount(String uname) { openDB(); String sql="select count(*) from tbl_message as m inner join tbl_user as u on m.receiveUname=u.id where u.uname='"+uname+"'"; int num=0; try { stm=conn.createStatement(); rs=stm.executeQuery(sql); while(rs.next()) { num=rs.getInt(1); } } catch (SQLException e) { logger.debug(e.getMessage()); }finally{ closeAll(); } return num; } }