I've written a simple little Groovy class handling my database interactions. All it has to do is check if a row exists in a target table with primary key values which match those of an incoming map. If a row exists, it updates the row based on the differing non-primary key values; otherwise, a new row is inserted.import groovy.sql.Sqlimport java.sql.ResultSetimport java.sql.SQLException/** * A simple class to insert or update records into an arbitrary database table. * <p/> * This class is used by first creating an instance with the target database parameters. Once an object is created, * {@link DataAcessObject#connect(java.lang.String)} can be called to connect to a database table. * <p/> * After connecting to a table, records may be submitted via the * {@link DataAcessObject#storeRecord(java.util.Map)} method. For each record submitted to this method, the table is * queried to see if another row with matching primary key column values match. If there is a match, then the existing * row is updated with the incoming values. If no match is found, then a new record is inserted. */final class DataAcessObject implements Closeable {  private static final int COLUMN_NAME_RS_INDEX = 4  private final Sql sql  private List<String> columns  private String tableName  private String whereClause  private boolean connected  /**   * Creates a new object which can insert and update table rows in the target database.   *   * @param connectionString the JDBC connection string to connect the to target database   * @param user the database user   * @param password the database user password   *   * @throws SQLException if a database exception occurs   */  DataAcessObject(final String connectionString, final String user, final String password) throws SQLException {    this.sql = Sql.newInstance(connectionString, user, password)  }  /**   * Connects to a database table and prepares this object for storing records.   *   * @param tableName the name of the target database table   *   * @throws SQLException if a database exception occurs   */  void connect(final String tableName) throws SQLException {    assert !connected, 'Already connected to a table'    this.tableName = tableName    final def metadata = sql.connection.metaData    columns = metadata.getColumns(null, null, tableName, null).with {      getColumnNames it    }    whereClause = metadata.getPrimaryKeys(null, null, tableName).with {      getColumnNames it    }.collect { final String pk ->      $pk = :$pk    }.join(' and ')    connected = true  }  @Override  void close() throws IOException {    assert connected, 'Not connected to a table'    sql.close()    connected = false  }  /**   * Inserts a new record into the target table, or updates an existing record if a previous record with matching   * primary key values exists.   *   * @param record the record to insert or update   *   * @throws SQLException if a database exception occurs   */  void storeRecord(final Map<String, Object> record) throws SQLException {    assert connected, 'Not connected to a table'    final def select =           select ${columns.join(', ')}            from $tableName           where $whereClause              final Map<String, Object> row = sql.firstRow(select, record)    if (row) {      updateRow row, record    }    else {      insertRow record    }  }  private def updateRow = { final Map<String, Object> row, final Map<String, Object> record ->    assert connected, 'Not connected to a table'    println Found matching record: $record    final List<String> updates = row.entrySet().findAll {      it.value != record[it.key]    }.collect {      println Updating $it.key to ${record[it.key]} from $it.value      it.key    }    if (updates) {      final def setClause = updates.collect {        $it = :$it      }.join(', ')      final def update = update $tableName set $setClause where $whereClause      sql.executeUpdate(update, record)    }    else {      println 'No differences found. Record is dropped.'    }  }  private def insertRow = { final Map<String, Object> record ->    assert connected, 'Not connected to a table'    final def insert =           insert into $tableName           (${columns.join(', ')})          values           (${columns.collect { :$it }.join(', ')})            sql.executeInsert(insert, record)    println Inserting record: $record  }  private def getResultSetString = { final ResultSet rs, final int col ->    final def result = []    while (rs.next()) {      result << rs.getString(col)    }    result  }  private def getColumnNames = getResultSetString.rcurry(COLUMN_NAME_RS_INDEX)}As simple as it is, I had a great time writing it since I've done a similar thing previously in Java, and the conciseness of Groovy is always a joy to experience. That being said, I'm primarily a Java programmer, and I'm sure that shows through in my code. I have a tough time giving up static typing and labeling everything I can as final (my IDE is partially to blame for that, too).I'm particularly interested in making anything more Groovy. My primitive logging and assertions are standing out at me as lacking, but I'm sure there's a lot more that can be pointed out.
A Groovier way of inserting and updating database records
sql;groovy