Java Practices -> Consider data layer tools

Consider data layer tools

The standard JDBC API is a bit low level. Many applications will benefit from using tools that operate on top of the JDBC API.

Examples of such tools include :

These tools generally fall into two categories:

treat code and objects as primary (Java Persistence API and Hibernate, for example). These are usually referred to as Object-Relational Mapping (ORM) tools.
treat SQL as primary (IBatis and Web4j, for example)

Although ORM tools are currently popular, some argue that they should be avoided. In brief:

their performance is usually poorer than approaches based on SQL
there's usually no easy way to debug and tune queries
they usually don't support all relational databases
learning how to use them well is non-trivial
they reinvent the wheel by defining query APIs, which more or less reproduce what already exists in SQL (an existing, widely used, and well understood problem domain language for the manipulation of data)
they sometimes prevent you from defining your Model Objects as immutable

As an example of the relative simplicity of tools which give primacy to SQL, and not objects, here's an example of a Data Access Object implemented with web4j. The actual SQL statements are placed in .sql files placed near the corresponding DAO (usually in the same directory). Entries in these .sql files are identified using SqlId members such as RATING_LIST.


package hirondelle.fish.main.rating;

import java.util.List;
import hirondelle.web4j.database.Db;
import hirondelle.web4j.database.DAOException;
import hirondelle.web4j.database.SqlId;
import hirondelle.web4j.model.Id;

/** Data Access Object for {@link Rating} objects. */
public final class RatingDAO {

  public static final SqlId RATING_LIST = new SqlId("RATING_LIST");
  public static final SqlId RATING_FETCH_FOR_CHANGE = new SqlId("RATING_FETCH_FOR_CHANGE");
  public static final SqlId RATING_CHANGE = new SqlId("RATING_CHANGE");
  
  /** Return a <tt>List</tt> of all {@link Rating} objects, for all restaurants.  */
  List<Rating> list() throws DAOException {
    return Db.list(Rating.class, RATING_LIST);
  }
  
  /** Return a single {@link Rating} identified by its id.  */
  Rating fetch(Id aRatingId) throws DAOException {
    return Db.fetch(Rating.class, RATING_FETCH_FOR_CHANGE, aRatingId);
  }
  
  /** Update an existing {@link Rating}. */
  int change(Rating aRating) throws DAOException {
    Object[] params = { 
      aRating.getFishRating(), aRating.getChipsRating(), aRating.getPriceRating(), 
      aRating.getLocationRating(), aRating.getServiceRating(), 
      aRating.getBeerRating(), aRating.getId() 
    };
    return Db.edit(RATING_CHANGE, params);
  }
}