qq说说表结构设计demo

package com.gist.shuoshuo; 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 org.junit.Test; import com.gist.shuoshuo.beans.Comments; import com.gist.shuoshuo.beans.Tops; import com.gist.shuoshuo.beans.User; /** * @author 高远</n> 邮箱：wgyscsf@163.com</n> 博客 http://blog.csdn.net/wgyscsf</n> * 编写时期 2016-3-18 下午7:45:13 */ public class MyShuoshuo { /* * * - 请查出说说编号1的 拥有者id、拥有者名字、说说内容、【评论人id、评论人姓名、评论内容】----所有子评论。 */ static Connection conn = null; static PreparedStatement ps = null; static ResultSet rs = null; /* * 查询说说表：tops */ @Test public Tops getTopsByTid(int tid) { Tops tops = null;// 说说内容 String sql = "select * from tops where tid=?"; conn = JDBCUtils.getConnection(); try { ps = conn.prepareStatement(sql); ps.setInt(1, tid); rs = ps.executeQuery(); while (rs.next()) { tops = new Tops(); tops.setTid(rs.getInt(1)); tops.setTcontent(rs.getString(2)); tops.setTdatetime(rs.getString(3)); tops.setTplace(rs.getString(4)); tops.setTviews(rs.getInt(5)); tops.setTzan(rs.getInt(6)); tops.setUid(rs.getInt(7)); } return tops; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); return null; } finally { JDBCUtils.releaseConn(rs, ps, conn); } } /* * 根据说说id获取评论 */ @Test public List<Comments> getCommentsByTid(int tid) { List<Comments> commentss = new ArrayList<Comments>(); String sql = "select * from comments where tid=?"; conn = JDBCUtils.getConnection(); try { ps = conn.prepareStatement(sql); ps.setInt(1, tid); rs = ps.executeQuery(); while (rs.next()) { Comments comments = new Comments(); comments.setCid(rs.getInt(1)); comments.setCcontent(rs.getString(2)); comments.setCstate(rs.getInt(3)); comments.setCidId(rs.getInt(4)); comments.setTid(rs.getInt(5)); comments.setUid(rs.getInt(6)); commentss.add(comments); } return commentss; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { JDBCUtils.releaseConn(rs, ps, conn); } return null; } /* * 根据用户id获取用户信息 */ public User getUserByUid(int uid) { User user = new User(); String sql = "select * from User where uid=?"; conn = JDBCUtils.getConnection(); try { ps = conn.prepareStatement(sql); ps.setInt(1, uid); rs = ps.executeQuery(); if (rs.next()) { user.setUid(rs.getInt(1)); user.setUname(rs.getString(2)); user.setUimg(rs.getString(3)); } return user; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { JDBCUtils.releaseConn(rs, ps, conn); } return null; } /* * 根据评论的评论id */ @Test public Comments getPerCommentsByCid(int cid) { Comments comments = new Comments(); String sql = "select * from comments where cid=?"; conn = JDBCUtils.getConnection(); try { ps = conn.prepareStatement(sql); ps.setInt(1, cid); rs = ps.executeQuery(); if (rs.next()) { comments.setCid(rs.getInt(1)); comments.setCcontent(rs.getString(2)); comments.setCstate(rs.getInt(3)); comments.setCidId(rs.getInt(4)); comments.setTid(rs.getInt(5)); comments.setUid(rs.getInt(6)); } return comments; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { JDBCUtils.releaseConn(rs, ps, conn); } return null; } /* * 根据评论的评论id */ @Test public Comments getPerCommentsByCid_id(int cid_id) { Comments comments = new Comments(); String sql = "select * from comments where cid_id=?"; conn = JDBCUtils.getConnection(); try { ps = conn.prepareStatement(sql); ps.setInt(1, cid_id); rs = ps.executeQuery(); if (rs.next()) { comments.setCid(rs.getInt(1)); comments.setCcontent(rs.getString(2)); comments.setCstate(rs.getInt(3)); comments.setCidId(rs.getInt(4)); comments.setTid(rs.getInt(5)); comments.setUid(rs.getInt(6)); } return comments; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { JDBCUtils.releaseConn(rs, ps, conn); } return null; } public static void main(String[] args) throws SQLException { MyShuoshuo myShuoshuo = new MyShuoshuo(); Tops tops = myShuoshuo.getTopsByTid(1);// 获取说说 User topsUser = myShuoshuo.getUserByUid(tops.getUid());// 获取说说的拥有者 List<Comments> commentss = myShuoshuo.getCommentsByTid(tops.getTid());// 获取评论 /* * 开始按照说说格式显示 */ // 说说内容以及说说发表人 System.out.println("我是" + topsUser.getUname() + ",我发表了说说：" + tops.getTcontent() + ",发表日期：" + tops.getTdatetime() + "..."); // 以下是评论内容 for (Comments comments : commentss) { // 如果cid_id等于0（在数据表现为null），说明就是直接评论的说说；否则评论的是评论表id对应cid_id的评论 // 评论说说 if (comments.getCidId() == 0) { /* * 打印评论说说的评论 */ User user = myShuoshuo.getUserByUid(comments.getUid());// 该评论的拥有者 System.out.println("我叫：" + user.getUname() + "，我评论了说说；" + tops.getTcontent() + ",评论内容为：" + comments.getCcontent()); int cid = comments.getCid();// 拿取评论说说的id，这个是评论的对象 // 打印评论该评论的所有评论 for (Comments comments2 : commentss) { // 判断谁评论了cid这条评论，全部打印出来 if (comments2.getCidId() == cid) { // 评论评论=_+ User myUser = myShuoshuo.getUserByUid(comments2 .getUid());// 该评论的拥有者 Comments herComments = myShuoshuo .getPerCommentsByCid(comments2.getCidId());// 获取评论评论的对象 User herUser = myShuoshuo.getUserByUid(herComments .getUid());// 获取被评论的个人信息 System.out.println("我叫：" + myUser.getUname() + ",我评论了：" + herUser.getUname() + ",的评论，他的内容是：" + herComments.getCcontent() + ",我的评论内容是：" + comments2.getCcontent()); // 难点 cid = comments2.getCidId();// 更换评论评论的的对象 } } } else { } } } }