留言板系统

package com.xiaojia.dao.impl; 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 com.xiaojia.bean.Message; import com.xiaojia.bean.ReplyMsg; import com.xiaojia.dao.MessageDao; import com.xiaojia.utils.DBUtils; /** * 持久层接口实现类 * @author wu * */ public class MessageDaoImpl implements MessageDao { @Override public void addMessage(Message msg) { PreparedStatement ps=null;; Connection conn=DBUtils.getConnection(); try { ps = conn.prepareStatement("insert into message (name,title,content,time,replynum) value (?,?,?,?,?)"); ps.setString(1, msg.getName()); ps.setString(2, msg.getTitle()); ps.setString(3, msg.getContent()); ps.setString(4, msg.getTime()); ps.setInt(5, msg.getReplynum()); ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); }finally{ DBUtils.release(conn, ps, null); } } @Override public List<Message> findAllMessage() { Connection conn=DBUtils.getConnection(); PreparedStatement ps=null; ResultSet rs=null; List<Message> lists=new ArrayList<Message>(); try { ps = conn.prepareStatement("select * from message order by time desc"); rs = ps.executeQuery(); Message msg=null; while(rs.next()){ msg=new Message(); msg.setId(rs.getInt("id")); msg.setName(rs.getString("name")); msg.setTitle(rs.getString("title")); msg.setContent(rs.getString("content")); msg.setTime(rs.getString("time")); msg.setReplynum(rs.getInt("replynum")); lists.add(msg); } } catch (SQLException e) { e.printStackTrace(); }finally{ DBUtils.release(conn, ps, rs); } return lists; } @Override public Message findMessageById(String id) { Connection conn=DBUtils.getConnection(); PreparedStatement ps=null; ResultSet rs=null; Message msg=null; try { ps = conn.prepareStatement("select * from message where id=?"); ps.setInt(1, Integer.valueOf(id)); rs=ps.executeQuery(); while(rs.next()){ msg=new Message(); msg.setId(rs.getInt("id")); msg.setName(rs.getString("name")); msg.setTitle(rs.getString("title")); msg.setContent(rs.getString("content")); msg.setTime(rs.getString("time")); msg.setReplynum(rs.getInt("replynum")); return msg; } } catch (SQLException e) { e.printStackTrace(); }finally{ DBUtils.release(conn, ps, rs); } return msg; } @Override public List<ReplyMsg> findAllReplyByMessageId(String id) { Connection conn=DBUtils.getConnection(); PreparedStatement ps=null; ResultSet rs=null; List<ReplyMsg> lists=new ArrayList<ReplyMsg>(); try{ ps=conn.prepareStatement("select * from replymsg where mid=?"); ps.setInt(1, Integer.valueOf(id)); rs = ps.executeQuery(); ReplyMsg replyMsg=null; while(rs.next()){ replyMsg=new ReplyMsg(); replyMsg.setId(rs.getInt("id")); replyMsg.setRtime(rs.getString("rtime")); replyMsg.setRcontent(rs.getString("rcontent")); replyMsg.setMid(rs.getInt("mid")); replyMsg.setRname(rs.getString("rname")); lists.add(replyMsg); } }catch(Exception e){ e.printStackTrace(); }finally{ DBUtils.release(conn, ps, rs); } return lists; } @Override public void addReplyMessage(String rtime, String rcontent, String mid, String rname) { Connection conn=DBUtils.getConnection(); PreparedStatement ps=null; try{ ps=conn.prepareStatement("insert into replymsg (rtime,rcontent,mid,rname) value (?,?,?,?)"); ps.setString(1, rtime); ps.setString(2, rcontent); ps.setInt(3, Integer.valueOf(mid)); ps.setString(4, rname); ps.executeUpdate(); }catch(Exception e){ e.printStackTrace(); }finally{ DBUtils.release(conn, ps, null); } } @Override public void updateReplyNum(String mid) { //先查出具体的Message Message message = findMessageById(mid); Connection conn=DBUtils.getConnection(); PreparedStatement ps=null; try{ ps=conn.prepareStatement("update message set replynum=? where id=?"); ps.setInt(1, message.getReplynum()+1); ps.setInt(2, Integer.valueOf(mid)); ps.executeUpdate(); }catch(Exception e){ e.printStackTrace(); }finally{ DBUtils.release(conn, ps, null); } } @Override public int count(String search) { Connection conn=DBUtils.getConnection(); PreparedStatement ps=null; ResultSet rs=null; try{ ps=conn.prepareStatement("select count(*) from message where name like ? or title like ? or content like ? "); ps.setString(1, "%"+search+"%"); ps.setString(2, "%"+search+"%"); ps.setString(3, "%"+search+"%"); rs = ps.executeQuery(); while(rs.next()){ return rs.getInt(1); } }catch(Exception e){ e.printStackTrace(); }finally{ DBUtils.release(conn, ps, rs); } return 0; } @Override public List<Message> findMessagePage(int currentPage, int pageSize,String search) { Connection conn = DBUtils.getConnection(); PreparedStatement ps=null; ResultSet rs=null; List<Message> lists=new ArrayList<Message>(); try{ ps=conn.prepareStatement("select * from message where name like ? or title like ? or content like ? order by time desc limit ?,?"); ps.setString(1, "%"+search+"%"); ps.setString(2, "%"+search+"%"); ps.setString(3, "%"+search+"%"); ps.setInt(4, (currentPage-1)*pageSize); ps.setInt(5, pageSize); rs=ps.executeQuery(); Message msg=null; while(rs.next()){ msg=new Message(); int id = rs.getInt("id"); msg.setId(id); msg.setName(rs.getString("name")); msg.setTitle(rs.getString("title")); msg.setContent(rs.getString("content")); msg.setTime(rs.getString("time")); msg.setReplynum(rs.getInt("replynum")); //查出该留言所有回复 List<ReplyMsg> replyMsgs = findAllReplyByMessageId(id+""); msg.setReplyMsg(replyMsgs); lists.add(msg); } }catch(Exception e){ e.printStackTrace(); }finally{ DBUtils.release(conn, ps, rs); } return lists; } @Override public void deleteReplyById(String id) { Connection conn=DBUtils.getConnection(); PreparedStatement ps=null; try{ ps=conn.prepareStatement("delete from replymsg where mid=?"); ps.setInt(1, Integer.valueOf(id)); ps.executeUpdate(); }catch(Exception e){ e.printStackTrace(); }finally{ DBUtils.release(conn, ps, null); } } @Override public void deleteMessageById(String id) { Connection conn=DBUtils.getConnection(); PreparedStatement ps=null; try{ ps=conn.prepareStatement("delete from message where id=?"); ps.setInt(1, Integer.valueOf(id)); ps.executeUpdate(); }catch(Exception e){ e.printStackTrace(); }finally{ DBUtils.release(conn, ps, null); } } }