KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > javabb > dao > jdbc > JdbcRefreshStatsDAO


1 package org.javabb.dao.jdbc;
2
3 import java.sql.PreparedStatement JavaDoc;
4 import java.sql.ResultSet JavaDoc;
5 import java.sql.SQLException JavaDoc;
6 import java.sql.Timestamp JavaDoc;
7
8 import org.apache.commons.logging.Log;
9 import org.apache.commons.logging.LogFactory;
10 import org.javabb.dao.entity.IRefreshStatsDAO;
11 import org.javabb.infra.ConfigurationFactory;
12 import org.javabb.infra.Paging;
13 import org.springframework.jdbc.core.support.JdbcDaoSupport;
14
15 /*
16  * Copyright 2004 JavaFree.org
17  *
18  * Licensed under the Apache License, Version 2.0 (the "License");
19  * you may not use this file except in compliance with the License.
20  * You may obtain a copy of the License at
21  *
22  * http://www.apache.org/licenses/LICENSE-2.0
23  *
24  * Unless required by applicable law or agreed to in writing, software
25  * distributed under the License is distributed on an "AS IS" BASIS,
26  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
27  * See the License for the specific language governing permissions and
28  * limitations under the License.
29  */

30
31 /**
32  * $Id: JdbcRefreshStatsDAO.java,v 1.5.2.1.2.1.2.2 2006/04/17 17:47:30 daltoncamargo Exp $
33  *
34  * @author Dalton Camargo - <a HREF="mailto:dalton@javabb.org">dalton@javabb.org
35  * </a>
36  */

37 public class JdbcRefreshStatsDAO extends JdbcDaoSupport implements IRefreshStatsDAO {
38
39     private final Log log = LogFactory.getLog(JdbcRefreshStatsDAO.class);
40
41     public void refreshForum(Long JavaDoc forumId) {
42
43         log.debug("Refreshing forums...");
44
45         StringBuffer JavaDoc sql = new StringBuffer JavaDoc();
46         // Attributes
47
int topicCount = 0;
48         int postCount = 0;
49
50         // Last post
51
Long JavaDoc postId = new Long JavaDoc(0);
52         Timestamp JavaDoc postDate = null;
53         Long JavaDoc userId = new Long JavaDoc(0);
54         String JavaDoc userName = "";
55
56         try {
57             // Number of topics
58
sql.append(" SELECT COUNT(id_topic) ");
59             sql.append(" FROM jbb_topics WHERE id_forum = " + forumId);
60
61             ResultSet JavaDoc rsTopicCount = this.getConnection().createStatement().executeQuery(sql.toString());
62
63             rsTopicCount.next();
64             topicCount = rsTopicCount.getInt(1);
65             rsTopicCount.close();
66             rsTopicCount = null;
67
68             // Number of Posts..
69
sql = null;
70             sql = new StringBuffer JavaDoc();
71             sql.append(" SELECT COUNT(jbb_posts.id_post) FROM jbb_posts,jbb_topics, jbb_forum ");
72             sql.append(" WHERE jbb_posts.id_topic = jbb_topics.id_topic AND ");
73             sql.append(" jbb_topics.id_forum = jbb_forum.id_forum AND ");
74             sql.append(" jbb_forum.id_forum = " + forumId);
75
76             ResultSet JavaDoc rsPostCount = this.getConnection().createStatement().executeQuery(sql.toString());
77             rsPostCount.next();
78             postCount = rsPostCount.getInt(1);
79             rsPostCount.close();
80             rsPostCount = null;
81
82             // Last post by Forum..
83
sql = null;
84             sql = new StringBuffer JavaDoc();
85             sql.append(" SELECT ");
86             sql.append(" jbb_posts.id_post, jbb_posts.data_post, jbb_users.id_user, jbb_users.user_name");
87             sql.append(" FROM jbb_posts, jbb_topics, jbb_forum, jbb_users");
88             sql.append(" WHERE jbb_posts.id_topic = jbb_topics.id_topic AND");
89             sql.append(" jbb_topics.id_forum = jbb_forum.id_forum AND");
90             sql.append(" jbb_posts.id_user = jbb_users.id_user AND");
91             sql.append(" jbb_forum.id_forum = " + forumId);
92             sql.append(" ORDER BY jbb_posts.data_post DESC");
93             sql.append(" LIMIT 1");
94
95             ResultSet JavaDoc rsLastPost = this.getConnection().createStatement().executeQuery(sql.toString());
96
97             if (rsLastPost.next()) {
98                 postId = new Long JavaDoc(rsLastPost.getLong(1));
99                 postDate = rsLastPost.getTimestamp(2);
100                 userId = new Long JavaDoc(rsLastPost.getLong(3));
101                 userName = rsLastPost.getString(4);
102             }
103
104             rsLastPost.close();
105             rsLastPost = null;
106
107             // Forum updating...
108
sql = null;
109             sql = new StringBuffer JavaDoc();
110             sql.append(" UPDATE jbb_forum SET post_count = ?, topic_count = ?, last_post_id = ?, ");
111             sql.append(" last_post_user_id = ?, last_post_user_name = ?, last_post_date = ? ");
112             sql.append(" WHERE id_forum = ?");
113
114             PreparedStatement JavaDoc pStmt = this.getConnection().prepareStatement(sql.toString());
115
116             pStmt.setInt(1, postCount);
117             pStmt.setInt(2, topicCount);
118             pStmt.setInt(3, postId.intValue());
119             pStmt.setInt(4, userId.intValue());
120             pStmt.setString(5, userName);
121             pStmt.setTimestamp(6, postDate);
122             pStmt.setInt(7, forumId.intValue());
123
124             pStmt.executeUpdate();
125
126             sql = null;
127             pStmt.close();
128             pStmt = null;
129
130         } catch (SQLException JavaDoc e) {
131             log.error("SQL Exception error:" + e.getMessage());
132         } catch (Exception JavaDoc e) {
133             log.error("Exception error:" + e.getMessage());
134         }
135
136         log.debug("Forums: ok!");
137     }
138
139     public void refreshTopic(Long JavaDoc topicId) {
140
141         log.debug("Refreshing topics...");
142         StringBuffer JavaDoc sql = new StringBuffer JavaDoc();
143
144         // Attributes
145
int forumId = 0;
146         int topicCount = 0;
147         int postCount = 0;
148
149         // Last post
150
Long JavaDoc postId = new Long JavaDoc(0);
151         Timestamp JavaDoc postDate = null;
152         Long JavaDoc userId = new Long JavaDoc(0);
153         String JavaDoc userName = "";
154         int totalPages = 0;
155
156         try {
157             // Get forum details
158
sql.append(" SELECT id_forum ");
159             sql.append(" FROM jbb_topics WHERE id_topic = " + topicId);
160
161             ResultSet JavaDoc rsTopicDetail = this.getConnection().createStatement().executeQuery(sql.toString());
162
163             rsTopicDetail.next();
164             forumId = rsTopicDetail.getInt(1);
165             rsTopicDetail.close();
166             rsTopicDetail = null;
167
168             // Number of topics
169
sql = null;
170             sql = new StringBuffer JavaDoc();
171             sql.append(" SELECT COUNT(id_topic) ");
172             sql.append(" FROM jbb_topics WHERE id_forum = " + forumId);
173
174             ResultSet JavaDoc rsTopicCount = this.getConnection().createStatement().executeQuery(sql.toString());
175
176             rsTopicCount.next();
177             topicCount = rsTopicCount.getInt(1);
178             rsTopicCount.close();
179             rsTopicCount = null;
180
181             // Forum updating.....
182
sql = null;
183             sql = new StringBuffer JavaDoc();
184             sql.append(" UPDATE jbb_forum SET topic_count = ? ");
185             sql.append(" WHERE id_forum = ?");
186
187             PreparedStatement JavaDoc pStmt = this.getConnection().prepareStatement(sql.toString());
188
189             pStmt.setInt(1, topicCount);
190             pStmt.setInt(2, forumId);
191
192             pStmt.executeUpdate();
193
194             pStmt.close();
195             pStmt = null;
196
197             // Topic model updating.....
198
sql = null;
199             sql = new StringBuffer JavaDoc();
200             sql.append(" UPDATE jbb_topics SET topic_model = 0 ");
201             sql.append(" WHERE topic_model is null");
202
203             pStmt = this.getConnection().prepareStatement(sql.toString());
204
205             pStmt.executeUpdate();
206
207             pStmt.close();
208             pStmt = null;
209             
210             
211             // Number of posts
212
sql = null;
213             sql = new StringBuffer JavaDoc();
214             sql.append(" SELECT COUNT(id_post) ");
215             sql.append(" FROM jbb_posts WHERE id_topic = " + topicId);
216
217             ResultSet JavaDoc rsPostsCount = this.getConnection().createStatement().executeQuery(sql.toString());
218
219             rsPostsCount.next();
220             postCount = rsPostsCount.getInt(1); //Answers
221
rsPostsCount.close();
222             rsPostsCount = null;
223
224             // Last post by Topic..
225
sql = null;
226             sql = new StringBuffer JavaDoc();
227             sql.append(" SELECT jbb_posts.id_post, jbb_posts.data_post, jbb_users.user_name, jbb_users.id_user ");
228             sql.append(" FROM jbb_posts, jbb_users ");
229             sql.append(" WHERE id_topic = " + topicId);
230             sql.append(" AND jbb_posts.id_user = jbb_users.id_user ");
231             sql.append(" ORDER BY data_post ");
232             sql.append(" DESC LIMIT 1 ");
233
234             ResultSet JavaDoc rsLastPost = this.getConnection().createStatement().executeQuery(sql.toString());
235
236             if (rsLastPost.next()) {
237                 postId = new Long JavaDoc(rsLastPost.getLong(1));
238                 postDate = rsLastPost.getTimestamp(2);
239                 userName = rsLastPost.getString(3);
240                 userId = new Long JavaDoc(rsLastPost.getLong(4));
241             }
242
243             rsLastPost.close();
244             rsLastPost = null;
245
246             // Last post page
247
int rowsPerPage = ConfigurationFactory.getConf().postsPage.intValue();
248             totalPages = Paging.getNroPages(rowsPerPage, postCount);
249
250             // Topic updating...
251
sql = null;
252             sql = new StringBuffer JavaDoc();
253             sql.append(" UPDATE jbb_topics SET last_post_user_name = ?, last_post_id = ?, last_post_date = ?, ");
254             sql.append(" last_post_user_id = ?, last_post_page = ? ");
255             sql.append(" WHERE id_topic = ?");
256
257             pStmt = this.getConnection().prepareStatement(sql.toString());
258
259             pStmt.setString(1, userName);
260             pStmt.setInt(2, postId.intValue());
261             pStmt.setTimestamp(3, postDate);
262             pStmt.setInt(4, userId.intValue());
263             pStmt.setInt(5, totalPages);
264             pStmt.setInt(6, topicId.intValue());
265
266             pStmt.executeUpdate();
267             
268             sql = null;
269             pStmt.close();
270             pStmt = null;
271
272         } catch (SQLException JavaDoc e) {
273             log.error("SQL Exception error:" + e.getMessage());
274         }
275
276         log.debug("Topics: ok!");
277
278     }
279
280     public void refreshPost(Long JavaDoc postId) {
281
282         log.debug("Refreshing posts...");
283
284         StringBuffer JavaDoc sql = new StringBuffer JavaDoc();
285
286         // Attributes
287
int topicId = 0;
288         int forumId = 0;
289         int userId = 0;
290         String JavaDoc userName = "";
291         int topicCount = 0;
292         int postCount = 0;
293         int postCountByTopic = 0;
294         Timestamp JavaDoc postDate = null;
295         int totalPages = 0;
296
297         try {
298             // Get forum details
299
sql.append(" SELECT jbb_topics.id_topic, jbb_topics.id_forum");
300             sql.append(" FROM jbb_topics, jbb_posts ");
301             sql.append(" WHERE jbb_topics.id_topic = jbb_posts.id_topic");
302             sql.append(" AND jbb_posts.id_post = " + postId);
303             sql.append(" LIMIT 1");
304
305             ResultSet JavaDoc rsPostDetail = this.getConnection().createStatement().executeQuery(sql.toString());
306
307             rsPostDetail.next();
308             topicId = rsPostDetail.getInt(1);
309             forumId = rsPostDetail.getInt(2);
310             rsPostDetail.close();
311             rsPostDetail = null;
312
313             // Number of posts by topic
314
sql = null;
315             sql = new StringBuffer JavaDoc();
316             sql.append(" SELECT COUNT(id_post) ");
317             sql.append(" FROM jbb_posts");
318             sql.append(" WHERE id_topic = " + topicId);
319
320             ResultSet JavaDoc rsPostCountByTopic = this.getConnection().createStatement().executeQuery(sql.toString());
321
322             rsPostCountByTopic.next();
323             postCountByTopic = rsPostCountByTopic.getInt(1);
324             rsPostCountByTopic.close();
325             rsPostCountByTopic = null;
326
327             // Number of posts by forum
328
sql = null;
329             sql = new StringBuffer JavaDoc();
330             sql.append(" SELECT COUNT(id_post) ");
331             sql.append(" FROM jbb_posts, jbb_topics ");
332             sql.append(" WHERE jbb_posts.id_topic = jbb_topics.id_topic ");
333             sql.append(" AND jbb_topics.id_forum = " + forumId);
334
335             ResultSet JavaDoc rsPostsCount = this.getConnection().createStatement().executeQuery(sql.toString());
336
337             rsPostsCount.next();
338             postCount = rsPostsCount.getInt(1);
339             rsPostsCount.close();
340             rsPostsCount = null;
341
342             // Last post by Forum..
343
sql = null;
344             sql = new StringBuffer JavaDoc();
345             sql.append(" SELECT ");
346             sql.append(" jbb_posts.id_post, jbb_posts.data_post, jbb_users.id_user, jbb_users.user_name");
347             sql.append(" FROM jbb_posts, jbb_topics, jbb_forum, jbb_users");
348             sql.append(" WHERE jbb_posts.id_topic = jbb_topics.id_topic AND");
349             sql.append(" jbb_topics.id_forum = jbb_forum.id_forum AND");
350             sql.append(" jbb_posts.id_user = jbb_users.id_user AND");
351             sql.append(" jbb_forum.id_forum = " + forumId);
352             sql.append(" ORDER BY jbb_posts.data_post DESC");
353             sql.append(" LIMIT 1");
354
355             ResultSet JavaDoc rsLastPost = this.getConnection().createStatement().executeQuery(sql.toString());
356
357             if (rsLastPost.next()) {
358                 postId = new Long JavaDoc(rsLastPost.getLong(1));
359                 postDate = rsLastPost.getTimestamp(2);
360                 userId = rsLastPost.getInt(3);
361                 userName = rsLastPost.getString(4);
362             }
363
364             rsLastPost.close();
365             rsLastPost = null;
366
367             // Last post page
368
int rowsPerPage = ConfigurationFactory.getConf().postsPage.intValue();
369             totalPages = Paging.getNroPages(rowsPerPage, postCountByTopic);
370
371             // Forum updating...
372
sql = null;
373             sql = new StringBuffer JavaDoc();
374             sql.append(" UPDATE jbb_forum SET post_count = ?, last_post_id = ?, ");
375             sql.append(" last_post_user_id = ?, last_post_user_name = ?, last_post_date = ?, last_page_post = ? ");
376             sql.append(" WHERE id_forum = ?");
377
378             PreparedStatement JavaDoc pStmt = this.getConnection().prepareStatement(sql.toString());
379
380             pStmt.setInt(1, postCount);
381             pStmt.setInt(2, postId.intValue());
382             pStmt.setInt(3, userId);
383             pStmt.setString(4, userName);
384             pStmt.setTimestamp(5, postDate);
385             pStmt.setInt(6, totalPages);
386             pStmt.setInt(7, forumId);
387
388             pStmt.executeUpdate();
389
390             sql = null;
391             pStmt.close();
392             pStmt = null;
393
394         } catch (SQLException JavaDoc e) {
395             log.error("SQL Exception error:" + e.getMessage());
396         }
397         
398         log.debug("Posts: ok!");
399     }
400
401     /**
402      * Do nothing
403      */

404     public void refreshSession(Object JavaDoc obj) {}
405    
406 }
Popular Tags