Java Practices->Consider data layer tools

Consider data layer tools

Many applications store data in a relational database. The standard JDBC API is used to interact with relational databases, but it's a bit low level. It's not hard to define a few helper classes that will encapsulate the bulk of repeated JDBC code. But even so, 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), and SQL as secondary. These are usually referred to as Object-Relational Mapping (ORM) tools.
treat SQL as primary (IBatis and web4j, for example), and code as secondary.

ORM tools are widely used, but there are trade-offs:

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
they sometimes prevent you from defining your Model Objects as immutable, or otherwise leak into your Model Object

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 (DAO) implemented with web4j. The actual SQL statements are placed in .sql text files placed near the corresponding DAO (usually in the same directory).

FETCH_PREFERENCES {
  SELECT Id, LoginName, ScreenName FROM Users WHERE LoginName=?
}
CHANGE_PREFERENCES {
  UPDATE Users SET  ScreenName=? WHERE LoginName=?
}

Entries in the above .sql file are referenced in code using SqlId objects, such as FETCH_PREFERENCES:

import static hirondelle.predict.main.preferences.PreferencesAction.CHANGE_PREFERENCES;
import static hirondelle.predict.main.preferences.PreferencesAction.FETCH_PREFERENCES;
import hirondelle.web4j.database.DAOException;
import hirondelle.web4j.database.Db;
import hirondelle.web4j.security.SafeText;

/**
 Data Access Object for user {@link Preferences}. 
*/
public final class PreferencesDAO {
  
  /** Fetch the {@link Preferences} for a given user. */ 
  public Preferences fetch(SafeText aUserId) throws DAOException {
    return Db.fetch(Preferences.class, FETCH_PREFERENCES, aUserId);
  }
  
  /** Change the {@link Preferences} for a given user. */
  void change(Preferences aPreferences) throws DAOException {
    Db.edit(
      CHANGE_PREFERENCES, 
      aPreferences.getScreenName(), 
      aPreferences.getLoginName()
    );
  }
}

That's all there is to it. There's no mapping involved.