1 package org.javabb.dao.jdbc; 2 3 import java.sql.PreparedStatement ; 4 import java.sql.ResultSet ; 5 import java.sql.SQLException ; 6 import java.sql.Timestamp ; 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 30 31 37 public class JdbcRefreshStatsDAO extends JdbcDaoSupport implements IRefreshStatsDAO { 38 39 private final Log log = LogFactory.getLog(JdbcRefreshStatsDAO.class); 40 41 public void refreshForum(Long forumId) { 42 43 log.debug("Refreshing forums..."); 44 45 StringBuffer sql = new StringBuffer (); 46 int topicCount = 0; 48 int postCount = 0; 49 50 Long postId = new Long (0); 52 Timestamp postDate = null; 53 Long userId = new Long (0); 54 String userName = ""; 55 56 try { 57 sql.append(" SELECT COUNT(id_topic) "); 59 sql.append(" FROM jbb_topics WHERE id_forum = " + forumId); 60 61 ResultSet rsTopicCount = this.getConnection().createStatement().executeQuery(sql.toString()); 62 63 rsTopicCount.next(); 64 topicCount = rsTopicCount.getInt(1); 65 rsTopicCount.close(); 66 rsTopicCount = null; 67 68 sql = null; 70 sql = new StringBuffer (); 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 rsPostCount = this.getConnection().createStatement().executeQuery(sql.toString()); 77 rsPostCount.next(); 78 postCount = rsPostCount.getInt(1); 79 rsPostCount.close(); 80 rsPostCount = null; 81 82 sql = null; 84 sql = new StringBuffer (); 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 rsLastPost = this.getConnection().createStatement().executeQuery(sql.toString()); 96 97 if (rsLastPost.next()) { 98 postId = new Long (rsLastPost.getLong(1)); 99 postDate = rsLastPost.getTimestamp(2); 100 userId = new Long (rsLastPost.getLong(3)); 101 userName = rsLastPost.getString(4); 102 } 103 104 rsLastPost.close(); 105 rsLastPost = null; 106 107 sql = null; 109 sql = new StringBuffer (); 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 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 e) { 131 log.error("SQL Exception error:" + e.getMessage()); 132 } catch (Exception e) { 133 log.error("Exception error:" + e.getMessage()); 134 } 135 136 log.debug("Forums: ok!"); 137 } 138 139 public void refreshTopic(Long topicId) { 140 141 log.debug("Refreshing topics..."); 142 StringBuffer sql = new StringBuffer (); 143 144 int forumId = 0; 146 int topicCount = 0; 147 int postCount = 0; 148 149 Long postId = new Long (0); 151 Timestamp postDate = null; 152 Long userId = new Long (0); 153 String userName = ""; 154 int totalPages = 0; 155 156 try { 157 sql.append(" SELECT id_forum "); 159 sql.append(" FROM jbb_topics WHERE id_topic = " + topicId); 160 161 ResultSet rsTopicDetail = this.getConnection().createStatement().executeQuery(sql.toString()); 162 163 rsTopicDetail.next(); 164 forumId = rsTopicDetail.getInt(1); 165 rsTopicDetail.close(); 166 rsTopicDetail = null; 167 168 sql = null; 170 sql = new StringBuffer (); 171 sql.append(" SELECT COUNT(id_topic) "); 172 sql.append(" FROM jbb_topics WHERE id_forum = " + forumId); 173 174 ResultSet rsTopicCount = this.getConnection().createStatement().executeQuery(sql.toString()); 175 176 rsTopicCount.next(); 177 topicCount = rsTopicCount.getInt(1); 178 rsTopicCount.close(); 179 rsTopicCount = null; 180 181 sql = null; 183 sql = new StringBuffer (); 184 sql.append(" UPDATE jbb_forum SET topic_count = ? "); 185 sql.append(" WHERE id_forum = ?"); 186 187 PreparedStatement 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 sql = null; 199 sql = new StringBuffer (); 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 sql = null; 213 sql = new StringBuffer (); 214 sql.append(" SELECT COUNT(id_post) "); 215 sql.append(" FROM jbb_posts WHERE id_topic = " + topicId); 216 217 ResultSet rsPostsCount = this.getConnection().createStatement().executeQuery(sql.toString()); 218 219 rsPostsCount.next(); 220 postCount = rsPostsCount.getInt(1); rsPostsCount.close(); 222 rsPostsCount = null; 223 224 sql = null; 226 sql = new StringBuffer (); 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 rsLastPost = this.getConnection().createStatement().executeQuery(sql.toString()); 235 236 if (rsLastPost.next()) { 237 postId = new Long (rsLastPost.getLong(1)); 238 postDate = rsLastPost.getTimestamp(2); 239 userName = rsLastPost.getString(3); 240 userId = new Long (rsLastPost.getLong(4)); 241 } 242 243 rsLastPost.close(); 244 rsLastPost = null; 245 246 int rowsPerPage = ConfigurationFactory.getConf().postsPage.intValue(); 248 totalPages = Paging.getNroPages(rowsPerPage, postCount); 249 250 sql = null; 252 sql = new StringBuffer (); 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 e) { 273 log.error("SQL Exception error:" + e.getMessage()); 274 } 275 276 log.debug("Topics: ok!"); 277 278 } 279 280 public void refreshPost(Long postId) { 281 282 log.debug("Refreshing posts..."); 283 284 StringBuffer sql = new StringBuffer (); 285 286 int topicId = 0; 288 int forumId = 0; 289 int userId = 0; 290 String userName = ""; 291 int topicCount = 0; 292 int postCount = 0; 293 int postCountByTopic = 0; 294 Timestamp postDate = null; 295 int totalPages = 0; 296 297 try { 298 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 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 sql = null; 315 sql = new StringBuffer (); 316 sql.append(" SELECT COUNT(id_post) "); 317 sql.append(" FROM jbb_posts"); 318 sql.append(" WHERE id_topic = " + topicId); 319 320 ResultSet rsPostCountByTopic = this.getConnection().createStatement().executeQuery(sql.toString()); 321 322 rsPostCountByTopic.next(); 323 postCountByTopic = rsPostCountByTopic.getInt(1); 324 rsPostCountByTopic.close(); 325 rsPostCountByTopic = null; 326 327 sql = null; 329 sql = new StringBuffer (); 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 rsPostsCount = this.getConnection().createStatement().executeQuery(sql.toString()); 336 337 rsPostsCount.next(); 338 postCount = rsPostsCount.getInt(1); 339 rsPostsCount.close(); 340 rsPostsCount = null; 341 342 sql = null; 344 sql = new StringBuffer (); 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 rsLastPost = this.getConnection().createStatement().executeQuery(sql.toString()); 356 357 if (rsLastPost.next()) { 358 postId = new Long (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 int rowsPerPage = ConfigurationFactory.getConf().postsPage.intValue(); 369 totalPages = Paging.getNroPages(rowsPerPage, postCountByTopic); 370 371 sql = null; 373 sql = new StringBuffer (); 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 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 e) { 395 log.error("SQL Exception error:" + e.getMessage()); 396 } 397 398 log.debug("Posts: ok!"); 399 } 400 401 404 public void refreshSession(Object obj) {} 405 406 } | Popular Tags |