单表评论设计

来源：互联网发布：java异或运算有什么用编辑：程序博客网时间：2024/05/22 12:17

数据库我这里创建的是一个商品评论回复的表

comment_type为判断是评论还是评论

commt_id为评论/回复的数据id. 评论是值为-1,回复时设置为指定的数据id

CREATE TABLE item_comments ( id int PRIMARY KEY, item_id int not NULL, commnt_id int , from_user int not NULL, to_user int , comment_msg CHAR(128) not NULL, comment_type int not null)

部分java代码展示评论数据

package test;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.Statement;import java.util.ArrayList;import java.util.List;import org.junit.Test;public class Demo {public static void main(String[] args) throws Exception {long start = System.currentTimeMillis();test();long end = System.currentTimeMillis();System.out.println(end-end);}@Testpublic static  void test() throws Exception {List<Comments> commentList = new ArrayList<>();Class.forName("com.mysql.jdbc.Driver");String url = "jdbc:mysql://localhost:3306/abc";String username = "root";String password = "root";Connection con = DriverManager.getConnection(url, username, password);//String sql = "select * from item_comments ";String sql = "select * from item_comments ic ORDER BY ic.from_user ,ic.to_user";/*PreparedStatement pt = con.prepareStatement(sql);pt.setInt(1, 1);ResultSet rs = pt.executeQuery(sql);*/Statement st = con.createStatement();ResultSet rs = st.executeQuery(sql);while (rs.next()) {Comments cs = new Comments();cs.setComment_msg(rs.getString("comment_msg"));cs.setComment_type(rs.getInt("comment_type"));cs.setCommnt_id(rs.getInt("commnt_id"));cs.setFrom_user(rs.getInt("from_user"));cs.setTo_user(rs.getInt("to_user"));cs.setId(rs.getInt("id"));cs.setItem_id(rs.getInt("item_id"));commentList.add(cs);}System.out.println(get(commentList, -1,new StringBuilder()).toString());}public static StringBuilder get(List<Comments> commentList,int comment_id/* 回复主题id */,StringBuilder str){if(comment_id==-1){for(Comments comment:commentList){/* 评论 */int id = comment.getCommnt_id();if(id==-1&&comment.getComment_type()==0){str.append(comment.getFrom_user()).append(" : ").append(comment.getComment_msg()).append("\n");/* 获取该评论的回复*/str = get(commentList,comment.getId(),str);}}}else{for(Comments comment:commentList){/* 回复 的回复*/if(comment.getCommnt_id()!=null){if(comment.getCommnt_id()==comment_id&&comment.getCommnt_id()!=-1){str.append(comment.getFrom_user()+"@"+comment.getTo_user()+" : "+comment.getComment_msg()).append("\n");str = get(commentList,comment.getId(),str);}}}}    return str; } }

结果显示

1 : hello2@1 : hi1@2 : dd2@1 : cc3@2 : sb2 : no bb please3@2 : you a big egg

0 0