registry skeleton. now with some flesh.

gistfile1.java

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.Collections;
import java.util.Date;
import java.util.HashSet;
import java.util.Set;

public class FnordRegistry
{
  private static final Logger log = new Logger(FnordRegistry.class);

  private static final String TABLE = "Fnord";
  private static final int BLOCK = 50000;

  private static boolean initialized = false;



  //----------------------------------------------------------------------------
  //  public
  //----------------------------------------------------------------------------

  public static Fnord get(long id)
  {
    Set<Fnord> tmp = fetchFnords("id = " + id);
    return tmp.isEmpty() ? null : tmp.iterator().next();
  }

  public static void save(Fnord ob)
  {
    save(Collections.singleton(ob));
  }

  public static void save(Collection<Fnord> obs)
  {
    savedb(obs);
  }

  public static void delete(Fnord ob)
  {
    delete(Collections.singleton(ob));
  }

  public static void delete(Collection<Fnord> obs)
  {
    deletedb(obs);
  }



  //----------------------------------------------------------------------------
  //  private: QUERIES
  //----------------------------------------------------------------------------

  /**
   * @return an empty collection if no results.
   */
  private static Set<Fnord> fetchFnords(String whereClause)
  {
    Set<Fnord> result = new HashSet<Fnord>();

    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    try
    {
      String query = "SELECT * FROM " + TABLE;

      if (whereClause != null && !whereClause.trim().isEmpty())
      {
        query += " WHERE " + whereClause;
      }

      conn = ConnectionUtil.getLocalConnection();
      ps = conn.prepareStatement(query);
      log.trace(ps);
      rs = ps.executeQuery();

      while (rs.next())
      {
        Fnord ob = buildFnordFromResultSet(rs);
        if (ob != null)
        {
          result.add(ob);
        }
      }
    }
    catch (Exception e)
    {
      log.error(e);
    }
    finally
    {
      ConnectionUtil.closeConnection(conn, ps, rs);
    }

    return result;
  }

  private static Fnord buildFnordFromResultSet(ResultSet rs)
  {
    Fnord result = null;

    try
    {
      result = new Fnord();

      result.setId          (rs.getLong    ("id"));

      result.setCreated     (new Date(rs.getTimestamp  ("created").getTime()));
      result.setCreatedBy   (RoleRegistry.getUser(rs.getString  ("createdBy")));
      result.setModified    (new Date(rs.getTimestamp  ("modified").getTime()));
      result.setModifiedBy  (RoleRegistry.getUser(rs.getString  ("modifiedBy")));
    }
    catch (Exception e)
    {
      result = null;
      log.error(e);
    }

    return result;
  }



  //----------------------------------------------------------------------------
  //  private: EXECUTIONS
  //----------------------------------------------------------------------------

  private static void savedb(Collection<Fnord> obs)
  {
    Connection conn = null;
    PreparedStatement insert = null;
    PreparedStatement update = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    // save the users!
    Set<User> users = new HashSet<User>();
    for (Fnord o : obs)
    {
      users.add(o.getCreatedBy());
      users.add(o.getModifiedBy());
    }
    RoleRegistry.save(users);

    try
    {
      conn = ConnectionUtil.getLocalConnection();

      String fields = TABLE + " SET " +
          "created = ?, " +
          "createdBy = ?, " +
          "modified = ?, " +
          "modifiedBy = ?";

      insert = conn.prepareStatement("insert into " + fields,
          PreparedStatement.RETURN_GENERATED_KEYS);
      update = conn.prepareStatement("update " + fields + " where id = ?",
          PreparedStatement.RETURN_GENERATED_KEYS);

      for (Fnord ob : obs)
      {
        // use insert if object is new
        ps = (ob.getId() == -1) ? insert : update;

        // to avoid numbering mistakes, the order of query fields above must be
        // the same as ps.sets below.
        int i = 1;

        ps.setString( i++, MysqlUtil.format(ob.getCreated()));
        ps.setString( i++, QueryUtil.formatSuperKey(ob.getCreatedBy()));
        ps.setString( i++, MysqlUtil.format(ob.getModified()));
        ps.setString( i++, QueryUtil.formatSuperKey(ob.getModifiedBy()));

        // set id col if object isn't new
        if (ob.getId() != -1) ps.setLong(i++, ob.getId());

        log.trace("savedb() - " + ps);
        ps.executeUpdate();

        // set id field on returned object if keys were generated
        rs = ps.getGeneratedKeys();
        if (rs != null && rs.next())
        {
          ob.setId(rs.getLong(1));
        }
      }
    }
    catch (Exception e)
    {
      log.error(e);
    }
    finally
    {
      ConnectionUtil.closeConnection(null, insert, null);
      ConnectionUtil.closeConnection(null, update, null);
      ConnectionUtil.closeConnection(conn, null, rs);
    }
  }

  private static void deletedb(Collection<Fnord> obs)
  {
    Connection conn = null;
    Statement st = null;

    Set<Long> ids = new HashSet<Long>();

    for (Fnord o : obs)
    {
      ids.add(o.getId());
    }

    try
    {
      conn = ConnectionUtil.getLocalConnection();

      for (String block : QueryUtil.buildInClauseBlocks("id", ids, BLOCK))
      {
        st = conn.createStatement();
        st.executeUpdate("delete from " + TABLE + " where " + block);
        st.close();
      }
    }
    catch (Exception e)
    {
      log.error(e);
    }
    finally
    {
      ConnectionUtil.closeConnection(conn, null, null);
    }
  }



  //----------------------------------------------------------------------------
  //  initialisation
  //----------------------------------------------------------------------------

  public static void init()
  {
    Connection conn = null;

    if (!initialized)
    {
      try
      {
        conn = ConnectionUtil.getLocalConnection();

        createTableFnord(conn);

        initialized = true;
      }
      catch (Exception e)
      {
        log.error(e);
      }
      finally
      {
        ConnectionUtil.closeConnection(conn, null, null);
      }
    }
  }

  private static void createTableFnord(Connection conn) throws SQLException
  {
    Statement stmt = conn.createStatement();

    // beautifully formatted by http://www.dpriver.com/pp/sqlformat.htm
    StringBuilder q = new StringBuilder();
    q.append("\nCREATE TABLE IF NOT EXISTS `" + TABLE + "` ");
    q.append("\n  ( ");
    q.append("\n     `id`         BIGINT UNSIGNED PRIMARY KEY NOT NULL UNIQUE auto_increment, ");
    q.append("\n     `created`    TIMESTAMP, ");
    q.append("\n     `createdBy`  VARCHAR(255), ");
    q.append("\n     `modified`   TIMESTAMP, ");
    q.append("\n     `modifiedBy` VARCHAR(255) ");
    q.append("\n  ) ");
    q.append("\nengine=innodb ");
    q.append("\ncharset=latin1");

    log.trace(q.toString());
    stmt.executeUpdate(q.toString());
    ConnectionUtil.closeConnection(null, stmt, null);
  }

}