threaddaomysql.java

一个功能较为完善的论坛

     */
    public int findCountByCategory( final int category ) throws SQLException{
        final Connection conn = getConnection();
        int count = 0;
        final PreparedStatement stat
            = conn.prepareStatement( "select count(*)"
                                      + " from " + PersistentThread.TABLE_PERSISTENCE
                                      +" where " + PersistentThread.PROPERTY_CATEGORY
                                      + "= " + category + " 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();
        return count;
    }

     /**
     *  Get all the top-level threads under the given category
     *
     *  @param category - The category
     *  @return Collection - A collection of threads under the given category
     */
    public Collection findByCategory( final int category )
                                                    throws SQLException,
			 CategoryNotFoundException{
        final Connection conn = getConnection();
        final ArrayList result = new ArrayList();
        final PreparedStatement stat
            = conn.prepareStatement( "select * from threads where " + PersistentThread.PROPERTY_CATEGORY
                                      + "= " + category + " and "
                                      + PersistentThread.PROPERTY_PARENT_ID
                                      + "=-1 order by " + PersistentThread.PROPERTY_TIMESTAMP );
        final ResultSet resultSet = stat.executeQuery();
        while( resultSet.next() ){
			PersistentThread pt = internalMapResultSet(resultSet);
            result.add(pt);
        }
        stat.close();
        conn.close();
        return result;
    }


    /**
     *  Get all the children threads given a id of a parent threa
     *
     *  @return Collection - A collection of children threads
     *
     */
    public Collection findByParnetID( final long parentID ) throws ThreadNotFoundException, SQLException{
		final Connection conn = getConnection();
        final ArrayList result = new ArrayList();
        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);
        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 SQLException{
        return null;
    }

	/**
	 * Update title , content, notify of a thread
	 */
	public void updateThread( final PersistentThread thread ) throws SQLException, ThreadNotFoundException{
        Validation.validateNotNull( thread );
        final PersistentThread newThread = thread;
     	findByUID( newThread.getID() );
        String notifyString = newThread.getNotify() == true ?"Y":"N";
		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();
	}
    /**
	 * Update the click count of the parent thread
	 */
	public void updateClick(long id) throws SQLException {
		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();


	}

    /**
     *  Get a list of thread by given the user Id
     *  @return Collection -- a list of PersistentThread Object which user id is the same as given
     *  @exception SQLException
     */
    public  Collection findByUserId(final String userId) throws SQLException {
        Validation.validateNotNull( userId );
        // no user has a name more than 15 chars long
        if (userId.length()>15) {
          return new Vector(0);
        }
        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_AUTHOR
                                                              + " = ? "
                                                              + "and " + PersistentThread.PROPERTY_PARENT_ID
                                                              + "=-1");
        pStat.setString(1, userId);
        final ResultSet resultSet=pStat.executeQuery();
        final Vector listOfThreads=new Vector();
        Thread resultThread;
        while (resultSet.next()) {
            resultThread = new 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) );
            listOfThreads.add(resultThread);
        }
        pStat.close();
        conn.close();
        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   exception when querying db
     *
     */
    public Collection findThreadsPostedLaterThan( long time ) throws SQLException{
        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();
        final Vector listOfThreads=new Vector();
        Thread resultThread;
        while (resultSet.next()) {
            resultThread = new 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) );
            listOfThreads.add(resultThread);
        }
        pStat.close();
        conn.close();
        return listOfThreads;
    }
}
//EOC