title: Java and SQL : return null or throw exception?
body: <p>This is another debated subject, but this time i am searching only for the simple and documented answers. The scenario : 
  </p> Let's assume the following method: </p></p>
  
  <pre><code> public static Hashtable&lt;Long, Dog&gt; getSomeDogs(String colName, String colValue) {
    Hashtable&lt;Long, Dog&gt; result = new Hashtable&lt;Long, Dog&gt;();
    StringBuffer sql = null;
    Dog dog = null;
    ResultSet rs = null;
        try {
            sql = new StringBuffer();
            sql.append("SELECT * FROM ").append("dogs_table");
            sql.append(" WHERE ").append(colName).append("='");
            sql.append(colValue).append("'");
            rs = executeQuery(sql.toString());
                while (rs.next()) {
                    dog= new Dog();
                    //...initialize the dog from the current resultSet row
                result.put(new Long(dog.getId()), dog);
                }
            }
       catch (Exception e) {
           createErrorMsg(e);
           result = null; //i wonder....
           }
       finally {
           closeResultSet(rs); //this method tests for null rs and other stuff when closing the rs.
       }
     return result;
   }
  </code></pre>
  
  <p>Questions : </p>
  
  <p>1. What ways do u suggest on improving this technique of returning some dogs, with some attribute?
  </p>
  
  <p>2. rs.next() will return false for a null ResultSet, or will generate an exception, like this one : 
  <p>
  
  String str = null;
  
  System.out.println(str.toString()); 
  
  </p>
  </p>
  
  <p>3. What if, while initializing a dog object from the current row of the ResultSet, something bad happens, like : connection failure, incompatible value has been passed to dog property setter, etc? I might have 10 elements in the hashtable by now, or none (first row). What will be the next action : a) return null hashtable; b) return the result hashtable, the way it is at this stage; c) throw an exception : what will be the exception type here?
  </p>
  
  <p>4. I think u will all agree on this one : nothing bad happens, there are no rows on the interrogation, a null value will be return. However, @Thorbjørn Ravn Andersen said <a href="https://stackoverflow.com/questions/1515075/java-where-and-how-should-exceptions-be-used/1515835#1515835">here</a> that i should return a NullObject instead of a null value. I wonder what that is.
  </p>
  
  <p>5. I've noticed people and groups of people saying that one should split the application into layers, or levels of some kind. Considering the example above, what layers are here, except these ones i can think of : 
  
  <p>Layer1 :: Database layer, where actions are performed : this method.</p>
  <p>Layer2 :: ??? : some layer where the new Dog object is constructed : my Dog object.</p>
  <p>Layer3 :: ? : some layer where i intend to do something with the collection of dogs : GUI layer, mostly, or a sub-layer of user interface.</p>
  
  <p> Following the application flow, if an exception occures in the 1st layer, what is the best think to do with it? My thoughts : catch the exception, log the exception, return some value. Is that the best practice?</p>
  Manny thanks for your answers, i look forward to see what other people think of these matters.
  </p>