Stephen Nimmo

Making Things Too Complicated

The Data Access Object pattern is a staple of the developer cookbook. To abstract the persistence mechanisms from the application resolves many issues with extensibility and flexibility, but also gives the developer a sandbox to improve performance. Rarely do you see pieces of business logic you write actually become the bottleneck of an application. More often than that, it's either an IO issue, a serialization issue or a DBMS issue - some piece of code you need to perform the business logic, but isn't a logical piece of the model.

So where does a DAO go wrong? Just like other values of application development, the issue boils down to a quote from our friend Albert Einstein...

"Everything should be made as simple as possible, but not one bit simpler. "

So let's look at an example of a DAO pattern. If you are a developer and unfamiliar with this pattern, shame on you. But here's a link anyway. While your at it, go google the GOF patterns and spend the weekend memorizing them. You will be asked about them on your next interview.

When you have simple tables with no foreign key relationships, it's as easy as pie.

CREATE TABLE SCHD_TYPE (
    SCHD_TYPE_ID  			VARCHAR2 (2)    NOT NULL,
    DESCRIPTION                    	VARCHAR2 (20)  NOT NULL,
    CONSTRAINT SCHD_TYPE_PK
    	PRIMARY KEY (SCHD_TYPE_ID)
);

public interface ScheduleType {

	public String getDescription();

	public void setDescription(String description);

	public String getScheduleTypeId();

	public void setScheduleTypeId(String scheduleTypeId);

}

public class ScheduleTypeImpl implements ScheduleType {

	private String scheduleTypeId;
	private String description;

	public String getDescription() {
		return description;
	}
	public void setDescription(String description) {
		this.description = description;
	}
	public String getScheduleTypeId() {
		return scheduleTypeId;
	}
	public void setScheduleTypeId(String scheduleTypeId) {
		this.scheduleTypeId = scheduleTypeId;
	}
}

public interface ScheduleTypeDAO {

	public static final String FIND_ALL = "SELECT * FROM schd_type";
	public Collection<ScheduleType> findAll();

	public static final String FIND_PK = "SELECT * FROM schd_type WHERE schd_type_id = ?";
	public ScheduleType find(long scheduleTypeId);

}

public class ScheduleTypeDAOImpl extends AbstractDataAccessObject implements ScheduleTypeDAO {

	static final private Logger log = Logger.getLogger(ScheduleTypeDAOImpl.class.getName());

	public Collection<ScheduleType> findAll(){
		StringBuffer sql = new StringBuffer(ScheduleTypeDAO.FIND_ALL);
		Connection con = null;
		ResultSet rs = null;
		Collection<ScheduleType> results = null;
		try {
			con = DatabaseConnectionFactory.getConnection(DatabaseConnectionFactory.POWERLINK);
			PreparedStatement stmt = con.prepareStatement(sql.toString());
			rs = stmt.executeQuery();
			results = populate(rs);
		} catch (SQLException e) {
			log.log(Level.SEVERE, e.getMessage());
			throw new DAOException(e);
		} finally {
			close(rs);
			close(con);
		}
		return results;
	}

	public ScheduleType find(long scheduleTypeId){
		StringBuffer sql = new StringBuffer(ScheduleTypeDAO.FIND_PK);
		Connection con = null;
		ResultSet rs = null;
		ScheduleType result = null;
		try {
			con = DatabaseConnectionFactory.getConnection(DatabaseConnectionFactory.POWERLINK);
			PreparedStatement stmt = con.prepareStatement(sql.toString());
			stmt.setLong(1, scheduleTypeId);
			rs = stmt.executeQuery();
			result = populate(rs).iterator().next();
		} catch (SQLException e) {
			log.log(Level.SEVERE, e.getMessage());
			throw new DAOException(e);
		} finally {
			close(rs);
			close(con);
		}
		return result;
	}

	private Collection<ScheduleType> populate(ResultSet rs){
		try {
			Collection<ScheduleType> result = new ArrayList<ScheduleType>();
			while (rs.next()) {
				ScheduleType o = new ScheduleTypeImpl();
				o.setScheduleTypeId(rs.getString("schd_type_id"));
			 	o.setDescription(rs.getString("description"));
			 	result.add(o);
			 }
			 return result;
		} catch (SQLException e) {
			log.log(Level.SEVERE, e.getMessage());
			throw new DAOException(e);
		}
	}
}

Simple. Clean. Extends. Flexible.

But then people get too fancy for their own good. For example, we all know there are fancier ways to populate objects witha resultset. Here's an example of the fancy way.

protected static synchronized Object populateObject(DataTransferObject dto, HashMap mapping, 

							ResultSet rs){
		Class clazz = dto.getClass();
		Iterator columnNames = mapping.keySet().iterator();
		ResultSetMetaData rsmd = null;
		try {
			 rsmd = rs.getMetaData();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		Object o = null;
		try {
			o = clazz.newInstance();
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		while(columnNames.hasNext()){
			String columnName = columnNames.next();
			String fieldName = mapping.get(columnName);
			Object columnValue = null;
			try {
				columnValue = rs.getObject(columnName);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			Field field = null;
			try {
				field = clazz.getDeclaredField(fieldName);
			} catch (SecurityException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (NoSuchFieldException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

			try {
				field.set(o, columnValue);
			} catch (IllegalArgumentException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return o;
	}

Fancy Schmancy. Until the developers want to do a join to populate an object. Or the developers need to build extra columns into a domain object but don't want to include an entire separate object. Or you have a naming conflict in the columns of multiple tables. This will work for some of your code but will leave the developers scrambling and coming up with their own implementations, which will all be DIFFERENT.

Keep it simple. Keep it readable. Keep it easy. Is it 3 minutes more of coding? Yes. Will it save you hours and hours of time down the road when you want to refactor or something doesn't fit the mold? Yes.