threaddaomysql.java

Chinaxp 论坛源代码

    public Collection findByParnetID( final long parentID ) throws ThreadNotFoundException, DAOException{
        final ArrayList result = new ArrayList();
        try{
            final Connection conn = getConnection();
            final PreparedStatement stat1
                    = conn.prepareStatement( "select * from threads where " + PersistentThread.PROPERTY_PARENT_ID
                    + "= -1" + " and "+ PersistentThread.PROPERTY_ID
                    + "="+ parentID +" order by " + PersistentThread.PROPERTY_TIMESTAMP );
            final ResultSet resultSet1 = stat1.executeQuery();

            if( resultSet1.next() ){
                PersistentThread pt = internalMapResultSet(resultSet1);
                result.add(pt);
            }  else {
                throw new ThreadNotFoundException("No thread found with parentid: "+ parentID);
            }

            stat1.close();

            final PreparedStatement stat2
                    = conn.prepareStatement( "select * from threads where " + PersistentThread.PROPERTY_PARENT_ID
                    + "= " + parentID +" order by " + PersistentThread.PROPERTY_TIMESTAMP );
            final ResultSet resultSet2 = stat2.executeQuery();

            while( resultSet2.next() ){
                PersistentThread pt = internalMapResultSet(resultSet2);
                result.add( pt);
            }
            stat2.close();

            conn.close();

            if(result.isEmpty()) throw new ThreadNotFoundException("No thread found with parentid: "+ parentID);
        } catch (SQLException e) {
            throw new DAOException( e );
        }
        return result;

    }

    /**
     *  Get all the children threads given a id of a parent threa
     *
     *  @param parentID - The id of a parnt thread
     *  @param startIndex - The number of row that we start to get the children threads
     *  @param endIndex - The number of row we end geting the children threads
     *  @return Collection - A collection of children threads
     *
     */
    public Collection findByParnetID( final long parentID, final int startIndex, final int endIndex ) throws DAOException{
        return null;
    }

    /**
     * Update title , content, notify of a thread
     */
    public void updateThread( final org.redsoft.forum.dao.Thread thread ) throws DAOException, ThreadNotFoundException{
        Validation.validateNotNull( thread );
        final PersistentThread newThread = thread;
        findByUID( newThread.getId() );
        String notifyString = newThread.isNotify() == true ?"Y":"N";
        try{
            Connection conn = getConnection();
            PreparedStatement stat
                    = conn.prepareStatement( "update " +
                    PersistentThread.TABLE_PERSISTENCE + " set " +
                    PersistentThread.PROPERTY_TITLE   + " = ? , " +
                    PersistentThread.PROPERTY_CONTENT + " = ? , " +
                    PersistentThread.PROPERTY_NOTIFY  + " = ? where " +
                    PersistentThread.PROPERTY_ID + " = " + newThread.getId() );
            stat.setString( 1, newThread.getTitle() );
            stat.setString( 2, newThread.getContent() );
            stat.setString( 3, notifyString );
            stat.execute();

            stat.close();
            conn.close();
        } catch (SQLException e) {
            throw new DAOException( e );
        }
    }
    /**
     * Update the click count of the parent thread
     */
    public void updateClick(long id) throws DAOException {
        try{
            Connection conn = getConnection();
            String sql = "update threads set click=click+1 where id="+id;
            Statement stmt = conn.createStatement();

            stmt.execute(sql);
            stmt.close();
            conn.close();
        } catch (SQLException e) {
            throw new DAOException( e );
        }
    }

    /**
     * Add a thread to  column
     * @param threadId
     * @throws ThreadNotFoundException
     * @throws DAOException
     */
    public void addColumnThread(long threadId) throws ThreadNotFoundException, DAOException {
        try{
            // check if this thread exist
            findByUID( threadId );
            final Connection conn = getConnection();
            final PreparedStatement pStat= conn.prepareStatement("update threads set columnThread='Y' where id=" + threadId );
            pStat.execute();
            pStat.close();
            conn.close();
        } catch (SQLException e) {
            throw new DAOException( e );
        }
    }

    /**
     * Remove a thread from column
     * @param threadId
     * @throws ThreadNotFoundException
     * @throws DAOException
     */
    public void removeColumnThread(long threadId) throws ThreadNotFoundException, DAOException {
        try{
            // check if this thread exist
            findByUID( threadId );
            final Connection conn = getConnection();
            final PreparedStatement pStat= conn.prepareStatement("update threads set columnThread='N' where id=" + threadId );
            pStat.execute();
            pStat.close();
            conn.close();
        } catch (SQLException e) {
            throw new DAOException( e );
        }
    }

    /**
     *  Get a list of thread by given the user Id from startIndex to endIndex
     *  startIndex included, endIndex exclulded
     *  @return Collection -- a list of PersistentThread Object which user id is the same as given
     *  @exception DAOException
     * @param userId
     * @param startIndex should be greater than zero
     * @param endIndex
     * @return
     * @throws DAOException
     */
    public  Collection findByUserId(final String userId, int startIndex, int endIndex) throws DAOException {
        Validation.validateNotNull( userId );
        Validation.validateInt( startIndex, 1 );
        // no user has a name more than 15 chars long
        if (userId.length()>15) {
            return new Vector(0);
        }
        final Vector listOfThreads=new Vector();
        try{
            final Connection conn = getConnection();
            final PreparedStatement pStat= conn.prepareStatement("select "
                    + PersistentThread.PROPERTY_ID +","
                    + PersistentThread.PROPERTY_TITLE +","
                    + PersistentThread.PROPERTY_CONTENT +","
                    + PersistentThread.PROPERTY_TITLE +","
                    + PersistentThread.PROPERTY_AUTHOR +","
                    + PersistentThread.PROPERTY_TIMESTAMP + ","
                    + PersistentThread.PROPERTY_CATEGORY + ","
                    + PersistentThread.PROPERTY_PARENT_ID + ","
                    + PersistentThread.PROPERTY_LAST_UPDATED + ","
                    + PersistentThread.PROPERTY_REPLY + ","
                    + PersistentThread.PROPERTY_CLICK + ","
                    + PersistentThread.PROPERTY_REPLIED_THREAD + ","
                    + PersistentThread.PROPERTY_COLUMN_THREAD
                    + " from "
                    + PersistentThread.TABLE_PERSISTENCE
                    + " where "
                    + PersistentThread.PROPERTY_AUTHOR
                    + " = ? "
                    + "and " + PersistentThread.PROPERTY_PARENT_ID
                    + "=-1"
                    + " order by " + PersistentThread.PROPERTY_TIMESTAMP + " desc "
            );
            pStat.setString(1, userId);
            final ResultSet resultSet=pStat.executeQuery();

            org.redsoft.forum.dao.Thread resultThread;
            if( resultSet.absolute( startIndex ) ) {
                do{
                    resultThread = new org.redsoft.forum.dao.Thread( resultSet.getLong( PersistentThread.PROPERTY_ID ),
                            resultSet.getString( PersistentThread.PROPERTY_TITLE ),
                            resultSet.getString( PersistentThread.PROPERTY_CONTENT ),
                            resultSet.getString( PersistentThread.PROPERTY_AUTHOR ),
                            resultSet.getLong( PersistentThread.PROPERTY_TIMESTAMP ),
                            resultSet.getLong( PersistentThread.PROPERTY_PARENT_ID ),
                            resultSet.getInt( PersistentThread.PROPERTY_CATEGORY ),
                            resultSet.getLong( PersistentThread.PROPERTY_LAST_UPDATED),
                            resultSet.getInt( PersistentThread.PROPERTY_REPLY),
                            resultSet.getInt(PersistentThread.PROPERTY_CLICK),
                            resultSet.getLong(PersistentThread.PROPERTY_REPLIED_THREAD),
                            resultSet.getBoolean( PersistentThread.PROPERTY_NOTIFY ));
                    resultThread.setColumnThread( resultSet.getString( PersistentThread.PROPERTY_COLUMN_THREAD ).equals("Y") );
                    listOfThreads.add(resultThread);
                }while( resultSet.next() && (resultSet.getRow() <= endIndex) );
            }
            pStat.close();
            conn.close();
        } catch (SQLException e) {
            throw new DAOException( e );
        }
        //System.out.println( listOfThreads.size() );
        return listOfThreads;
    }

    /**
     * Find all threads posted later than <code>time</code>
     * (compare timestamp field of threads table in db with time)
     * 
     * @param time  time in milliseconds
     *              use System.currentTimeMillis() to get current time
     *              if time==0, return all threads
     * @return      all threads found
     * @exception   DAOException when querying db
     *
     */
    public Collection findThreadsPostedLaterThan( long time ) throws DAOException{
        final Vector listOfThreads=new Vector();
        try{
            final Connection conn = getConnection();
            final PreparedStatement pStat= conn.prepareStatement("select "
                    + PersistentThread.PROPERTY_ID +","
                    + PersistentThread.PROPERTY_TITLE +","
                    + PersistentThread.PROPERTY_CONTENT +","
                    + PersistentThread.PROPERTY_TITLE +","
                    + PersistentThread.PROPERTY_AUTHOR +","
                    + PersistentThread.PROPERTY_TIMESTAMP + ","
                    + PersistentThread.PROPERTY_CATEGORY + ","
                    + PersistentThread.PROPERTY_PARENT_ID + ","
                    + PersistentThread.PROPERTY_LAST_UPDATED + ","
                    + PersistentThread.PROPERTY_REPLY + ","
                    + PersistentThread.PROPERTY_CLICK + ","
                    + PersistentThread.PROPERTY_REPLIED_THREAD
                    + " from "
                    + PersistentThread.TABLE_PERSISTENCE
                    + " where "
                    + PersistentThread.PROPERTY_TIMESTAMP
                    + " >= ? ");
            pStat.setLong(1, time);
            final ResultSet resultSet=pStat.executeQuery();
            org.redsoft.forum.dao.Thread resultThread;
            while (resultSet.next()) {
                resultThread = new org.redsoft.forum.dao.Thread( resultSet.getLong( PersistentThread.PROPERTY_ID ),
                        resultSet.getString( PersistentThread.PROPERTY_TITLE ),
                        resultSet.getString( PersistentThread.PROPERTY_CONTENT ),
                        resultSet.getString( PersistentThread.PROPERTY_AUTHOR ),
                        resultSet.getLong( PersistentThread.PROPERTY_TIMESTAMP ),
                        resultSet.getLong( PersistentThread.PROPERTY_PARENT_ID ),
                        resultSet.getInt( PersistentThread.PROPERTY_CATEGORY ),
                        resultSet.getLong( PersistentThread.PROPERTY_LAST_UPDATED),
                        resultSet.getInt( PersistentThread.PROPERTY_REPLY),
                        resultSet.getInt(PersistentThread.PROPERTY_CLICK),
                        resultSet.getLong(PersistentThread.PROPERTY_REPLIED_THREAD),
                        resultSet.getBoolean( PersistentThread.PROPERTY_NOTIFY ));
                listOfThreads.add(resultThread);
            }
            pStat.close();
            conn.close();
        } catch (SQLException e) {
            throw new DAOException( e );
        }
        return listOfThreads;
    }

    public Collection findColumnThreadsByAccountId(String userId) throws DAOException {
        final Vector results=new Vector();
        try{
            final Connection conn = getConnection();
            final PreparedStatement pStat= conn.prepareStatement(
                    "select "
                    + PersistentThread.PROPERTY_ID + ","
                    + PersistentThread.PROPERTY_TITLE + ","
                    + PersistentThread.PROPERTY_TIMESTAMP + ","
                    + PersistentThread.PROPERTY_CATEGORY + ","
                    + PersistentThread.PROPERTY_REPLY + ","
                    + PersistentThread.PROPERTY_CLICK
                    + " from " + PersistentThread.TABLE_PERSISTENCE
                    + " where " + PersistentThread.PROPERTY_AUTHOR + "='" + userId + "'"
                    + " and " + PersistentThread.PROPERTY_COLUMN_THREAD + "='Y'"
            );
            final ResultSet resultSet=pStat.executeQuery();
            while (resultSet.next()) {
                org.redsoft.forum.dao.Thread thread = new org.redsoft.forum.dao.Thread(
                        resultSet.getLong( PersistentThread.PROPERTY_ID ) ,
                        resultSet.getString( PersistentThread.PROPERTY_TITLE ),
                        resultSet.getLong( PersistentThread.PROPERTY_TIMESTAMP),
                        resultSet.getInt( PersistentThread.PROPERTY_CATEGORY ),
                        resultSet.getInt( PersistentThread.PROPERTY_REPLY ),
                        resultSet.getInt( PersistentThread.PROPERTY_CLICK )
                );
                results.add( thread );
            }
            pStat.close();
            conn.close();
        } catch (SQLException e) {
            throw new DAOException( e );
        }
        //System.out.println( results.size() );
        return results;
    }

    public int findCountByUserId(String userId) throws DAOException {
        int count = 0;
        try{
            final Connection conn = getConnection();
            final PreparedStatement stat
                    = conn.prepareStatement( "select count(*)"
                    + " from " + PersistentThread.TABLE_PERSISTENCE
                    +" where " + PersistentThread.PROPERTY_AUTHOR
                    + "= '" + userId + "'"
                    + " and " + PersistentThread.PROPERTY_PARENT_ID  + "=-1" );
            final ResultSet resultSet = stat.executeQuery();

            // Get the first column which is the only column,the count of threads
            // under this category
            if( resultSet.next() ){
                count = resultSet.getInt( 1 );
            }
            stat.close();
            conn.close();
        } catch (SQLException e) {
            throw new DAOException( e );
        }
        return count;
    }
}