JSP技术实现BBS论坛

package s2jsp.lg.dao.impl; import java.util.*; import s2jsp.lg.dao.TopicDao; import s2jsp.lg.entity.Topic; import java.sql.*; public class TopicDaoImpl extends BaseDao implements TopicDao { private Connection conn=null; //用于保存数据库连接 private PreparedStatement pstmt=null; //用于执行SQL语句 private ResultSet rs=null; //用于保存查询结果集 /** * 添加主题 */ public int addTopic(Topic topic) { int num=0; try { String sql="insert into TBL_TOPIC values(?,?,convert(datetime,?),convert(datetime,?)" + ",convert(smallint,?),convert(smallint,?))"; String[] param=new String[]{topic.getTitle(),topic.getContent(),topic.getPublishTime(), topic.getModifyTime(),Integer.toString(topic.getUid()), Integer.toString(topic.getBoardId())}; num=super.executeSQL(sql, param); } catch (RuntimeException e) { e.printStackTrace(); }finally{ this.closeAll(conn, pstmt, rs); } return num; } /** * 删除主题 */ public int delectTopic(int topicId) { int row=0; try{ String sql="delete from TBL_TOPIC where topicId="+topicId; row=super.executeSQL(sql, null) ; }catch(Exception e){ e.printStackTrace(); }finally{ this.closeAll(conn, pstmt, rs); } return row; } /** * 查找主题List */ public List<Topic> findListTopic(int page, int boardId) { List<Topic> list=new ArrayList<Topic>(); //用来保存主题对象列表 int rowBegin=0; //开始行数，表示每页第一条记录在数据库中的行数 if(page>1){ rowBegin=5*(page-1);//按页数取得开始行数，设每页可以显示5条回复 } String sql="select top 5 * from TBL_TOPIC where boardId=" + boardId+ "and topicId not in(select top "+rowBegin+" topicId " + "from TBL_TOPIC where boardId="+boardId+" order by publishTime desc)" + "order by publishTime desc"; try{ conn=this.getConn(); //获得数据库连接 pstmt=conn.prepareStatement(sql); rs=pstmt.executeQuery();//执行SQL，得到结果集 /*将结果集中的信息取出保存到list中*/ while(rs.next()){ Topic topic=new Topic(); //主题对象 topic.setTopicId(rs.getInt("topicId")); topic.setTitle(rs.getString("title")); topic.setContent(rs.getString("content")); topic.setPublishTime(rs.getString("publishTime")); topic.setModifyTime(rs.getString("modifyTime")); topic.setUid(rs.getInt("uid")); topic.setBoardId(rs.getInt("boardId")); list.add(topic); } }catch(Exception e){ e.printStackTrace(); }finally{ this.closeAll(conn, pstmt, rs); } return list; } /** * 查找一个主题的详细信息 */ public Topic findTopic(int topicId) { Topic topic=new Topic(); try{ String sql="select * from TBL_TOPIC where topicId="+topicId; conn=this.getConn(); pstmt=conn.prepareStatement(sql); rs=pstmt.executeQuery(); if(rs.next()){ topic.setTitle(rs.getString("title")); topic.setContent(rs.getString("content")); topic.setPublishTime(rs.getString("publishTime")); topic.setModifyTime(rs.getString("modifyTime")); topic.setUid(rs.getInt("uid")); topic.setBoardId(rs.getInt("boardId")); } }catch(Exception e){ e.printStackTrace(); }finally{ this.closeAll(conn, pstmt, rs); } return topic; } /** * 更新主题 */ public int updateTopic(Topic topic) { int row=0; try{ String sql="update TBL_TOPIC set title=?,content=?,publishTime=?,modifyTime=?," + "uId=?,boardId=? where topicId=?"; String[] param=new String[]{topic.getTitle(),topic.getContent(),topic.getPublishTime(), topic.getModifyTime(),Integer.toString(topic.getUid()), Integer.toString(topic.getBoardId()),Integer.toString(topic.getTopicId())}; row=super.executeSQL(sql, param); }catch(Exception e){ e.printStackTrace(); }finally{ this.closeAll(conn, pstmt, rs); } return row; } /** * 查询主题数 */ public int findCountTopic(int boardId){ int num=0; try{ String sql="select * from TBL_TOPIC where boardId="+boardId; conn=this.getConn(); pstmt=conn.prepareStatement(sql); rs=pstmt.executeQuery(); while(rs.next()){ num=rs.getRow(); } }catch(Exception e){ e.printStackTrace(); }finally{ this.closeAll(conn, pstmt, rs); } return num; } /** * 查找最后发表的主题 */ public Topic findTopicByBoardId(int boardId) { Topic topic=new Topic(); try{ String sql="select Top 1 * from TBL_TOPIC where boardId="+boardId+ "order by publishTime desc"; conn=this.getConn(); pstmt=conn.prepareStatement(sql); rs=pstmt.executeQuery(); if(rs.next()){ topic.setTitle(rs.getString("title")); topic.setContent(rs.getString("content")); topic.setPublishTime(rs.getString("publishTime")); topic.setModifyTime(rs.getString("modifyTime")); topic.setUid(rs.getInt("uid")); topic.setBoardId(rs.getInt("boardId")); topic.setTopicId(rs.getInt("topicId")); } }catch(Exception e){ e.printStackTrace(); }finally{ this.closeAll(conn, pstmt, rs); } return topic; } }