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.Statement ; 7 import java.util.Date ; 8 9 import org.apache.commons.logging.Log; 10 import org.apache.commons.logging.LogFactory; 11 import org.javabb.dao.entity.IUserRankDAO; 12 import org.springframework.jdbc.CannotGetJdbcConnectionException; 13 import org.springframework.jdbc.core.support.JdbcDaoSupport; 14 15 30 31 35 public class JdbcUserRankDAO extends JdbcDaoSupport implements IUserRankDAO { 36 37 private final Log log = LogFactory.getLog(this.getClass()); 38 39 public void refreshUserRankByForum(Long forumId) { 40 41 log.debug("Refreshing UserRankByForum..."); 42 43 44 ResultSet rs = null; 45 PreparedStatement stmtInsert = null; 46 47 try { 48 StringBuffer sql = new StringBuffer (); 49 sql.append(" select u.id_user, count(p.id_user) as cnt from jbb_users as u "); 50 sql.append(" inner join jbb_posts as p on p.id_user = u.id_user"); 51 sql.append(" inner join jbb_topics as t on p.id_topic = t.id_topic"); 52 sql.append(" inner join jbb_forum as f on t.id_forum = f.id_forum"); 53 sql.append(" where f.id_forum = " + forumId); 54 sql.append(" group by u.id_user, p.id_user"); 55 sql.append(" order by cnt desc"); 56 sql.append(" limit 10; "); 57 rs = this.getConnection().createStatement().executeQuery(sql.toString()); 58 59 StringBuffer sqlInsert = new StringBuffer (); 60 sqlInsert.append(" INSERT INTO jbb_forum_top_user (ID_USER, POST_COUNT, DATE_ROW, ID_FORUM) "); 61 sqlInsert.append(" VALUES(?,?,?,?) "); 62 stmtInsert = this.getConnection().prepareStatement(sqlInsert.toString()); 63 64 while(rs.next()){ 65 stmtInsert.setLong(1, rs.getLong(1)); 66 stmtInsert.setLong(2, rs.getLong(2)); 67 stmtInsert.setDate(3, null); 68 stmtInsert.setLong(4, forumId.longValue()); 69 stmtInsert.addBatch(); 70 } 71 72 stmtInsert.executeBatch(); 74 75 } catch (SQLException e) { 76 log.error("SQL Exception error:" + e.getMessage()); 77 } catch (Exception e) { 78 log.error("Exception error:" + e.getMessage()); 79 }finally{ 80 try { 81 rs.close(); 82 rs = null; 83 stmtInsert.close(); 84 stmtInsert = null; 85 } catch (SQLException e) {} 86 } 87 88 log.debug("UserRankByForum: ok!"); 89 } 90 91 92 95 public void cleanAllUserRank(){ 96 Statement stmt = null; 97 try { 98 log.debug("Cleaning UserRank Table..."); 99 stmt = this.getConnection().createStatement(); 100 stmt.executeUpdate("DELETE FROM jbb_forum_top_user"); 101 log.debug("Table was been cleaned"); 102 } catch (CannotGetJdbcConnectionException e) { 103 e.printStackTrace(); 104 } catch (SQLException e) { 105 e.printStackTrace(); 106 }finally{ 107 try { 108 stmt.close(); 109 stmt = null; 110 } catch (SQLException e) {} 111 } 112 } 113 114 115 public void refreshSession(Object obj) {} 116 } | Popular Tags |