KickJava   Java API By Example, From Geeks To Geeks.

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


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.Statement JavaDoc;
7 import java.util.Date JavaDoc;
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 /*
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: JdbcUserRankDAO.java,v 1.1.2.3.2.1.2.2 2006/04/17 17:47:29 daltoncamargo Exp $
33  * @author Dalton Camargo - dalton@javabb.org
34  */

35 public class JdbcUserRankDAO extends JdbcDaoSupport implements IUserRankDAO {
36
37     private final Log log = LogFactory.getLog(this.getClass());
38
39     public void refreshUserRankByForum(Long JavaDoc forumId) {
40
41         log.debug("Refreshing UserRankByForum...");
42         
43
44         ResultSet JavaDoc rs = null;
45         PreparedStatement JavaDoc stmtInsert = null;
46         
47         try {
48             StringBuffer JavaDoc sql = new StringBuffer JavaDoc();
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 JavaDoc sqlInsert = new StringBuffer JavaDoc();
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             //Insert all USERS_RANK
73
stmtInsert.executeBatch();
74             
75         } catch (SQLException JavaDoc e) {
76             log.error("SQL Exception error:" + e.getMessage());
77         } catch (Exception JavaDoc 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 JavaDoc e) {}
86         }
87
88         log.debug("UserRankByForum: ok!");
89     }
90    
91     
92     /**
93      * Clean the jbb_forum_top_user table
94      */

95     public void cleanAllUserRank(){
96         Statement JavaDoc 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 JavaDoc e) {
105             e.printStackTrace();
106         }finally{
107              try {
108                 stmt.close();
109                 stmt = null;
110             } catch (SQLException JavaDoc e) {}
111         }
112     }
113
114     
115     public void refreshSession(Object JavaDoc obj) {}
116 }
Popular Tags