I have something to say:

After completing the data display function, I want to improve the whole APP. It is found that the background is very chaotic, and there are many points that are not available. For example, the picture should have thumbnails and original pictures, paragraphs, comments and comments should be linked together, paragraphs should have creation time, etc.

So we redesigned the database, re-crawled the data, and redesigned the background interface.

This time, we will focus on the main contents of this reconstruction.

Database design:

 

Six tables were designed, respectively.

1. Segment table, which mainly stores pictures and other information of each paragraph.
2. Comment form, which mainly stores comment information. Comments can upload pictures.
3. User table
4. Tag tables, which customize tags before each paragraph is published, store these tags.
5. Since there is a many-to-many relationship between user's comment and paragraph, a table should be added to store the comment record.
6. Segment label Association table, because segments and labels are many-to-many, so it needs an additional table to store the association relationship.

Interface design:

Orange is the table and coffee is the interface.

At present, fourteen interfaces are designed, and the relationship between the interfaces and the related tables is illustrated in the figure above.

Background structure:

Under the bean package is the basic entity class.

The implement ation package is a subclass of the message entity class.

dao package is a concrete implementation class involving database.

Serlet is the interface class.

util is the tool class used in the process.

Specific examples:

Next, take query segment sub-interface as an example to introduce the specific structure.

Class bean:

Message Entity Class:

 1 public class MessageEntity {
 2 
 3     // Return Information Description
 4     private String reason;
 5     // Return code
 6     private int errorCode;
 7 
 8     public String getReason() {
 9         return reason;
10     }
11 
12     public void setReason(String reason) {
13         this.reason = reason;
14     }
15 
16     public int getErrorCode() {
17         return errorCode;
18     }
19 
20     public void setErrorCode(int errorCode) {
21         this.errorCode = errorCode;
22     }
23 
24 }

 

Segment message entity class:

 1 public class TopicMessageEntity extends MessageEntity {
 2 
 3     // Get the result of the paragraph
 4     private List<TopicEntity> result;
 5 
 6     public List<TopicEntity> getResult() {
 7         return result;
 8     }
 9 
10     public void setResult(List<TopicEntity> result) {
11         this.result = result;
12     }
13 
14 }

 

Section fruiting body class:

 1 public class TopicEntity {
 2 
 3     // Paragraph identifier
 4     private int id;
 5     // Scribe author
 6     private String author = "";
 7     // Paragraph title
 8     private String title = "";
 9     // Paragraph Point Ratio
10     private int upvote;
11     // Number of paragraph comments
12     private int commentCount;
13     // Paragraph thumbnail address
14     private String thumbNail = "";
15     // Address of paragraph original map
16     private String orgPicture = "";
17     // Time of Paragraph Publication
18     private String postTime = "";
19     
20     // Is it praise or tread?,0 For no point, 1 for praise.-1 Representative step
21     private int like = 0;
22 
23     public int getId() {
24         return id;
25     }
26 
27     public void setId(int id) {
28         this.id = id;
29     }
30 
31     public String getAuthor() {
32         return author;
33     }
34 
35     public void setAuthor(String author) {
36         this.author = author;
37     }
38 
39     public String getTitle() {
40         return title;
41     }
42 
43     public void setTitle(String title) {
44         this.title = title;
45     }
46 
47     public int getUpvote() {
48         return upvote;
49     }
50 
51     public void setUpvote(int upvote) {
52         this.upvote = upvote;
53     }
54 
55     public int getCommentCount() {
56         return commentCount;
57     }
58 
59     public void setCommentCount(int commentCount) {
60         this.commentCount = commentCount;
61     }
62 
63     public String getThumbNail() {
64         return thumbNail;
65     }
66 
67     public void setThumbNail(String thumbNail) {
68         this.thumbNail = thumbNail;
69     }
70 
71     public String getOrgPicture() {
72         return orgPicture;
73     }
74 
75     public void setOrgPicture(String orgPicture) {
76         this.orgPicture = orgPicture;
77     }
78 
79     public String getPostTime() {
80         return postTime;
81     }
82 
83     public void setPostTime(String postTime) {
84         this.postTime = postTime;
85     }
86 
87     public int getLike() {
88         return like;
89     }
90 
91     public void setLike(int like) {
92         this.like = like;
93     }
94 
95 }

 

This is slightly different from the database table, mainly like fields.

The like field represents whether or not the current person who gets the data points to the paragraph.

dao level:

Query paragraph method:

 1 public List<TopicEntity> query(int topicId, int count, boolean after) {
 2         List<TopicEntity> topicList = new ArrayList<TopicEntity>();
 3 
 4         if (topicId <= 0) {
 5             topicId = 0;
 6         }
 7 
 8         if (count <= 0) {
 9             count = 10;
10         }
11 
12         if (after) {
13             queryAfter(topicId, count, topicList);
14         } else {
15             queryBefore(topicId, count, topicList);
16         }
17 
18         return topicList;
19     }

 1 private void queryAfter(int topicId, int count, List<TopicEntity> topicList) {
 2         String queryAfter = "SELECT * FROM 9gag_topics WHERE id > ? LIMIT ?";
 3 
 4         Connection conn = DatabaseUtil.getConnection();
 5         PreparedStatement pstmt = null;
 6         ResultSet rs = null;
 7 
 8         try {
 9             pstmt = conn.prepareStatement(queryAfter);
10             pstmt.setInt(1, topicId);
11             pstmt.setInt(2, count);
12             rs = pstmt.executeQuery();
13 
14             while (rs.next()) {
15                 TopicEntity topicEntity = new TopicEntity();
16                 topicEntity.setId(rs.getInt("id"));
17                 topicEntity.setAuthor(rs.getString("author"));
18                 topicEntity.setTitle(rs.getString("title"));
19                 topicEntity.setUpvote(rs.getInt("upvote"));
20                 topicEntity.setCommentCount(rs.getInt("commentcount"));
21                 topicEntity.setThumbNail(rs.getString("thumbnail"));
22                 topicEntity.setOrgPicture(rs.getString("orgpicture"));
23                 topicEntity.setPostTime(rs.getString("posttime"));
24                 topicList.add(topicEntity);
25             }
26         } catch (SQLException e) {
27             e.printStackTrace();
28         } finally {
29             DatabaseUtil.close(conn, pstmt, rs);
30         }
31     }

 1 private void queryBefore(int topicId, int count, List<TopicEntity> topicList) {
 2         String queryBefore = "SELECT * FROM 9gag_topics WHERE id < ? ORDER BY id DESC LIMIT ?";
 3 
 4         Connection conn = DatabaseUtil.getConnection();
 5         PreparedStatement pstmt = null;
 6         ResultSet rs = null;
 7 
 8         try {
 9             pstmt = conn.prepareStatement(queryBefore);
10             pstmt.setInt(1, topicId);
11             pstmt.setInt(2, count);
12             rs = pstmt.executeQuery();
13 
14             while (rs.next()) {
15                 TopicEntity topicEntity = new TopicEntity();
16                 topicEntity.setId(rs.getInt("id"));
17                 topicEntity.setAuthor(rs.getString("author"));
18                 topicEntity.setTitle(rs.getString("title"));
19                 topicEntity.setUpvote(rs.getInt("upvote"));
20                 topicEntity.setCommentCount(rs.getInt("commentcount"));
21                 topicEntity.setThumbNail(rs.getString("thumbnail"));
22                 topicEntity.setOrgPicture(rs.getString("orgpicture"));
23                 topicEntity.setPostTime(rs.getString("posttime"));
24                 topicList.add(topicEntity);
25             }
26         } catch (SQLException e) {
27             e.printStackTrace();
28         } finally {
29             DatabaseUtil.close(conn, pstmt, rs);
30         }
31 
32         // Inverse order after getting the data, because the search is in reverse order
33         Collections.reverse(topicList);
34     }

 

These three methods implement querying count s before (or after) a specified paragraph.

servlet layer:

 1 protected void doPost(HttpServletRequest request, HttpServletResponse response)
 2             throws ServletException, IOException {
 3         response.setContentType("text/json; charset=utf-8");
 4         PrintWriter out = response.getWriter();
 5 
 6         TopicMessageEntity message = new TopicMessageEntity();
 7         TopicDAO topicDao = new TopicDAO();
 8         UpvoteDAO upvoteDao = new UpvoteDAO();
 9         Gson gson = GsonUtil.getGson();
10 
11         request.setCharacterEncoding("utf-8");
12         response.setCharacterEncoding("utf-8");
13 
14         int topicId = Integer.parseInt(request.getParameter("topicId"));
15         int count = Integer.parseInt(request.getParameter("count"));
16         boolean after = Boolean.parseBoolean(request.getParameter("after"));
17         String author = request.getParameter("author");
18 
19         if (count <= 0) {
20             message.setErrorCode(-1);
21             message.setReason("count Value cannot be negative!");
22             out.print(gson.toJson(message));
23             return;
24         }
25 
26         try {
27             List<TopicEntity> topics = topicDao.query(topicId, count, after);
28             
29             // Judging whether the author is overpraised
30             if (author != null) {
31                 List<UpvoteEntity> upvoteList = upvoteDao.findUpvoteByAuthor(author, true);
32                 if (upvoteList != null) {
33                     for (TopicEntity topic : topics) {
34                         for (UpvoteEntity upvote : upvoteList) {
35                             if (upvote.getLikedId() == topic.getId()) {
36                                 int like = upvote.isLiked() ? 1 : -1;
37                                 topic.setLike(like);
38                             }
39                         }
40                     }                    
41                 }
42             }
43             
44             Collections.reverse(topics);
45             message.setErrorCode(0);
46             message.setReason("success");
47             message.setResult(topics);
48         } catch (Exception e) {
49             message.setErrorCode(-1);
50             message.setReason(e.getMessage());
51         } finally {
52             out.print(gson.toJson(message));
53         }
54 
55     }

Main Logic: Find the required paragraph traverse the paragraph If the paragraph is clicked to praise or trample, change the corresponding field of the paragraph to praise or trample because the data found in the time sequence, to reverse display.

Reflect:

This time, we mainly reconstruct the design logic of the background, but there are still many imperfections.

Through this reconfiguration, a key point is made clear. To do a thing, first of all, we should plan well. First, we should design all the processes and frameworks, and then do them step by step. Only in this way can we make something better.

Otherwise, the process will be very chaotic, seriously affecting efficiency.

Notice:

The next chapter is about the logic of praise, because the logic of praise is more complex.

 

If you have any questions or suggestions, you can comment or mail The way to contact me, welcome your comments~