bbs.rar_论坛

package com.tsinghua.forum.dao.impl; import java.sql.Connection; import java.util.Date; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.List; import com.sun.java_cup.internal.shift_action; import com.tsinghua.forum.dao.Board; import com.tsinghua.forum.pojo.ChildBoard; import com.tsinghua.forum.pojo.MainBoard; import com.tsinghua.forum.pojo.Motif; import com.tsinghua.forum.pojo.NextReply; import com.tsinghua.forum.pojo.Reply; import com.tsinghua.forum.pojo.ReplyBean; import com.tsinghua.forum.pojo.TopicBean; import com.tsinghua.forum.pojo.User; import com.tsinghua.forum.dbc.DBConnection; public class BoardImpl extends DBConnection implements Board { Connection conn = null; ResultSet rs = null; PreparedStatement ps =null; public List<ChildBoard> getChildBoards() { // TODO Auto-generated method stub List<ChildBoard> list = new ArrayList<ChildBoard>(); conn = this.getConnection(); String sql = "select b.boardid , boardname , parentid , (select count(*) from tbl_topic where tbl_topic.boardid = b.boardid) topicCount , title , (select uname from tbl_user where t.uid=tbl_user.uid) uname , uid , topicid , modifytime from tbl_board as b left outer join tbl_topic as t on b.boardid = t.boardid where b.parentid > 0 order by b.boardid asc , modifytime desc "; try { ps = conn.prepareStatement(sql); rs = ps.executeQuery(); int boardid =0; while(rs.next()){ if(boardid != rs.getInt("boardid")){ ChildBoard cb = new ChildBoard(); cb.setBoardid(rs.getInt("boardId")); cb.setBoardname(rs.getString("boardName")); cb.setTopiccount(rs.getInt("topicCount")); cb.setUsername(rs.getString("uname")); cb.setFodimytime((new SimpleDateFormat("yyyy-MM-dd HH:mm").format(rs.getDate("modifyTime")))); cb.setTitle(rs.getString("title")); cb.setParentid(rs.getString("parentid")); cb.setTopicid(rs.getInt("topicid")); cb.setUid(rs.getInt("uid")); list.add(cb); boardid = rs.getInt("boardid"); } } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ this.closeConnection(conn, ps, rs); } return list; } public static void main(String[] args) { System.out.println(new BoardImpl().getReplys(81).size()); } public void test(){ conn = this.getConnection(); try { ps = conn.prepareStatement("select * from test"); rs = ps.executeQuery(); while(rs.next()){ System.out.println( new SimpleDateFormat("yyyy-MM-dd HH:mm:ss ").format(rs.getDate("hehe"))); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } public List<MainBoard> getMainBoards() { // TODO 返回所以主版块 也是 parendId == 0 的学生 ！ List<MainBoard> list = new ArrayList<MainBoard>(); conn = this.getConnection(); String sql = "select * from tbl_board where parentId = 0"; try { ps = conn.prepareStatement(sql); rs = ps.executeQuery(); while(rs.next()){ MainBoard mb = new MainBoard(); mb.setBoardid(rs.getInt("boardId")); mb.setBoardname(rs.getString("boardName")); mb.setParentid(rs.getInt("parentId")); list.add(mb); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ this.closeConnection(conn, ps, rs); } return list; } /* * user login check * */ public User checkUser(User u) { // check user User user = null; try { conn = getConnection(); ps = conn.prepareStatement("select * from tbl_user where uName=? and uPass=?"); ps.setString(1,u.getUname()); ps.setString(2,u.getUpass()); rs = ps.executeQuery(); if(rs.next()){ user = new User(); user.setId(rs.getInt("uid")); user.setUname(rs.getString("uName")); user.setUpass(rs.getString("uPass")); user.setHead(rs.getString("head")); user.setSex(rs.getInt("gender")); user.setMark(rs.getInt("mark")); user.setGrade(this.getGrade(user.getMark())); user.setRegtime(new SimpleDateFormat("yyyy-MM-dd HH:mm").format(rs.getDate("regTime"))); } } catch (Exception e) { e.printStackTrace(); }finally{ this.closeConnection(conn, ps, rs); } return user; } /* * 返回 user 信息 */ public User getUserDate(int uid){ User user = null; try { conn = getConnection(); ps = conn.prepareStatement("select * from tbl_user where uid =?"); ps.setInt(1,uid); rs = ps.executeQuery(); if(rs.next()){ user = new User(); user.setId(rs.getInt("uid")); user.setUname(rs.getString("uName")); user.setUpass(rs.getString("uPass")); user.setHead(rs.getString("head")); user.setSex(rs.getInt("gender")); user.setMark(rs.getInt("mark")); user.setGrade(this.getGrade(user.getMark())); user.setRegtime(new SimpleDateFormat("yyyy-MM-dd HH:mm").format(rs.getDate("regTime"))); } } catch (Exception e) { e.printStackTrace(); }finally{ this.closeConnection(conn, ps, rs); } return user; } /* * 找到对应的 帖子 ！ */ public Motif getMotif(int topicid){ Motif mf = new Motif(); conn = this.getConnection(); String sql = "select t.* ,(select boardname from tbl_board where tbl_board.boardid=t.boardid) boardname , uname , regtime ,head ,mark from tbl_topic as t left outer join tbl_user on t.uid = tbl_user.uid where topicid = ? "; try { ps = conn.prepareStatement(sql); ps.setInt(1,topicid); rs = ps.executeQuery(); while(rs.next()){ mf.setBoardid(rs.getInt("boardid")); mf.setBoardname(rs.getString("boardname")); mf.setTopicid(rs.getInt("topicid")); mf.setUid(rs.getInt("uid")); mf.setUname(rs.getString("uname")); mf.setRegtime(new SimpleDateFormat("yyyy-MM-dd HH:mm").format(rs.getDate("regtime"))); mf.setTitle(rs.getString("title")); mf.setContent(rs.getString("content")); mf.setPublishtime(new SimpleDateFormat("yyyy-MM-dd HH:mm").format(rs.getDate("publishtime"))); mf.setModifytime(new SimpleDateFormat("yyyy-MM-dd HH:mm").format(rs.getDate("modifytime"))); mf.setHead(rs.getString("head")); mf.setMark(rs.getInt("mark")); mf.setGrade(this.getGrade(mf.getMark())); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ this.closeConnection(conn, ps, rs); } return mf; } /* * 返回对应的 回复帖子 ！ */ public List<Reply> getReplys(int topicid){ List<Reply> list = new ArrayList<Reply>(); conn = this.getConnection(); String sql = "select tbl_reply.uid , head , uname , replyid , regtime,title,[content],publishtime,modifytime , mark from tbl_reply left outer join tbl_user on tbl_reply.uid = tbl_user.uid where tbl_reply.topicid = ? "; try { ps = conn.prepareStatement(sql); ps.setInt(1,topicid); rs = ps.executeQuery(); while(rs.next()){ Reply rl = new Reply(); rl.setUid(rs.getInt("uid")); rl.setUname(rs.getString("uname")); rl.setRegtime(new SimpleDateFormat("yyyy-MM-dd HH:mm").format(rs.getDate("regtime"))); rl.setTitle(rs.getString("title")); rl.setReplyid(rs.getInt("replyid")); rl.setContent(rs.getString("content")); rl.setPublishtime(new SimpleDateFormat("yyyy-MM-dd HH:mm").format(rs.getDate("publishtime"))); rl.setModifytime(new SimpleDateFormat("yyyy-MM-dd HH:mm").format(rs.getDate("modifytime"))); rl.setHead(r