forumdao.java

jGossip是一个简单而功能强大的Java论坛软件（消息板）

                    .getForumQueries().getSql_SEARCH_QUERY_SUFF_COUNT()
                    + queryString.toString());
            rs = st.executeQuery();

            if (rs.next()) {
                recordsData.setRecordsCount(rs.getInt(1));
            }
        } finally {
            if (rs != null) {
                rs.close();
            }

            st.close();
            connection.close();
        }
    }

    /**
     * DOCUMENT ME!
     * 
     * @param tid
     * @param i
     * 
     * @throws SQLException
     *             DOCUMENT ME!
     */
    public void setThreadSortBy(String tid, int i) throws SQLException {
        Connection connection = this.dataSource.getConnection();

        PreparedStatement st = connection.prepareStatement(dbDriver
                .getQueries().getForumQueries().getSql_SET_THREAD_SORT_BY());

        try {
            st.setInt(1, i);
            st.setInt(2, Integer.parseInt(tid));
            st.execute();
        } finally {
            st.close();
            connection.close();
        }
    }

    /**
     * DOCUMENT ME!
     * 
     * @param map
     * 
     * @throws SQLException
     *             DOCUMENT ME!
     */
    public void updateConstants(Map map) throws SQLException {
        Connection connection = this.dataSource.getConnection();
        Set keySet = map.keySet();
        Iterator it = keySet.iterator();

        PreparedStatement st = null;

        try {
            st = connection.prepareStatement(dbDriver.getQueries()
                    .getForumQueries().getSql_UPDATE_CONSTANTS());

            while (it.hasNext()) {
                String key = (String) it.next();

                st.setString(1, (String) map.get(key));
                st.setString(2, key);
                st.execute();
            }
        } finally {
            if (st != null) {
                st.close();
            }

            connection.close();
        }
    }

    /**
     * DOCUMENT ME!
     * 
     * @param tid
     * @param uname
     *            DOCUMENT ME!
     * 
     * @return 
     * @throws SQLException
     *             DOCUMENT ME!
     */
    public ArrayList getSubscribersList(String tid, String uname)
            throws SQLException {
        ArrayList list = new ArrayList();
        Connection connection = this.dataSource.getConnection();
        PreparedStatement st = connection.prepareStatement(dbDriver
                .getQueries().getForumQueries().getSql_GET_SUBSCRIBERS_LIST());
        ResultSet rs = null;

        try {
            st.setInt(1, Integer.parseInt(tid));
            st.setString(2, uname);
            rs = st.executeQuery();

            while (rs.next()) {
                Subscriber s = new Subscriber();
                s.setEmail(rs.getString("user_mail"));
                s.setName(rs.getString("user_name"));
                list.add(s);
            }
        } finally {
            if (rs != null) {
                rs.close();
            }

            st.close();
            connection.close();
        }

        return list;
    }

    /**
     * DOCUMENT ME!
     * 
     * @param tid
     * @param email
     * @param name
     * 
     * @throws SQLException
     *             DOCUMENT ME!
     */
    public void subscribe(String tid, String email, String name)
            throws SQLException {
        Connection connection = this.dataSource.getConnection();
        PreparedStatement st = connection.prepareStatement(dbDriver
                .getQueries().getForumQueries().getSql_GET_SUBSCRIPTION());
        ResultSet rs = null;

        try {
            st.setInt(1, Integer.parseInt(tid));
            st.setString(2, email);
            st.setString(3, name);
            rs = st.executeQuery();

            if (!rs.next()) {
                st = connection.prepareStatement(dbDriver.getQueries()
                        .getForumQueries().getSql_ADD_SUBSCRIPTION());
                st.setInt(1, Integer.parseInt(tid));
                st.setString(2, email);
                st.setString(3, name);
                st.execute();
            }
        } finally {
            if (rs != null) {
                rs.close();
            }

            st.close();
            connection.close();
        }
    }

    /**
     * DOCUMENT ME!
     * 
     * @param email
     * @param name
     * @param tid
     * 
     * @return DOCUMENT ME!
     * 
     * @throws SQLException
     *             DOCUMENT ME!
     */
    public boolean unsubscribe(String email, String name, String tid)
            throws SQLException {
        Connection connection = this.dataSource.getConnection();

        PreparedStatement st = null;

        if (Integer.parseInt(tid) > 0) {
            st = connection.prepareStatement(dbDriver.getQueries()
                    .getForumQueries().getSql_GET_SUBSCRIPTION());
            st.setInt(1, Integer.parseInt(tid));
            st.setString(2, email);
            st.setString(3, name);
        } else {
            st = connection.prepareStatement(dbDriver.getQueries()
                    .getForumQueries().getSql_GET_SUBSCRIPTION_ALL());
            st.setString(1, email);
            st.setString(2, name);
        }

        ResultSet rs = null;
        boolean success = false;

        try {
            rs = st.executeQuery();

            if (rs.next()) {
                if (Integer.parseInt(tid) > 0) {
                    st = connection.prepareStatement(dbDriver.getQueries()
                            .getForumQueries().getSql_DELETE_SUBSCRIPTION());
                    st.setInt(1, Integer.parseInt(tid));
                    st.setString(2, email);
                    st.setString(3, name);
                } else {
                    st = connection
                            .prepareStatement(dbDriver.getQueries()
                                    .getForumQueries()
                                    .getSql_DELETE_SUBSCRIPTION_ALL());
                    st.setString(1, email);
                    st.setString(2, name);
                }

                st.execute();
                success = true;
            }
        } finally {
            if (rs != null) {
                rs.close();
            }

            st.close();
            connection.close();
        }

        return success;
    }

    /**
     * DOCUMENT ME!
     * 
     * @param user
     * @param recordsData
     * @param block
     * 
     * @throws InstantiationException
     *             DOCUMENT ME!
     * @throws IllegalAccessException
     *             DOCUMENT ME!
     * @throws InvocationTargetException
     *             DOCUMENT ME!
     * @throws NoSuchMethodException
     *             DOCUMENT ME!
     * @throws SQLException
     *             DOCUMENT ME!
     */
    public void fillSubscriptionList(User user, RecordsData recordsData,
            String block) throws InstantiationException,
            IllegalAccessException, InvocationTargetException,
            NoSuchMethodException, SQLException {
        Connection connection = this.dataSource.getConnection();
        PreparedStatement st = connection.prepareStatement(dbDriver
                .getQueries().getForumQueries().getSql_COUNT_SUBSCRIPTIONS());
        ResultSet rs = null;

        try {
            st.setString(1, user.getName());
            rs = (ResultSet) st.executeQuery();

            if (rs.next()) {
                int blockSize=user.getSettings().getMes_per_page() * 2;
                int currBlock=Integer.parseInt(block);
                
                recordsData.setRecordsCount(rs.getInt(1));
                recordsData.setBlockSize(blockSize);
                recordsData.setCurrBlock(currBlock);
                st = connection.prepareStatement(dbDriver.getQueries()
                        .getForumQueries().getSql_GET_USER_SUBSCRIPTIONS());
                st.setString(1, user.getName());
                st.setInt(2, currBlock);
                st.setInt(3, blockSize);
                rs = st.executeQuery();
                recordsData.fillRecords(rs,
                        Mapping.getInstance().SubscriptionMapping,
                        NewTopic.class);
            }
        } finally {
            if (rs != null) {
                rs.close();
            }

            st.close();
            connection.close();
        }
    }

    /**
     * DOCUMENT ME!
     * 
     * @param period
     * 
     * @return 
     * @throws SQLException
     *             DOCUMENT ME!
     */
    public int[] dropOld(String period) throws SQLException {
        Connection connection = this.dataSource.getConnection();
        PreparedStatement st = connection.prepareStatement(dbDriver
                .getQueries().getForumQueries().getSql_GET_OLD_TOPICS());
        ResultSet rs = null;
        int[] wasDeleted = new int[2];

        try {
            st.setInt(1, Integer.parseInt(period));
            rs = (ResultSet) st.executeQuery();

            while (rs.next()) {
                wasDeleted[0]++;
                wasDeleted[1] += rs.getInt("cc");
                this.deleteThread(rs.getString("threadid"), true);
            }
        } finally {
            if (rs != null) {
                rs.close();
            }

            st.close();
            connection.close();
        }

        return wasDeleted;
    }

    /**
     * DOCUMENT ME!
     * 
     * @param user
     * @param recordsData
     * @param block
     * 
     * @throws InstantiationException
     *             DOCUMENT ME!
     * @throws IllegalAccessException
     *             DOCUMENT ME!
     * @throws InvocationTargetException
     *             DOCUMENT ME!
     * @throws NoSuchMethodException
     *             DOCUMENT ME!
     * @throws SQLException
     *             DOCUMENT ME!
     */
    public void fillLastUpdatedTopicList(User user, RecordsData recordsData,
            String block) throws InstantiationException,
            IllegalAccessException, InvocationTargetException,
            NoSuchMethodException, SQLException {
        Connection connection = this.dataSource.getConnection();
        PreparedStatement st = null;

        if (user.getStatus() < 7) {
            st = connection.prepareStatement(dbDriver.getQueries()
                    .getForumQueries().getSql_GET_LAST_UPDATED_TOPICS());
        } else {
            st = connection.prepareStatement(dbDriver.getQueries()
                    .getForumQueries().getSql_GET_LAST_UPDATED_TOPICS_ALL());
        }

        ResultSet rs = null;

        try {
            int blockSize=user.getSettings().getMes_per_page() * 2;
            int currBlock=Integer.parseInt(block);
            st.setTimestamp(1, new Timestamp(user.getIntime().getTime()));
            st.setInt(2, currBlock);
            st.setInt(3, blockSize);
            rs = (ResultSet) st.executeQuery();
            recordsData.fillRecords(rs, Mapping.getInstance().NewTreadMapping,
                    NewTopic.class);
            recordsData.setBlockSize(blockSize);

            recordsData.setCurrBlock(currBlock);
            Iterator it = recordsData.getRecords().iterator();
            st = connection.prepareStatement(dbDriver.getQueries()
                    .getForumQueries().getSql_GET_THREAD_LAST_MESS());

            while (it.hasNext()) {
                NewTopic nt = (NewTopic) it.next();
                setLastMessage(nt, st);
                nt.setForumName(getForumInfo(nt.getForumid()).getTitle());
            }

            if (user.getStatus() < 7) {
                st = connection.prepareStatement(dbDriver.getQueries()
                        .getForumQueries().getSql_COUNT_NEW_THREADS());
            } else {
                st = connection.prepareStatement(dbDriver.getQueries()
                        .getForumQueries().getSql_COUNT_NEW_THREADS_ALL());
            }

            st.setTimestamp(1, new Timestamp(user.getIntime().getTime()));
            rs = (ResultSet) st.executeQuery();
            rs.next();
            recordsData.setRecordsCount(rs.getInt(1));
        } finally {
            if (rs != null) {
                rs.close();
            }

            st.close();
            connection.close();
        }
    }

    /**
     * DOCUMENT ME!
     * 
     * @param user
     * @param form
     * 
     * @return 
     * @throws SQLException
     *             DOCUMENT ME!
     */
    public int getMessBlock(User user, ProcessMessageForm form)
            throws SQLException {
        int block = 0;
        Connection connection = this.dataSource.getConnection();
        PreparedStatement st = connection.prepareStatement(dbDriver
                .getQueries().getForumQueries().getSql_GET_MESS_INTIME());
        ResultSet rs = null;

        try {
            st.setI