title: Why is my JDBC code returning no records when MySQL itself returns 1+ record?
body: <p>In an application I've been writing, one of the methods in the DAO layer is supposed to query the database (<strong>MySQL</strong> in this case) returning a list of records that match the criteria.  The table to which it refers is WordInstance and it has 3 foreign keys to 3 other tables(<strong>Words</strong>, <strong>Parts of Speech</strong>, and <strong>Categories</strong>).  The <strong>Category</strong> field in the <strong>wordInstances</strong> table can be <strong>null</strong> but the rest of the fields can not - because a word may not have a category.</p>
  
  <p>I have 2 separate methods <strong>createObjectFromResults</strong> and <strong>createObjectListFromResults</strong>.  Inside createObjectListFromResults, there's a line/loop while(objResults.next()) and inside that calls the other create function to do the work for an individual row.  For whatever reason, it's skipping over that loop as if there's no records to process - except when I take the same statement and plug it into MySQL's workbench, I get 1 row(converting the section of "Category = ?" to "Category is null" for MySQL Workbench) - This is after running it one time and not dropping the schema or table in between executions.  Am I maybe doing something wrong w/ my sQL Query with regard to JDBC for this to return no records?  Code is below:</p>
  
  <pre><code>public List&lt;WordInstance&gt; listByWordPartOfSpeechAndCategory(final Word objWord, final PartOfSpeech objPartOfSpeech, final Category objCategory) throws SQLException
  {
      if(!isTableExists())
          createTable();
  
      try
          {
          PreparedStatement objStatement = getConnection().prepareStatement("select " + getQueriedColumnsForSelect() + " from " + TableName + " where Word = ? and PartOfSpeech = ? and Category = ?;");
  
          objStatement.setInt(1, objWord.getId());
          objStatement.setInt(2, objPartOfSpeech.getId());
  
          if(objCategory != null)
              objStatement.setInt(3, objCategory.getId());
          else
              objStatement.setNull(3, Types.INTEGER);
  
          return(createObjectListFromResults(objStatement.executeQuery()));
          }
      catch(SQLException objException)
          {
          msObjLogger.error("There was a problem retrieving the word instances that had the same word, part of speech, and category...", objException);
  
          return(null);
          }
      finally
          {
          closeConnection();
          }
  }
  </code></pre>
  
  <p>TableName for this DAO object is "WordInstances" and getQueriedColumnsForSelect is:</p>
  
  <pre><code>protected String getQueriedColumnsForSelect()
  {
      return("Id, Word, PartOfSpeech, Category, Definition, Slang");
  }
  </code></pre>
  
  <p>createObjectListFromResults is here:</p>
  
  <pre><code>protected List&lt;WordInstance&gt; createObjectListFromResults(final ResultSet objResults) throws SQLException
  {
      List&lt;WordInstance&gt; lstWordInstances = new ArrayList&lt;WordInstance&gt;();
  
      while(objResults.next())
          lstWordInstances.add(createObjectFromResults(objResults));
  
      return(lstWordInstances);
  }
  </code></pre>
  
  <p>I can include any other code as requested to help figure this problem out.  Like I said - in MySQL itself it returns a row (or more as I noticed the problem because there were multiple records in there) - Actually the multiple records confound me because I have a unique key for Word, PartOfSpeech and Category unless the fact that Category is null makes it un-unique-able.</p>
  
  <p>Any thoughts?</p>