Of late, I have been playing around with DAOs a little bit and have also been blogging extensively on the implementation aspects of them (e.g. see
here and
here). Of all the leading vendors that offer DAO implementation,
CodeFutures'
Firestorm/DAO offers automatic code generation for DAOs from the relational schema. Andy Grove, the CTO of CodeFutures claims in his weblog that the code which FireStorm generates is as efficient and maintenable as hand-written code. In fact he also has a separate blog entry on the debate of frameworks versus code generators, where he mentions about the complexity of maintaining the framework as one of the driving reasons for going to the code generator approach. As an example he mentions that
the real benefit of Code Automation is that if the underlying framework changes substantially (for example going from EJB 1.1 to EJB 2.0) then the code can be re-generated for the new framework.
I have never been a fan of automatic code generators, which always tend to produce tons of boilerplates, which could otherwise be refactored much more elegantly using OO frameworks. Here is what Rod Johnson has to
say about automatic generation of DAOs
There shouldn't be vast amounts of boilerplate code in DAOs. ... Using JDBC abstraction layer is better than generating JDBC code from a maintenability perspective. If we see requirement for boring repetitive code, we should apply an OO solution and abstract it into a framework, rather than generate it and live with the resulting duplication.
In one of my
earlier posts, I expressed the same concern regarding the level of abstraction that Firestorm generated DAOs provide - I still feel that the code could have been better reengineered had we employed an OO framework based solution. At
Anshinsoft we have been working on generic DAOs and adopting a mixed strategy for DAO code generation. All repetitive codes are part of an OO framework, which offers base level abstractions for DAO operations. The generated code only contains the specifics for the particular tables as specializations of the base abstractions.
After going through Andy's weblogs, recently I downloaded a copy of the evaluation version of FireStorm/DAO and started playing with it. I generated some DAO code against a schema and came up with the following implementation of
AuthorDao, which I replicate below.
The generated DAO implementation class consists of 586 lines of code for a table having 3 columns! Multiply this with the number of tables that can be there in a typical enterprise application, and all jars begin to explode.
/*
* This source file was generated by FireStorm/DAO 3.0.1
* on 07-Apr-2006 at 10:13:32
*
* If you purchase a full license for FireStorm/DAO you can customize this file header.
*
* For more information please visit http://www.codefutures.com/products/firestorm
*/
package com.mycompany.myapp.jdbc;
import com.mycompany.myapp.dao.*;
import com.mycompany.myapp.factory.*;
import com.mycompany.myapp.dto.*;
import com.mycompany.myapp.exceptions.*;
import java.sql.Connection;
import java.sql.Types;
import java.util.Collection;
import org.apache.log4j.Logger;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.util.List;
import java.util.Iterator;
import java.util.ArrayList;
public class AuthorDaoImpl extends AbstractDataAccessObject implements AuthorDao
{
/**
* The factory class for this DAO has two versions of the create() method - one that
takes no arguments and one that takes a Connection argument. If the Connection version
is chosen then the connection will be stored in this attribute and will be used by all
calls to this DAO, otherwise a new Connection will be allocated for each operation.
*/
protected java.sql.Connection userConn;
protected static final Logger logger = Logger.getLogger( AuthorDaoImpl.class );
/**
* All finder methods in this class use this SELECT constant to build their queries
*/
protected final String SQL_SELECT = "SELECT ID, NAME, PASSWORD FROM " + getTableName() + "";
/**
* Finder methods will pass this value to the JDBC setMaxRows method
*/
private int maxRows;
/**
* SQL INSERT statement for this table
*/
protected final String SQL_INSERT = "INSERT INTO " + getTableName() + " ( ID, NAME, PASSWORD ) VALUES ( ?, ?, ? )";
/**
* SQL UPDATE statement for this table
*/
protected final String SQL_UPDATE = "UPDATE " + getTableName() + " SET ID = ?, NAME = ?, PASSWORD = ? WHERE ID = ?";
/**
* SQL DELETE statement for this table
*/
protected final String SQL_DELETE = "DELETE FROM " + getTableName() + " WHERE ID = ?";
/**
* Index of column ID
*/
protected static final int COLUMN_ID = 1;
/**
* Index of column NAME
*/
protected static final int COLUMN_NAME = 2;
/**
* Index of column PASSWORD
*/
protected static final int COLUMN_PASSWORD = 3;
/**
* Number of columns
*/
protected static final int NUMBER_OF_COLUMNS = 3;
/**
* Index of primary-key column ID
*/
protected static final int PK_COLUMN_ID = 1;
/**
* Inserts a new row in the AUTHOR table.
*/
public AuthorPk insert(Author dto) throws AuthorDaoException
{
long t1 = System.currentTimeMillis();
// declare variables
final boolean isConnSupplied = (userConn != null);
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
// get the user-specified connection or get a connection from the ResourceManager
conn = isConnSupplied ? userConn : ResourceManager.getConnection();
StringBuffer sql = new StringBuffer();
sql.append( "INSERT INTO " + getTableName() + " (" );
int modifiedCount = 0;
if (dto.isIdModified()) {
if (modifiedCount > 0) {
sql.append( ", " );
}
sql.append( "ID" );
modifiedCount++;
}
if (dto.isNameModified()) {
if (modifiedCount > 0) {
sql.append( ", " );
}
sql.append( "NAME" );
modifiedCount++;
}
if (dto.isPasswordModified()) {
if (modifiedCount > 0) {
sql.append( ", " );
}
sql.append( "PASSWORD" );
modifiedCount++;
}
if (modifiedCount==0) {
// nothing to insert
throw new IllegalStateException( "Nothing to insert" );
}
sql.append( ") VALUES (" );
for (int i=0; i < modifiedCount; i++ ) {
if (i>0) {
sql.append( "," );
}
sql.append( "?" );
}
sql.append( ")" );
stmt = conn.prepareStatement( sql.toString() );
int index = 1;
if (dto.isIdModified()) {
stmt.setInt( index++, dto.getId() );
}
if (dto.isNameModified()) {
stmt.setString( index++, dto.getName() );
}
if (dto.isPasswordModified()) {
stmt.setString( index++, dto.getPassword() );
}
if (logger.isDebugEnabled()) {
logger.debug( "Executing " + sql.toString() + " with values: " + dto);
}
int rows = stmt.executeUpdate();
long t2 = System.currentTimeMillis();
if (logger.isDebugEnabled()) {
logger.debug( rows + " rows affected (" + (t2-t1) + " ms)");
}
return dto.createPk();
}
catch (SQLException _e) {
logger.error( "SQLException: " + _e.getMessage(), _e );
throw new AuthorDaoException( "SQLException: " + _e.getMessage(), _e );
}
catch (Exception _e) {
logger.error( "Exception: " + _e.getMessage(), _e );
throw new AuthorDaoException( "Exception: " + _e.getMessage(), _e );
}
finally {
ResourceManager.close(stmt);
if (!isConnSupplied) {
ResourceManager.close(conn);
}
}
}
/**
* Updates a single row in the AUTHOR table.
*/
public void update(AuthorPk pk, Author dto) throws AuthorDaoException
{
long t1 = System.currentTimeMillis();
// declare variables
final boolean isConnSupplied = (userConn != null);
Connection conn = null;
PreparedStatement stmt = null;
try {
// get the user-specified connection or get a connection from the ResourceManager
conn = isConnSupplied ? userConn : ResourceManager.getConnection();
StringBuffer sql = new StringBuffer();
sql.append( "UPDATE " + getTableName() + " SET " );
boolean modified = false;
if (dto.isIdModified()) {
if (modified) {
sql.append( ", " );
}
sql.append( "ID=?" );
modified=true;
}
if (dto.isNameModified()) {
if (modified) {
sql.append( ", " );
}
sql.append( "NAME=?" );
modified=true;
}
if (dto.isPasswordModified()) {
if (modified) {
sql.append( ", " );
}
sql.append( "PASSWORD=?" );
modified=true;
}
if (!modified) {
// nothing to update
return;
}
sql.append( " WHERE ID=?" );
if (logger.isDebugEnabled()) {
logger.debug( "Executing " + sql.toString() + " with values: " + dto);
}
stmt = conn.prepareStatement( sql.toString() );
int index = 1;
if (dto.isIdModified()) {
stmt.setInt( index++, dto.getId() );
}
if (dto.isNameModified()) {
stmt.setString( index++, dto.getName() );
}
if (dto.isPasswordModified()) {
stmt.setString( index++, dto.getPassword() );
}
stmt.setInt( index++, pk.getId() );
int rows = stmt.executeUpdate();
long t2 = System.currentTimeMillis();
if (logger.isDebugEnabled()) {
logger.debug( rows + " rows affected (" + (t2-t1) + " ms)");
}
}
catch (SQLException _e) {
logger.error( "SQLException: " + _e.getMessage(), _e );
throw new AuthorDaoException( "SQLException: " + _e.getMessage(), _e );
}
catch (Exception _e) {
logger.error( "Exception: " + _e.getMessage(), _e );
throw new AuthorDaoException( "Exception: " + _e.getMessage(), _e );
}
finally {
ResourceManager.close(stmt);
if (!isConnSupplied) {
ResourceManager.close(conn);
}
}
}
/**
* Deletes a single row in the AUTHOR table.
*/
public void delete(AuthorPk pk) throws AuthorDaoException
{
long t1 = System.currentTimeMillis();
// declare variables
final boolean isConnSupplied = (userConn != null);
Connection conn = null;
PreparedStatement stmt = null;
try {
// get the user-specified connection or get a connection from the ResourceManager
conn = isConnSupplied ? userConn : ResourceManager.getConnection();
if (logger.isDebugEnabled()) {
logger.debug( "Executing " + SQL_DELETE + " with PK: " + pk);
}
stmt = conn.prepareStatement( SQL_DELETE );
stmt.setInt( 1, pk.getId() );
int rows = stmt.executeUpdate();
long t2 = System.currentTimeMillis();
if (logger.isDebugEnabled()) {
logger.debug( rows + " rows affected (" + (t2-t1) + " ms)");
}
}
catch (SQLException _e) {
logger.error( "SQLException: " + _e.getMessage(), _e );
throw new AuthorDaoException( "SQLException: " + _e.getMessage(), _e );
}
catch (Exception _e) {
logger.error( "Exception: " + _e.getMessage(), _e );
throw new AuthorDaoException( "Exception: " + _e.getMessage(), _e );
}
finally {
ResourceManager.close(stmt);
if (!isConnSupplied) {
ResourceManager.close(conn);
}
}
}
/**
* Returns the rows from the AUTHOR table that matches the specified primary-key value.
*/
public Author findByPrimaryKey(AuthorPk pk) throws AuthorDaoException
{
return findByPrimaryKey( pk.getId() );
}
/**
* Returns all rows from the AUTHOR table that match the criteria 'ID = :id'.
*/
public Author findByPrimaryKey(int id) throws AuthorDaoException
{
Author ret[] = findByDynamicSelect( SQL_SELECT + " WHERE ID = ?", new Object[] { new Integer(id) } );
return ret.length==0 ? null : ret[0];
}
/**
* Returns all rows from the AUTHOR table that match the criteria ''.
*/
public Author[] findAll() throws AuthorDaoException
{
return findByDynamicSelect( SQL_SELECT + " ORDER BY ID", null );
}
/**
* Returns all rows from the AUTHOR table that match the criteria 'ID = :id'.
*/
public Author[] findWhereIdEquals(int id) throws AuthorDaoException
{
return findByDynamicSelect( SQL_SELECT + " WHERE ID = ? ORDER BY ID", new Object[] { new Integer(id) } );
}
/**
* Returns all rows from the AUTHOR table that match the criteria 'NAME = :name'.
*/
public Author[] findWhereNameEquals(String name) throws AuthorDaoException
{
return findByDynamicSelect( SQL_SELECT + " WHERE NAME = ? ORDER BY NAME", new Object[] { name } );
}
/**
* Returns all rows from the AUTHOR table that match the criteria 'PASSWORD = :password'.
*/
public Author[] findWherePasswordEquals(String password) throws AuthorDaoException
{
return findByDynamicSelect( SQL_SELECT + " WHERE PASSWORD = ? ORDER BY PASSWORD", new Object[] { password } );
}
/**
* Method 'AuthorDaoImpl'
*
*/
public AuthorDaoImpl()
{
}
/**
* Method 'AuthorDaoImpl'
*
* @param userConn
*/
public AuthorDaoImpl(final java.sql.Connection userConn)
{
this.userConn = userConn;
}
/**
* Sets the value of maxRows
*/
public void setMaxRows(int maxRows)
{
this.maxRows = maxRows;
}
/**
* Gets the value of maxRows
*/
public int getMaxRows()
{
return maxRows;
}
/**
* Method 'getTableName'
*
* @return String
*/
public String getTableName()
{
return "AUTHOR";
}
/**
* Fetches a single row from the result set
*/
protected Author fetchSingleResult(ResultSet rs) throws SQLException
{
if (rs.next()) {
Author dto = new Author();
populateDto( dto, rs);
return dto;
} else {
return null;
}
}
/**
* Fetches multiple rows from the result set
*/
protected Author[] fetchMultiResults(ResultSet rs) throws SQLException
{
Collection resultList = new ArrayList();
while (rs.next()) {
Author dto = new Author();
populateDto( dto, rs);
resultList.add( dto );
}
Author ret[] = new Author[ resultList.size() ];
resultList.toArray( ret );
return ret;
}
/**
* Populates a DTO with data from a ResultSet
*/
protected void populateDto(Author dto, ResultSet rs) throws SQLException
{
dto.setId( rs.getInt( COLUMN_ID ) );
dto.setName( rs.getString( COLUMN_NAME ) );
dto.setPassword( rs.getString( COLUMN_PASSWORD ) );
}
/**
* Returns all rows from the AUTHOR table that match the specified arbitrary SQL statement
*/
public Author[] findByDynamicSelect(String sql, Object[] sqlParams) throws AuthorDaoException
{
// declare variables
final boolean isConnSupplied = (userConn != null);
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
// get the user-specified connection or get a connection from the ResourceManager
conn = isConnSupplied ? userConn : ResourceManager.getConnection();
// construct the SQL statement
final String SQL = sql;
if (logger.isDebugEnabled()) {
logger.debug( "Executing " + SQL);
}
// prepare statement
stmt = conn.prepareStatement( SQL );
stmt.setMaxRows( maxRows );
// bind parameters
for (int i=0; sqlParams!=null && i < sqlParams.length; i++ ) {
stmt.setObject( i+1, sqlParams[i] );
}
rs = stmt.executeQuery();
// fetch the results
return fetchMultiResults(rs);
}
catch (SQLException _e) {
logger.error( "SQLException: " + _e.getMessage(), _e );
throw new AuthorDaoException( "SQLException: " + _e.getMessage(), _e );
}
catch (Exception _e) {
logger.error( "Exception: " + _e.getMessage(), _e );
throw new AuthorDaoException( "Exception: " + _e.getMessage(), _e );
}
finally {
ResourceManager.close(rs);
ResourceManager.close(stmt);
if (!isConnSupplied) {
ResourceManager.close(conn);
}
}
}
/**
* Returns all rows from the AUTHOR table that match the specified arbitrary SQL statement
*/
public Author[] findByDynamicWhere(String sql, Object[] sqlParams) throws AuthorDaoException
{
// declare variables
final boolean isConnSupplied = (userConn != null);
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
// get the user-specified connection or get a connection from the ResourceManager
conn = isConnSupplied ? userConn : ResourceManager.getConnection();
// construct the SQL statement
final String SQL = SQL_SELECT + " WHERE " + sql;
if (logger.isDebugEnabled()) {
logger.debug( "Executing " + SQL);
}
// prepare statement
stmt = conn.prepareStatement( SQL );
stmt.setMaxRows( maxRows );
// bind parameters
for (int i=0; sqlParams!=null && i < sqlParams.length; i++ ) {
stmt.setObject( i+1, sqlParams[i] );
}
rs = stmt.executeQuery();
// fetch the results
return fetchMultiResults(rs);
}
catch (SQLException _e) {
logger.error( "SQLException: " + _e.getMessage(), _e );
throw new AuthorDaoException( "SQLException: " + _e.getMessage(), _e );
}
catch (Exception _e) {
logger.error( "Exception: " + _e.getMessage(), _e );
throw new AuthorDaoException( "Exception: " + _e.getMessage(), _e );
}
finally {
ResourceManager.close(rs);
ResourceManager.close(stmt);
if (!isConnSupplied) {
ResourceManager.close(conn);
}
}
}
}
To me, this is reams of boilerplate code with hardly any abstraction for the common SQL operations. Have a look at the method
Author[] findByDynamicSelect(...), which gets duplicated for every DAO you generate. Apart from the
return type and the
exception class, everything is identical across all DAOs! This, I think, is a design smell. As Andy has mentioned in his blog,
Code Generators don’t mind writing repetitive code, but all these repetitive stuff goes to swell the bottomline of my application. I have been working on a financials project which has 600 database tables - the amount of boilerplate code that will be generated with the above approach will be enough to stretch the existing jar size by at least 30%. I simply could not afford the decision.
Bye Bye Code Generators - Enter OOIn my earlier post, I had discussed about how generic DAOs have helped us shrinkwrap the core data access functionalities into generic base abstractions - every specific DAO implementation only contains the details of that particular database table. Have a look at the following code generated for a table
Employee using our code generator and OO framework concoction :
package com.anshinsoft.pi.dao.app.dao;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.beanutils.BeanUtils;
import com.anshinsoft.pi.core.StringBufferSize;
import com.anshinsoft.pi.dao.DAOBase;
import com.anshinsoft.pi.dao.DAOImplBase;
import com.anshinsoft.pi.dao.DTOBase;
import com.anshinsoft.pi.pix.orm.DbUtils;
import com.anshinsoft.pi.pix.orm.ICriteria;
import com.anshinsoft.pi.pix.orm.NullCriteria;
import com.anshinsoft.pi.pix.orm.SimpleCriteria;
/**
* The DAO implementation for {@link Employee} class.
*
*
*/
public class EmployeeDAO
extends DAOBase {
/**
* Enum for column names.
*/
public enum ColumnNames {
PK,
ID,
NAME,
BIRTH_DATE,
JOINING_DATE,
DESIGNATION,
ADDRESS_PK,
PASSPORT_NO,
EXPERTISE;
/**
* Constructor.
*/
ColumnNames() {
}
}
/**
* Constructor.
*
* @param impl the concrete implementation of {@link DAOImplBase}
*/
public EmployeeDAO(DAOImplBase impl) {
super(impl);
}
/**
* Returns a list of the column names.
* @return list of column names.
*/
protected List getColumnNames() {
ColumnNames[] names = ColumnNames.values();
List l = new ArrayList(names.length);
for (ColumnNames name : names) {
l.add(name.name());
}
return l;
}
/**
* Subclasses must override and provide the TABLE_NAME
* that the bean is associated with.
*
* @return the table name.
*/
public String getTableName() {
return "EMPLOYEE";
}
/**
* {@inheritDoc}.
*/
protected ICriteria getPrimaryKeyWhereClause(T employee) {
try {
String str =
BeanUtils.getProperty(employee,
DbUtils.dbColumn2PropertyName(
ColumnNames.PK.name()));
return new SimpleCriteria(
new StringBuilder(StringBufferSize.SMALL.size())
.append(ColumnNames.PK.name())
.append(" = ")
.append(str).toString());
} catch (Exception ex) {
return NullCriteria.getInstance();
}
}
}
All mundane SQL operations are wrapped inside
DAOBase<T>, all base level functionalities of transfer objects are encapsulated within
DTOBase. In case the user wants to add additional behavior to the transfer objects, he can extend one out of DTOBase :
class MyDTOBase extends DTOBase {
// added behavior
}
And this way they can add global behaviors like state management functionalities to all DTOs that they use.
