bbs系统 JSP SERVLET

package dao.impl; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Timestamp; import java.util.ArrayList; import java.util.List; import util.DButil; import dao.MessageDao; import entitybean.Broad; import entitybean.Message; import entitybean.User; public class MessageDaoImpl implements MessageDao { public int addMessage(Message msg) { String sql="insert into Message (title,content,ts,broad_id,user_id,message_id) values (?,?,?,?,?,?)"; int count=0; Connection con=DButil.getConnection(); PreparedStatement pst=null; try { pst=con.prepareStatement(sql); pst.setString(1, msg.getTitle()); pst.setString(2, msg.getContent()); pst.setTimestamp(3, new Timestamp(msg.getTs().getTime())); pst.setInt(4, msg.getBroad().getId()); pst.setInt(5, msg.getUser().getId()); pst.setInt(6, msg.getToMsg().getId()); count=pst.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally{ DButil.closeResource(con, pst, null); } return count; } public int deleteMessage(Message msg) { String sql="delete from Message where id=?"; int count=0; Connection con=DButil.getConnection(); PreparedStatement pst=null; try { pst=con.prepareStatement(sql); pst.setInt(1,msg.getId()); count=pst.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally{ DButil.closeResource(con, pst, null); } return count; } public List<Message> findMessagesByBid(Integer bid) { String sql="select id,title,ts from Message where broad_id=? and message_id=0"; List<Message> msgs=new ArrayList<Message>(); Connection con=DButil.getConnection(); PreparedStatement pst=null; ResultSet rs=null; try { pst=con.prepareStatement(sql); pst.setInt(1,bid); rs=pst.executeQuery(); while(rs.next()){ Message msg = new Message(); msg.setId(rs.getInt("id")); msg.setTitle(rs.getString("title")); msg.setTs(rs.getTimestamp("ts")); msgs.add(msg); } } catch (SQLException e) { e.printStackTrace(); } finally{ DButil.closeResource(con, pst, null); } return msgs; } public Message loadMessage(Integer id) { String sql="select title,content,ts,message_id,user_id,broad_id from Message where id=?"; String sql3="select title,content from Message where message_id=?"; Message msg=null; Connection con=DButil.getConnection(); PreparedStatement pst=null; PreparedStatement pst2=null; PreparedStatement pst3=null; ResultSet rs=null; ResultSet rs2=null; ResultSet rs3=null; try { pst=con.prepareStatement(sql); pst.setInt(1, id); rs=pst.executeQuery(); if(rs.next()){ msg=new Message(); msg.setId(id); msg.setTitle(rs.getString("title")); msg.setContent(rs.getString("content")); msg.setTs(rs.getTimestamp("ts")); int bid=rs.getInt("broad_id"); Broad b = new Broad(); b.setId(bid); msg.setBroad(b); int uid=rs.getInt("user_id"); User u=loadUser(con, pst2, rs2, uid); if(u!=null) msg.setUser(u); int m=rs.getInt("message_id"); if(m==0){ pst3=con.prepareStatement(sql3); pst3.setInt(1, id); rs3=pst3.executeQuery(); while(rs3.next()){ Message msg1 = new Message(); msg1.setTitle(rs3.getString("title")); msg1.setContent(rs3.getString("content")); msg.getSubMsgs().add(msg1); } } } } catch (SQLException e) { e.printStackTrace(); } finally{ DButil.closeResource(con, pst, rs); DButil.closeResource(null, pst2, rs2); DButil.closeResource(null, pst2, rs3); } return msg; } //内部可重用类,贴子中需要大量显示用户名称的情况 private User loadUser(Connection con,PreparedStatement pst,ResultSet rs,int id) throws SQLException{ User u=null; String sql2="select login_name,real_name from User where id=?"; pst=con.prepareStatement(sql2); pst.setInt(1, id); rs=pst.executeQuery(); if(rs.next()){ u = new User(); u.setId(id); u.setLoginName(rs.getString("login_name")); u.setRealName(rs.getString("real_name")); } return u; } }