Java and SQL: return null or throw exception?

Question

This is another discussion topic, but this time I'm only looking for simple and documented answers. Scenario:

Assume the following method:

 public static Hashtable<Long, Dog> getSomeDogs(String colName, String colValue) {
  Hashtable<Long, Dog> result = new Hashtable<Long, Dog>();
  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;
 }

Questions:

1. In what ways do you propose to improve this technique of returning some dogs with some attribute?

2. rs.next () will return false for a null ResultSet or throw an exception like this:

String str = null; System.out.println (str.toString ());

3. , ResultSet - , : , ..? 10 -, ( ). : a) null hashtable; ) - , ; c) : ?

4. , : , , . , @Thorbjørn Ravn Andersen here, NullObject . , ?

5. I noticed that people and groups of people say that you need to break the application into layers or levels. Given the above example, which layers are here, other than those that I can think of:

Layer1 :: The database level where the actions are performed: this method.

Layer2 :: ???: some layer on which the new Dog object is built: my Dog object.

Layer3 ::?: Some layer where I am going to do something with a collection of dogs: a layer with a graphical interface, basically, or a sublayer of the user interface.

Following the flow of the application, if an exception occurs in the first level, what is the best way to deal with it? My thoughts: catch the exception, write down the exception, return some value. Is this the best practice?

Manny thanks for your answers, I look forward to what other people think about these issues.

Answer 1

1. ?

, .

• - , , "executeQuery", ...
• "" OO - , - , "".
• HashTable . HashMap ConcurrentHashMap .
• - , , , sql.append( "SELECT * FROM dogs_table WHERE" ); , (*) (dogs_table) .

2. rs.next() false ResultSet

, , rs.next() false, .

3. , ResultSet - .

"- ", , . ( , ), ( ). "" , "" , - , . .

4. , : , , .

, . -, , , . HashTable ( , " " ). -, null " ".

, . , , . , " " .

5. , , - .

, , .

Answer 2

   sql.append("SELECT * FROM ").append("dogs_table");
   sql.append(" WHERE ").append(colName).append("='");
                        sql.append(colValue).append("'");

PreparedStatement (setString()) .. colValue SQL- (, , colValue, SQL).

null, . .

null , (, , ). , ( re) ) , , ( ). , , . Null .

, Dog? , , , . Dog s, , ? ( - ).

. HashMap, Hashtable ( , , Collection - Collection, , Collection), StringBuilder StringBuffer . , .

Answer 3

Null Object Pattern - , , NPE: s null . , null Hashtable<Long, Dogs>.

, , , ; , , NPE: - .

, Null /, , , . - , null, , , , , , ! Null Object , singletons, , , , .

Answer 4

SQL-, , :

sql = new StringBuffer();
sql.append("SELECT * FROM ").append("dogs_table");
sql.append(" WHERE ").append(colName).append("='");
sql.append(colValue).append("'");

, SQL injection. PreparedStatement , set...(). , , , . :

PreparedStatement ps = connection.prepareStatement("SELECT * FROM dogs_table WHERE MYCOL=?");
ps.setString(1, colValue);

rs = ps.executeQuery();

PreparedStatement, JDBC , colValue, SQL- .

Answer 5

, . , , . (, , "", ​​ ),

Answer 6

JDBC, Spring -JDBC JDBC. Spring -JDBC

public static Hashtable<Long, Dogs> getSomeDogs(String colName, String colValue) {

    StringBuffer sql = new StringBuffer();
    sql.append("SELECT * FROM ").append("dogs_table");
    sql.append(" WHERE ").append(colName).append("='");
    sql.append(colValue).append("'");

    Hashtable<Long, Dogs> result = new Hashtable<Long, Dogs>();

    RowMapper mapper = new RowMapper() {

        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            Dogs dog = new Dogs();
            //...initialize the dog from the current resultSet row
            result.put(new Long(dog.getId()), dog);
        }
    };
    (Hashtable<Long, Dogs>) jdbcTemplate.queryForObject(sql, mapper);
}

Spring :

• ResultSet
• ResultSet

, PreparedStatement SQL String ( StringBuffer). - , , SQL :

    String sql = 
        "SELECT * FROM dogs_table " +
        "WHERE " + "colName" + " = '" + colValue + "'";