To Get the Number of Matches Found in the Database

[JiaPei Jen]

I am working a database search by supplying a 'memberName'. There is a field in the data table called 'message_receiver'. I want to count the number if the 'memberName' and the 'messag_receiver' is a match and return the number. How do I do it?

   public int getNumberOfThreadBeans_forReceiver( String memberName )
        throws AssertionException, DatabaseException 
   {
      Connection conn = null;
      PreparedStatement stmt = null;
      String query = "SELECT ***** FROM message_thread WHERE message_receiver = '" + memberName + "'";
      ResultSet rs = null;
      try 
      {
         conn = DBConnection.getDBConnection();
         stmt = conn.prepareStatement( query );
         ********** 
         rs = stmt.executeQuery;
         if ( !resultSet.next() ) 
         {
            throw new AssertionException("Assertion in ListThreadHandler.getNumberOfThreads_forReceiver.");
         }
         return rs.*******;
      }
      catch(SQLException sqle) 
      {
         sqle.printStackTrace();
         throw new DatabaseException("Error executing SQL in ListThreadHandler.getNumberOfThreads_forReceiver.");
      }
      finally 
      {
         if ( conn != null )
         {
            try
            {
               stmt.close();
               stmt = null;
               conn.close();
            }
            catch( SQLException sqlEx )
            {
               System.out.println( "Problem occurs while closing " + sqlEx );
            }
            conn = null;
         }       
      }
   }

[Mukesh Netedge]

Hi JiaPei Jen ,
It seems you need count of the result set returns from the query according to the parameter passed.
Try the following query:
String query = "SELECT count(*****) as countNo FROM message_thread WHERE message_receiver = '" + memberName + "'";
or
String query = "SELECT count(*****) as countNo FROM message_thread WHERE message_receiver like '%" + memberName + "%'";
Then usinf your resultset retrieve the first element as
String noOfMembers=rs.getString("countNo")
Hope it will hepl you.
Mukesh